Peter Ramm's Oracle performance analysis stuff

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...

To view how the Oracle optimizer comes to his decision, there are several ways to create a trace file like ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS 'TRACE[rdbms.SQL_Optimizer.*][sql:6jksrnjx4zvuk]'; EXEC DBMS_SQLDIAG.DUMP_TRACE(p_sql_id, p_child_number, p_component, p_file_id); Especially the DBMS_SQLDIAG.DUMP_TRACE helps to quickly review the parse steps of a SQL that already has a valid plan in the SGA. Using the parameter p_component=>'Compiler' parses the SQL again even if it only needs a soft parse. Use this steps to view the optimizer trace also without filesystem access on DB server -- grab the SQL and reparse it with trace EXEC DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'6jksrnjx4zvuk', p_child_number=>0, p_component=>'Compiler', p_file_id=>'mine'); -- find the created trace file by the used p_file_id SELECT Inst_ID, ADR_Home, Trace_Filename, ...

If using user-defined PL/SQL functions in SQL, it is crucial for the optimizer to know if a function is deterministic or not. Deterministic means, that the result of a function call is stable for subsequent calls with the same call paramater values. You need to tell the optimizer that the result can be considered as stable by adding the keyword DETERMINISTIC to the function declaration. CREATE OR REPLACE FUNCTION MyFunc() RETURN NUMBER DETERMINISTIC ... If knowing that a function is deterministic, the SQL engine can cache the function results for repeated calls of this function. This can make a huge difference for the SQL execution effort. Consider an example: A table MyTab has 1 mio. records. A package function does some calculations returning a value that is used as filter criteria at access on MyTab. CREATE TABLE MyTab(ID NUMBER); INSERT INTO MyTab SELECT Level FROM DUAL CONNECT BY LEVEL Now let's check what happens. SET SERVEROUTPUT ON; SELECT * FROM MyTab WHERE ID = My...