KEEP BUFFER 활용방안 엑셈컨설팅본부 /DB 컨설팅팀장정민 개요 Oracle 은유저가요청한작업을빠르게처리하기위해 Buffer Cache 라는것을사용한다. Buffer Cache 는 SGA 에위치하고있으며, 오라클인스턴스에접속하는모든프로세스에의해공유된다. 이 Bu

KEEP BUFFER 활용방안 엑셈컨설팅본부 /DB 컨설팅팀장정민 개요 Oracle 은유저가요청한작업을빠르게처리하기위해 Buffer Cache 라는것을사용한다. Buffer Cache 는 SGA 에위치하고있으며, 오라클인스턴스에접속하는모든프로세스에의해공유된다. 이 Buffer Cache 는오라클 I/O 관리의핵심으로자주사용하는데이터파일의블록들을메모리에상주시킴으로써물리적인 I/O Operation 을줄이는역할을한다. Buffer Cache 를효과적으로사용하면물리적 I/O 가줄어들고자연스럽게 I/O 성능문제를해결할수있다. 오라클의버전이올라감에따라 Buffer Cache 를처리하는알고리즘은끊임없이개선되었고, 더불어새로운관리방법들이제공되었다. Oracle 7 까지의 Buffer Cache 는하나의틀로서운영되었고, 각 Object 의특성에따른차별적인 Buffer Cache 이용이어려웠다. 이런면을해결하기위해 Oracle 8 부터 Multiple buffer pool 이라는기능을지원하게되었는데, 이로인해각 Object 의특성이나엑세스빈도등차별성을고려하여 Buffer Cache 를보다세밀하게관리할수있게되었다. Keep Buffer 는이 Multiple Buffer Pool 을구성하는여러영역중의하나이다. KEEP Buffer 사용목적및특성 Keep Buffer 의사용목적은본래의 Buffer Cache 의목적과마찬가지로, Object 를메모리에 상주시킴으로써물리적인 I/O 를피하는데있다. Keep Buffer Pool 과 Default Buffer Pool 이데이터블록을 Cache 하거나 Flush 할때서로다 른알고리즘을사용하지는않는다. 그럼에도불구하고하나였던 Buffer Cache 영역을 Multiple Buffer Pool 로나누게된이유는 Object 의특성을고려한 Buffer Cache 이용을위함이다. Part 1 ORACLE 161

KEEP Buffer 의메모리공간은 Sequential 하게관리된다. 한번 KEEP 된세그먼트는 KEEP Buffer 의메모리공간을모두할당하기전까지메모리에유지되다가 KEEP Buffer 공간을모두할당하게되면, 가장오래된블록부터 default pool 로밀려난다. 때문에 KEEP 대상이되는세그먼트들의사이즈를정확히계산하여 KEEP Buffer 크기를적절히할당해야한다. KEEP Buffer 사용순서 Keep Buffer 의사용은다음과같은순서로진행한다. 1. KEEP 대상선정하기 2. OS Memory & SGA 공간확인 3. KEEP Buffer 설정 4. 테이블 / 인덱스속성변경 5. 테이블 / 인덱스 Keeping 6. KEEP 효율성체크 7. KEEP 대상선정기준 KEEP 대상선정하기 KEEP 대상선정에있어서명확한기준점은없다. 실제업무를고려하여각 DB 의운영환경에맞는대상을선정해야한다. 만약 KEEP 하는대상이아주빈번하게사용되는블록이라면, 기본적인 Default Buffer 를사용해도 Cache 돼있을가능성이높은데, 이런경우에는 Keep Buffer 사용이성능상의이점을가져오지못한다. 반대로자주사용되지않는블록을 Keep Buffer 에상주시킨다면, 사용하지않는메모리를가지고있는것이기때문에전반적인성능을저하시키는요인이될수도있다. 때문에 Keep 대상을선정하는데있어서는실제업무의고려가필수적이다. 예를들어하루에 1 번만수행되는프로그램인데어떻게해서든수행시간을단축시켜야하는경우나, 많은업무를처리하는시간대에꼭수행되야하는데많은 I/O 때문에병목현상을일으켜서시스템에전반적으로악영향을끼치는프로그램등이있을수있다. 이런프로그램들이사용하는 Object 들이 162 2013 기술백서 White Paper

