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


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
TkProf: A Worked Example
Low-Level Trace Files


SQL*Plus Timing

Space Management

Full Table Scans and High Water Marks
Rowid Formats
Index Bloating
Moving Segments out of SYSTEM

The XMLiad