forked from abdulirfan3/Oracle_SQL_Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathash.sql
More file actions
121 lines (103 loc) · 3.42 KB
/
ash.sql
File metadata and controls
121 lines (103 loc) · 3.42 KB
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
120
121
/* version 1
prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
prompt RR columns are ratio to report
SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;
select * from (
select NVL(event,'CPU') event,count(*),
round((ratio_to_report(sum(1)) over ()*100),1) rr
from gv$active_session_history
WHERE user_id<>0
AND sample_time<trunc(SYSDATE+1) AND sample_time>trunc(sysdate-1)
group by event
order by 2 desc
) where rownum<10;
prompt ************************************
prompt **** ASH I/O by SQL_ID
prompt ************************************
prompt RR columns are ratio to report
COLUMN force_matching_signature FOR 999999999999999999999999999
select * from (
select
sql_id ,sql_plan_hash_value,force_matching_signature,
NVL(event,'CPU') Event,
count(*),
round((ratio_to_report(sum(1)) over ()*100),1) rr
from gv$active_session_history
where
1=1
AND wait_class LIKE '%I/O'
--AND event IS null
and user_id<>0
AND sql_id IS NOT NULL
group by
sql_id,sql_plan_hash_value,event,force_matching_signature
order by 5 desc
) where rownum<30;
Prompt
prompt Run sql_wait_on_event_ash script to find SQLID for a paticular wait event
prompt
*/
-- version 2, in use
prompt
accept how_many_min_back prompt "How many minutes do you want to go back? "
prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
prompt RR columns are ratio to report
SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;
select * from (
select NVL(event,'CPU') event,count(*),
round((ratio_to_report(sum(1)) over ()*100),1) rr
from gv$active_session_history
WHERE user_id<>0
AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))
group by event
order by 2 desc
) where rownum<10;
prompt ************************************
prompt **** ASH I/O by SQL_ID, Top 10
prompt ************************************
prompt RR columns are ratio to report
COLUMN force_matching_signature FOR 999999999999999999999999999
select * from (
select
sql_id ,sql_plan_hash_value,force_matching_signature,
NVL(event,'CPU') Event,
count(*),
round((ratio_to_report(sum(1)) over ()*100),1) rr
from gv$active_session_history
where
1=1
AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))
AND wait_class LIKE '%I/O'
--AND event IS null
and user_id<>0
AND sql_id IS NOT NULL
group by
sql_id,sql_plan_hash_value,event,force_matching_signature
order by 5 desc
) where rownum<11;
prompt ************************************
prompt **** ASH DB_TIME by SQL_ID, Top 10
prompt ************************************
select * from (
select sql_id
, count(*) DBTime
, round(count(*)*100/sum(count(*))
over (), 2) pctload
from v$active_session_history
where sample_time > sysdate - ('&how_many_min_back'/(24*60))
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc
) where rownum < 11;
prompt ************************************
prompt **** Wait class breakdown by sqlid/module
prompt **** Can change grouping
prompt ************************************
@ash_top sql_id session_type='FOREGROUND' sysdate-('&how_many_min_back'/(24*60)) sysdate
Prompt
prompt Run sql_wait_on_event_ash script to find SQLID for a paticular wait event
prompt