by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

대기이벤트




목차

1. 대기 이벤트(Wait Event)란?

2. 대기 이벤트는 언제 발생하나?

3. 대기 이벤트는 언제 사라지나?

4. 래치와 대기 이벤트 개념 명확화

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를 할다 받아 일을 재개할 수 있도록 스케쥴링 함.
  • 유닉스에서 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'

문서에 대하여

참고자료

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  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 :

    1. r ( runeueue) : The runqueue value show s the number of tasks executing and
      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.
    1. pi ( Page in) A page-in operation occurs when the server is experiencing a
      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.
    1. us ( user CPU ) This is the amount of CPU that is servicing user tasks.
    1. sy ( system CPU ) This is the percentage of CPU being used to service system tasks.
    1. id ( idle ) This is percentage of CPU that is idle.
    1. wa ( wait-IBM-AIX only ) This shows the percentage of CPU that is waiting on
      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.

      1. 출처 : Oracle 9i High Performance Tuning with STATSPACK

    저자는 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

      1. Display RAM Size on DEC-UNIX
        uerf -r 300 | grep -i mem
      1. Display RAM Size on HP/UX
        dmesg
      1. Display RAM Size on AIX
        1. lsdev -C | grep mem
        mem0 Avaiable 00-00 Memory
        2. lsattr -El mem0
        size 3064 Total amount of physical memory
      1. Display RAM Size on Solaris
        prtconf | grep -i mem
      1. Display RAM Size in Linux
        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 :

    • Bitmap Indexes - Oracle 7.3.3.
    • Locally Managed Tablespaces - Oracle 8.0
    • Bitmap segment management - Oracle 9i

    Locally Managed Tablespace

    • SEGMENT_SPACE_MANAGEMENT - AUTO ==> Bitmap space management
    • UNIFORM
    • EXTENT_MANAGEMENT - DICTIONARY
    • LOCAL
      Oracle automatically allocates new space management blocks when a new
      extent is created and maintains internal pointer to the bitmap blocks
    • Slow inserts : insert operations run slowly and have excessive I/O.
      This happens when blocks on the freelist only have room for a few rows
      before Oracle is forced to grab another free block.
    • Slow selects : Select statements have excessive I/O because of chanined
      rows. This occurs when rows "chain" and fragment onto several data blocks,
      causing additional I/O to fetch the blocks.
    • Slow updates : Updates statements run very slowly with double the amount
      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.
    • Slow deletes : Large delete statements can run slowly and cause segment
      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:

    • Freelists relinks can be "turned down" by setting pctused to 1. Setting
      pctused to a low value means that data blocks are not relinked onto the
      freelist chain unless they are completely empty.
    • Use the APPEND hint when adding rows, By using APPEND with inserts,
      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:

    • Walk the existing index to get the index keys.
    • Populate temporary segments with the new tree structure
    • Once the operation has completed successfully, drop the old tree,
      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 ;

    • This command only works in Oracle9i or later.
      1. 스크립트에서 유용한 command line
    1. Oracle SID
      cat /etc/oratab|grep ^$ORACLE_SID:|cut -f1 -d':'
    1. Oracle Home
      cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'
    1. Server Name
      uname -a|awk '
      Unknown macro: {print $2}
      '

    [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 :

    • Remove unnecessary large-table full scans
      :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.
    • Cache small-table full table scans
      : 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.
    • Verify optimal index usage
      : 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.
    • Verify optimal JOIN techniques
      : 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:

    • For row-sequenced tables
      : 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.
    • For unordered tables
      : 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:

    • HASH_AJ : This hint is palced in a NOT IN subquery to perform a hash anti-JOIN.
    • MERGE_AJ : This hint is placed in a NOT IN subquery to perform an anti-JOIN.
    • ORDERED : REquests that the tables should be joined in the order that they
      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.
    • PARALLEL(table_name degreee) : For full table scans, this hint requests
      that the table_name query be executed in parallel mode with degree processes
      servicing the table access.
    • USE_CONCAT : Request that a UNION ALL be used for all OR conditions.
    • USE_NL(table_name) : Requests a nested loop operation with the specified
      table as the driving table.

    However, there are some general rules that can be applied :

    • Pre-Oracle8i queries that join three or more large tables will generally
      benefit from the rule-based optimizer or the first_rows hint.
    • Queries that access bitmapped or function-based indexes will benefit from
      the cost-based optimizer
    • Queries that use start query hints need the cost-based optimizer.
    • Databases at Oracle8i and beyond will benefit from 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 :

    • Tuning with hints
    • Tuning subqueries
    • The problem of literal SQL statements
    • Tuning with temporary tables
    • General rules for writing efficient SQL