Oracle tips

1. Slow running queries caused by bad optimization plans.

The Solution: Gather statistics on temp tables.

The Reason: If ONE table in a query doesn't have statistics, the whole query is optimized
by Rule rather than Cost. Don't believe it when they say the rule-based optimizer is gone.
Explain Plan doesn't tell you which optimizer was used; you can only tell by inference.

2. Transactions partially committed.

The Solution: Don't do administrative (DDL) commands during a transaction.

The Reason: Every DDL command, even if it fails, does an implicit commit. The most common
offenders are ALTER SESSION SET <nls setting> <sql trace> <current_schema>
<timed_statistics> and ALTER SYSTEM ARCHIVE LOG <start/stop/next>.

3. Speedup not working as expected.

Example: You have a transaction table with ten million rows. Being a well designed table,
its first column is a sequential number, Tran_ID, which is indexed. A full table scan is
taking too long. You know the transactions of interest are in the last million rows, so
you add
WHERE Tran_ID > 9000000
It should find that row in the index and start searching there. But it doesn't, it's still
doing a full table scan.

The Solution: WHERE Tran_ID > CAST(9000000 as NUMBER)

The Reason: Literals are untyped until late in the optimization process. You have a type
mismatch between the literal and the index, causing it to not use the index. If you're
writing PL/SQL, you can accomplish the same by putting 9000000 in a host variable of type
number. You can use the less elegant TO_NUMBER(9000000).

In article <quo4945dl4cn230c7tj68lsmt3vob43vcu@4ax.com>,
Robert <no@e.mail> wrote:

Greatly appreciated, Mr Wagner, even if it is a tad off-topic... one of
these days I'll have to find a way to use that 9i DBA certification I
earned a few years ago.

DD