Keep 대상이될수있다. 그러나위와같은업무프로그램에사용되는모든세그먼트를 KEEP Buffer 에상주시킬수는없다. 그러므로 KEEP Buffer 에상주시킬대상은각 DB 운영환경을고려하여선정해야한다. 이외에도크기, DML 빈도, 데이터엑세스빈도에따라 Keep 하기에적절한세그먼트들이존재한다. 선정기준 [1]. 프로그램중요도 Keep 대상선정에있어서가장중요한부분이바로해당세그먼트를조회하는업무프로그램의중요도이다. 해당프로그램이중요하지않다면굳이 Keep Buffer 를사용해야할필요가없다. 반대로해당세그먼트를조회하는프로그램이중요도가아주높고어떻게든수행시간을단축해야한다면프로그램수행빈도와상관없이 KEEP 대상으로의선정을고려할수있다. 선정기준 [2]. 세그먼트크기 세그먼트크기가일정하지않고, 과다하게커지는세그먼트는 Keep Buffer 의효율성을떨어뜨릴수있다. Keep 된세그먼트는 Keep Buffer 의용량이부족하면오래된블록부터 Default Buffer 로밀려나게되는데, 크기가계속커지는세그먼트가 Keep Buffer 에존재한다면타세그먼트를조회하는프로그램의성능저하를가져올수있기때문이다. 따라서일정한사이즈또는변동량이심하지않으면서최대크기가일정수준이하인경우의세그먼트를선정하는것이바람직하다. 예를들면 최대크기가 10 만블록이하인세그먼트 같은기준을정할수있다. 선정기준 [3]. Full Table Scan & Index Full Scan & Index Fast Full Scan KEEP Buffer 에 KEEP 된세그먼트를조회할때효율성을극대화하기위해서는다소많은량을 처리해야하는경우이다. Scan 범위가넓은비효율 Index Scan 이나 Full Table Scan, Index Fast Full Scan 으로처리되는세그먼트가대상이될수있다. Part 1 ORACLE 163

KEEP 대상선정 SQLScript SELECT owner, table_name, index_name, partition_name, SUM( blocks ) AS t_blocks FROM ( SELECT sg.owner, decode( SUBSTR( s.ob_type, 1, 5 ), 'TABLE', s.ob_name, 'INDEX', ( SELECT table_name FROM dba_indexes WHERE index_name = s.ob_name ) ) AS table_name, decode( SUBSTR( s.ob_type, 1, 5 ), 'INDEX', s.ob_name ) AS index_name, sg.partition_name, sg.blocks FROM ( SELECT DISTINCT object_name AS ob_name, object_type AS ob_type FROM v$sql_plan WHERE ( operation = 'TABLE ACCESS' AND options = 'FULL' ) OR ( operation = 'INDEX' AND options = 'FULL SCAN' ) OR ( operation = 'INDEX' AND options = 'FAST FULL SCAN' ) --> 선정기준 [3] ) s, dba_segments sg WHERE s.ob_name = sg.segment_name ) GROUP BY owner, table_name, index_name, partition_name HAVING SUM( blocks ) > 100000 --> 선정기준 [2]SELECT * FROM DUAL 164 2013 기술백서 White Paper

OS Memory & SGA 공간확인 OS Memory SGA 공간확인 SQLScript $ cat /proc/meminfo MemTotal: 4055152 kb MemFree: 1390308 kb Buffers: 166768 kb Cached: 2019992 kb SwapCached: 0 kb Active: 1118484 kb Inactive: 1277864 kb SGA 공간확인 SQLScript SGA 전체 size 확인 SELECT name, FROM ROUND( bytes/1024/1024 ) "size(mb)" V$SGAINFO; size(mb) ------------------------------- ---------- Fixed SGA Size 2 Redo Buffers 5 Buffer Cache Size 48 Shared Pool Size 128 Large Pool Size 0 Java Pool Size 24 Streams Pool Size 0 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 207 Startup overhead in Shared Pool 72 Free SGA Memory Available 0 Data Buffer size 확인 Part 1 ORACLE 165

