11g - Serial direct read 동작원리 엑셈컨설팅본부 /DB 컨설팅팀임경석 개요 오라클 11g 에서처음소개된 Serial direct read 는대량의데이터를처리하는엑사데이타에서매우중요한기능이다. 스마트스캔을하기위해서는반드시테이블 full scan 과 d

11g - Serial direct read 동작원리 엑셈컨설팅본부 /DB 컨설팅팀임경석 개요 오라클 11g 에서처음소개된 Serial direct read 는대량의데이터를처리하는엑사데이타에서매우중요한기능이다. 스마트스캔을하기위해서는반드시테이블 full scan 과 direct path read 가선행되어야하기때문이다. 그러나, serial direct read 의동작은옵티마이져환경에영향을받지않으므로힌트를적용하여제어할수없고, 뜻대로동작하지않거나중간에변경되기도한다. 그렇다고임의로동작하는것은아니며몇가지규칙이존재한다. 필자는 serial direct read 가어떠한규칙에의해결정되고, 변경되는지몇가지사례를통해자세히소개하고자한다. Serial direct read 란? DW 환경에서는대량의데이터를조회하는경우가빈번하다. 따라서, 테이블이나인덱스에대한풀스캔을피할수없다. 오라클에서풀스캔은여러개의블록을한번에버퍼캐시로읽어들이는멀티블록 I/O 를통해처리된다. 멀티블록 I/O 는크기가제한된버퍼캐시를통해이루어지므로내부적으로복잡한과정이필요하므로버퍼캐시에상당한부담이될수있다. 블록을읽기위해프리리스트를검색하고버퍼를변경할때마다래치를획득하여 cache buffers chains latch, cache buffers lru chain latch 와같은이벤트가발생할수있다. 이러한블록들은 LRU 리스트끝에위치하지만다른세션들이필요로하는캐시된블록들을 age out 시키고, 이미변경된블록들에대해서는다량의 CR 복사본을생성할수있다. 따라서, 이러한이유들로인해일반적으로테이블을풀스캔할경우버퍼캐시를거치지않는 PQ(Parallel Query) 를많이사용한다. PQ 슬레이브세션들은 direct path read 를통해 PGA 로 데이터를직접읽어들이므로버퍼캐시에대한부담을줄여주고성능에도효과적이다. 하지만, Part 1 ORACLE 59

과도한 PQ 사용은 CPU, MEMORY 사용률증가와빈번한체크포인트로인해오히려시스템부 하를가중시킬수있다. 오라클은 Parallel 옵션을사용하지않고 direct read 가가능한 SDPR(serial direct path read) 을 11g 에서처음소개하였다. SDPR 은테이블 full scan 이나인덱스 fast full scan 시싱글모드로버퍼캐시를거치지않고블록을직접읽어들이는것을말한다. 사실이러한기능은오라클 10G 에도있었지만본격적으로적용된것은 11g 부터이다. 특히, 11gr2 이후에는몇가지파라미터를통해적절한제어도가능해졌다. serial direct read 는히든파라미터 _serial_direct_read 로정의한다. 10G 에는기본값이 false 였지만, 11gr2 부터 auto 로변경되면서오라클이적절하게 direct read 를적용할수있 게되었다. _serial_direct_read 옵션은 true, false, auto, always, never 로크게 5 가지로정의할수있다. 이중 true, always 는풀스캔하는모든세그먼트를항상 SDPR 로처리하고, 반대로 never 는어느경우든처리하지않는다. 하지만, false 의경우 never 와같은의미로보이지만오히려 auto 와비슷하게동작한다. NAME VALUE DESCRIPTION ----------------------------------------------------------- _serial_direct_read AUTO enable direct read in serial 어떻게동작하는가? 데이터베이스에존재하는모든테이블이 SDPR 의대상이되는것은아니다. 오라클은 SDPR 이가능한테이블의선택기준을히든파라미터 _small_table_threshold 에정의하였다. 기본값은 _db_block_buffers 의 2% 로써이보다작으면오라클이작은테이블로인식한다는의미이다. 10g 에서는테이블을풀스캔할때작은테이블은 LRU 리스트의중간에위치하고, 큰테이블은 LRU 리스트의맨끝에위치하므로 age out 되기가쉬워 physical read 가발생할확률이높음을의미했다. 그러나, 11gr2 에서해당파라미터는 direct read 가가능한테이블크기의최소임계치로정의한다. 60 2013 기술백서 White Paper

