SQL Script to Find the Hash Plans for a SQL id and its execution details

SQL Script to Find the Hash Plans for a SQL id and its execution details

Script

select sql_id, plan_hash_value as "Plan hash"

     , sum(executions_calc) as "Times called"

     , sum(end_of_fetch_count) as "Times completed"

     , round(100 * sum(end_of_fetch_count) / sum(executions_calc),1) as "Success %"

     , cast(numtodsinterval(sum(elapsed_time)/1E6,'SECOND') as interval day(1) to second(2)) as "Total time"

     -- , round(sum(elapsed_time)/1e6) as "Total seconds"

     , round(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),1) as "Average seconds"

     , cast(numtodsinterval(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"

     -- , sum(buffer_gets) as "Buffer gets"

     , round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"

     , round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"

     , sum(rows_processed) as "Rows"

     , round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"

     , cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1E6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"

     , cast

       ( case

            when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then  -- 2**31 -1, limit for 32 bit integers and a

                 numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')

            else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')

         end  -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values

       as interval day(5) to second(0)) as "Avg time/1M rows"

     , sum(px_servers_executions) as "PX server executions"

from   ( select s.sql_id

              , trunc(s.last_active_time) as exec_date

              , plan_hash_value

              --, executions

              , case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF

              , px_servers_executions

              , elapsed_time

              , buffer_gets

              , rows_processed

              , end_of_fetch_count

         from   v$sqlstats s

         union

         select s.sql_id

              , trunc(cast(h.begin_interval_time as date)) as exec_date

              , plan_hash_value

              --, executions_delta executions

              , nullif(executions_delta,0) executions_calc

              , px_servers_execs_delta as px_servers_executions

              , elapsed_time_delta as elapsed_time

              , buffer_gets_delta as buffer_gets

              , rows_processed_delta as rows_processed

              , end_of_fetch_count_delta as end_of_fetch_count

         from   dba_hist_sqlstat s

                join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid and h.instance_number = s.instance_number

       )

where  sql_id = '&sql_id'

group by sql_id,plan_hash_value

having sum(executions_calc) > 0

order by sql_id;

If you like please follow and comment