- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900266&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
목차
1. 대기 이벤트(Wait Event)란?
- 대기 이벤트란 오라클 인스턴스 내에서 공유된 자원을 사용하기 위해 프로세스들이 점유를 해야 하는데 이 때 점유를 하지 못한 상태에서는 Sleep 상태에 빠지며
이 때 발생되는 현상을 대기 이벤트(Wait Event)라고 함. - 대기 이벤트는 오라클 개발자들이 Debuging 용도로 개발한 것인데 오늘날까지 발전되어 성능관리 분야에 일대 혁신을 가져옴.
- 대기 이벤트 종류(10.2.0.3 기준)
- 대기 이벤트 종류는 아래의 SQL을 수행하여 확인할 수 있다.
SELECT WAIT_CLASS, NAME, PARAMETER1, PARAMETER2, PARAMETER3 FROM V$EVENT_NAME ORDER BY WAIT_CLASS, NAME ;
Count | WAIT_CLASS | NAME | PARAMETER1 | PARAMETER2 | PARAMETER3 |
---|---|---|---|---|---|
1 | Administrative | ASM COD rollback operation completion | dismount force | ||
2 | Administrative | ASM mount : wait for heartbeat | |||
3 | Administrative | Backup: sbtbackup | |||
4 | Administrative | Backup: sbtclose | |||
5 | Administrative | Backup: sbtclose2 | |||
6 | Administrative | Backup: sbtcommand | |||
7 | Administrative | Backup: sbtend | |||
8 | Administrative | Backup: sbterror | |||
9 | Administrative | Backup: sbtinfo | |||
10 | Administrative | Backup: sbtinfo2 | |||
11 | Administrative | Backup: sbtinit | |||
12 | Administrative | Backup: sbtinit2 | |||
13 | Administrative | Backup: sbtopen | |||
14 | Administrative | Backup: sbtpcbackup | |||
15 | Administrative | Backup: sbtpccancel | |||
16 | Administrative | Backup: sbtpccommit | |||
17 | Administrative | Backup: sbtpcend | |||
18 | Administrative | Backup: sbtpcquerybackup | |||
19 | Administrative | Backup: sbtpcqueryrestore | |||
20 | Administrative | Backup: sbtpcrestore | |||
21 | Administrative | Backup: sbtpcstart | |||
22 | Administrative | Backup: sbtpcstatus | |||
23 | Administrative | Backup: sbtpcvalidate | |||
24 | Administrative | Backup: sbtread | |||
25 | Administrative | Backup: sbtread2 | |||
26 | Administrative | Backup: sbtremove | |||
27 | Administrative | Backup: sbtremove2 | |||
28 | Administrative | Backup: sbtrestore | |||
29 | Administrative | Backup: sbtwrite | |||
30 | Administrative | Backup: sbtwrite2 | |||
31 | Administrative | JS coord start wait | |||
32 | Administrative | JS kgl get object wait | |||
33 | Administrative | JS kill job wait | |||
34 | Administrative | alter rbs offline | |||
35 | Administrative | alter system set dispatcher | waited | ||
36 | Administrative | buffer pool resize | buffer pool id | current size | new size |
37 | Administrative | enq: DB - contention | name_mode | EnqMode | 0 |
38 | Administrative | enq: TW - contention | name_mode | 0 | operation |
39 | Administrative | enq: ZG - contention | name_mode | file group id | version id |
40 | Administrative | index (re)build online cleanup | object | mode | wait |
41 | Administrative | index (re)build online merge | object | mode | wait |
42 | Administrative | index (re)build online start | object | mode | wait |
43 | Administrative | multiple dbwriter suspend/resume for file offline | |||
44 | Administrative | switch logfile command | |||
45 | Administrative | switch undo - offline | |||
46 | Administrative | wait for possible quiesce finish | |||
47 | Application | SQL*Net break/reset to client | driver id | break? | |
48 | Application | SQL*Net break/reset to dblink | driver id | break? | |
49 | Application | Streams capture: filter callback waiting for ruleset | |||
50 | Application | Streams: apply reader waiting for DDL to apply | sleep time | ||
51 | Application | Wait for Table Lock | |||
52 | Application | enq: KO - fast object checkpoint | name_mode | 2 | 0 |
53 | Application | enq: PW - flush prewarm buffers | name_mode | 0 | 0 |
54 | Application | enq: RO - contention | name_mode | 2 | 0 |
55 | Application | enq: RO - fast object reuse | name_mode | 2 | 0 |
56 | Application | enq: TM - contention | name_mode | object # | table/partition |
57 | Application | enq: TX - row lock contention | name_mode | usn<<16 _ slot | sequence |
58 | Application | enq: UL - contention | name_mode | id | 0 |
59 | Cluster | ASM PST query : wait for [PM][grp][0] grant | |||
60 | Cluster | Streams: RAC waiting for inter instance ack | |||
61 | Cluster | gc assume | le | ||
62 | Cluster | gc block recovery request | file# | block# | class# |
63 | Cluster | gc buffer busy | file# | block# | id# |
64 | Cluster | gc claim | |||
65 | Cluster | gc cr block 2-way | |||
66 | Cluster | gc cr block 3-way | |||
67 | Cluster | gc cr block busy | |||
68 | Cluster | gc cr block congested | |||
69 | Cluster | gc cr block lost | |||
70 | Cluster | gc cr block unknown | |||
71 | Cluster | gc cr cancel | le | ||
72 | Cluster | gc cr disk read | |||
73 | Cluster | gc cr disk request | file# | block# | class# |
74 | Cluster | gc cr failure | |||
75 | Cluster | gc cr grant 2-way | |||
76 | Cluster | gc cr grant busy | |||
77 | Cluster | gc cr grant congested | |||
78 | Cluster | gc cr grant unknown | |||
79 | Cluster | gc cr multi block request | file# | block# | class# |
80 | Cluster | gc cr request | file# | block# | class# |
81 | Cluster | gc current block 2-way | |||
82 | Cluster | gc current block 3-way | |||
83 | Cluster | gc current block busy | |||
84 | Cluster | gc current block congested | |||
85 | Cluster | gc current block lost | |||
86 | Cluster | gc current block unknown | |||
87 | Cluster | gc current cancel | le | ||
88 | Cluster | gc current grant 2-way | |||
89 | Cluster | gc current grant busy | |||
90 | Cluster | gc current grant congested | |||
91 | Cluster | gc current grant unknown | |||
92 | Cluster | gc current multi block request | file# | block# | id# |
93 | Cluster | gc current request | file# | block# | id# |
94 | Cluster | gc current retry | |||
95 | Cluster | gc current split | |||
96 | Cluster | gc domain validation | file# | block# | class# |
97 | Cluster | gc freelist | |||
98 | Cluster | gc prepare | |||
99 | Cluster | gc quiesce wait | |||
100 | Cluster | gc recovery free | |||
101 | Cluster | gc recovery quiesce | |||
102 | Cluster | gc remaster | file# | block# | class# |
103 | Cluster | lock remastering | |||
104 | Cluster | pi renounce write complete | file# | block# | |
105 | Cluster | retry contact SCN lock master | |||
106 | Commit | log file sync | buffer# | ||
107 | Concurrency | buffer busy waits | file# | block# | class# |
108 | Concurrency | cursor: mutex S | idn | value | where_sleeps |
109 | Concurrency | cursor: mutex X | idn | value | where_sleeps |
110 | Concurrency | cursor: pin S wait on X | idn | value | where_sleeps |
111 | Concurrency | enq: TX - index contention | name_mode | usn<<16 _ slot | sequence |
112 | Concurrency | latch: In memory undo latch | address | number | tries |
113 | Concurrency | latch: MQL Tracking Latch | address | number | tries |
114 | Concurrency | latch: Undo Hint Latch | address | number | tries |
115 | Concurrency | latch: cache buffers chains | address | number | tries |
116 | Concurrency | latch: library cache | address | number | tries |
117 | Concurrency | latch: library cache lock | address | number | tries |
118 | Concurrency | latch: library cache pin | address | number | tries |
119 | Concurrency | latch: row cache objects | address | number | tries |
120 | Concurrency | latch: shared pool | address | number | tries |
121 | Concurrency | library cache load lock | object address | lock address | 100*mask+namespace |
122 | Concurrency | library cache lock | handle address | lock address | 100*mode+namespace |
123 | Concurrency | library cache pin | handle address | pin address | 100*mode+namespace |
124 | Concurrency | logout restrictor | |||
125 | Concurrency | os thread startup | |||
126 | Concurrency | pipe put | handle address | record length | timeout |
127 | Concurrency | resmgr:internal state change | location | ||
128 | Concurrency | resmgr:internal state cleanup | location | ||
129 | Concurrency | resmgr:sessions to exit | location | ||
130 | Concurrency | row cache lock | cache id | mode | request |
131 | Concurrency | row cache read | cache id | address | times |
132 | Configuration | Streams AQ: enqueue blocked on low memory | |||
133 | Configuration | Streams capture: resolve low memory condition | |||
134 | Configuration | Streams capture: waiting for subscribers to catch up | |||
135 | Configuration | checkpoint completed | |||
136 | Configuration | enq: HW - contention | name_mode | table space # | block |
137 | Configuration | enq: SQ - contention | name_mode | object # | 0 |
138 | Configuration | enq: SS - contention | name_mode | tablespace # | dba |
139 | Configuration | enq: ST - contention | name_mode | 0 | 0 |
140 | Configuration | enq: TX - allocate ITL entry | name_mode | usn<<16 _ slot | sequence |
141 | Configuration | free buffer waits | file# | block# | set-id# |
142 | Configuration | latch: redo copy | address | number | tries |
143 | Configuration | latch: redo writing | address | number | tries |
144 | Configuration | log buffer space | |||
145 | Configuration | log file switch (archiving needed) | |||
146 | Configuration | log file switch (checkpoint incomplete) | |||
147 | Configuration | log file switch (private strand flush incomplete) | |||
148 | Configuration | log file switch completion | |||
149 | Configuration | sort segment request | |||
150 | Configuration | statement suspended, wait error to be cleared | |||
151 | Configuration | undo segment extension | segment# | ||
152 | Configuration | undo segment tx slot | segment# | ||
153 | Configuration | wait for EMON to process ntfns | |||
154 | Configuration | write complete waits | file# | block# | |
155 | Idle | ASM background timer | |||
156 | Idle | DIAG idle wait | component | where | wait time(millisec) |
157 | Idle | EMON idle wait | |||
158 | Idle | HS message to agent | |||
159 | Idle | JS external job | |||
160 | Idle | KSV master wait | |||
161 | Idle | LNS ASYNC archive log | |||
162 | Idle | LNS ASYNC dest activation | |||
163 | Idle | LNS ASYNC end of log | |||
164 | Idle | LogMiner: client waiting for transaction | |||
165 | Idle | LogMiner: slave waiting for activate message | |||
166 | Idle | LogMiner: wakeup event for builder | |||
167 | Idle | LogMiner: wakeup event for preparer | |||
168 | Idle | LogMiner: wakeup event for reader | |||
169 | Idle | PL/SQL lock timer | duration | ||
170 | Idle | PX Deq Credit: need buffer | sleeptime/senderid | passes | qref |
171 | Idle | PX Deq: Execute Reply | sleeptime/senderid | passes | |
172 | Idle | PX Deq: Execution Msg | sleeptime/senderid | passes | |
173 | Idle | PX Deq: Index Merge Close | sleeptime/senderid | passes | |
174 | Idle | PX Deq: Index Merge Execute | sleeptime/senderid | passes | |
175 | Idle | PX Deq: Index Merge Reply | sleeptime/senderid | passes | |
176 | Idle | PX Deq: Join ACK | sleeptime/senderid | passes | |
177 | Idle | PX Deq: Msg Fragment | sleeptime/senderid | passes | |
178 | Idle | PX Deq: Par Recov Change Vector | sleeptime/senderid | passes | |
179 | Idle | PX Deq: Par Recov Execute | sleeptime/senderid | passes | |
180 | Idle | PX Deq: Par Recov Reply | sleeptime/senderid | passes | |
181 | Idle | PX Deq: Parse Reply | sleeptime/senderid | passes | |
182 | Idle | PX Deq: Table Q Normal | sleeptime/senderid | passes | |
183 | Idle | PX Deq: Table Q Sample | sleeptime/senderid | passes | |
184 | Idle | PX Deq: Txn Recovery Reply | sleeptime/senderid | passes | |
185 | Idle | PX Deq: Txn Recovery Start | sleeptime/senderid | passes | |
186 | Idle | PX Deq: kdcph_mai | kdcph_mai | ||
187 | Idle | PX Deq: kdcphc_ack | kdcphc_ack | ||
188 | Idle | PX Deque wait | sleeptime/senderid | passes | |
189 | Idle | PX Idle Wait | sleeptime/senderid | passes | |
190 | Idle | SGA: MMAN sleep for component shrink | component id | current size | target size |
191 | Idle | SQL*Net message from client | driver id | #bytes | |
192 | Idle | SQL*Net message from dblink | driver id | #bytes | |
193 | Idle | Streams AQ: RAC qmn coordinator idle wait | |||
194 | Idle | Streams AQ: deallocate messages from Streams Pool | |||
195 | Idle | Streams AQ: delete acknowledged messages | |||
196 | Idle | Streams AQ: qmn coordinator idle wait | |||
197 | Idle | Streams AQ: qmn slave idle wait | |||
198 | Idle | Streams AQ: waiting for messages in the queue | queue id | process# | wait time |
199 | Idle | Streams AQ: waiting for time management or cleanup tasks | |||
200 | Idle | Streams fetch slave: waiting for txns | |||
201 | Idle | class slave wait | slave id | ||
202 | Idle | dispatcher timer | sleep time | ||
203 | Idle | gcs remote message | waittime | poll | event |
204 | Idle | ges remote message | waittime | loop | p3 |
205 | Idle | i/o slave wait | msg ptr | ||
206 | Idle | jobq slave wait | |||
207 | Idle | parallel recovery coordinator waits for cleanup of slaves | |||
208 | Idle | pipe get | handle address | buffer length | timeout |
209 | Idle | pmon timer | duration | ||
210 | Idle | rdbms ipc message | timeout | ||
211 | Idle | single-task message | |||
212 | Idle | smon timer | sleep time | failed | |
213 | Idle | virtual circuit status | circuit# | status | |
214 | Idle | wait for unread message on broadcast channel | channel context | channel handle | |
215 | Idle | wait for unread message on multiple broadcast channels | channel context | channel handle count | |
216 | Idle | watchdog main loop | |||
217 | Network | ARCH wait for flow-control | |||
218 | Network | ARCH wait for net re-connect | |||
219 | Network | ARCH wait for netserver detach | |||
220 | Network | ARCH wait for netserver init 1 | |||
221 | Network | ARCH wait for netserver init 2 | |||
222 | Network | ARCH wait for netserver start | |||
223 | Network | ARCH wait on ATTACH | |||
224 | Network | ARCH wait on DETACH | |||
225 | Network | ARCH wait on SENDREQ | |||
226 | Network | LGWR wait on ATTACH | |||
227 | Network | LGWR wait on DETACH | |||
228 | Network | LGWR wait on LNS | |||
229 | Network | LGWR wait on SENDREQ | |||
230 | Network | LNS wait on ATTACH | |||
231 | Network | LNS wait on DETACH | |||
232 | Network | LNS wait on LGWR | |||
233 | Network | LNS wait on SENDREQ | |||
234 | Network | SQL*Net message to client | driver id | #bytes | |
235 | Network | SQL*Net message to dblink | driver id | #bytes | |
236 | Network | SQL*Net more data from client | driver id | #bytes | |
237 | Network | SQL*Net more data from dblink | driver id | #bytes | |
238 | Network | SQL*Net more data to client | driver id | #bytes | |
239 | Network | SQL*Net more data to dblink | driver id | #bytes | |
240 | Network | TCP Socket (KGAS) | |||
241 | Network | TEXT: URL_DATASTORE network wait | |||
242 | Network | dedicated server timer | wait event | ||
243 | Network | dispatcher listen timer | sleep time | ||
244 | Other | ARCH wait for archivelog lock | |||
245 | Other | ARCH wait for process death 1 | |||
246 | Other | ARCH wait for process death 2 | |||
247 | Other | ARCH wait for process death 3 | |||
248 | Other | ARCH wait for process death 4 | |||
249 | Other | ARCH wait for process death 5 | |||
250 | Other | ARCH wait for process start 1 | |||
251 | Other | ARCH wait for process start 2 | |||
252 | Other | ARCH wait for process start 3 | |||
253 | Other | ARCH wait for process start 4 | |||
254 | Other | ARCH wait on c/f tx acquire 1 | |||
255 | Other | ARCH wait on c/f tx acquire 2 | |||
256 | Other | ASM background running | |||
257 | Other | ASM background starting | |||
258 | Other | ASM db client exists | |||
259 | Other | ASM internal hang test | test # | ||
260 | Other | AWR Flush | |||
261 | Other | AWR Metric Capture | |||
262 | Other | BFILE check if exists | |||
263 | Other | BFILE check if open | |||
264 | Other | BFILE closure | |||
265 | Other | BFILE get length | |||
266 | Other | BFILE get name object | |||
267 | Other | BFILE get path object | |||
268 | Other | BFILE internal seek | |||
269 | Other | BFILE open | |||
270 | Other | CGS skgxn join retry | retry count | ||
271 | Other | CGS wait for IPC msg | |||
272 | Other | Cluster Suspension wait | |||
273 | Other | Cluster stablization wait | |||
274 | Other | DBFG waiting for reply | |||
275 | Other | DBMS_LDAP: LDAP operation | |||
276 | Other | DFS db file lock | file# | ||
277 | Other | DFS lock handle | type_mode | id1 | id2 |
278 | Other | Data Guard broker: single instance | Data Guard broker: single instance | ||
279 | Other | Data Guard broker: wait upon ORA-12850 error | waiting for retrying the query to mask ORA-12850 error | ||
280 | Other | Data Guard: process clean up | |||
281 | Other | Data Guard: process exit | |||
282 | Other | FAL archive wait 1 sec for REOPEN minimum | |||
283 | Other | GCS lock cancel | le | ||
284 | Other | GCS lock cvt S | group | obj# | block# |
285 | Other | GCS lock cvt X | group | obj# | block# |
286 | Other | GCS lock esc | group | obj# | block# |
287 | Other | GCS lock esc X | group | obj# | block# |
288 | Other | GCS lock open | group | obj# | block# |
289 | Other | GCS lock open S | group | obj# | block# |
290 | Other | GCS lock open X | group | obj# | block# |
291 | Other | GCS recovery lock convert | group | obj# | block# |
292 | Other | GCS recovery lock open | group | obj# | block# |
293 | Other | GV$: slave acquisition retry wait time | |||
294 | Other | IPC busy async request | |||
295 | Other | IPC send completion sync | send count | ||
296 | Other | IPC wait for name service busy | |||
297 | Other | IPC waiting for OSD resources | |||
298 | Other | KJC: Wait for msg sends to complete | msg | dest_rcvr | mtype |
299 | Other | Kupp process shutdown | nalive | sleeptime | loop |
300 | Other | L1 validation | seghdr | l1bmb | |
301 | Other | LGWR simulation latency wait | |||
302 | Other | LGWR wait for redo copy | copy latch # | ||
303 | Other | LGWR wait on full LNS buffer | |||
304 | Other | LGWR-LNS wait on channel | |||
305 | Other | LMON global data update | |||
306 | Other | LNS simulation latency wait | |||
307 | Other | LNS wait for LGWR redo | |||
308 | Other | Logical Standby Apply shutdown | |||
309 | Other | Logical Standby Terminal Apply | stage | ||
310 | Other | Logical Standby dictionary build | |||
311 | Other | Logical Standby pin transaction | xidusn | xidslt | xidsqn |
312 | Other | MMON (Lite) shutdown | process# | waited | |
313 | Other | MMON slave messages | |||
314 | Other | MRP wait on archivelog archival | |||
315 | Other | MRP wait on archivelog arrival | |||
316 | Other | MRP wait on archivelog delay | |||
317 | Other | MRP wait on process death | |||
318 | Other | MRP wait on process restart | |||
319 | Other | MRP wait on process start | |||
320 | Other | MRP wait on startup clear | |||
321 | Other | MRP wait on state change | |||
322 | Other | MRP wait on state n_a | |||
323 | Other | MRP wait on state reset | |||
324 | Other | OLAP Aggregate Client Deq | sleeptime/senderid | passes | |
325 | Other | OLAP Aggregate Client Enq | sleeptime/senderid | passes | |
326 | Other | OLAP Aggregate Master Deq | sleeptime/senderid | passes | |
327 | Other | OLAP Aggregate Master Enq | sleeptime/senderid | passes | |
328 | Other | OLAP Null PQ Reason | sleeptime/senderid | passes | |
329 | Other | OLAP Parallel Temp Grew | sleeptime/senderid | passes | |
330 | Other | OLAP Parallel Temp Grow Request | sleeptime/senderid | passes | |
331 | Other | OLAP Parallel Temp Grow Wait | sleeptime/senderid | passes | |
332 | Other | OLAP Parallel Type Deq | sleeptime/senderid | passes | |
333 | Other | PMON to cleanup pseudo-branches at svc stop time | |||
334 | Other | PX Deq Credit: free buffer | sleeptime/senderid | passes | qref |
335 | Other | PX Deq Credit: send blkd | sleeptime/senderid | passes | qref |
336 | Other | PX Deq: OLAP Update Close | sleeptime/senderid | passes | |
337 | Other | PX Deq: OLAP Update Execute | sleeptime/senderid | passes | |
338 | Other | PX Deq: OLAP Update Reply | sleeptime/senderid | passes | |
339 | Other | PX Deq: Signal ACK | sleeptime/senderid | passes | |
340 | Other | PX Deq: Table Q Close | sleeptime/senderid | passes | |
341 | Other | PX Deq: Table Q Get Keys | sleeptime/senderid | passes | |
342 | Other | PX Deq: Table Q qref | sleeptime/senderid | passes | |
343 | Other | PX Deq: Test for msg | sleeptime/senderid | passes | |
344 | Other | PX Deq: reap credit | |||
345 | Other | PX Nsq: PQ descriptor query | |||
346 | Other | PX Nsq: PQ load info query | |||
347 | Other | PX Send Wait | |||
348 | Other | PX create server | nservers | sleeptime | enqueue |
349 | Other | PX qref latch | function | sleeptime | qref |
350 | Other | PX server shutdown | nalive | sleeptime | loop |
351 | Other | PX signal server | serial | error | nbusy |
352 | Other | PX slave connection | |||
353 | Other | PX slave release | |||
354 | Other | RF - FSFO Wait for Ack | |||
355 | Other | RFS announce | |||
356 | Other | RFS attach | |||
357 | Other | RFS close | |||
358 | Other | RFS create | |||
359 | Other | RFS detach | |||
360 | Other | RFS dispatch | |||
361 | Other | RFS ping | |||
362 | Other | RFS register | |||
363 | Other | RVWR wait for flashback copy | copy latch # | ||
364 | Other | Replication Dequeue | sleeptime/senderid | passes | |
365 | Other | SGA: allocation forcing component growth | |||
366 | Other | SGA: sga_target resize | |||
367 | Other | Streams AQ: enqueue blocked due to flow control | |||
368 | Other | Streams AQ: qmn coordinator waiting for slave to start | |||
369 | Other | Streams AQ: waiting for busy instance for instance_name | where | wait_count | |
370 | Other | Streams capture: waiting for archive log | |||
371 | Other | Streams capture: waiting for database startup | |||
372 | Other | Sync ASM rebalance | |||
373 | Other | Wait for TT enqueue | tsn | ||
374 | Other | Wait for shrink lock | object_id | lock_mode | |
375 | Other | Wait for shrink lock2 | object_id | lock_mode | |
376 | Other | Wait on stby instance close | |||
377 | Other | affinity expansion in replay | |||
378 | Other | block change tracking buffer space | |||
379 | Other | buffer busy | group# | obj# | block# |
380 | Other | buffer deadlock | dba | class*10+mode | flag |
381 | Other | buffer dirty disabled | group# | ||
382 | Other | buffer exterminate | file# | block# | buf_ptr |
383 | Other | buffer freelistbusy | group# | obj# | block# |
384 | Other | buffer invalidation wait | group# | obj# | block# |
385 | Other | buffer latch | latch addr | chain# | |
386 | Other | buffer rememberlist busy | group# | obj# | block# |
387 | Other | buffer resize | |||
388 | Other | buffer write wait | group# | obj# | block# |
389 | Other | buffer writeList full | group# | obj# | block# |
390 | Other | change tracking file parallel write | blocks | requests | |
391 | Other | change tracking file synchronous read | block# | blocks | |
392 | Other | change tracking file synchronous write | block# | blocks | |
393 | Other | check CPU wait times | |||
394 | Other | checkpoint advanced | group# | ||
395 | Other | cleanup of aborted process | location | ||
396 | Other | control file diagnostic dump | type | param | |
397 | Other | control file heartbeat | |||
398 | Other | cr request retry | file# | block# | |
399 | Other | cursor: pin S | idn | value | where_sleeps |
400 | Other | cursor: pin X | idn | value | where_sleeps |
401 | Other | debugger command | |||
402 | Other | dispatcher shutdown | waited | ||
403 | Other | dma prepare busy | group | obj# | block# |
404 | Other | dupl. cluster key | dba | ||
405 | Other | enq: AD - allocate AU | name_mode | group and disk number | AU number |
406 | Other | enq: AD - deallocate AU | name_mode | group and disk number | AU number |
407 | Other | enq: AF - task serialization | name_mode | task id | 0 |
408 | Other | enq: AG - contention | name_mode | workspace # | generation |
409 | Other | enq: AM - client registration | name_mode | id1 | id2 |
410 | Other | enq: AM - rollback COD reservation | name_mode | id1 | id2 |
411 | Other | enq: AM - shutdown | name_mode | id1 | id2 |
412 | Other | enq: AO - contention | name_mode | workspace # | object # |
413 | Other | enq: AS - modify service | name_mode | 0 | 0 |
414 | Other | enq: AS - service activation | name_mode | 0 | 0 |
415 | Other | enq: AT - contention | name_mode | 0 | 0 |
416 | Other | enq: AU - audit index file | name_mode | XML audit index file | 0 |
417 | Other | enq: AW - AW generation lock | name_mode | operation | workspace # |
418 | Other | enq: AW - AW state lock | name_mode | operation | workspace # |
419 | Other | enq: AW - AW$ table lock | name_mode | operation | workspace # |
420 | Other | enq: AW - user access for AW | name_mode | operation | workspace # |
421 | Other | enq: BF - allocation contention | name_mode | node#/parallelizer# | bloom# |
422 | Other | enq: BR - file shrink | name_mode | operation | file # |
423 | Other | enq: BR - proxy-copy | name_mode | operation | file # |
424 | Other | enq: CF - contention | name_mode | 0 | operation |
425 | Other | enq: CI - contention | name_mode | opcode | type |
426 | Other | enq: CL - compare labels | name_mode | object # | 0 |
427 | Other | enq: CL - drop label | name_mode | object # | 0 |
428 | Other | enq: CM - gate | name_mode | disk group # | type |
429 | Other | enq: CM - instance | name_mode | disk group # | type |
430 | Other | enq: CN - race with init | name_mode | reg id | 0 |
431 | Other | enq: CN - race with reg | name_mode | reg id | 0 |
432 | Other | enq: CN - race with txn | name_mode | reg id | 0 |
433 | Other | enq: CT - CTWR process start/stop | name_mode | operation | operation parm |
434 | Other | enq: CT - change stream ownership | name_mode | operation | operation parm |
435 | Other | enq: CT - global space management | name_mode | operation | operation parm |
436 | Other | enq: CT - local space management | name_mode | operation | operation parm |
437 | Other | enq: CT - reading | name_mode | operation | operation parm |
438 | Other | enq: CT - state | name_mode | operation | operation parm |
439 | Other | enq: CT - state change gate 1 | name_mode | operation | operation parm |
440 | Other | enq: CT - state change gate 2 | name_mode | operation | operation parm |
441 | Other | enq: CU - contention | name_mode | handle | handle |
442 | Other | enq: DD - contention | name_mode | disk group | type |
443 | Other | enq: DF - contention | name_mode | 0 | file # |
444 | Other | enq: DG - contention | name_mode | disk group | type |
445 | Other | enq: DL - contention | name_mode | object # | 0 |
446 | Other | enq: DM - contention | name_mode | type | type |
447 | Other | enq: DN - contention | name_mode | 0 | 0 |
448 | Other | enq: DP - contention | name_mode | 0 | 0 |
449 | Other | enq: DR - contention | name_mode | 0 | 0 |
450 | Other | enq: DS - contention | name_mode | 0 | 0 |
451 | Other | enq: DT - contention | name_mode | 0 | 0 |
452 | Other | enq: DV - contention | name_mode | object # | 0 |
453 | Other | enq: DX - contention | name_mode | transaction entry # | 0 |
454 | Other | enq: FA - access file | name_mode | disk group number | file number |
455 | Other | enq: FB - contention | name_mode | tablespace # | dba |
456 | Other | enq: FC - open an ACD thread | name_mode | disk group | thread |
457 | Other | enq: FC - recover an ACD thread | name_mode | disk group | thread |
458 | Other | enq: FD - Flashback coordinator | name_mode | Internal | Internal |
459 | Other | enq: FD - Flashback on/off | name_mode | Internal | Internal |
460 | Other | enq: FD - Marker generation | name_mode | Internal | Internal |
461 | Other | enq: FD - Restore point create/drop | name_mode | Internal | Internal |
462 | Other | enq: FD - Tablespace flashback on/off | name_mode | Internal | Internal |
463 | Other | enq: FG - FG redo generation enq race | name_mode | disk group | type |
464 | Other | enq: FG - LGWR redo generation enq race | name_mode | disk group | type |
465 | Other | enq: FG - serialize ACD relocate | name_mode | disk group | type |
466 | Other | enq: FL - Flashback database log | name_mode | Log # | zero |
467 | Other | enq: FL - Flashback db command | name_mode | Log # | zero |
468 | Other | enq: FM - contention | name_mode | 0 | 0 |
469 | Other | enq: FP - global fob contention | name_mode | low file obj add | high file obj add |
470 | Other | enq: FR - contention | name_mode | disk group | unused |
471 | Other | enq: FS - contention | name_mode | 0 | type |
472 | Other | enq: FT - allow LGWR writes | name_mode | disk group | thread |
473 | Other | enq: FT - disable LGWR writes | name_mode | disk group | thread |
474 | Other | enq: FU - contention | name_mode | 0 | 0 |
475 | Other | enq: HD - contention | name_mode | disk group | 0 |
476 | Other | enq: HP - contention | name_mode | tablespace # | dba |
477 | Other | enq: HQ - contention | name_mode | object # | hash value |
478 | Other | enq: HV - contention | name_mode | object # | 0 |
479 | Other | enq: IA - contention | name_mode | 0 | 0 |
480 | Other | enq: ID - contention | name_mode | 0 | 0 |
481 | Other | enq: IL - contention | name_mode | object # | 0 |
482 | Other | enq: IM - contention for blr | name_mode | pool # | 0 |
483 | Other | enq: IR - contention | name_mode | 0 | 0/1 |
484 | Other | enq: IR - contention2 | name_mode | 0 | 0/1 |
485 | Other | enq: IS - contention | name_mode | 0 | type |
486 | Other | enq: IT - contention | name_mode | object # | 0 |
487 | Other | enq: JD - contention | name_mode | 0 | 0 |
488 | Other | enq: JI - contention | name_mode | view object # | 0 |
489 | Other | enq: JQ - contention | name_mode | 0 | 0 |
490 | Other | enq: JS - contention | name_mode | service ID | queue type |
491 | Other | enq: JS - evt notify | name_mode | service ID | queue type |
492 | Other | enq: JS - evtsub add | name_mode | service ID | queue type |
493 | Other | enq: JS - evtsub drop | name_mode | service ID | queue type |
494 | Other | enq: JS - job recov lock | name_mode | service ID | queue type |
495 | Other | enq: JS - job run lock - synchronize | name_mode | service ID | queue type |
496 | Other | enq: JS - q mem clnup lck | name_mode | service ID | queue type |
497 | Other | enq: JS - queue lock | name_mode | service ID | queue type |
498 | Other | enq: JS - sch locl enqs | name_mode | service ID | queue type |
499 | Other | enq: JS - wdw op | name_mode | service ID | queue type |
500 | Other | enq: KK - context | name_mode | 0 | redo thread |
501 | Other | enq: KM - contention | name_mode | type | type |
502 | Other | enq: KP - contention | name_mode | 0 | 0 |
503 | Other | enq: KT - contention | name_mode | plan # | 0 |
504 | Other | enq: MD - contention | name_mode | master object # | 0 |
505 | Other | enq: MH - contention | name_mode | 0 | 0 |
506 | Other | enq: MK - contention | name_mode | 0 | 0 |
507 | Other | enq: ML - contention | name_mode | 0 | 0 |
508 | Other | enq: MN - contention | name_mode | session ID | 0 |
509 | Other | enq: MR - contention | name_mode | 0 or file # | type |
510 | Other | enq: MS - contention | name_mode | master object # | 0 |
511 | Other | enq: MW - contention | name_mode | Schedule Id | 0 |
512 | Other | enq: OC - contention | name_mode | 1 | 2 |
513 | Other | enq: OL - contention | name_mode | hash value | 0 |
514 | Other | enq: OQ - xsoq*histrecb | name_mode | resource id | 0 |
515 | Other | enq: OQ - xsoqhiAlloc | name_mode | resource id | 0 |
516 | Other | enq: OQ - xsoqhiClose | name_mode | resource id | 0 |
517 | Other | enq: OQ - xsoqhiFlush | name_mode | resource id | 0 |
518 | Other | enq: OQ - xsoqhistrecb | name_mode | resource id | 0 |
519 | Other | enq: OW - initialization | name_mode | 0 | 0 |
520 | Other | enq: OW - termination | name_mode | 0 | 0 |
521 | Other | enq: PD - contention | name_mode | property name | key hash |
522 | Other | enq: PE - contention | name_mode | parno | 0 |
523 | Other | enq: PF - contention | name_mode | 0 | 0 |
524 | Other | enq: PG - contention | name_mode | 0 | 0 |
525 | Other | enq: PH - contention | name_mode | 0 | 0 |
526 | Other | enq: PI - contention | name_mode | operation | serial # |
527 | Other | enq: PL - contention | name_mode | 0 | 0 |
528 | Other | enq: PR - contention | name_mode | 0 | 0 |
529 | Other | enq: PS - contention | name_mode | instance | slave ID |
530 | Other | enq: PT - contention | name_mode | disk group # | type |
531 | Other | enq: PV - syncshut | name_mode | 0 | 0 |
532 | Other | enq: PV - syncstart | name_mode | 0 | 0 |
533 | Other | enq: PW - perwarm status in dbw0 | name_mode | 0 | 0 |
534 | Other | enq: RB - contention | name_mode | disk group | 0 |
535 | Other | enq: RF - RF - Database Automatic Disable | name_mode | lock operation | lock value |
536 | Other | enq: RF - RF - FSFO Observed | name_mode | lock operation | lock value |
537 | Other | enq: RF - RF - FSFO connectivity | name_mode | lock operation | lock value |
538 | Other | enq: RF - RF - FSFO state | name_mode | lock operation | lock value |
539 | Other | enq: RF - RF - FSFO synchronization | name_mode | lock operation | lock value |
540 | Other | enq: RF - RF - FSFO wait | name_mode | lock operation | lock value |
541 | Other | enq: RF - atomicity | name_mode | lock operation | lock value |
542 | Other | enq: RF - new AI | name_mode | lock operation | lock value |
543 | Other | enq: RF - synch: DG Broker metadata | name_mode | lock operation | lock value |
544 | Other | enq: RF - synchronization: HC master | name_mode | lock operation | lock value |
545 | Other | enq: RF - synchronization: aifo master | name_mode | lock operation | lock value |
546 | Other | enq: RF - synchronization: chief | name_mode | lock operation | lock value |
547 | Other | enq: RF - synchronization: critical ai | name_mode | lock operation | lock value |
548 | Other | enq: RN - contention | name_mode | thread number | log number |
549 | Other | enq: RP - contention | name_mode | file # | 1 or block |
550 | Other | enq: RS - file delete | name_mode | record type | record id |
551 | Other | enq: RS - persist alert level | name_mode | record type | record id |
552 | Other | enq: RS - prevent aging list update | name_mode | record type | record id |
553 | Other | enq: RS - prevent file delete | name_mode | record type | record id |
554 | Other | enq: RS - read alert level | name_mode | record type | record id |
555 | Other | enq: RS - record reuse | name_mode | record type | record id |
556 | Other | enq: RS - write alert level | name_mode | record type | record id |
557 | Other | enq: RT - contention | name_mode | redo thread | type |
558 | Other | enq: RU - contention | name_mode | 0 | 0 |
559 | Other | enq: RU - waiting | name_mode | 0 | 0 |
560 | Other | enq: RW - MV metadata contention | name_mode | table obj# | 0 |
561 | Other | enq: SB - contention | name_mode | 0 | 0 |
562 | Other | enq: SE - contention | name_mode | Session-id | Serial# |
563 | Other | enq: SF - contention | name_mode | 0 | 0 |
564 | Other | enq: SH - contention | name_mode | 0 | 0 |
565 | Other | enq: SI - contention | name_mode | object # | 0 |
566 | Other | enq: SK - contention | name_mode | tablespace # | dba |
567 | Other | enq: SR - contention | name_mode | operation | sequence # / apply # |
568 | Other | enq: SU - contention | name_mode | table space # | 0 |
569 | Other | enq: SW - contention | name_mode | 0 | 0 |
570 | Other | enq: TA - contention | name_mode | operation | undo segment # / other |
571 | Other | enq: TB - SQL Tuning Base Cache Load | name_mode | 1 | 2 |
572 | Other | enq: TB - SQL Tuning Base Cache Update | name_mode | 1 | 2 |
573 | Other | enq: TC - contention | name_mode | checkpoint ID | 0 |
574 | Other | enq: TC - contention2 | name_mode | checkpoint ID | 0 |
575 | Other | enq: TD - KTF dump entries | name_mode | 0 | 0 |
576 | Other | enq: TE - KTF broadcast | name_mode | 0 | 0 |
577 | Other | enq: TF - contention | name_mode | tablespace # | relative file # |
578 | Other | enq: TL - contention | name_mode | 0 | 0 |
579 | Other | enq: TO - contention | name_mode | object # | 1 |
580 | Other | enq: TQ - DDL contention | name_mode | QT_OBJ# | 0 |
581 | Other | enq: TQ - INI contention | name_mode | QT_OBJ# | 0 |
582 | Other | enq: TQ - TM contention | name_mode | QT_OBJ# | 0 |
583 | Other | enq: TS - contention | name_mode | tablespace ID | dba |
584 | Other | enq: TT - contention | name_mode | tablespace ID | operation |
585 | Other | enq: TX - contention | name_mode | usn<<16 _ slot | sequence |
586 | Other | enq: US - contention | name_mode | undo segment # | 0 |
587 | Other | enq: WA - contention | name_mode | 0 | 0 |
588 | Other | enq: WF - contention | name_mode | 0 | 0 |
589 | Other | enq: WL - contention | name_mode | log # / thread id # | sequence # |
590 | Other | enq: WP - contention | name_mode | 0 | 0 |
591 | Other | enq: WR - contention | name_mode | thread id # | sequence # |
592 | Other | enq: XH - contention | name_mode | 0 | 0 |
593 | Other | enq: XQ - recovery | name_mode | disk group # | unused |
594 | Other | enq: XQ - relocation | name_mode | disk group # | unused |
595 | Other | enq: XR - database force logging | name_mode | operation | 0 |
596 | Other | enq: XR - quiesce database | name_mode | operation | 0 |
597 | Other | enq: XY - contention | name_mode | id1 | id2 |
598 | Other | events in waitclass Other | |||
599 | Other | extent map load/unlock | group | file | extent |
600 | Other | flashback buf free by RVWR | |||
601 | Other | flashback free VI log | |||
602 | Other | flashback log switch | |||
603 | Other | free global transaction table entry | tries | ||
604 | Other | free process state object | |||
605 | Other | gcs ddet enter server mode | |||
606 | Other | gcs domain validation | cluinc | rcvinc | |
607 | Other | gcs drm freeze begin | |||
608 | Other | gcs drm freeze in enter server mode | |||
609 | Other | gcs enter server mode | |||
610 | Other | gcs log flush sync | waittime | poll | event |
611 | Other | gcs remastering wait for read latch | |||
612 | Other | gcs remastering wait for write latch | |||
613 | Other | gcs resource directory to be unfrozen | |||
614 | Other | gcs to be enabled | |||
615 | Other | ges LMD suspend for testing event | |||
616 | Other | ges LMD to inherit communication channels | |||
617 | Other | ges LMD to shutdown | |||
618 | Other | ges LMON for send queues | |||
619 | Other | ges LMON to get to FTDONE | |||
620 | Other | ges LMON to join CGS group | |||
621 | Other | ges cached resource cleanup | waittime | ||
622 | Other | ges cancel | |||
623 | Other | ges cgs registration | where | ||
624 | Other | ges enter server mode | |||
625 | Other | ges generic event | |||
626 | Other | ges global resource directory to be frozen | |||
627 | Other | ges inquiry response | type_mode_where | id1 | id2 |
628 | Other | ges lmd and pmon to attach | |||
629 | Other | ges lmd/lmses to freeze in rcfg - mrcvr | |||
630 | Other | ges lmd/lmses to unfreeze in rcfg - mrcvr | |||
631 | Other | ges master to get established for SCN op | |||
632 | Other | ges performance test completion | |||
633 | Other | ges pmon to exit | |||
634 | Other | ges process with outstanding i/o | pid | ||
635 | Other | ges reconfiguration to start | |||
636 | Other | ges resource cleanout during enqueue open | |||
637 | Other | ges resource cleanout during enqueue open-cvt | |||
638 | Other | ges resource directory to be unfrozen | |||
639 | Other | ges retry query node | |||
640 | Other | ges reusing os pid | pid | count | |
641 | Other | ges user error | error | ||
642 | Other | ges wait for lmon to be ready | |||
643 | Other | ges1 LMON to wake up LMD - mrcvr | |||
644 | Other | ges2 LMON to wake up LMD - mrcvr | |||
645 | Other | ges2 LMON to wake up lms - mrcvr 2 | |||
646 | Other | ges2 LMON to wake up lms - mrcvr 3 | |||
647 | Other | ges2 proc latch in rm latch get 1 | |||
648 | Other | ges2 proc latch in rm latch get 2 | |||
649 | Other | global cache busy | group | file# | block# |
650 | Other | global enqueue expand wait | |||
651 | Other | imm op | msg ptr | ||
652 | Other | inactive session | session# | waited | |
653 | Other | inactive transaction branch | branch# | waited | |
654 | Other | index block split | rootdba | level | childdba |
655 | Other | instance state change | layer | value | waited |
656 | Other | job scheduler coordinator slave wait | |||
657 | Other | jobq slave TJ process wait | |||
658 | Other | jobq slave shutdown wait | |||
659 | Other | kcbzps | |||
660 | Other | kcrrrcp | |||
661 | Other | kdic_do_merge | |||
662 | Other | kfcl: instance recovery | group | obj# | block# |
663 | Other | kgltwait | |||
664 | Other | kjbdomalc allocate recovery domain - retry | |||
665 | Other | kjbdrmcvtq lmon drm quiesce: ping completion | |||
666 | Other | kjbopen wait for recovery domain attach | |||
667 | Other | kjctcisnd: Queue/Send client message | |||
668 | Other | kjctssqmg: quick message send wait | |||
669 | Other | kjudomatt wait for recovery domain attach | |||
670 | Other | kjudomdet wait for recovery domain detach | |||
671 | Other | kjxgrtest | |||
672 | Other | kkdlgon | |||
673 | Other | kkdlhpon | |||
674 | Other | kkdlsipon | |||
675 | Other | kksfbc child completion | |||
676 | Other | kksfbc research | |||
677 | Other | kkshgnc reloop | |||
678 | Other | kksscl hash split | |||
679 | Other | knpc_acwm_AwaitChangedWaterMark | |||
680 | Other | knpc_anq_AwaitNonemptyQueue | |||
681 | Other | knpsmai | |||
682 | Other | ksbcic | |||
683 | Other | ksbsrv | |||
684 | Other | ksdxexeother | |||
685 | Other | ksdxexeotherwait | |||
686 | Other | ksim generic wait event | where | wait_count | |
687 | Other | ksqded | |||
688 | Other | ksv slave avail wait | |||
689 | Other | ksxr poll remote instances | |||
690 | Other | ksxr wait for mount shared | |||
691 | Other | ktfbtgex | tsn | ||
692 | Other | ktm: instance recovery | undo segment# | ||
693 | Other | ktsambl | |||
694 | Other | kttm2d | |||
695 | Other | kupp process wait | |||
696 | Other | kxfxse | kxfxse debug wait: stalling for slave 0 | ||
697 | Other | kxfxsp | kxfxsp debug wait: stalling for slave 0 | ||
698 | Other | latch activity | address | number | process# |
699 | Other | latch free | address | number | tries |
700 | Other | latch: Change Notification Hash table latch | address | number | tries |
701 | Other | latch: KCL gc element parent latch | address | number | tries |
702 | Other | latch: cache buffer handles | address | number | tries |
703 | Other | latch: cache buffers lru chain | address | number | tries |
704 | Other | latch: checkpoint queue latch | address | number | tries |
705 | Other | latch: enqueue hash chains | address | number | tries |
706 | Other | latch: gcs resource hash | address | number | tries |
707 | Other | latch: ges resource hash list | address | number | tries |
708 | Other | latch: messages | address | number | tries |
709 | Other | latch: object queue header heap | address | number | tries |
710 | Other | latch: object queue header operation | address | number | tries |
711 | Other | latch: parallel query alloc buffer | address | number | tries |
712 | Other | latch: redo allocation | address | number | tries |
713 | Other | latch: session allocation | address | number | tries |
714 | Other | latch: undo global data | address | number | tries |
715 | Other | latch: virtual circuit queues | address | number | tries |
716 | Other | library cache revalidation | |||
717 | Other | library cache shutdown | |||
718 | Other | listen endpoint status | end-point# | status | |
719 | Other | lms flush message acks | loc | tries | |
720 | Other | lock close | group | lms# | |
721 | Other | lock deadlock retry | |||
722 | Other | lock escalate retry | |||
723 | Other | lock release pending | group | file# | block# |
724 | Other | log file switch (clearing log file) | |||
725 | Other | log switch/archive | thread# | ||
726 | Other | log write(even) | group# | ||
727 | Other | log write(odd) | group# | ||
728 | Other | master exit | alive slaves | ||
729 | Other | name-service call wait | waittime | ||
730 | Other | no free buffers | group# | obj# | block# |
731 | Other | no free locks | |||
732 | Other | null event | |||
733 | Other | opishd | |||
734 | Other | optimizer stats update retry | |||
735 | Other | pending global transaction(s) | scans | ||
736 | Other | prewarm transfer retry | |||
737 | Other | prior spawner clean up | process_pid | process_sno | |
738 | Other | process shutdown | type | process# | waited |
739 | Other | process startup | type | process# | waited |
740 | Other | process terminate | |||
741 | Other | qerex_gdml | |||
742 | Other | queue slave messages | |||
743 | Other | rdbms ipc message block | |||
744 | Other | rdbms ipc reply | from_process | timeout | |
745 | Other | recovery area: computing applied logs | |||
746 | Other | recovery area: computing backed up files | |||
747 | Other | recovery area: computing dropped files | |||
748 | Other | recovery area: computing identical files | |||
749 | Other | recovery area: computing obsolete files | |||
750 | Other | reliable message | channel context | channel handle | broadcast message |
751 | Other | rfi_drcx_site_del | DRCX waiting for site to delete metadata | ||
752 | Other | rfi_insv_shut | wait for INSV to shutdown | ||
753 | Other | rfi_insv_start | wait for INSV to start | ||
754 | Other | rfi_nsv_deldef | NSVx to defer delete response message post to DMON | ||
755 | Other | rfi_nsv_md_close | NSVx metadata file close wait | ||
756 | Other | rfi_nsv_md_write | NSVx metadata file write wait | ||
757 | Other | rfi_nsv_postdef | NSVx to defer message post to DMON | ||
758 | Other | rfi_nsv_shut | wait for NSVx to shutdown | ||
759 | Other | rfi_nsv_start | wait for NSVx to start | ||
760 | Other | rfi_recon1 | letting site register with its local listener before connect ret | ||
761 | Other | rfi_recon2 | retrying connection for sending to remote DRCX | ||
762 | Other | rfm_dmon_last_gasp | DMON waiting on the last gasp event | ||
763 | Other | rfm_dmon_pdefer | DMON phase deferral wait | ||
764 | Other | rfm_dmon_shut | wait for DMON to shutdown | ||
765 | Other | rfm_dmon_timeout_op | DMON waiting to timeout an operation | ||
766 | Other | rfm_pmon_dso_stall | PMON delete state object stall | ||
767 | Other | rfrdb_dbop | waiting for database to be opened | ||
768 | Other | rfrdb_recon1 | reconnecting back to new primary site during standby viability c | ||
769 | Other | rfrdb_recon2 | waiting for standby database to be mounted | ||
770 | Other | rfrdb_try235 | waiting for retrying the query to mask ORA-235 error | ||
771 | Other | rfrla_lapp1 | waiting for logical apply engine to initialize | ||
772 | Other | rfrla_lapp2 | checking for logical apply engine run-down progress | ||
773 | Other | rfrla_lapp3 | waiting for new primary to initialize tables | ||
774 | Other | rfrla_lapp4 | waiting for v$logstdby_stats view to be initialized | ||
775 | Other | rfrla_lapp5 | waiting to reconnect to primary that is in BUILD_UP | ||
776 | Other | rfrld_rhmrpwait | waiting for MRP0 to stop while reinstating old primary to logica | ||
777 | Other | rfrm_dbcl | RSM notifier: waiting for sql latch on db close | ||
778 | Other | rfrm_dbop | RSM notifier: waiting for sql latch on db open | ||
779 | Other | rfrm_nonzero_sub_count | wait for subscriber count to become nonzero | ||
780 | Other | rfrm_rsm_shut | wait for RSMx processes to shutdown | ||
781 | Other | rfrm_rsm_so_attach | wait for RSMx to attach to state object | ||
782 | Other | rfrm_rsm_start | wait for RSMx processes to start | ||
783 | Other | rfrm_stall | RSM stall due to event RSM_STALL | ||
784 | Other | rfrm_zero_sub_count | wait for subscriber count to become zero | ||
785 | Other | rfrpa_mrpdn | waiting for MRP0 to stop while bringing physical apply engine of | ||
786 | Other | rfrpa_mrpup | waiting for MRP0 to start while bringing physical apply engine o | ||
787 | Other | rfrxpt_pdl | waiting for retrying potential dataloss calculation before switc | ||
788 | Other | rfrxptarcurlog | waiting for logical apply engine to finish initialization | ||
789 | Other | rollback operations active | operation count | ||
790 | Other | rollback operations block full | max operations | ||
791 | Other | rolling migration: cluster quiesce | location | waits | |
792 | Other | scginq AST call | |||
793 | Other | secondary event | event # | wait time | |
794 | Other | select wait | |||
795 | Other | set director factor wait | |||
796 | Other | simulated log write delay | |||
797 | Other | slave exit | nalive | sleeptime | loop |
798 | Other | test long ops | |||
799 | Other | timer in sksawat | |||
800 | Other | transaction | undo seg#_slot# | wrap# | count |
801 | Other | tsm with timeout | |||
802 | Other | txn to complete | |||
803 | Other | unbound tx | |||
804 | Other | undo segment recovery | segment# | tx flags | |
805 | Other | undo_retention publish retry | where | retry_count | |
806 | Other | unspecified wait event | |||
807 | Other | wait active processes | |||
808 | Other | wait for EMON to die | |||
809 | Other | wait for EMON to spawn | |||
810 | Other | wait for FMON to come up | |||
811 | Other | wait for MTTR advisory state object | |||
812 | Other | wait for a paralle reco to abort | |||
813 | Other | wait for a undo record | |||
814 | Other | wait for another txn - rollback to savepoint | |||
815 | Other | wait for another txn - txn abort | |||
816 | Other | wait for another txn - undo rcv abort | |||
817 | Other | wait for assert messages to be sent | |||
818 | Other | wait for change | |||
819 | Other | wait for master scn | waittime | startscn | ackscn |
820 | Other | wait for membership synchronization | |||
821 | Other | wait for message ack | |||
822 | Other | wait for record update | |||
823 | Other | wait for rr lock release | |||
824 | Other | wait for scn ack | pending_nd | scnwrp | scnbas |
825 | Other | wait for split-brain resolution | |||
826 | Other | wait for stopper event to be increased | |||
827 | Other | wait for sync ack | cluinc | pending_nd | |
828 | Other | wait for tmc2 to complete | |||
829 | Other | wait for verification ack | cluinc | pending_insts | |
830 | Other | wait for votes | |||
831 | Other | wait list latch activity | address | number | process# |
832 | Other | wait list latch free | address | number | tries |
833 | Other | waiting to get CAS latch | |||
834 | Other | waiting to get RM CAS latch | |||
835 | Other | writes stopped by instance recovery or database suspension | by thread# | our thread# | |
836 | Scheduler | resmgr:become active | location | ||
837 | Scheduler | resmgr:cpu quantum | location | ||
838 | System I/O | ARCH random i/o | |||
839 | System I/O | ARCH sequential i/o | |||
840 | System I/O | ARCH wait for pending I/Os | |||
841 | System I/O | LGWR random i/o | |||
842 | System I/O | LGWR sequential i/o | |||
843 | System I/O | LNS ASYNC control file txn | |||
844 | System I/O | Log archive I/O | count | intr | timeout |
845 | System I/O | RFS random i/o | |||
846 | System I/O | RFS sequential i/o | |||
847 | System I/O | RFS write | |||
848 | System I/O | RMAN backup & recovery I/O | count | intr | timeout |
849 | System I/O | Standby redo I/O | count | intr | timeout |
850 | System I/O | control file parallel write | files | block# | requests |
851 | System I/O | control file sequential read | file# | block# | blocks |
852 | System I/O | control file single write | file# | block# | blocks |
853 | System I/O | db file parallel write | requests | interrupt | timeout |
854 | System I/O | io done | msg ptr | ||
855 | System I/O | kfk: async disk IO | count | intr | timeout |
856 | System I/O | ksfd: async disk IO | count | intr | timeout |
857 | System I/O | kst: async disk IO | count | intr | timeout |
858 | System I/O | log file parallel write | files | blocks | requests |
859 | System I/O | log file sequential read | log# | block# | blocks |
860 | System I/O | log file single write | log# | block# | blocks |
861 | System I/O | recovery read | |||
862 | User I/O | BFILE read | |||
863 | User I/O | DG Broker configuration file I/O | count | intr | timeout |
864 | User I/O | Data file init write | count | intr | timeout |
865 | User I/O | Datapump dump file I/O | count | intr | timeout |
866 | User I/O | Log file init write | count | intr | timeout |
867 | User I/O | buffer read retry | file# | block# | |
868 | User I/O | db file parallel read | files | blocks | requests |
869 | User I/O | db file scattered read | file# | block# | blocks |
870 | User I/O | db file sequential read | file# | block# | blocks |
871 | User I/O | db file single write | file# | block# | blocks |
872 | User I/O | dbms_file_transfer I/O | count | intr | timeout |
873 | User I/O | direct path read | file number | first dba | block cnt |
874 | User I/O | direct path read temp | file number | first dba | block cnt |
875 | User I/O | direct path write | file number | first dba | block cnt |
876 | User I/O | direct path write temp | file number | first dba | block cnt |
877 | User I/O | local write wait | file# | block# | |
878 | User I/O | read by other session | file# | block# | class# |
- 대기 이벤트를 시스템 커널 레벨에서 표현
- 프로세스가 할 일을 모두 마쳤거나 다른 프로세스를 기다려야 하는 상황에서 CPU를 쥔 채 대기하면 불필요하게 CPY 자원을 낭비하므로
CPU를 OS에 반환하고 Sleep 상태로 빠지는 것을 말함. - 수면에 빠진다는 것은 프로세스가 'wait queue'로 옮겨지는 것을 말하며, 'wait queue'에 놓인 프로세스에게는 CPY를 할당해 줄 필요가
없으므로 OS는 해당 프로세스를 스케쥴링 대상에서 제외시킴 - 선행 프로세스가 일을 마치면 OS에게 그 사실을 알려 자신을 기다리던 수면 상태의 프로세스를 깨우도록 신호를 보냄(interrupted)
그러면 OS는 그 프로세스를 'runnable queue'에 옮김으로써 가능한 한 빨리 CPY를 할다 받아 일을 재개할 수 있도록 스케쥴링 함.
- 프로세스가 할 일을 모두 마쳤거나 다른 프로세스를 기다려야 하는 상황에서 CPU를 쥔 채 대기하면 불필요하게 CPY 자원을 낭비하므로
- 유닉스에서 vmstat 유틸리티를 통해 시스템 상황을 모니터링 한 결과
> vmstat -t 60 1000
- vmstat 명령어
- 서버의 가상 메모리 통계정보를 보기위한 명령어
- vmstat 명령은 커널 스레드, 가상 메모리, 디스크, 트랩 및 CPU 활동에 대한 통계를 보고함.
- vmstat 명령으로 작성된 보고서는 시스템 로드 활동의 균형을 유지하는 데 사용될 수 있으며
이 시스템 전반에 대한 통계(모든 프로세서 중에서의 통계)는 비율로 표시된 값의 평균 또는 합계로 계산됨. - 구문
vmstat [ -f ] [ -i ] [ -s ] [ -I ] [ -t ] [ -v ] [ -w] [ -l ] [ { -p | -P } pagesize | ALL ] ALL ] [ PhysicalVolume ... ] [ Interval [ Count ] ] -t : 출력의 각 행 다음에 시간 소인을 출력하며 시간 소인은 HH:MM:SS 형식으로 표시됨 60 : 60초 간격으로 출력하도록 함 1000 : 한번에 1000개의 로우를 출력하도록 함
- 출력 결과
r b avm fre re pi po fr sr cy in sy cs us sy id wa hr mi se 7 24 3132936 2000 0 0 0 5186 27716 0 5546 40941 14037 40 6 8 45 08:48:06 7 26 3137616 2062 0 1 0 7389 47361 0 5787 42744 17656 41 8 8 43 08:49:06 9 33 3145090 2081 0 1 0 7047 37573 0 5813 57915 20228 43 9 5 44 08:50:06 7 55 3140985 9712 0 1 0 9142 51835 0 8203 39344 20428 37 9 2 52 08:51:06 7 52 3152171 2001 0 1 0 8396 51356 0 7999 40604 20296 36 9 2 53 08:52:06
- 항목 설명
대분류 소분류 설명 kthr r 현재 일을 수행 중이거나 runnable queue에서 CPU 리소스를 기다리는 프로세스 개수 b 샘플 간격을 통해 VMM 대기행렬에서 대기 중인 커널 스레드의 평균 개수(대기 중인 자원, 대기 중인 입출력) memory avm 사용 중인 가상 페이지 fre 사용 가능 리스트의 크기(실제 메모리의 많은 부분이 파일 시스템 데이터를 위한 캐쉬로 이용되며 사용 가능 리스트 크기의 양이 적게 남는 일은 드뭄) page re 호출기 입출력 리스트 pi 페이징 공간에서 들어오는 페이지 po 페이징 공간으로 나가는 페이지 fr 사용 가능 페이지(페이지 교체) sr 페이지 교체 알고리즘에 의해 스캔되는 페이지 cy 페이지 교체 알고리즘에 의한 시간 주기 faults in 장치 인터럽트 sy 시스템 호출 cs 커널 스레드 문맥 전환 cpu us 사용자 시간 sy 시스템 시간 id CPU 유휴 시간 wa 시스템이 미결 디스크/NFS 입출력 요청을 보유하는 동안의 CPU 유휴 시간 time hr mi ss 시분초
2. 대기 이벤트는 언제 발생하나?
1. 자신이 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용중일 때
- 자신이 읽으려는 버퍼에 다른 프로세스가 쓰기 작업을 진행 중이라면 선행 프로세스가 일을 마칠 때까지 기다려야 하며
'buffer busy waits', 'latch free', 'enqueue' 이벤트 등이 여기에 속함
2. 다른 프로세스에 의해 선행작업이 완료되기를 기다릴 때
- DBWR가 Dirty 버퍼를 디스크에 기록할 때, 먼저 LGWR가 로그 버퍼에 있는 Redo Entry를 Redo Log 파일에 기록하는 작업이
선행되야 하는데 이 때 DBWR는 LGWR를 깨워 로그 버퍼를 비우라는 신호를 보내고 LGWR가 일을 마칠 때까지 수면 상태에서
휴식을 취함.
LGWR가일을 마치면 DBWR를 개ㅜ고 자신은 다시 대기 상태로 빠지며, 'write complete waits', 'checkpoint completed',
'log file sync', 'log file switch' 이벤트 등이 여기에 속함
3. 할 일이 없을 때(idle 대기 이벤트)
- 서버 프로세스는 쿼리 결과를 사용자에게 전송하는 동안 Array 단위로 일을 처리하는데, Array 크기만큼 데이터를 전송하면
다음 Fetch Call을 받을 때까지 기다림. 쿼리 결과집합을 모두 전송하고 나서도 다음 Parse Call 또는 Execute Call을 받을
때까지 기다리며 'SQL*Net message from client' 등이 여기에 속함
3. 대기 이벤트는 언제 사라지나?
- 오라클 프로세스가 자신이 작업을 진행할 대 선행 프로세스가 먼저 점유하여 대기 상태에 빠질 경우 선행작업이 끝나고 나서
자신에게 알려주어 일을 진행하거나, 타이머에 설정된 시간에 깨어나 선행 프로세스가 작업을 모두 마쳤는지 확인할 때 사라짐
1. 대기 상태에 빠진 프로세스가 기다리던 리소스를 사용할 수 있게 될 경우
2. 작업을 계속 진행하기 위한 선행작업이 완료될 경우
3. 해야 할 일이 생겼을 경우
4. 래치와 대기 이벤트 개념 명확화
- 래치는 공유된 자원을 읽기 위해서는 반드시 획득해야 하기 때문에 래치가 증가한다고 해서 문제가 되진 않음
- 다만 획득하는 과정에서 다른 프로세스와 경합이 발생되는지를 모니터링 해야 하며 이 때 발생되는 대기 이벤트를 분석해야 함
- 래치 확인
SELECT * FROM (SELECT ADDR, LATCH#, LEVEL#, NAME, GETS, MISSES, SPIN_GETS, SLEEPS FROM V$LATCH ORDER BY 2) WHERE ROWNUM <= 10 ; ADDR LATCH# LEVEL# NAME GETS MISSES SPIN_GETS SLEEPS ---------------- ------ ------ ----------------------- -------- -------- ---------- ------ 0700000010006FB8 0 8 event range base latch 1 0 0 0 0700000010007058 1 8 post/wait queue 1388 17 17 0 07000000100070F8 2 0 hot latch diags 0 0 0 0 07000000100074D0 3 1 process allocation 1064 0 0 0 0700000010007570 4 5 session allocation 16031367 10427696 10426488 1238 0700000010007618 5 0 session switching 1222 0 0 0 07000000100076B8 6 0 process group creation 926 8 8 0 0700000010007758 7 1 session idle bit 111772 91 91 0 0700000010007808 8 7 client/application info 8554 0 0 0 0700000010007EB0 9 8 longop free list parent 39705 0 0 0 ;
항목 | 설명 |
---|---|
ADDR | 래치 오브젝트 주소 |
LATCH# | 래치 번호 |
LEVEL# | 래치 레벨 |
NAME | 래치 이름 |
GETS | 래치 요청 횟수 |
MISSES | 래치를 요청했는데 다른 프로세스에 의해 자원이 사용 중이어서 첫 번째 시도에서 바로 래치를 얻지 못한 횟수 |
SPIN_GETS | 래치를 요청한 첫 번째 시도에서 곧바로 래치를 얻지는 못했지만 이후 spin하는 과정에서 래치 획득에 성공한 횟수 |
SLEEPS | 래치를 요청했는데 자원이 사용 중어어서 곧바로 래치를 얻지 못하고 정해진 횟수만큼 계속 spin 했는데도 결국 래치를 얻지 못해 대기 상태로 빠진 횟수. 이때 발생하는 것이 'latch free' 이벤트. 10g 이후부터는 이 이벤트를 세분화하여 'latch : cache buffers chains', 'latch : library cache lock' |
- 래치 관련 추가 참고 사이트
문서에 대하여
- 최초작성자 : 강정식
- 최초작성일 : 2009년 10월 22일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.
참고자료
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900266&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
Comments (1)
10월 23, 2009
장태길 says:
예전에 원서보면서 vmstat 관련 정리 했던거... 일반적으로 모니터링을 OS / DB / WAS layer 에서 하는게 맞구 DB 에서는 C...예전에 원서보면서 vmstat 관련 정리 했던거...
일반적으로 모니터링을
OS / DB / WAS layer 에서 하는게 맞구
DB 에서는 Contention 이 없어도, OS 에서 Contention 이 발생하거든
EM 에서는 평이하게 나와도 OS 에서는 미친듯시 사용하는게 보이거든
이건 아마두 OS 에서 바라보는 View 와 DB 에서 바라보는 View 다 달라서 인듯..
암튼 내 경험상 그렇더라구..
EM 에서는 아주 깨끗한데.. 서비스가 안된다구 해서 OS 에서 보면 CPU 100% 치구 머 그래..
참고 하삼
What to Look for in vmstat ORACLE TUNING
2006/08/07 20:54
복사 http://blog.naver.com/darkturtle/50007239602
What to Look for in vmstat
: 중요 칼럼만 보고 금방 OS Level 의 Resource 현황을 파악 하고,
문제 없음을 확인 후 DB 나 Application 파악하는 습관을 가지도록 하자
모든 Software 는 OS Base 위에서 구동 된다는 것을 다시금 기억 하자
As you can see, each dialect of vmstat reports different information about the
current status of the server, Despite theses dialect differences, theses are only
a small number of metrics that are important for server monitoring.
Theses metrics include :
waiting for CPU resources. When this number exceeds the number of CPUs on the
server, a CPU bottleneck exists, and some task are waiting for execution.
shotage of RAM memory. While all virtual memory servers will page out to
the swap disk , page-in operations show that that the servers has exceeded
the avaiable RAM storage. Any nonzero value for pi indicate excessive activity
as RAM memory contents are read in from the swap disk.
external operations such as disk I/O
Note that all of the CPU metrics are expressed as percentages. Hence, all of the
CPU values ( us + sy + id + wa ) will always sum to 100.
identifying CPU bottlenecks with vmstat ...
In short, the server is experiencing a CPU bottleneck when r is greater than
the number of CPUs on the server.
저자는 DONALD K. BURLESON
예제 ) AIX 5.3 환경에서 vmstat 명령 결과
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
1 0 451376 1094 0 0 0 0 0 0 415 4513 58225 41 34 25 0
1 0 451436 1034 0 0 0 0 0 0 419 4789 57513 38 38 24 0
CPU Check
Display the Number of CPUs in IBM AIX and HP/UX
lsdev -C | grep Process |wc -l
Display NUmber of CPUs in Solaris
psrinfo -v | grep "Status of processor"| wc -l
Display NUmber of CPUs in Linux
cat /proc/cpuinfo | grep processor | wc -l
Memory Check
uerf -r 300 | grep -i mem
dmesg
1. lsdev -C | grep mem
mem0 Avaiable 00-00 Memory
2. lsattr -El mem0
size 3064 Total amount of physical memory
prtconf | grep -i mem
free
##Using the Top Utility for Displaying RAM
top
CPU waits > 40% ( AIX version only )
This may indicate I/O-based contention. The solution is to spread files
across more disks or add buffer memory
Run queue > xxx ( where xxx is the number of CPUs on the server, 2 in this
example ) This indicates an overloaded CPU. The solution is to add additional
procesor to the server
Page_in > 2 with correlated scan rates
Page-in operates can indicate overlorded memory. The solution is to reduce the
size of the Oracle SGA, PGA, or add additional RAM memory to the server
User CPU + System CPU > 90%
This indicates periods where the CPU is highly utilized.
##
Also, because the MTS uses the shared_pool for process sorting, the DBA
will also see increased demand on the shared_pool.
Connections using the MTS will place the UGA inside the Oracle SGA. To hold
the UGA storage for MTS connections, Oracle has provided the large_pool init.ora
parameter. The LARGE pool is an area of the SGA similar to the SHARED pool,
but with restrictions on its usage such that only certain types and size of
memory can be allocated in this pool.
##
The Oracle DBA needs to consider the RAM cache on the disk array because it
changes the basic nature of disk I/O. When Oracle cannot find a data block in one
of the data buffers in the SGA, Oracle will issue a physical read request to the disk
array. This physical read request is received by the disk array, and the disk RAM cache
is checked for the desired block. if the desired block is in the RAM cache, the disk
arrary will return the block to Oracle without making a physical disk I/O. The fact
that Oracle physical requests may not match actual read requests is a very important
point because it can lead to misleading statistics. For example, the stats$filestatxs
table shows the number of reads and writes to files. If you are using a disk array
such as EMC, these I/O statistics will not correspond to the actual disk reads and
writes. The only conclusive way to check "real" disk I/O is to compare the physical
I/O as measured on the disk array with Oracle's read and write statistics. In many
cases, the disks are performing less than half the I/O reported by Oracle, and this
discrepancy is due to the caching of data blocks on the disk array RAM memory.
RAID 0
RAID 1
RAID 0 + 1 - Best
RAID 5 - in RAID 5, read performance is improved, but every write has to incur the
additional overhead of reading old parity, computing new parity, writing
new parity, and then the actual data, with the last two operations
happending whiel two disk drves are simultaneously locked.
This overhead is known as the RAID-5 write penalty.
Also, if a disk fails in a RAID-5 configuration, the I/O penalty incurred
during the disk rebuild is extremely high.
A raw device is defined as a disk that bypasses the I/O overhead created by
throughput, but only in cases where I/O is already the bottleneck for the Oracle
database. Furthermore, raw devices require a termendous amount of manual work for
the Oracle administrator nad the systems administrator. Oracle recommends that
raw devices should only be considered when the Oracle database is I/O bound.
However, for these types of Oracle database, raw device can dramatically improve
overall performance. if the database is not I/O bound, switching to raw devices
will have no impact on performance.
####
As u may know, the pctfree parameter tells Oracle when to unlink a data
block from the freelists and the pctused parameter and pctused parameter
tells Oracle when to relink a data block back onto the freelist. The values
for pctfree and pctused are heavily dependent upon row length and the desired
reserved space for row expansion.
Over the past several years, Oracle has gradually recongnized the benefits
of bitmap data structures. As Oracle hea evolved, we see the following
progressive introduction of bitmaps :
Locally Managed Tablespace
Oracle automatically allocates new space management blocks when a new
extent is created and maintains internal pointer to the bitmap blocks
This happens when blocks on the freelist only have room for a few rows
before Oracle is forced to grab another free block.
rows. This occurs when rows "chain" and fragment onto several data blocks,
causing additional I/O to fetch the blocks.
of I/O. This happens when update operations expaned a VARCHAR or BLOB cloumn
and Oracle is foreced to chain the row contents onto additional data blocks.
header contention. This happens when rows are deleted and Oracle must
relink the data block onto the freelist for the table .
####
The purpose of pctfree is to tell Oracle when to remove a block from the
object's freelists. Since the Oracle default is pctfree = 10, blocks remain
on the freelist while they are less than 90 percent full.
The pctused parameter tells Oracle when to add a previously full block
onto the freelists. As rows are deleted from a table, the database blocks
become eligible to accept new rows.
This happens when the amount of space in a database block falls below
putused, and a freelist relink operation is triggered.
The freelists parameter tells Oracle how many segment header blocks to
create for a table or index. Multiple freelists are used to prevent segment
header contention when several tasks competer to INSERT,UPDATE, or DELETE
from the table. The freelists parameter should be set to the maximum number
of concurrent update operations.
The freelists groups parameter is used in Oracle Parallel Server
(Real Application Clusters )
The freelists groups parameter should be set to the number of instance
that access the table.
For efficient space reuse
: A high value for pctused will effectively reuse space on data blocks,
but at the expense of additional I/O. A high pctused means that relatively
full blocks are placed on the freelist. Hence, these blocks will be able
to accept only a few rows before becoming full again, leading to more I/O.
For High performance
: A low value for pctused means that Oracle wiil not place a data block
onto the freelist until it is nearly empty. The block will be able to accept
many rows until it become full, thereby reducing I/O at insert time.
Remember that is always faster for Oracle to extent into new blocks than to
reuses existing blocks. it takes fewer resources for Oracle to extend
a table than to manage freelists.
The presence of chained rows in a table means that pctfree is too low or
that db_block_size is too small.
Whenever a request is made to insert a row into a table, Oracle goes to a
freelist to find a block with enough space to accept a row. As you may know,
the freelist chanin is kept in the first block of the table or index, and
this block is known as the segment header. The sole purpose of the pctfree
and pctused table allocation parameters is to controlthe movement of blocks
to and from freelists. While freelist link and unlink operations are simple
Oracle function, the setting for freelist link (pctused) and unlink(pctfree)
operations can have a dramatic impact on the performance of Oracle.
As you may know, linking and unlinking a block requires two writes: one to
the segment header for the freelist head node, and the other to the new
block to make it participate in the freelist chain. The following general
rules apply to freelists:
1. Oracle detects that free space is than pctfree for block 20 and invokes
the unlink operation. Since block 20 is the first block on the freelists chain,
Oracle read s the data block address(DBA) inside the block header and sees
that the next free block is block 60.
2. Oracle next adjust the freelist header node and moves the DBA for block
60 to the head of the freelist in the segment header. Block 20 no longer
participates in the freelist chain, and the firsth entry in the freelist
is now block 60.
Because the updated block is not at the head of the freelist chain, the prior
block's freelist pointer cannot be adjusted to omit the block. Note that the
dead block remains oh the freelist even though it does not have room to
accept a row.
The dead block remaining oh the list will cause additional Oracle overhead,
especially if there is a large number of "unavaiable" blocks on the freelist.
Freelists relinks can be reduced in two ways:
pctused to a low value means that data blocks are not relinked onto the
freelist chain unless they are completely empty.
you tell Oracle to bypass the freelists and raise the high-water mark for the
table to grab a fresh, unused data block.
This first-in, fist-out linking method allows for very fast unlinking and
relinking of data blocks from the freelist.
Noete that the segment header nad data block waits are often related to
competing update tasks that have to wait on a single freelist in the
segment header.
We are discussing buffer waits now because buffer waits are usually associated
with segment header contetion that can be remedied by adding additional
freelists for the table or index.
Let's remember that a buffer busy wait occurs when a database block is found
in the data buffer but it is unavailable because another Oracle task is using
the data block.
As you may know, you can easily rebuild on Oracle index with the following
command :
ALTER INDEX index_name REBUILD TABLESPACE tablespace_name ;
We might want to rebuild an index if the block gets per access is greater
than 5, since excessive block gets indicate a fragmented B-tree structure.
Another rebuild condition would be cases where deleted leaf nodes comprise
more than 20 percent of the index nodes. Aother reason to rebuild is
when any index shows a depth of 4 or greater.
NOTE
It is interesting to note that Oracle indexing has change dramatically
between Oracle7 and Oracle8. While the functions of the indexes remain
the same, the sizes of the indexes have been reduced by almost 50 percent.
The rebuild command will perform the following steps:
and rename the temporary segments to the new index.
!! 실제 online rebuild (8i) 시에 문제 발생 한다. !!
Index rebuilding with the alter index rebuild is a very safe command.
If anything goes wrong, Oracle aborts the operation and leaves the existing
index in place.
Most Oracle administrators run this scripts and then select the index that
they would like ot rebuild. Note that the TABLESPACE clause should always be
used with the alter index rebuild command to eusure that the index is rebuilt
in the same tablespace as the existing index. Be aware that you must have
enough room in that tablespace to hold all of temporary segments required
for the index rebuild, so most Oracle DBAs will double-size index tablespaces
with enough space for two full index trees.
Also note that if you use the PARALLEL option to increase speed when rebuilding
indexes, each parallel process will require enough space for the indexes'initial
extent size. Hence, if you rebuild indexes with PARALLEL DEGREE 4, you must
have four times the space in the target tablespace.
One of the most serious causes of poor DML performance is the existence of
unused index.
alter index index_name monitoring usage ;
cat /etc/oratab|grep ^$ORACLE_SID:|cut -f1 -d':'
cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'
uname -a|awk '
[WEBINT]sel550b:/app/oracle/tg/STATSPACK> sqlplus -
SQL*Plus: Release 10.1.0.4.0 - Production
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-C <v>] [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_identifier>] | / | /NOLOG
<start> ::= @<URL>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-C" sets SQL*Plus compatibility version <v>
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode
It is also imortant to remember that the tuning of SQL statements must
come after the tuning of the instance and the objects, for several
very goo reasons.
For more details about tuning Oracle SQL, see the Oracle Press book
Oracle High-Performance SQL Tuning, by Don Burleson.
The goals of SQL tuning are simple :
:Unnecesary full table scans cause a huge amount of unnecessary I/O, and
can drag down an entire database. The tuning expert evaluates the SQL based
on the number of rows returned by the query, If the query returns less than
40 percent of the table rows in an ordered table, or 7 percent of the
rows in an unordered table, the query can be tuned to use and index in lieu
of the full table scan.
The most common tuning for unnecessary full table scans is adding indexes.
Standard B-tree indexes can be added to tables, and bitmappd and function-based
indexes can also eliminate full table scans.
The decision to remove a full table scan should be base on a careful examination
of the I/O costs the index scan vs. the costs of the full table scan, factoring
in the multiblock reads and possible parallel execution. in some cases an
unnecessary full table scan can be forced to use an index by adding an index
hint to the SQL statement.
: In cases where a full table scan is the fastest access method, the tuning
professional should ensure that a dedicated data buffer is avaiable for the
rows, In Oracle7 you can issue alter table xxx cache. In Oracle8 and beyond,
the small table can be cached by forcing it into the KEEP pool.
: This is especially important for improving the speed of queries. Oracle
somethimes has a choice of index, and the tuning professional must examine
each index and ensure that Oracle is using the proper index. This also includes
the use of bitmapped and function-based indexes.
: Some queries will perform faster with NESTED LOOP joins, other with HASH joins.
Caution
: The use of any SQL hint except the RULE hint forces the cost-based optimizer
to be invoked. Hence, make sure you have analyzed your tables and indexes
prior to using any hints.
Rule-Based Shortcomings
: Often chooses the wrong index to retrieve rows.
Cost-Based shortcomings
: Often performs unnecessary full tables scans, especially when more than
three are being joined.
Most Oracle SQL queries will only be retrieving a small subset of the rows
within the table and full table scans are only appropriate when more than
40 percent of the table rows are required.
Hence, the general guideline for replacing an index range scan is:
: Queries that retrieve less than 40 percent of the table rows
should use an index range scan. Conversely, queries that read more than
40 percent of the rows should use a full table scan.
: Queries that retrieve less than 7 percent of the table should use and
index range scan. Conversely, queries that read more than 7 percent of
the table rows will probably be faster with a full table scan.
Also, unlinke the cost-based optimizer, the order of tables in the FROM
clause and the order of Booleans in the WHERE clause affecting the
execution plan for the query.
Changing the Rule-Based Driving Table
In Oracle's rule-based optimizer, the ordering of the table names in the
FROM cluase determines the driving table. The driving table is important
because it it retrieved first, and the rows from the second table are then
merged into the result set from the first table. Therefore, it is essential
that the second table return the least amount of rows based on the WHERE
clause.
With the rule-based optimizer, the table names are read from right to left.
Hence, the last table in the FROM clause should be the table that returns
the smallest number of rows.
When the Rule-Based Optimizer Fails to Use the Correct Index
: In Sum, we need to pay careful attention to the indexes that are chosen
by the rule-based optimizer, and either disable the indexes that we do not
want used in the query or force the use the index that we want. To review,
indexes can be explicitly specified with the INDEX hint, or unwanted indexes
can be disabled by mixing datatypes on the index
(i.e. WHERE numeric_column_value=123||''), or by specifying a FULL hint in
the query.
Tuning with Cost-Based Optimization ( CBO )
alter session set optimizer goal = rule
Here is a list of common hints that are used to change the execution plan
in the cost-based optimizer:
are specified ( left to right ). For example, if you know that a stat table has
only 50 rows, you may want to use this hint to make state the driving table.
that the table_name query be executed in parallel mode with degree processes
servicing the table access.
table as the driving table.
However, there are some general rules that can be applied :
benefit from the rule-based optimizer or the first_rows hint.
the cost-based optimizer
Miscellaneous Tuning Techniques
Before we go into detail on the process of tuning, let's look at several
important ways to tune individual SQL Statements. These topics include :