NAME DESCRIPTION ------------------------- -------------------------------------------------- _small_table_threshold lower threshold level of table size for direct reads 테이블을풀스캔하면먼저테이블의세그먼트헤더블록을읽어 HWM 이하차지하는블록이몇개인지조회한다. 블록의개수가 _small_table_threshold 보다크면 direct path read 로처리되고, 이보다작으면버퍼캐시를통한멀티블록 I/O 로인해 db file scattered read 로처리된다. 11gR2 이전에는테이블의크기가 _small_table_threshold 설정값의 5 배또는그이상이되어야 SDPR 이가능하였다. 그러나, 11gR2 이후에는 _small_table_threshold 값과테이블의크기가거의비슷해지는순간발생한다. 또한, 테이블의전체블록수와비교하여버퍼캐시에캐시되어있는테이블의블록또는더티블록이차지하는비율에따라달라지기도한다. 특정테이블이차지하는전체블록중 50% 이상이버퍼캐시에캐시되어있거나 25% 이상이더티상태로캐시될경우, 테이블의전체블록수가 _small_table_threshold 보다크더라도 SDPR 은발생하지않는다. 이와같은사실은 SDPR 이옵티마이져에의한것이아닌실제세그먼트블록을조회하는런타임시점에결정되기때문이다. 따라서, 테이블의크기, 버퍼캐시의크기, 버퍼캐시에캐시된테이블의블록수를고려하여 SDPR 여부는언제든바뀔수있어좀더안정적인성능을보장할수있다. 11gR2 이후에는이와는다른한가지방식이추가되었다. SDPR 의가능여부를실제테이블의블록수를조회하여결정하던것을테이블의통계정보에저장된블록수 (dba_tables.blocks) 로결정하는것이다. 이것은히든파라미터 _direct_read_decision_statistics_driven 로제어할수있으며기본값은 true 이다. 통계정보의 blocks 를참조할경우통계정보가바뀌지않는한테이블의크기에변화가있더라도 SDPR 결정에영향을주지않는다. 따라서, 통계정보를활용하여 SDPR 를좀더유연하게적용할수있다. 하지만, 버퍼캐시에캐시된테이블의블록수와상태에따라서는여전히영향을받는다. serial direct read 등장배경에는엑사데이타와밀접한관련이있다. 아마도엑사테이타의주요기능인스마트스캔이테이블을 direct read 로풀스캔할때가능하기때문일것이다. SSD 플래시디스크활용이증가하면서플래시디스크를통한 direct read 는 I/O 속도를한단계더진화시켰다. 따라서, serial direct read 를적절하게사용하면성능에큰도움이될수있다. Part 1 ORACLE 61

(1) 테이블크기변화에따른 serial direct read. _small_table_threshold 를기준으로 SDPR 이결정되는테이블의최소크기는어떻게정해지는지테스트를통해살펴보자. 한가지주의할것은오라클 11gR2 의경우, 정확한테스트를위해테이블통계정보를생성하지않거나 _direct_read_decision_statistics_driven=false 로하여통계정보 blocks 로 SDPR 이결정되지않게해야한다. 테스트로사용되는테이블은한블록에한개의로우만저장되도록생성하고, 테이블크기변화에따라 SDPR 발생여부를측정할수있는프로시져를생성한다. -- 오라클버젼확인 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production -- 테스트테이블생성. SQL> create table sdpr_test (col varchar2(100)) pctused 1 pctfree 99 tablespace users ; -- 테이블의크기를측정하기위한프로시져생성. create or replace procedure SDPR_PRO( p_start in number default 0, p_step in number, p_stop in number default null ) is b_prd number; b_prd1 number; b_prd2 number; b_blocks number:=0; b_start number:=p_start; b_cnt number:=0 ; b_result number; begin 62 2013 기술백서 White Paper

dbms_output.enable(buffer_size=>10000); execute immediate 'truncate table sdpr_test'; select value into b_prd1 from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = 'physical reads direct' and st.sid = userenv('sid'); loop insert /*+ append */ into sdpr_test select rpad('*', 100, '*') from dual connect by level <= p_step + b_start; commit; b_blocks:=b_blocks + p_step + b_start; b_start:=0; execute immediate 'alter system flush buffer_cache'; select /*+ full(sdpr_test) */ count(*) into b_cnt from sdpr_test; select value into b_prd2 from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = 'physical reads direct' and st.sid = userenv('sid'); b_prd:=b_prd2 - b_prd1 ; exit when (b_prd > 0 or b_blocks > nvl(p_stop, b_blocks)); end loop; b_result:=b_blocks-p_step; dbms_output.put_line(chr(10) 'Block count for SDPR: ' b_result ' blocks'); end; / Part 1 ORACLE 63

