Oracle Tips and Tricks

David Clement

What the Tips and Tricks Are For

The Six Commandments

Parsing and Cursor Management

How SQL Is Parsed

Rules for Closing Cursors

Reopen Cursors in Pro*C/C++ to Reuse Them

Use Bind Variables to Help the Outline Check

Raise maxopencursors when Compiling Pro*C/C++

hold_cursor and release_cursor

Rollback Segments

The Read-Consistency Model and "Snapshot too old"

Do Not Commit Unnecessarily

Avoid "fetch across commit"

Autonomous Transactions

DML Tips

The Oracle Disk I/O Mechanism

Where is Full Table Scan Data Cached?

Do Not Update the Same Row Twice

Use One Statement to Insert with a Sequence Value

Update by Row ID

Coding Technique for Upsert

DML Implies a Query

Optimizing

Explaining the Execution Plan

Read the Execution Statistics

Using rownum with count (*)

in and exists

When is a Full Table Scan a Good Thing?

An Introduction to CBO Cost Estimates

SQL Functions

Group Functions Always Return a Value

Avoid Functions in Query Conditions

Roll Your Own Functions

Hidden Processing

Fetch into an Array

Stored SQL Reduces Network Traffic

Use Aliases in Joins

Initializing Public Variables

Trace Files

Profiling and Tracing

Bstat and Estat

Statspack

TkProf: A Worked Example

Low-Level Trace Files

Timing

SQL*Plus Timing

Space Management

Full Table Scans and High Water Marks

Rowid Formats

Index Bloating

Moving Segments out of SYSTEM

The XMLiad