Oracle AWR run manully

Oracle AWR report run



from web
http://www.optimaldba.com/scripts/cust_awr_10g.sql



-- awr_report.sql


---===========================================


SET LINESIZE 152 PAGESIZE 45 FEEDBACK off VERIFY off
TTITLE off
CLEAR SCREEN

-- Generic Columns
COLUMN rpt_db_time_pct FORMAT A7 HEADING 'DB Time' JUSTIFY RIGHT
COLUMN rpt_event_name FORMAT A50 HEADING 'Event'
COLUMN rpt_spacer FORMAT A32 HEADING ""

COLUMN rpt_sql_id FORMAT A14 HEADING 'SQL ID'
COLUMN rpt_sql_elapsed FORMAT A26 HEADING 'Elapsed Time (us)'
COLUMN rpt_sql_cpu FORMAT A26 HEADING 'CPU Time (us)'
COLUMN rpt_sql_lios FORMAT A26 HEADING 'Logical I/O'
COLUMN rpt_sql_pios FORMAT A26 HEADING 'Physical I/O'
COLUMN rpt_sql_execs_num FORMAT 999,999,999 HEADING 'Executions'
COLUMN rpt_sql_rows_num FORMAT 999,999,999,999 HEADING 'Rows'

COLUMN rpt_stat_name FORMAT A30 HEADING 'Statistic'
COLUMN rpt_stat_value FORMAT 999,999,999,999 HEADING 'Value'

COLUMN rpt_val_minutes FORMAT 999,999,999.99 HEADING 'Minutes'
COLUMN rpt_val_seconds FORMAT 999,999,999.99 HEADING 'Seconds'
COLUMN rpt_val_centi FORMAT 999,999,999 HEADING 'Centiseconds'
COLUMN rpt_val_milli FORMAT 999,999,999,999 HEADING 'Milliseconds'
COLUMN rpt_val_micro FORMAT 999,999,999,999,999 HEADING 'Microseconds'

COLUMN rpt_val_count FORMAT 999,999,999 HEADING 'Count'
COLUMN rpt_val_name FORMAT A40
COLUMN rpt_val_pct FORMAT 999,990.99 HEADING 'Pct'
COLUMN rpt_val_per_second FORMAT 999,999,999.99 HEADING 'Per Second'
COLUMN rpt_val_per_tx FORMAT 999,999,999.99 HEADING 'Per Trans'
COLUMN sort_order FORMAT 99999 NOPRINT


SET PAGESIZE 0

/*
Get Current database and instance to use as default values
*/

COLUMN curr_db_id FORMAT 999999999999 HEADING 'DBID' NEW_VALUE curr_db_id NOPRINT
COLUMN curr_db_name FORMAT A10 HEADING 'DB Name' NEW_VALUE curr_db_name NOPRINT
COLUMN curr_inst_id FORMAT 999 HEADING 'InstID' NEW_VALUE curr_inst_id NOPRINT
COLUMN curr_inst_name FORMAT A10 HEADING 'Instance Name' NEW_VALUE curr_inst_name NOPRINT


SELECT d.dbid curr_db_id
, d.name curr_db_name
FROM sys.v$database d
/

SELECT i.instance_number curr_inst_id
, i.instance_name curr_inst_name
FROM v$instance i
/

SET VERIFY OFF

SET PAGESIZE 45

/*
Database Selection
*/

COLUMN awr_db_id FORMAT A12 HEADING 'DBID' NEW_VALUE awr_db_id
COLUMN awr_db_name FORMAT A10 HEADING 'DB Name' NEW_VALUE awr_db_name
COLUMN awr_db_version FORMAT A10 HEADING 'Version'
COLUMN awr_db_rac FORMAT A03 HEADING 'RAC'

PROMPT
PROMPT *** Databases in AWR Repository ***
PROMPT

SELECT wdi.db_name||CASE WHEN wdi.db_name = '&curr_db_name' THEN '*' END awr_db_name
, RPAD(wdi.dbid,12) awr_db_id
, wdi.version awr_db_version
, REPLACE(wdi.parallel, 'YES', 'RAC') awr_db_rac
FROM sys.wrm$_database_instance wdi
WHERE wdi.instance_number = 1
AND wdi.startup_time = ( SELECT MAX(wdi2.startup_time)
FROM sys.wrm$_database_instance wdi2
WHERE wdi.dbid = wdi2.dbid
AND wdi.instance_number = wdi2.instance_number
)
ORDER BY wdi.db_name
/