-- buffer.sql accept obj prompt'enter the object name :' SELECT o.owner, o.object_name, decode( state, 0, 'free', 1, 'xcur', 2, 'scur', 3, 'cr', 4, 'read', 5, 'mrec', 6, 'irec', 7, 'write', 8, 'pi' ) state, decode( bitand( flag, 1 ), 0, 'N', 'Y' ) dirty, decode( bitand( flag, 16 ), 0, 'N', 'Y' ) temp, decode( bitand( flag, 1536 ), 0, 'N', 'Y' ) ping, decode( bitand( flag, 16384 ), 0, 'N', 'Y' ) stale, decode( bitand( flag, 65536 ), 0, 'N', 'Y' ) direct, COUNT( * ) blocks FROM sys.xm$bh b, dba_objects o WHERE b.obj = o.data_object_id AND b.ts# >= 0 AND o.object_name = UPPER( LTRIM( RTRIM( '&obj' ) ) ) AND state = 1 GROUP BY o.owner, o.object_name, state, decode( bitand( flag, 1 ), 0, 'N', 'Y' ), decode( bitand( flag, 16 ), 0, 'N', 'Y' ), decode( bitand( flag, 1536 ), 0, 'N', 'Y' ), decode( bitand( flag, 16384 ), 0, 'N', 'Y' ), decode( bitand( flag, 65536 ), 0, 'N', 'Y' ), blsiz ; -- _small_table_threshold 을 1000 으로설정. SQL> alter session set "_small_table_threshold"=1000 ; -- sdpr_pro 프로시져실행 SQL> exec sdpr_pro(p_start=>800,p_step=>1,p_stop=>1500); Block count for SDPR: 976 blocks 64 2013 기술백서 White Paper

-- 버퍼캐시상태조회. SQL> @buffer enter the object name :sdpr_test OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS -------- ---------- ----- ----- ----- ----- ----- ------ ------ SYS SDPR_TEST xcur N N N N N 1 _small_table_threshold 를 1000 으로하고 SDPR_TEST 테이블의블록수가 800 블록부터한블록씩증가하여 1500 블록까지커지도록프로시저를실행한결과, 테이블의크기가 976 블록을 1 블록초과하는 977 블록이되는순간최초로 direct read 가발생한다. 이것은 _small_table_threshold 가 1000 일때테이블크기의최소임계치는 976 블록임을의미한다. 따라서, HWM 까지의크기가 976 블록을초과하는테이블을풀스캔한다면 direct path read 로실행될수있다는의미이다. 테스트세션에대해 10046 트레이스를걸어좀더자세히살펴보면, 제일먼저 db file sequential read 이벤트를대기하면서 file#=4, block#=17098 블록을읽는것을확인할수있다. 해당블록은 SDPR_TEST 테이블의세그먼트헤더블록으로이를통해테이블이차지하는실제 HWM 이하까지의블록수를조회한다. 테이블의실제블록수를 _small_table_threshold 값과비교하여 SDPR 을수행하기로결정되면, 뒤이어더티상태의블록들을디스크에반영하는체크포인트가이루어지고 enq: KO - fast object checkpoint 이벤트를대기한다. 체크포인트는 SDPR_TEST 테이블에한하여매우짧은시간수행되고, 체크포인트가끝나면 direct path read 로테이블을풀스캔하게된다. 실제버퍼캐시상태를 x$bh 뷰를통해조회하면, SDPR_TEST 테이블의전체블록중세그먼트헤더에해당하는블록한개만버퍼캐시에존재한다는것을알수있다. -- 10046 세션트레이스 WAIT #140630657766376: nam='db file sequential read' ela= 9 file#=4 block#=17098 blocks=1 obj#=80219 tim=1362977906994976 WAIT #140630657766376: nam='db file sequential read' ela= 6 file#=3 block#=192 blocks=1 obj#=0 tim=1362977906995033 Part 1 ORACLE 65

