Things to look at when investigating poorly performing SQL

i) The stats for the table and index may be out of date.

ii) Set the session variables as above.

iii) Don’t get caught out by explain plan (use toad most of the time because it’s easiest to use but use ‘explain plan for…’ to confirm conclusions.

iv) Don’t get caught out by database caching results in the SGA. Use different data values for queries to hit bits of the table than are not cached.

v) Don’t assume the query path is the same for all parameters in your query. The CBO may change it query path depending on the number of rows it expects to return.

vi) Toad only returns the first 500 rows by default, do a count(*) to confirm your findings.