NLJ BATCH 과부분범위처리 엑셈컨설팅본부 / DB 컨설팅팀오수영 개요 오라클은새로운버전이출시될때마다한층업그레이드된기능들이추가된다. 이기능들은사용자에게편리함을제공함은물론이고, 기존의기능들이성능적으로업그레이드되어보다강력해지기도한다. 그러나때로는새롭게추가된기능으로인해,

NLJ BATCH 과부분범위처리 엑셈컨설팅본부 / DB 컨설팅팀오수영 개요 오라클은새로운버전이출시될때마다한층업그레이드된기능들이추가된다. 이기능들은사용자에게편리함을제공함은물론이고, 기존의기능들이성능적으로업그레이드되어보다강력해지기도한다. 그러나때로는새롭게추가된기능으로인해, 사용자들이큰혼란을겪기는경우도발생된다. 그 대표적인예로는 GROUP BY 가 SORT GROUP BY 처리방식에서 HASH GROUP BY 로처리가 가능가능해진사례가아닐까생각된다. HASH GROUP BY 는정렬작업이사라져, 기존의 SORT GROUP BY 에비해월등한성능을보임으로써당시획기적인기술이었다. 하지만개발당시 SORT GROUP BY 가자동적으로정렬된다는점을이용해, 별도로 ORDER BY 절을명시않았던 SQL 들은 GROUP BY 절에명시된칼럼순서대로정렬이되지않아, 의도했던결과와다른데이터를추출하게됨으로써많은개발자들을혼란에빠뜨리게만들었다. 이악몽과도같은이슈는 Oracle 11g 에서 NLJ BATCH 로인해다시한번재현되게된다. NLJ BATCH 란용어는공식적인명칭은아니며, 힌트가 NLJ_BATCHING 이란점을고려해필자가명한것임을미리밝힌다. 이문서는 NLJ_BATCH 는무엇인지, 어떠한장점이있는지, 그리고 11g 이전버전에서부분범위처리를한 SQL 들의결과가잘못추출되어겪는혼란을어떻게해결할수있는지를다루기위해작성되었다. 202 2013 기술백서 White Paper

NLJ BATCH 란무엇인가? 내용을설명하기에앞서 11g 에서새로출시된 NLJ BATCH 기능이무엇인지알아보도록하자. 11g 이하에서 Nested Loop Join 은 Disk I/O 가발생할경우, 매번 Sinlge Block I/O 를수행 하였다. 이는 Nested Loop Join 을수행할때 Disk I/O 를많이동반할경우, SQL 의수행속도 를현저하게떨어지는원인이었다. 따라서 11g 에서는이러한단점을보강하고자, DISK I/O 발생시그때마다 Single Blokc I/O 를하지않고, DISK I/O 가필요한블럭을모아서, 한번의 I/O CALL 시여러개의 Block 을읽는 NLJ_BATCH 기능을추가하게된다. 이로인해 Nested Loop Join 시발생하는 Random I/O 에대한부하를줄여응답속도는개선되었지만, 이전 Oracle Version 에비해데이터가잘못추출되는크리티컬한문제도동반하게된다. 11G 이전에는 NESTED LOOP JOIN 으로수행될경우, 선행테이블에서추출한순서를절대적으로보장받을수있었고, 이를이용해부분범위처리를수해했다. 하지만 NLJ BATCH 의등장으로인해이제더이상순서를보장받을수없게되었고, 이로인해 Oracle 11g 이전에사용하였던부분범위처리를 SQL 들이정확한데이터를추출할수없게되었다. 이문제는데이터정합성을해칠수있기에매우심각한문제였다. 필자는 11g 에서는잘못된결과를추출하게되는문제를해결하기위해많은사이트가 11g 설치후온라인프로그램이 NL BATCH 를수행못하도록파라미터 _NLJ_BATCHING_MISSES_ENABLED 파라미터를 0 으로변경하거나, 부분범위처리 SQL 문들에대해서정렬순서를보장받을수있도록 NO_NLJ_BATCHING 힌트를사용하는사례를본적이있다. 당시필자도역시위두가지방법이최선이라고생각했다. 하지만파라미터를바꾸겠다는의미는새로나온 NLJ BATCH 기능을전혀사용하지않겠다는 이야기이다. NLJ BATCH 는 Nested Loop Join 의성능을극대화시킬수있는방법인데이를 전혀사용하지않겠다는것은 구더기가무서워장을못담근다 라는말과다를바가없다. 따라서전체적으로 NLJ_BATCH 기능을사용하지않기보다는꼭사용하지말아야할대상을선 택적으로지정하는것이바람직하다. NLJ BATCH 가무엇인지또어떤상황에서동작하는지만 Part 1 ORACLE 203