WAIT #140630657766376: nam='reliable message' ela= 71 channel context=2736958984 channel handle=2736907704 broadcast message=2738048368 obj#=0 tim=1362977906995246 WAIT #140630657766376: nam='enq: KO - fast object checkpoint' ela= 483 name mode=1263468550 2=65562 0=1 obj#=0 tim=1362977906995753 WAIT #140630657766376: nam='asynch descriptor resize' ela= 0 outstanding #aio=0 current aio limit=412 new aio limit=442 obj#=0 tim=1362977906995790 WAIT #140630657766376: nam='direct path read' ela= 24 file number=4 first dba=17099 block cnt=5 obj#=80219 tim=1362977906995941 WAIT #140630657766376: nam='direct path read' ela= 39 file number=4 first dba=16080 block cnt=8 obj#=80219 tim=1362977906996015 WAIT #140630657766376: nam='direct path read' ela= 96 file number=4 first dba=16089 block cnt=15 obj#=80219 tim=1362977906996142 WAIT #140630657766376: nam='direct path read' ela= 43 file number=4 first dba=16105 block cnt=15 obj#=80219 tim=1362977906996213 WAIT #140630657766376: nam='direct path read' ela= 9 file number=4 first dba=16121 block cnt=7 obj#=80219 tim=1362977906996261 WAIT #140630657766376: nam='direct path read' ela= 11 file number=4 first dba=17024 block cnt=8 obj#=80219 tim=1362977906996297 -- 버퍼캐시상태조회. SQL> SELECT o.owner, 2 o.object_name, 3 decode( state, 0, 'free', 1, 'xcur', 2, 'scur', 3, 'cr', 4, 'read', 5, 'mrec', 6, 'irec', 7, 'write', 8, 'pi' ) state, 4 blsiz, 5 dbarfil, 6 dbablk 7 FROM sys.xm$bh b, 8 dba_objects o 9 WHERE b.obj = o.data_object_id 10 AND b.ts# >= 0 11 AND o.object_name = 'SDPR_TEST' 12 AND state = 1; OWNER OBJECT_NAM STATE BLSIZ DBARFIL DBABLK ------------------------------------------------------- SYS SDPR_TEST xcur 8192 4 17098 66 2013 기술백서 White Paper

-- SDPR_TEST 테이블의세그먼트헤더블록조회. SQL> select segment_name, header_file, header_block 2 from dba_segments 3 where segment_name = 'SDPR_TEST'; SEGMENT_NAME HEADER_FILE HEADER_BLOCK --------------- ------------- ------------- SDPR_TEST 4 17098 (2) 테이블의캐시된블록수변화에따른 serial direct read. SDPR_TEST 테이블이 977 블록이상커지더라도항상 SDPR 로수행되는것은아니다. SDPR_TEST 테이블이현재버퍼캐시에얼마나캐시되어있는지에따라 SDPR 여부는달라질수있다. 테이블의전체블록중 50% 이상이버퍼캐시에이미캐시되어있다면더이상 SDPR 은발생하지않는다. 테이블전체블록을디스크로부터직접읽는것보다캐시되어있는블록을읽는것이더유리하다고판단해서이다. 그러면, SDPR_TEST 테이블이버퍼캐시에어느정도캐시되었을때 SDPR 이발생하지않는지테스트를통해살펴보자. 먼저, 버퍼캐시에블록을캐시하기위한프로시저를생성한다. SDPR_TEST 테이블 300 블록을미리캐시한후, 캐시된블록이한개씩증가하도록프로시저를실행하면블록이캐시된블록이몇개일때 SDPR 이멈추는지확인해보자 -- 인덱스생성. SQL> create index idx_sdpr_test on sdpr_test (1) tablespace users; -- 프로시져생성. create or replace procedure cached_sdpr_pro( p_start in number default 0, p_step in number default 1 ) is b_v varchar2(100); b_trsh number:=0; Part 1 ORACLE 67

b_prd number; b_prd1 number; b_prd2 number; b_cnt number:=0; b_start number:=p_start; cursor b_cur is select /*+ index(sdpr_test idx_sdpr_test) */ * from sdpr_test ; begin dbms_output.enable(buffer_size=>10000); execute immediate 'alter system flush buffer_cache'; select value into b_prd1 from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = 'physical reads direct' and st.sid = userenv('sid'); open b_cur; loop for i in 1.. p_step+b_start loop fetch b_cur into b_v; end loop; b_trsh:=b_trsh+p_step+b_start; b_start:=0; select /*+ full(sdpr_test) */ count(*) into b_cnt from sdpr_test ; select value into b_prd2 from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = 'physical reads direct' and st.sid = userenv('sid'); b_cnt:=b_prd2 - b_prd1 ; exit when b_cnt=b_prd or b_cur%notfound ; b_prd:=b_cnt; end loop; close b_cur; dbms_output.put_line(chr(10) 'Block count for impossible sdpr : ' b_trsh ' blocks'); end; / -- _small_table_threshold 을 1000 으로설정. 68 2013 기술백서 White Paper

