\ub2e4\uc74c\uc5d0 \uc5f4\uac70\ub41c \ud30c\ub9ac\ubbf8\ud130\ub294 \uac01\uac01 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ud29c\ub2dd\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce58\ub294 \uac83\ub4e4\uc774\ub2e4.<\/p>\n
DB_BLOCK_BUFFERS
SHARED_POOL_SIZE
SORT_AREA_SIZE
DBWR_IO_SLAVES
ROLLBACK_SEGMENTS
SORT_AREA_RETAINED_SIZE
DB_BLOCK_LRU_EXTENDED_STATISTICS
SHARED_POOL_RESERVE_SIZE<\/p>\n
1. DB_BLOCK_BUFFERS
\uc774 \ud30c\ub77c\ubbf8\ud130\ub294 \ubaa8\ub4e0 \ubc84\uc83c\uc758 \uc624\ub77c\ud074\uc5d0\uc11c \uc0ac\uc6a9\ub418\uba70, Oracle block \ud06c\uae30\ub97c \ub2e8\uc704\ub85c \uc9c0\uc815\ud558\uac8c \ub41c\ub2e4. \uc774 \uac12\uc740 \uc0ac\uc6a9\uc790\uac00 \uc694\uccad\ud558\ub294 \ub370\uc774\ud130\ub97c, \uba54\ubaa8\ub9ac \uc601\uc5ed\uc5d0 \uc800\uc7a5\ud574 \ub458 \uc218 \uc788\ub294 \uacf5\uac04\uc758 \ud06c\uae30\ub97c \uc9c0\uc815\ud558\ubbc0\ub85c \ud29c\ub2dd\uc2dc \ub9e4\uc6b0 \uc911\uc694 \ud55c \uc5ed\ud560\uc744 \ud55c\ub2e4. db_block_buffers \uac12\uc740 SGA \uce90\uc26c \uc601\uc5ed\uc5d0 \uc874\uc7ac\ud558\ub294 \ubc84\ud37c\uc758 \uac2f\uc218\ub97c \uc9c0\uc815 \ud558\ub294\ub370 \uc0ac\uc6a9\ub418\uba70, \uc801\uc808\ud55c \uce90\uc26c \ud06c\uae30\ub294 \uc2e4\uc81c \ub514\uc2a4\ud06c I\/O\ub97c \uc904\uc774\ub294\ub370 \ub3c4\uc6c0 \uc774 \ub41c\ub2e4. \uce90\uc26c \uc601\uc5ed\uc774 \uc801\uc808\ud558\uac8c \uc9c0\uc815\ub418\uc5b4 \uc788\ub294\uc9c0 \uc5ec\ubd80\ub294 buffer cache
hit ratio\ub85c \uce21\uc815 \uac00\ub2a5\ud558\uba70, \uc77c\ubc18\uc801\uc73c\ub85c 90% \uc774\uc0c1\uc758 \uac12\uc744 \uc720\uc9c0\ud558\ub3c4\ub85d \ud558\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4. buffer cache hit ratio\ub294 \ub2e4\uc74c SQL\uc744 \uc0ac\uc6a9\ud558\uc5ec \uc870\ud68c \uac00\ub2a5\ud558\ub2e4.<\/p>\n
select round(((1-(sum(decode(name,
‘physical reads’, value,0))\/
(sum(decode(name, ‘db block gets’, value,0))+
(sum(decode(name, ‘consistent gets’, value, 0))))))*100),2)
|| ‘%’ “Buffer Cache Hit Ratio”
from v$sysstat;<\/p>\n
\uc2e4\ud589 \uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \ud615\uc2dd\uc73c\ub85c \ub098\ud0c0\ub098\uac8c \ub41c\ub2e4.<\/p>\n
Buffer Cache Hit Ratio: 97.63%<\/p>\n
\ub9cc\uc57d hit ratio\uac00 90% \ubbf8\ub9cc\uc774\ub77c\uba74, hit ratio \uac00 90% \uc774\uc0c1\uc744 \uc720\uc9c0\ud560 \uc815\ub3c4\ub85c buffer cache\uc758 \ud06c\uae30\ub97c \ub298\ub824\uc8fc\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4. \uc774 \uac12\uc774 \uc791\uc744 \uacbd\uc6b0 \uc0ac\uc6a9\ub41c \ub370\uc774\ud130\uac00, \ub2e4\ub978 \ub370\uc774\ud130\ub97c \ucc98\ub9ac\ud560 \uba54\ubaa8\ub9ac \uc601\uc5ed\uc744 \ud655\ubcf4\uc2dc\ud0a4\uae30 \uc704\ud574 \uba54\ubaa8\ub9ac\uc5d0\uc11c \uc0ad\uc81c\ub41c \ud6c4, \ub2e4\uc2dc\ud574\ub2f9 \ub370\uc774\ud130\uac00 \uc694\uccad\ub420 \uacbd\uc6b0 \ucda9\ubd84\ud55c cache\ub97c \ud655\ubcf4\ud558\uc600\uc744 \ub54c \ud53c\ud560 \uc218 \uc788\ub294 \ubb3c\ub9ac I\/O \uac00 \ubc1c\uc0dd\ud558\uac8c \ub41c\ub2e4.
\uadf8\ub7ec\ub098 \ub9cc\uc57d \uc774 \uac12\uc744 \uac00\uc6a9\ud55c \uba54\ubaa8\ub9ac \ud06c\uae30\uc5d0 \ube44\ud574 \ub108\ubb34 \ud06c\uac8c \uc9c0\uc815\ud560 \uacbd\uc6b0\uc5d0\ub294 OS \uc5d0\uc11c swapping\uc774 \ubc1c\uc0dd\ud558\uac8c \ub418\uc5b4 \uc2dc\uc2a4\ud15c\uc774 hang \uc0c1\ud0dc\uae4c\uc9c0 \uac08 \uc218 \uc788\ub2e4.<\/p>\n
<\/p>\n
2. SHARED_POOL_SIZE
shared_pool_size\ub294 \ubaa8\ub4e0 \ubc84\uc83c\uc758 \uc624\ub77c\ud074\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 \ud30c\ub77c\ubbf8\ud130\ub85c, \ub2e8\uc704\ub294 byte \ub2e8\uc704\uc774\ub2e4. \uc774 \uc601\uc5ed\uc740 data dictionary\ub098, stored procedure, \uadf8\ub9ac\uace0 \uac01\uc885 SQL statement\uac00 \uc800\uc7a5\ub41c\ub2e4. SGA \uc601\uc5ed\uac00\uc6b4\ub370 \ub9ce\uc740 \ube44\uc911\uc744 \ucc28\uc9c0\ud558\ub294
shared_pool_size\ub294 \ub2e4\uc2dc dictionary cache \ubc0f library cache \uc601\uc5ed\uc73c\ub85c \ub098\ub258\uc5b4 \uc9c0\uba70, db_block_buffers\uc640 \ub9c8\ucc2c\uac00\uc9c0\ub85c \ub108\ubb34 \ud06c\uac70\ub098, \uc791\uac8c \uc7a1\uc9c0 \uc54a\ub3c4\ub85d \ud558\uc5ec\uc57c \ud55c\ub2e4. shared_pool_size \uac12\uc774 \uc801\uc808\ud55c\uc9c0 \uc5ec\ubd80\ub294 data dictionary cache \ubc0f library
cache \uc758 hitratio\ub85c \uce21\uc815\ud560 \uc218 \uc788\ub2e4. SQL \ucc98\ub9ac\uc5d0\ub294 data dictionary\uac00 \uc5ec\ub7ec\ucc28\ub840 \ucc38\uc870\ub418\ubbc0\ub85c, data dictionary
\uc870\ud68c\uc2dc \ub514\uc2a4\ud06c I\/O\uac00 \uc801\uac8c \ubc1c\uc0dd\ud558\ub3c4\ub85d \ud558\uba74, \uc131\ub2a5 \ud5a5\uc0c1\uc5d0 \ub3c4\uc6c0\uc774 \ub41c\ub2e4.<\/p>\n
Data dictionary cache hit ratio\ub294 \ub2e4\uc74c SQL\uc5d0 \uc758\ud574 \uce21\uc815 \uac00\ub2a5\ud558\ub2e4.<\/p>\n
select (1-(sum(getmisses)\/sum(gets))) * 100 “Hit Ratio” from v$rowcache;<\/p>\n
\uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uc774 \uc0dd\uc131\ub41c\ub2e4.
Hit Ratio : 95.40%<\/p>\n
Data dictionary cache hit ratio\ub294 90% \uc774\uc0c1\uc744 \uc720\uc9c0\ud558\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1 \ud558\uc9c0\ub9cc, \uc778\uc2a4\ud134\uc2a4 \uad6c\ub3d9 \uc9c1\ud6c4\uc5d0\ub294 \uce90\uc26c\uc601\uc5ed\uc5d0 \ub370\uc774\ud130\uac00 \uc800\uc7a5\ub418\uc9c0 \uc54a\uc73c\ubbc0\ub85c \ub300\ub7b5 85% \uac00\ub7c9\uc744 \uc720\uc9c0 \ud558\ub3c4\ub85d \ud558\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4. Library cahce \uc601\uc5ed\uc740 \uacf5\uc720 SQL \uc601\uc5ed \ubc0f PL\/SQL \uc601\uc5ed\uc73c\ub85c \ub098\ub258\uc5b4 \uc9c4\ub2e4. SQL\uc774 \uc2e4\ud589\ub420 \uacbd\uc6b0, \ubb38\uc7a5\uc740 \uba3c\uc800 parsing \ub418\uc5b4\uc57c \ud558\ub294\ub370, library cache\ub294
SQL \ubc0f PL\/SQL\uc744 \ubbf8\ub9ac \uc800\uc7a5\ud574 \ub450\uc5b4, \uc2e4\uc81c parsing\uc774 \ubc1c\uc0dd\ud558\ub294 \ube48\ub3c4\ub97c \uc904\uc774\ub294 \uc5ed\ud560\uc744 \ud55c\ub2e4. OLTP \uc5c5\ubb34\uc758 \uacbd\uc6b0, \ub3d9\uc77c\ud55c SQL\uc774 \uc5ec\ub7ec\ucc28\ub840 \uc218\ud589\ub418\ubbc0\ub85c \uc801\uc808\ud55c cache \uc601\uc5ed\uc744 \ud655\ubcf4\ud568\uc73c\ub85c\uc368 \uc131\ub2a5 \ud5a5\uc0c1\uc744 \uae30\ub300\ud560 \uc218 \uc788\ub2e4. \u2013 \ubb3c\ub860 bind variable\uc744 \uc0ac\uc6a9\ud558\uc5ec\uc57c\ub9cc \uacf5\uc720\uac00\ub2a5\ud55c SQL\uc774 \uc0dd\uc131\ub41c\ub2e4. shared_pool_size \uac12\uc774 \uc801\uc744 \uacbd\uc6b0\ub294 \ubb3c\ub860\uc774\uac70\ub2c8\uc640, \ub108\ubb34 \uc774 \uac12\uc744 \ud06c\uac8c
\uc9c0\uc815\ud574\ub3c4 \ubb38\uc81c\uac00 \ub41c\ub2e4. shared_pool_size\uac00 \ub108\ubb34 \ud074 \uacbd\uc6b0, \uc0c8\ub85c\uc6b4 SQL \uc218\ud589\uc2dc \uac00\uc6a9\ud55c \uba54\ubaa8\ub9ac \uc601\uc5ed\uc744 \ucc3e\uc544 \ub0b4\uae30 \uc704\ud55c latch contention \uc758 \uac00\ub2a5\uc131\uc774 \ub192\uc544\uc9c0\uac8c \ub41c\ub2e4.
v$sgastat\uc744 \uc870\ud68c\ud558\uc5ec free memory\ub97c \uc870\uc0ac\ud560 \uc218 \uc788\uc73c\uba70, \uba54\ubaa8\ub9ac\uac00 \ub0ad\ube44\ub418\uace0 \uc788\ub294\uc9c0 \uc5ec\ubd80\ub3c4 \ud655\uc778 \uac00\ub2a5\ud558\ub2e4.<\/p>\n
select name, bytes\/1024\/1024 “Size in MB” from v$sgastat where name=’free memory’ ;<\/p>\n
\uc2e4\ud589 \uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.
NAME Size in MB
Free memory 39.6002884<\/p>\n
\uc774 \uacb0\uacfc\ub294 shared pool\uc5d0 39M \uacf5\uac04\uc774 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uace0 \uc788\uc73c\uba70, \ub9cc\uc57d shared pool\uc758 \ud06c\uae30\ub97c 70M \ub85c \uc9c0\uc815\ud558\uc600\ub2e4\uba74, \uc808\ubc18 \uc774\uc0c1\uc758 \uba54\ubaa8\ub9ac \uacf5\uac04\uc774 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uace0 \ub0ad\ube44\ub418\uace0 \uc788\uc74c\uc744 \uc758\ubbf8\ud55c\ub2e4.<\/p>\n
3. SORT_AREA_SIZE
SORT_AREA_SIZE\uc5d0 \ub300\ud574\uc11c\ub294 \ud754\ud788 \uc798\ubabb\ub41c \uc774\ud574\ub97c \ud558\uac8c\ub41c\ub2e4. \ub300\ubd80\ubd84\uc758 \uc0ac\uc6a9\uc790\ub4e4\uc740 \uc774 \uac12\uc774 \ubaa8\ub4e0 \uc0ac\uc6a9\uc790\ub4e4\uc774 sort \uc791\uc5c5\uc5d0 \uc0ac\uc6a9\ud558\uac8c \ub418\ub294 \uacf5\uc6a9 \uba54\ubaa8\ub9ac \uc601\uc5ed\uc758 \ud06c\uae30\ub85c \uc774\ud574\ub97c \ud558\ub294\ub370, \uc2e4\uc81c\ub85c\ub294 \uc0ac\uc6a9\uc790 \ud504\ub85c\uc138\uc2a4 \ubcc4\ub85c \uc0ac\uc6a9\ud558\uac8c \ub418\ub294 sort \uc601\uc5ed\uc758 \ud06c\uae30\ub97c \ub098\ud0c0\ub0b8\ub2e4. \uc55e\uc5d0\uc11c \uc0b4\ud3b4\ubcf8 \ub450\uac1c\uc758 \ud30c\ub77c\ubbf8\ud130\uc640 \ub2ec\ub9ac, SORT_AREA_SIZE\ub294 SGA\uc601\uc5ed\uc5d0 \uc18d\ud558\uc9c0 \uc54a\ub294\ub2e4.
\ub9cc\uc57d sort_area_size \uac12\uc774 \ub108\ubb34 \uc791\ub2e4\uba74, sort \uc791\uc5c5 \ub300\ubd80\ubd84\uc774 \uc0ac\uc6a9\uc790\uc758 temporary tablespace\uc5d0\uc11c \ub514\uc2a4\ud06c\ub97c \uc0ac\uc6a9\ud558\uc5ec \uc774\ub8e8\uc5b4 \uc9c0\uac8c \ub41c\ub2e4.
SQL \ucc98\ub9ac\uc2dc order by \ub098, group by \ub4f1\uc744 \uc0ac\uc6a9\ud560 \uacbd\uc6b0\uc5d0\ub294 sort \uc791\uc5c5\uc774 \ubc1c\uc0dd\ud558\ub098. index \uc0dd\uc131\ub4f1\uc5d0\ub3c4 sort\uac00 \ubc1c\uc0dd\ud55c\ub2e4.
\uba54\ubaa8\ub9ac sort\ub294 \ub514\uc2a4\ud06c sort\uc5d0 \ube44\ud574 \ud6e8\uc52c \uc88b\uc740 \uc131\ub2a5\uc744 \ubcf4\uc774\ubbc0\ub85c, \uc9c0\uc18d\uc801\uc73c\ub85c SORT_AREA_SIZE \uac12\uc744 \ubaa8\ub2c8\ud130\ud558\uc5ec \ud29c\ub2dd\uc744 \ud558\ub294\uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4. \ud558\uc9c0\ub9cc, \uc774 \uac12\uc744 \ub108\ubb34 \ud06c\uac8c \uc9c0\uc815\ud560 \uacbd\uc6b0, swapping\uc774 \ubc1c\uc0dd\ud558\uba74\uc11c \uc2dc\uc2a4\ud15c \uc131\ub2a5\uc774 \uae09\uaca9\ud558\uac8c \uc800\ud558\ub420 \uc218 \uc788\ub2e4.
* SORT_AREA_SIZE\ub294 \uc138\uc158\ubcc4\ub85c\ub3c4 \uc9c0\uc815\uac00\ub2a5\ud558\uba70, \uc9c0\uc815\ud558\uae30 \uc704\ud574\uc11c\ub294 ALTER SESSION \uad8c\ud55c\uc774 \uc788\uc5b4\uc57c \ud55c\ub2e4. \ud2b9\uc815 \uc138\uc158\uc5d0\uc11c \uc2dc\uc2a4\ud15c\uc0c1\uc758 \ubaa8\ub4e0 \uba54\ubaa8\ub9ac\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud560 \uacbd\uc6b0 \uc2dc\uc2a4\ud15c \uc131\ub2a5\uc774 \uae09\uaca9\ud788 \uc800\ud558 \ub420 \uc218\ub3c4 \uc788\ub2e4.<\/p>\n
4. DBWR_IO_SLAVES
DBWR_IO_SLAVES\ub294 SORT_AREA_SIZE\uc640 \ub9c8\ucc2c\uac00\uc9c0\ub85c \uc0ac\uc6a9\uc790\ub4e4\uc774 \ud754\ud788 \uc798\ubabb \uc774\ud574\ud558\ub294 \ud30c\ub77c\ubbf8\ud130\ub85c, Oracle 8 \uc774\ud6c4 \ubc84\uc83c\uc5d0\uc11c \uc0ac\uc6a9\ub41c\ub2e4. \uc774 \ud30c\ub77c\ubbf8\ud130\ub294 Oracle 8 \uc774\uc804\uc5d0 \uc0ac\uc6a9\ub418\ub358 DB_WRITERS \ud30c\ub77c\ubbf8\ud130\ub97c \ub300\uccb4\ud55c\ub2e4. Oracle 8\uc5d0\uc11c\ub294 DB_WRITER_PROCESSES \ub77c\ub294 \ud30c\ub77c\ubbf8\ud130\uac00 DB_WRITERS\ub97c \ub300\uccb4\ud558\uc9c0\ub9cc, DBWR_IO_SLAVES \ud30c\ub77c\ubbf8\ud130\uc640 \ud568\uaed8 \uc0ac\uc6a9\ud560
\uacbd\uc6b0 \uc544\uc9c1\uae4c\uc9c0\ub3c4 \ubb38\uc81c\uc810\ub4e4\uc774 \ubc1c\uc0dd\ud55c\ub2e4. DBWR_IO_SLAVES\ub294 slave writer process\uac00 – OS\uc5d0\uc11c \uc9c0\uc6d0\ud560 \uacbd\uc6b0 -asynchronous I\/O\ub97c \uc218\ud589\ud558\ub3c4\ub85d \ud5c8\uc6a9\ud55c\ub2e4.
DB_WRITERS \ubc0f DBWR_IO_SLAVES \uad00\ub828 \uc790\ub8cc\ub294 METALINK\uc5d0 \ub9ce\uc774 \uc62c\ub77c\uc640 \uc788\uc73c\uba70, DB_WRITERS \uc640 DBWR_IO_SLAVES \ub294 \ub3d9\uc2dc\uc5d0 \uc0ac\uc6a9\ud558\uc9c0 \ubabb\ud55c\ub2e4\ub294 \uac83\uc744 \uc774\ud574\ud558\ub294 \uac83\uc774 \uc911\uc694\ud558\ub2e4.<\/p>\n
* <Bulletin : 11699> \ucc38\uc870<\/p>\n
5. ROLLBACK_SEGMENTS
\uc774 \ud30c\ub77c\ubbf8\ud130\ub294 \ubaa8\ub4e0 \ubc84\uc83c\uc758 \uc624\ub77c\ud074\uc5d0\uc11c \uc0ac\uc6a9\ub418\uba70, \uc778\uc2a4\ud134\uc2a4 \uae30\ub3d9\uc911\uc5d0 \uc628\ub77c\uc778 \uc0c1\ud0dc\ub85c \uc0ac\uc6a9\ud560 rollback segment\ub97c \uc9c0\uc815\ud55c\ub2e4. \ub9cc\uc57d \ud30c\ub77c\ubbf8\ud130\uc5d0\uc11c \uc9c0\uc815\ud55c rollback segment\uac00 \uc874\uc7ac\ud558\uc9c0 \uc54a\ub294 \uac83\uc774\ub77c\uba74 ora-1534 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\uba70, \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub294 mount\uae4c\uc9c0\ub9cc \ub418\uace0 open \ub418\uc9c0\ub294 \uc54a\ub294\ub2e4.
Rollback segment\ub294 \ud2b8\ub79c\uc7ad\uc158\uc5d0\uc11c \ubc1c\uc0dd\ud558\ub294 \ubcc0\uacbd\uc0ac\ud56d\uc744 \uae30\ub85d\ud558\uc5ec, \ud2b8\ub79c\uc7ad\uc158\uc774 rollback \ub418\uc5b4\uc57c \ud560 \uacbd\uc6b0 \uc774\uc804 \uc0c1\ud0dc\ub85c \ub3cc\ub9ac\uae30 \uc704\ud55c \uac01\uc885 \uc815\ubcf4\ub97c \uc800\uc7a5\ud558\ub294 \uc601\uc5ed\uc774\ub2e4. – Windows \uc758 undo \uae30\ub2a5\uacfc \uc720\uc0ac\ud568. Rollback segment\ub294 \uc5ec\ub7ec extent\ub4e4\ub85c \uad6c\uc131\ub418\ub294\ub370, extent\ub294 round-robin \ubc29\uc2dd\uc73c\ub85c \uc21c\ud658\ub418\uba70 \uc0ac\uc6a9\ub41c\ub2e4. \uc989, \ud604\uc7ac \uc0ac\uc6a9\ub418\ub294 extent\uac00 full\uc774 \ub098\ub294 \uacbd\uc6b0 \ub2e4\uc74c extent\ub97c \uc0ac\uc6a9\ud558\ub294 \uc2dd\uc73c\ub85c \uc0ac\uc6a9\ub41c\ub2e4. Rollback segment\ub294 read consistency\ub97c \uc81c\uacf5\ud574 \uc8fc\uace0, \ud2b8\ub79c\uc7ad\uc158\uc744 undo
\uc2dc\ud0ac\uc218 \uc788\uace0, recovery\uc5d0 \uc0ac\uc6a9\ub418\ub294 \ub4f1, \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0\uc11c \ub9e4\uc6b0 \uc911\uc694\ud55c \uc5ed\ud560\uc744 \uc218\ud589\ud55c\ub2e4.
Read consistency\ub294 \uc5c5\ubb34\uc801\uc73c\ub85c\ub3c4 \ub9e4\uc6b0 \uc911\uc694\ud55c\ub370, \ud55c \uc0ac\uc6a9\uc790 (1\ubc88 \uc0ac\uc6a9\uc790) \uac00 \ub370\uc774\ud130\ub97c \uc77d\ub294\ub3d9\uc548, \ub2e4\ub978 \uc0ac\uc6a9\uc790\uac00 (2\ubc88 \uc0ac\uc6a9\uc790) \uadf8 \ub370\uc774\ud130\uc5d0 \ubcc0\uacbd\uc744 \uac00\ud55c\ub2e4\uba74, 2\ubc88 \uc0ac\uc6a9\uc790\uac00 \ub370\uc774\ud130 \ubcc0\uacbd\uc744 \uc77c\uad00\uc131 \uc788\uac8c \uc885\ub8cc\ud558\uae30 \uc804 \uac00\uc9c0 1\ubc88 \uc0ac\uc6a9\uc790\ub294 \uc774\uc804 \uc0c1\ud0dc\uc758 \ub370\uc774\ud130, \uc989 \uc774\uc804\uc5d0 commit \ub41c \uc0c1\ud0dc\uc758 \ub370\uc774\ud130\ub97c \uc0ac\uc6a9\ud558\uc5ec\uc57c\ub9cc \ub370\uc774\ud130 \uc77c\uad00\uc131\ubc0f \uc815\ud569\uc131\uc774 \ubcf4\uc7a5\ub41c\ub2e4.
RBS\uc758 \uc801\uc815 \ud06c\uae30\ub294 \ub2e4\ub978 \ubb38\uc81c\uc640 \ub9c8\ucc2c\uac00\uc9c0\ub85c \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ub0b4\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 \uc77c\ubc18\uc801\uc778 \ud2b8\ub79c\uc7ad\uc158 \ub808\ubca8\uc5d0 \ub530\ub77c \ub2e4\ub974\ub2e4. RBS extent\uc758 \ud06c\uae30\uc640 \uad00\ub828\ud574\uc11c\ub294 \uc624\ub77c\ud074\uc5d0\uc11c\ub294 extent size\uc640 \uad00\ub828\ub41c ( initial , next \uac12 ) \uad8c\uace0 \uc0ac\ud56d\uc774 \uc874\uc7ac\ud55c\ub2e4.
Rollback segment\uc758 \uac2f\uc218\uc640 \uad00\ub828\ud574\uc11c\ub294, rollback segment\uac04\uc758 contention \uc774 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\ub3c4\ub85d \uc870\uc815\ud574 \uc8fc\ub294 \uac83\uc774 \uc911\uc694\ud558\ub2e4. \ubaa8\ub4e0 \ud2b8\ub79c\uc7ad\uc158\uc740 RBS\uc758 \ud5e4\ub354\uc5d0 \uc874\uc7ac\ud558\ub294 \ud2b8\ub79c\uc7ad\uc158 \ud14c\uc774\ube14\uc5d0 \uc815\ubcf4\uac00 \uc800\uc7a5\ub41c\ub2e4. \ubaa8\ub4e0 \ud2b8\ub79c\uc7ad\uc158\uc774
\uc774 \ud14c\uc774\ube14\uc758 \ub0b4\uc6a9\uc744 \ubcc0\uacbd\ud558\uc5ec\uc57c \ud558\ubbc0\ub85c, contention\uc774 \ubc1c\uc0dd\ud560 \uc218 \uc788\ub2e4. \ud55c \uc2dc\uc810\uc5d0 \ud55c\uac1c\uc758 \ud2b8\ub79c\uc7ad\uc158\uc774 \ud55c\uac1c\uc758 rollback segment\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud558\ub294 \uac83\uc774 \uc77c\ubc18\uc801\uc778 \uc6d0\uce59\uc774\ub2e4. \uc624\ub77c\ud074\uc5d0\uc11c\ub294 4\uac1c\uc758 \ud2b8\ub79c\uc7ad\uc158\ub2f9 \ud55c\uac1c\uc758 rollback
segment\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83\uc744 \uad8c\uace0\ud558\uc9c0\ub9cc, \uc808\ub300\uc801\uc778 \uae30\uc900\uc774 \uc544\ub2c8\ub77c \uc0c1\ub300\uc801\uc778 \uae30\uc900\uc73c\ub85c \ubcf4\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4.
rollback segment\uac04 contention\uc744 \uc870\uc0ac\ud558\uae30 \uc704\ud574\uc11c\ub294 v$waitstat\uc744 \uc870\ud68c\ud558\uba74 \ub41c\ub2e4. \ub2e4\uc74c query\ub85c rollback segment\uac04 contention\uc744 \uc870\ud68c\ud574 \ubcfc \uc218 \uc788\ub2e4.<\/p>\n
Select a. name, b.extents, b.rssize, b.xacts, b.waits,
b. gets, optsize, status
From v$rollname a, v$rollstat b
Where a.usn = b.usn;<\/p>\n
\uc2e4\ud589\uacb0\uacfc\ub294 \ub300\ub7b5 \ub2e4\uc74c\uacfc \uac19\uc740 \ud615\uc2dd\uc73c\ub85c \ub098\ud0c0\ub09c\ub2e4.<\/p>\n
NAME EXTENTS RSSIZE XACTS WAITS GETS OPTSIZE STATUS
SYSTEM 4 540672 1 0 51 ONLINE
RB1 2 10240000 0 0 427
10240000 ONLINE
RB2 2 10240000 1 0 425
10240000 ONLINE
RB3 2 10240000 1 0 422
10240000 ONLINE
RB4 2 10240000 0 0 421
10240000 ONLINE<\/p>\n
\uc704\uc758 \uc9c8\uc758\ub97c \ucc98\ub9ac\ud55c \uacb0\uacfc\ub85c “xacts” ( \ud2b8\ub79c\uc7ad\uc158\uc758 \uc904\uc784\ub9d0 ) \uac00 \uacc4\uc18d\ud574\uc11c 1 \uc774\uc0c1\uc774 \uacbd\uc6b0, rollback segment\uc758 \uac2f\uc218\ub97c \ub298\ub824\uc8fc\ub294 \uac83\uc774 contention\uc774 \ubc1c\uc0dd\ud560 \uac00\ub2a5\uc131\uc744 \uc904\uc5ec\uc900\ub2e4. \ub9cc\uc57d wait \uac2f\uc218\uac00 0\ubcf4\ub2e4 \ud06c\uace0, \ud2b9\ubcc4\ud55c \uc0ac\ud56d\uc5d0\uc11c\ub9cc \ub098\ud0c0\ub098\ub294 \uac83\uc774 \uc544\ub2c8\ub77c \ud56d\uc0c1 \ube44\uc2b7\ud55c \uc0c1\ud669\uc774\ub77c\uba74, \uc774 \uacbd\uc6b0\uc5d0\ub3c4 rollback segment\uc758 \uac2f\uc218\ub97c \ub298\ub824\uc8fc\ub294 \ud3b8\uc774 \ub0ab\ub2e4.<\/p>\n
* Rollback segment\uc758 \uc801\uc815 \uac2f\uc218 \ub3c4\ucd9c\uad00\ub828 \uc790\ub8cc\ub294 <bulletin: 10802>, <NOTE:10579.1<\/a>> \ucc38\uc870 <\/p>\n 6. SORT_AREA_RETAINED_SIZE 7. DB_BLOCK_LRU_EXTENDED_STATISTICS 8. SHARED_POOL_RESERVED_SIZE Reference Documents \ub2e4\uc74c\uc5d0 \uc5f4\uac70\ub41c \ud30c\ub9ac\ubbf8\ud130\ub294 \uac01\uac01 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ud29c\ub2dd\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce58\ub294 \uac83\ub4e4\uc774\ub2e4. DB_BLOCK_BUFFERSSHARED_POOL_SIZESORT_AREA_SIZEDBWR_IO_SLAVESROLLBACK_SEGMENTSSORT_AREA_RETAINED_SIZEDB_BLOCK_LRU_EXTENDED_STATISTICSSHARED_POOL_RESERVE_SIZE 1. DB_BLOCK_BUFFERS\uc774 \ud30c\ub77c\ubbf8\ud130\ub294 \ubaa8\ub4e0 \ubc84\uc83c\uc758 \uc624\ub77c\ud074\uc5d0\uc11c \uc0ac\uc6a9\ub418\uba70, Oracle block \ud06c\uae30\ub97c \ub2e8\uc704\ub85c \uc9c0\uc815\ud558\uac8c \ub41c\ub2e4. \uc774 \uac12\uc740 \uc0ac\uc6a9\uc790\uac00 \uc694\uccad\ud558\ub294 \ub370\uc774\ud130\ub97c, \uba54\ubaa8\ub9ac \uc601\uc5ed\uc5d0 \uc800\uc7a5\ud574 \ub458 \uc218 \uc788\ub294 \uacf5\uac04\uc758 \ud06c\uae30\ub97c \uc9c0\uc815\ud558\ubbc0\ub85c \ud29c\ub2dd\uc2dc \ub9e4\uc6b0 \uc911\uc694 \ud55c \uc5ed\ud560\uc744 \ud55c\ub2e4. db_block_buffers \uac12\uc740 SGA \uce90\uc26c \uc601\uc5ed\uc5d0 \uc874\uc7ac\ud558\ub294 \ubc84\ud37c\uc758 \uac2f\uc218\ub97c \uc9c0\uc815 \ud558\ub294\ub370 \uc0ac\uc6a9\ub418\uba70, \uc801\uc808\ud55c […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","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\/2645"}],"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=2645"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2645\/revisions"}],"predecessor-version":[{"id":2646,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2645\/revisions\/2646"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2645"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2645"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2645"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
* Rollback segment\uc758 \uc0dd\uc131, \ucd5c\uc801\ud654 \uad00\ub828 \uc790\ub8cc\ub294 <bulletin: 11715,10072>, <NOTE:62005.1<\/a>> \ucc38\uc870<\/p>\n
init.ora \ud30c\uc77c\uc5d0\uc11c \uc9c0\uc815\ud558\ub294 sort \uc791\uc5c5 \uad00\ub828\ub41c \ud30c\ub77c\ubbf8\ud130\ub85c SORT_AREA_RETAINED_SIZE \ub3c4 \uc788\ub2e4. \uc774 \uac12\uc740 sort \uac00 \ub05d\ub09c \ud6c4\uc5d0\ub3c4 \uc720\uc9c0\ud558\uace0\uc790 \ud558\ub294 SORT_AREA_SIZE\ub97c \ub098\ud0c0\ub0b8\ub2e4. \uc774 \ud30c\ub77c\ubbf8\ud130\ub294 SORT_AREA_SIZE \uac12\uacfc \uac19\uac70\ub098 \uc801\uac8c \uc9c0\uc815\ub418\uc5b4\uc57c \ud55c\ub2e4. SORT_AREA_RETAINED_SIZE\ub294 SORT_AREA_SIZE\uc640 \ub9c8\ucc2c\uac00\uc9c0\ub85c \uc801\uc808\ud55c \uac12\uc774 \uc9c0\uc815\ub418\uc5b4\uc57c \ud558\ub294\ub370, \uc18c\ud2b8\uc791\uc5c5\uc744 \uc218\ud589\ud558\uae30 \uc704\ud574 \ud560\ub2f9\ub41c \uba54\ubaa8\ub9ac \uc601\uc5ed\uc774 \uc18c\ud2b8 \uc791\uc5c5\uc774 \ub05d\ub09c \ud6c4\uac00 \uc544\ub2c8\ub77c \uc138\uc158\uc774 \uc885\ub8cc\ub420 \ub54c \uae4c\uc9c0 \uc720\uc9c0\ub420 \uc218 \uc788\uae30 \ub54c\ubb38\uc774\ub2e4. SORT_AREA_SIZE \uac12\uc740 \ub2e4\ub978 \ud30c\ub77c\ubbf8\ud130\uc640 \ub9c8\ucc2c\uac00\uc9c0\ub85c \uc2dc\uc2a4\ud15c\uc5d0 \uac00\uc6a9\ud55c \uc2e4\uc81c \uba54\ubaa8\ub9ac \ud06c\uae30 \uc774\ub0b4\uc5d0\uc11c \uc870\uc815\ub418\uc5b4\uc57c \ud55c\ub2e4. \uc77c\ubc18\uc801\uc73c\ub85c \uad8c\uace0\ub418\ub294 SORT_AREA_SIZE \uac12\uc740 65k \uc5d0\uc11c 1M \uc0ac\uc774 \uc5d0\uc11c \uacb0\uc815\ub41c\ub2e4.<\/p>\n
Oracle 8i \ubd80\ud130\ub294 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\ub294 \ud30c\ub77c\ubbf8\ud130\ub85c, SGA\uc758 buffer cache \uac12\uc744 \uc99d\uac00\uc2dc\ud0a4\uac70\ub098 \uac10\uc18c\uc2dc\ud0ac \uacbd\uc6b0 \ubbf8\uce58\ub294 \uc601\ud5a5\uc744 \uc608\uce21\ud558\uae30 \uc704\ud55c \uac01\uc885 \ud1b5\uacc4 \uc815\ubcf4\ub97c \uc218\uc9d1\ud558\ub294 \uc791\uc5c5\uc744 \ud65c\uc131\ud654 \uc2dc\ud0a4\uac70\ub098 \ube44 \ud65c\uc131\ud654 \uc2dc\ud0ac \uc218 \uc788\ub2e4.
\uc0ac\uc6a9\uc790\ub294 DB_BLOCK_BUFFERS \uac12\uc744 \ubc14\uafb8\uc5b4 \uc2dc\uc2a4\ud15c\uc744 \uc7ac \uae30\ub3d9 \uc2dc\ud0a4\uc9c0 \uc54a\uace0\ub3c4, alter system \uba85\ub839\uc73c\ub85c buffer cache \ud06c\uae30\ub97c \uc870\uc815\ud560 \uc218 \uc788\uac8c \ud574 \uc8fc\uc2dc\ub9cc, \ub0b4\ubd80\uc801\uc73c\ub85c\ub294 DB_BLOCK_BUFFERS \uac12\uc740 \ub370\uc774\ud130
\ubca0\uc774\uc2a4 \uc7ac \uae30\ub3d9\uc2dc\uc5d0\ub9cc \ubc14\ub014 \uc218 \uc788\ub2e4. \ud1b5\uacc4\uc815\ubcf4\ub294 X$KCBRBH \ud14c\uc774\ube14\uc5d0 \uc800\uc7a5\ub41c\ub2e4. \uc774 \uac12\uc744 0 \uc774\uc0c1\uc73c\ub85c \uc9c0\uc815\ud558\uba74 DB_BLOCK_BUFFERS \uac12\uc744 \ucd94\uac00\ud558\uac70\ub098 \ud639\uc740 \ucd94\uac00\ud55c \uac83\ucc98\ub7fc simulate \uc2dc\ud0ac \uc218 \uc788\ub2e4. \uae30\ub2a5\uc0c1\uc73c\ub85c\ub294 \ud29c\ub2dd\uc5d0
\ub9ce\uc740 \ub3c4\uc6c0\uc744 \uc904 \uac83 \ucc98\ub7fc \ubcf4\uc774\ub098, \ub9ce\uc740 \ubb38\uc81c\uc810\uc744 \uc548\uace0 \uc788\ub294 \uac83\uc73c\ub85c \uc54c\ub824\uc838 \uc788\uc73c\ubbc0\ub85c \uc624\ub77c\ud074\uc5d0\uc11c\ub294 production \ud658\uacbd\uc5d0\uc11c\ub294 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub3c4\ub85d \uad8c\uace0\ud558\uace0 \uc788\ub2e4.<\/p>\n
sahred pool\uc758 \uc77c\uc815 \ubd80\ubd84\uc744 larget object\uc744 \uc704\ud574 \ud560\ub2f9\ud558\ub3c4\ub85d \uc9c0\uc815\ud558\ub294 \ud30c\ub77c\ubbf8\ud130\ub85c, \uae30\ubcf8\uc801\uc73c\ub85c\ub294 shared_pool_size\uc758 5% \uc815\ub3c4\uac00 \uc0ac\uc6a9\ub41c\ub2e4. \ud30c\ub77c\ubbf8\ud130 \uac12\uc740 byte \ub2e8\uc704\ub85c \uc9c0\uc815\ud55c\ub2e4. \uc774 \ud30c\ub77c\ubbf8\ud130\ub97c \uc9c0\uc815\ud560 \ub54c \uc720\uc758\ud574\uc57c \ud560 \uc810\uc740 shared pool\uc758
\ub300\ubd80\ubd84\uc758 \uc601\uc5ed\uc774 large object\uc5d0 \uc758\ud574 \uc0ac\uc6a9\ub418\uc9c0 \uc54a\ub3c4\ub85d \ud558\uace0, large object\ub294 \ubcc4\ub3c4\uc758 \uc601\uc5ed\uc5d0\uc11c \ucc98\ub9ac\ub418\ub3c4\ub85d \uc9c0\uc815\ud558\ub294 \uac83\uc774 \uad00\uac74\uc774\ub2e4.<\/p>\n
——————-
<Note:100709.1<\/a>>
<NOTE:106285.1<\/a>>
<NOTE:62161.1<\/a>><\/p>\n","protected":false},"excerpt":{"rendered":"