PROMPT
ACCEPT usr_db_name PROMPT 'Database Name <&curr_db_name> : ' DEFAULT &curr_db_name

-- Instance Selection
COLUMN awr_inst_host_name FORMAT A30 HEADING 'Host Name'
COLUMN awr_inst_id FORMAT A4 HEADING 'Inst|ID' NEW_VALUE awr_inst_id
COLUMN awr_inst_name FORMAT A10 HEADING 'Instance|Name' NEW_VALUE awr_inst_name
COLUMN awr_inst_start_time FORMAT A30 HEADING 'Start Time'

PROMPT
PROMPT *** Instances for &usr_db_name in AWR Repository ***
PROMPT

SELECT wdi.instance_number||CASE WHEN wdi.instance_name = '&curr_inst_name' THEN '*' END awr_inst_id
, wdi.instance_name awr_inst_name
, wdi.startup_time awr_inst_start_time
, wdi.host_name awr_inst_host_name
FROM sys.wrm$_database_instance wdi
WHERE UPPER(wdi.db_name) = UPPER('&usr_db_name')
AND wdi.startup_time = ( SELECT MAX(wdi2.startup_time)
FROM sys.wrm$_database_instance wdi2
WHERE wdi.dbid = wdi2.dbid
AND wdi.instance_number = wdi2.instance_number
)
ORDER BY wdi.instance_number
/

PROMPT
ACCEPT usr_inst_name PROMPT 'Instance ID <&curr_inst_name> : ' DEFAULT &curr_inst_name

SET HEADING OFF
COLUMN rpt_db_id FORMAT 999999999999 HEADING 'DBID' NEW_VALUE rpt_db_id NOPRINT
COLUMN rpt_inst_id FORMAT A4 HEADING 'Inst|ID' NEW_VALUE rpt_inst_id NOPRINT

SELECT DISTINCT wdi.dbid rpt_db_id
, wdi.instance_number rpt_inst_id
FROM sys.wrm$_database_instance wdi
WHERE UPPER(wdi.db_name) = UPPER('&usr_db_name')
AND UPPER(wdi.instance_name) = UPPER('&usr_inst_name')
/
SET HEADING ON

SET HEADING OFF
COLUMN snap_int_min FORMAT A100
COLUMN snap_retention FORMAT A100 FOLD_BEFORE
SELECT 'Current Snapshot Interval for &usr_db_name ==> '||EXTRACT(minute FROM snap_interval)||' minutes' snap_int_min
, 'Current Snapshot Retention for &usr_db_name ==> '||EXTRACT(day FROM retention)||' days' snap_retention
FROM sys.wrm$_wr_control
WHERE dbid = &rpt_db_id
/
SET HEADING ON

@awr_snap_wkly_matrix &rpt_db_id &rpt_inst_id

SET PAGESIZE 45

ACCEPT usr_begin_snap PROMPT 'Enter Beginning Snapshot ID : '
ACCEPT usr_end_snap PROMPT 'Enter Ending Snapshot ID : '

/*
Set spool name as "cust_awr_dbid_instid_begintime_endtime.lst
*/

/*
Get report times
Elapsed
DB Time
*/

COLUMN elapsed_time_minutes FORMAT 9999999999999999 HEADING 'Elapsed Time|Minutes' NEW_VALUE elapsed_time_minutes NOPRINT
COLUMN elapsed_time_seconds FORMAT 9999999999999999 HEADING 'Elapsed Time|Seconds' NEW_VALUE elapsed_time_seconds NOPRINT