SQL> alter session set "_small_table_threshold"=1000 ; -- dynamic sampling 비활성화 SQL> alter session set optimizer_dynamic_sampling=0; -- 프로시져실행 SQL> exec cached_sdpr_pro(p_start=>300, p_step=>1); Block count for impossible sdpr : 497 blocks 프로시저실행결과 SDPR_TEST 테이블전체블록중 50% (497/977*100 = 50.8 ) 인 497 개블록이캐시되는시점에더이상 SDPR 은발생하지않는다. SDPR_TEST 테이블을풀스캔할때, 버퍼캐시에이미캐시된블록수가 497 개이상존재한다면, direct path read 가아닌버퍼캐시를경유하는 db file scattered read 로처리된다. 여기서한가지주의할점은테스트에사용된테이블은통계정보가존재하지않으므로프로시저를처음수행할때 dynamic sampling 이발생할수있다는것이다. dynamic sampling 이수행되면테이블을샘플링하는과정에블록의일부가버퍼캐시에미리캐시된채프로시저가실행될수있어 497 보다작은결과값이나올수있다. 따라서, 정확한결과값을얻기위해프로시저를한번더실행하거나 dynamic sampling 이되지않도록 optimizer_dynamic_sampling 파라미터레벨을 '0' 으로변경하여야한다. (3) 캐시된더티블록수변화에따른 serial direct read 결정테이블의블록중 25% 이상이더티블록상태로캐시된경우에도 SDPR 은발생하지않는다. 앞에서설명했듯이 direct read 는오브젝트단위의체크포인트를동반하므로테이블의모든더티블록들은체크포인트의대상이된다. 따라서, 25% 이상존재하는더티블록에대해체크포인트를적용한후디스크로부터다시블록을읽는것보다는이미캐시된상태의블록을읽는것이유리하다고판단할수있다. Part 1 ORACLE 69

그럼실제몇개의더티블록이캐시될 SDPR 이더이상발생하지않는지테스트를통해살펴보자. 더티블록이버퍼캐시에캐시되도록프로시저를생성한후어느시점에 SDPR 이멈추는지프로시저를수행한결과값을통해확인해보자. -- 더티블록을생성하기위한프로시저생성. create or replace procedure dirty_sdpr_pro ( p_start in number default 0, p_step in number, p_stop in number default null ) is b_trsh number:=0; b_prd number:=0; b_prd1 number:=0; b_prd2 number:=0; b_cnt number:=0; b_start number:=p_start; begin dbms_output.enable(buffer_size=>10000); execute immediate 'alter system flush buffer_cache'; select value into b_prd1 from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = 'physical reads direct' and st.sid = userenv('sid'); loop b_trsh:=b_trsh+p_step+b_start; update sdpr_test set col=col where rownum <= b_trsh; commit; b_start:=0; select /*+ full(sdpr_test) */ count(*) into b_cnt from sdpr_test ; select value into b_prd2 from v$sesstat st, v$statname sn where st.statistic#=sn.statistic# and sn.name = 'physical reads direct' and st.sid = userenv('sid'); 70 2013 기술백서 White Paper

b_cnt:= b_prd2 - b_prd1 ; exit when b_cnt=b_prd or b_trsh > nvl(p_stop, b_trsh); b_prd:=b_cnt; end loop; dbms_output.put_line(chr(10) 'Block count for impossible sdpr : ' b_trsh ' blocks'); end; / -- _small_table_threshold 을 1000 으로설정. SQL> alter session set "_small_table_threshold"=1000 ; SQL> alter session set optimizer_dynamic_sampling=0 ; -- 프로시져실행 SQL> exec dirty_sdpr_pro(p_start=>50, p_step=>1, p_stop=>1500) ; Block count for impossible sdpr : 245 blocks SQL> @buffer enter the object name :sdpr_test OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS ------- ----------- ------ ------- ------ ------ ------ ------- ------ SYS SDPR_TEST xcur Y N N N N 245 SYS SDPR_TEST xcur N N N N N 733 프로시저수행결과, SDPR_TEST 테이블전체블록중 25% ( 245/977*100 = 25 ) 인 245 개의더티블록이캐시된시점에더이상 SDPR 은발생하지않는다. x$bh 뷰를조회해보면 SDPR_TEST 테이블의더티블록이정확히 245 개캐시되는시점에 direct path read 가멈추고전체 977 블록중나머지 733 개블록이멀티블록 i/o 로인해캐시됐음을확인할수있다. Part 1 ORACLE 71

