-
Notifications
You must be signed in to change notification settings - Fork 0
/
asqlmon.sql
109 lines (101 loc) · 4.04 KB
/
asqlmon.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
------------------------------------------------------------------------------------------------------------------------
--
-- File name: asqlmon.sql (v1.1)
--
-- Purpose: Report SQL-monitoring-style drill-down into where in an execution plan the execution time is spent
--
-- Author: Tanel Poder
--
-- Copyright: (c) http://blog.tanelpoder.com - All rights reserved.
--
-- Disclaimer: This script is provided "as is", no warranties nor guarantees are
-- made. Use at your own risk :)
--
-- Usage: @asqlmon <sqlid> <child#> sysdate-1/24 sysdate
--
-- Notes: This script runs on Oracle 11g+ and you should have the
-- Diagnostics and Tuning pack licenses for using it as it queries
-- some separately licensed views.
--
------------------------------------------------------------------------------------------------------------------------
SET LINESIZE 999 PAGESIZE 5000 TRIMOUT ON TRIMSPOOL ON
COL asqlmon_operation HEAD Plan_Operation FOR a70
COL asqlmon_predicates HEAD PREDICATES FOR a100 word_wrap
COL options FOR a30
COL asqlmon_plan_hash_value HEAD PLAN_HASH_VALUE
COL asqlmon_sql_id HEAD SQL_ID NOPRINT
COL asqlmon_sql_child HEAD CHILD# NOPRINT
COL asqlmon_sample_time HEAD SAMPLE_HOUR
COL projection FOR A520
COL pct_child HEAD "Activity %" FOR A8
COL pct_child_vis HEAD "Visual" FOR A12
COL asqlmon_id HEAD "ID" FOR 9999
COL asqlmon_parent_id HEAD "PID" FOR 9999
BREAK ON asqlmon_plan_hash_value SKIP 1 ON asqlmon_sql_id SKIP 1 ON asqlmon_sql_child SKIP 1 ON asqlmon_sample_time SKIP 1 DUP ON asqlmon_operation
PROMPT
PROMPT -- ASQLMon v1.1 - by Tanel Poder ( http://blog.tanelpoder.com ) - Display SQL execution plan line level activity breakdown from ASH
WITH sample_times AS (
select * from dual
),
sq AS (
SELECT
count(*) samples
, ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, NVL(ash.sql_plan_line_id,1) sql_plan_line_id -- this is because simple "planless" operations like single-row insert
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.event
-- , AVG(ash.p3) avg_p3 -- p3 is sometimes useful for listing block counts for IO wait events
FROM
v$active_session_history ash
WHERE
1=1
AND ash.sql_id LIKE '&1'
AND ash.sql_child_number LIKE '&2'
AND ash.sample_time BETWEEN &3 AND &4
GROUP BY
ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, NVL(ash.sql_plan_line_id,1)
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.event
)
SELECT
plan.sql_id asqlmon_sql_id
, plan.child_number asqlmon_sql_child
-- , plan.plan_hash_value asqlmon_plan_hash_value
, sq.samples seconds
, LPAD(TO_CHAR(ROUND(RATIO_TO_REPORT(sq.samples) OVER (PARTITION BY sq.sql_id, sq.sql_plan_hash_value) * 100, 1), 999.9)||' %',8) pct_child
, '|'||RPAD( NVL( LPAD('#', ROUND(RATIO_TO_REPORT(sq.samples) OVER (PARTITION BY sq.sql_id, sq.sql_plan_hash_value) * 10), '#'), ' '), 10,' ')||'|' pct_child_vis
--, LPAD(plan.id,4)||CASE WHEN parent_id IS NULL THEN ' ' ELSE ' <- ' END||LPAD(plan.parent_id,4) asqlmon_plan_id
, plan.id asqlmon_id
, plan.parent_id asqlmon_parent_id
, LPAD(' ', depth, ' ') || plan.operation ||' '|| plan.options || NVL2(plan.object_name, ' ['||plan.object_name ||']', null) asqlmon_operation
, sq.session_state
, sq.event
-- , sq.avg_p3
, plan.object_alias || CASE WHEN plan.qblock_name IS NOT NULL THEN ' ['|| plan.qblock_name || ']' END obj_alias_qbc_name
, CASE WHEN plan.access_predicates IS NOT NULL THEN '[A:] '|| plan.access_predicates END || CASE WHEN plan.filter_predicates IS NOT NULL THEN ' [F:]' || plan.filter_predicates END asqlmon_predicates
-- , plan.projection
FROM
v$sql_plan plan
, sq
WHERE
1=1
AND sq.sql_id(+) = plan.sql_id
AND sq.sql_child_number(+) = plan.child_number
AND sq.sql_plan_line_id(+) = plan.id
AND sq.sql_plan_hash_value(+) = plan.plan_hash_value
AND plan.sql_id LIKE '&1'
AND plan.child_number LIKE '&2'
ORDER BY
plan.child_number
, plan.plan_hash_value
, plan.id
/