SELECT ROUND(SUM(EXTRACT (DAY FROM (sn.end_interval_time - sn.begin_interval_time)) * 86400 +
EXTRACT (HOUR FROM (sn.end_interval_time - sn.begin_interval_time)) * 3600 +
EXTRACT (MINUTE FROM (sn.end_interval_time - sn.begin_interval_time)) * 60 +
ROUND(EXTRACT (SECOND FROM (sn.end_interval_time - sn.begin_interval_time)))
)/60) elapsed_time_minutes
, SUM(EXTRACT (DAY FROM (sn.end_interval_time - sn.begin_interval_time)) * 86400 +
EXTRACT (HOUR FROM (sn.end_interval_time - sn.begin_interval_time)) * 3600 +
EXTRACT (MINUTE FROM (sn.end_interval_time - sn.begin_interval_time)) * 60 +
ROUND(EXTRACT (SECOND FROM (sn.end_interval_time - sn.begin_interval_time)))
) elapsed_time_seconds
FROM sys.wrm$_snapshot sn
WHERE sn.snap_id between (&usr_begin_snap + 1) and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
/

SET HEADING off
COLUMN db_time_stat_id FORMAT 9999999999999999 NEW_VALUE db_time_stat_id NOPRINT

SELECT sname.stat_id db_time_stat_id
FROM wrh$_stat_name sname
WHERE sname.stat_name = 'DB time'
AND sname.dbid = &rpt_db_id
/

COLUMN total_db_time_micro FORMAT 9999999999999999999 NEW_VALUE total_db_time_micro NOPRINT
COLUMN total_db_time_minutes FORMAT 9999999999999999 HEADING 'DB Time|Minutes' NEW_VALUE total_db_time_minutes NOPRINT
COLUMN total_db_time_seconds FORMAT 9999999999999999 HEADING 'DB Time|Seconds' NEW_VALUE total_db_time_seconds NOPRINT

WITH db_time_q
AS ( SELECT wtm.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap
THEN 0
ELSE LAG(wtm.value) OVER (PARTITION BY sn.startup_time
ORDER BY sn.snap_id) END),wtm.value) snap_db_time
FROM sys.wrh$_sys_time_model wtm
, sys.wrm$_snapshot sn
WHERE wtm.stat_id = &db_time_stat_id
AND wtm.snap_id between &usr_begin_snap and &usr_end_snap
AND wtm.dbid = &rpt_db_id
AND wtm.instance_number = &rpt_inst_id
AND sn.snap_id = wtm.snap_id
AND sn.dbid = wtm.dbid
AND sn.instance_number = wtm.instance_number
)
SELECT SUM(snap_db_time) total_db_time_micro
, ROUND(SUM(snap_db_time)/60000000) total_db_time_minutes
, ROUND(SUM(snap_db_time)/1000000) total_db_time_seconds
FROM db_time_q
/

SET HEADING ON PAGESIZE 45
COLUMN time_desc FORMAT A20 HEADING ""

PROMPT
PROMPT *** Report Times ***
PROMPT

SELECT 'Elapsed Time' time_desc
, &elapsed_time_minutes rpt_val_minutes
, &elapsed_time_seconds rpt_val_seconds
FROM dual
UNION ALL
SELECT 'Database Time' time_desc
, &total_db_time_minutes rpt_val_minutes
, &total_db_time_seconds rpt_val_seconds
FROM dual
ORDER BY time_desc DESC
/



SET HEADING off
COLUMN rpt_transactions FORMAT 9999999999999999 HEADING 'Transactions' NEW_VALUE rpt_transactions NOPRINT

WITH sysstat_value_q
AS ( SELECT wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_sysstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('user commits', 'user rollbacks')
)
SELECT SUM(svq.snap_metric_value) rpt_transactions
FROM sysstat_value_q svq
/
SET HEADING on

PROMPT
PROMPT
PROMPT
PROMPT *** Load Profile ***
PROMPT

