64 lines
2.1 KiB
SQL
64 lines
2.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top SQLs from AWR - by Buffer Gets Total
|
|
* 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
|
|
* --------- ------------ -----------------------------------------
|
|
* 15-May-12 Vishal Gupta Intial version
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* 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 "_FROM_TIMESTAMP" new_value FROM_TIMESTAMP NOPRINT
|
|
COLUMN "_TO_TIMESTAMP" new_value TO_TIMESTAMP NOPRINT
|
|
COLUMN "_INST_ID" new_value INST_ID NOPRINT
|
|
COLUMN "_TOP_RECORDCOUNT" new_value TOP_RECORDCOUNT NOPRINT
|
|
COLUMN "_WHERECLAUSE" new_value WHERECLAUSE NOPRINT
|
|
|
|
|
|
set term off
|
|
SELECT DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
, DECODE('&&FROM_TIMESTAMP'
|
|
,'',TO_CHAR(sysdate - (DECODE('&&FROM_TIMESTAMP','','24','&&FROM_TIMESTAMP')/24) , 'YYYY-MM-DD HH24:MI:SS')
|
|
,'&&FROM_TIMESTAMP') "_FROM_TIMESTAMP"
|
|
, DECODE('&&TO_TIMESTAMP'
|
|
,'',TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS')
|
|
,'&&TO_TIMESTAMP') "_TO_TIMESTAMP"
|
|
, DECODE('&&TOP_RECORDCOUNT','','10','&&TOP_RECORDCOUNT') "_TOP_RECORDCOUNT"
|
|
FROM DUAL
|
|
;
|
|
set term on
|
|
|
|
|
|
@@awr_top_sql "&&INST_ID" "&FROM_TIMESTAMP" "&TO_TIMESTAMP" "&TOP_RECORDCOUNT" "physical_write_bytes_total" "&&WHERECLAUSE"
|
|
|
|
@@footer
|