126 lines
3.6 KiB
MySQL
126 lines
3.6 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display SQL ID Heirarchy from AWR
|
|
* Version : 10.1 and above
|
|
* Parameters : 1 - SQL_ID
|
|
* 2 - Number of Days (Default 7 days)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 07-Mar-13 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE sql_id
|
|
UNDEFINE days
|
|
UNDEFINE whereclause
|
|
|
|
DEFINE sql_id="&&1"
|
|
DEFINE days="&&2"
|
|
DEFINE whereclause="&&3"
|
|
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&plan_hash_value','','%','&&plan_hash_value') "_plan_hash_value"
|
|
, DECODE('&&days','','7','&&days') "_days"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
|
|
Prompt
|
|
Prompt **************************************************************
|
|
Prompt * AWR SQL Execution Hierarchy
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - SQL Id = '&&sql_id'
|
|
PROMPT * - Days = '&&days'
|
|
PROMPT * - WhereClause = '&&whereclause'
|
|
Prompt **************************************************************
|
|
|
|
/*
|
|
|
|
COLUMN sample_time HEADING "SampleTime" FORMAT a18
|
|
COLUMN SQL_ID HEADING "SQLId" FORMAT a50
|
|
COLUMN sql_opname HEADING "SQL Opname" FORMAT a10
|
|
|
|
|
|
SELECT --+ parallel(ash 10)
|
|
TO_CHAR(ash.sample_time,'DD-MON-YY HH24:MI:SS') sample_time
|
|
, LPAD(' ',level*4,'`---') || ash.sql_id sql_id
|
|
, ash.sql_opname
|
|
FROM dba_hist_active_sess_history ash
|
|
WHERE ash.sample_time > sysdate - '&&days'
|
|
&&whereclause
|
|
START WITH ash.top_level_sql_id = '&&sql_id'
|
|
CONNECT BY PRIOR ash.top_level_sql_id = ash.sql_id
|
|
ORDER BY ash.sample_time
|
|
;
|
|
*/
|
|
|
|
|
|
COLUMN FORCE_MATCHING_SIGNATURE FORMAT 99999999999999999999999
|
|
COLUMN sql_exec_start FORMAT a20
|
|
COLUMN sql_exec_end FORMAT a27
|
|
COLUMN duration FORMAT a23
|
|
COLUMN sql_opname FORMAT a20
|
|
|
|
SELECT DISTINCT
|
|
ash.sql_opname
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.FORCE_MATCHING_SIGNATURE
|
|
, ash.top_level_sql_id
|
|
FROM dba_hist_active_sess_history ash
|
|
, v$database d
|
|
WHERE ash.dbid = d.dbid
|
|
AND ash.top_level_sql_id = '&&sql_id'
|
|
AND ash.sample_time > sysdate - '&&days'
|
|
;
|
|
|
|
|
|
|
|
SELECT --ash.sql_exec_id,
|
|
TO_CHAR(NVL(ash.sql_exec_start,MIN(ash.sample_time)),'DD-MON-YY HH24:MI:SS') sql_exec_start
|
|
, max(ash.sample_time) sql_exec_end
|
|
, max(ash.sample_time) - NVL(ash.sql_exec_start,MIN(ash.sample_time)) duration
|
|
, ash.sql_opname
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.FORCE_MATCHING_SIGNATURE
|
|
, ash.top_level_sql_id
|
|
FROM dba_hist_active_sess_history ash
|
|
, v$database d
|
|
WHERE ash.dbid = d.dbid
|
|
AND ash.top_level_sql_id = '&&sql_id'
|
|
AND ash.sample_time > sysdate - '&&days'
|
|
GROUP BY ash.FORCE_MATCHING_SIGNATURE
|
|
, ash.sql_exec_id
|
|
, ash.sql_exec_start
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.sql_opname
|
|
, ash.top_level_sql_id
|
|
ORDER BY max(ash.sample_time) asc
|
|
, NVL(ash.sql_exec_start,MIN(ash.sample_time)) ASC
|
|
;
|
|
|
|
|
|
|
|
|
|
@@footer
|