WITH sysstat_value_q
AS ( SELECT wsv.stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_sysstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('user calls', 'execute count',
'parse count (total)', 'parse count (hard)',
'session logical reads', 'db block changes',
'redo size', 'physical reads', 'physical writes',
'recursive calls', 'sorts (memory)', 'sorts (disk)')
),
sysstat_value_summary
AS ( SELECT CASE WHEN svq.stat_name LIKE 'sorts%' THEN 'sorts'
ELSE svq.stat_name
END stat_name
, SUM(svq.snap_metric_value) rpt_value
, ROUND((SUM(svq.snap_metric_value) / &elapsed_time_seconds),2) rpt_val_per_second
, ROUND((SUM(svq.snap_metric_value) / &rpt_transactions),2) rpt_val_per_tx
FROM sysstat_value_q svq
GROUP BY CASE WHEN svq.stat_name LIKE 'sorts%' THEN 'sorts'
ELSE svq.stat_name
END
UNION ALL
SELECT 'transactions' stat_name
, &rpt_transactions rpt_value
, ROUND((&rpt_transactions / &elapsed_time_seconds),2) rpt_val_per_second
, NULL
FROM dual
)
SELECT DECODE(svs.stat_name,
'transactions', 001,
'user calls', 002,
'recursive calls', 003,
'execute count', 004,
'parse count (total)', 005,
'parse count (hard)', 006,
'session logical reads', 007,
'physical reads', 008,
'physical writes',009,
'db block changes',010,
'redo size',011,
'sorts',012,
9999) sort_order
, svs.stat_name rpt_stat_name
, svs.rpt_value rpt_stat_value
, svs.rpt_val_per_second rpt_val_per_second
, svs.rpt_val_per_tx rpt_val_per_tx
FROM sysstat_value_summary svs
ORDER BY sort_order
/

PROMPT
PROMPT
PROMPT
PROMPT *** Operating System Statistics ***
PROMPT
PROMPT *** O/S Configuration (end) ***
PROMPT

SELECT wsv.stat_name rpt_stat_name
, wsv.value rpt_stat_value
FROM dba_hist_osstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id = &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('NUM_CPUS', 'PHYSICAL_MEMORY_BYTES')
/

PROMPT
PROMPT
PROMPT
PROMPT *** O/S Time ***
PROMPT

COLUMN qry_val_per_second FORMAT 999,999,999.99 HEADING 'Seconds|Per Second'

WITH sysstat_value_q
AS ( SELECT wsv.stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_osstat wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('BUSY_TIME', 'IDLE_TIME', 'SYS_TIME', 'USER_TIME')
)
SELECT DECODE(svq.stat_name,
'BUSY_TIME', 001,
'SYS_TIME', 002,
'USER_TIME', 003,
'IDLE_TIME', 004,
9999) sort_order
, svq.stat_name rpt_stat_name
, SUM(svq.snap_metric_value) rpt_val_centi
, CASE WHEN svq.stat_name LIKE '%TIME' THEN SUM(svq.snap_metric_value)/100 END rpt_val_seconds
, ROUND(((CASE WHEN svq.stat_name LIKE '%TIME' THEN SUM(svq.snap_metric_value)/100 END) / &elapsed_time_seconds),4) qry_val_per_second
FROM sysstat_value_q svq
GROUP BY svq.stat_name
ORDER BY sort_order
/

PROMPT
PROMPT
PROMPT
PROMPT *** Time Model Statistics ***
PROMPT

WITH sysstat_value_q
AS ( SELECT wsv.stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_metric_value
FROM dba_hist_sys_time_model wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name IN ('DB CPU', 'DB time', 'sql execute elapsed time',
'parse time elapsed', 'hard parse elapsed time',
'PL/SQL execution elapsed time', 'background elapsed time',
'background cpu time')
)
SELECT DECODE(svq.stat_name,
'DB time', 001,
'DB CPU', 002,
'sql execute elapsed time', 003,
'parse time elapsed', 004,
'hard parse elapsed time', 005,
'PL/SQL execution elapsed time', 006,
'background elapsed time', 007,
'background cpu time', 008,
9999) sort_order
, svq.stat_name rpt_stat_name
, SUM(svq.snap_metric_value) rpt_val_micro
, SUM(svq.snap_metric_value)/1000000 rpt_val_seconds
, CASE WHEN svq.stat_name IN ('DB CPU','sql execute elapsed time','parse time elapsed',
'hard parse elapsed time', 'PL/SQL execution elapsed time')
THEN LPAD(TO_CHAR(ROUND((SUM(svq.snap_metric_value)/&total_db_time_micro),4)*100)||'%',7)
END rpt_db_time_pct
FROM sysstat_value_q svq
GROUP BY svq.stat_name
ORDER BY sort_order
/

PROMPT
PROMPT
PROMPT
PROMPT *** Top 10 Timed Events ***
PROMPT

