-
Notifications
You must be signed in to change notification settings - Fork 9
/
Copy pathrun_awr_planx.sql
174 lines (162 loc) · 7.01 KB
/
run_awr_planx.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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
define SQL_TOP_N = 2
set head off verify off feed off
COL name NEW_V _instname NOPRINT
select lower(instance_name) name from v$instance;
COL ecr_dbid NEW_V ecr_dbid;
SELECT 'get_dbid', TO_CHAR(dbid) ecr_dbid FROM v$database;
COL current_time NEW_V current_time FOR A15;
SELECT 'current_time: ' x, TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') current_time FROM DUAL;
spool awr_planx-&_instname-¤t_time..sql.reference.sql
select distinct '@planx Y ' || sql_id || ',' || sqldetail || ',' || parsing_schema_name
from
(
select force_matching_signature, max(sql_id) keep (dense_rank first order by elap_rank asc) sql_id , sqldetail, parsing_schema_name
from
(
SELECT * FROM(
SELECT s.sql_id, s.force_matching_signature, t.sqldetail ,s.PARSING_SCHEMA_NAME ,
DENSE_RANK() OVER
(ORDER BY sum(EXECUTIONS_DELTA) DESC ) exec_rank,
DENSE_RANK() OVER
(ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
DENSE_RANK() OVER
(ORDER BY sum(BUFFER_GETS_DELTA) DESC ) log_reads_rank,
DENSE_RANK() OVER
(ORDER BY sum(disk_reads_delta) DESC ) phys_reads_rank
FROM dba_hist_sqlstat s,(select sql_id, dbid , sqldetail
from
(select
sql_id, dbid,
REPLACE(REPLACE( dbms_lob.substr(sql_text,50,1), CHR(10) ), CHR(13) ) sqldetail
from dba_hist_sqltext a) a
where a.sqldetail not like '%SQL Analyze%'
and a.sqldetail not like '%sys.ora%') t
WHERE s.dbid = &&ecr_dbid.
AND s.dbid = t.dbid
AND s.sql_id = t.sql_id
AND s.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','AUDSYS','MDSYS','ORDSYS','XDB','APEX_PUBLIC_USER','ORACLE_OCM','APEX_050100','GSMADMIN_INTERNAL','ORDS_METADATA')
AND s.force_matching_signature != 0
GROUP BY s.sql_id,s.force_matching_signature , t.sqldetail, s.PARSING_SCHEMA_NAME
)
WHERE elap_rank <= &SQL_TOP_N
OR phys_reads_rank <= &SQL_TOP_N
or log_reads_rank <= &SQL_TOP_N
or exec_rank <= &SQL_TOP_N
)
group by force_matching_signature , sqldetail, parsing_schema_name
)
union all
select distinct '@planx Y ' || sql_id || ',' || sqldetail || ',' || parsing_schema_name
from
(
SELECT * FROM(
SELECT s.sql_id, s.force_matching_signature, t.sqldetail ,s.PARSING_SCHEMA_NAME ,
DENSE_RANK() OVER
(ORDER BY sum(EXECUTIONS_DELTA) DESC ) exec_rank,
DENSE_RANK() OVER
(ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
DENSE_RANK() OVER
(ORDER BY sum(BUFFER_GETS_DELTA) DESC ) log_reads_rank,
DENSE_RANK() OVER
(ORDER BY sum(disk_reads_delta) DESC ) phys_reads_rank
FROM dba_hist_sqlstat s,(select sql_id, dbid , sqldetail
from
(select
sql_id, dbid,
REPLACE(REPLACE( dbms_lob.substr(sql_text,50,1), CHR(10) ), CHR(13) ) sqldetail
from dba_hist_sqltext a) a
where a.sqldetail not like '%SQL Analyze%'
and a.sqldetail not like '%sys.ora%') t
WHERE s.dbid = &&ecr_dbid.
AND s.dbid = t.dbid
AND s.sql_id = t.sql_id
AND s.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','AUDSYS','MDSYS','ORDSYS','XDB','APEX_PUBLIC_USER','ORACLE_OCM','APEX_050100','GSMADMIN_INTERNAL','ORDS_METADATA')
AND force_matching_signature = 0
GROUP BY s.sql_id,s.force_matching_signature , t.sqldetail, s.PARSING_SCHEMA_NAME
)
WHERE elap_rank <= &SQL_TOP_N
OR phys_reads_rank <= &SQL_TOP_N
or log_reads_rank <= &SQL_TOP_N
or exec_rank <= &SQL_TOP_N
);
spool off
spool awr_planx-&_instname-¤t_time..sql
select distinct '@planx Y ' || sql_id --|| ',' || sqldetail || ',' || parsing_schema_name
from
(
select force_matching_signature, max(sql_id) keep (dense_rank first order by elap_rank asc) sql_id --, sqldetail, parsing_schema_name
from
(
SELECT * FROM(
SELECT s.sql_id, s.force_matching_signature, --t.sqldetail ,s.PARSING_SCHEMA_NAME ,
DENSE_RANK() OVER
(ORDER BY sum(EXECUTIONS_DELTA) DESC ) exec_rank,
DENSE_RANK() OVER
(ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
DENSE_RANK() OVER
(ORDER BY sum(BUFFER_GETS_DELTA) DESC ) log_reads_rank,
DENSE_RANK() OVER
(ORDER BY sum(disk_reads_delta) DESC ) phys_reads_rank
FROM dba_hist_sqlstat s,(select sql_id, dbid --, sqldetail
from
(select
sql_id, dbid,
REPLACE(REPLACE( dbms_lob.substr(sql_text,50,1), CHR(10) ), CHR(13) ) sqldetail
from dba_hist_sqltext a) a
where a.sqldetail not like '%SQL Analyze%'
and a.sqldetail not like '%sys.ora%') t
WHERE s.dbid = &&ecr_dbid.
AND s.dbid = t.dbid
AND s.sql_id = t.sql_id
AND s.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','AUDSYS','MDSYS','ORDSYS','XDB','APEX_PUBLIC_USER','ORACLE_OCM','APEX_050100','GSMADMIN_INTERNAL','ORDS_METADATA')
AND s.force_matching_signature != 0
GROUP BY s.sql_id,s.force_matching_signature --, t.sqldetail, s.PARSING_SCHEMA_NAME
)
WHERE elap_rank <= &SQL_TOP_N
OR phys_reads_rank <= &SQL_TOP_N
or log_reads_rank <= &SQL_TOP_N
or exec_rank <= &SQL_TOP_N
)
group by force_matching_signature --, sqldetail, parsing_schema_name
)
union all
select distinct '@planx Y ' || sql_id --|| ',' || sqldetail || ',' || parsing_schema_name
from
(
SELECT * FROM(
SELECT s.sql_id, s.force_matching_signature, --t.sqldetail ,s.PARSING_SCHEMA_NAME ,
DENSE_RANK() OVER
(ORDER BY sum(EXECUTIONS_DELTA) DESC ) exec_rank,
DENSE_RANK() OVER
(ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
DENSE_RANK() OVER
(ORDER BY sum(BUFFER_GETS_DELTA) DESC ) log_reads_rank,
DENSE_RANK() OVER
(ORDER BY sum(disk_reads_delta) DESC ) phys_reads_rank
FROM dba_hist_sqlstat s,(select sql_id, dbid --, sqldetail
from
(select
sql_id, dbid,
REPLACE(REPLACE( dbms_lob.substr(sql_text,50,1), CHR(10) ), CHR(13) ) sqldetail
from dba_hist_sqltext a) a
where a.sqldetail not like '%SQL Analyze%'
and a.sqldetail not like '%sys.ora%') t
WHERE s.dbid = &&ecr_dbid.
AND s.dbid = t.dbid
AND s.sql_id = t.sql_id
AND s.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','AUDSYS','MDSYS','ORDSYS','XDB','APEX_PUBLIC_USER','ORACLE_OCM','APEX_050100','GSMADMIN_INTERNAL','ORDS_METADATA')
AND force_matching_signature = 0
GROUP BY s.sql_id,s.force_matching_signature --, t.sqldetail, s.PARSING_SCHEMA_NAME
)
WHERE elap_rank <= &SQL_TOP_N
OR phys_reads_rank <= &SQL_TOP_N
or log_reads_rank <= &SQL_TOP_N
or exec_rank <= &SQL_TOP_N
);
spool off
PRO Running planx
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@awr_planx-&_instname-¤t_time..sql
! mkdir awr_planx-&_instname
! mv planx*&_instname*.txt awr_planx-&_instname
! mv awr_planx-&_instname-*.sql awr_planx-&_instname