@@header /* * * Author : Vishal Gupta * Purpose : Display Session's SQL IDs from AWR * Compatibility : 10.1 and above * Parameters : 1 - SID * 2 - INST_ID (optional, default to 1) * 3 - Serial# (Default %) * 4 - Number of hours (Default 24) * 5 - SQL_ID (Default %) * 6 - WhereClause * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 31-DEC-15 Vishal Gupta Change logic of query to start with parent session to optimize performance * 28-JAN-14 Vishal Gupta Added grand parent logic * 21-JAN-14 Vishal Gupta Added SQL_ID and whereclause as input parameter * 28-Jun-12 Vishal Gupta Created * */ /************************************ * INPUT PARAMETERS ************************************/ UNDEFINE SID UNDEFINE INST_ID UNDEFINE SERIAL UNDEFINE HOURS UNDEFINE SQL_ID UNDEFINE WHERECLAUSE UNDEFINE WHERECLAUSE2 UNDEFINE HAVINGCLAUSE DEFINE SID="&&1" DEFINE INST_ID="&&2" DEFINE SERIAL="&&3" DEFINE HOURS="&&4" DEFINE SQL_ID="&&5" DEFINE WHERECLAUSE="&&6" DEFINE WHERECLAUSE2="&&7" DEFINE HAVINGCLAUSE="&&8" COLUMN _SID NEW_VALUE SID NOPRINT COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT COLUMN _SERIAL NEW_VALUE SERIAL NOPRINT COLUMN _HOURS NEW_VALUE HOURS NOPRINT COLUMN _SQL_ID NEW_VALUE SQL_ID NOPRINT set term off SELECT DECODE(UPPER('&&SID'),'','%',UPPER('&&SID')) "_SID" , DECODE(UPPER('&&INST_ID'),'','1',UPPER('&&INST_ID')) "_INST_ID" , DECODE(UPPER('&&SERIAL'),'','%',UPPER('&&SERIAL')) "_SERIAL" , DECODE(UPPER('&&HOURS'),'','24',UPPER('&&HOURS')) "_HOURS" , DECODE('&&SQL_ID','','%','&&SQL_ID') "_SQL_ID" FROM DUAL; set term on /***********************************/ PROMPT *************************************************** PROMPT * AWR - ASH SQL Ids PROMPT * PROMPT * Input Parameter: PROMPT * SID = "&&SID" PROMPT * Instance ID = "&&INST_ID" PROMPT * Serial# = "&&SERIAL" PROMPT * Number of Hours = "&&HOURS" PROMPT * SQL Id = "&&SQL_ID" PROMPT * Where Clause = "&&WHERECLAUSE" PROMPT * Where Clause2 = "&&WHERECLAUSE2" PROMPT * Having Clause = "&&HAVINGCLAUSE" 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 HEADING "Duration|+D HH:MM:SS" 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_requests HEADING "Phy|Read|Reqs|(#1000)" FORMAT 999999 COLUMN phywrite_requests HEADING "Phy|Write|Reqs|(#1000)" FORMAT 999999 COLUMN phyread HEADING "Phy|Read|(GB)" FORMAT 99999 COLUMN phywrite HEADING "Phy|Write|(GB)" FORMAT 99999 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,8) parallel(ash_child,8) */ decode(ash_child.qc_session_id, 0, ash_child.session_id, ash.session_id) session_id , decode(ash_child.qc_session_id, 0, ash_child.instance_number, ash.instance_number) instance_number , decode(ash_child.qc_session_id, 0, ash_child.session_serial#, ash.session_serial#) session_serial# &&_IF_ORA_11gR1_OR_HIGHER , TO_CHAR(NVL(ash.sql_exec_start,MIN(NVL(ash_child.sample_time,ash.sample_time))),'DD-MON-YY HH24:MI:SS') sql_exec_start , TO_CHAR(max(NVL(ash_child.sample_time,ash.sample_time)) ,'DD-MON-YY HH24:MI:SS') sql_exec_end , SUBSTR(REPLACE( max(NVL(ash_child.sample_time,ash.sample_time)) - NVL(ash.sql_exec_start,MIN(NVL(ash_child.sample_time,ash.sample_time))) ,'+00000000','+') ,1,INSTR(REPLACE( max(NVL(ash_child.sample_time,ash.sample_time)) - NVL(ash.sql_exec_start,MIN(NVL(ash_child.sample_time,ash.sample_time))),'+00000000','+'),'.')-1 ) duration --&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname , ash.sql_id , ash.sql_child_number , ash.sql_plan_hash_value &&_IF_ORA_11202_OR_HIGHER , max(trunc(NVL(ash_child.px_flags,ash.px_flags) / 2097152)) current_dop , ash.force_matching_signature &&_IF_ORA_11gR1_OR_HIGHER , NVL(ash.top_level_sql_id,ash_child.top_level_sql_id) top_level_sql_id &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash.delta_read_io_requests,0) + NVL(ash_child.delta_read_io_requests,0) )/power(1000,1)) phyread_requests &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash.delta_write_io_requests,0) + NVL(ash_child.delta_write_io_requests,0) )/power(1000,1)) phywrite_requests &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash.delta_read_io_bytes,0) + NVL(ash_child.delta_read_io_bytes,0) )/power(1024,3)) phyread &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash.delta_write_io_bytes,0) + NVL(ash_child.delta_write_io_bytes,0) )/power(1024,3)) phywrite &&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash.delta_interconnect_io_bytes,0) + NVL(ash_child.delta_interconnect_io_bytes,0) )/power(1024,3)) interconnect_io &&_IF_ORA_11gR1_OR_HIGHER , ROUND(MAX(NVL(ash.pga_allocated,0) + NVL(ash_child.pga_allocated,0) )/power(1024,3),2) pga_allocated &&_IF_ORA_11gR1_OR_HIGHER , ROUND(MAX(NVL(ash.temp_space_allocated,0) + NVL(ash_child.temp_space_allocated,0) )/power(1024,3),2) temp_space_allocated FROM v$database d JOIN sys.wrh$_active_session_history ash ON ash.dbid = d.dbid AND ash.instance_number LIKE '&&INST_ID' AND ash.session_id LIKE '&&SID' AND ash.session_serial# LIKE '&&SERIAL' AND ash.sql_id LIKE '&&SQL_ID' AND ash.sample_time > sysdate - (&&HOURS/24) &&WHERECLAUSE LEFT OUTER JOIN sys.wrh$_active_session_history ash_child ON ash_child.dbid = ash.dbid AND ash_child.qc_instance_id = ash.instance_number AND ash_child.qc_session_id = ash.session_id AND ash_child.qc_session_serial# = ash.session_serial# AND ash_child.qc_session_id <> 0 &&_IF_ORA_11gR1_OR_HIGHER AND ash_child.sql_exec_start = ash.sql_exec_start AND ash_child.sample_time > sysdate - (&&HOURS/24) AND ash_child.qc_instance_id LIKE '&&INST_ID' AND ash_child.qc_session_id LIKE '&&SID' AND ash_child.qc_session_serial# LIKE '&&SERIAL' AND ash_child.sql_id LIKE '&&SQL_ID' &&WHERECLAUSE2 GROUP BY decode(ash_child.qc_session_id, 0, ash_child.session_id, ash.session_id) , decode(ash_child.qc_session_id, 0, ash_child.instance_number, ash.instance_number) , decode(ash_child.qc_session_id, 0, ash_child.session_serial#, ash.session_serial#) &&_IF_ORA_11gR1_OR_HIGHER , ash.sql_exec_id &&_IF_ORA_11gR1_OR_HIGHER , ash.sql_exec_start --&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname , ash.sql_id , ash.sql_child_number , ash.sql_plan_hash_value , ash.force_matching_signature &&_IF_ORA_11gR1_OR_HIGHER , NVL(ash.top_level_sql_id,ash_child.top_level_sql_id) &&HAVINGCLAUSE --having (SUM(ash.delta_read_io_bytes)+SUM(ash.delta_interconnect_io_bytes) ) / power(1024,3) > 20 -- Physical Read + Write GBs --having (SUM(ash.delta_read_io_bytes) ) / power(1024,3) > 100 -- Physical Write GBs --having (SUM(ash.delta_interconnect_io_bytes) ) / power(1024,3) > 100 -- Interconnect IO GBs --having (SUM(ash.pga_allocated) ) / power(1024,3) > 2 -- PGA GBs --having (SUM(ash.temp_space_allocated) ) / power(1024,3) > 10 -- Temp Space GBs ORDER BY max(ash.sample_time) asc &&_IF_ORA_11gR1_OR_HIGHER, NVL(ash.sql_exec_start,MIN(NVL(ash_child.sample_time,ash.sample_time))) ASC ; @@footer