SELECT name, current_size FROM v$buffer_pool; CURRENT_SIZE -------------------- ------------ DEFAULT 48 KEEP BUFFER 설정 KEEP Buffer 설정은 KEEP Buffer 크기와 SGA 여유공간에따라, Online 작업또는 Offline 작업으로수행한다. 이문서의스크립트는 SGA 영역의메모리관리를수동으로하는경우를바탕으로작성하였다. KEEP Buffer 설정 Script @sga size(mb) -------------------------------- ---------- Buffer Cache Size 500 Maximum SGA Size 1019 Free SGA Memory Available 228 @bc CURRENT_SIZE -------------------- ------------ DEFAULT 500 KEEP Buffer 의크기가 SGA 의 Free 공간보다작은경우 Online ( KEEP Buffer 100M ) SQL> alter system set db_keep_cache_size = 100M scope = both; System altered. SQL> @sga 166 2013 기술백서 White Paper

size(mb) -------------------------------- ---------- Buffer Cache Size 600 Maximum SGA Size 1019 Free SGA Memory Available 128 SQL> @bc CURRENT_SIZE -------------------- ------------ KEEP 100 DEFAULT 500 KEEP Buffer 의크기가 SGA 의 Free 공간보다큰경우 ( KEEP Buffer 300M ) 1. SGA 전체크기늘린후 KEEP Buffer 할당 Offline 작업필요 SQL> alter system set sga_max_size = 1100M scope = spfile; System altered. SQL> alter system set db_keep_cache_size = 300M scope = spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. Database opened. 1169227776 bytes 2212696 bytes 301993128 bytes 855638016 bytes 9383936 bytes SQL> @sga Part 1 ORACLE 167

size(mb) -------------------------------- ---------- Buffer Cache Size 816 Maximum SGA Size 1115 Free SGA Memory Available 0 SQL> @bc CURRENT_SIZE -------------------- ------------ KEEP 304 DEFAULT 512 => SGA 전체크기가 1G 를초과하면서 Granule 크기가 16Mb 로늘어났다 이때지정한값보다큰 16 의배수중가장작은크기의값이할당된다. 2. SGA 의다른영역의크기를줄인후 KEEP Buffer 할당할경우 Online 작업가능 Online 상태에서변경가능 Parameter 추출 Script SELECT name, issys_modifiable FROM v$parameter WHERE name LIKE '%size%' AND issys_modifiable = '' ISSYS_MOD --------------------------------------- --------- shared_pool_size large_pool_size java_pool_size streams_pool_size db_cache_size db_2k_cache_size db_4k_cache_size db_8k_cache_size db_16k_cache_size db_32k_cache_size db_keep_cache_size db_recycle_cache_size _shared_io_pool_size 168 2013 기술백서 White Paper

db_flash_cache_size db_recovery_file_dest_size result_cache_max_size workarea_size_policy max_dump_file_size => 해당 Parameter 값을적절히조절하여 Free Memory 확보후 KEEP 설정 테이블 / 인덱스속성변경 테이블 / 인덱스속성변경 Script ALTER TABLE T1 STORAGE (BUFFER_POOL KEEP); --테이블속성변경 ALTER INDEX T1_PK STORAGE (BUFFER_POOL KEEP); --인덱스속성변경 ALTER TABLE P1 MODIFY PARTITION P1_1 STORAGE (BUFFER_POOL KEEP); --파티션테이블속성변경 ALTER INDEX P1_ID1 MODIFY PARTITION P1_ID1_1 STORAGE (BUFFER_POOL KEEP);--파티션인덱스속성변경 테이블 / 인덱스 Keeping Segment 의 Buffer Pool 이 KEEP 으로설정된테이블과인덱스는 Query 시 KEEP Buffer 에해당세그먼트의블록을로딩하게된다. 그러므로최초세그먼트를 Loading 할때에는 Disk I/O 가발생하게된다. 만일처음실행하는때를포함하여모든 Application 의조회에서 Disk I/O 를제거하고싶다면, 업무가진행되기전에해당세그먼트들을 Full Table Scan 이나 Index Fast Full Scan 으로 KEEP Buffer 에로딩시키면된다. KEEP Buffer 효율성판단 KEEP Buffer 의사용에명확한기준이정해져있는것이아니라운영환경에따라차이가존재한다. 때문에모든운영환경에서같은방법으로효율성을판단하기에는무리가있다. 하지만다음과같은자료들이 KEEP Buffer 의효율성을판단하는데근거가될수있다. KEEP Buffer Size & Hit Ratio SQLScript Part 1 ORACLE 169

