support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/vg/awr_sid_sqlids.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

171 lines
9.9 KiB
SQL
Raw Permalink Blame History

@@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 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,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
Reference in New Issue View Git Blame Copy Permalink