잘이해한다면, 문제는생각보다쉽게해결될수있다. 따라서지금부터 NLJ BATCH 의특성및 특징에대해자세히알아보도록하자. NLJ BATCH 동작원리 결론부터말하자면 11g 에서 NLJ BATCH 때문에항상부분범위처리결과가잘못추출되는것은아니다. 만약모든데이터가 buffer cache 에상주해있다면, 실제적인물리적 I/O 가일어나지않아의도했던데이터를추출할수있다. 백문이불여일견이므로, 테스트를통해알아보도록하겠다. 테스트 Script drop table t1 drop table t2 create table t1 as select 1 as c1, mod(level, 4) as c2, level as c3, level as c4, rpad('x',1000) as dummy from dual connect by level <= 1000000; create table t2 as select mod(1000000-level, 1000000) as c1, level as c2, rpad('x',1000) as dummy from dual connect by level <= 1000000; create index t1_n1 on t1(c1, c2, c3); create index t2_n1 on t2(c1); exec dbms_stats.gather_table_stats(user, 't1'); exec dbms_stats.gather_table_stats(user, 't2'); 204 2013 기술백서 White Paper

CASE1 Buffer Cache 에모든블록이상주하는경우 먼저 buffer cache 에모든블럭이상주하는경우에대해알아보도록하겠다. 먼저블록이모 두상주하도록, 동일 SQL 문을한번미리수행한후, 재수행한결과이다. SELECT * FROM ( SELECT /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ ROWNUM AS rnum, t2.c1, t1.c4, t2.c2 FROM t1, t2 WHERE t1.c3 = t2.c1 AND t1.c1 = 1 AND t1.c2 = 0 AND ROWNUM <= 30000 ) WHERE rnum >= 28000 AND rnum <28010 RNUM C1 C4 C2 ---------- ---------- ---------- ---------- 28000 112000 112000 888000 28001 112004 112004 887996 28002 112008 112008 887992 28003 112012 112012 887988 28004 112016 112016 887984 28005 112020 112020 887980 28006 112024 112024 887976 28007 112028 112028 887972 28008 112032 112032 887968 28009 112036 112036 887964 결과를확인해보면, 모든 Block 이 buffer Cache 에상주할경우, NLJ_BATCH 로수행된다하 더라도, C1 값이순서대로정렬되어추출되는것을알수있다. Part 1 ORACLE 205

CASE2 Buffer Cache 를 Flush 하여 Disk I/O 를유발할경우 두번째테스트는 BUFFER CACHE 를 FLUSH 하여, Physical Reads 를발생하게만든경우이 다. alter system flush buffer_cache; SELECT * FROM ( SELECT /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ ROWNUM AS rnum, t2.c1, t1.c4, t2.c2 FROM t1, t2 WHERE t1.c3 = t2.c1 AND t1.c1 = 1 AND t1.c2 = 0 AND ROWNUM <= 30000 ) WHERE rnum >= 28000 AND rnum <28010 RNUM C1 C4 C2 ---------- ---------- ---------- ---------- 28000 111996 111996 888004 28001 112000 112000 888000 28002 112004 112004 887996 28003 112008 112008 887992 28004 112012 112012 887988 28005 112020 112020 887980 28006 112096 112096 887904 28007 112024 112024 887976 28008 112028 112028 887972 28009 112032 112032 887968 < 앞서 Buffer Cache 에 Block 이상주했을경우의결과 > RNUM C1 C4 C2 ---------- ---------- ---------- ---------- 206 2013 기술백서 White Paper