COLUMN rpt_avg_micro FORMAT 999,999.99 HEADING 'Avg Time|(micro)'
COLUMN rpt_event_count FORMAT 999,999,999,999 HEADING 'Event Count'
WITH sysstat_value_q
AS ( SELECT wsv.event_name
, wsv.time_waited_micro - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.time_waited_micro) OVER (PARTITION BY sn.startup_time, wsv.event_name
ORDER BY sn.snap_id) END),wsv.time_waited_micro) snap_wait_time
, wsv.total_waits - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.total_waits) OVER (PARTITION BY sn.startup_time, wsv.event_name
ORDER BY sn.snap_id) END),wsv.total_waits) snap_wait_count
FROM dba_hist_system_event wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.wait_class != 'Idle'
UNION ALL
SELECT 'CPU time' stat_name
, wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0
ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name
ORDER BY sn.snap_id) END),wsv.value) snap_wait_time
, NULL snap_wait_count
FROM dba_hist_sys_time_model wsv
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND wsv.snap_id = sn.snap_id
AND wsv.dbid = sn.dbid
AND wsv.instance_number = sn.instance_number
AND wsv.stat_name = 'DB CPU'
),
sysstat_wait_summary
AS ( SELECT svq.event_name
, SUM(svq.snap_wait_time) event_value
, DENSE_RANK() OVER (ORDER BY SUM(svq.snap_wait_time) DESC) event_rank
, SUM(svq.snap_wait_count) event_count
FROM sysstat_value_q svq
GROUP BY svq.event_name
),
sysstat_wait_pcts
AS ( SELECT sws.event_name
, RATIO_TO_REPORT(sws.event_value) OVER () event_pct
FROM sysstat_wait_summary sws
)
SELECT sws.event_rank sort_order
, sws.event_name rpt_event_name
, ROUND((sws.event_value/1000000),2) rpt_val_seconds
, ROUND((swp.event_pct*100),2) rpt_val_pct
, ROUND(sws.event_value/sws.event_count)/1000 rpt_avg_micro
, sws.event_count rpt_event_count
FROM sysstat_wait_summary sws
, sysstat_wait_pcts swp
WHERE sws.event_rank <= 10
AND sws.event_name = swp.event_name
ORDER BY sws.event_rank ASC
/

PROMPT
PROMPT
PROMPT
PROMPT *** SQL Ranking Overview ***
PROMPT

WITH sql_stats_summary
AS ( SELECT sqs.sql_id
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.buffer_gets_delta
END lios
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.disk_reads_delta
END pios
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.elapsed_time_delta
END ela_time
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.cpu_time_delta
END cpu_time
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.executions_delta
END execs
, CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.rows_processed_delta
END q_rows
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
),
sql_ranking
AS ( SELECT sqs.sql_id
, SUM(lios) lios
, DENSE_RANK() OVER (ORDER BY SUM(lios) DESC) lios_rank
, SUM(pios) pios
, DENSE_RANK() OVER (ORDER BY SUM(pios) DESC) pios_rank
, SUM(ela_time) ela_time
, DENSE_RANK() OVER (ORDER BY SUM(ela_time) DESC) ela_rank
, SUM(cpu_time) cpu_time
, DENSE_RANK() OVER (ORDER BY SUM(cpu_time) DESC) cpu_rank
, SUM(execs) execs
, SUM(q_rows) q_rows
FROM sql_stats_summary sqs
GROUP BY sqs.sql_id
),
sql_top_5
AS ( SELECT sqlr.sql_id
, sqlr.lios
, sqlr.lios_rank
, sqlr.pios
, sqlr.pios_rank
, sqlr.ela_time
, sqlr.ela_rank
, sqlr.cpu_time
, sqlr.cpu_rank
, sqlr.execs
, sqlr.q_rows
FROM sql_ranking sqlr
WHERE ( sqlr.lios_rank <= 5
OR sqlr.pios_rank <= 5
OR sqlr.ela_rank <= 5
OR sqlr.cpu_rank <= 5
)
),
sql_plan_count
AS ( SELECT sqs.sql_id
, COUNT(DISTINCT sqs.plan_hash_value) plan_count
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
, sql_top_5 st5
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
AND sqs.sql_id = st5.sql_id
AND sqs.executions_delta > 0
GROUP BY sqs.sql_id
)
SELECT sql5.sql_id||(CASE WHEN spc.plan_count > 1 THEN '*' ELSE NULL END) rpt_sql_id
, TO_CHAR(sql5.ela_time, '99,999,999,999,999')||LPAD('('||TO_CHAR(ela_rank)||')', 6) rpt_sql_elapsed
, TO_CHAR(sql5.cpu_time, '99,999,999,999,999')||LPAD('('||TO_CHAR(cpu_rank)||')', 6) rpt_sql_cpu
, TO_CHAR(sql5.lios, '99,999,999,999,999')||LPAD('('||TO_CHAR(lios_rank)||')', 6) rpt_sql_lios
, TO_CHAR(sql5.pios, '99,999,999,999,999')||LPAD('('||TO_CHAR(pios_rank)||')', 6) rpt_sql_pios
, sql5.execs rpt_sql_execs_num
, sql5.q_rows rpt_sql_rows_num
FROM sql_top_5 sql5
, sql_plan_count spc
WHERE sql5.sql_id = spc.sql_id (+)
ORDER BY (sql5.ela_rank + sql5.lios_rank + sql5.pios_rank + sql5.cpu_rank + sql5.wait_rank) ASC
/





