-- May 2020 -- Jure Bratina, Pythian -- -- -- DISCLAIMER -- -- This script was prepared by the author in a personal capacity and does not constitute technical advice -- or recommendation from Pythian. Content in this script is provided AS-IS, without representations or -- warranties of any kind and Pythian expressly disclaims all warranties, including express, statutory or -- implied warranties of non-infringement, merchantability, and fitness for a particular purpose. -- Pythian does not make any warranty regarding the accuracy, adequacy, validity, reliability, availability, -- or completeness of the Content provided in this script. You understand that the Content may contain -- defects or errors. Your access to and use of the Content in this script is at your own risk. Pythian -- will not accept any liability arising out of your access to or use of the Content provided in this script. -- -- -- -- Purpose: Reporting SQL performance related statistics as they appear in the "SQL ordered by" -- Statspack's report sections for a time period provided as an input parameter. -- Script's core logic is based on Oracle's $ORACLE_HOME/rdbms/admin/sprepins.sql (StatsPack -- Report Instance) script. sprepins.sql is not required to execute statspack_top_sqls.sql. -- Script output is wide, so it's suggested to spool the output to a file for easier viewing. -- -- -- Usage: Start statspack_top_sqls.sql, and provide the input parameters as illustrated below -- -- Example: -- -- SQL> @statspack_top_sqls.sql -- List SQL by [elapsed_time | cpu_time | buffer_gets | disk_reads | executions | parse_calls | max_sharable_mem | max_version_count | cluster_wait_time]: -- -- Enter a value - default "elapsed_time" : /* provide one of the above attributes to order the reported SQLs by */ -- -- -- Instances in this Statspack schema -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- DB Id Inst Num DB Name Instance Host -- ----------- -------- ------------ ------------ ------------- -- 1558102526 1 ORCL orcl1 ol7-122-rac1 -- 1558102526 2 ORCL orcl2 ol7-122-rac2 -- -- -- Enter DBID to analyze - default "1558102526" : /* enter DBID to analyze */ -- Enter instance number or "all" to analyze all instancs for DBID = 1558102526 - default "all" : /* report data for a specific RAC instance or all of them */ -- -- -- Enter begin time for report [DD-MON-YYYY HH24:MI] - default "30-APR-2020 10:54" : /* specify time period to analyze */ -- Enter end time for report [DD-MON-YYYY HH24:MI] - default "30-APR-2020 22:54" : -- SCRIPT -- Below values are taken from $ORACLE_HOME/rdbms/admin/sprepcon.sql, some of them are modified -- Number of Rows of SQL to display in each SQL section of the report define top_n_sql = 65 -- Number of rows of SQL text to print in the SQL sections of the report for each hash_value -- define num_rows_per_hash = 4; -- Comment: default is 4, however we need to define it as 1, otherwise there will be duplicate rows in the output, -- one for each line of SQL from stats$sqltext. If more than 1 line of SQL is requred, consider using a -- "BREAK ON" sqlplus formatting clause including all of the relevant columns, so they don't repeat for each -- SQL text line define num_rows_per_hash = 1 -- Filter which restricts the rows of SQL shown in the SQL sections of the -- report to be the top N pct define top_pct_sql = 1.0 set lin 1000 pagesize 9999 tab off trimspool on feedback off verify off heading on newpage none arraysize 1000 -- order SQL-s by one of the below attributes: PROMPT PROMPT List SQL by [elapsed_time | cpu_time | buffer_gets | disk_reads | executions | parse_calls | max_sharable_mem | max_version_count | cluster_wait_time]: PROMPT ACCEPT top_n_by_attribute CHAR PROMPT 'Enter a value - default "elapsed_time" : ' default elapsed_time whenever sqlerror exit; begin IF lower('&top_n_by_attribute') not in ('elapsed_time','cpu_time','buffer_gets', 'disk_reads','executions','parse_calls', 'max_sharable_mem','max_version_count','cluster_wait_time') THEN raise_application_error(-20200, '''&top_n_by_attribute'' is not a valid option.'); END IF; end; / whenever sqlerror continue; column dbbid new_val dbid heading "DB Id" format 9999999999 just c; column instt_num heading "Inst Num" format 99999; column instt_name heading "Instance" format a12; column dbb_name heading "DB Name" format a12; column host heading "Host" format a32; prompt prompt prompt Instances in this Statspack schema prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ select distinct dbid dbbid , instance_number instt_num , db_name dbb_name , instance_name instt_name , host_name host from stats$database_instance order by instance_name, instance_number, dbid; PROMPT PROMPT ACCEPT p_dbid CHAR PROMPT 'Enter DBID to analyze - default "&dbid" : ' default '&dbid' ACCEPT p_inst_num CHAR PROMPT 'Enter instance number or "all" to analyze all instancs for DBID = &p_dbid - default "all" : ' default 'all' PROMPT PROMPT SET TERMOUT OFF column dt_s new_val dt_s column dt_e new_val dt_e select to_char(sysdate-12/24,'DD-MON-YYYY HH24:MI') dt_s, to_char(sysdate,'DD-MON-YYYY HH24:MI') dt_e from dual; SET TERMOUT ON ACCEPT start_date_time CHAR PROMPT 'Enter begin time for report [DD-MON-YYYY HH24:MI] - default "&dt_s" : ' default '&dt_s' ACCEPT end_date_time CHAR PROMPT 'Enter end time for report [DD-MON-YYYY HH24:MI] - default "&dt_e" : ' default '&dt_e' PROMPT PROMPT col module for a60 col SQL_TEXT for a75 col DBtimemin for 99,990.99 col AAS for 99,990.99 BREAK ON instance_number SKIP 1 ON b_snap_id skip 1 on e_snap_id on b_snap_time on e_snap_time on interval_min on DBtimemin on aas alter session set nls_date_format='DD-MON-YY HH24:MI:SS'; select /*+ cursor_sharing_exact */ topn.instance_number, topn.b_snap_id, topn.e_snap_id, topn.b_snap_time, topn.e_snap_time, topn.interval_min, topn.DBtimemin, topn.AAS, topn.old_hash_value hv, round(delta_elapsed_time/1000000, 2) elapsed_time_sec, delta_executions executions, decode(delta_executions , 0, 0, round(delta_elapsed_time/1000000/delta_executions,2)) elapsed_per_exec_sec, decode(topn.dbtim, 0, 0 ,round(100*delta_elapsed_time/topn.dbtim, 2)) percent_of_dbtime_used, round(delta_cpu_time/1000000, 2) cpu_time_sec, decode(delta_executions , 0, 0, round(delta_cpu_time/delta_executions/1000, 2)) cpu_time_ms_per_exec, delta_disk_reads physical_reads, decode(delta_executions , 0, 0, round(delta_disk_reads/delta_executions, 2)) physical_reads_per_execution, delta_buffer_gets buffer_gets, decode(delta_executions , 0, 0, round(delta_buffer_gets/delta_executions, 2)) gets_per_execution, delta_rows_processed rows_processed, decode(delta_executions , 0, 0, round(delta_rows_processed/delta_executions, 2)) rows_processed_per_execution, delta_parse_calls parse_calls, round(max_sharable_mem/1024, 2) max_sharable_mem_kb, round(last_sharable_mem/1024, 2) last_sharable_mem_kb, max_version_count, last_version_count, delta_version_count, round(delta_cluster_wait_time/1000000, 2) cluster_wait_time_sec, decode(delta_elapsed_time, 0, 0, round(100*delta_cluster_wait_time/delta_elapsed_time, 2)) cwt_percent_of_elapsed_time, round(avg_hard_parse_time/1000, 2) avg_hard_parse_time_ms, topn.module, translate(st.sql_text, chr(9) || chr(13) || chr(10), ' ') sql_text -- convert tabs and newlines to spaces from ( select * from ( select gtt.*, row_number() over (partition by gtt.dbid, gtt.instance_number, gtt.e_snap_id order by gtt.dbid, gtt.instance_number, gtt.e_snap_id, case when '&top_n_by_attribute' = 'elapsed_time' then delta_elapsed_time when '&top_n_by_attribute' = 'cpu_time' then delta_cpu_time when '&top_n_by_attribute' = 'buffer_gets' then delta_buffer_gets when '&top_n_by_attribute' = 'disk_reads' then delta_disk_reads when '&top_n_by_attribute' = 'executions' then delta_executions when '&top_n_by_attribute' = 'parse_calls' then delta_parse_calls when '&top_n_by_attribute' = 'max_sharable_mem' then max_sharable_mem when '&top_n_by_attribute' = 'max_version_count' then max_version_count when '&top_n_by_attribute' = 'cluster_wait_time' then delta_cluster_wait_time else delta_elapsed_time end desc ) rn from ( with sysstat as ( select /*consider enabling in case of TEMP TABLE TRANSFORMATION performance related issues: "qb_name(sysstat) inline"*/ * from (select instance_number, snap_id, dbid, name, value from STATS$SYSSTAT where name in ('physical reads', 'session logical reads', 'execute count', 'parse count (total)' ) ) pivot (sum(value) for (name) in ('physical reads' physical_reads, 'session logical reads' session_logical_reads, 'execute count' execute_count, 'parse count (total)' tot_parses ) ) ) , sys_time_model as ( select /*consider enabling in case of TEMP TABLE TRANSFORMATION performance related issues: "qb_name(sys_tm_model) inline"*/ * from (select instance_number, snap_id, dbid, stat_name, value from stats$sys_time_model stm, stats$time_model_statname tms where stm.stat_id = tms.stat_id and tms.stat_name in ('DB CPU', 'DB time') ) pivot (sum(value) for (stat_name) in ('DB CPU' dbcpu, 'DB time' dbtime) ) ) , snaps as (select s.dbid, s.instance_number, s.b b_snap_id, s.e e_snap_id, s.bt b_snap_time, s.et e_snap_time, round((s.et - s.bt)*24*60,2) interval_min, sys_time_model_e.dbtime - sys_time_model_b.dbtime dbtim , round((sys_time_model_e.dbtime - sys_time_model_b.dbtime)/1000000/60,2) DBtimemin , round(((sys_time_model_e.dbtime - sys_time_model_b.dbtime)/1000000/60)/((s.et - s.bt)*24*60),2) "AAS" , s.esmt , s.evc , sysstat_e.session_logical_reads - sysstat_b.session_logical_reads session_logical_reads , sysstat_e.physical_reads - sysstat_b.physical_reads physical_reads , sysstat_e.execute_count - sysstat_b.execute_count execute_count , sysstat_e.tot_parses - sysstat_b.tot_parses tot_parses , sys_time_model_e.dbcpu - sys_time_model_b.dbcpu dbcpu , i.parallel from sys_time_model sys_time_model_b , sys_time_model sys_time_model_e , (select dbid, instance_number, snap_id b, snap_time bt, lead(snap_id) over (order by dbid, instance_number, snap_id) e, lead(snap_time) over (order by dbid, instance_number, snap_id) et, startup_time b_startup_time, lead(startup_time) over (order by dbid, instance_number, snap_id) e_startup_time, lead(sharable_mem_th) over (order by dbid, instance_number, snap_id) esmt, lead(version_count_th) over (order by dbid, instance_number, snap_id) evc from stats$snapshot) s , sysstat sysstat_b , sysstat sysstat_e , stats$database_instance i where sys_time_model_b.snap_id = s.b and sys_time_model_b.instance_number = s.instance_number and sys_time_model_b.dbid = s.dbid and sys_time_model_e.snap_id = s.e and sys_time_model_e.instance_number = s.instance_number and sys_time_model_e.dbid = s.dbid and s.b_startup_time = s.e_startup_time and sysstat_b.snap_id = s.b and sysstat_b.dbid = s.dbid and sysstat_b.instance_number = s.instance_number and sysstat_e.snap_id = s.e and sysstat_e.dbid = s.dbid and sysstat_e.instance_number = s.instance_number and i.dbid = s.dbid and i.instance_number = s.instance_number and i.startup_time = s.b_startup_time and s.dbid = to_number('&p_dbid') and (lower('&p_inst_num') = 'all' or s.instance_number = to_number('&p_inst_num')) and s.bt >= to_date('&start_date_time','DD-MON-YYYY HH24:MI') and s.et <= to_date('&end_date_time','DD-MON-YYYY HH24:MI') ) select old_hash_value, s2.instance_number, s2.b_snap_id, s2.e_snap_id, s2.b_snap_time, s2.e_snap_time, s2.dbid, s2.interval_min, s2.dbtim, s2.dbcpu, s2.DBtimemin, s2.AAS, s2.session_logical_reads, s2.physical_reads, s2.execute_count, s2.tot_parses, lev1.text_subset, lev1.module, lev1.delta_buffer_gets, lev1.delta_executions, lev1.delta_cpu_time, lev1.delta_elapsed_time, lev1.avg_hard_parse_time, lev1.delta_disk_reads, lev1.delta_parse_calls, lev1.max_sharable_mem, lev1.last_sharable_mem, lev1.delta_version_count, lev1.max_version_count, lev1.last_version_count, lev1.delta_cluster_wait_time, lev1.delta_rows_processed, s2.parallel, s2.esmt, s2.evc from ( select -- sum deltas dbid, snap_id, instance_number, old_hash_value , text_subset , module , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (buffer_gets < prev_buffer_gets) then buffer_gets else buffer_gets - prev_buffer_gets end end) delta_buffer_gets , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (executions < prev_executions) then executions else executions - prev_executions end end) delta_executions , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (cpu_time < prev_cpu_time) then cpu_time else cpu_time - prev_cpu_time end end) delta_cpu_time , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (elapsed_time < prev_elapsed_time) then elapsed_time else elapsed_time - prev_elapsed_time end end) delta_elapsed_time , avg(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else avg_hard_parse_time end) avg_hard_parse_time , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (disk_reads < prev_disk_reads) then disk_reads else disk_reads - prev_disk_reads end end) delta_disk_reads , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (parse_calls < prev_parse_calls) then parse_calls else parse_calls - prev_parse_calls end end) delta_parse_calls , max(sharable_mem) max_sharable_mem , sum(case when snap_id = lev0.e_snap_id then last_sharable_mem else 0 end) last_sharable_mem , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (version_count < prev_version_count) then version_count else version_count - prev_version_count end end) delta_version_count , max(version_count) max_version_count , sum(case when snap_id = lev0.e_snap_id then last_version_count else 0 end) last_version_count , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (cluster_wait_time < prev_cluster_wait_time) then cluster_wait_time else cluster_wait_time - prev_cluster_wait_time end end) delta_cluster_wait_time , sum(case when ((snap_id = lev0.b_snap_id and prev_snap_id = -1) or (snap_id not in (lev0.b_snap_id,lev0.e_snap_id))) then 0 else case when (address != prev_address) or (rows_processed < prev_rows_processed) then rows_processed else rows_processed - prev_rows_processed end end) delta_rows_processed from (select /* commented out the first_rows hint from sprepins.sql - alternatively use "qb_name(sql_sum) index(s)" */ snaps.dbid, s.snap_id, snaps.b_snap_id, snaps.e_snap_id, s.instance_number , old_hash_value , text_subset , module , (lag(snap_id, 1, -1) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_snap_id , (lead(snap_id, 1, -1) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) next_snap_id , address ,(lag(address, 1, hextoraw(0)) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_address , buffer_gets ,(lag(buffer_gets, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_buffer_gets , cpu_time ,(lag(cpu_time, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_cpu_time , executions ,(lag(executions, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_executions , elapsed_time ,(lag(elapsed_time, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_elapsed_time , avg_hard_parse_time , disk_reads ,(lag(disk_reads, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_disk_reads , parse_calls ,(lag(parse_calls, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_parse_calls , sharable_mem ,(last_value(sharable_mem) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) last_sharable_mem ,(lag(sharable_mem, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_sharable_mem , version_count ,(lag(version_count, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_version_count ,(last_value(version_count) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) last_version_count , cluster_wait_time ,(lag(cluster_wait_time, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_cluster_wait_time , rows_processed ,(lag(rows_processed, 1, 0) over (partition by old_hash_value , s.dbid , s.instance_number order by snap_id, b_snap_id)) prev_rows_processed from stats$sql_summary s, snaps where ((s.snap_id = snaps.b_snap_id) or (s.snap_id = snaps.e_snap_id)) and s.dbid = snaps.dbid and s.instance_number = snaps.instance_number ) lev0 group by dbid, instance_number , snap_id, old_hash_value , text_subset , module ) lev1, snaps s2 where s2.instance_number = lev1.instance_number and s2.dbid = lev1.dbid and lev1.snap_id = s2.e_snap_id and ( delta_buffer_gets > 0 or delta_executions > 0 or delta_cpu_time > 0 or delta_disk_reads > 0 or delta_parse_calls > 0 or max_sharable_mem >= s2.esmt or max_version_count >= s2.evc or delta_cluster_wait_time > 0 ) ) gtt where ('&top_n_by_attribute' = 'elapsed_time' and decode(gtt.dbtim, 0, 2, 100*delta_elapsed_time/gtt.dbtim) > decode(gtt.dbtim, 0, 1, &&top_pct_sql)) or ('&top_n_by_attribute' = 'cpu_time' and decode(gtt.dbcpu, 0, 2, null, 2, 100*delta_cpu_time/gtt.dbcpu) > decode(gtt.dbcpu, 0, 1, null, 2, &&top_pct_sql)) or ('&top_n_by_attribute' = 'buffer_gets' and 100*delta_buffer_gets/gtt.session_logical_reads > &&top_pct_sql ) or ('&top_n_by_attribute' = 'disk_reads' and gtt.physical_reads > 0 and 100*delta_disk_reads/gtt.physical_reads > &&top_pct_sql ) or ('&top_n_by_attribute' = 'executions' and 100*delta_executions/gtt.execute_count > &&top_pct_sql ) or ('&top_n_by_attribute' = 'parse_calls' and 100*delta_parse_calls/gtt.tot_parses > &&top_pct_sql ) or ('&top_n_by_attribute' = 'max_sharable_mem' and max_sharable_mem > gtt.esmt) or ('&top_n_by_attribute' = 'max_version_count' and max_version_count > gtt.evc) or ('&top_n_by_attribute' = 'cluster_wait_time' and delta_cluster_wait_time > 0 and gtt.parallel = 'YES') ) x where rn <= &&top_n_sql order by instance_number asc, b_snap_id asc, case when '&top_n_by_attribute' = 'elapsed_time' then delta_elapsed_time when '&top_n_by_attribute' = 'cpu_time' then delta_cpu_time when '&top_n_by_attribute' = 'buffer_gets' then delta_buffer_gets when '&top_n_by_attribute' = 'disk_reads' then delta_disk_reads when '&top_n_by_attribute' = 'executions' then delta_executions when '&top_n_by_attribute' = 'parse_calls' then delta_parse_calls when '&top_n_by_attribute' = 'max_sharable_mem' then max_sharable_mem when '&top_n_by_attribute' = 'max_version_count' then max_version_count when '&top_n_by_attribute' = 'cluster_wait_time' then delta_cluster_wait_time else delta_elapsed_time end desc ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash -- number of rows of SQL text to output order by topn.instance_number asc, topn.b_snap_id asc, case when '&top_n_by_attribute' = 'elapsed_time' then topn.delta_elapsed_time when '&top_n_by_attribute' = 'cpu_time' then topn.delta_cpu_time when '&top_n_by_attribute' = 'buffer_gets' then topn.delta_buffer_gets when '&top_n_by_attribute' = 'disk_reads' then topn.delta_disk_reads when '&top_n_by_attribute' = 'executions' then topn.delta_executions when '&top_n_by_attribute' = 'parse_calls' then topn.delta_parse_calls when '&top_n_by_attribute' = 'max_sharable_mem' then topn.max_sharable_mem when '&top_n_by_attribute' = 'max_version_count' then topn.max_version_count when '&top_n_by_attribute' = 'cluster_wait_time' then topn.delta_cluster_wait_time else delta_elapsed_time end desc, topn.old_hash_value, st.piece;