28000 112000 112000 888000 28001 112004 112004 887996 28002 112008 112008 887992 28003 112012 112012 887988 28004 112016 112016 887984 28005 112020 112020 887980 28006 112024 112024 887976 28007 112028 112028 887972 28008 112032 112032 887968 28009 112036 112036 887964 먼저 RNUM 28005~ 28007 까지의데이터를확인해보자 Buffer Cache 를 Flush 하여, DISK I/O 가동반되자, C1 의값은 112020 에서 112096 으로급격히증가했다가, 다시 112024 로떨어진것을알수있다. 즉더이상 C1 값에대한정렬이보장이되지않았다. 게다가앞서 Buffer Cache 에모든 Block 이상주했을때와비교해보면데이터추출결과는매우상이함을알수있다. 지금까지의결과를종합해보면, NLJ_BATCH 는 DISK I/O 발생여부에따라민감하게반응함을알수있다. 사실 NLJ BATCH 는선행테이블에 DISK I/O 가발생하게될시바로 DISK I/O 를수행하지않고, 일정량의 I/O 작업이모이면, 한번의 I/O CALL 로여러개의블록을읽어들이는기능이다. 즉 NLJ BATCH 는 Nested Loop Join 시 Single Block I/O 에의한속도저하를개선할수있지 만, I/O 를순차적으로처리하지않기때문에, Index 를이용한정렬은항상보장받을수없다. 이처럼 NLJ BATCH 를사용할경우, 기존의부분범위처리를수행했던 SQL 들이데이터가부정확해질수있는문제를가지고있으므로, NLJ BATCH 를사용할경우, 수행속도측면에서큰장점이있어야할것이다. 그렇다면 NLJ BATCH 를사용할경우얼마나성능이좋아지는지테스트를통해알아보도록하자. 지금부터는데이터의추출결과와는상관없이 NLJ_BATCH 의효율성에대해테스트를해보도 록하겠다. Part 1 ORACLE 207

