Peter Ramm's Oracle performance analysis stuff

For performance analysis of problematic SQL statements it is crucial to have the extended statistics of the execution plan available. These extended statistics per plan line include as total and last gathered values: The number of starts of execution of this plan line The number of rows gotten by execution of this plan line The number of DB buffers scanned in consistent or current mode The number of disk reads and writes The elapsed time during execution of this plan line There are several ways to force the DB to record these extended statistics: Set the global initialization variable 'STATISTICS_LEVEL to 'ALL' to enable them for the whole DB 'ALTER SESSION SET STATISTICS_LEVEL=ALL;' to enable them for the current session The optimizer hint /*+ GATHER_PLAN_STATISTICS */ in the SQL to enable the extented only for this SQL statement Unfortunately, to activate the extended statistics for a specific SQL by adding the optimizer hint, you ...

If only following columns of an index are used as filter conditions and the first columns of the index are missing in the filter, Oracle's optimizer may use the INDEX SKIP SCAN operation. At SQL execution the DB will iterate in that case over all distinct values of the skipped column and proceed with B-tree access for the columns used as access criteria. The efficiency of a SKIP SCAN therefore depends on the number of distinct values for the skipped column(s). If the skipped column has only one distinct value, then the SKIP SCAN operation will succeed with 3-5 buffer gets similar to a regular RANGE SCAN. If the skipped column has lots of distinct values, then the B-tree access with the filter criteria will be executed as many times as the number of distinct values, resulting in thousands or millions of buffer gets for a single index access instead of 3-5. So far, this is mostly known for SKIP SCAN. But also if the SQL plan states an INDEX RANGE SCAN, this possibly problemati...