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