Diagnose cardinality errors in explain plan

Actual vs Estimated Row in Explain plan:

We can use several tools to see the internals of the SQL optimizer execution plan details in Oracle database. These are:

• TKPROF (SQL*Trace) 10046
• SQL*Plus "set autotrace" or explain plan for <query>
• Extended execution plan statistics (v$sql_workarea)

We can use third party tools for the same.

Sometimes we may see number of rows are extremely high but cardinality is less for the tables. This may be the issue with optimizer or stats collection. So you may have following common question now.

“How do I know if the cardinality estimates in a plan are accurate?”

To find actual and estimated rows in explain plan, use “/*+ GATHER_PLAN_STATISTICS */” hint and execute the query and then run below command to find it.

Sample Example:

The Query:

SELECT /*+ GATHER_PLAN_STATISTICS */ MAX (to_number(ORDER_NO||PRE_ORDER_NO)) 

FROM ORDERS OR1 

WHERE TRUNC(OR1.OR_EFFECTIVE_DATE_START) <='07-FEB-2018' 

AND ( greatest (TRUNC(OR1.RECV_DATE),TRUNC(OR1.DATE_POSTED)) >= '01-JAN-1995' 

AND TRUNC(OR1.RECV_DATE) <= '07-FEB-2018' ) 

AND OR1.DELIVERY_STATUS = 'YES' ;

Collect explain plan after execution:

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));  

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------

SQL_ID  9ybvktk8n7d9v, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ MAX (to_number(ORDER_NO||PRE_ORDER_NO))

FROM ORDERS OR1

WHERE

TRUNC(OR1.OR_EFFECTIVE_DATE_START) <=:"SYS_B_0"

AND ( greatest(TRUNC(OR1.RECV_DATE),TRUNC(OR1.DATE_POSTED)) >= :"SYS_B_1"

AND TRUNC(OR1.RECV_DATE) <= :"SYS_B_2" )

AND OR1.DELIVERY_STATUS =:"SYS_B_3"

Plan hash value: 1806674675

---------------------------------------------------------------------------------------------

| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |          |      1 |        |      1 |00:05:11.90 |      27 |

|   1 |  SORT AGGREGATE        |          |      1 |      1 |      1 |00:05:11.90 |      27 |

|   2 |   PX COORDINATOR       |          |      1 |        |     32 |00:05:05.89 |      27 |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      0 |      1 |      0 |00:00:00.01 |       0 |

|   4 |     SORT AGGREGATE     |          |      0 |      1 |      0 |00:00:00.01 |       0 |

|   5 |      PX BLOCK ITERATOR |          |      0 |  67667 |      0 |00:00:00.01 |       0 |

|*  6 |       TABLE ACCESS FULL| ORDERS   |      0 |  67667 |      0 |00:00:00.01 |       0 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access(:Z>=:Z AND :Z<=:Z)

       filter(("OR1"."DELIVERY_STATUS"=:SYS_B_3 AND

              GREATEST(TRUNC(INTERNAL_FUNCTION("OR1"."RECV_DATE")),TRUNC(INTERNAL_FUNCTION("OR1

              "."DATE_POSTED")))>=:SYS_B_1 AND TRUNC(INTERNAL_FUNCTION("DM_EFFECTIVE_DATE_STAR

              T"))<=:SYS_B_0 AND TRUNC(INTERNAL_FUNCTION("RECV_DATE"))<=:SYS_B_2))

Note

-----

   - dynamic statistics used: dynamic sampling (level=AUTO)

   - Degree of Parallelism is 32 because of table property

In order to see the A-Rows values for all of the operations in the plan you would have to use the FORMAT value  'ALLSTATS ALL', which will show you the execution statistics for ALL executions of the cursor.

Click here to learn more…