Posts Tagged ‘DBMS_XPLAN’
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