Tales From A Lazy Fat DBA

Struck in a difficult performance issue related with a SQL and you have to verify if the Cardinality estimates made by the MIGHTY CBO are correct, No idea how t0 do that 😦 😦

Lets things make little easy for ourselves!
Let me take an example and explain how to do this.

SQL Statement (From my Personal Test Environment):
SELECT COUNT (DISTINCT SB_NO) FROM OPS$EXP.C_AL_SB WHERE SB_NO IN (SELECT DISTINCT SB_NO FROM OPS$EXP.C_AL_AWB WHERE EGM_DT BETWEEN :1 AND :1 ) AND ERR_MESG =’S’

Below is the execution plan for the SQL (Lets forgot about the behemoth elapsed time and Cost and Rows Processed in the plan for a minute 🙂 ) ….

So the above plan doesn’t show any estimations or Cardinality details what it considered during the creation of the plan, But starting from 10g we have GATHER_PLAN_STATISTICS hint. The GATHER_PLAN_STATISTICS hint tells Oracle to collect execution statistics for a SQL statement.

These execution statistics are then shown next to the original Optimizer estimates in the execution plan if you use the function DBMS_XPLAN.DISPLAY_CURSOR to display the plan. You also have to set the FORMAT parameter to ‘ALLSTATS LAST’ (DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>’ALLSTATS LAST’)).

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT (DISTINCT SB_NO) FROM OPS$EXP.C_AL_SB WHERE SB_NO IN (SELECT DISTINCT SB_NO FROM OPS$EXP.C_AL_AWB WHERE EGM_DT BETWEEN :1 AND :1 ) AND ERR_MESG =’S’;

The execution plan for the query is as follows:

The original Optimizer estimates are shown in the E-Rows column while the actual statistics gathered during execution are shown in the A-Rows column.