87 lines
3.8 KiB
SQL
87 lines
3.8 KiB
SQL
set echo off
|
|
set feed off
|
|
set verify off
|
|
-- No header and footer needed, as its just a wrapper script giving call to other scripts
|
|
-- @@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top SQLs from AWR - by CPU Time
|
|
* Version : "&&RECORDCOUNT".1 and above
|
|
* Parameters : 1 - hours (If from and to timestamp are passed , this is ignored)
|
|
* 2 - Instance Number (Default is '%' ie ALL)
|
|
* 3 - From Timestamp (YYYY-MM-DD HH24:MI:SS)
|
|
* 4 - To Timestamp (YYYY-MM-DD HH24:MI:SS)
|
|
* 5 - Top record Count
|
|
* 6 - Where Clause
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 15-May-12 Vishal Gupta Intial version
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
UNDEFINE HOURS
|
|
UNDEFINE INST_ID
|
|
UNDEFINE RECORDCOUNT
|
|
UNDEFINE FROM_TIME
|
|
UNDEFINE TO_TIME
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE HOURS="&&1"
|
|
DEFINE INST_ID="&&2"
|
|
DEFINE FROM_TIME="&&3"
|
|
DEFINE TO_TIME="&&4"
|
|
DEFINE RECORDCOUNT="&&5"
|
|
DEFINE WHERECLAUSE="&&6"
|
|
|
|
COLUMN "_from_time" new_value from_time NOPRINT
|
|
COLUMN "_to_time" new_value to_time NOPRINT
|
|
COLUMN "_INST_ID" new_value INST_ID NOPRINT
|
|
COLUMN "_RECORDCOUNT" new_value recordcount NOPRINT
|
|
COLUMN "_hours" new_value hours NOPRINT
|
|
|
|
|
|
set term off
|
|
SELECT DECODE('&&HOURS','','24','&&HOURS') "_hours"
|
|
, DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
, DECODE('&&RECORDCOUNT','','10','&&RECORDCOUNT') "_RECORDCOUNT"
|
|
, DECODE('&&FROM_TIME'
|
|
,'',TO_CHAR(sysdate - (DECODE('&&HOURS','','24','&&HOURS')/24) , 'YYYY-MM-DD HH24:MI:SS')
|
|
,'&&FROM_TIME') "_from_time"
|
|
, DECODE('&&TO_TIME'
|
|
,'',TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS')
|
|
,'&&TO_TIME') "_to_time"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
--@awr_top_sql_by_cpu "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "&&WHERECLAUSE"
|
|
--@awr_top_sql_by_exec "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "&&WHERECLAUSE"
|
|
--@awr_top_sql_by_elapsed_time "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "&&WHERECLAUSE"
|
|
--@awr_top_sql_by_physicalread "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "&&WHERECLAUSE"
|
|
--@awr_top_sql_by_physicalwrite "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "&&WHERECLAUSE"
|
|
--@awr_top_sql_by_buffer_gets "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "&&WHERECLAUSE"
|
|
--@awr_top_sql_by_buffer_gets "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "&&WHERECLAUSE"
|
|
|
|
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "cpu_time_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "elapsed_time_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "executions_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "physical_read_requests_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "physical_write_requests_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "physical_read_bytes_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "physical_write_bytes_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "physical_change_bytes_total" "&&WHERECLAUSE"
|
|
@@awr_top_sql "&&INST_ID" "&&from_time" "&&to_time" "&&RECORDCOUNT" "buffer_gets_total" "&&WHERECLAUSE"
|
|
|
|
|
|
-- @@footer |