SET PAGESIZE 0
BREAK ON qry_sql_id NODUP SKIP 2 ON qry_sql_text NODUP SKIP 2
COLUMN qry_sql_id FORMAT A20
COLUMN qry_sql_text FORMAT A100 WORD_WRAP FOLD_AFTER
COLUMN qry_exec_plan_hash FORMAT A36 FOLD_BEFORE
COLUMN qry_execs FORMAT A36
COLUMN qry_rows FORMAT A36
COLUMN qry_rows_per_exec FORMAT A36

COLUMN qry_spacer FORMAT A36 FOLD_BEFORE

COLUMN qry_ela_time FORMAT A36
COLUMN qry_cpu_time FORMAT A36
COLUMN qry_wait_time FORMAT A36

COLUMN qry_ela_per_exec FORMAT A36
COLUMN qry_cpu_per_exec FORMAT A36
COLUMN qry_wait_per_exec FORMAT A36

COLUMN qry_lios FORMAT A36
COLUMN qry_lios_per_exec FORMAT A36
COLUMN qry_lios_per_row FORMAT A36
COLUMN qry_pios FORMAT A36
COLUMN qry_pios_per_exec FORMAT A36
COLUMN qry_pios_per_row FORMAT A36


PROMPT
PROMPT
PROMPT
PROMPT *** SQL Executions ***
PROMPT