(4) 통계정보와 serial direct read 오라클 11gR2 이후에는옵티마이져통계정보에저장된 tab$blkcnt, tabpart$.blkcnt, ind$.leafcnt 를참조하여 serial direct read 여부가결정된다. 통계정보를적용하여 SDPR 을결정하기위해서는 _direct_read_decision_statistics_driven 히든파라미터가 true 여야한다. 해당파라미터는 11gr2 에새로추가되었으며기본값이 true 이다. 이것은 11gr2 이후에는기본적으로통계정보를참조하여 SDPR 을결정하겠다는의미이 다. 통계정보에저장된테이블의 blocks 가 _small_table_threshold 값보다크면 SDPR 로처 리되고작으면버퍼캐시를경유하는멀티블록 I/O 로처리된다. NAME VALUE DESCRIPTION ------------------------------------------- ----------- ------------------------ _direct_read_decision_statistics_driven TRUE enable direct read decision based on optimizer statistics 통계정보가존재하는테이블의경우테이블의전체블록수에큰변화가있더라도 SDPR 은도중에변경되지않는다. SDPR 의판단기준이더이상테이블의실제블록수를통해적용되지않기때문이다. 하지만, 테이블의통계정보가새로갱신되거나버퍼캐시에캐시된테이블블록수와더티블록의수에변화가생긴다면 SDPR 여부는언제든바뀔수있다. 이것은앞에서설명했듯이 SDPR 결정이옵티마이져환경이아닌런타임시점에결정되기때문이다. 테이블통계정보의 blocks 를 _small_table_threshold 값보다크게설정하여테이블을풀스캔할때항상 SDPR 로실행되게하고, 테이블의크기, 버퍼캐시상태에따라 SDPR 이어떻게적용되는지테스트를통해살펴보자. -- _small_table_threshold 를 1000 으로설정. SQL> alter session set "_small_table_threshold" = 1000 ; -- SDPR_TEST 테이블통계정보의 blocks 를 _small_table_threshold 보다큰 1100 으로 설정 SQL>EXECDBMS_STATS.SET_TABLE_STATS(ownname=>'SYS',tabname=>'SDPR_TEST',numblks=> 1100,no_invalidate=>false); 72 2013 기술백서 White Paper

-- _sdpr_pro 프로시져실행. SQL> exec sdpr_pro(p_start=>0,p_step=>1,p_stop=>1500); Block count for SDPR: 0 blocks SQL> @buffer enter the object name :sdpr_test OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS ------ ------------ ------- ------ ----- ----- ----- ------ ------- SYS SDPR_TEST xcur N N N N N 1 SQL> select statistic_name, value 2 from v$segment_statistics 3 where owner=user 4 and object_name='sdpr_test' 5 and statistic_name='physical reads direct'; STATISTIC_NAME VALUE --------------------------------------------------- ---------- physical reads direct 1 테이블통계정보의 blocks 를 small_table_threshold 보다큰 1100 으로하고, 테이블크기가 0 부터 1 블록씩증가하여 1500 블록이될때까지 sdpr_pro 프로시저를수행하면 0 블록을초과하는시점인실제테이블크기가 1 블록이상이될때 SDPR 이최초로발생한다. 통계정보의 blocks 가 _small_table_threshold 값보다크므로 1 블록을읽을때도 SDPR 로처리되는것이다. 이와반대로테이블통계정보의 blocks 가 _small_table_threshold 값보다작으면테이블이아무리크더라도 SDPR 은발생하지않는다. 따라서, 데이터의변화량이큰테이블일수록현재상태에맞게통계정보를생성하는것이중요하다. 이번에는버퍼캐시에캐시된테이블의블록수와더터블록의수가변경될경우 SDPR 은어떻게처리되는지살펴보자 -- 테이블 2000 블록생성. SQL> truncate table sdpr_test; Part 1 ORACLE 73