CASE3 NLJ_BATCH 를사용하지않은경우 alter system flush buffer_cache select * from ( select /*+ no_nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ rownum as rnum, t2.c1, t1.c4, t2.c2 from t1, t2 where t1.c3 = t2.c1 and t1.c1 = 1 and t1.c2 = 0 and rownum <= 30000 ) where rnum >= 28000 and rnum <28010 평균수행시간 : 3.06 초 CASE4 NLJ_BATCH 를사용한경우 alter system flush buffer_cache select * from ( select /*+ nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ rownum as rnum, t2.c1, t1.c4, t2.c2 from t1, t2 where t1.c3 = t2.c1 and t1.c1 = 1 and t1.c2 = 0 208 2013 기술백서 White Paper

and rownum <= 30000 ) where rnum >= 28000 and rnum <28010 평균수행시간 : 1.93 초 아래의표는 NLJ BATCH 를사용할경우와사용하지않을경우를약 10 회씩수행한결과이다. 시도 NLJ BATCH(SEC) NO NLJ BATCH(SEC) 1 차 1.9 2.75 2 차 1.97 2.7 3 차 1.89 2.87 4 차 1.98 3.14 5 차 1.89 3.12 6 차 1.9 3.14 7 차 1.9 3.21 8 차 1.93 3.18 9 차 1.92 3.21 10 차 1.97 3.31 평균 1.925 3.063 위표를보면 Buffer Cache 를 Flush 한상태에서약 30,000 건의데이터를 Nested Loop Join 을수행할경우, NLJ BATCH 를사용한경우가그렇지않은경우보다약 1 초가량속도가개선 되었다. 만약대용량의데이터를 Nested Loop Join 으로수행하는경우라면, 그효과는더욱클 것이다. 이것이 NLJ BATCH 가가진매력이며, 필자가이글을쓰게된이유이다. 부분범위처리 시잘못된데이터가나오는것을우려해전사적으로사용을금하지말자는것이다. NLJ BATCH 모니터링방법 한가지주의할점이있다. NLJ_BATCH 를모니터링하려 TRACE 를수행하거나, XPLAN 으로 모니터링하기위해 GATHER_STATISTICS 힌트를수행하면 NLJ_BATCH 기능이사용되지않 Part 1 ORACLE 209

는다는점이다. 그렇다면어떻게모니터링이가능할까. 바로 v$sesstat, v$session_event 뷰를 통해모니터링이가능하다. NLJ_BATCH 가기능이수행될경우, V$SESSTAT 의 Batched IO (bound) vector count 지표 가증가하며, 배치 I/O 가일어날경우, db file parallel read 대기이벤트가발생하므로 V$SESSION_EVENT 를모니터링하면된다. 그럼직접모니터링을수행해보도록하겠다. CASE1 NLJ_BATCH 를사용한경우 alter system flush buffer_cache select * from ( select /*+ nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ rownum as rnum, t2.c1, t1.c4, t2.c2 from t1, t2 where t1.c3 = t2.c1 and t1.c1 = 1 and t1.c2 = 0 and rownum <= 30000 ) where rnum >= 28000 and rnum <28010 select * from v$sesstat a, v$statname b where sid =133 and a.statistic#=b.statistic# and a.statistic#=249 order by 3 desc SID STATISTIC# VALUE STATISTIC# NAME STAT_ID ---- ---------- ---------- ---------- ------------------------------------- ----------- 70 249 1217 249 Batched IO (bound) vector count 2669900039 210 2013 기술백서 White Paper

모니터링결과를보면 Batched IO (bound) Vector count 지표가증가한것으로보아 NL BATCH 가사용된것을알수있다. 이때 V$SESSION_EVENT 를조회해보면아래와같이 db file parallel read 가발생한다. select sid, event, total_waits, time_waited_micro, wait_class from v$session_event where sid ='133' SID EVENT TOTAL_WAITS TIME_WAITED_MICRO WAIT_CLASS ---------- --------------------------- ----------- ----------------- ------------------- 70 Disk file operations I/O 3 1641 User I/O 70 log file sync 1 330 Commit 70 db file sequential read 2246 348896 User I/O 70 db file scattered read 3424 1427445 User I/O 70 db file parallel read 6 3383 User I/O 70 SQL*Net message to client 39 33 Network 70 SQL*Net message from client 38 32065340 Idle 70 events in waitclass Other 6 24545 Other CASE2 NLJ_BATCH 를사용하지않은경우 alter system flush buffer_cache select * from ( select /*+ no_nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ rownum as rnum, t2.c1, t1.c4, t2.c2 from t1, t2 where t1.c3 = t2.c1 and t1.c1 = 1 and t1.c2 = 0 and rownum <= 30000 ) where rnum >= 28000 and rnum <28010 Part 1 ORACLE 211

select * from v$sesstat a, v$statname b where sid =133 and a.statistic#=b.statistic# and a.statistic#=249 order by 3 desc SID STATISTIC# VALUE STATISTIC# NAME STAT_ID -------- --------- -------- ---------- ----------------------------------- ---------- 191 249 0 249 Batched IO (bound) vector count 2669900039 Value 값이 0 이므로 NLJ BATCH 가사용되지않았음을알수있다. NLJ BATCH 와부분범위처리 SQL 그리고해결책 이제 11g 이후 NESTED LOOP JOIN 의특성을이용해부분범위처리를한 SQL 들에대해정상 적으로데이터를가져오기위한해결책에대해논의해보도록하겠다. 앞서서론에서도이야기했듯이 _NLJ_BATCHING_MISSES_ENABLED 파미터를조절하거나 NO_NLJ_BATCHING 힌트를사용하면데이터가잘못추출되는문제는해결할수있다. 두가지방법중파라미터를변경은전체적으로 NLJ_BATCH 기능을사용하지못함으로써, 득보 다실이많을수도있을것이다. 따라서부분범위처리를수행하는 SQL 들을찾아 NO_NLJ_BATCHING 힌트를추가하는것이가장좋은해결방법이라생각될것이다. 하지만 NO_NLJ_BATCHING 힌트를사용한다하더라도, INDEX 가 UNUSEBLE 상태가된다 거나조건이바뀌어더이상 INDEX 를이용한정렬이불가능하다면, 데이터가잘못추출되는현 상은여전히발생할것이다. 가장좋은방법은 SQL 작성이복잡해지겠지만 ORDER BY 를명시하는것이다. 아래와같이 INLINE VIEW 내에 ORDER BY 절이있더라도, INDEX 칼럼의순서와 ORER BY 순서만일치한다면오라클은부분범위를처리한다. 하지만 INDEX 가 UNUSEBLE 되거나, SQL 이변경되어 ORDER BY 절과 INDEX 가부합해진다면그때정렬을수행한다. 따라서어떠한상황이변경되더라도항상정확한데이터를가져올수있다. 게다가 ORDER BY 가명시되어있다면그정렬순서를보장하기위해 NLJ_BATCH 기능이활성화되지않음으로써, 정확한데이터를가져올수있다. 212 2013 기술백서 White Paper

11g 에서부분범위처리시지켜야할 SQL 작성법 SELECT * FROM ( SELECT ROWNUM rnum, a.* FROM ( SELECT /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ t2.c1, t1.c4, t2.c2 FROM t1, t2 WHERE t1.c3 = t2.c1 AND t1.c1 = 1 AND t1.c2 = 0 ORDER BY c3 ASC ) A WHERE ROWNUM < 30000 ) WHERE rnum >= 28000 AND rnum <28010 RNUM C1 C4 C2 ---------- ---------- ---------- ---------- 28000 112000 112000 888000 28001 112004 112004 887996 28002 112008 112008 887992 28003 112012 112012 887988 28004 112016 112016 887984 28005 112020 112020 887980 28006 112024 112024 887976 28007 112028 112028 887972 28008 112032 112032 887968 28009 112036 112036 887964 실행계획 SELECT STATEMENT ALL_ROWS-Cost : 77372 VIEW SCOTT.(1) ("RNUM"<28010 AND "RNUM">=28000) COUNT STOPKEY (ROWNUM<30000) VIEW SCOTT.(2) Part 1 ORACLE 213

NESTED LOOPS NESTED LOOPS TABLE ACCESS BY INDEX ROWID SCOTT.T1(3) Analyzed : 20130915 INDEX RANGE SCAN SCOTT.T1_N1 (C1,C2,C3) Analyzed : 20130915 ("T1"."C1"=1 AND "T1"."C2"=0) INDEX RANGE SCAN SCOTT.T2_N1 (C1) Analyzed : 20130915 ("T1"."C3"="T2"."C1") TABLE ACCESS BY INDEX ROWID SCOTT.T2(4) Analyzed : 20130915 위와같이 ORDER BY 절까지명시하여부분범위를처리하게되면, NO_NLJ_BATCHING 힌트가없지만, 정확한데이터를추출할수있게된다. 이는오라클이 ORDER BY 절이존재함으로 NL BATCH 를수행할경우결과값이달라질수있음을인지하고 VECTOR I/O 를수행하지않고, 매번 SINGLE BLOCK I/O 를수행했음을예상할수있다. 11g 부터는부분범위처리를할때 ORDER BY 절을명시하는것이얼마나중요한지알수있다. 많은서적들이부분범위처리를서명할때 ORDER BY 절이생략된형태를가지고설명하고있고, 또이를그대로사용하여, Index 가 Unuseble 되거나 11g 로업그레이드된후데이터가잘못추출되어큰혼란을겪고있다. 오라클에서의정렬을 100% 보장하기위한방법은 Order by 절을사용하는것이외에는없다. 따라서 SQL 이길어지고좀복잡해지더라도, 필자가언급한대로 SQL 을작성한다면, 언제든정확한데이터를추출할수있음은물론이고, ORDER BY 가생략된전체범위처리를수행하는 NESTED LOOP JOIN 들은 NLJ_BATCH 기능을적극활용함으로써성능이더욱향상될것이라고생각된다. 결론 지금까지 NLJ BATC 에대해알아보았다. 11g 에서부분범위처리시데이터가잘못추출된다는 이슈가있었지만, 이는 SQL 작성이잘못되어있었기때문에발생한문제이다. 부분범위처리를유도하고싶다면, 이제단순히 INDEX 명과 ROWNUM 만을사용하지말고, ORDER BY 를정확히명시한후부분범위처리를유도해야할것이다. 오라클옵티마이저는 ORDER BY 를만나면정확히정렬된데이터를추출하기위해 NLJ BATCH 를사용하지않을것이며, 반대로 ORDER BY 절이없는 Nested Loop Join 은정렬을보장하지않아도된다고생각 214 2013 기술백서 White Paper

해 NLJ BATCH 를이용해응답속도를높일것이다. 즉근본적인문제는개발자와오라클옵티 마이저간의해석차이로인해잘못된결과를가져오게된것이라볼수있다. 앞으로는부분범 위처리를할때반드시 Order by 절을명시하고사용하길바란다. Part 1 ORACLE 215