-
Notifications
You must be signed in to change notification settings - Fork 62
/
ash_top_procedure.sql
57 lines (49 loc) · 1.99 KB
/
ash_top_procedure.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
/*
only 10.2.0.3 and above
COUNT(*) SQL_ID calling_code
--------- ------------- --------------------------------------------------------------------
2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_RANDOM.VALUE
2 07p193phmhx3z ORDERENTRY.BROWSEPRODUCTS => DBMS_APPLICATION_INFO.SET_ACTION
2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_LOCK.SLEEP
3 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_APPLICATION_INFO.SET_ACTION
13 1xxksrhwtz3zf ORDERENTRY.NEWORDER
16 0bzhqhhj9mpaa ORDERENTRY.NEWCUSTOMER
45 41zu158rqf4kf ORDERENTRY.BROWSEANDUPDATEORDERS
70 0yas01u2p9ch4 ORDERENTRY.NEWORDER
76 dw2zgaapax1sg ORDERENTRY.NEWORDER
82 05s4vdwsf5802 ORDERENTRY.BROWSEANDUPDATEORDERS
111 75621g9y3xmvd ORDERENTRY.NEWORDER
120 75621g9y3xmvd ORDERENTRY.BROWSEPRODUCTS
131 75621g9y3xmvd ORDERENTRY.BROWSEANDUPDATEORDERS
163 0uuqgjq7k12nf ORDERENTRY.NEWORDER
*/
set linesize 120
col entry_package for a25
col entry_procedure for a25
col cur_package for a25
col cur_procedure for a25
col calling_code for a70
select
count(*),
sql_id,
procs1.object_name || decode(procs1.procedure_name,'','','.')||
procs1.procedure_name ||' '||
decode(procs2.object_name,procs1.object_name,'',
decode(procs2.object_name,'','',' => '||procs2.object_name))
||
decode(procs2.procedure_name,procs1.procedure_name,'',
decode(procs2.procedure_name,'','',null,'','.')||procs2.procedure_name)
"calling_code"
from v$active_session_history ash,
all_procedures procs1,
all_procedures procs2
where
ash.PLSQL_ENTRY_OBJECT_ID = procs1.object_id (+)
and ash.PLSQL_ENTRY_SUBPROGRAM_ID = procs1.SUBPROGRAM_ID (+)
and ash.PLSQL_OBJECT_ID = procs2.object_id (+)
and ash.PLSQL_SUBPROGRAM_ID = procs2.SUBPROGRAM_ID (+)
and ash.sample_time > sysdate - &minutes/(60*24)
group by procs1.object_name, procs1.procedure_name,
procs2.object_name, procs2.procedure_name,sql_id
order by count(*)
/