{"id":1580,"date":"2018-01-23T17:52:49","date_gmt":"2018-01-23T08:52:49","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1580"},"modified":"2020-03-10T09:59:41","modified_gmt":"2020-03-10T00:59:41","slug":"oracle-wait-event-%eb%aa%a8%eb%8b%88%ed%84%b0%eb%a7%81","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1580","title":{"rendered":"Oracle Wait Event \ubaa8\ub2c8\ud130\ub9c1"},"content":{"rendered":"

Oracle Wait Event \ubaa8\ub2c8\ud130\ub9c1
\n<\/strong><\/span><\/p>\n

\uc21c\uc11c<\/strong>
\n<\/span><\/p>\n

[1] Oracle Wait Event \ubaa8\ub2c8\ud130\ub9c1<\/strong><\/span>
\n[2] Enqueue\uc640 Latch <\/span>
\n[3] Shared Pool \uad00\ub828 Wait Event <\/span><\/a>
\n[4] buffer cache \uad00\ub828 Wait Event <\/span><\/a>
\n[5] redo log \uad00\ub828 Wait Event<\/span><\/a>
\n[6] Top SQL \ud29c\ub2dd<\/span><\/a><\/span><\/p>\n\n\n<\/colgroup>\n\n\n
<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

[1] Oracle Wait Event \ubaa8\ub2c8\ud130\ub9c1<\/p>\n

<\/strong>\ud754\ud788 DBA\ub97c 3D\uc5c5\uc885\uc774\ub77c\uace0 \ubd80\ub974\ub294 \uc774\uc720 \uac00\uc6b4\ub370 \ud558\ub098\ub294 \ubab8\uc740 \uace0\ub2ec\ud508\ub370 \ubc18\ud574 \uadf8 \uc131\uacfc\uac00 \ubcc4\ub85c \ud2f0\uac00 \ub098\uc9c0 \uc54a\ub294\ub2e4\ub294 \uc0ac\uc2e4 \ub54c\ubb38\uc77c \uac83\uc774\ub2e4. \uc2e4\uc81c\ub85c, DBA\uac00 \uc218\ud589\ud574\uc57c \ud558\ub294 \uc77c\uc0c1\uc801\uc778 \uad00\ub9ac \uc5c5\ubb34\ub4e4\uc740 \ubab8\uc740 \ub2e4\uc18c \ud53c\uace4\ud558\uac8c \ub9cc\ub4e4\uc9c0 \ubab0\ub77c\ub3c4 \uc5b4\ub290 \uc815\ub3c4 \uacbd\ud5d8\uc774 \uc313\uc774\uba74 \uadf8\ub9ac \ubd80\ub2f4\uc744 \uc8fc\ub294 \uc77c\uc740 \uc544\ub2c8\ub2e4. \uc6b0\ub9ac\uac00 \ud55c\ub2e8\uacc4 \uc5c5\uadf8\ub808\uc774\ub4dc\ub41c \uc804\ubb38\uac00\ub85c\uc11c \uc778\uc815 \ubc1b\ub294 DBA\uac00 \ub418\uae30 \uc704\ud574\uc11c\ub294 \uc7a5\uc560\uc0c1\ud669 \ud639\uc740 \uc720\uc0ac \uc7a5\uc560 \uc0c1\ud669\uc5d0\uc11c DB \ubaa8\ub2c8\ud130\ub9c1 \uc791\uc5c5\uc744 \uc218\ud589\ud558\uace0 \ubd84\uc11d\ud560 \uc218 \uc788\uc5b4\uc57c \ud55c\ub2e4. \uc2dc\uc2a4\ud15c\uc774 \uac11\uc790\uae30 \ub290\ub824\uc9c0\uace0 \uc5c5\ubb34\uac00 \ub9c8\ube44\ub418\ub294 \uc0c1\ud669\uc5d0 \ub9de\ub2e5\ub728\ub838\uc744 \ub54c \ubb38\uc81c\uc758 \uc6d0\uc778\uc774 \ubb34\uc5c7\uc778\uc9c0\ub97c \uc9d1\uc5b4\ub0bc \uc218 \uc788\ub294 \ub2a5\ub825\uc774 \uc788\uc5b4\uc57c \ud558\uba70 \ucd5c\uc18c\ud55c \uc624\ub77c\ud074\uc758 \ubb38\uc81c\uc778\uc9c0 \uc544\ub2cc\uc9c0\ub97c \ud310\ub2e8\ud560 \uc218\ub294 \uc788\uc5b4\uc57c \ubab8\uc73c\ub85c \uc57c\uac04\uc791\uc5c5\uc774\ub098 \ub54c\uc6b0\ub294 DBA\uac00 \uc544\ub2c8\ub77c \uc870\uc9c1\uc5d0 \uc5c6\uc5b4\uc11c\ub294 \uc548\ub420 \uc804\ubb38\uac00\ub85c\uc11c\uc758 \ub098\uc758 \uc874\uc7ac\uac00\uce58\ub97c \uc778\uc815 \ubc1b\uc744 \uc218 \uc788\uc744 \uac83\uc774\ub2e4.<\/p>\n

\uc774 \uae00\uc5d0\uc11c\ub294 \uc624\ub77c\ud074 Wait Event\uc5d0 \ub300\ud558\uc5ec \uac04\ub2e8\ud788 \uc54c\uc544\ubcf4\uace0 \uc77c\uc2dc\uc801\uc778 \uc131\ub2a5\uc800\ud558 \uc0c1\ud669\uc5d0\uc11c Wait Event\ub97c \ubaa8\ub2c8\ud130\ub9c1\ud558\uace0 \uadf8 \uc6d0\uc778\uc744 \ucc3e\uc544\uac00\ub294 \ubc29\ubc95\uc5d0 \ub300\ud558\uc5ec \ub2e4\ub8e8\uc5b4 \ubcf4\uace0\uc790 \ud55c\ub2e4. \uc9e7\uc740 \uc9c0\uba74 \uc704\uc5d0 \ub2e4\ub8f0 \uc218 \uc788\ub294 \ub0b4\uc6a9\ub3c4 \uc81c\ud55c\ub418\uc5b4 \uc788\uace0 \uae00\uc4f4\uc774\uc758 \uc9c0\uc2dd \ub610\ud55c \uc77c\ucc9c\ud558\uc9c0\ub9cc \uc624\ub77c\ud074 \uc804\ubb38\uac00\uac00 \ub418\uae30 \uc704\ud574 \uac19\uc740 \uae38\uc744 \uac00\uace0 \uc788\ub294 \ub3d9\ub8cc\ub85c\uc11c \uac00\uc9c4 \uc9c0\uc2dd \uba87 \uac00\uc9c0 \uacf5\uc720\ud55c\ub2e4\ub294 \ucde8\uc9c0\ub85c \uc774 \uae00\uc744 \uc4f4\ub2e4.<\/p>\n

\uc624\ub77c\ud074\uc758 Wait Event \uc815\ubcf4\ub294 V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT \ub4f1\uc774 \uc788\ub294\ub370, \uc774 \uac00\uc6b4\ub370 V$SESSION_WAIT\ub294 \uac01 \uc138\uc158\uc774 \ud604\uc7ac Waiting \ud558\uace0 \uc788\ub294 Event\ub098 \ub9c8\uc9c0\ub9c9\uc73c\ub85c Wait\ud55c Event \uc815\ubcf4\ub97c \ubcf4\uad00\ud558\uace0 \uc788\uc73c\uba70, V$SYSTEM_EVENT\uc640 V$SESSION_EVENT\ub294 \uc2dc\uc2a4\ud15c\uc774 Startup\ub41c \uc774\ud6c4 \uac01\uac01 \uc2dc\uc2a4\ud15c \uc804\uccb4, \ud639\uc740 \uc138\uc158\ubcc4\ub85c \ubc1c\uc0dd\ud55c Wait Event \uc815\ubcf4\ub97c \ub204\uc801\ud558\uc5ec \uae30\ub85d\ud558\uace0 \uc788\ub2e4.<\/p>\n

\uc624\ub77c\ud074\uc758 Wait Event\ub294 \uc131\uaca9\uc5d0 \ub530\ub77c Network\uad50\uc2e0\uc774\ub098 IO\ub97c \uc704\ud574 \ub300\uae30\ud558\ub294 \uc77c\uc0c1\uc801\uc778 Wait\uc640 \ud2b9\uc815 \uc790\uc6d0\uc5d0 \ub300\ud574 \uc5ec\ub7ec \ud504\ub85c\uc138\uc2a4\uac00 \ub3d9\uc2dc\uc5d0 \uc561\uc138\uc2a4\ud558\uace0\uc790 \ud560 \ub54c \ubc1c\uc0dd\ud558\ub294 Wait, \ubcc4\ub2ec\ub9ac \ud560 \uc77c\uc774 \uc5c6\uc5b4 \ub300\uae30\ud558\uace0 \uc788\ub294 Idle Wait \ub4f1 \uc138\uac00\uc9c0 \uc720\ud615\uc73c\ub85c \uad6c\ubd84\ud560 \uc218 \uc788\ub294\ub370 \uadf8 \uc720\ud615\uc5d0 \ub530\ub77c \ud574\uc11d\ubc29\ubc95\ub3c4 \ub2ec\ub77c\uc9c4\ub2e4. \uc77c\ub2e8, Idle Wait\ub294 \uc77c\ubc18\uc801\uc778 \uad00\uc2ec\uc758 \ub300\uc0c1\uc5d0\uc11c \uc81c\uc678\ub418\uba70 IO\ub098 Network \uad00\ub828 Wait\ub294 \uc791\uc5c5\ub7c9\uc774 \uc99d\uac00\ud558\uba74 \uac19\uc774 \uc99d\uac00\ud558\ub294 Wait\uc774\ubbc0\ub85c \uc804\uccb4 \uc11c\ube44\uc2a4 \uc2dc\uac04(CPU time)\uacfc \ube44\uad50\ud558\uc5ec \uc0c1\ub300\uc801\uc73c\ub85c \ud3c9\uac00\ud574\uc57c \ud558\uba70 \ucd1d Wait time\ubcf4\ub2e4\ub294 \ud3c9\uade0 Wait Time\uc5d0 \uad00\uc2ec\uc744 \ub450\uace0 \ubd84\uc11d\uc744 \ud574\uc57c \ud560 \uac83\uc774\ub2e4. \uc2dc\uc2a4\ud15c \uc790\uc6d0\uc5d0 \ub300\ud55c Wait\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc11c\ubc84 \ud29c\ub2dd\uc2dc \uac00\uc7a5 \uc8fc\ub41c \uad00\uc2ec \ub300\uc0c1\uc774 \ub418\uba70 \uc774\ub4e4 Wait\uc5d0 \ub300\ud574\uc11c\ub294 \ud3c9\uade0 Wait Time\ubfd0\ub9cc \uc544\ub2c8\ub77c \ucd1d Wait Time\uc5d0\ub3c4 \uad00\uc2ec\uc744 \uac00\uc9c0\uace0 \ubd84\uc11d\ud574\uc57c \ud560 \uac83\uc774\ub2e4. \uc720\ud615\ubcc4\ub85c \ub300\ud45c\uc801\uc778 Wait Event\ub97c \uc0b4\ud3b4\ubcf8\ub2e4\uba74 \uc544\ub798\uc640 \uac19\ub2e4.<\/p>\n

[\uc8fc\uc694 Wait Event]<\/strong><\/p>\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\uad6c\ubd84<\/strong><\/span><\/td>\n\n

\uc774\ubca4\ud2b8\uba85<\/strong><\/span><\/p>\n<\/td>\n

\n

\uc124 \uba85<\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n

\uc77c\uc0c1\uc801\uc778 Wait Event <\/span><\/td>\ndb file scattered read<\/span><\/td>\nFull Scan\uc2dc OS\uc5d0 I\/O\ub97c \uc694\uccad\ud574\ub193\uace0 \ub300\uae30<\/span><\/td>\n<\/tr>\n
db file sequential read<\/span><\/td>\nIndex Scan\uc2dc OS\uc5d0 I\/O\ub97c \uc694\uccad\ud574\ub193\uace0 \ub300\uae30<\/span><\/td>\n<\/tr>\n
(IO, Network)<\/span><\/td>\nlog file sync<\/span><\/td>\n\ubcc0\uacbd log buffer\ub97c log file\uc5d0 \ubc18\uc601\ud558\ub294 \ub3d9\uc548 \ub300\uae30<\/span><\/td>\n<\/tr>\n
DFS lock handle<\/span><\/td>\nOPS \ud658\uacbd\uc5d0\uc11c \ub178\ub4dc\uac04 \ubd84\uc0b0 Lock \uad50\ud658\uc5d0 \ub530\ub978 \ub300\uae30<\/span><\/td>\n<\/tr>\n
global cache cr request<\/span><\/td>\nOPS \ud658\uacbd\uc5d0\uc11c \ub178\ub4dc\uac04 Buffer Block \uad50\ud658\uc5d0 \uc758\ud55c \ub300\uae30<\/span><\/td>\n<\/tr>\n
\uc790\uc6d0 \uacbd\ud569\uc5d0 \ub530\ub978
\nWait Event\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span><\/td>\n		enqueue<\/span><\/td>\nType\uc5d0 \ub530\ub77c \uc138\ubd84\ud654 (24\uac1c\uc758 enqueue type (9i))<\/span><\/td>\n<\/tr>\n
latch free<\/span><\/td>\nName\uc5d0 \ub530\ub77c \uc138\ubd84\ud654 (239\uac1c\uc758 latch\uac00 \uc874\uc7ac (9i))<\/span><\/td>\n<\/tr>\n
buffer busy waits<\/span><\/td>\n\ub3d9\uc77c\ube14\ub85d\uc5d0 \ub300\ud55c \ub3d9\uc2dc \uc561\uc138\uc2a4\uc5d0 \ub530\ub978 \uacbd\ud569<\/span><\/td>\n<\/tr>\n
free buffer waits<\/span><\/td>\nfree buffer\ub97c \ud560\ub2f9\uc704\ud574 DBWR\uc758 Write\ub97c \ub300\uae30<\/span><\/td>\n<\/tr>\n
Log buffer space<\/span><\/td>\nLog buffer\ub97c \ud560\ub2f9 \ubc1b\uae30 \uc704\ud574 LGWR\uc758 write\ub97c \ub300\uae30<\/span><\/td>\n<\/tr>\n
library cache lock<\/span><\/td>\nSGA\ub0b4\uc758 library cache\ub97c \ucc38\uc870\ud558\uae30 \uc704\ud55c \ub300\uae30(\uac80\uc0c9)<\/span><\/td>\n<\/tr>\n
row cache lock<\/span><\/td>\nSGA\ub0b4\uc758 dictionary cache\ub97c \ucc38\uc870\ud558\uae30 \uc704\ud55c \ub300\uae30<\/span><\/td>\n<\/tr>\n
Idle Event\u00a0<\/span><\/td>\nSQL*Net message from client<\/span><\/td>\nClient\ub85c\ubd80\ud130\uc758 \uc791\uc5c5\uc694\uccad\uc744 \ub300\uae30<\/span><\/td>\n<\/tr>\n
Pmon timer<\/span><\/td>\nPMON\uc774 \ud560\uc77c \uc5c6\uc744 \ub54c \ub300\uae30\ud558\ub294 Event<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\uc5c5\ubb34\uc2dc\uac04\ub300\uc5d0 \uc2dc\uc2a4\ud15c\uc774 \uac11\uc790\uae30 \ub290\ub824\uc84c\ub2e4\uba74\uc11c \uc624\ub77c\ud074 \uc11c\ubc84\uc5d0 \ubb38\uc81c\uac00 \uc5c6\ub294\uc9c0 \ubb38\uc758\uac00 \ub4e4\uc5b4\uc624\uba74 \uae00\uc4f4\uc774\ub294 \uc6b0\uc120 \uc544\ub798\uc758 SQL\uc744 \uc218\ud589\uc2dc\ucf1c\ubcf8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
select \/*+ ordered \/ distinct \/* \uc18d\ub3c4\ub97c \uc704\ud574 v$sql\uc744 \uc870\uc778\ud560 \uacbd\uc6b0 \uc911\ubcf5\ub418\ub294 \ub808\ucf54\ub4dc \uc81c\uac70 *\/
\ns.sid SID, s.username, s.program, p.spid “OS-Pid”,w.seconds_in_wait as “W_time(Sec)”,
\ndecode(w.wait_time,0,’Wai-ting’, ‘Waited’) Status, w.ename event,
\n— \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p1text || ‘:’ || decode(event,’latch free’,p1raw, to_char(p1)) ||’,’||
\n— \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p2text || ‘:’ || to_char(p2) ||’,’|| p3text || ‘:’ || to_char(p3) “Additional Info”,
\nq.sql_text
\nfrom ( select a.*, decode(a.event,’latch free’, ‘latch free (‘ ||b.name||’)’,
\n‘row cache lock’, ‘row cache lock (‘ || c.parameter || ‘)’,
\n‘enqueue’, ‘enqueue (‘||chr(bitand(p1, -16777216)\/16777215)||
\nchr(bitand(p1,16711680)\/65535)||’:’||
\ndecode(bitand(p1,65535), 1, ‘N’, 2, ‘SS’,3,’SX’,4,’S’,5,’SSX’,6,’X’) ||’)’,
\na.event ) ename
\nfrom v$session_wait a, v$latchname b, v$rowcache c
\nwhere a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) = ‘PARENT’
\nand a.event not in (‘rdbms ipc message’,’smon timer’,’pmon timer’,’slave wait’,’pipe get’,’null event’,
\n‘SQL*Net message from client’, ‘SQL*Net message to client’,’PX Idle Wait’,
\n‘PX Deq: Execution Msg’, ‘KXFQ: kxfqdeq – normal deqeue’,
\n‘ges remote message’, ‘wakeup time manager’, \/* idle event \uc801\uc808\ud788 \uc218\uc815 *\/
\n‘lock manager wait for remote message’, ‘single-task message’)
\n) w, v$session s, v$process p, v$sql q
\nwhere w.sid = s.sid and s.paddr = p.addr
\nand s.sql_hash_value = q.hash_value(+) and s.sql_address = q.address(+)
\norder by w.ename;<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

SQL\uc758 \uad6c\uccb4\uc801\uc778 \ub0b4\uc6a9\uc774\uc57c \ud544\uc694\ud55c \uc815\ubcf4\uc640 \uac1c\uc778\uc801 \ucde8\ud5a5\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c0\uaca0\uc9c0\ub9cc, \uc911\uc694\ud55c \uac83\uc740 \uc77c\ub2e8 V$SESSION_WAIT \ubdf0\ub85c\ubd80\ud130 \uc2e4\uc2dc\uac04 Wait Event \uc815\ubcf4\ub97c \uc5bb\uc5b4\ub0b8\ub2e4\ub294 \uac83\uc774\ub2e4. \uc704 SQL\uc744 \uc218\ud589\ud588\uc744 \ub54c \ub098\ud0c0\ub098\ub294 \uacb0\uacfc\uac00 \uc5c6\ub2e4\uba74 \uc77c\ub2e8 \uc624\ub77c\ud074 \uce21\uba74\uc5d0\uc11c \uc5c5\ubb34\uc131\ub2a5\uc744 \uc2ec\uac01\ud558\uac8c \ub9c8\ube44\uc2dc\ud0a4\ub294 Waiting\uc774 \ubc1c\uc0dd\ud558\uace0 \uc788\uc9c0 \uc54a\ub2e4\uace0 \ubd10\ub3c4 \ud070 \ubb34\ub9ac\uac00 \uc5c6\uc744 \uac83\uc774\ub2e4.<\/p>\n

\uc77c\ubc18\uc801\uc778 \uc0c1\ud0dc\uc5d0\uc11c\ub294 \uc8fc\ub85c ‘db file sequential read’\ub098 ‘db file scattered read’ \uac00 \ub098\ud0c0\ub0a0 \ud150\ub370, \uc774\ub7ec\ud55c Wait Event\ub294 \ubcf4\ud1b5 \uc9e7\uc740 \uc2dc\uac04 \ub3d9\uc548 \uc9c0\uc18d\ub418\uba70 \ub300\uc0c1 \uc790\uc6d0(\ube14\ub85d)\uc744 \ubc14\uafd4\uac00\uba70 Wait\uac00 \ubc18\ubcf5\ub418\ub294 \ud615\ud0dc\ub85c \ub098\ud0c0\ub0a0 \uac83\uc774\ub2e4. \uc774\ub294 \uc791\uc5c5 \ucc98\ub9ac\ub7c9\uc774 \ub9ce\uc744 \ub54c \uc77c\uc0c1\uc801\uc73c\ub85c \ubc1c\uc0dd\ud558\ub294 IO\uad00\ub828 Wait Event\uc774\ubbc0\ub85c \ud574\ub2f9 \uc138\uc158\uc5d0\uc11c IO\ub97c \uc81c\ubc95 \ub9ce\uc774 \uc720\ubc1c\ud558\uace0 \uc788\ub2e4\ub294 \uc815\ub3c4\ub85c \uc774\ud574\ud558\uace0 \ub118\uc5b4\uac00\uba74 \ub420 \uac83\uc774\ub2e4. \ubb3c\ub860, Wait\uc758 \uc9c0\uc18d\uc2dc\uac04\uc774 \uae38\uac70\ub098 \uc9c0\ub098\uce58\uac8c \ube48\ubc88\ud788 \ub098\ud0c0\ub098\ub294 SQL\uc5d0 \ub300\ud574\uc11c\ub294 \ube44\ud6a8\uc728\uc801\uc778 \uc2e4\ud589\uacc4\ud68d\uc744 \uc218\ub9bd\ud558\uace0 \uc788\uc9c0 \uc54a\uc740\uc9c0 \uac80\ud1a0\ud574\uc11c \ud29c\ub2dd\ud574 \uc8fc\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n

\uc131\ub2a5\uc800\ud558\uc758 \uc6d0\uc778\uc774 \uc624\ub77c\ud074 \ucabd\uc5d0 \uc788\ub294 \uacbd\uc6b0\uc5d0\ub294 \ud2b9\uc815 \uc790\uc6d0\uc5d0 \ub300\ud55c Waiting\uc774 \uc0c1\ub2f9\ud788 \uc624\ub7ab\ub3d9\uc548 \uc9c0\uc18d\ub418\uc5b4 \ud604\uc7ac\uae4c\uc9c0 Waiting\uc774 \uc9c4\ud589 \uc911\uc778 \uc138\uc158\ub4e4(STATUS\uac00 ‘Wai-ting’ (wait_time=0)\uc774\uba70 ‘W_time(sec)’ (seconds_in_wait) \uac12\uc774 \uc0c1\ub2f9\ud788 \ud070 \uc138\uc158)\uc774 \uc874\uc7ac\ud560 \uac00\ub2a5\uc131\uc774 \ub192\ub2e4. \uc624\ub77c\ud074\uc758 \ub0b4\ubd80\uc801\uc778 \uc791\uc5c5\ub4e4\uc740 \ub9e4\uc6b0 \uc9e7\uc740 \uae30\uac04\uc5d0 \ucc98\ub9ac\ub418\uc5b4\uc57c \ud558\ubbc0\ub85c, Idle event(where\uc808\uc5d0\uc11c not in\uc73c\ub85c \ucc98\ub9ac\ud55c \ubd80\ubd84, \ubc84\uc804\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c8 \uc218 \uc788\ub2e4.) \uc774\uc678\uc758 \ud2b9\uc815 Wait Event\uac00 \ub208\uc5d0 \ub74c \uc815\ub3c4\ub85c \uac80\ucd9c\ub41c\ub2e4\ub294 \uac83\uc740 \uc624\ub77c\ud074 \ub0b4\ubd80\uc801\uc73c\ub85c\ub294 \ud6e8\uc52c \ub354 \ub9ce\uc740 Waiting\uc774 \ubc1c\uc0dd\ud558\uace0 \uc788\ub2e4\uace0 \uc0dd\uac01\ud574\uc57c \ud55c\ub2e4. \ubc14\ub85c \uc774\ub7f0 \uc138\uc158\ub4e4\uc774 \ubb38\uc81c\uc758 \ubc94\uc778\ub4e4\uc774\uba70 \uc774\uc81c\ubd80\ud130 DBA\ub294 \uc774\ub4e4 Wait Event\uc5d0 \ub300\ud55c \uc6d0\uc778\uc744 \ud30c\uc545\ud558\uc5ec \uc870\uce58\ud558\ub294 \uc791\uc5c5\uc744 \ud574\uc8fc\uc5b4\uc57c \ud55c\ub2e4. \uac01\uac01\uc758 Wait Event\uc5d0 \ub530\ub77c \uc6d0\uc778\uc744 \ucd94\uc801\ud558\uace0 \uc870\uce58\ud558\ub294 \ubc29\ubc95\uc740 \ub2ec\ub77c\uc9c8 \uac83\uc774\ub2e4.<\/p>\n

\ub2e4\uc74c \ud638\uc5d0\uc11c\ub294, \uc790\uc8fc \uacbd\ud5d8\ud558\ub294 \uba87\uac00\uc9c0 \ub300\ud45c\uc801\uc778 Wait Event\ub4e4\uc5d0 \ub300\ud558\uc5ec SGA \uc601\uc5ed\ubcc4\ub85c \uad6c\ubd84\ud558\uc5ec \uc880 \ub354 \uc790\uc138\ud788 \uc0b4\ud3b4\ubcf4\uace0, \uadf8\uc5d0 \uc55e\uc11c Lock \ub610\ub294 Latch Event\uc758 \uc774\ud574\ub97c \uc704\ud574 \ud544\uc694\ud55c Enqueue\uc640 Latch\uc758 \uac1c\ub150\uc744 \uac04\ub2e8\ud788 \uc54c\uc544\ubcf4\ub3c4\ub85d \ud558\uaca0\ub2e4.<\/p>\n

[2] Enqueue\uc640 Latch \uac1c\ub150 \uc774\ud574\ud558\uae30<\/p>\n

DBMS\uc758 \uac00\uc7a5 \uc8fc\ub41c \uae30\ub2a5 \uc911\uc5d0 \ud558\ub098\ub294 \ub3d9\uc77c \uc790\uc6d0\uc5d0 \ub300\ud55c \ub3d9\uc2dc \uc561\uc138\uc2a4\ub97c \uad00\ub9ac\ud558\ub294 \uac83\uc774\uba70, \uc774\ub97c \uc704\ud574 \uc624\ub77c\ud074\uc774 \uc0ac\uc6a9\ud558\ub294 \ub300\ud45c\uc801\uc778 \uc81c\uc5b4 \uad6c\uc870\uac00 Enqueue\uc640 Latch\uc774\ub2e4.
\nEnqueue\uc640 Latch\ub294 \ubaa8\ub450 \ud2b9\uc815 \uc790\uc6d0\uc5d0 \ub300\ud55c \uc811\uadfc\uc744 serialize\ud558\ub294 \uac83\uc774 \ubaa9\uc801\uc774\ub77c\ub294 \uc810\uc5d0\uc11c\ub294 \uac19\uc740 Lock\uc758 \uc77c\uc885\uc774\uc9c0\ub9cc \uad00\ub9ac\ubc29\uc2dd\uc774\ub098 \uc6a9\ub3c4\uc5d0\uc11c \ucc28\uc774\uac00 \uc788\ub2e4. Enqueue\ub294 \uc774\ub984\uc5d0\uc11c \ubcf4\ub4ef Queue\ub97c \ud1b5\ud574 \uad00\ub9ac\ub41c\ub2e4. \ub300\uc0c1 \uc790\uc6d0\uc5d0 \ub300\ud55c Owner, Waiter, Converter Queue\ub97c \uad00\ub9ac\ud558\uba74\uc11c \uba3c\uc800 \uc694\uccad\ud55c \uc21c\uc11c\ub300\ub85c Lock\uc744 \ud68d\ub4dd\ud558\ub3c4\ub85d \ud558\ub294 \uad6c\uc870\uc774\uba70, Exclusive \ubaa8\ub4dc \ubfd0 \uc544\ub2c8\ub77c \ub2e4\uc591\ud55c \uc218\uc900\uc758 \uacf5\uc720\ub97c \ud5c8\uc6a9\ud55c\ub2e4. \ub300\ud45c\uc801\uc778 \uac83\uc774 \ud14c\uc774\ube14 \ub370\uc774\ud130\ub97c Update\ud560 \ub54c \uc0ac\uc6a9\ub418\ub294 TM, TX enqueue\uc774\ub2e4.<\/p>\n

\ubc18\uba74\uc5d0, Latch\ub294 Enqueue\uc5d0 \ube44\ud574 \ud6e8\uc52c \ub2e8\uc21c\ud55c \uad6c\uc870\ub85c\uc11c \ub9e4\uc6b0 \uc9e7\uc740 \uc2dc\uac04 \ub0b4\uc5d0 \ud68d\ub4dd\ub418\uace0 \ud574\uc81c\ub41c\ub2e4. Queue\ub97c \ud1b5\ud574 \uad00\ub9ac\ub418\uc9c0 \uc54a\uc73c\ubbc0\ub85c \uba3c\uc800 Request\ud55c \ud504\ub85c\uc138\uc2a4\uac00 \uba3c\uc800 latch\ub97c \ud68d\ub4dd\ud55c\ub2e4\ub294 \ubcf4\uc7a5\uc774 \uc5c6\uc73c\uba70, \ub300\ubd80\ubd84\uc758 \uacbd\uc6b0 Exclusive\ubaa8\ub4dc\ub85c\ub9cc \ud68d\ub4dd\ub41c\ub2e4. Latch\ub294 \uc8fc\ub85c SGA\uc758 \ud2b9\uc815 \uba54\ubaa8\ub9ac \uad6c\uc870\uccb4\uc5d0 \ub300\ud55c \uc561\uc138\uc2a4(library cache latch, cache buffers chains latch) \ud639\uc740 \uba54\ubaa8\ub9ac \ud560\ub2f9 \uc2dc (shared pool latch) \uc0ac\uc6a9\ub418\uac70\ub098 \uc624\ub77c\ud074\uc758 \uc911\uc694\ud55c \ucf54\ub4dc\uac00 \ub3d9\uc2dc\uc5d0 \uc218\ud589\ub418\uc9c0 \uc54a\ub3c4\ub85d \ud558\uae30 \uc704\ud55c \uc6a9\ub3c4\ub85c(redo writing latch) \uc0ac\uc6a9\ub41c\ub2e4. Latch\ub294 Enqueue\ubcf4\ub2e4\ub294 \ud558\uc704 level\uc5d0\uc11c Locking \uc790\uccb4\uc758 \ubd80\ud558\ub97c \ucd5c\uc18c\ud654\ud558\uba70 \uc791\ub3d9\ud558\ub294 \uc81c\uc5b4 \uba54\ucee4\ub2c8\uc998\uc774\ub77c\uace0 \ud560 \uc218 \uc788\uc73c\uba70, \uc2e4\uc81c\ub85c Enqueue \uc5ed\uc2dc \ub0b4\ubd80\uc801\uc73c\ub85c\ub294 Latch (enqueues, enqueue hash chains latch )\uc5d0 \uc758\ud574 \uc6b4\uc601\ub41c\ub2e4\ub294 \uc810\uc744 \uc0dd\uac01\ud558\uba74 \ub458 \uc0ac\uc774\uc758 \ucc28\uc774\ub97c \uc27d\uac8c \uc774\ud574\ud560 \uc218 \uc788\uc744 \uac83\uc774\ub2e4.<\/p>\n

\u25a0 Enqueue<\/p>\n

Enqueue \uc815\ubcf4\ub294 \ub0b4\ubd80\uc801\uc73c\ub85c Enqueue Resource \ubc30\uc5f4\uacfc Enqueue Lock \ubc30\uc5f4\uc5d0 \uc800\uc7a5\ub41c\ub2e4. \ud2b9\uc815 \uc790\uc6d0\uc5d0 \ub300\ud55c Lock\uc774 \uc694\uccad\ub418\uba74 \ub300\uc0c1\uc744 \ud558\ub098\uc758 Resource\ub85c \uc815\uc758\ud558\uc5ec \ud560\ub2f9\ud558\uace0 \uadf8 Resource\uc5d0 \ub300\ud574 \uad00\ub828 Lock \uc815\ubcf4\ub97c Owner, Waiter, Converter\uac00\uc6b4\ub370 \ud558\ub098\ub85c\uc11c Link\uc2dc\ud0a4\ub294 \ubc29\uc2dd\uc73c\ub85c \uc6b4\uc601\ub418\uba70, \uc774\ub7ec\ud55c \uc815\ubcf4\ub294 V$RESOURCE\uc640 V$LOCK \ubdf0\ub97c \ud1b5\ud574 \uc870\ud68c\ud574 \ubcfc \uc218 \uc788\ub2e4. V$RESOURCE\uc640 V$LOCK\uc740 1:M \uad00\uacc4\ub85c \ud558\ub098\uc758 Resource\uc5d0 \ub300\ud558\uc5ec \uc5ec\ub7ec \uac74\uc758 Lock \ub808\ucf54\ub4dc\uac00 Owner (LMODE>0, REQUEST=0), Waiter (LMODE=0 ,REQUEST>0), Converter (LMODE>0, REQUEST>0) \uc911 \ud558\ub098\ub85c\uc11c \ub300\uc751\ub41c\ub2e4.
\nEnqueue Wait\uc774 \ubc1c\uc0dd\ud558\ub294 \uac83\uc740 \ub2e4\ub978 \uc138\uc158\uc774 \uc774\ubbf8 \ub098\ubcf4\ub2e4 \uba3c\uc800 \ud574\ub2f9 \uc790\uc6d0\uc5d0 \ub300\ud55c Lock\uc744 \uc7a1\uace0 \uc788\uc73c\ubbc0\ub85c \uc778\ud574 \ub0b4\uac00 \uc6d0\ud558\ub294 \ubaa8\ub4dc\ub85c Lock\uc744 \ud560\ub2f9 \ubc1b\uc744 \uc218 \uc5c6\uae30 \ub54c\ubb38\uc774\ub2e4. \uc790\uc2e0\uc774 \ud544\uc694\ub85c \ud558\ub294 Lock\uc758 \ud68d\ub4dd\uc5d0 \uc2e4\ud328\ud55c \uc138\uc158\uc740 Owner\uac00 \uc791\uc5c5\uc744 \uc644\ub8cc\ud558\uace0 \uc790\uc2e0\uc744 \uae68\uc6cc\uc904 \ub54c\uae4c\uc9c0(\uc138\ub9c8\ud3ec\uc5b4\ub97c \ud3ec\uc2a4\ud2b8\ud574\uc904 \ub54c\uae4c\uc9c0) Waiter \ud639\uc740 Converter Queue\uc5d0\uc11c \ub300\uae30\ud558\uac8c \ub418\uba70, \uae30\ub2e4\ub824\ub3c4 \uc18c\uc2dd\uc774 \uc5c6\uc73c\uba74 3\ucd08 \uac04\uaca9\uc73c\ub85c timeout\uc5d0 \uc758\ud574 \uc77c\uc5b4\ub098 \ud639\uc2dc Deadlock \uc0c1\ud669\uc774 \uc544\ub2cc\uc9c0 \uc810\uac80\ud574 \ubcf8 \ud6c4 \ub2e4\uc2dc Sleep\uc5d0 \ube60\uc838\ub4e4\uae30\ub97c \ubc18\ubcf5\ud558\uac8c \ub41c\ub2e4. \ud29c\ub2dd\uad00\ub828 \uc790\ub8cc\ub97c \ubcf4\ub2e4 \ubcf4\uba74 \uac00\ub054 Enqueue\uc5d0 \ub300\ud55c Wait\uc774 \ub9ce\uc740 \uacbd\uc6b0\uc5d0 Enqueue_resource\ub098 Enqueue_lock \ud30c\ub77c\ubbf8\ud130\ub97c \uc99d\uac00\uc2dc\ucf1c \uc8fc\uc5b4\uc57c \ud55c\ub2e4\ub294 \uac00\uc774\ub4dc\ub97c \ubcf4\uac8c \ub418\ub294 \uacbd\uc6b0\uac00 \uc788\ub294\ub370 \uc774 \ud30c\ub77c\ubbf8\ud130\ub4e4\uc740 Enqueue resource\uc640 lock \ubc30\uc5f4\uc758 \ud06c\uae30\ub97c \ub298\ub824\uc904 \ubfd0 \ud2b9\uc815 Enqueue \uc790\uc6d0\uc5d0 \ub300\ud55c \ub3d9\uc2dc \uacbd\ud569\uc744 \ud574\uc18c\uc2dc\ud0a4\ub294 \uac83\uacfc\ub294 \uc0c1\uad00\uc774 \uc5c6\ub2e4. Enqueue Wait\ub97c \ud574\uc18c\ud558\uae30 \uc704\ud55c \uad6c\uccb4\uc801\uc778 \ubc29\ubc95\uc740 Enqueue type\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c0\uc9c0\ub9cc \uacb0\uad6d\uc740 Enqueue\ub97c \ubd88\ud544\uc694\ud558\uac8c \uc694\uccad\ud558\ub294 \uacbd\uc6b0\uac00 \uc5c6\ub294\uc9c0\ub97c \uc0b4\ud3b4 Enqueue\uc5d0 \ub300\ud55c \uc694\uccad\uc744 \ucd5c\uc18c\ud654\ud558\uace0 Enqueue\ub97c \uc810\uc720\ud558\ub294 \uc2dc\uac04\uc744 \ucd5c\ub300\ud55c \ub2e8\ucd95\uc2dc\ud0a4\ub294 \uac83\uc774\ub2e4. TX Enqueue\uc5d0 \ub300\ud55c Wait\uc740 \ub300\uc0c1 \uc790\uc6d0\uc5d0 \ub300\ud55c Lock\uc744 \uc18c\uc720\ud558\uace0 \uc788\ub294 \uc138\uc158\uacfc \uadf8 \uc138\uc158\uc774 \uc218\ud589 \uc911\uc778 SQL\uc744 \ucc3e\uc544 \ud2b8\ub79c\uc7ad\uc158\uc774 \uc7a5\uc2dc\uac04 \uc9c0\uc18d\ub418\uace0 \uc788\ub294 \uc774\uc720\uac00 \ubb34\uc5c7\uc778\uc9c0 \uc560\ud50c\ub9ac\ucf00\uc774\uc158 \uce21\uba74\uc5d0\uc11c \uc870\uc0ac\ud574\uc57c \ud558\uba70, SQ enqueue\ub294 Sequence \uac12 \ud560\ub2f9 \uc2dc \ubc1c\uc0dd\ud558\ub294 \uacbd\ud569\uc774\ubbc0\ub85c cache\uac12\uc744 \ub298\ub824\uc90c\uc73c\ub85c\uc368 \uc644\ud654\uc2dc\ud0a8\ub2e4\uac70\ub098 ST Enqueue\uc758 \uacbd\ud569\uc774 \uc874\uc7ac\ud560 \uacbd\uc6b0\uc5d0\ub294 Locally managed tablespace\ub97c \uc0ac\uc6a9\ud558\uac70\ub098 Initial, Next \ub4f1\uc758 extent \ud06c\uae30\ub97c \uc801\ub2f9\ud55c \uac12\uc73c\ub85c \uc870\uc815\ud558\uc5ec \uc2e4\uc2dc\uac04 \uacf5\uac04\ud560\ub2f9\uc744 \uac10\uc18c\uc2dc\ucf1c\uc8fc\ub294 \ub4f1\uc758 \ubc29\ubc95\ub4e4\uc774 Enqueue Wait\uc5d0 \ub300\ucc98\ud558\ub294 \ub300\ud45c\uc801\uc778 \uc0ac\ub840\uc774\ub2e4. \uc9c0\ub09c \ud638\uc5d0\uc11c \uc18c\uac1c\ud55c Session Waiter \uc2a4\ud06c\ub9bd\ud2b8\ub294 Enqueue Wait \uc774\ubca4\ud2b8\uc5d0 \ub300\ud574\uc11c Enqueue type\uacfc \ubaa8\ub4dc\ub97c \ud568\uaed8 \ud45c\uc2dc\ud558\uc5ec \uc8fc\ub3c4\ub85d \ud558\uace0 \uc788\uc73c\uba70, \ucc38\uace0\ub85c Enqueue type\ubcc4 \ub204\uc801 Wait\ud604\ud669\uc744 \ud655\uc778\ud558\uace0\uc790 \ud558\uba74 \uc544\ub798 SQL\uc744 \uc218\ud589\ud558\uba74 \ub41c\ub2e4.<\/p>\n

select q.ksqsttyp type,
\nq.ksqstget gets,
\nq.ksqstwat waits,
\nround(q.ksqstwat\/q.ksqstget,3) waitratio
\nfrom sys.x$ksqst q
\nwhere q.inst_id = userenv(‘Instance’)
\nand q.ksqstget > 0
\norder by waits desc
\n\/<\/p>\n

\u25a0 Latch<\/p>\n

\uc624\ub77c\ud074 \uc6b4\uc601 \uc2dc\uc5d0 \ud558\uc704\ub808\ubca8\uc5d0\uc11c \ub0b4\ubd80\uc801\uc73c\ub85c \ucc98\ub9ac\ub418\ub294 \ub2e4\uc591\ud55c \uc870\uc791\ub4e4\uc774 latch\uc758 \uad00\ud560 \ud558\uc5d0 \uc218\ud589\ub418\ub294\ub370 V$LATCHNAME\uc744 \uc870\ud68c\ud574\ubcf4\uba74 (9i \uae30\uc900\uc73c\ub85c) 239 \uc885\ub958\ub098 \ub418\ub294 Latch\uac00 \uc874\uc7ac\ud558\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4. \uc774 \uac00\uc6b4\ub370 \uc6b0\ub9ac\uac00 \uc790\uc8fc \uc811\ud558\uac8c \ub418\ub294 latch\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \uc815\ub3c4\uc774\uba70 \uac01 Latch\uc758 \uae30\ub2a5\uc740 \uad00\ub828 SGA\ubcc4 Wait\ub97c \ub2e4\ub8f0 \ub54c \uac04\ub2e8\ud558\uac8c\ub098\ub9c8 \uc18c\uac1c\ud558\ub3c4\ub85d \ud558\uaca0\ub2e4.<\/p>\n\n\n\n<\/colgroup>\n\n\n\n\n\n
Shared pool<\/span><\/td>\nlibrary cache latch, shared pool latch, row cache objects<\/span><\/td>\n<\/tr>\n
Buffer Cache<\/span><\/td>\ncache buffers chains latch, cache buffers lru latch, cache buffer handle<\/span><\/td>\n<\/tr>\n
Redo log<\/span><\/td>\nredo allocation latch, redo copy latch, redo writing latch<\/span><\/td>\n<\/tr>\n
OPS<\/span><\/td>\ndlm resource hash list<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u25b7 Willing to wait \ubaa8\ub4dc\uc640 No-wait \ubaa8\ub4dc<\/p>\n

Latch \ud68d\ub4dd \ubc29\uc2dd\uc740 No-wait\uacfc Willing to wait \uc758 \ub450 \uac00\uc9c0 \ubaa8\ub4dc\ub85c \uad6c\ubd84\ud560 \uc218 \uc788\ub2e4. Willing to wait \ubaa8\ub4dc\ub294 Latch\uc758 \ud68d\ub4dd\uc5d0 \uc2e4\ud328\ud558\uba74 \uc880\ub354 \uc2dc\uac04\uc744 \ub04c\uba74\uc11c \ud574\ub2f9 Latch\ub97c \uc7a1\uc744 \ub54c\uae4c\uc9c0 \uc7ac\uc2dc\ub3c4\ub97c \ud574\ubcf4\ub294 \ubc29\uc2dd\uc744 \ub9d0\ud55c\ub2e4. \uc77c\ucc28\uc801\uc73c\ub85c\ub294 CPU\ub97c \ub193\uc9c0 \uc54a\uace0 \uc815\ud574\uc9c4 \ud69f\uc218\ub9cc\ud07c Spinning\uc744 \ud55c \ud6c4 \uc7ac\uc2dc\ub3c4\ub97c \ud574\ubcf4\ub2e4\uac00 \uadf8\ub798\ub3c4 \uc2e4\ud328\ud558\uba74 CPU\ub97c \ub193\uace0 Sleep\ud558\ub2e4\uac00 timeout\ub418\uc5b4 \uc7ac\uc2dc\ub3c4\ud558\ub294 \uc791\uc5c5\uc744 \ubc18\ubcf5\ud558\uba74\uc11c Latch\uc758 \ud68d\ub4dd\uc744 \ub178\ub825\ud558\uac8c \ub41c\ub2e4. Latch\uac00 sleep\uc5d0 \ub4e4\uc5b4\uac00\uac8c \ub418\uba74 ‘latch free’ wait event \ub300\uae30\uac00 \uc2dc\uc791\ub41c\ub2e4. sleep\uc758 \uc9c0\uc18d\uc2dc\uac04\uc740 sleep \ud69f\uc218\uac00 \ub298\uc5b4\uac08\uc218\ub85d \uc810\uc810 \uae38\uc5b4\uc9c0\uac8c \ub418\ub294\ub370, \ub530\ub77c\uc11c V$LATCH\uc758 Gets\uc640 Sleeps\uc758 \ube44\uc728\uacfc \ud568\uaed8 Sleep1~sleep4 \ud56d\ubaa9\uc5d0\uc11c \uba87\ucc28 Sleep\uae4c\uc9c0 \ubc1c\uc0dd\ud588\ub294\uc9c0 \uc5ec\ubd80\ub3c4 \uac01 Latch Wait\uc758 \uc2ec\uac01\uc131\uc744 \ud310\ub2e8\ud558\ub294 \uc694\uc18c \uac00\uc6b4\ub370 \ud558\ub098\uac00 \ub41c\ub2e4.<\/p>\n

No-wait \ubaa8\ub4dc\ub294 Willing to wait\uacfc\ub294 \ub2ec\ub9ac \ub354 \uc774\uc0c1 \ubbf8\ub828\uc744 \ub450\uc9c0 \uc54a\uace0 \ud574\ub2f9 Latch\uc5d0 \ub300\ud55c \ud68d\ub4dd\uc744 \ud3ec\uae30\ud558\ub294 \uac83\uc774\ub2e4. No-wait \ubaa8\ub4dc\uac00 \uc0ac\uc6a9\ub418\ub294 \uacbd\uc6b0\ub294 \ub450 \uac00\uc9c0\uac00 \uc788\ub294\ub370, \ud558\ub098\ub294 \ub3d9\uc77c\ud55c \uae30\ub2a5\uc744 \ud558\ub294 Latch\uac00 \uc5ec\ub7ec \uac1c \uc874\uc7ac\ud558\uc5ec \uadf8 \uc911\uc5d0 \ud558\ub098\ub9cc \ud68d\ub4dd\ud558\uba74 \ucda9\ubd84\ud558\uc5ec\uc11c \ud2b9\uc815 Latch\uc5d0 \ubbf8\ub828\uc744 \uac00\uc9c8 \ud544\uc694\uac00 \uc5c6\ub294 \uacbd\uc6b0\uc774\ub2e4. \ubb3c\ub860, \uc774 \ub54c\uc5d0\ub3c4 \uac19\uc740 \uae30\ub2a5\uc758 \ubaa8\ub4e0 Latch\uc5d0 \ub300\ud55c \uc2dc\ub3c4\uac00 \uc2e4\ud328\ub85c \ub05d\ub0a0 \uacbd\uc6b0\uc5d0\ub294 Willing to wait \ubaa8\ub4dc\ub85c \uc694\uccad\uc744 \ud560 \uac83\uc774\ub2e4. No-wait \ubaa8\ub4dc\uac00 \uc0ac\uc6a9\ub418\ub294 \ub2e4\ub978 \ud55c\uac00\uc9c0 \uacbd\uc6b0\ub294 dead lock\uc744 \ud53c\ud558\uae30 \uc704\ud574\uc11c \uc774\ub2e4. \uc624\ub77c\ud074\uc740 \uae30\ubcf8\uc801\uc73c\ub85c latch dead lock \uc0c1\ud669\uc744 \ud53c\ud558\uae30 \uc704\ud558\uc5ec \ubaa8\ub4e0 Latch\uc5d0 level\uc744 \ubd80\uc5ec\ud558\uc5ec \uc815\ud574\uc9c4 \uc21c\uc11c\ub97c \ub530\ub77c\uc11c\ub9cc Latch\ub97c \ud68d\ub4dd\ud558\ub3c4\ub85d \ud558\uace0 \uc788\ub294\ub370, \ud544\uc694\uc5d0 \uc758\ud574 \uc774 \uaddc\uce59\uc744 \uc5b4\uae30\uace0 Latch\ub97c \ud68d\ub4dd\ud558\uace0\uc790 \ud560 \uacbd\uc6b0 \uc77c\ub2e8 No-wait \ubaa8\ub4dc\ub85c \uc2dc\ub3c4\ub97c \ud574\ubcf4\ub294 \uac83\uc774\ub2e4. \ub2e4\ud589\ud788 Latch\ub97c \uc7a1\uc73c\uba74 \uc88b\uc740 \uac83\uc774\uace0 \ube44\ub85d latch\ub97c \uc7a1\uc744 \uc218 \uc5c6\ub354\ub77c\ub3c4 \ubb34\ud55c\uc815 \uae30\ub2e4\ub9bc\uc73c\ub85c\uc368 dead lock \uc0c1\ud0dc\uc5d0 \ube60\uc9c0\ub294 \uc77c\uc740 \ud53c\ud560 \uc218 \uc788\ub294 \uac83\uc774\ub2e4. No-wait \ubaa8\ub4dc\uc758 Latch\uc791\uc5c5\uc5d0\uc11c\ub294 \ub2f9\uc5f0\ud788 Latch \uad00\ub828 wait\uc774 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\uc73c\uba70, redo copy latch\ub97c \uc81c\uc678\ud558\uace0\ub294 Willing to wait \ubaa8\ub4dc\ub85c Latch\ub97c \ud68d\ub4dd\ud558\ub294 \uacbd\uc6b0\uac00 \ud6e8\uc52c \ub9ce\ub2e4.<\/p>\n

\u25b7 Parent latch\uc640 Child latch<\/p>\n

Latch \uac00\uc6b4\ub370\uc5d0\ub294 \ub3d9\uc77c \uae30\ub2a5\uc744 \ud558\ub294 Child latch\ub4e4\uc758 set\uc73c\ub85c \uc6b4\uc601\ub418\ub294 Latch\ub3c4 \uc788\uc73c\uba70 \ud558\ub098\uc758 Latch\ub85c\ub9cc \uc6b4\uc601\ub418\ub294 Latch\ub3c4 \uc788\ub2e4. \uc804\uc790\uc758 \ub300\ud45c\uc801\uc778 \uc608\ub85c\ub294 cache buffers chains (\ubc84\ud37c\uce90\uc26c \ube14\ub85d \ub4e4\uc744 \uac19\uc740 \uc774\ub984\uc758 \ub2e4\uc218\uc758 Latch\uac00 \ub098\ub204\uc5b4 \ub2f4\ub2f9)\uac00 \uc788\uc73c\uba70, \ud6c4\uc790\uc758 \uc608\ub85c\ub294 shared pool latch (shared pool\ub0b4\uc5d0\uc11c \uba54\ubaa8\ub9ac \ud560\ub2f9\uc744 \uc704\ud574 \ud68d\ub4dd\ud574\uc57c \ud558\ub294 Latch\ub85c \uc2dc\uc2a4\ud15c\uc5d0 \ud558\ub098\ub9cc \uc874\uc7ac)\uac00 \uc788\ub2e4. \uc774\uc640 \uac19\uc740 Latch \uad00\ub828 \ud1b5\uacc4 \uc815\ubcf4\ub294 Parent latch\uc640 Child latch\uc758 \uac1c\ub150\uc73c\ub85c \uad00\ub9ac\uac00 \ub418\ub294\ub370 Latch set\uc5d0\uc11c \uac1c\ubcc4 Child latch\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4\ub294 V$LATCH_CHILDREN \ubdf0\ub97c \ud1b5\ud574 \uc870\ud68c\ud560 \uc218 \uc788\uc73c\uba70, \ub2e8\uc77c Latch \ud639\uc740 Latch set\uc758 \ub9c8\uc2a4\ud130 Latch (parent)\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4\ub294 V$LATCH_PARENT \ubdf0\ub97c \ud1b5\ud574 \uc870\ud68c\ud560 \uc218 \uc788\ub2e4.<\/p>\n

\uc9c0\uae08\uae4c\uc9c0 \ud55c \ud68c \ubd84\ub7c9\uc744 \ud560\uc560\ud558\uc5ec Enqueue\uc640 Latch\uc5d0 \ub300\ud574 \uc694\uc57d\ud574\ubcf8 \uc774\uc720\ub294, \ub9ce\uc740 Waiting\uc774 SGA\ub0b4\uc758 \uacf5\uc720\uc790\uc6d0 (Block, Cursor \ub4f1)\uc5d0 \ub300\ud55c \uacbd\ud569\uc73c\ub85c \uc778\ud574 \ubc1c\uc0dd\ud558\uba70 \uc774\ub7ec\ud55c \uacbd\ud569\uc740 \ub2e4\uc2dc \ud574\ub2f9 \uc790\uc6d0\uc5d0 \ub300\ud55c \ub3d9\uc2dc \uc561\uc138\uc2a4\ub97c \uc81c\uc5b4\ud558\ub294 Enqueue\uc640 Latch\uc5d0 \ub300\ud55c \uacbd\ud569\uc73c\ub85c \ud754\ud788 \ub4dc\ub7ec\ub098\uac8c \ub418\ubbc0\ub85c \uc624\ub77c\ud074\uc758 Wait Event\ub97c \ubaa8\ub2c8\ud130\ub9c1\ud558\uae30 \uc704\ud574\uc11c\ub294 Enqueue\uc640 Latch\uc758 \uad6c\uc870\uc640 \uc791\ub3d9\uc6d0\ub9ac\uc5d0 \ub300\ud574 \uc774\ud574\ud558\ub294 \uac83\uc774 \ud544\uc218\uc801\uc774\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n

[3] Shared Pool \uad00\ub828 Wait<\/p>\n

\u25a0Share pool\uacfc \uc131\ub2a5\ubb38\uc81c<\/p>\n

\uc624\ub77c\ud074\uc774 \uacf5\uc720 \uba54\ubaa8\ub9ac(SGA)\ub97c \uc0ac\uc6a9\ud558\ub294 \uac00\uc7a5 \ud070 \uc774\uc720\ub294 \uae30\ubcf8\uc801\uc73c\ub85c \uba54\ubaa8\ub9ac \uc0ac\uc6a9\uc744 \ucd5c\uc18c\ud654\ud558\uba74\uc11c \ucc98\ub9ac\uc131\ub2a5\uc740 \ucd5c\ub300\ud654\ud558\uae30 \uc704\ud55c \uac83\uc774\ub2e4. \ud55c\ubc88 \uc561\uc138\uc2a4\ub41c \ube14\ub85d\uc744 Database buffer cache\uc5d0 \uce90\uc26c \ud568\uc73c\ub85c\uc368 \ube44\uc6a9\uc774 \ud070 Disk I\/O\ub97c \ucd5c\uc18c\ud654\ud558\ub294 \uac83\ucc98\ub7fc, \ud55c\ubc88 \ucc98\ub9ac\ub41c SQL\uc758 \uc2e4\ud589 \uc815\ubcf4\ub97c Shared Pool\uc5d0 \uacf5\uc720\ud568\uc73c\ub85c\uc368 \ud30c\uc2f1 \uc791\uc5c5\uc744 \uc704\ud55c CPU, \uba54\ubaa8\ub9ac \uc790\uc6d0\uc758 \uc0ac\uc6a9\uc744 \ucd5c\uc18c\ud654\ud558\uace0 SQL \uc218\ud589\uc18d\ub3c4\ub97c \uc99d\uac00\uc2dc\ud0ac \uc218 \uc788\ub2e4. Shared Pool\uc5d0\ub294 SQL\uc774\ub098 PL\/SQL\uc744 \uc218\ud589\ud558\uae30 \uc704\ud55c \uac01\uc885 \uc815\ubcf4 – SQL\uad6c\ubb38 \ubc0f \uc2e4\ud589\uacc4\ud68d, PL\/SQL \uc18c\uc2a4, \ud14c\uc774\ube14, \ubdf0 \ub4f1\uc758 \uac01\uc885 \uc624\ube0c\uc81d\ud2b8\uc640 \uc624\ube0c\uc81d\ud2b8 \uc0c1\ud638\uac04\uc758 \uc758\uc874\uad00\uacc4, \uad8c\ud55c\uad00\uacc4 \ub4f1 – \uac00 \uc800\uc7a5\ub418\uc5b4 \uc788\ub2e4. \uc9c0\uba74 \uad00\uacc4\uc0c1 \uc774 \uae00\uc5d0\uc11c Shared Pool\uc758 \uad00\ub9ac \uba54\ucee4\ub2c8\uc998\uc744 \uc0c1\uc138\ud788 \uae30\uc220\ud560 \uc218\ub294 \uc5c6\uc9c0\ub9cc \uba87 \uac00\uc9c0 \ub0b4\uc7ac\uc801\uc778 \ud2b9\uc9d5\uc73c\ub85c \uc778\ud574 Shared Pool\uc740 \uc624\ub77c\ud074\uc758 \uba54\ubaa8\ub9ac \uc601\uc5ed \uac00\uc6b4\ub370\uc5d0\uc11c\ub3c4 \uac00\uc7a5 \uc131\ub2a5\ubb38\uc81c\uc758 \uc694\uc18c\uac00 \ub9ce\uc740 \uacf3\uc774\uba74\uc11c\ub3c4 \ud6a8\uacfc\uc801\uc778 \ud29c\ub2dd\uc774 \uc218\uc6d4\uce58 \uc54a\uc740 \uc601\uc5ed\uc774\uae30\ub3c4 \ud558\ub2e4.<\/p>\n

\ubb34\uc5c7\ubcf4\ub2e4, Shared Pool\uc5d0\uc11c \uac00\uc7a5 \ubb38\uc81c\uac00 \ub418\ub294 \uac83\uc740 \uba54\ubaa8\ub9ac\uc758 \uc870\uac01\ud654(Fragmentation)\uc774\ub2e4. Shared Pool\uc5d0\uc11c \ub77c\uc774\ube0c\ub7ec\ub9ac \uce90\uc26c \uc624\ube0c\uc81d\ud2b8\ub97c \uc704\ud574 \ud560\ub2f9\ub418\ub294 \uba54\ubaa8\ub9ac \ub2e8\uc704\ub97c chunk\ub77c\uace0 \ubd80\ub974\ub294\ub370 chunk\uc758 \ud06c\uae30\ub294 \uc218 \ubc14\uc774\ud2b8\uc5d0\uc11c \uc218 K\ubc14\uc774\ud2b8\uc5d0 \uc774\ub974\uae30\uae4c\uc9c0 \ud544\uc694\uc5d0 \uc758\ud574 \ub2e4\uc591\ud558\uac8c \ud560\ub2f9\ub41c\ub2e4. \uc0c8\ub85c\uc6b4 chunk\uc758 \ud560\ub2f9\uc774 \ud544\uc694\ud558\uac8c \ub418\uba74, \ud504\ub85c\uc138\uc2a4\ub294 \uc774\ubbf8 \uc874\uc7ac\ud558\ub294 chunk\ub85c\ubd80\ud130 \ud544\uc694\ud55c \ub9cc\ud07c\uc758 \ud06c\uae30\ub9cc\uc744 \ub5bc\uc5b4\ub0b4\uc5b4 \uc0ac\uc6a9\ud558\ubbc0\ub85c \uc2dc\uac04\uc774 \ud750\ub97c\uc218\ub85d \uc810\ucc28 \uba54\ubaa8\ub9ac\uac00 \uc870\uac01\ud654 \ub418\ub294 \uac83\uc744 \ud53c\ud560 \uc218 \uc5c6\ub2e4. ( \uc774\ub294, Pctincrease\uac00 0\uac00 \uc544\ub2cc \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4\uc5d0\uc11c \uc775\uc2a4\ud150\ud2b8\uc758 \ud560\ub2f9\uacfc \ud574\uc81c\uac00 \ubc18\ubcf5\ub428\uc5d0 \ub530\ub77c \uacf5\uac04\uc758 \uc870\uac01\ud654\uac00 \uc2ec\ud574\uc9c0\ub294 \uac83\uc744 \ub5a0\uc62c\ub9ac\uba74 \uc774\ud574\uac00 \uc26c\uc6b8 \uac83\uc774\ub2e4. ). \uc5b4\ub290 \uc815\ub3c4 \uc815\ud615\ud654\ub41c \ud328\ud134\uc758 \uc560\ud50c\ub9ac\ucf00\uc774\uc158\uc774 \uc218\ud589\ub418\ub294 \ud658\uacbd\uc774 \uc544\ub2c8\ub77c, \uacf5\uc720\uac00 \ubd88\uac00\ub2a5\ud55c \ub2e4\uc591\ud55c \ud615\ud0dc\uc758 SQL(\ub300\ud45c\uc801\uc73c\ub85c Literal SQL)\uc774 \ube48\ubc88\ud788 \uc694\uccad\ub418\ub294 \ud658\uacbd\uc774\ub77c\uba74 Shared Pool \uba54\ubaa8\ub9ac \uc870\uac01\ud654\uc5d0 \ub530\ub978 \ubb38\uc81c\ub294 \ub354\uc6b1 \uc2ec\uac01\ud574\uc9c4\ub2e4.<\/p>\n

\ub610\ud55c, Shared Pool\uc740 \uc77c\ubc18\uc801\uc778 \uba54\ubaa8\ub9ac \uce90\uc26c\uc640\ub294 \ub2ec\ub9ac \uba54\ubaa8\ub9ac\uc5d0 \uc800\uc7a5\ub418\uc5c8\ub358 \uc815\ubcf4\ub97c \uc7a0\uc2dc \uae30\ub85d\ud574\ub458 \ub300\uc751\ub418\ub294 \ub514\uc2a4\ud06c \uacf5\uac04\uc774 \uc5c6\uc73c\ubbc0\ub85c \ud55c\ubc88 flush\ub41c \ub77c\uc774\ube0c\ub7ec\ub9ac \uce90\uc26c \uc624\ube0c\uc81d\ud2b8\ub97c reload\ud558\uae30 \uc704\ud574\uc11c\ub294 \ud574\ub2f9 \uc815\ubcf4\ub97c \uc7ac\uc0dd\uc131 \ud574\uc57c\ub9cc \ud55c\ub2e4. \uc774 \uacfc\uc815\uc5d0\uc11c \uad00\ub828 \uc624\ube0c\uc81d\ud2b8 \uc815\ubcf4\uc758 \uac80\uc0c9 \ubc0f \ucc38\uc870, locking, \uba54\ubaa8\ub9ac \ud560\ub2f9 \ub4f1\uc758 \uc791\uc5c5\uc744 \uc704\ud574 \ub9ce\uc740 \ube44\uc6a9\uc774 \ub4e4\uae30 \ub54c\ubb38\uc5d0 \uacb0\uad6d Shared Pool \uad00\ub828 \ud29c\ub2dd\uc758 \ucd5c\ub300 \uacfc\uc81c\ub294 SQL \uacf5\uc720\ub97c \ucd5c\ub300\ud654\ud558\uc5ec \uc0c8\ub85c\uc6b4 \ud30c\uc2f1 \uc694\uccad\uacfc \uba54\ubaa8\ub9ac \uc694\uccad\uc744 \ucd5c\uc18c\ud654\ud558\ub294 \uac83\uc774\ub77c\uace0 \ud560 \uc218 \uc788\ub2e4. \ud5cc\ub370, \uc774\ub294 \uc560\ud50c\ub9ac\ucf00\uc774\uc158\uc758 \uc124\uacc4\uc640 \uc5f0\uacc4\ub418\ub294 \uc601\uc5ed\uc73c\ub85c\uc11c \uc774\ubbf8 \uac1c\ubc1c\uc774 \uc644\ub8cc\ub41c \uc6b4\uc601\uc11c\ubc84\uc5d0\uc11c\ub294 \ubcc0\uacbd\uc791\uc5c5\uc774 \uc5ec\uc758\uce58 \uc54a\uc740 \uac83\uc774 \ud604\uc2e4\uc774\ub2e4. \uc55e\uc11c, Shared Pool\uc774 DBA\ub85c\uc11c \ud29c\ub2dd\uc774 \uc218\uc6d4\uce58 \uc54a\uc740 \uc601\uc5ed\uc774\ub77c\uace0 \ud45c\ud604\ud55c \uc774\uc720 \uac00\uc6b4\ub370 \ud558\ub098\uac00 \uc5ec\uae30\uc5d0 \uc788\ub2e4.<\/p>\n

\u25a0 Shared Pool \uad00\ub828 \uc624\ud574 \ubc14\ub85c\uc7a1\uae30<\/p>\n

Shared Pool\uacfc \uad00\ub828\ud558\uc5ec \ud310\ub2e8\uc774 \uc27d\uc9c0 \uc54a\uc740 \ubd80\ubd84 \uac00\uc6b4\ub370 \ud558\ub098\uac00 \uacfc\uc5f0 shared_pool_size\ub97c \uc5bc\ub9c8\ub098 \ud560\ub2f9\ud560 \uac83\uc778\uac00 \ud558\ub294 \uac83\uc774\ub2e4. \uc624\ub77c\ud074\uc740 Shared Pool \uba54\ubaa8\ub9ac\ub97c \ucd5c\ub300\ud55c \ud6a8\uc728\uc801\uc73c\ub85c \ud65c\uc6a9\ud558\uae30 \uc704\ud558\uc5ec \ub2e4\uc591\ud55c \uae30\ubc95\uc744 \ub3d9\uc6d0\ud558\uace0 \uc788\ub294\ub370, \uc774\ub7ec\ud55c \uba54\ubaa8\ub9ac \uad00\ub9ac \uba54\ucee4\ub2c8\uc998\uc5d0 \ub300\ud574 \uc815\ud655\ud788 \uc54c\uc9c0 \ubabb\ud558\uc5ec Shared Pool \ud06c\uae30\ub97c \uc9c0\ub098\uce58\uac8c \ud06c\uac8c \ud560\ub2f9\ud568\uc73c\ub85c\uc368 \uc624\ud788\ub824 \ubb38\uc81c\ub97c \uc545\ud654\uc2dc\ud0a4\ub294 \uacbd\uc6b0\ub3c4 \ub4dc\ubb3c\uc9c0 \uc54a\ub2e4. \uc774\ub7ec\ud55c \uc624\ud574\ub97c \ubc14\ub85c\uc7a1\uae30 \uc704\ud574 Shared Pool\uc758 \uba54\ubaa8\ub9ac \ud560\ub2f9\uacfc\uc815\uc744 \uac04\ub2e8\ud558\uac8c\ub098\ub9c8 \uc0b4\ud3b4\ubcf4\ub3c4\ub85d \ud558\uaca0\ub2e4.<\/p>\n

\uc0c8\ub85c\uc6b4 \uba54\ubaa8\ub9ac Chunk\uac00 \ud560\ub2f9\ub418\ub294 \uacfc\uc815\uc744 \uc0b4\ud3b4\ubcf4\uba74, \uc6b0\uc120 \ud504\ub85c\uc138\uc2a4\ub294 Free List\ub97c \uac80\uc0c9\ud558\uc5ec \uc790\uc2e0\uc774 \ud544\uc694\ub85c \ud558\ub294 \ud06c\uae30\uc758 Free Chunk\ub97c \ucc3e\uace0, \uadf8\ub7ec\ud55c Free Chunk\uac00 \uc5c6\uc73c\uba74 \uc6d0\ud558\ub294 \ud06c\uae30\ubcf4\ub2e4 \ud55c\ub2e8\uacc4 \ud070 Free Chunk\ub97c \ucc3e\uc544\uc11c \ud544\uc694\ud55c \ud06c\uae30\ub9cc\ud07c \ubd84\ud560\ud558\uc5ec \uc0ac\uc6a9\ud558\uac8c \ub41c\ub2e4. \ub9cc\uc57d Free List\uc5d0\uc11c \ucda9\ubd84\ud55c \ud06c\uae30\uc758 Free Chunk\ub97c \ucc3e\uc744 \uc218 \uc5c6\ub2e4\uba74, \uc774\ubbf8 \uc0ac\uc6a9\ub418\uc5c8\uc73c\ub098 \ud604\uc7ac\ub294 \uc0ac\uc6a9\ub418\uace0 \uc788\uc9c0 \uc54a\ub294(unpinned) Chunk\ub4e4\uc758 LRU List\ub97c \uac80\uc0c9\ud558\uc5ec \uc624\ub798\ub41c \uac83\ubd80\ud130 8\uac1c\uc529 flush\uc2dc\ucf1c Free Chunk\ub85c \ub9cc\ub4e0 \ud6c4 \uc790\uc2e0\uc774 \ud544\uc694\ud55c \ud06c\uae30\ub97c \ud560\ub2f9\ud558\uc5ec \uc0ac\uc6a9\ud558\uac8c \ub41c\ub2e4. \ub9cc\uc57d \uc774 \uacfc\uc815\uc5d0\uc11c \ud604\uc7ac \uc0ac\uc6a9\uc911\uc778(pinned) Chunk\uac00 \ub300\ubd80\ubd84\uc774\uac70\ub098, \ub108\ubb34 \uba54\ubaa8\ub9ac \uc870\uac01\ud654\uac00 \ub9ce\uc774 \uc77c\uc5b4\ub098\uc11c \uae30\uc874 Chunk\ub97c Flush\uc2dc\ud0a8 \ud6c4 \uc778\uc811\ud55c Free Chunk\ub4e4\uc744 \ubcd1\ud569\ud574\ubcf4\uc544\ub3c4 \uc6d0\ud558\ub294 \ud06c\uae30\uc758 Free Chunk\ub97c \uc5bb\uc5b4\ub0bc \uc218 \uc5c6\ub2e4\uba74 \uc624\ub77c\ud074\uc740 ORA-4031 \uc5d0\ub7ec\ub97c \ubc1c\uc0dd\uc2dc\ud0a4\ub294\ub370, \uadf8 \uc774\uc804\uc5d0 \ud55c\uac00\uc9c0 \ucd5c\ud6c4\uc758 \ube44\ubc00\ubb34\uae30\uac00 \ub354 \uc228\uc5b4 \uc788\ub2e4. \ubc14\ub85c Spare Free \uba54\ubaa8\ub9ac\ub77c\ub294 \uac83\uc778\ub370 \uc624\ub77c\ud074\uc740 \uc778\uc2a4\ud134\uc2a4 \uae30\ub3d9 \ud6c4 \ucc98\uc74c\uc5d0\ub294 \uc804\uccb4 Shared Pool\uc758 50% \uac00\ub7c9\uc740 Free List\uc5d0 \uc62c\ub824\ub193\uc9c0 \uc54a\uace0 \uc544\uc608 \uc228\uaca8\ub450\uc5c8\ub2e4\uac00 \uc55e\uc11c\uc640 \uac19\uc774 \ub3c4\uc800\ud788 \ud53c\ud560 \uc218 \uc5c6\ub294 \uc21c\uac04\uc774 \ub418\uba74 \uc870\uae08\uc529 \ud574\uc81c \uc2dc\ucf1c \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud55c\ub2e4. \uadf8\uc57c\ub9d0\ub85c \uba54\ubaa8\ub9ac\uc758 \uc870\uac01\ud654\ub97c \ucd5c\uc18c\ud654\ud558\uae30 \uc704\ud55c \uc624\ub77c\ud074\uc758 \ub208\ubb3c \ub098\ub294 \ub178\ub825\uc774\ub77c\uace0 \ud560 \uc218 \uc788\uc744 \uac83\uc774\ub2e4. \ubb3c\ub860 \uc774 \uc601\uc5ed\uae4c\uc9c0 \ub2e4 \uc18c\ubaa8\ud55c \ud6c4\uc5d0 flush\ub97c \ud1b5\ud574\uc11c\ub3c4 \ud544\uc694\ud55c Chunk\ub97c \ud655\ubcf4\ud560 \uc218 \uc5c6\ub294 \uc0c1\ud669\uc774 \ub418\uba74 \uacb0\uad6d ORA-4031 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud560 \uac83\uc774\ub2e4.<\/p>\n

\ub9ce\uc740 \uc774\ub4e4\uc774 Shared Pool\uc758 \ub0a8\uc544\uc788\ub294 Free memory\uc758 \ud06c\uae30\uac00 \uc791\uc73c\uba74 shared_pool_size\ub97c \uc99d\uac00\uc2dc\ucf1c\uc8fc\uc5b4\uc57c \ud55c\ub2e4\uace0 \ubbff\uace0 \uc788\ub294\ub370 \uc774\ub294 \uc798\ubabb\ub41c \uac83\uc774\ub2e4. Shared Pool\uc740 \uc815\ubcf4\uc758 \uc7ac\uc0ac\uc6a9\uc744 \uc704\ud574 \uc6b4\uc601\ud558\ub294 \uac83\uc774\ubbc0\ub85c SQL \uc2e4\ud589\uc774 \ub05d\ub0ac\ub2e4\uace0 \ud574\uc11c \ud574\ub2f9 Chunk\ub97c Free List\ub85c \ubc18\ub0a9\ud558\uc9c0 \uc54a\ub294\ub2e4. \uc989, Free Memory\uac00 \ub0a8\uc544\uc788\ub294 \ud55c \uacc4\uc18d \uc18c\ubaa8 \uc2dc\ud0a4\ub294 \ubc29\uc2dd\uc73c\ub85c \uc0ac\uc6a9\ub418\ubbc0\ub85c \uc624\ub79c \uc2dc\uac04\ub3d9\uc548 \uc6b4\uc601\ub418\uc5b4\uc628 \uc2dc\uc2a4\ud15c\uc5d0\uc11c Shared Pool\uc758 Free Memory\uac00 \ub9e4\uc6b0 \uc801\uac8c \ub0a8\uc544 \uc788\ub294 \uac83\uc740 \uadf8 \uc790\uccb4\ub85c\ub294 \ubb38\uc81c\uac00 \ub418\uc9c0 \uc54a\uc73c\uba70, \uc624\ud788\ub824 \ud53c\ud06c\ud0c0\uc784\uc774 \uc9c0\ub09c \ud6c4\uc5d0\ub3c4 \ub9ce\uc740 \uc591\uc758 Free Memory\uac00 \ub0a8\uc544\uc788\ub2e4\uba74 \uc774\ub294 Spare Free \uba54\ubaa8\ub9ac\ub3c4 \ub2e4 \uc18c\ubaa8\ud558\uc9c0 \uc54a\uc740 \uc0c1\ud0dc\ub85c\uc11c \ubd88\ud544\uc694\ud558\uac8c \ub9ce\uc740 \uba54\ubaa8\ub9ac\uac00 \ud560\ub2f9\ub418\uc5b4 \ub0ad\ube44\ub418\uace0 \uc788\uc74c\uc744 \uc758\ubbf8\ud55c\ub2e4. \ub354\uad6c\ub098, Shared Pool \ud06c\uae30\uac00 \uc9c0\ub098\uce58\uac8c \ud06c\uba74 Free Memory\ub97c \ub2e4 \uc0ac\uc6a9\ud560 \ub54c\uae4c\uc9c0\uc758 \uae30\uac04\uc774 \uc5f0\uc7a5\ub418\ub294 \ud6a8\uacfc\ub294 \uc5bb\uc744 \uc218 \uc788\uaca0\uc9c0\ub9cc, \uc2dc\uac04\uc774 \uc9c0\ub0a0\uc218\ub85d Memory\uc758 \uc870\uac01\ud654\uac00 \ub354\uc6b1 \uc2ec\ud574\uc9c0\uace0 Free List\uc758 \uae38\uc774\uac00 \uae38\uc5b4\uc838 Free Chunk\uc758 \uac80\uc0c9\uacfc \ud560\ub2f9\uc5d0 \uac78\ub9ac\ub294 \uc2dc\uac04\uc774 \uc9c0\uc5f0\ub418\ubbc0\ub85c \uc624\ud788\ub824 \uc131\ub2a5\uc774 \uc545\ud654\ub418\ub294 \uacb0\uacfc\ub97c \ucd08\ub798\ud560 \uac83\uc774\ub2e4.<\/p>\n

\ub610\ud55c, \uba54\ubaa8\ub9ac \uc870\uac01\ud654\uc5d0 \ub530\ub978 \uc601\ud5a5\uc744 \uc904\uc774\uae30 \uc704\ud574 \uc624\ub77c\ud074\uc740 5000 bytes\uac00 \ub118\ub294 \ud070 \uc0ac\uc774\uc988\uc758 Chunk\ub9cc\uc744 \uc704\ud574 \uc804\uccb4 Shared Pool\uc758 5% \uc815\ub3c4\ub97c \ub530\ub85c \uad00\ub9ac\ud558\ub294 \ubc29\ubc95\uc744 \uc0ac\uc6a9\ud558\uace0 \uc788\ub294\ub370, \uacbd\ud5d8\uc801\uc73c\ub85c \ubcf4\uba74 \uc774 \uacf5\uac04\uc740 \uac70\uc758 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uace0 \ubc84\ub824\uc9c0\uace0 \uc788\ub294 \uacbd\uc6b0\uac00 \ub9ce\ub2e4. \uc774\ub294 V$SHARED_POOL_RESERVED \ubdf0\uc758 USED_SPACE \uac12\uc744 \ud655\uc778\ud574 \ubcf4\uba74 \uc54c \uc218 \uc788\uc73c\uba70, 5000 bytes \uc774\uc0c1\uc758 large chunk\uac00 \uac70\uc758 \uc694\uad6c\ub418\uc9c0 \uc54a\ub294 \ud658\uacbd\uc5d0\uc11c\ub294 \uc624\ud788\ub824 \uc774 \ud06c\uae30\ub97c \uc904\uc5ec\uc8fc\ub294 \uac83\uc774 \ub098\uc744 \uac83\uc774\ub2e4.<\/p>\n

\u25a0Shared Pool \uad00\ub828 wait<\/p>\n

Shared Pool\uacfc \uad00\ub828\ud558\uc5ec \ud754\ud788 \ubc1c\uc0dd\ud558\ub294 Wait\uc740 \ub77c\uc774\ube0c\ub7ec\ub9ac \uce90\uc26c \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud55c \ub3d9\uc2dc \uc561\uc138\uc2a4\uc640 \uba54\ubaa8\ub9ac \ud560\ub2f9\uc5d0 \ub530\ub978 \uad00\ub828 Lock \ub610\ub294 Latch\uc5d0 \ub300\ud55c \uacbd\ud569\uc774 \ub300\ubd80\ubd84\uc774\uba70, \uad6c\uccb4\uc801\uc778 \uc774\ub984\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4. (Latch free \uc774\ubca4\ud2b8\uc2dc \uad04\ud638 \uc548\uc758 \uad00\ub828 latch \uc774\ub984\uc740 v$session_wait\uc758 p2\uac12\uacfc v$latchname\uc758 latch#\ub97c \uc870\uc778\ud558\uc5ec \uc5bb\uc5b4\ub0bc \uc218 \uc788\ub2e4. 1\ud68c SQL \ucc38\uc870)<\/p>\n\n\n\n<\/colgroup>\n\n\n\n\n\n
\n

Latch<\/span><\/p>\n<\/td>\n

\n

Lock<\/span><\/p>\n<\/td>\n<\/tr>\n

latch free ( library cache )
\nlatch free ( library cache load lock)<\/span><\/td>\n		library cache lock, library cache pin
\nlibrary cache load lock<\/span><\/td>\n<\/tr>\n
latch free ( row cache objects )<\/span><\/td>\nrow cache lock<\/span><\/td>\n<\/tr>\n
latch free ( shared pool )<\/span><\/td>\n\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Library cache lock, library cache pin, library load lock\uc740 \uac01\uac01 \ud2b9\uc815 \ub77c\uc774\ube0c\ub7ec\ub9ac \uce90\uc26c \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud55c \uac80\uc0c9\uc774\ub098 \ubcc0\uacbd \ubc0f \uc2e4\ud589 \ub610\ub294 \ub85c\ub4dc \uc2dc\uc5d0 \ub300\uc0c1 \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud574 \ud560\ub2f9\ub418\uba70, \uc774\ub7ec\ud55c Locking \uc791\uc5c5\uc740 library cache latch\uc640 library cache load lock latch\uc758 \uad00\ud560 \ud558\uc5d0 \ucc98\ub9ac\ub41c\ub2e4. Shared pool latch\ub294 Free List\ub098 LRU List\ub97c \uac80\uc0c9\ud558\uac70\ub098 \uba54\ubaa8\ub9ac\ub97c \ud560\ub2f9\ud558\ub294 \uc791\uc5c5\uc5d0 \uc0ac\uc6a9\ub418\uba70, row cache lock\uacfc row cache objects latch\ub294 Data dictionary cache \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud55c \ub3d9\uc2dc \uc561\uc138\uc2a4\ub97c \uc81c\uc5b4\ud558\ub294\ub370 \uc0ac\uc6a9\ub41c\ub2e4.<\/p>\n

Latch\uc758 \uac1c\uc218\ub294 \uc2dc\uc2a4\ud15c \uc804\uccb4\uc801\uc73c\ub85c \ud558\ub098 \ub610\ub294 \uc81c\ud55c\ub41c \uac1c\uc218\uac00 \uc874\uc7ac\ud558\ub294 \uac83\uc774\uace0 Lock\uc740 \ub300\uc0c1 \uc624\ube0c\uc81d\ud2b8 \uac01\uac01 \ub300\ud574 \ud560\ub2f9\ub418\ub294 \uac83\uc774\ubbc0\ub85c, \uc5c4\ubc00\ud558\uac8c \ub9d0\ud574\uc11c Lock\uc5d0 \ub300\ud55c \uacbd\ud569\uc740 \uc9c1\uc811\uc801\uc73c\ub85c\ub294 \ud2b9\uc815 \ub77c\uc774\ube0c\ub7ec\ub9ac \uce90\uc26c \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud55c \ub3d9\uc2dc \uc561\uc138\uc2a4\ub85c \uc778\ud574 \uc720\ubc1c\ub418\ub294 \uac83\uc778 \ubc18\uba74\uc5d0, Latch\uc5d0 \ub300\ud55c \uacbd\ud569\uc740 \uc2dc\uc2a4\ud15c \uc804\uccb4\uc801\uc73c\ub85c \uad00\ub828 \uc624\ud37c\ub808\uc774\uc158(\uc989, SQL \ud30c\uc2f1) \uc790\uccb4\uac00 \uc9c0\ub098\uce58\uac8c \ub9ce\uc774 \ubc1c\uc0dd\ud558\uac70\ub098, \uc9e7\uc740 \uc2dc\uac04 \ub0b4\uc5d0 \ucc98\ub9ac\ub418\uc9c0 \ubabb\ud568\uc73c\ub85c \uc778\ud574 \uc720\ubc1c\ub418\ub294 \uac83\uc774\ub77c\uace0 \uad6c\ubd84\ud574\ubcfc \uc218 \uc788\ub2e4. \uadf8\ub7ec\ub098, \uacb0\uad6d \uc774 \ubaa8\ub4e0 \uacbd\ud569\uc740 \uadfc\ubcf8\uc801\uc73c\ub85c Shared Pool\uc758 \uc870\uac01\ud654(Fragmentation)\uc5d0 \ub530\ub978 \ubb38\uc81c\uac00 \uc8fc\ub41c \uc6d0\uc778\uc774\uba70 \ub2e4\uc2dc \uc774\ub7ec\ud55c \uc870\uac01\ud654\ub294 \uc694\uccad\ub418\ub294 SQL\ub4e4\uc774 \uacf5\uc720\ub418\uc9c0 \ubabb\ud558\uace0 \uc9c0\uc18d\uc801\uc73c\ub85c \uc0c8\ub86d\uac8c \ud30c\uc2f1\ub418\uace0 \uba54\ubaa8\ub9ac\uac00 \ud560\ub2f9\ub428\uc73c\ub85c \uc778\ud574 \ubc1c\uc0dd\ud558\ub294 \uac83\uc774\ub2e4. \ub530\ub77c\uc11c, \uc774\ub7ec\ud55c \ubb38\uc81c\ub97c \ud574\uacb0\ud558\ub294 \uac00\uc7a5 \ud6a8\uacfc\uc801\uc778 \ubc29\ubc95\uc740 Literal SQL\uc744 \ubc14\uc778\ub4dc \ubcc0\uc218\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \uc218\uc815\ud558\uac70\ub098, SQL\uc791\uc131 \ud45c\uc900\uc744 \ub9c8\ub828\ud558\uace0, HOLD_CURSOR\/ RELEASE_CURSOR, SESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIME, CURSOR_SHARING \ub4f1\uc758 \ud30c\ub77c\ubbf8\ud130\ub97c \ud65c\uc6a9\ud558\ub294 \ub4f1\uc758 \ubc29\ubc95\uc744 \ud1b5\ud574 SQL\uc758 \uacf5\uc720\ub3c4\ub97c \ub192\uc5ec\uc8fc\ub294 \uac83\uc774\uba70, \ub610\ud55c \uc790\uc8fc \uc0ac\uc6a9\ub418\ub294 PL\/SQL\uc5d0 \ub300\ud574\uc11c\ub294 DBMS_SHARED_POOL \ud328\ud0a4\uc9c0\ub97c \uc0ac\uc6a9\ud558\uc5ec \uba54\ubaa8\ub9ac\uc5d0\uc11c Flush\ub418\uc9c0 \uc54a\ub3c4\ub85d \ubcf4\uc874\ud558\ub294 \ub4f1\uc758 \uc870\uce58\ub97c \ucde8\ud574\uc8fc\uba74 \ub3c4\uc6c0\uc774 \ub420 \uac83\uc774\ub2e4. SQL\uc758 \uc218\uc815\uc774 \uc5b4\ub824\uc6b4 \ud658\uacbd\uc774\uac70\ub098 \uc2dc\uc2a4\ud15c\uc5d0 \uc694\uccad\ub418\ub294 SQL\uc758 \uc808\ub300\ub7c9\uc774 \ud655\ubcf4\ub41c \uba54\ubaa8\ub9ac \uacf5\uac04\uc5d0 \ube44\ud574 \ub9ce\uc740 \uc0c1\ud669\uc774\ub77c\uba74 \uc8fc\uae30\uc801\uc73c\ub85c \ud53c\ud06c\ud0c0\uc784\uc744 \ud53c\ud574 Shared Pool\uc744 \uc9c1\uc811 Flush(alter system flush shared_pool \uba85\ub839\uc744 \uc0ac\uc6a9\ud55c\ub2e4.) \uc2dc\ucf1c\uc8fc\ub294 \uac83\ub3c4 \uad8c\uc7a5\ud560 \ub9cc\ud55c \uad00\ub9ac \ubc29\ubc95\uc774\ub2e4. \ub9ce\uc740 \uc774\ub4e4\uc774 \uc6b0\ub824\ud558\ub294 \ubc14\uc640\ub294 \ub2ec\ub9ac Shared Pool\uc744 \uc9c1\uc811 flush \uc2dc\ud0a4\ub294 \uac83\uc774 \uc2ec\uac01\ud55c \uc131\ub2a5\uc0c1 \ubb38\uc81c\ub97c \uc57c\uae30\ud558\uc9c0\ub294 \uc54a\uc73c\uba70 \ud2b9\ud788 \uc911\uc694\ud55c \ud328\ud0a4\uc9c0\ub098 SQL cursor, Sequence \ub4f1\uc774 keep\ub418\uc5b4 \uc788\ub294 \uacbd\uc6b0\ub77c\uba74 \ub354\uc6b1 \uadf8\ub7ec\ud558\ub2e4.<\/p>\n

\uac00\ub054 \ubc84\uadf8\ub97c \ud3ec\ud568\ud55c \ud2b9\uc218\ud55c \uc0c1\ud669\uc5d0\uc11c \ud2b9\uc815 \ub77c\uc774\ube0c\ub7ec\ub9ac \uce90\uc26c \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud55c lock\uc774 \uc7a5\uc2dc\uac04 \ud574\uc81c\ub418\uc9c0 \ubabb\ud558\uace0 \uc788\ub294 \uacbd\uc6b0\ub3c4 \uc788\ub294\ub370 \uc774\ub54c\ub294 X$KGLLK \ubdf0\ub97c \uc870\ud68c\ud558\uba74 library cache lock\uc5d0 \ub300\ud55c holder\/waiter\ub97c \ud655\uc778\ud558\uc5ec \uc870\uce58\ud560 \uc218 \uc788\ub2e4. \ub610\ud55c, Row cache lock\uc5d0 \ub300\ud55c \uacbd\ud569\uc740 Locally managed tablespace\ub97c \ub3c4\uc785\ud558\uac70\ub098, DML\uc774 \ube48\ubc88\ud55c \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \uc778\ub371\uc2a4\uc758 \uac1c\uc218\ub97c \uc904\uc5ec\uc8fc\ub294 \ub4f1\uc758 \uc870\uce58\ub97c \ud1b5\ud574 \uc644\ud654\ub420 \uc218 \uc788\uc744 \uac83\uc774\ub2e4.<\/p>\n

\ubd80\uc5f0\ud558\uc790\uba74, Shared Pool\uacfc \uad00\ub828\ub41c Wait\ub294 \ud2b9\uc815 \uc624\ube0c\uc81d\ud2b8 \uc790\uc6d0\uc5d0 \ub300\ud55c \uacbd\ud569\uc5d0 \uc758\ud574 \ubc1c\uc0dd\ud558\uae30 \ubcf4\ub2e4\ub294 \uc560\ud50c\ub9ac\ucf00\uc774\uc158\uc758 \uc124\uacc4, \ubcf4\ub2e4 \ub2e8\uc21c\ud654\uc2dc\ucf1c \ud45c\ud604\ud558\uba74 Literal SQL\uc5d0 \uc758\ud55c \uba54\ubaa8\ub9ac \uc870\uac01\ud654\uc5d0 \uc758\ud574 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\uac00 \ub9ce\ub2e4. \ub530\ub77c\uc11c, Shared Pool\uad00\ub828 Wait\uac00 \ub9ce\uc774 \ubc1c\uc0dd\ud558\uc5ec \uc624\ub77c\ud074\uc774 \uadf8\ub85c \uc778\ud55c \uc131\ub2a5\uc0c1\uc758 \ubb38\uc81c\ub97c \ub4dc\ub7ec\ub0bc \ub54c \ub208\uc5d0 \ub744\ub294 \ud558\ub098\uc758 \uc8fc\ubc94\uc744 \ucc3e\uc544\ub0b4\ub824\ub294 \ub178\ub825\uc740 \ubcc4 \ud6a8\uacfc\ub97c \uac70\ub450\uc9c0 \ubabb\ud558\ub294 \uacbd\uc6b0\uac00 \ub9ce\uc73c\uba70, \uadf8\ub7ec\ud55c \uc2dc\uc810\uc5d0 DBA\uac00 \uc989\uac01\uc801\uc73c\ub85c \ucde8\ud560 \uc218 \uc788\ub294 \uc870\uce58\ub85c\ub294 \uc9c1\uc811 Shared Pool\uc744 Flush \uc2dc\ud0a4\ub294 \uc815\ub3c4\uac00 \uc788\uc744 \uac83\uc774\ub2e4. \uacb0\uad6d, \ud3c9\uc18c\uc5d0 \uafb8\uc900\ud55c \ubaa8\ub2c8\ud130\ub9c1\uc744 \ud1b5\ud574 Shared Pool\uc758 \uc801\uc808\ud55c \ud06c\uae30\uc640 \uad00\ub828 \ud30c\ub77c\ubbf8\ud130 \uac12\uc744 \ucc3e\uc544\uac00\ub294 \uac83, \uadf8\ub9ac\uace0 \ubb34\uc5c7\ubcf4\ub2e4 \uc560\ud50c\ub9ac\ucf00\uc774\uc158 \uce21\uba74\uc5d0\uc11c \ud29c\ub2dd \ubc0f \uc218\uc815 \uc791\uc5c5\uc744 \uc9c4\ud589\ud568\uc73c\ub85c\uc368 \uc131\ub2a5\ubb38\uc81c\ub97c \uc0ac\uc804\uc5d0 \uc608\ubc29\ud558\ub294 \uac83\uc774 \ucd5c\uc120\uc774\ub2e4.<\/p>\n

[3] Buffer Cache \uad00\ub828 Wait<\/p>\n

\u25a0 Buffer Cache \uad6c\uc870<\/p>\n

Buffer Cache\uc758 \uae30\ubcf8\uc801\uc778 \uae30\ub2a5\uc740 \uc5ec\ub7ec \ud504\ub85c\uc138\uc2a4\uc5d0 \uc758\ud574 \uacf5\ud1b5\uc73c\ub85c \uc790\uc8fc \uc561\uc138\uc2a4 \ub418\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ube14\ub85d\uc744 \uba54\ubaa8\ub9ac\uc5d0 \uce90\uc26c\ud558\uc5ec \ubb3c\ub9ac\uc801\uc778 \ub514\uc2a4\ud06c IO\ub97c \ucd5c\uc18c\ud654\ud568\uc73c\ub85c\uc368 \ub354 \ube60\ub978 \uc561\uc138\uc2a4 \uc18d\ub3c4\ub97c \uc81c\uacf5\ud558\uae30 \uc704\ud55c \uac83\uc774\ub2e4. \ubcf5\uc7a1\ud55c \uc124\uba85\uc740 \uc0dd\ub7b5\ud558\uace0, Buffer Cache \uc758 \uae30\ubcf8\uad6c\uc870\ub97c \uc774\ud574\ud558\uae30 \uc704\ud55c \uba87 \uac00\uc9c0 \ud575\uc2ec \uc6a9\uc5b4\ub4e4\uc744 \uac04\ub2e8\ud788 \uc815\ub9ac\ud574 \ubcf4\ub3c4\ub85d \ud558\uaca0\ub2e4.<\/p>\n

\u25b7 Buffer header<\/p>\n

\ubaa8\ub4e0 \ubc84\ud37c \ube14\ub85d\ub4e4\uc740 \uac01\uc790\uc758 buffer header\ub97c \ud1b5\ud574 \uc561\uc138\uc2a4\ub418\uace0 \uad00\ub9ac\ub41c\ub2e4. \uc989, \uba54\ubaa8\ub9ac\uc5d0 \uce90\uc26c\ub41c \ud2b9\uc815 \ub370\uc774\ud130 \ube14\ub85d\uc5d0 \ub300\ud55c \uc561\uc138\uc2a4\ub294 \uba3c\uc800 \ud574\uc26c \uc54c\uace0\ub9ac\uc998\uc744 \ud1b5\ud574 cache chain \uc0c1\uc758 buffer header\ub97c \ucc3e\uace0 \ud574\ub2f9 buffer header\uc5d0 \uae30\ub85d\ub41c \ub370\uc774\ud130 \ube14\ub85d\uc758 \uba54\ubaa8\ub9ac\uc0c1 \uc8fc\uc18c\ub97c \ucc3e\uc544\uac00 \uc6d0\ud558\ub294 \uc815\ubcf4\ub97c \uc77d\ub294 \ubc29\uc2dd\uc73c\ub85c \uc774\ub8e8\uc5b4\uc9c4\ub2e4. Buffer header\uc5d0 \uae30\ub85d\ub418\ub294 \uc8fc\uc694\uc815\ubcf4\ub294 \ub2e4\uc74c\uacfc \uac19\uc73c\uba70 Buffer header\uc758 \ub0b4\uc6a9\uc740 V$bh \ubdf0\ub97c \ud1b5\ud558\uc5ec \uc870\ud68c\ud574\ubcfc \uc218 \uc788\ub2e4.<\/p>\n

– \uba54\ubaa8\ub9ac\uc0c1\uc5d0\uc11c\uc758 \ud574\ub2f9 \ubc84\ud37c\ube14\ub85d\uc758 \uc8fc\uc18c
\n– \ud574\ub2f9 \ubc84\ud37c \ube14\ub85d(\uc2e4\uc81c\ub85c\ub294 \ubc84\ud37c\ud5e4\ub354)\uac00 \ud3ec\ud568\ub418\uc5b4 \uc788\ub294 hash chain
\n– LRU, LRUW, CKPTQ\uc640 \uac19\uc740 \ub9ac\uc2a4\ud2b8\uc0c1\uc5d0\uc11c\uc758 \ud574\ub2f9 \ubc84\ud37c\ube14\ub85d\uc758 \uc704\uce58
\n– \ud574\ub2f9 \ubc84\ud37c\ube14\ub85d\uc5d0 \ub300\ud55c User, Waiter\uc640 \uc0c1\ud0dc\ub97c \ub098\ud0c0\ub0b4\ub294 \uac01\uc885 Flag<\/p>\n

\u25b7 Hash Buckets\/ Hash Chains<\/p>\n

Buffer Cache\uc758 \ubaa8\ub4e0 \ube14\ub85d\uc740 \ud574\uc26c \uc54c\uace0\ub9ac\uc998\uc744 \ud1b5\ud574 \uad00\ub9ac\ub41c\ub2e4. \uace7, \ub370\uc774\ud130 \ube14\ub85d\uc758 DBA, Class \uac12\uc73c\ub85c Hash Function\uc744 \uc801\uc6a9\ud558\uc5ec \ud574\ub2f9 \ube14\ub85d\uc774 \uc18d\ud558\ub294 hash buckets\uc744 \ud560\ub2f9\ud558\uba70, \ub3d9\uc77c\ud55c hash buckets\uc5d0 \ud560\ub2f9\ub418\ub294 \ub370\uc774\ud130 \ube14\ub85d\uc758 \ubc84\ud37c\ud5e4\ub354\ub4e4\uc740 linked list\ud615\ud0dc\ub85c hash chain\uc744 \uc774\ub8e8\uac8c \ub41c\ub2e4. Hash buckets\/hash chains\ub294 \ud2b9\uc815 \ub370\uc774\ud130 \ube14\ub85d\uc744 \ucc3e\uc544\uac00\uae30 \uc704\ud55c \uc218\ub2e8\uc744 \uc81c\uacf5\ud55c\ub2e4. \uac01\uac01\uc758 hash buckets\uc5d0\ub294 \uc790\uc2e0\uc5d0 \uc18d\ud55c hash chain\uc744 \ubcf4\ud638\ud558\uae30 \uc704\ud55c latch(cache buffers chains)\uac00 \ud560\ub2f9\ub41c\ub2e4.<\/p>\n

\u25b7 LRU<\/p>\n

LRU\ub294 \ub450\uac1c\uc758 \ub9ac\uc2a4\ud2b8, \uc989 LRUW\uc640 LRU \ub9ac\uc2a4\ud2b8\uc758 \uc30d\uc73c\ub85c \uad6c\uc131\ub41c\ub2e4. LRUW(LRU Write list)\ub294 dirty list\uc640 \uac19\uc740 \ub9d0\uc774\uba70, \uc218\uc815\ub418\uc5b4 \ub514\uc2a4\ud06c\uc5d0 \ubc18\uc601\ub418\uc5b4\uc57c \ud560 \ube14\ub85d\ub4e4\uc758 \ub9ac\uc2a4\ud2b8\uc774\ub2e4. LRU(Least recently used list)\ub294 LRUW\uc5d0 \uc62c\ub77c\uac00\uc9c0 \uc54a\uc740 \ub098\uba38\uc9c0 \ubc84\ud37c \ube14\ub85d\ub4e4\uc774 \ub4f1\ub85d\ub418\uc5b4 \uc788\ub2e4. Buffer cache \uc0c1\uc758 \ubc84\ud37c\ube14\ub85d\uc740 \ubc18\ub4dc\uc2dc LRU\ub098 LRUW \ub458 \uc911\uc758 \ud558\ub098\uc5d0 \ub4f1\ub85d\ub418\uba70, \ub450 \ub9ac\uc2a4\ud2b8\uc5d0 \ub3d9\uc2dc\uc5d0 \ud3ec\ud568\ub418\ub294 \uacbd\uc6b0\ub294 \uc5c6\ub2e4. LRU\ub294 Free Buffer\ub97c \ucc3e\uae30 \uc704\ud55c \uc218\ub2e8\uc744 \uc81c\uacf5\ud55c\ub2e4. \uacbd\ud569\uc744 \ud53c\ud558\uae30 \uc704\ud574 \ubc84\ud37c\uce90\uc26c \ube14\ub85d\ub4e4\uc744 \uc5ec\ub7ec \uac1c\uc758 LRU\uc30d\uc73c\ub85c \ub098\ub204\uc5b4 \uad00\ub9ac\ud560 \uc218 \uc788\uc73c\uba70, \uac01 LRU\ub9ac\uc2a4\ud2b8\ub97c \ubcf4\ud638\ud558\uae30 \uc704\ud574 Latch(Cache buffers lru chain)\uac00 \ud558\ub098\uc529 \ud560\ub2f9\ub41c\ub2e4.<\/p>\n

\u25a0 Buffer Cache \uc6b4\uc601\uaddc\uce59<\/p>\n

\u25b7 \uba54\ubaa8\ub9ac\uc0c1\uc758 \ud2b9\uc815 \ubc84\ud37c\ube14\ub85d\uc744 \ucc3e\uc544\uac00\uac70\ub098, \ud2b9\uc815 \ube14\ub85d\uc774 \uba54\ubaa8\ub9ac\uc5d0 \uce90\uc26c \ub418\uc5b4 \uc788\ub294\uc9c0\ub97c \ud655\uc778\ud558\uae30 \uc704\ud574\uc11c \uc624\ub77c\ud074\uc740 hash bucket\/hash chain \uad6c\uc870\ub97c \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n

\u25b7\uc0c8\ub85c\uc6b4 \ub370\uc774\ud130\ube14\ub85d\uc744 \ub514\uc2a4\ud06c\ub85c\ubd80\ud130 \uba54\ubaa8\ub9ac\ub85c \uc77d\uc5b4 \ub4e4\uc774\uae30 \uc704\ud55c free buffer\ub97c \ud655\ubcf4\ud558\uae30 \uc704\ud574 \uc624\ub77c\ud074\uc740 LRU \ub9ac\uc2a4\ud2b8\ub97c \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n

\u25b7 \ubc84\ud37c\ube14\ub85d\uc740 LRU\ub098 LRUW \ub458 \uac00\uc6b4\ub370 \ud558\ub098\uc5d0 \ub4f1\ub85d\ub41c\ub2e4.<\/p>\n

\u25b7 \ud558\ub098\uc758 \ube14\ub85d\uc5d0 \ub300\ud574 \uc2dc\uac04\ub300\uac00 \ub2e4\ub978 \uc5ec\ub7ec \uac1c\uc758 \ubcf5\uc0ac\ubcf8\uc774 \uc874\uc7ac\ud560 \uc218 \uc788\uc73c\uba70, \uadf8 \uac00\uc6b4\ub370 \uc624\uc9c1 CURRENT \ubc84\ud37c\ub9cc\uc774 \ubcc0\uacbd\ub420 \uc218 \uc788\ub2e4.<\/p>\n

\u25b7\ud558\ub098\uc758 \ubc84\ud37c\ube14\ub85d\uc740 \ud55c\ubc88\uc5d0 \uc624\uc9c1 \ud558\ub098\uc758 \ud504\ub85c\uc138\uc2a4\uc5d0 \uc758\ud574\uc11c\ub9cc \ubcc0\uacbd\ub420 \uc218 \uc788\ub2e4.<\/p>\n

\u25a0 Buffer Cache \uad00\ub828 Waits<\/p>\n

\ubc84\ud37c\uce90\uc26c\uc640 \uad00\ub828\ub418\uc5b4 \ud754\ud788 \ubc1c\uc0dd\ud558\ub294 \ub300\ud45c\uc801\uc778 Wait \uc774\ubca4\ud2b8\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

\u25b7 buffer busy waits<\/p>\n

\uc5ec\ub7ec \uc138\uc158\uc774 \ub3d9\uc2dc\uc5d0 \uac19\uc740 \ube14\ub85d\uc744 \uc77d\uc73c\ub824\uace0 \ud558\uac70\ub098 \uc5ec\ub7ec \uc138\uc158\uc774 \uac19\uc740 \ube14\ub85d\uc5d0 \ub300\ud55c \ubcc0\uacbd\uc791\uc5c5\uc774 \uc644\ub8cc\ub418\uae30\ub97c \uae30\ub2e4\ub9ac\uace0 \uc788\ub294 \uacbd\uc6b0\uc5d0 \ubc1c\uc0dd\ud558\uba70, \ud2b9\uc815 \ube14\ub85d\uc5d0 \ub300\ud55c \uacbd\ud569\uc744 \ud574\uc18c\ud558\uae30 \uc704\ud55c \uc870\uce58\ub294 \ube14\ub85d\uc758 \uc720\ud615\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c4\ub2e4. Data block\uc5d0 \ub300\ud55c \uacbd\ud569\uc774 \ub9ce\uc740 \uacbd\uc6b0\ub294 Pct free\ub098 Pct used \uac12\uc744 \uc0ac\uc6a9\ud558\uc5ec \ube14\ub85d \ub2f9 \ub85c\uc6b0\uc218\ub97c \uc904\uc774\uac70\ub098, \ud2b9\uc815 \ube14\ub85d\uc5d0 \ub85c\uc6b0 \uc785\ub825\uc774 \ubab0\ub9ac\ub294 \uad6c\uc870\uc758 \uc778\ub371\uc2a4(right-hand-index)\uc77c \uacbd\uc6b0\ub294 reverse key index\uc758 \uc0ac\uc6a9\uc744 \uac80\ud1a0\ud558\ub294 \ub4f1\uc758 \ubc29\ubc95\uc774 \uc788\uc73c\uba70, segment header\uc758 \uacbd\ud569\uc774 \ub9ce\uc740 \uacbd\uc6b0\ub294 freelist \uc218\ub97c \ub298\ub9ac\uac70\ub098 Extent\uc758 \ud06c\uae30\ub97c \uc99d\uac00\uc2dc\ud0a4\ub294 \ub4f1\uc758 \ubc29\ubc95\uc774 \uc788\uace0, undo header\ub098 undo block\uc5d0 \ub300\ud55c \uacbd\ud569\uc740 \ub864\ubc31\uc138\uadf8\uba3c\ud2b8\uc758 \uac1c\uc218\ub098 \ud06c\uae30\ub97c \uc99d\uac00\uc2dc\ud0a4\ub294 \uac83\uc774 \uc804\ud615\uc801\uc778 \uc870\uce58 \ubc29\ubc95\uc774\ub2e4. v$waitstat\uacfc x$kcbfwait\uc744 \uc774\uc6a9\ud558\uba70 Class \ub610\ub294 file\ubcc4\ub85c wait \ubc1c\uc0dd\uc0c1\ud669\uc744 \ud310\ub2e8\ud560 \uc218 \uc788\ub2e4.<\/p>\n

\u25b7 free buffer waits\/write complete waits<\/p>\n

DBWR\uac00 dirty buffer\ub97c write\ud558\ub294 \ub3d9\uc548 \uc11c\ubc84 \ud504\ub85c\uc138\uc2a4\uac00 \ub300\uae30\ud558\uace0 \uc788\ub294 \uacbd\uc6b0 \ubc1c\uc0dd\ud55c\ub2e4. \uace7, \ub108\ubb34\ub098 \ub9ce\uc740 dirty buffer\uac00 \uc0dd\uaca8\ub098\uac70\ub098 DBWR\uc758 \uc4f0\uae30 \uc18d\ub3c4\uac00 \ucda9\ubd84\ud788 \ud29c\ub2dd \ub418\uc9c0 \ubabb\ud55c \uacbd\uc6b0\uc5d0 \ubc1c\uc0dd\ud55c\ub2e4. \uc810\uac80 \ud3ec\uc778\ud2b8\ub294 \ubb3c\ub9ac\uc801 \ub514\uc2a4\ud06c\uc758 \uc18d\uc131(stripe size, layour, cache size) \ucd5c\uc801\ud654, Raw device\uc758 \ud65c\uc6a9, Async IO\ub098 multi-DBWR(db_writer_processes) \ud65c\uc6a9\uc5ec\ubd80 \ub4f1\uc774\ub2e4.<\/p>\n

\uc704\uc640 \uac19\uc740 \ubc84\ud37c \ube14\ub85d\uc5d0 \ub300\ud55c \uacbd\ud569 \uc5ed\uc2dc \ube44\ud6a8\uc728\uc801\uc778 \uc2e4\ud589\uacc4\ud68d\uc744 \ud1b5\ud574 \uc218\ud589\ub418\ub294 \uc560\ud50c\ub9ac\ucf00\uc774\uc158\uc5d0 \uc758\ud558\uc5ec \ubd88\ud544\uc694\ud558\uac8c \ub9ce\uc740 \ube14\ub85d\uc774 \uba54\ubaa8\ub9ac\ub85c \uc62c\ub77c\uc624\ub294 \uac83\uc774 \uc6d0\uc778\uc77c \uacbd\uc6b0\uac00 \ub9ce\uc73c\ubbc0\ub85c \uacbd\ud569\uc774 \ube48\ubc88\ud55c \ube14\ub85d\uc774 \uc18d\ud558\ub294 \ud14c\uc774\ube14\/\uc778\ub371\uc2a4 \uba85\uc744 \ucc3e\uc544\ub0bc \uc218 \uc788\ub2e4\uba74 \uad00\ub828 SQL\uc744 \ucc3e\uc544\ub0b4\uc5b4 \ubcf4\ub2e4 \ud6a8\uacfc\uc801\uc778 \ud29c\ub2dd\uc791\uc5c5\uc774 \uc774\ub8e8\uc5b4\uc9c8 \uc218 \uc788\uc744 \uac83\uc774\ub2e4. v$session_wait\uc758 p1,p2 \uceec\ub7fc\uc5d0 \uac01\uac01 file#, block#\uac12\uc744 \ud45c\uc2dc\ud558\uc5ec \uc8fc\ubbc0\ub85c \uc774 \uac12\uc744 \uc774\uc6a9\ud558\uc5ec \uc544\ub798\uc758 SQL\ubb38\uc73c\ub85c \ud604\uc7ac \uc5b4\ub5a4 \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud558\uc5ec \ud574\ub2f9 wait\uac00 \ubc1c\uc0dd\ud558\uace0 \uc788\ub294\uc9c0\ub97c \ucd94\uc801\ud560 \uc218 \uc788\ub2e4. ( 1\ud68c\uc5d0 \uc18c\uac1c\ud55c SQL\ubb38\uc5d0\uc11c\ub294 Additional Info \uac12\uc744 \ucc38\uc870. )<\/p>\n

select segment_name, segment_type
\nfrom dba_extents
\nwhere file_id = :file#
\nand :block# between block_id and block_id + blocks -1<\/p>\n

\u25b7 cache buffers chains latch<\/p>\n

SGA\ub0b4\uc5d0 \uce90\uc26c\ub41c \ub370\uc774\ud130\ube14\ub85d\uc744 \uac80\uc0c9\ud560 \ub54c \uc0ac\uc6a9\ub41c\ub2e4. \ubc84\ud37c\uce90\uc26c\ub294 \ube14\ub85d\ub4e4\uc758 chain\uc744 \uc774\ub8e8\uace0 \uc788\uc73c\ubbc0\ub85c \uac01\uac01\uc758 chain\uc740 \uc774 Latch\uc758 child\ub4e4\uc5d0 \uc758\ud574 \ubcf4\ud638\ub41c\ub2e4. \uc774 Latch\uc5d0 \ub300\ud55c \uacbd\ud569\uc740 \ud2b9\uc815 \ube14\ub85d\uc5d0 \ub300\ud55c \ub300\ub7c9\uc758 \ub3d9\uc2dc \uc561\uc138\uc2a4\uac00 \ubc1c\uc0dd\ud560 \ub54c \uc720\ubc1c\ub41c\ub2e4. \uc560\ud50c\ub9ac\ucf00\uc774\uc158\uc744 \uac80\ud1a0\ud574 \ubcf4\uc544\uc57c \ud55c\ub2e4.
\n\u00d8 cache buffers lru chain latch
\n\ubc84\ud37c\uce90\uc26c\uc758 \ubc84\ud37c\ub97c LRU \uc815\ucc45\uc5d0 \ub530\ub77c \uc774\ub3d9\uc2dc\ucf1c\uc57c \ud560 \ud544\uc694\uac00 \uc788\ub294 \uacbd\uc6b0 \ud504\ub85c\uc138\uc2a4\ub294 \uc774 Latch \ud68d\ub4dd\ud558\uac8c \ub41c\ub2e4. \uc774 Latch\uc5d0 \ub300\ud55c \uacbd\ud569\uc740 Multiple buffer pool\uc744 \uc0ac\uc6a9\ud558\uac70\ub098 DB_BLOCK_LRU_LATCHES \ub97c \uc99d\uac00\uc2dc\ucf1c LRU Latch\uc758 \uac1c\uc218\ub97c \ub298\ub824\uc11c \ud574\uc18c\ud560 \uc218 \uc788\ub2e4. SQL\ubb38\uc744 \ud29c\ub2dd\ud558\uba74 \ud574\ub2f9 \ud504\ub85c\uc138\uc2a4\uc5d0 \uc758\ud574 \uc561\uc138\uc2a4 \ub420 \ube14\ub85d\uc758 \uc218\uac00 \uc904\uc5b4\ub4e4 \uac83\uc774\ubbc0\ub85c \ub2f9\uc5f0\ud788 \ud6a8\uacfc\ub97c \uac70\ub458 \uc218 \uc788\ub2e4.<\/p>\n

\uc704\uc640 \uac19\uc774 \ubc84\ud37c\uce90\uc26c\ub97c \uad00\ub9ac\ud558\ub294 Latch\uc5d0 \ub300\ud55c \uacbd\ud569\uc740 \uacbd\ud569\uc774 \uc9d1\uc911\ub418\ub294 \ud2b9\uc815 Child Latch\uc5d0 \uc758\ud574 \uad00\ub9ac\ub418\ub294 \ubc84\ud37c\ube14\ub85d\uc744 \ucc3e\uc544 \ud574\ub2f9 \ube14\ub85d\uc774 \uc18d\ud55c \uc138\uadf8\uba3c\ud2b8 \uc815\ubcf4\ub97c \uc54c\uc544\ub0b8\ub2e4\uba74 \ubcf4\ub2e4 \ud6a8\uacfc\uc801\uc778 \uc870\uce58\uac00 \uac00\ub2a5\ud560 \uac83\uc778\ub370, latch free wait\uc77c \uacbd\uc6b0 v$session_wait\uc758 p1raw \uac12\uc774 \ud574\ub2f9 Latch address\ub97c \uc758\ubbf8\ud55c\ub2e4. \uc774 \uac12\uc744 x$bh\uc758 hladdr \uac12\uacfc \uc870\uc778\ud558\uba74 \uad00\ub828 \uc624\ube0c\uc81d\ud2b8 \uc774\ub984\uc744 \ucd94\uc801\ud574\ubcfc \uc218 \uc788\ub2e4.<\/p>\n

select file#, dbarfil, dbablk, obj, o.name
\nfrom x$bh bh, obj$ o
\nwhere bh.hladdr = :latch_address
\nand bh.obj = o.obj#;<\/p>\n

[5] Redo buffer \uad00\ub828 Wait<\/p>\n

\u25a0 Redo buffer \uad6c\uc870<\/p>\n

\uc624\ub77c\ud074 \ub9ac\ub450 \uad6c\uc870\uc758 \ud575\uc2ec\uc740 \ubaa8\ub4e0 \ud2b8\ub79c\uc7ad\uc158 \uc815\ubcf4\ub97c OS \ud30c\uc77c\uc5d0 \uae30\ub85d\ud574 \ub460\uc73c\ub85c\uc368 \uc2dc\uc2a4\ud15c \uc7a5\uc560\uac00 \ubc1c\uc0dd\ud574\ub3c4 \ud2b8\ub79c\uc7ad\uc158 \ub2e8\uc704\uc758 \uc77c\uad00\uc131\uc744 \uc783\uc9c0 \uc54a\uace0 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \ubcf5\uad6c\ud560 \uc218 \uc788\ub3c4\ub85d \ud558\uaca0\ub2e4\ub294 \uac83\uc774\ub2e4. \ub9ac\ub450\ubc84\ud37c(redo buffer)\ub294 \uc774\ucc98\ub7fc \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0 \uac00\ud574\uc9c4 \ubaa8\ub4e0 \ubcc0\uacbd\ub0b4\uc5ed\uc744 \ud30c\uc77c\uc5d0 \uae30\ub85d \ud558\uae30 \uc704\ud574 \uc7a0\uc2dc \uc0ac\uc6a9\ub418\ub294 \uba54\ubaa8\ub9ac \uc601\uc5ed\uc774\uba70 \ub9ac\ub450\ubc84\ud37c\uc5d0 \uae30\ub85d\ub41c \ub9ac\ub450 \uc815\ubcf4\ub294 \ub2e4\uc2dc \ub9ac\ub450\ub85c\uadf8 \ud30c\uc77c\uc5d0 \uae30\ub85d\ub418\uc5b4\uc9d0\uc73c\ub85c\uc368 \ud5a5\ud6c4 \uc2dc\uc2a4\ud15c \ubcf5\uad6c \uc791\uc5c5\uc774 \ud544\uc694\ud560 \ub54c\uc5d0 \uc0ac\uc6a9\ud558\uac8c \ub41c\ub2e4. \uc624\ub77c\ud074\uc758 \ub9ac\ub450 \uad6c\uc870\ub97c \uc774\ud574\ud558\uae30 \uc704\ud55c \ud575\uc2ec\uc801\uc778 \uac1c\ub150\uc744 \uac04\ub2e8\ud788 \uc815\ub9ac\ud574\ubcf4\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

\ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0 \ub300\ud55c \ubcc0\uacbd\ub0b4\uc5ed\uc740 \ube14\ub85d\ub2e8\uc704\ub85c \uc800\uc7a5\ub41c\ub2e4. \ubb3c\ub860 \ubcc0\uacbd\ub418\ub294 \ubaa8\ub4e0 \ube14\ub85d\uc758 \ubcf5\uc0ac\ubcf8\uc744 \ud1b5\uc9f8\ub85c \uc800\uc7a5\ud558\ub294 \uac83\uc740 \uc544\ub2c8\uace0 \ube14\ub85d\ubcc4\ub85c \uc5b4\ub5a0\ud55c \uc624\ud37c\ub808\uc774\uc158\uc744 \uc218\ud589\ud558\ub294\uac00, \uadf8\ub9ac\uace0 \uadf8\ub7ec\ud55c \ube14\ub85d\ubcc4 \uc624\ud37c\ub808\uc774\uc158\uc744 \uc5b4\ub5a0\ud55c \uc21c\uc11c\ub85c \uc218\ud589\ud558\ub294\uac00\ub97c \uae30\ub85d\ud55c\ub2e4. \uc774\ub7ec\ud55c \ube14\ub85d\ubcc4 \ub2e8\uc704\uc561\uc158\uc744 change vector\ub77c\uace0 \ubd80\ub974\uba70 change vector\uac00 \uc21c\ucc28\uc801\uc73c\ub85c \ubaa8\uc5ec \ud558\ub098\uc758 \uc758\ubbf8 \uc788\ub294 redo record\uac00 \ub41c\ub2e4. \ub9ac\ub450\ub85c\uadf8\ub294 \uc2dc\uc2a4\ud15c\ub0b4\uc758 \ubaa8\ub4e0 \ud504\ub85c\uc138\uc2a4\ub4e4\uc5d0 \uc758\ud574 \uc0dd\uc131\ub418\ub294 redo record\ub97c SCN \uc21c\uc11c\ub300\ub85c \uc800\uc7a5\ud574\ub193\uc740 \uac83\uc774\ub2e4. \uc774\ub54c \ub9ac\ub450\ub85c\uadf8\uc5d0 \uae30\ub85d\ub418\ub294 \ub0b4\uc6a9\uc5d0\ub294 \ud14c\uc774\ube14\uc774\ub098 \uc778\ub371\uc2a4 \ub4f1\uc758 \ub370\uc774\ud130 \ube14\ub85d \ubfd0\ub9cc \uc544\ub2c8\ub77c UNDO \ube14\ub85d \ub610\ub294 UNDO \uc138\uadf8\uba3c\ud2b8 \ud5e4\ub354\ube14\ub85d\uc5d0 \ub300\ud55c \ubcc0\uacbd\ub0b4\uc6a9\uc744 \ud3ec\ud568\ud558\ub294 \ubaa8\ub4e0 \ubc84\ud37c\uce90\uc26c \ube14\ub85d\uc5d0 \ub300\ud55c \ubcc0\uacbd\ub0b4\uc5ed\uc774 \ub300\uc0c1\uc774 \ub41c\ub2e4.<\/p>\n

\ub9ac\ub450 \uc815\ubcf4\ub294 \ud56d\uc0c1 \uc2e4\uc81c \ubcc0\uacbd\uc791\uc5c5\ubcf4\ub2e4 \uba3c\uc800 \ubcf4\uad00\ub418\uc5b4\uc57c \uc5b4\ub5a4 \uc0c1\ud669\uc5d0\uc11c\ub3c4 \ubcf5\uad6c\uac00 \uac00\ub2a5\ud574\uc9c4\ub2e4. \ub530\ub77c\uc11c \ud2b8\ub79c\uc7ad\uc158\uc744 \uc218\ud589\ud558\ub294(\ub370\uc774\ud130\ubca0\uc774\uc2a4 \ube14\ub85d\uc5d0 \ubcc0\uacbd\uc744 \uac00\ud558\ub294) \ud504\ub85c\uc138\uc2a4\ub294 \uc6b0\uc120 \uc790\uc2e0\uc758 \uba54\ubaa8\ub9ac \uc601\uc5ed \ub0b4\uc5d0\uc11c \uc218\ud589\ud558\uace0\uc790 \ud558\ub294 \uc791\uc5c5\uc5d0 \ub300\ud55c \ub9ac\ub450 \ub808\ucf54\ub4dc\ub97c \ub9cc\ub4e4\uba70, \uc774\ub97c \uba3c\uc800 \ub85c\uadf8\ubc84\ud37c\uc5d0 \uae30\ub85d\ud558\uace0 \ub09c \ud6c4\uc5d0 \uc2e4\uc81c \ubc84\ud37c\ube14\ub85d\uc5d0\ub3c4 \ub9ac\ub450 \ub808\ucf54\ub4dc\uc5d0 \ub2f4\uae34 \ub0b4\uc6a9\uc744 \ub530\ub77c \uc801\uc6a9\ud558\uac8c \ub41c\ub2e4. \ub610\ud55c \uac19\uc740 \uc774\uc720\ub85c \uc624\ub77c\ud074\uc740 \ubcc0\uacbd\ub41c \ubc84\ud37c\uce90\uc26c \ube14\ub85d\uc744 \ub514\uc2a4\ud06c\uc5d0 \uae30\ub85d\ud558\uae30 \uc804\uc5d0 \uba3c\uc800 \uad00\ub828\ub41c \ub85c\uadf8\ubc84\ud37c\ub97c \ub85c\uadf8\ud30c\uc77c\uc5d0 \uae30\ub85d\ud558\ub294 \uc791\uc5c5\uc744 \ucc98\ub9ac\ud558\uac8c \ub41c\ub2e4. \ub530\ub77c\uc11c, \ub9ac\ub450 \ubc84\ud37c \ub610\ub294 \ub9ac\ub450 \ud30c\uc77c (\uc544\uce74\uc774\ube0c \ud30c\uc77c\uc744 \ud3ec\ud568\ud574\uc11c)\uc5d0 \ub300\ud55c \uc4f0\uae30 \uc791\uc5c5\uc5d0 \ubcd1\ubaa9\uc774 \uc0dd\uae30\uba74 \uc2dc\uc2a4\ud15c\uc5d0 \ub300\ud55c \ubaa8\ub4e0 \uc791\uc5c5 \uc218\ud589\uc774 \ub300\uae30 \uc0c1\ud0dc\ub85c \ube60\uc9c0\uac8c \ub420 \uac83\uc774\ub2e4.<\/p>\n

\ud2b8\ub79c\uc7ad\uc158 \ucee4\ubc0b\uc744 \uc694\uccad\ud55c \ud504\ub85c\uc138\uc2a4\ub294 \uc6b0\uc120 \ud574\ub2f9 \ud2b8\ub79c\uc7ad\uc158\uc5d0 \ub300\ud55c \ub85c\uadf8\ubc84\ud37c\uac00 \ub9ac\ub450\ub85c\uadf8 \ud30c\uc77c\uc5d0 \uae30\ub85d\ub418\ub294 \uc791\uc5c5\uc774 \uc644\ub8cc\ub41c \ud6c4\uc5d0\uc57c \ucee4\ubc0b \uc644\ub8cc \uba54\uc138\uc9c0\ub97c \ubc1b\uc744 \uc218 \uc788\ub2e4. \uadf8\ub807\uac8c \ud568\uc73c\ub85c\uc368 \ubc84\ud37c\uce90\uc26c \ubcc0\uacbd\ub0b4\uc5ed\uc744 \ubaa8\ub450 \ub514\uc2a4\ud06c\uc5d0 \ubc18\uc601\ud558\uc9c0 \uc54a\uace0\ub3c4 \uc2dc\uc2a4\ud15c\uc758 \ube44\uc815\uc0c1 \uc885\ub8cc\uc2dc \ub9ac\ub450\ud30c\uc77c\uc5d0 \uc800\uc7a5\ub41c \ub9ac\ub450 \ub808\ucf54\ub4dc\ub85c\ubd80\ud130 \ucee4\ubc0b \ud2b8\ub79c\uc7ad\uc158\uc744 \ubcf4\uc874\ud560 \uc218 \uc788\uac8c \ub41c\ub2e4.<\/p>\n

\u25a0\ub9ac\ub450 \ubc84\ud37c\uad00\ub828 Wait \uc774\ubca4\ud2b8<\/p>\n

\uc77c\ubc18\uc801\uc73c\ub85c\ub294 \ub85c\uadf8\ubc84\ud37c \uad00\ub828\ud574\uc11c \uc2ec\uac01\ud55c Waiting\uc774 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\ub294 \ub4dc\ubb3c\uc9c0\ub9cc, \uac00\ub054 \ubcfc \uc218 \uc788\ub294 \ub9ac\ub450 \uad00\ub828 Wait \uc774\ubca4\ud2b8\ub85c\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \uac83\ub4e4\uc774 \uc788\ub2e4.<\/p>\n

\u25b7 Log file parallel write<\/p>\n

LGWR\uac00 OS\uc5d0 \ub9ac\ub450 \ubc84\ud37c\ub97c \ub85c\uadf8\ud30c\uc77c\uc5d0 \uae30\ub85d\ud558\ub3c4\ub85d \uc694\uccad\ud574 \ub454 \uc0c1\ud0dc\uc5d0\uc11c \ub300\uae30\ud558\uace0 \uc788\ub294 \uc774\ubca4\ud2b8\uc774\ub2e4. \uc774 \uacbd\uc6b0\uc5d0\ub294 DML \uc791\uc5c5\uc2dc nologging \uc635\uc158 \ub4f1\uc744 \uc0ac\uc6a9\ud558\uc5ec \uc2dc\uc2a4\ud15c\uc5d0\uc11c \ubc1c\uc0dd\ud558\ub294 \ub9ac\ub450 \ub808\ucf54\ub4dc\uc758 \uc808\ub300\ub7c9\uc744 \uc904\uc774\uac70\ub098 \ud558\ub4dc\uc6e8\uc5b4\uc801\uc73c\ub85c DISK IO\ub97c \uac1c\uc120\uc2dc\ucf1c\uc8fc\ub294 \uac83\uc774 \ubc29\uc548\uc774\ub2e4.<\/p>\n

\u25b7Log buffer space<\/p>\n

\ud504\ub85c\uc138\uc2a4\uac00 \ub85c\uadf8\ubc84\ud37c\ub97c \ud560\ub2f9\ud558\uae30 \uc704\ud574 \ub300\uae30\ud558\ub294 \uc774\ubca4\ud2b8\uc778\ub370 LGWR\uac00 \ub85c\uadf8\ubc84\ud37c\ub97c \ube44\uc6b0\ub294 \uac83\ubcf4\ub2e4 \ub354 \ube60\ub978 \uc18d\ub3c4\ub85c \ud504\ub85c\uc138\uc2a4\ub4e4\uc774 \ub9ac\ub450 \ub808\ucf54\ub4dc\ub97c \uc0dd\uc131\ud558\uace0 \uc788\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. \ub85c\uadf8\ubc84\ud37c\uc758 \ud06c\uae30\ub97c \ub298\ub824\uc8fc\uac70\ub098, DISK IO\uc758 \uc18d\ub3c4\ub97c \uac1c\uc120\uc2dc\ucf1c \uc8fc\uc5b4\uc57c \ud560 \uac83\uc774\ub2e4. \ub85c\uadf8\ubc84\ud37c\ub294 \ub85c\uadf8\ud30c\uc77c\uc5d0 \ub300\uc751\ub418\ub294 \ube14\ub85d\uc774 \ub9f5\ud551\uc774 \ub41c \ud6c4\uc5d0 \uc0ac\uc6a9\ub420 \uc218 \uc788\uc73c\ubbc0\ub85c \ub85c\uadf8 \uc2a4\uc704\uce58 \ubc1c\uc0dd\uc2dc\uc5d0\ub3c4 log buffer space \uc774\ubca4\ud2b8\uc5d0 \ub300\ud55c \ub300\uae30\uac00 \ubc1c\uc0dd\ud560 \uc218 \uc788\ub2e4. \ub85c\uadf8 \uc2a4\uc704\uce58\uac00 \ub108\ubb34 \uc7a6\ub2e4\uba74 \ub9ac\ub450 \ub85c\uadf8 \ud30c\uc77c\uc758 \ud06c\uae30\ub97c \uc99d\uac00\uc2dc\ucf1c\uc8fc\ub294 \uac83\uc774 \uc88b\ub2e4.<\/p>\n

\u25b7 Log file sync<\/p>\n

\ud504\ub85c\uc138\uc2a4\uac00 \ucee4\ubc0b\uc774\ub098 \ub864\ubc31\uc744 \uc218\ud589\ud560 \uacbd\uc6b0 \uc6b0\uc120 LGWR\uc5d0\uac8c \ud574\ub2f9 \ud2b8\ub79c\uc7ad\uc158\uae4c\uc9c0\uc758 \ub85c\uadf8\ubc84\ud37c\ub97c Write\ud558\ub3c4\ub85d \uc694\uccad\ud558\uac8c \ub418\ub294\ub370 \uc774\ub54c \uc0ac\uc6a9\uc790 \ud504\ub85c\uc138\uc2a4\ub294 LGWR\uac00 \uc4f0\uae30 \uc791\uc5c5\uc744 \uc644\ub8cc\ud560 \ub54c\uae4c\uc9c0 log file sync \uc774\ubca4\ud2b8\ub97c \ub300\uae30\ud558\uac8c \ub41c\ub2e4. \ubc84\uc804 8i \uc774\uc804\uc5d0\uc11c\ub294 DBWR\uac00 \uc4f0\uae30 \uc791\uc5c5\uc744 \uc218\ud589\ud558\ub2e4\uac00 \uc544\uc9c1 \uad00\ub828 \ub85c\uadf8\ubc84\ud37c\uac00 \ud30c\uc77c\uc5d0 \uc4f0\uc5ec\uc9c0\uc9c0 \uc54a\uc744 \uacbd\uc6b0\uc5d0\ub3c4 LGWR\uc5d0 \uc4f0\uae30\ub97c \uc694\uccad\ud558\uace0 log file sync \uc774\ubca4\ud2b8\uc5d0 \ub300\uae30\ud558\uc600\uc73c\ub098 8i \uc774\uc0c1\uc5d0\uc11c\ub294 log file sync\uc5d0 \ub300\uae30\ud558\ub294 \ub300\uc2e0 deferred write queue\uc5d0 \ub4f1\ub85d\ud55c\ub2e4. \ub530\ub77c\uc11c \ubc84\uc804 8i \uc774\uc0c1\uc5d0\uc11c log file sync \uc774\ubca4\ud2b8\ub294 \uc0ac\uc6a9\uc790 \ud504\ub85c\uc138\uc2a4\uc5d0 \uc758\ud574 \uc694\uccad\ub418\ub294 \ucee4\ubc0b, \ub864\ubc31 \ucc98\ub9ac \uc2dc\uc5d0 \ubc1c\uc0dd\ud558\uba70 \uacb0\uad6d, \uc2dc\uc2a4\ud15c \uc804\uccb4\uc801\uc73c\ub85c \ucee4\ubc0b, \ub864\ubc31\uc774 \uc9c0\ub098\uce58\uac8c \uc790\uc8fc \uc218\ud589\ub418\uac70\ub098 \uc0c1\ub300\uc801\uc73c\ub85c LGWR\uc758 \uc4f0\uae30 \uc18d\ub3c4\uac00 \ub290\ub9b0 \uac83\uc774 \uc6d0\uc778\uc77c \uac83\uc774\ub2e4. \ub610\ub294, \ub85c\uadf8 \ubc84\ud37c\uac00 \ub108\ubb34 \ucee4\uc11c LGWR\uac00 \ubc31\uadf8\ub77c\uc6b4\ub4dc\ub85c flush \uc2dc\ucf1c\uc8fc\uae30 \uc804( \ubcf4\ud1b5 3\ucd08 \uac04\uaca9 \ubc0f 1\/3 \uc774\uc0c1\uc758 \ub85c\uadf8\ubc84\ud37c\uac00 \ucc2c \uacbd\uc6b0)\uc5d0 \ucee4\ubc0b\uc5d0 \uc758\ud55c \uc4f0\uae30 \uc694\uccad\uc774 \uc774\ub8e8\uc5b4\uc9c0\ubbc0\ub85c \ucee4\ubc0b \uc2dc\uc810\uc5d0 \uc368\uc57c \ud560 \uc591\uc774 \ub9ce\uc544 \ub300\uae30\uc2dc\uac04\uc774 \uae38\uc5b4\uc9c0\ub294 \uacbd\uc6b0\ub3c4 \uc788\ub294\ub370 \uc774 \uacbd\uc6b0\uc5d4 \ub9ac\ub450 \ubc84\ud37c\uc758 \ud06c\uae30\ub97c \uc624\ud788\ub824 \uc904\uc5ec\uc8fc\uc5b4\uc57c \ud560 \uac83\uc774\ub2e4. \ub610\ub294, LGWR wait for redo copy \uc774\ubca4\ud2b8\uac00 \ub9ce\uc774 \ub098\ud0c0\ub09c\ub2e4\uba74 redo copy latch\uac00 \ub108\ubb34 \ub9ce\uc544 LGWR\uc774 \uc0ac\uc6a9\uc790 \ud504\ub85c\uc138\uc2a4\uac00 \ubc84\ud37c \uc4f0\uae30 \uc791\uc5c5\uc744 \ub9c8\uce60 \ub54c\uae4c\uc9c0 \uae30\ub2e4\ub9ac\ub294 \uc77c\uc774 \uc7a6\uc740 \uacbd\uc6b0\ub97c \ub73b\ud558\uba70 \uc774 \uacbd\uc6b0\uc5d4 _LOG_SIMULTANEOUS_COPIES \ud30c\ub77c\ubbf8\ud130\ub97c \uc0ac\uc6a9\ud558\uc5ec copy latch\uc758 \uc218\ub97c \uc904\uc5ec\uc8fc\ub294 \uc870\uce58\uac00 \ud544\uc694\ud560 \uac83\uc774\ub2e4.<\/p>\n

\uc2dc\uc2a4\ud15c\uc5d0 \ub530\ub77c\uc11c \uc5b8\uae09\ud55c \uc678\uc758 \ub2e4\uc591\ud55c \uc774\ubca4\ud2b8 \ub300\uae30\uc640 \uc6d0\uc778\uc774 \uc874\uc7ac\ud560 \uc218 \uc788\uace0, \ub354\uad6c\ub098 \ubc84\uc804\uc5d0 \ub530\ub77c redo copy latch\uc640 redo allocation latch\ub97c \ud3ec\ud568\ud55c \ub9ac\ub450 \uc6b4\uc601 \ubc29\uc2dd\uc0c1 \uc0c1\uc774\ud55c \ubd80\ubd84\uc774 \ub9ce\uc774 \uc874\uc7ac\ud558\uc5ec \uadf8\uc5d0 \ub530\ub978 \ucd94\uac00\uc801\uc778 \ud29c\ub2dd\uc694\uc18c\uac00 \uc788\uc73c\ub098 \uc774 \uae00\uc5d0\uc11c\ub294 \uc9c0\uba74 \uad00\uacc4\uc0c1 8i\ub97c \uae30\uc900\uc73c\ub85c \uac04\ub7b5\ud788 \uc815\ub9ac\ud574 \ubcf4\uc558\ub2e4.<\/p>\n

[6] Top SQL \ud29c\ub2dd\ud558\uae30 (\ub9fa\uc74c)<\/p>\n

\u25a0 Top SQL \ud29c\ub2dd\uc758 \ud544\uc694\uc131<\/p>\n

\uc9c0\ub09c \ud68c\uae4c\uc9c0 \uc2e4\uc2dc\uac04 Wait Event \ubaa8\ub2c8\ud130\ub9c1\uacfc \uc774\ubca4\ud2b8\ubcc4 \uc6d0\uc778\ubd84\uc11d\uc5d0 \ub300\ud574\uc11c \uac04\ub2e8\ud788 \uc0b4\ud3b4\ubcf4\uc558\ub2e4. \uc77c\uc2dc\uc801 \uc131\ub2a5\uc7a5\uc560 \uc2dc \uc7ac\ube68\ub9ac \uc6d0\uc778\uc744 \ucc3e\uc544\ub0b4\ub294 \uac83\ub3c4 \uc911\uc694\ud558\uc9c0\ub9cc \ubcf4\ub2e4 \ubc14\ub78c\uc9c1\ud55c \uac83\uc740 \uc774\ub7ec\ud55c \uc131\ub2a5\ubb38\uc81c\ub97c \uc0ac\uc804\uc5d0 \ucd5c\ub300\ud55c \uc608\ubc29\ud558\ub294 \uac83\uc784\uc740 \ub450\ub9d0\ud560 \ud544\uc694\ub3c4 \uc5c6\ub2e4. \uc624\ub77c\ud074 \uc131\ub2a5\ubb38\uc81c\ub97c \ub2e4\ub8e8\ub294\ub370 \uc788\uc5b4 \uac15\uc870\ud558\uace0 \uc2f6\uc740 \ud55c\uac00\uc9c0\ub294 \uc2dc\uc2a4\ud15c \uc790\uc6d0\uc758 \ubc30\ubd84\uc744 \ubcc0\uacbd\ud558\uac70\ub098 \uc99d\uac00\ub97c \uace0\ub824\ud558\uae30 \uc804\uc5d0, \ubd88\ud544\uc694\ud55c \uc791\uc5c5\uc744 \ucd5c\uc18c\ud654\ud568\uc73c\ub85c\uc368 \uc790\uc6d0\uc694\uad6c \ud69f\uc218\uc640 \uc790\uc6d0\uc810\uc720 \uc2dc\uac04\uc744 \uc904\uc5ec\uc8fc\ub294 \ub178\ub825\uc774 \uc120\ud589\ub418\uc5b4\uc57c \ud55c\ub2e4\ub294 \uc810\uc774\ub2e4. Wait Event\uc5d0 \ub300\ud55c \ubaa8\ub2c8\ud130\ub9c1\uacfc \ubd84\uc11d\uc774 DBMS\uc758 \uc790\uc6d0\uc5d0 \ub300\ud55c \uacbd\ud569\uacfc \uad00\ub828\ub41c \uc131\ub2a5\ubb38\uc81c\ub97c \ud30c\uc545\ud558\ub294\ub370 \uc720\uc6a9\ud55c \ubc29\ubc95\uc784\uc5d0 \ud2c0\ub9bc\uc5c6\uc9c0\ub9cc, \uc774 \uac19\uc740 \uc815\ubcf4\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ud639\uc740 \uadf8 \ud558\uc704 \ub808\ubca8\uc758 \uad6c\uc870\uc801 \ube44\ud6a8\uc728\uc131\uc744 \ub4dc\ub7ec\ub0b4\uc5b4\uc904 \ubfd0 \uc560\ud50c\ub9ac\ucf00\uc774\uc158 \ub808\ubca8\uc758 \ubb38\uc81c\ub97c \uc9c1\uc811\uc801\uc73c\ub85c \uc54c\ub824\uc8fc\uc9c0\ub294 \uc54a\ub294\ub2e4. \uac04\ub2e8\ud788 \ub9d0\ud558\uc790\uba74, DBMS \ud29c\ub2dd\uc744 \uc704\ud574 \uc815\uc131\uc744 \uc3df\uae30 \uc774\uc804\uc5d0 \uc560\ud50c\ub9ac\ucf00\uc774\uc158 \ud29c\ub2dd\uc5d0 \ub354 \ub9ce\uc740 \ud22c\uc790\ub97c \ud558\ub77c\ub294 \uac83\uc774\ub2e4. \uc774\ub7f0 \uad00\uc810\uc5d0\uc11c DBA\uac00 \ube44\uad50\uc801 \uc190\uc27d\uac8c \uc218\ud589\ud560 \uc218 \uc788\ub294 \uac83\uc774 \uc624\ub77c\ud074 \uba54\ubaa8\ub9ac\ub85c\ubd80\ud130 \uc545\uc131 SQL\uc744 \ucd94\ucd9c\ud558\uc5ec \ud29c\ub2dd\ud558\ub294 Top SQL \ud29c\ub2dd\uc774\ub2e4.<\/p>\n

\u25a0Top SQL \ucd94\ucd9c\uae30\uc900<\/p>\n

\uc0ac\uc6a9\uc790\ub85c\ubd80\ud130 \uc694\uccad\ub418\uc5b4 \uc624\ub77c\ud074 \ub0b4\uc5d0\uc11c \ucc98\ub9ac\ub418\ub294 \ubaa8\ub4e0 SQL\uc740 \uc624\ub77c\ud074\uc758 \uacf5\uc720 \uba54\ubaa8\ub9ac \uc601\uc5ed \uac00\uc6b4\ub370 shared pool\ub0b4\uc5d0 \uce90\uc26c \ub418\uc5b4 \uc9c0\uba70 \uc774\ub807\uac8c \uce90\uc26c \ub418\uc5b4 \uc788\ub294 SQL\uacfc \uad00\ub828 \ud1b5\uacc4\uc815\ubcf4\ub294 V$SQL \ub610\ub294 V$SQLAREA \ubdf0\ub97c \ud1b5\ud574\uc11c \uc870\ud68c\ud560 \uc218 \uc788\ub2e4. \uc774\ub54c, Top SQL\uc744 \ucd94\ucd9c\ud558\ub294\ub370 \uc911\uc694\ud558\uac8c \uc0ac\uc6a9\ub418\ub294 \ud56d\ubaa9\uc740 buffer_gets, disk_reads, executions, rows_processed \ub4f1\uc774\uba70 \uc77c\ubc18\uc801\uc73c\ub85c \uc544\ub798\uc640 \uac19\uc740 \uae30\uc900\uc73c\ub85c Top SQL\uc744 \ucd94\ucd9c\ud55c\ub2e4.<\/p>\n

\u25b7 Buffer gets \uc218\uce58\uac00 \ub192\uc740 SQL<\/p>\n

Buffer gets\uc740 \ud574\ub2f9 SQL\uc774 \ucc98\ub9ac\ub418\ub294 \uacfc\uc815\uc5d0\uc11c \uc561\uc138\uc2a4\ud55c \uba54\ubaa8\ub9ac \ube14\ub85d\uc758 \uc218(Logical IO)\ub97c \uc758\ubbf8\ud55c\ub2e4. \ubb3c\ub860 \uc774 \uac12\uc774 \ub192\ub2e4\uace0 \ud574\uc11c \ubb34\uc870\uac74 \uc545\uc131 SQL\uc784\uc744 \uc758\ubbf8\ud558\ub294 \uac83\uc740 \uc544\ub2c8\ub2e4. \uc989, \uc774\ub7ec\ud55c SQL\ub4e4 \uc911\uc5d0\ub294 \uc2e4\uc81c\ub85c \uc694\uad6c\ub418\ub294 \uc791\uc5c5\ub7c9\uc774 \ub9ce\uc544\uc11c \uc561\uc138\uc2a4 \ube14\ub85d\uc218\uac00 \ub9ce\uc740 SQL\ub3c4 \uc788\uc744 \uac83\uc774\uba70 \ubd88\ud544\uc694\ud55c \ucc98\ub9ac\ub97c \uc218\ud589\ud558\ub290\ub77c \uc561\uc138\uc2a4 \ube14\ub85d\uc218\uac00 \ub9ce\uc740 SQL\ub3c4 \uc788\uc744 \uac83\uc774\ub2e4. \uc5b4\ub290 \uacbd\uc6b0\uc774\ub4e0 \uc774 SQL\ub4e4\uc774 \ud604\uc7ac \uc624\ub77c\ud074 \uc11c\ubc84\uc5d0 \ubd80\ud558\ub97c \ub9ce\uc774 \uc720\ubc1c\ud558\uace0 \uc788\ub294 SQL\ub4e4\uc774\ub77c\ub294 \uac83\ub9cc\uc740 \ubd84\uba85\ud558\uba70 \uc0ac\uc18c\ud55c \ube44\ud6a8\uc728\uc801 \uc694\uc18c\uc5d0 \uc758\ud574\uc11c\ub3c4 \uc11c\ubc84\uc5d0 \ud070 \uc601\ud5a5\uc744 \ubbf8\uce60 \uc7a0\uc7ac\uc801\uc778 \uac00\ub2a5\uc131\uc774 \uc788\ub294 SQL\ub4e4\uc774\ubbc0\ub85c \uc77c\ucc28\uc801\uc73c\ub85c \uc810\uac80\ud574 \ubcfc \ud544\uc694\uac00 \uc788\ub2e4.<\/p>\n

\uc545\uc131 SQL\uc5ec\ubd80\ub97c \ud310\ub2e8\ud558\uae30 \uc704\ud55c Buffer gets\uc758 \uc218\uce58\uc5d0 \uc808\ub300\uc801\uc778 \uae30\uc900\uc740 \uc5c6\uc73c\uba70 \uc2dc\uc2a4\ud15c\uc758 \ub370\uc774\ud130 \uaddc\ubaa8\uc640 \ud2b8\ub79c\uc7ad\uc158\ub7c9\uc5d0 \ub530\ub77c \ub2e4\ub974\ub2e4. buffer gets\uac12\uc744 \uae30\uc900\uc73c\ub85c \uc5ed\uc21c\uc73c\ub85c \uc815\ub82c\ud55c \ud6c4 \ud328\ud134\uc744 \uc0b4\ud3b4 \uc801\uc808\ud55c \ucd94\ucd9c\uae30\uc900\uc744 \uc120\ud0dd\ud558\ub294 \uac83\uc774 \uc88b\uc744 \uac83\uc774\ub2e4. \ub9cc\uc77c, \uc0c1\uc704 \uba87 \uac1c\uc758 SQL\ub4e4\uacfc \ub098\uba38\uc9c0 SQL\ub4e4 \uac04\uc758 buffer gets\uc758 \ud3b8\ucc28\uac00 \ub9e4\uc6b0 \ud06c\uac8c \ub098\ud0c0\ub09c\ub2e4\uba74 \uc0c1\uc704 \uba87 \uac1c\uc758 SQL\uc5d0 \ub300\ud574\uc11c\ub9cc \ud29c\ub2dd\uc744 \uc218\ud589\ud574 \uc8fc\uc5b4\ub3c4 \ud070 \ud6a8\uacfc\ub97c \ubcfc \uc218 \uc788\uc744 \uac83\uc774\ub2e4. \uc77c\ubc18\uc801\uc73c\ub85c \uc2dc\uc2a4\ud15c\uc5d0\uc11c \uc218\ud589\ub418\ub294 SQL \uac00\uc6b4\ub370 \uc2ec\uac01\ud55c \ubd80\ud558\ub97c \uc57c\uae30\ud558\ub294 SQL\uc740 \uc18c\uc218\uc5d0 \ubd88\uacfc\ud55c \uacbd\uc6b0\uac00 \ub9ce\uc73c\uba70 \ub4a4\uc5d0 \uae30\uc220\ub420 \ub2e4\ub978 \uc870\uac74\ub4e4\uacfc \uc870\ud569\ud558\uc5ec \ucd5c\ub300 Top 50\uac74 \uc815\ub3c4\ub97c \ucd94\ucd9c\ud558\uc5ec \ud6a8\uc728\uc131\uc744 \uac80\uc99d\ud558\uace0 \ud29c\ub2dd\uc744 \ud1b5\ud574 \uac1c\uc120\ud558\ub294 \uc791\uc5c5\uc744 \uc218\ud589\ud558\uc5ec \uc8fc\uba74 \ucda9\ubd84\ud558\ub2e4.<\/p>\n

\u25b7Buffer gets\/Execution \uc218\uce58\uac00 \ub192\uc740 SQL<\/p>\n

SQL\uc758 \ub2e8\uc704 \uc218\ud589\ub2f9 buffer gets \uc218\uce58\ub97c \uc758\ubbf8\ud55c\ub2e4. \ub2e8\uc704 \uc218\ud589\ub2f9 buffer gets \uac12\uc774 \ub192\ub2e4\ub294 \uac83\uc740 \ud574\ub2f9 SQL\uc758 \ucc98\ub9ac\uac00 \ube44\ud6a8\uc728\uc801\uc77c \uac00\ub2a5\uc131\uc774 \ub192\uc74c\uc744 \uc758\ubbf8\ud55c\ub2e4. \uc561\uc138\uc2a4 \ube14\ub85d\uc218\uac00 \ube44\uc815\uc0c1\uc801\uc73c\ub85c \ub9ce\ub2e4\ub294 \uac83\uc740 rows_processed \uac12\uacfc \ube44\uad50\ud558\uc5ec \uc0c1\ub300\uc801\uc73c\ub85c \ud3c9\uac00\ub418\uc5b4\uc57c \ud560 \ubd80\ubd84\uc774\ub2e4. \uc2e4\uc81c\ub85c \ubc18\ud658\ud558\ub294 \ub85c\uc6b0\uc218\uac00 \ub9e4\uc6b0 \ub9ce\uc740 \ubc30\uce58\uc131 SQL\uc774\uac70\ub098 \ud639\uc740 \ubc18\ud658\ub418\ub294 \ub85c\uc6b0\uc218\uac00 1\uac74\uc774\ub77c\ub3c4 Group Function\uc774 \uc0ac\uc6a9\ub41c Summary\uc131 SQL\uc774\ub77c\uba74 \ucc98\ub9ac\uacfc\uc815\uc5d0\uc11c \ub9ce\uc740 \uc218\uc758 \ube14\ub85d\uc744 \uc561\uc138\uc2a4\ud558\ub294 \uac83\uc740 \ubd88\uac00\ud53c\ud558\uba70 \uc774 \uc790\uccb4\uac00 \ubb38\uc81c\uac00 \ub420 \uc218\ub294 \uc5c6\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n

\u25b7Execution \uc218\uce58\uac00 \ub192\uc740 SQL<\/p>\n

Executions\ub294 \ud574\ub2f9 SQL\uc774 \uc218\ud589\ub41c \ud69f\uc218\ub97c \uc758\ubbf8\ud55c\ub2e4. \uc218\ud589\ud69f\uc218\uac00 \uc7a6\uc740 SQL\uc740 buffer gets\uac00 \ub192\uc744 \uacbd\uc6b0\uac00 \ub9ce\ub2e4. \uc77c\ubc18\uc801\uc73c\ub85c \uc2ed\ub9cc ~ \ubc31\ub9cc \ud68c \uc774\uc0c1 \ube48\ubc88\ud558\uac8c \uc218\ud589\ub418\ub294 SQL\uc774\ub77c\uba74 buffer gets\/executions \uac12\uc774 2\uc790\ub9ac \uc218 \uc774\ub0b4\uc758 \uac12\uc744 \ub098\ud0c0\ub0b4\uc5b4\uc57c \uc815\uc0c1\uc774\uba70 \ub2e8\uc704 \uc218\ud589\ub2f9 \uc18d\ub3c4\ub294 0.1\ucd08 \uc774\ub0b4\ub85c \ub9e4\uc6b0 \ube68\ub77c\uc57c \ud55c\ub2e4. \ub530\ub77c\uc11c, \uc774\ub7ec\ud55c SQL\uc758 \uacbd\uc6b0 SQL\ub2e8\uc704\ub85c \ubcf4\uba74 \ud29c\ub2dd\uc758 \ud6a8\uacfc\ub97c \uccb4\uac10\ud558\uae30\ub3c4 \uc5b4\ub835\uace0 \ud544\uc694\uc131\uc744 \ub290\ub07c\uc9c0 \ubabb\ud560 \uc218\ub3c4 \uc788\uc73c\ub098 \ud29c\ub2dd\uc744 \ud1b5\ud574 \uc544\uc8fc \uc801\uc740 \ucc28\uc774\ub77c\ub3c4 \uac1c\uc120\uc744 \uac00\uc838\uc62c \uc218 \uc788\ub2e4\uba74 \uc2dc\uc2a4\ud15c \uc804\uccb4\uc801\uc778 \uad00\uc810\uc5d0\uc11c\ub294 \ub9e4\uc6b0 \ud070 \ud6a8\uacfc\ub97c \uac00\uc838\ub2e4 \uc904 \uc218 \uc788\ub2e4\ub294 \uc810\uc774 \uc911\uc694\ud558\ub2e4. \ud558\ub8e8\uc5d0 \ubc31\ub9cc\ubc88 \uc218\ud589\ub418\ub294 SQL\uc5d0 \ub300\ud558\uc5ec 0.01\ucd08\ub97c \uac1c\uc120\ud55c\ub2e4\uba74 \uc2dc\uc2a4\ud15c \uc2dc\uac04\uc73c\ub85c \ud558\ub8e8\uc5d0 \uc77c\ub9cc\ucd08\ub97c \uc808\uc57d\ud55c \uc148\uc774 \ub420 \uac83\uc774\ub2e4. \uc774\ub7ec\ud55c SQL\uc5d0 \ub300\ud574\uc11c\ub294 \ud604\uc7ac \ube60\ub974\uac8c \uc218\ud589\ub418\uace0 \uc788\ub2e4\uace0 \ud574\ub3c4 \ub354 \ube60\ub974\uac8c \ucc98\ub9ac\ud560 \uc5ec\uc9c0\uac00 \uc5c6\ub294\uc9c0 \uc810\uac80\ud558\uace0 \uac00\ub2a5\ud55c \ubaa8\ub4e0 \ubc29\uc548\uc744 \ub3d9\uc6d0\ud558\uc5ec \uac1c\uc120\uc2dc\ud0a4\ub3c4\ub85d \ub178\ub825\ud574\uc57c \ud55c\ub2e4.<\/p>\n

\u25b7disk_reads \uc218\uce58\uac00 \ub192\uc740 SQL<\/p>\n

disk_reads\ub294 SQL\uc774 \ucc98\ub9ac\ub418\ub294 \uacfc\uc815\uc5d0\uc11c \ubb3c\ub9ac\uc801\uc778 IO\ub97c \ud1b5\ud574 \uc561\uc138\uc2a4\ud55c \ube14\ub85d\uc758 \uc218\ub97c \uc758\ubbf8\ud55c\ub2e4. \ubb3c\ub9ac\uc801 IO\uc758 \ubc1c\uc0dd\uc5ec\ubd80\ub294 \uc6d0\ud558\ub294 \ube14\ub85d\uc774 \uba54\ubaa8\ub9ac\uc5d0 \uce90\uc26c\ub418\uc5b4 \uc788\ub294\uc9c0 \uc5ec\ubd80\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c0\ubbc0\ub85c \uc218\ud589\ub418\ub294 \ud69f\uc218\uc640 \uc218\ud589\ub418\ub294 \uc2dc\uac04\ub300\uc758 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uce90\uc26c \uc0c1\ud669\uc5d0 \ub530\ub77c \uc720\ub3d9\uc801\uc774\ub77c\uace0 \ud560 \uc218 \uc788\ub2e4. \uadf8\ub7ec\ub098, buffer gets\uc758 \uac12\uacfc \ube44\uad50\ud558\uc5ec disk_reads\uc758 \ube44\uc728\uc774 \ub192\uc740 SQL\uc740 Full Scan\uc744 \uc218\ud589\ud558\ub294 SQL\uc77c \uac00\ub2a5\uc131\uc774 \ud070\ub370 \uadf8 \uc774\uc720\ub294 Full Scan\uc744 \ud1b5\ud574 \uc561\uc138\uc2a4\ub418\ub294 \ube14\ub85d\ub4e4\uc740 \uae30\ubcf8\uc801\uc73c\ub85c DB buffer Cache\uc758 LRU \uc54c\uace0\ub9ac\uc998\uc5d0 \uc758\ud574 \uad00\ub9ac\ub418\uc9c0 \uc54a\uc73c\ubbc0\ub85c \uc791\uc5c5 \ud6c4\uc5d0 \uace7\ubc14\ub85c \uba54\ubaa8\ub9ac\ub85c\ubd80\ud130 \ubc00\ub824\ub098 \ubc84\ub9b4 \uac00\ub2a5\uc131\uc774 \ub192\uae30 \ub54c\ubb38\uc774\ub2e4. \ubc18\uba74\uc5d0 \uc778\ub371\uc2a4\ub97c \ud1b5\ud558\uc5ec \uc561\uc138\uc2a4\ud558\ub294 \uacbd\uc6b0, \uc77c\uc0c1\uc801\uc73c\ub85c \uc561\uc138\uc2a4\ub418\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud574\uc11c\ub294 \uc778\ub371\uc2a4\uc758 root block\uacfc branch block\uc740 \ud56d\uc0c1 \uba54\ubaa8\ub9ac\uc5d0 \uce90\uc26c \ub418\uc5b4 \uc788\uc744 \ud655\ub960\uc774 \ub192\uc73c\ubbc0\ub85c \ubb3c\ub9ac\uc801 IO\ub97c \uc720\ubc1c\ud558\ub294 \ube44\uc728\uc774 \ub0ae\uc744 \uc218 \ubc16\uc5d0 \uc5c6\ub2e4.<\/p>\n

\u25a0Top SQL \ucd94\ucd9c\uae30\uc900<\/p>\n

\uae00\uc744 \ub9fa\uae30 \uc804\uc5d0 \ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc5b8\uae09\ud558\uace0 \uc2f6\uc740 \uac83 \ud558\ub098\ub294, \ubb38\uc81c\uac00 \ubc1c\uc0dd\ud588\uc744 \ub54c \ubb38\uc81c\uc758 \uc6d0\uc778\uc774 bug\ub85c \uc778\ud55c \uac83\uc77c \uac00\ub2a5\uc131\uc744 \ud56d\uc0c1 \uc5fc\ub450\uc5d0 \ub450\uc5b4\uc57c \ud5db\ub41c \uace0\uc0dd\uc744 \ub35c\ud55c\ub2e4\ub294 \uac83\uc774\ub2e4. \uc624\ub77c\ud074\ub3c4 \uc0ac\ub78c\uc774 \ub9cc\ub4e0 \ud504\ub85c\uadf8\ub7a8\uc774\ubbc0\ub85c \ubc84\uadf8\uac00 \uc5c6\uc744 \uc218 \uc5c6\uc73c\ub098 \ub2e4\ud589\ud788 \uc624\ub77c\ud074\uc758 \ubc84\uadf8 \ubc0f \ud328\uce58 \uad00\ub9ac\ub294 \ub9e4\uc6b0 \ud6cc\ub96d\ud55c \ud3b8\uc774\ub2e4. \uc624\ub77c\ud074 \uba54\ud0c0\ub9c1\ud06c\ub97c \ud65c\uc6a9\ud558\uc5ec \uc720\uc0ac\ud55c \ubb38\uc81c\uac00 \ubcf4\uace0\ub41c \uc801\uc740 \uc5c6\ub294\uc9c0 \uad00\ub828 \ubc84\uadf8\uc5d0 \ub300\ud55c \uc815\ubcf4\ub294 \uc5c6\ub294\uc9c0 \uc0b4\ud3b4\ubcf4\uc544\uc57c \ud558\uba70, \ud3c9\uc18c\uc5d0 \uc815\uae30\uc801\uc73c\ub85c \ud328\uce58 \ubc0f \ubc84\uc804 \uad00\ub9ac\ub97c \ud574\uc8fc\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4.<\/p>\n

\uc624\ub77c\ud074 \uc2dc\uc2a4\ud15c\uc744 \uc6b4\uc601\ud558\ub2e4 \ubcf4\uba74 \ud604\uc2e4\uc5d0\uc11c\ub294 \ub2e4\uc591\ud55c \ubb38\uc81c\uac00 \ubcf5\uc7a1\ud558\uac8c \uc5bd\ud600 \ub098\ud0c0\ub098\ubbc0\ub85c \uba85\ubc31\ud55c \uc6d0\uc778\uc744 \ud30c\uc545\ud558\uae30\uac00 \uc27d\uc9c0 \uc54a\uc744 \ub54c\uac00 \ub9ce\uc9c0\ub9cc, \uc6b4\uc601 \uc2dc \uc790\uc2e0\uc758 \uc2dc\uc2a4\ud15c\uc5d0\uc11c \uc790\uc8fc \ubc1c\uc0dd\ud558\ub294 Wait \ud328\ud134 \ub610\ud55c \ubd84\uba85\ud788 \uc874\uc7ac\ud558\ubbc0\ub85c \uad75\uc740 \uac00\uc9c0\ub4e4\ubd80\ud130 \ud558\ub098\uc529 \uc774\ud574\ud558\uace0 \ud574\uacb0\ud574 \ub098\uac00\ub2e4 \ubcf4\uba74 \uc624\ub77c\ud074\uc774 \uc6b0\ub9ac\uac00 \ubcf4\uc778 \uc560\uc815\uc5d0 \ubcf4\ub2f5\ud574\uc904 \ub0a0\uc774 \uc62c \uac83\uc774\ub77c \ubbff\ub294\ub2e4. \uc9c0\uba74 \uad00\uacc4\uc0c1 OPS(RAC) \uad00\ub828 Wait\uc744 \ube44\ub86f\ud55c \uae30\ud0c0 Wait \uc774\ubca4\ud2b8\uc5d0 \ub300\ud574 \ub2e4\ub8e8\uc9c0 \ubabb\ud55c \uc810, \uadf8\ub9ac\uace0 \uac01 Wait \uc774\ubca4\ud2b8\ubcc4\ub85c \uc880\ub354 \uce5c\uc808\ud55c \uc124\uba85\uacfc \uc0ac\ub840\ub97c \uc81c\uc2dc\ud558\uc9c0 \ubabb\ud55c \uc810\uc774 \uc544\uc26c\uc6c0\uc73c\ub85c \ub0a8\uc9c0\ub9cc, \ub204\uad6c\ub4e0 \uc544\uc8fc \uc791\uc740 \uac83 \ud558\ub098\ub77c\ub3c4 \uc774 \uae00\uc744 \ud1b5\ud574 \uc0c8\ub85c\uc774 \uc5bb\uc744 \uc218 \uc788\uc5c8\ub2e4\uba74 \uadf8 \uc774\uc0c1 \ubc14\ub784 \uac83\uc740 \uc5c6\ub2e4.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Oracle Wait Event \ubaa8\ub2c8\ud130\ub9c1 \uc21c\uc11c [1] Oracle Wait Event \ubaa8\ub2c8\ud130\ub9c1 [2] Enqueue\uc640 Latch [3] Shared Pool \uad00\ub828 Wait Event [4] buffer cache \uad00\ub828 Wait Event [5] redo log \uad00\ub828 Wait Event [6] Top SQL \ud29c\ub2dd [1] Oracle Wait Event \ubaa8\ub2c8\ud130\ub9c1 \ud754\ud788 DBA\ub97c 3D\uc5c5\uc885\uc774\ub77c\uace0 \ubd80\ub974\ub294 \uc774\uc720 \uac00\uc6b4\ub370 \ud558\ub098\ub294 \ubab8\uc740 \uace0\ub2ec\ud508\ub370 \ubc18\ud574 \uadf8 \uc131\uacfc\uac00 \ubcc4\ub85c \ud2f0\uac00 \ub098\uc9c0 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1580"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1580"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1580\/revisions"}],"predecessor-version":[{"id":1582,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1580\/revisions\/1582"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1580"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}