WITH sql_stats_summary
AS ( SELECT sqs.sql_id
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.buffer_gets_delta
END) DESC) lios_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.disk_reads_delta
END) DESC) pios_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.elapsed_time_delta
END) DESC) ela_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.cpu_time_delta
END) DESC) cpu_rank
, DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE (sqs.elapsed_time_delta - sqs.cpu_time_delta)
END) DESC) wait_rank
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
GROUP BY sqs.sql_id
),
sql_top_5
AS ( SELECT sqlr.sql_id
FROM sql_stats_summary sqlr
WHERE ( sqlr.lios_rank <= 5
OR sqlr.pios_rank <= 5
OR sqlr.ela_rank <= 5
OR sqlr.cpu_rank <= 5
OR sqlr.wait_rank <= 5
)
),
sql_plan_stats
AS ( SELECT sqs.sql_id
, sqs.plan_hash_value
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.buffer_gets_delta
END) lios
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.disk_reads_delta
END) pios
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.elapsed_time_delta
END) ela_time
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.cpu_time_delta
END) cpu_time
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE (sqs.elapsed_time_delta - sqs.cpu_time_delta)
END) wait_time
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.executions_delta
END) execs
, SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0
ELSE sqs.rows_processed_delta
END) q_rows
FROM dba_hist_sqlstat sqs
, sys.wrm$_snapshot sn
, sql_top_5 sql5
WHERE sqs.sql_id = sql5.sql_id
AND sn.snap_id between &usr_begin_snap and &usr_end_snap
AND sn.dbid = &rpt_db_id
AND sn.instance_number = &rpt_inst_id
AND sqs.snap_id = sn.snap_id
AND sqs.dbid = sn.dbid
AND sqs.instance_number = sn.instance_number
GROUP BY sqs.sql_id, sqs.plan_hash_value
)
SELECT sps.sql_id qry_sql_id
, SUBSTR(sqt.sql_text, 1, 500) qry_sql_text
, 'Plan Hash ==> '||sps.plan_hash_value qry_exec_plan_hash
, 'Executions ==> '||TO_CHAR(execs,'999,999,999,999') qry_execs
, 'Rows ==> '||TO_CHAR(q_rows,'999,999,999,999') qry_rows
, 'Rows per Exec ==> '||TO_CHAR(ROUND(q_rows/DECODE(execs,0,1,execs)),'999,999,999,999') qry_rows_per_exec
, NULL qry_spacer
, 'Ela Time(s) ==> '||LPAD(TO_CHAR(ela_time/1000000,'999,990.900000'),16) qry_ela_time
, 'CPU Time(us) ==> '||LPAD(TO_CHAR(cpu_time/1000000,'999,990.900000'),16) qry_cpu_time
, 'Wait Time(us) ==> '||LPAD(TO_CHAR(wait_time/1000000,'999,990.900000'),16) qry_wait_time
, NULL qry_spacer
, 'Ela per Exec ==> '||LPAD(TO_CHAR(ROUND((ela_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_ela_per_exec
, 'CPU per Exec ==> '||LPAD(TO_CHAR(ROUND((cpu_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_cpu_per_exec
, 'Wait per Exec ==> '||LPAD(TO_CHAR(ROUND((wait_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_wait_per_exec
, NULL qry_spacer
, 'Logical I/O ==> '||TO_CHAR(lios,'999,999,999,999') qry_lios
, 'LIO per Exec ==> '||TO_CHAR(ROUND(lios/DECODE(execs,0,1,execs)),'999,999,999,999') qry_lios_per_exec
, 'LIO per Row ==> '||TO_CHAR(ROUND(lios/DECODE(q_rows,0,1,q_rows)),'999,999,999,999') qry_lios_per_row
, NULL qry_spacer
, 'Physical I/O ==> '||TO_CHAR(pios,'999,999,999,999') qry_pios
, 'PIO per Exec ==> '||TO_CHAR(ROUND(pios/DECODE(execs,0,1,execs)),'999,999,999,999') qry_pios_per_exec
, 'PIO per Row ==> '||TO_CHAR(ROUND(pios/DECODE(q_rows,0,1,q_rows)),'999,999,999,999') qry_pios_per_row
FROM sql_plan_stats sps
, dba_hist_sqltext sqt
WHERE sps.execs > 0
AND sps.sql_id = sqt.sql_id
ORDER BY sps.sql_id
, sps.ela_time DESC
/



==============================================================
snap matrix report


prompt awr_snap_wkly_matrix.sql <<<<<<<<<<<<<<<<< copy this script in same dir


COLUMN sort_ord NOPRINT
COLUMN snap_week NOPRINT

DEFINE awr_dbid = &1
DEFINE awr_instid = &2

SET HEADING off PAGESIZE 0
TTITLE off

PROMPT *** Snapshots in AWR repository for Database &usr_db_name Instance &usr_inst_name
PROMPT *** Weekly by hour (intermediate snapshots not listed)

SELECT DISTINCT 001 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, 'Sunday' sunday_snapid
, 'Monday' monday_snapid
, 'Tuesday' tuesday_snapid
, 'Wednesday' wednesday_snapid
, 'Thursday' thursday_snapid
, 'Friday' friday_snapid
, 'Saturday' saturday_snapid
FROM sys.wrm$_snapshot
UNION ALL
SELECT 010 sort_ord
, TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week
, NULL hour_of_day
, TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid
, TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
, TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
, TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
, TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
, TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
, TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
) s
UNION ALL
SELECT 011 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT DISTINCT 020 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, '---------' hour_of_day
, '---------' sunday_snapid
, '---------' monday_snapid
, '---------' tuesday_snapid
, '---------' wednesday_snapid
, '---------' thursday_snapid
, '---------' friday_snapid
, '---------' saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
UNION ALL
SELECT 030 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
, TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT DISTINCT 999 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, NULL sunday_snapid
, NULL monday_snapid
, NULL tuesday_snapid
, NULL wednesday_snapid
, NULL thursday_snapid
, NULL friday_snapid
, NULL saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = &awr_dbid
AND instance_number = &awr_instid
ORDER BY snap_week
, sort_ord
, hour_of_day
/

SET HEADING ON