254 lines
15 KiB
SQL
254 lines
15 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top SQLs from AWR (By Weight)
|
|
* Version : 10.1 and above
|
|
* Parameters : 1 - Instance Number (Default is '%' ie ALL)
|
|
* 2 - From Timestamp (YYYY-MM-DD HH24:MI:SS)
|
|
* 3 - To Timestamp (YYYY-MM-DD HH24:MI:SS)
|
|
* 4 - Top record Count
|
|
* 5 - Where Clause
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 20-JAN-14 Vishal Gupta Intial version
|
|
*
|
|
*/
|
|
|
|
set lines 20000
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE INST_ID
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE TO_TIMESTAMP
|
|
UNDEFINE TOP_RECORDCOUNT
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE FROM_TIMESTAMP="&&2"
|
|
DEFINE TO_TIMESTAMP="&3"
|
|
DEFINE TOP_RECORDCOUNT="&4"
|
|
DEFINE WHERECLAUSE="&&5"
|
|
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
COLUMN _FROM_TIMESTAMP NEW_VALUE FROM_TIMESTAMP NOPRINT
|
|
COLUMN _TO_TIMESTAMP NEW_VALUE TO_TIMESTAMP NOPRINT
|
|
COLUMN _TOP_RECORDCOUNT NEW_VALUE TOP_RECORDCOUNT NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
, DECODE('&&FROM_TIMESTAMP','',TO_CHAR(NEXT_DAY(TRUNC(SYSDATE-14),'MONDAY'),'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIMESTAMP') "_FROM_TIMESTAMP"
|
|
, DECODE('&&TO_TIMESTAMP','',TO_CHAR(NEXT_DAY(TRUNC(SYSDATE-7),'MONDAY'),'YYYY-MM-DD ') || '00:02:00','&&TO_TIMESTAMP') "_TO_TIMESTAMP"
|
|
, DECODE('&&TOP_RECORDCOUNT','','30','&&TOP_RECORDCOUNT') "_TOP_RECORDCOUNT"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
|
|
DEFINE COUNT_SMALL_FORMAT=999,999,999,999
|
|
DEFINE COUNT_SMALL_DIVIDER="1"
|
|
DEFINE COUNT_SMALL_HEADING="#"
|
|
--DEFINE COUNT_SMALL_DIVIDER="1000"
|
|
--DEFINE COUNT_SMALL_HEADING="#1000"
|
|
--DEFINE COUNT_SMALL_DIVIDER="1000000"
|
|
--DEFINE COUNT_SMALL_HEADING="#million"
|
|
|
|
DEFINE COUNT_FORMAT=999,999,999
|
|
--DEFINE COUNT_DIVIDER="1"
|
|
--DEFINE COUNT_HEADING="#"
|
|
DEFINE COUNT_DIVIDER="1000"
|
|
DEFINE COUNT_HEADING="#1000"
|
|
|
|
DEFINE BYTES_FORMAT="999,999"
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024"
|
|
--DEFINE BYTES_HEADING="MB"
|
|
DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
DEFINE BYTES_HEADING="GB"
|
|
|
|
DEFINE TIME_FORMAT=99,999
|
|
DEFINE TIME_DIVIDER="60"
|
|
DEFINE TIME_HEADING="min"
|
|
|
|
DEFINE WEIGHT_FORMAT=999,999
|
|
--DEFINE WEIGHT_DIVIDER="1000"
|
|
--DEFINE WEIGHT_HEADING="#1000"
|
|
DEFINE WEIGHT_DIVIDER="1000000"
|
|
DEFINE WEIGHT_HEADING="Million"
|
|
--DEFINE WEIGHT_DIVIDER="1000000000"
|
|
--DEFINE WEIGHT_HEADING="Billion"
|
|
|
|
|
|
DEFINE elapsed_time_weight=10
|
|
DEFINE executions_weight=0.1
|
|
DEFINE buffer_gets_weight=0.00000001
|
|
DEFINE physical_read_bytes_weight=0.00000015
|
|
DEFINE physical_write_bytes_weight=0.000001
|
|
|
|
COLUMN min_time HEADING "Snap Time (Min)" FORMAT a15
|
|
COLUMN max_time HEADING "Snap Time (Max)" FORMAT a15
|
|
COLUMN instance_number HEADING "I#" FORMAT 99
|
|
COLUMN username HEADING "UserName" FORMAT a20
|
|
COLUMN SQL_ID HEADING "SQLId" FORMAT a13
|
|
COLUMN force_matching_signature HEADING "Force matching signature" FORMAT a30
|
|
COLUMN plan_hash_value HEADING "Plan|Hash|Value"
|
|
COLUMN weight HEADING "Weight|(&WEIGHT_HEADING)" FORMAT &&WEIGHT_FORMAT NOPRINT
|
|
COLUMN reason HEADING "Top|Reason" FORMAT a11
|
|
COLUMN invalidations_total HEADING "Invalid|Total|(&COUNT_HEADING)" FORMAT 9,999
|
|
COLUMN iowait_total HEADING "IO|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN apwait_total HEADING "Appl|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN ccwait_total HEADING "Conc|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN clwait_total HEADING "Cluster|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN plsexec_time_total HEADING "PLsexec|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN cpu_time_total HEADING "CPU|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN elapsed_time_total HEADING "Elapsed|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
|
|
--COLUMN executions_total HEADING "Exec|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
|
|
COLUMN executions_total HEADING "Exec|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
|
|
COLUMN rows_processed_total HEADING "Rows|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
|
|
COLUMN sorts_total HEADING "Sorts|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
|
|
COLUMN buffer_gets_total HEADING "Buffer|Gets|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
|
|
COLUMN direct_writes_total HEADING "Direct|Write|Total|(&COUNT_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN io_interconnect_bytes_total HEADING "IO|Inter|Connect|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN physical_read_bytes_total HEADING "Phy|Read|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN physical_write_bytes_total HEADING "Phy|Write|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN direct_write_bytes_total HEADING "Direct|Write|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN physical_change_bytes_total HEADING "Phy|Change|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN optimized_physical_reads_total HEADING "Optimized|Phy|ReadReq|(&COUNT_HEADING)" FORMAT &TIME_FORMAT
|
|
COLUMN io_offload_elig_bytes_total HEADING "IO|Offload|Elig|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN io_offload_return_bytes_total HEADING "IO|Offload|Return|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
|
|
Prompt
|
|
Prompt ***************************************************************************************************
|
|
Prompt * A W R T O P &&TOP_RECORDCOUNT S Q L s
|
|
Prompt *
|
|
Prompt * Input Parameters
|
|
Prompt * - Instance Number - "&&INST_ID"
|
|
Prompt * - From Timestamp - "&&from_timestamp" (YYYY-MM-DD HH24:MI:SS)
|
|
Prompt * - To Timestamp - "&&to_timestamp" (YYYY-MM-DD HH24:MI:SS)
|
|
Prompt * - Top RecordCount - "&&TOP_RECORDCOUNT"
|
|
Prompt * - Where Clause - "&&WHERECLAUSE"
|
|
Prompt ***************************************************************************************************
|
|
|
|
set timing on
|
|
|
|
WITH topsql as
|
|
(
|
|
SELECT /*+ NO_MERGE NO_UNNEST */
|
|
*
|
|
FROM (
|
|
SELECT /*+ LEADING(d s) USE_NL (d s ss) PARALLEL(s,8) PARALLEL(ss,8)*/
|
|
CASE WHEN REGEXP_LIKE(DECODE(ss.force_matching_signature,0,ss.sql_id,ss.force_matching_signature), '[a-z]')
|
|
THEN '0'
|
|
ELSE DECODE(ss.force_matching_signature,0,ss.sql_id,ss.force_matching_signature)
|
|
END force_matching_signature
|
|
, max(ss.sql_id) sql_id
|
|
, DECODE( GREATEST(SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight
|
|
, SUM(ss.executions_delta) * &&executions_weight
|
|
--, SUM(ss.buffer_gets_delta) * &&buffer_gets_weight
|
|
, SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight
|
|
, SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight
|
|
)
|
|
, SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight ,'CPU/Elapsed'
|
|
, SUM(ss.executions_delta) * &&executions_weight ,'Execs'
|
|
--, SUM(ss.buffer_gets_delta) * &&buffer_gets_weight ,'BufferGets'
|
|
, SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight ,'PhyRead'
|
|
, SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight ,'PhyWrite'
|
|
) Reason
|
|
, ROUND((
|
|
SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight
|
|
+ SUM(ss.executions_delta) * &&executions_weight
|
|
--+ SUM(ss.buffer_gets_delta) * &&buffer_gets_weight
|
|
+ SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight
|
|
+ SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight
|
|
)/&WEIGHT_DIVIDER) weight
|
|
/*
|
|
, ROUND(SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight) elapsed_time_weight
|
|
, ROUND(SUM(ss.executions_delta) * &&executions_weight) executions_weight
|
|
--, ROUND(SUM(ss.buffer_gets_delta) * &&buffer_gets_weight) buffer_gets_weight
|
|
, ROUND(SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight) physical_read_bytes_weight
|
|
, ROUND(SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight) physical_write_bytes_weight
|
|
*/
|
|
, ROUND(SUM(ss.iowait_delta)/1000000 /&TIME_DIVIDER) iowait_total
|
|
, ROUND(SUM(ss.apwait_delta)/1000000 /&TIME_DIVIDER) apwait_total
|
|
, ROUND(SUM(ss.ccwait_delta)/1000000 /&TIME_DIVIDER) ccwait_total
|
|
, ROUND(SUM(ss.clwait_delta)/1000000 /&TIME_DIVIDER) clwait_total
|
|
, ROUND(SUM(ss.plsexec_time_delta)/1000000 /&TIME_DIVIDER) plsexec_time_total
|
|
, ROUND(SUM(ss.cpu_time_delta)/1000000 /&TIME_DIVIDER) cpu_time_total
|
|
, ROUND(SUM(ss.elapsed_time_delta)/1000000 /&TIME_DIVIDER) elapsed_time_total
|
|
, ROUND(SUM(ss.executions_delta) /&COUNT_SMALL_DIVIDER) executions_total
|
|
, ROUND(SUM(ss.rows_processed_delta) /&COUNT_DIVIDER) rows_processed_total
|
|
, ROUND(SUM(ss.sorts_delta) /&COUNT_SMALL_DIVIDER) sorts_total
|
|
, ROUND(SUM(ss.buffer_gets_delta) /&COUNT_DIVIDER) buffer_gets_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_interconnect_bytes_delta) /&BYTES_DIVIDER) io_interconnect_bytes_total
|
|
, ROUND(SUM(ss.physical_read_bytes_delta) /&BYTES_DIVIDER) physical_read_bytes_total
|
|
, ROUND(SUM(ss.physical_write_bytes_delta) /&BYTES_DIVIDER) physical_write_bytes_total
|
|
, ROUND(SUM(ss.direct_writes_delta * p.value) /&BYTES_DIVIDER) direct_write_bytes_total
|
|
, ROUND(SUM(ss.physical_write_bytes_delta
|
|
- (ss.direct_writes_delta * p.value)) /&BYTES_DIVIDER) physical_change_bytes_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.optimized_physical_reads_delta)/&BYTES_DIVIDER) optimized_physical_reads_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_offload_elig_bytes_delta) /&BYTES_DIVIDER) io_offload_elig_bytes_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_offload_return_bytes_delta) /&BYTES_DIVIDER) io_offload_return_bytes_total
|
|
from v$database d
|
|
, dba_hist_snapshot s
|
|
, dba_hist_sqlstat ss
|
|
, (select value from v$system_parameter where name = 'db_block_size') p
|
|
where d.dbid = s.dbid
|
|
AND ss.dbid = s.dbid
|
|
AND ss.instance_number = s.instance_number
|
|
AND ss.snap_id = s.snap_id
|
|
AND ss.instance_number LIKE '&&INST_ID'
|
|
AND s.end_interval_time BETWEEN TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS')
|
|
AND TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS')
|
|
and ss.sql_id not in
|
|
('d7y4tdacc7f3j','5ysyjtdsjwsr2','7akf6xvjp5a6d','3s2u15uzjum48','5ysyjtdsjwsr2','4sp1tg6fv1a9p'
|
|
,'bcv9qynmu1nv9','cm5vu20fhtnq1','7wv97t3szuvtd','ar9nmtmd28460','4m7m0t6fjcs5x','2skwhauh2cwky'
|
|
,'12kw3xcxv1qpg','9tgj4g8y4rwy8','d9g3t9zr4v2kb','2ywx7n1p7v2fy','grwydz59pu6mc','8r5um7rpdkdkd'
|
|
,'gx87sann8asuf','5wkhpq3k2ytq8','2jfqzrxhrm93b'
|
|
, '1awnrtw3c0v9g' -- Excluded on request of Kurt Vile (04-Jun-15)
|
|
, '8z8g8jz1dmrjs' -- Excluded on request of Paul Yip (04-Jun-15)
|
|
, '54xk7w1kf3prc' -- Excluded on request of Robert Lee (05-Aug-15)
|
|
)
|
|
and ss.parsing_user_id not in (
|
|
84 --svc_name
|
|
, 0 -- SYS
|
|
)
|
|
|
|
&&WHERECLAUSE
|
|
GROUP BY ss.dbid
|
|
, DECODE(ss.force_matching_signature,0,ss.sql_id,ss.force_matching_signature)
|
|
order by weight DESC NULLS LAST
|
|
)
|
|
WHERE ROWNUM <= &&TOP_RECORDCOUNT
|
|
)
|
|
SELECT /*+ */
|
|
--/*
|
|
( SELECT /*+ first_rows(ash) */
|
|
u.username
|
|
FROM dba_hist_active_sess_history ash
|
|
, dba_users u
|
|
, v$database d
|
|
WHERE d.dbid = ash.dbid
|
|
AND ash.user_id = u.user_id
|
|
AND ash.sql_id = ss.sql_id
|
|
and ash.sample_time between TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS')
|
|
and TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS')
|
|
and rownum = 1
|
|
) username
|
|
,
|
|
--*/
|
|
ss.*
|
|
from topsql ss
|
|
;
|
|
|
|
set timing off
|
|
|
|
@@footer
|