SELECT current_size keep_size, seg_size, ROUND( seg_size/current_size*100, 1 ) "Ratio(%)" FROM v$buffer_pool, ( SELECT SUM( bytes ) /1024 /1024 seg_size FROM dba_segments WHERE buffer_pool = 'KEEP' ) WHERE name = 'KEEP' KEEP_SIZE SEG_SIZE Ratio(%) ---------- ---------- ---------- 304 118 38.8 SELECT db_block_gets, consistent_gets, physical_reads, CASE WHEN db_block_gets+consistent_gets <> 0 THEN ROUND(( 1-( physical_reads/( db_block_gets+consistent_gets ) ) ) *100, 2 ) END "Keep_Hit(%)" FROM v$buffer_pool_statistics WHERE name = 'KEEP' DB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS Keep_Hit(%) ------------- --------------- -------------- ----------- 0 44474 4435 90.03 만약 KEEP Buffer 를사용하는 Segment 크기의총합이 KEEP Buffer 크기보다작은경우, 해 당 Segment 들의크기가더이상커지지않는다면, 한번 KEEP 영역으로올라간 Segment 의 Cache Hit Ratio 가 100% 에가깝게될것이다. 만일 KEEP Buffer 의크기가해당영역을사용하는 Segment 들의크기보다작다면 KEEP Buffer 영역에서경합이발생하고, 그로인해 Physical I/O 가발생하여 Cache Hit Ratio 가떨어질수있다. 이때 KEEP Buffer 의크기를늘려주거나중요도가떨어지는 Segment 의 KEEP Buffer 사용을막는방안을고려해볼수있다. 반대로 KEEP Buffer 의크기가 Segment 들의 170 2013 기술백서 White Paper

크기보다많이크다면, 사용하지않는메모리공간을차지하고있는것이므로 KEEP Buffer 의 크기를줄이는것을고려해볼수있다. 따라서시스템성능과 Segment 들의중요도에따라효 율적인 KEEP Buffer 의크기조절이필요하다. 다음스크립트로 dba_hist_seg_stat 뷰를조회하여 Segment 조회시발생하는 I/O 발생량에 대한 AWR 정보를확인하여 Segment 별효율성을판단할수있다. Segment I/O SQLScript accept i_begin_time prompt 'Enter begin time[yyyymmddhh24]: ' accept i_end_time prompt 'Enter end time[yyyymmddhh24]: ' variable v_begin_time char(10) variable v_end_time char(10) exec :v_begin_time:=&i_begin_time exec :v_end_time :=&i_end_time SELECT /*+ leading(k) */ s.dbid, decode( SUBSTR( o.object_type, 1, 5 ), 'TABLE', o.object_name, 'INDEX', ( SELECT table_name FROM dba_indexes WHERE index_name = o.object_name AND owner = k.owner ) ) AS table_name, decode( SUBSTR( o.object_type, 1, 5 ), 'INDEX', o.object_name ) AS index_name, s.snap_id, TO_CHAR( w.begin_interval_time, 'yyyymmdd.hh24' ) AS begin_time, s.physical_reads_delta, s.physical_reads_direct_delta, s.physical_reads_delta + s.physical_reads_direct_delta AS total_diskio FROM sys.wrm$_snapshot w, dba_hist_seg_stat s, dba_objects o, ( SELECT owner, segment_name FROM dba_segments Part 1 ORACLE 171

WHERE buffer_pool = 'KEEP' ) k WHERE w.begin_interval_time >= to_timestamp( '2013062510', 'yyyymmddhh24' ) AND w.end_interval_time <= to_timestamp( '2013062518', 'yyyymmddhh24' ) AND w.snap_id = s.snap_id AND w.dbid = s.dbid AND w.instance_number = s.instance_number AND s.obj# = o.object_id AND k.segment_name = o.object_name AND k.owner = o.owner ORDER BY 2, 3, 5 결론 KEEP Buffer 를사용하는데있어가장중요한것이업무의반영일것이다. 자주사용하는 Object 들만상주하는 Buffer Cache 하나만사용하는것이시스템전체의관점에서보면효율적일수도있다. 하지만업무의중요성이나특성을고려한다면다른결과가나올수있다. 적게실행되더라도중요도가높은업무가있을수있고, 수행시간단축이매우중요한업무가있을수있다. 이러한업무에대한특성을반영한운영계획을세우는데있어서, KEEP Buffer 를효율적으로사용할수있다면, 시스템성능향상에큰도움이될것이다. 172 2013 기술백서 White Paper