-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathoracle_performance.ini
225 lines (200 loc) · 16.7 KB
/
oracle_performance.ini
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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
[database]
type = Oracle
host = 192.168.0.100
port = 1521
user = system
passwd = 123456
sid = orcl
[report]
report_title = Oracle performance monitoring Report
report_count = 25
#Waits
#---1---------------------------------
title1 = Waiting for the longest time in the past 15 minutes SQL TOP10
query1 = SELECT ROWNUM AS RANK, a.* FROM ( SELECT u.username,SUM (wait_time + time_waited) total_wait_time,
s.SQL_text FROM v$active_session_history a, v$SQLarea s, dba_users u WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.SQL_id = s.SQL_id AND a.user_id = u.user_id GROUP BY u.username, s.SQL_text ORDER BY 2 DESC) a WHERE ROWNUM < 11
style1 = {1: 'RANK,r', 2: 'username,l', 3: 'total_wait_time,r', 4: 'SQL text,l'}
#---2---------------------------------
title2 = Wait for events statistics over the past 15 minutes
query2 = SELECT event, SUM (wait_time + time_waited) total_wait_time FROM v$active_session_history
WHERE sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE GROUP BY event ORDER BY total_wait_time DESC
style2 = {1: 'Event,l', 2: 'Total_wait_time,r'}
#---3---------------------------------
title3 = Waiting for the longest time object
query3 = SELECT o.object_name,o.object_TYPE,a.event,SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects o WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.current_obj# = o.object_id GROUP BY o.object_name, o.object_type, a.event ORDER BY total_wait_time DESC
style3 = {1: 'Object_name,l', 2: 'Object_TYPE,l', 3: 'event,l', 4: 'total_wait_time,r'}
#---4---------------------------------
title4 = User activity connection and wait for statistics
query4 = SELECT SCHEMANAME,STATUS,wait_class,state,count(*) FROM v$session a
group by SCHEMANAME,STATUS,wait_class,state order by 1,2
style4 = {1: 'SCHEMANAME,l', 2: 'STATUS,l', 3: 'wait_class,l', 4: 'state,l', 5: 'count(*),r'}
#---5---------------------------------
title5 = The user lock the object statistics
query5 = SELECT LPAD (' ', DECODE (i.xidusn, 0, 3, 0)) || i.oracle_username users,o.object_name,COUNT (*)
FROM v$locked_object i, dba_objects o WHERE i.object_id = o.object_id
GROUP BY LPAD (' ', DECODE (i.xidusn, 0, 3, 0)) || i.oracle_username, o.object_name ORDER BY 3 DESC
style5 = {1: 'users,r', 2: 'object_name,l', 3: 'COUNT (*),r'}
#File Information
#---6---------------------------------
title6 = The user to open the cursor
query6 = SELECT SUM (a.VALUE) total_cur, AVG (a.VALUE) avg_cur,MAX (a.VALUE) max_cur,s.username,s.machine,S.PROGRAM
FROM v$sesstat a, v$statname b, v$session s WHERE a.statistic# = b.statistic# AND s.sid = a.sid
AND b.name = 'opened cursors current' GROUP BY s.username, s.machine, S.PROGRAM ORDER BY 1 DESC
style6 = {1: 'total_cur,r', 2: 'avg_cur,r', 3: 'max_cur,r', 4: 'username,l', 5: 'machine,l', 6: 'PROGRAM,l'}
#---7---------------------------------
title7 = Oracle CPU Load
query7 = SELECT ROUND (100 * CPU_sec / available_time, 2) "ORACLE CPU TIME AS % AVAIL.",
ROUND (100 * (DB_sec - CPU_sec) / available_time, 2) "NON-IDLE WAITS AS % AVAIL.",
CASE SIGN (available_time - DB_sec) WHEN 1 THEN ROUND (100 * (available_time - DB_sec) / available_time, 2) ELSE 0 END "ORACLE IDLE AS % AVAIL."
FROM (SELECT (SYSDATE - i.startup_time) * 86400 * c.cpus available_time,t.DB_sec,t.CPU_sec FROM v$instance i,
(SELECT VALUE cpus FROM v$parameter WHERE name = 'cpu_count') c,
(SELECT SUM (CASE name WHEN 'DB time' THEN ROUND (VALUE / 100) ELSE 0 END) DB_sec,
SUM (CASE name WHEN 'DB time' THEN 0 ELSE ROUND (VALUE / 100) END) CPU_sec
FROM v$sysstat WHERE name IN ('DB time', 'CPU used by this session')) t WHERE i.instance_number = USERENV ('INSTANCE'))
style7 = {1: 'ORACLE CPU TIME AS % AVAIL.,l',2: 'NON-IDLE WAITS AS % AVAIL.,l',3: 'ORACLE IDLE AS % AVAIL.,l'}
#---8---------------------------------
title8 = Wait for events sum
query8 = SELECT wait_class,
ROUND (100 * time_class / total_waits, 2) "CLASS AS % OF WHOLE",
event,
ROUND (100 * time_waited / time_class, 2) "EVENT AS % OF CLASS",
ROUND (100 * time_waited / total_waits, 2) "EVENT AS % OF WHOLE"
FROM (SELECT wait_class,event,time_waited,
SUM (time_waited) OVER (PARTITION BY wait_class) time_class,
RANK () OVER (PARTITION BY wait_class ORDER BY time_waited DESC) rank_within_class,
SUM (time_waited) OVER () total_waits FROM v$system_event WHERE wait_class <> 'Idle')
WHERE rank_within_class <= 3 and time_class<>0 ORDER BY time_class DESC, rank_within_class
style8 = {1: 'wait_class,l', 2: 'CLASS AS % OF WHOLE,r', 3: 'event,l', 4: 'EVENT AS % OF CLASS,r', 5: 'EVENT AS % OF WHOLE,r'}
#TOP SQL
#---9---------------------------------
title9 = Elapsed Time
query9 = SELECT ROWNUM AS RANK, a.* FROM ( SELECT vs.sql_id,au.USERNAME,vs.module,rows_processed,elapsed_time,
executions,buffer_gets,disk_reads,cpu_time,vs.first_load_time,vs.sql_text FROM v$sqlarea vs, all_users au
WHERE (au.user_id(+) = vs.parsing_user_id) AND (executions >= 1) AND elapsed_time > 20000
ORDER BY elapsed_time DESC) a WHERE ROWNUM < 11
style9 = {1: 'RANK,r', 2: 'sql_id,l', 3: 'USERNAME,l', 4: 'module,l', 5: 'rows_processed,r', 6: 'elapsed_time,r', 7: 'executions,r', 8: 'buffer_gets,r', 9: 'disk_reads,r', 10: 'cpu_time,r', 11: 'first_load_time,l', 12: 'sql_text,l'}
#---10---------------------------------
title10 = CPU Time
query10 = SELECT ROWNUM AS RANK, a.* FROM ( SELECT vs.sql_id,au.USERNAME,vs.module, rows_processed,elapsed_time,
executions,buffer_gets,disk_reads,cpu_time,vs.first_load_time,vs.sql_text FROM v$sqlarea vs, all_users au
WHERE (au.user_id(+) = vs.parsing_user_id) AND (executions >= 1) AND cpu_time > 20000 ORDER BY cpu_time DESC) a WHERE ROWNUM < 11
style10 = {1: 'RANK,r', 2: 'sql_id,l', 3: 'USERNAME,l', 4: 'module,l', 5: 'rows_processed,r', 6: 'elapsed_time,r', 7: 'executions,r', 8: 'buffer_gets,r', 9: 'disk_reads,r', 10: 'cpu_time,r', 11: 'first_load_time,l', 12: 'sql_text,l'}
#---11---------------------------------
title11 = Buffer Gets by Executions
query11 = SELECT ROWNUM AS RANK, a.* FROM ( SELECT vs.sql_id, au.USERNAME,vs.module, rows_processed,vs.buffer_gets,
vs.executions, buffer_gets / executions gets_per_exec, vs.first_load_time, vs.sql_text FROM v$sqlarea vs, all_users au
WHERE (au.user_id(+) = vs.parsing_user_id) AND (executions >= 1) AND buffer_gets > 50000
ORDER BY gets_per_exec DESC) a WHERE ROWNUM < 11
style11 = {1: 'RANK,r', 2: 'sql_id,l', 3: 'USERNAME,l', 4: 'module,l', 5: 'rows_processed,r', 6: 'buffer_gets,r', 7: 'executions,r', 8: 'gets_per_exec,r', 9: 'first_load_time,l', 10: 'sql_text,l'}
#---12---------------------------------
title12 = Physical Reads by Executions
query12 = SELECT ROWNUM AS RANK, a.* FROM ( SELECT vs.sql_id, au.USERNAME, vs.module, rows_processed,
disk_reads,executions,disk_reads / executions reads_per_exec,vs.first_load_time, vs.sql_text
FROM v$sqlarea vs, all_users au WHERE (au.user_id(+) = vs.parsing_user_id) AND (executions >= 1)
ORDER BY disk_reads DESC) a WHERE ROWNUM < 11
style12 = {1: 'RANK,r', 2: 'sql_id,l', 3: 'USERNAME,l', 4: 'module,l', 5: 'rows_processed,r', 6: 'disk_reads,r', 7: 'executions,r', 8: 'reads_per_exec,r', 9: 'first_load_time,l', 10: 'sql_text,l'}
#---13---------------------------------
title13 = Rows Processed by Executions
query13 = SELECT ROWNUM AS RANK, a.* FROM ( SELECT vs.sql_id,au.USERNAME,vs.module,rows_processed, executions,
rows_processed / executions rows_per_exec, vs.first_load_time, vs.sql_text FROM v$sqlarea vs, all_users au
WHERE (au.user_id(+) = vs.parsing_user_id) AND (executions >= 1) AND rows_processed > 10000
ORDER BY rows_processed DESC) a WHERE ROWNUM < 11
style13 = {1: 'RANK,r', 2: 'sql_id,l', 3: 'USERNAME,l', 4: 'module,l', 5: 'rows_processed,r', 6: 'executions,r', 7: 'rows_per_exec,r', 8: 'first_load_time,l', 9: 'sql_text,l'}
#---14---------------------------------
title14 = Buffer Gets vs Rows Processed
query14 = SELECT ROWNUM AS RANK, a.* FROM (SELECT sql_id,buffer_gets,LPAD (rows_processed|| DECODE (users_opening + users_executing, 0, ' ', '*'),20) "rows_processed",
executions, loads,(DECODE (rows_processed, 0, 1, 1)) * buffer_gets / DECODE (rows_processed, 0, 1, rows_processed) avg_cost,sql_text
FROM v$sqlarea WHERE DECODE (rows_processed, 0, 1, 1) * buffer_gets / DECODE (rows_processed, 0, 1, rows_processed) > 10000
ORDER BY 5 DESC) a WHERE ROWNUM < 11
style14 = {1: 'RANK,r', 2: 'sql_id,l', 3: 'buffer_gets,r', 4: 'rows_processed,r', 5: 'executions,r', 6: 'loads,r', 7: 'avg_cost,r', 8:'sql_text,l'}
#LOCK
#---15---------------------------------
title15 = Locks
query15 = SELECT LK.SID,SE.USERNAME,SE.OSUSER,SE.MACHINE,
DECODE (LK.TYPE,'TX', 'Transaction','TM', 'DML','UL', 'PL/SQL User Lock',LK.TYPE) LOCK_TYPE,
DECODE (LK.LMODE,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive', TO_CHAR (LK.LMODE)) MODE_HELD,
DECODE (LK.REQUEST,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive', TO_CHAR (LK.REQUEST)) MODE_REQUESTED,
TO_CHAR (LK.ID1) LOCK_ID1,TO_CHAR (LK.ID2) LOCK_ID2,OB.OWNER,OB.OBJECT_TYPE,OB.OBJECT_NAME,LK.BLOCK,SE.LOCKWAIT
FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE WHERE LK.TYPE IN ('TM', 'UL') AND LK.SID = SE.SID AND LK.ID1 = OB.OBJECT_ID(+)
style15 = {1: 'SID,l', 2: 'USERNAME,l', 3: 'OSUSER,l', 4: 'MACHINE,l', 5: 'LOCK_TYPE,l', 6: 'MODE_HELD,l', 7: 'MODE_REQUESTED,l', 8: 'LOCK_ID1,l', 9: 'LOCK_ID2,l', 10: 'OWNER,l', 11: 'OBJECT_TYPE,l', 12: 'OBJECT_NAME,l', 13: 'BLOCK,r', 14: 'LOCKWAIT,r'}
#---16---------------------------------
title16 = Locks Objects
query16 = SELECT s1.username blkg_user,s1.machine blkg_ws,s1.sid blkg_sid,s2.username wait_user,
s2.machine wait_ws,s2.sid wait_sid,lo.object_id blkd_obj_id,do.owner,do.object_name
FROM v$lock l1,v$session s1,v$lock l2,v$session s2,v$locked_object lo,dba_objects do
WHERE s1.sid = l1.sid AND s2.sid = l2.sid AND l1.id1 = l2.id1 AND s1.sid = lo.session_id
AND lo.object_id = do.object_id AND l1.block = 1 AND l2.request > 0
style16 = {1: 'blkg_user,l', 2: 'blkg_ws,l', 3: 'blkg_sid,r', 4: 'wait_user,l', 5: 'wait_ws,l', 6: 'wait_sid,l', 7: 'blkd_obj_id,l', 8: 'owner,l', 9: 'object_name,l'}
#---17---------------------------------
title17 = Locks Objects SUM
query17 = SELECT LPAD (' ', DECODE (i.xidusn, 0, 3, 0)) || i.oracle_username users,o.object_name,COUNT (*)
FROM v$locked_object i, dba_objects o WHERE i.object_id = o.object_id
GROUP BY LPAD (' ', DECODE (i.xidusn, 0, 3, 0)) || i.oracle_username,o.object_name ORDER BY 3 DESC
style17 = {1: 'users,l', 2: 'object_name,l', 3: 'COUNT (*),r'}
#SESSION
#---18---------------------------------
title18 = > 5 seconds SQL execution time of 60 minutes
query18 = SELECT SID,SERIAL#,OPNAME,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS,TARGET,TARGET_DESC,
SQL_EXEC_START,LAST_UPDATE_TIME,ELAPSED_SECONDS,b.SQL_TEXT
FROM v$session_longops a, V$sql b WHERE USERNAME not in ('SYS','SYSTEM','DBSNMP')
AND SQL_PLAN_HASH_VALUE <> 0 AND SQL_EXEC_START >= SYSDATE - 60/ 1440
AND a.SQL_ID= B.SQL_ID(+) ORDER BY ELAPSED_SECONDS DESC
style18 = {1: 'SID,l', 2: 'SERIAL#,l', 3: 'OPNAME,l', 4: 'SQL_PLAN_OPERATION,l', 5: 'SQL_PLAN_OPTIONS,l', 6: 'TARGET,l', 7: 'TARGET_DESC,l', 8: 'SQL_EXEC_START,l', 9: 'LAST_UPDATE_TIME,l', 10: 'ELAPSED_SECONDS,r', 11: 'SQL_TEXT,l'}
#---19---------------------------------
title19 = ACTIVE SESSION
query19 = SELECT a.SID,a.serial#,a.username,a.lockwait,a.osuser,a.machine,a.program,a.TYPE,a.module,a.logon_time,
a.event,a.wait_class,a.seconds_in_wait,a.state,disk_reads,buffer_gets,sorts,B.SQL_TEXT
FROM v$session a, v$sqlarea b WHERE a.sql_address = b.address AND a.SQL_HASH_VALUE = b.HASH_VALUE
AND TYPE != 'BACKGROUND' AND status = 'ACTIVE'
style19 = {1: 'SID,r', 2: 'serial#,r', 3: 'username,l', 4: 'lockwait,l', 5: 'osuser,l', 6: 'machine,l', 7: 'program,l', 8: 'TYPE,l', 9: 'module,l', 10: 'logon_time,l', 11: 'event,l', 12: 'wait_class,l', 13: 'seconds_in_wait,r', 14: 'state,l', 15: 'disk_reads,r', 16: 'buffer_gets,r', 17: 'sorts,r', 18: 'SQL_TEXT,l'}
#---20---------------------------------
title20 = Executing big SQL
query20 = SELECT username,SID,opname,ROUND (sofar * 100 / totalwork, 0) || '%' AS progress,time_remaining,sql_text
FROM v$session_longops, v$sql WHERE time_remaining <> 0 AND sql_address = address AND sql_hash_value = hash_value
style20 = {1: 'username,l', 2: 'SID,r', 3: 'opname,l', 4: 'progress,l', 5: 'time_remaining,r', 6: 'sql_text,l'}
#---21---------------------------------
title21 = ACTIVE TOP CPU SQL
query21 = SELECT * FROM ( SELECT a.sid session_id, a.sql_id,a.status,a.cpu_time / 1000000 cpu_sec,a.buffer_gets,a.disk_reads,b.sql_text sql_text
FROM v$sql_monitor a, v$sql b WHERE a.sql_id = b.sql_id ORDER BY a.cpu_time DESC) WHERE ROWNUM <= 20
style21 = {1: 'session_id,r', 2: 'sql_id,l', 3: 'status,l', 4: 'cpu_sec,r', 5: 'buffer_gets,r', 6: 'disk_reads,r', 7: 'sql_text,l'}
#---22---------------------------------
title22 = highest CPU consumption
query22 = SELECT s.sid,s.serial#,p.spid AS "OS PID",s.username,s.module,st.VALUE / 100 AS "CPU sec"
FROM v$sesstat st,v$statname sn,v$session s,v$process p
WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr
AND s.last_call_et < 1800 AND s.logon_time > (SYSDATE - 240 / 1440) ORDER BY st.VALUE
style22 = {1: 'sid,r', 2: 'serial#,r', 3: 'OS PID,r', 4: 'username,l', 5: 'module,l', 6: 'CPU sec,r'}
#---23---------------------------------
title23 = highest time for wait
query23 = SELECT s.sid,s.serial#,p.spid AS "OS PID",s.username,s.module,se.event,se.time_waited
FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 1800
AND s.logon_time > (SYSDATE - 240 / 1440) AND se.sid = s.sid AND s.paddr = p.addr ORDER BY se.time_waited desc
style23 = {1: 'sid,r', 2: 'serial#,r', 3: 'OS PID,r', 4: 'username,l', 5: 'module,l', 6: 'event,r', 7: 'time_waited,r'}
#---24---------------------------------
title24 = highest DB Time usage
query24 = SELECT s.sid,s.serial#,p.spid AS "OS PID",s.username,s.module,st.VALUE / 100 AS "DB Time (sec)",
stcpu.VALUE / 100 AS "CPU Time (sec)",ROUND (stcpu.VALUE / st.VALUE * 100, 2) AS "% CPU"
FROM v$sesstat st,v$statname sn,v$session s,v$sesstat stcpu,v$statname sncpu,v$process p
WHERE sn.name = 'DB time' AND st.statistic# = sn.statistic# AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid AND s.paddr = p.addr AND s.last_call_et < 1800
AND s.logon_time > (SYSDATE - 240 / 1440) AND st.VALUE > 0
style24 = {1: 'sid,r', 2: 'serial#,r', 3: 'OS PID,r', 4: 'username,l', 5: 'module,l', 6: 'DB Time (sec),r', 7: 'CPU Time (sec),r', 8: '% CPU,r'}
#---25---------------------------------
title25 = Objects Missing Statistics
query25 = SELECT 'TABLE' object_type,owner,table_name object_name,last_analyzed,stattype_locked,stale_stats
FROM all_tab_statistics WHERE (last_analyzed IS NULL OR stale_stats = 'YES') AND stattype_locked IS NULL
AND owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS',
'ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
'TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB','HBXY','ORDDATA')
AND owner NOT LIKE 'FLOW%' AND owner NOT LIKE 'APEX%' UNION ALL
SELECT 'INDEX' object_type,owner,index_name object_name,last_analyzed,stattype_locked,stale_stats
FROM all_ind_statistics WHERE (last_analyzed IS NULL OR stale_stats = 'YES') AND stattype_locked IS NULL
AND owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS',
'ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
'TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB','HBXY','ORDDATA')
AND owner NOT LIKE 'FLOW%' AND owner NOT LIKE 'APEX%' ORDER BY object_type DESC, owner, object_name
style25 = {1: 'object_type,l', 2: 'owner,l', 3: 'object_name,l', 4: 'last_analyzed,l', 5: 'stattype_locked,l', 6: 'stale_stats,l'}