-
Notifications
You must be signed in to change notification settings - Fork 35
/
ash.sql
119 lines (116 loc) · 4.9 KB
/
ash.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
110
111
112
113
114
115
116
117
118
119
prompt ===========================================================
prompt &_C_REVERSE *** ASH by last N minutes by sid &_C_RESET
prompt * Usage @ash sid/username/* N [serial]
prompt ===========================================================
@inc/input_vars_init;
@inc/main_with_params_only;
col min_s_time format a25 ;
col max_s_time format a25 ;
col username format a25 ;
col blocker format 99999 ;
col text format a60 ;
col event format a25 ;
col wait_class format a25 ;
col ple format a20 ;
col plo format a20 ;
col curobj format a30 ;
col program format a15 ;
col module format a15 ;
col action format a15 ;
col sql_id format a13 ;
with ash_pre as (
select
h.SAMPLE_TIME
,h.session_id
,h.session_serial#
,h.blocking_session
,h.user_id
,h.sql_id
,count(*) over(partition by h.session_id,h.session_serial#,h.sql_id) as sqlid_count
&&_IF_ORA112_OR_HIGHER ,count(*) over(partition by h.sql_exec_id) as sql_exec_count
,h.plsql_entry_object_id ple
,h.plsql_object_id plo
,h.event
,h.p1,h.p1text
,h.p2,h.p2text
,h.p3,h.p3text
,h.wait_class
,h.current_obj#
,h.module
,substr(h.program,1,15) program
,h.action
&&_IF_ORA112_OR_HIGHER ,h.TOP_LEVEL_SQL_ID
--,h.*
from gv$active_session_history h
where h.sample_time > sysdate-&2/24/60
and (h.session_id = decode(translate('&1','x0132456789','x'),null,to_number('&1'))
or
h.user_id in (select/*+ precompute_subquery */ u.user_id
from dba_users u
where lower(u.username) like decode(translate('&1','x0132456789','x'),null,null,lower('%&1%'))
)
or '&1'='*'
)
and ('&3' is null or h.session_serial# = to_number('&3'))
)
select
min(h.SAMPLE_TIME) min_s_time
,max(h.SAMPLE_TIME) max_s_time
,h.blocking_session blocker
,h.user_id
,h.session_id
,h.session_serial#
,(select username from dba_users u where u.user_id=h.user_id) username
,h.sql_id
,sum(sum(sqlid_count)) over(partition by h.sql_id) as sqlid_count
&&_IF_ORA112_OR_HIGHER ,max(sql_exec_count) as max_sql_exec_count
,substr(
coalesce(
(select a.sql_text from v$sqlarea a where a.sql_id=h.sql_id and rownum=1)
,(select substr(to_char(a.sql_text),1,4000) from dba_hist_sqltext a where a.sql_id=h.sql_id and rownum=1 and dbid=(select dbid from v$database))
)
,1,100)
as text
,(select object_name from dba_objects o where o.object_id=h.ple) ple
,(select object_name from dba_objects o where o.object_id=h.plo) plo
,h.event
,h.wait_class
,h.current_obj#
,(select object_name from dba_objects o where o.object_id=h.current_obj#) curobj
,h.module
,h.program
,h.action
&&_IF_ORA112_OR_HIGHER ,h.TOP_LEVEL_SQL_ID
from ash_pre h
group by
h.blocking_session
,h.user_id
,h.session_id
,h.session_serial#
,h.sql_id
,h.ple
,h.plo
,h.event
,h.wait_class
,h.current_obj#
,h.module
,h.program
,h.action
&&_IF_ORA112_OR_HIGHER ,h.TOP_LEVEL_SQL_ID
order by sqlid_count desc
&&_IF_ORA112_OR_HIGHER ,max_sql_exec_count desc
/
col text clear;
col event clear;
col wait_class clear;
col ple clear;
col plo clear;
col curobj clear;
col program clear;
col module clear;
col action clear;
col min_s_time clear;
col max_s_time clear;
col username clear;
/* end main */
@inc/input_vars_undef;