@@header /* * * Author : Vishal Gupta * Purpose : Display SQLs above certain thresholds from ASH repository * Compatibility : 11.1 and above * Parameters : 1 - Number of hours (Default 1) * 2 - Physical IO in GBs (Default 100) * 3 - Physical Read in GBs (Default 0) * 4 - Physical Write in GBs (Default 0) * 5 - Interconnect IO in GBs (Default 0) * 6 - WhereClause * 7 - WhereClause2 * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 21-JAN-14 Vishal Gupta Added SQL_ID and whereclause as input parmaeter * 28-JUN-12 Vishal Gupta Created * */ /************************************ * INPUT PARAMETERS ************************************/ UNDEFINE HOURS UNDEFINE PHYIO UNDEFINE PHYREAD UNDEFINE PHYWRITE UNDEFINE INTERCONNECT_IO UNDEFINE WHERECLAUSE UNDEFINE WHERECLAUSE2 UNDEFINE INST_ID DEFINE HOURS="&&1" DEFINE PHYIO="&&2" DEFINE PHYREAD="&&3" DEFINE PHYWRITE="&&4" DEFINE INTERCONNECT_IO="&&5" DEFINE WHERECLAUSE="&&6" DEFINE WHERECLAUSE2="&&7" DEFINE INST_ID="" COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT COLUMN _HOURS NEW_VALUE HOURS NOPRINT COLUMN _PHYIO NEW_VALUE PHYIO NOPRINT COLUMN _PHYREAD NEW_VALUE PHYREAD NOPRINT COLUMN _PHYWRITE NEW_VALUE PHYWRITE NOPRINT COLUMN _INTERCONNECT_IO NEW_VALUE INTERCONNECT_IO NOPRINT set term off SELECT DECODE('&&HOURS','','1','%','1','&&HOURS') "_HOURS" , DECODE('&&PHYIO','','100','%','100','&&PHYIO') "_PHYIO" , DECODE('&&PHYREAD','','0','%','0','&&PHYREAD') "_PHYREAD" , DECODE('&&PHYWRITE','','0','%','0','&&PHYWRITE') "_PHYWRITE" , DECODE('&&INTERCONNECT_IO','','0','%','0','&&INTERCONNECT_IO') "_INTERCONNECT_IO" FROM DUAL ; set term on /***********************************/ PROMPT *************************************************** PROMPT * ASH SQL Ids PROMPT * PROMPT * Input Parameter: PROMPT * Number of Hours = "&&HOURS" PROMPT * Physical IO (GB) = "&&PHYIO" PROMPT * Physical Read (GB) = "&&PHYREAD" PROMPT * Physical Write (GB) = "&&PHYWRITE" PROMPT * Interconnect IO (GB) = "&&INTERCONNECT_IO" PROMPT * Where Clause = "&&WHERECLAUSE" PROMPT * Where Clause2 = "&&WHERECLAUSE2" PROMPT *************************************************** COLUMN session_id HEADING "SID" FORMAT 99999 COLUMN instance_number HEADING "I#" FORMAT 99 COLUMN inst_id HEADING "I#" FORMAT 99 COLUMN "session_serial#" HEADING "Serial#" FORMAT 999999 COLUMN FORCE_MATCHING_SIGNATURE HEADING "Force|Matching|Signature" FORMAT 99999999999999999999999 COLUMN sql_plan_hash_value HEADING "Plan|Hash|Value" FORMAT 9999999999 COLUMN sql_exec_start FORMAT a18 COLUMN sql_exec_end FORMAT a18 COLUMN duration FORMAT a12 COLUMN sql_opname HEADING "SQL|Operation" FORMAT a10 TRUNCATE COLUMN sql_child_number HEADING "SQL|Ch#" FORMAT 999 COLUMN current_dop HEADING "DOP" FORMAT 999 COLUMN phyread HEADING "Phy|Read|(GB)" FORMAT 9999 COLUMN phywrite HEADING "Phy|Write|(GB)" FORMAT 9999 COLUMN interconnect_io HEADING "Inter|Connect|IO|(GB)" FORMAT 9999 COLUMN pga_allocated HEADING "PGA|(GB)" FORMAT 999.00 COLUMN temp_space_allocated HEADING "Temp|Space|(GB)" FORMAT 9999.00 -- Get the SQL Statements from ASH SELECT /*+ parallel(ash, 10) LEADING(ash) */ NVL(ash.qc_session_id,ash.session_id) session_id , NVL(ash.qc_instance_id,ash.inst_id) inst_id , NVL(ash.qc_session_serial#,ash.session_serial#) session_serial# , TO_CHAR(NVL(ash.sql_exec_start,MIN(ash.sample_time)),'DD-MON-YY HH24:MI:SS') sql_exec_start , TO_CHAR(max(ash.sample_time) ,'DD-MON-YY HH24:MI:SS') sql_exec_end , SUBSTR(REPLACE(max(ash.sample_time) - NVL(ash.sql_exec_start,MIN(ash.sample_time)),'+00000000','+') ,1,INSTR(REPLACE(max(ash.sample_time) - NVL(ash.sql_exec_start,MIN(ash.sample_time)),'+00000000','+'),'.')-1 ) duration &&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname --, ash.sql_exec_id , ash.sql_id , ash.sql_child_number , ash.sql_plan_hash_value &&_IF_ORA_11202_OR_HIGHER , max(trunc(ash.px_flags / 2097152)) current_dop , ash.force_matching_signature , NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id) top_level_sql_id &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_read_io_bytes)/power(1024,3)) phyread &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_write_io_bytes)/power(1024,3)) phywrite &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_interconnect_io_bytes)/power(1024,3)) interconnect_io &&_IF_ORA_11gR1_OR_HIGHER , ROUND(MAX(ash.pga_allocated)/power(1024,3),2) pga_allocated &&_IF_ORA_11gR1_OR_HIGHER , ROUND(MAX(ash.temp_space_allocated)/power(1024,3),2) temp_space_allocated FROM gv$active_session_history ash LEFT OUTER JOIN gv$active_session_history ash_parent ON ash_parent.inst_id = ash.qc_instance_id AND ash_parent.session_id = ash.qc_session_id AND ash_parent.session_serial# = ash.qc_session_serial# AND CAST(ash_parent.sample_time as DATE) = ash.sql_exec_start AND ash_parent.sample_time > sysdate - (&&HOURS/24) WHERE ash.sql_id IS NOT NULL AND ash.sample_time > sysdate - (&&HOURS/24) AND ash.sql_exec_id IS NOT NULL &&WHERECLAUSE &&WHERECLAUSE2 GROUP BY NVL(ash.qc_session_id,ash.session_id) , NVL(ash.qc_instance_id,ash.inst_id) , NVL(ash.qc_session_serial#,ash.session_serial#) , ash.sql_exec_id , ash.sql_exec_start , ash.sql_id , ash.sql_child_number , ash.sql_plan_hash_value , ash.force_matching_signature &&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname , NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id) having (SUM(ash.delta_read_io_bytes)+SUM(ash.delta_write_io_bytes) ) / power(1024,3) >= &&PHYIO AND (SUM(ash.delta_read_io_bytes) ) / power(1024,3) >= &&PHYREAD AND (SUM(ash.delta_write_io_bytes) ) / power(1024,3) >= &&PHYWRITE AND (SUM(ash.delta_interconnect_io_bytes) ) / power(1024,3) >= &&INTERCONNECT_IO ORDER BY --max(ash.sample_time) asc --, NVL(ash.sql_exec_start,MIN(ash.sample_time)) ASC , max(ash.sample_time) asc ; @@footer