Why oracle doesn’t provide delta value in the output of DBMS_XPLAN.

Delta Value is very efficient to find the steps which take long time.

DBMS_XPLAN is very powerful and useful tool for sql tuning. But, there is no delta value in the output of DBMS_XPLAN. So, when you want to find the steps which take a long time, you have to substract each sigma value. In my opinion, it’s very time consuming. If you agree with me, the following query maybe helpful to you. The fomula for delta value is very simple.

Detal Value = The value of current ID – SUM(The value of PARENT_ID(s) which is same number with current ID).

 WITH v1 AS (
           SELECT vp.id pid,
                  nvl(SUM( vp1.last_cr_buffer_gets ),0) sum_lio,
                  nvl(SUM( vp1.last_elapsed_time), 0) sum_ela
           FROM   v$sql_plan_statistics_all vp ,
                  v$sql_plan_statistics_all vp1
           WHERE  vp.sql_id = :sql_id
           AND    vp.child_number=0
           AND    vp.sql_id = vp1.sql_id(+)
           AND    vp.child_number=vp1.child_number(+)
           AND    vp.id = vp1.parent_id(+)
           GROUP  BY vp.id
     ),
     v2 AS (
            SELECT MAX( vp.last_cr_buffer_gets ) max_lio ,
                   MAX( vp.last_elapsed_time ) max_ela
            FROM   v$sql_plan_statistics_all vp
            WHERE  vp.sql_id = :sql_id
            AND    vp.child_number=0
     )
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ /* EXEC_FROM_DBMS_XPLAN */
       id,
       depth ,
       position ,
       parent_id,
       lpad(' ', depth*2       )||operation||' '||options Operation,
       object_name name,
       cardinality e_rows ,
       outrows a_rows ,
       crgets "LIO_SIGMA(BLOCK)",
       crgets - (select sum_lio from v1 where v1.pid=a.id) "LIO_DELTA(BLOCK)",
       round((crgets - (select sum_lio from v1 where v1.pid=a.id))/(select max_lio from v2)*100,1) "LIO_RATIO(%)",
       round(etime/1000000,2) "ELA_SIGMA(SEC)",
       round((etime  - (select sum_ela from v1 where v1.pid=a.id))/1000000,2) "ELA_DELTA(SEC)",
       round((etime  - (select sum_ela from v1 where v1.pid=a.id))/(select max_ela from v2)*100,1) "ELA_RATIO(%)"
FROM   (
        SELECT 
 
               vp.id id ,
               vp.depth depth ,
               vp.position position ,
               vp.operation operation ,
               vp.options options ,
               vp.parent_id parent_id,
             --  vp.cost cost ,
             --  vp.time TIME ,
               vp.cardinality cardinality ,
             --  vp.bytes bytes ,
             --  vp.object_node object_node ,
               vp.object_name object_name ,
             --  vp.temp_space temp_space ,
               vp.last_starts starts ,
               vp.last_output_rows outrows ,
               vp.last_cr_buffer_gets crgets ,
             --  vp.last_cu_buffer_gets cugets ,
             --  vp.last_disk_reads reads ,
             --  vp.last_disk_writes writes ,
               vp.last_elapsed_time etime --,
             --  vp.estimated_optimal_size mem_opt ,
             --  vp.estimated_onepass_size mem_one ,
             --  vp.last_memory_used last_mem_used ,
             --  vp.last_execution last_mem_usage ,
             --  vp.optimal_executions opt_cnt ,
             --  vp.onepass_executions one_cnt ,
             --  vp.multipasses_executions multi_cnt ,
             --  vp.max_tempseg_size max_tmp ,
             --  vp.last_tempseg_size last_tmp
        FROM   v$sql_plan_statistics_all vp
        WHERE  vp.sql_id = :sql_id
        AND    vp.child_number=0
       ) a