10g explain plan output

found a new explain plan statement.

in addition to normal expected explain plan output like operation, access plan, cost and rows returned,
it also includes predicate information like:

Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("D"."OPERATOR_ID"="V"."FRANCHISE_ID" AND "D"."BILLING_NETWORK_OPERATOR_ID"="V"."NOP_ID")
     filter("D"."YEAR_MONTH_START_DATE">=TRUNC("V"."START_DATE") AND
     "D"."YEAR_MONTH_START_DATE"<=TRUNC(NVL("V"."END_DATE","D"."YEAR_MONTH_END_DATE")) AND
     NVL("D"."OP_VARIANCE_PERCENT","D"."BOP_VARIANCE_PERCENT")>=CASE WHEN
     ("V"."PREVIOUS_PERCENT"="V"."PERCENTAGE") THEN 0 WHEN ("V"."PREVIOUS_PERCENT"<"V"."PERCENTAGE") THEN
     "V"."PREVIOUS_PERCENT"+0.0000001 END AND "V"."PERCENTAGE">=NVL("D"."OP_VARIANCE_PERCENT","D"."BOP_VARIAN
     CE_PERCENT"))

The predicate information explains why a certain operation was taken at the step.

The script is simple:

SELECT plan_table_output
FROM table(dbms_xplan.display('plan_table', null,'SERIAL'));
-- parameters:
-- 1. plan TABLE name
-- 2. statement id -- Null will fetch the last explain plan
-- 3. level OF detail eg BASIC, SERIAL, TYPICAL, ALL

Links

• Subscribe to this feed