SQL> insert /*+ append */ into sdpr_test 2 select rpad('*', 100, '*') 3 from dual 4 connect by level <= 2000; 2000 rows created. -- 통계정보생성 SQL> exec dbms_stats.gather_table_stats(ownname=>'sys', tabname=>'sdpr_test', method_opt=>'for ALL COLUMNS SIZE 1', no_invalidate=>false); SQL> select owner,table_name,blocks,num_rows 2 from dba_tables 3 where table_name = 'SDPR_TEST'; OWNER TABLE_NAME BLOCKS NUM_ROWS -------- --------------------------- ---------- --------- SYS SDPR_TEST 2040 2000 -- 테이블캐시프로시져수행. SQL> alter session set "_small_table_threshold" = 1000 ; SQL> exec cached_sdpr_pro(p_start=>300, p_step=>1); Block count for impossible sdpr : 1990 blocks SQL> @buffer enter the object name :sdpr_test OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS ------------------- ------- ----- ------ ----- ------ ------ ------- SYS SDPR_TEST xcur N N N N N 2001 -- 더티블록생성프로시져수행. SQL> exec dirty_sdpr_pro(p_start=>50, p_step=>1, p_stop=>1500) ; Block count for impossible sdpr : 990 blocks SQL> @buffer enter the object name :sdpr_test 74 2013 기술백서 White Paper

OWNER OBJECT_NAM STATE DIRTY TEMP PING STALE DIREC BLOCKS ------------------- ------- ----- ------ ----- ------ ------ ------- SYS SDPR_TEST xcur Y N N N N 990 SYS SDPR_TEST xcur N N N N N 1011 SDPR_TEST 테이블에블록 2000 개를생성하고통계정보를수집하면통계정보가없을때와약간다른결과를보여준다. 통계정보가없을때는버퍼캐시에 SDPR_TEST 테이블블록이 50% 이상캐시되거나더티블록이 25% 이상캐시될경우 SDPR 이멈추었다. 물론버퍼캐시및테이블의크기에따라약간의차이가있지만그래도거의비슷한수준에서멈추었다. 그러나, 통계정보를참조하는경우테이블의캐시된블록수가테이블의크기와거의비슷한 100% 일때와더티블록이 50% 일때 SDPR 이멈춘다는것이다. 이것은통계정보가존재하지않을때의캐시비율과비교할때각각 2 배씩증가한것으로보인다. 이때, 통계정보의테이블 blocks 를임의로 1100 으로작게한다면어떻게될까? -- 테이블통계정보의 blocks 를 1100 으로낮춤. EXEC DBMS_STATS.SET_TABLE_STATS (ownname=>'sys', tabname=> 'SDPR_TEST', numblks=>1100, no_invalidate=>false) ; SQL> select owner,table_name,blocks,num_rows 2 from dba_tables 3 where table_name = 'SDPR_TEST'; OWNER TABLE_NAME BLOCKS NUM_ROWS -------- ----------------- --------- ---------- SYS SDPR_TEST 1100 2000 SQL> exec cached_sdpr_pro(p_start=>300, p_step=>1); Block count for impossible sdpr : 1073 blocks SQL> exec dirty_sdpr_pro(p_start=>50, p_step=>1, p_stop=>1500) ; Block count for impossible sdpr : 497 blocks 테스트결과, 실제테이블의크기는 2000 블록이상이지만통계정보의 blocks 를 1100 으로낮추었더니 1070 개의블록이캐시될때또는 497 개의더티블록이캐시될때멈추었다. 이것은테이블캐시여부를실제테이블의크기가아닌통계정보의 blocks 를기준으로결정한다는것을알수있다. 테이블통계정보의 blocks 가 1100 이므로캐시된테이블블록수가통계정보 Part 1 ORACLE 75

blocks 의 100% 에가까운 1070 블록, 더티블록이 blocks 의 50% 이 497 블록에서 SDPR 이멈추었다. 통계정보가없는테이블을풀스캔할때는커서를실행할때마다테이블의실제블록수를세그먼트헤더블록을통해조회하였다. 그러나, 통계정보를적용할때는 tab$blkcnt, tabpart$.blkcnt, ind$.leafcnt 값을커서의어딘가에저장하고실행할때마다저장된값을참조하여 SDPR 여부를결정한다. 따라서, 커서가 invalid 되거나, 통계정보를새로생성하거나, 캐시및더티블록비율이변할경우 SDPR 여부도런타임시점에언제든바뀔수있다. SQL> truncate table sdpr_test; SQL> analyze table sdpr_test delete statistics ; SQL> alter system flush shared_pool ; -- 테이블 500 블록생성. SQL> insert /*+ append */ into sdpr_test 2 select rpad('*', 100, '*') 3 from dual 4 connect by level <= 500; 500 rows created. -- sdpr_test 테이블통계정보제거. SQL> alter table sdpr_test delete statistics; -- test-1 번커서실행. SQL> alter session set "_small_table_threshold" = 1000 ; SQL> select /*+ full(sdpr_test) test-1 */ count(*) from sdpr_test; COUNT(*) ---------- 500 -- v$segment_statistics 조회. 76 2013 기술백서 White Paper

SQL> select statistic_name, value 2 from v$segment_statistics 3 where owner=user 4 and object_name='sdpr_test' 5 and statistic_name='physical reads direct'; STATISTIC_NAME VALUE ------------------------------ ---------- physical reads direct 0 -- 테이블의통계정보 blocks 를 1100 으로설정 ( no_invalidate 옵션제거 ) SQL> EXEC DBMS_STATS.SET_TABLE_STATS(ownname=>'SYS', tabname=>'sdpr_test',numblks=>1100); -- 커서 test-1 재실행. SQL> select /*+ full(sdpr_test) test-1 */ count(*) from sdpr_test; -- v$segment_statistics 조회. STATISTIC_NAME VALUE --------------------------------- --------- physical reads direct 0 -- 커서 test-2 실행. SQL> select /*+ full(sdpr_test) test-2 */ count(*) from sdpr_test; COUNT(*) ---------- 1000 -- v$segment_statistics 조회. STATISTIC_NAME VALUE --------------------------- -------- physical reads direct 2000 SDPR_TEST 테이블에 500 블록을생성한후통계정보를제거한상태에서테이블을풀스캔하면실제블록수가 _small_table_threshold 보다작으므로 SDPR 는발생하지않는다. 통계정 Part 1 ORACLE 77

보의 blocks 를 1100 으로설정하고다시동일한 SQL 을실행할경우마찬가지로 SDPR 로실행되지않는다. 통계정보 blocks 가 1100 이므로당연히 SDPR 로처리되야하지만그렇지않은이유는통계정보를 no_invalidate 로생성했기때문이다. no_invalidate 옵션을빼면기본값이 auto 모드로통계정보가생성되어일정시간이지난후에야커서가 invalid 된다. 따라서, 먼저수행된 test-1 번커서는재실행했을때도커서가아직 invalid 되지않았으므로 SDPR 로실행되지않지만, 통계정보생성후처음실행되는 test-2 번커서는통계정보 blocks 를적용하여 SDPR 로실행된다. 따라서, test-2 커서는 invalid 되지않거나, 버퍼캐시에테이블블록의캐시및더티블록상태가바뀌지않는이상계속 SDPR 로실행된다. [ 표-1] 은앞에서설명한 serial direct read 여부를결정하는판별기준을표로정리한것이다. 구분 SDPR 결정기준 SDPR 멈춤 ( 블록캐시 ) SDPR 멈춤 ( 더티블록캐시 ) 통계정보있음 통계정보 blocks ( tab$blkcnt, tabpart$.blkcnt, ind$.leafcnt) 통계정보 blocks 대비 100% 캐시 통계정보 blocks 대비 50% 캐시 통계정보없음 세그먼트실제블록수 ( 세그먼트 헤더블록조회 ) 세그먼트블록수대비 50% 캐시 세그먼트블록수대비 25% 캐시 [ 표 1] serial direct read 발생기준 결론 지금까지 SDPR(serial direct read) 이어떻게동작하는지몇가지사례를통해살펴보았다. 하지만, 경우에따라다양하고복잡하게느껴질수도있을것이다. 관련된파라미터를변경하지않고통계정보만적절히생성하여오라클이알아서수행하도록놔두면될일이지만, 뭐든지부족하거나과하면좋지않듯이불필요한 direct read 가과하게발생하거나, 반대로그렇지못할경우 SDPR 의동작방식을알고있다면위에서언급한파라미터와통계정보등을활용하여적절한튜닝도가능할것으로생각한다. 78 2013 기술백서 White Paper

참고문헌 http://afatkulin.blogspot.co.uk, http://blog.tanelpoder.com, http://docs.oracle.com Part 1 ORACLE 79