Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUERY 을실행하게된다면 BLOCK I/O 가많이발생하게된다. 이런이유로 QUERY 의성능은좋지못할것이다. 코드테이블과같이적은데이터를동시에많은사용자들이조회한다면 BUFFER CACHE 에서원하는 BLOCK 을찾아결과를전송하겠지만동시에많은사용자들이사용하기때문에 HOT BLOCK 이되고 ORACLE 대기이벤트가발생하여빠른성능을기대하기가어렵다. I/O 에따라 QUERY 의성능이좌우되기때문에 I/O 을최소한으로발생시키고 QUERY 를실 행한다면성능은향상될것이다. ORACLE 11G 에서 I/O 의개선을위해선보인기능이 RESULT CACHE 기능이다. RESULT CACHE 는 SHARED POOL 에 RESULT CACHE MEMORY 로불리는영역에 SQL 및 PL/SQL FUNCTION 의결과를저장하고, 이후동일 QUERY 조회시 RESULT CACHE 에저장되어있는 QUERY 결과값을그대로활용하는기능이다. 반복적으로동일한결과값을조회하는 QUERY 에서사용되며많은데이터를처리하여적은결과건수를보고자할때 BLOCK I/O 을발생시키지않고결과를전송하기때문에조회성능에있어서매우빠르다. 이제부터 RESULT CACHE QUERY 의동작원리와설정방법을알아본후에기능활용에대해 알아보도록하자. 24 2013 기술백서 White Paper

RESULT CACHE QUERY 동작원리 일반적으로 QUERY 가수행되어 BUFFER CACHE 에원하는블록이존재한다면요청한세션에게블록을전송한다. 만약 BUFFER CACHE 영역에존재하지않는다면 DISK I/O 발생후 BUFFER CACHE 에 BLOCK 을올려놓고요청한세션에게결과값을전송한다. RESULT CACHE 기능은일반적인 QUERY 동작방식과같지만그결과값을 CACHING 한다는 점이다르다. RESULT CACHE QUERY 최초실행. 1. RESULT CACHE QUERY 가실행되면가장먼저 SHARED POOL 영역의 RESULT CACHE 메모리에서 OBJECT 의 CACHING 여부를확인한다. 2. OBJECT 가 CACHING 되어있지않다면 BUFFER CACHE 에서 BLOCK 을찾는다. 3. BUFFER CACHE 에원하는 BLOCK 이존재하지않으면 DISK 에서 BLOCK 을읽어 BUFFER CACHE 에전송한다. 4. 그결과값을 QUERY 한세션에전송한다. 5. 마지막으로 RESULT CACHE 영역에 QUERY 결과값을저장한다. Part 1 ORACLE 25

RESULT CACHE QUERY 반복실행. 1. RESULT CACHE QUERY 가실행되면가장먼저 SHARED POOL 영역의 RESULT CACHE 메모리에서 OBJECT 의 CACHING 여부를확인한다. 2. CACHING 된정보가존재하면 I/O 을발생시키지않고 CACHING 된결과값을요청한세션에게전송한다. RESULT CACHE 기능설정및해지 RESULT CACHE 기능설정하기 PARAMETER 설정으로 RESULT CACHE 기능을사용할수있다. 설정에필요한주요 PARAMETER 들을살펴보도록하겠다. RESULT_CACHE_MODE RESULT_CACHE_MODE PARAMETER 값에따라 2 가지 MODE 의설정이가능한데 MANUAL 과 FORCE 이다. MANUAL 일경우에는 SQL 마다 /*+ RESULT_CACHE */ HINT 을주어 26 2013 기술백서 White Paper

RESULT CACHE 기능을사용할수있고 FORCE 일경우에는모든 SQL 이 RESULT CACHE 의 대상이된다. 단 /*+ NO_ RESULT_CACHE */ HINT 을제외된다. RESULT_CACHE_MODE PARAMETER 의 DEFAULT 값은 MANUAL 이다. RESULT_CACHE_MAX_SIZE RESULT CACHE 기능을사용하려면 RESULT_CACHE_MAX_SIZE 값도명시적으로지정되어있어야한다. 이 PARAMETER 의최대값은 SHARED POOL 의최대 75% 까지만설정할수있다. RESULT_CACHE_MAX_RESULT 하나의 SQL 에결과집합에대한전체캐시영역에서최대할당할수있는메모리크기이며 DEFAULT 값은 5% 이다. RESULT_CACHE_REMOTE_EXPIRATION REMOTE DATABASE OBJECT 의보관주기를시간 ( 분 ) 지정이가능하며 DEFAULT 값은 0 이 다. The default is 0 which means that resultsets dependant on remote OBJECT RESULT CACHE 기능해지하기 RESULT CACHE 기능을해지하는방법에는특정 INSTANCE 가 RESULT CACHE 기능을사용할수없게설정하는방법과 RESULT CACHE 기능은사용이가능하지만 CACHE 에서 OBJECT 을해지하는 2 가지방법이있다. INSTANCE 가 RESULT CACHE 기능을사용하지않으려면 RESULT_CACHE_MODE 값을 0 의값으로설정하고, INSTANCE 을재기동하면된다. RESULT CACHE 기능은사용가능하지만 CACHING 되어있는 OBJECT 을해지하는방법은 RESULT CACHE 패키지를이용해해지할수있다. CACHE 에서해지하는방법은예제를통해알아보도록하겠다. Part 1 ORACLE 27

CACHING 된 OBJECT 해지하는방법 RESULT CACHE 기능을사용하다가 CACHING 된여러 OBJECT 중에서특정 OBJECT 만 CACHE 에서해지하려면 DBMS_RESULT_CACHE.INVALIDATE 패키지를사용하여해지할수있다. ID, TYPE, STATUS, BUCKET_NO, HASH,NAME FROM V$RESULT_CACHE_OBJECT; [ 결과값 ] ID TYPE STATUS BUCKET_NO HASH NAME ----- ---- ------- ---------- --------- ---------------------------- 0 Dependency Published 660 319061 DBAADM.TB_RC_TEST_YYYYMMDD 1 RESULT Published 2011 159411 /*+ RESULT_CACHE */ SUBSTR(SDATE,1,6) SDATE, PROD, SUM(AMT1) AMT1, SUM(amt2) AMT2, SUM(AMT3) AMT3 FROM TB_RC_TEST_YYYYMMDD PROD [ 패키지실행 ] EXEC DBMS_RESULT_CACHE.INVALIDATE('DBAADM',' TB_RC_TEST_YYYYMMDD') ID, TYPE, STATUS, BUCKET_NO, HASH,NAME FROM V$RESULT_CACHE_OBJECT; [ 결과값 ] 28 2013 기술백서 White Paper

no rows selected. [ 패키지실행 ] EXEC DBMS_RESULT_CACHE.FLUSH ID, TYPE, STATUS, BUCKET_NO, HASH,NAME FROM V$RESULT_CACHE_OBJECT; [ 결과값 ] no rows selected. RESULT CACHE 에등록된모든 OBJECT 들을 DBMS_RESULT_CACHE.FLUSH 패키지를통해일괄해지할수있다. /*+ RESULT_CACHE */ 1 FROM DUAL; /*+ RESULT_CACHE */ 2 FROM DUAL; /*+ RESULT_CACHE */ 3 FROM DUAL; ID, TYPE, STATUS, BUCKET_NO, HASH,NAME FROM V$RESULT_CACHE_OBJECT; [ 결과값 ] ID TYPE STATUS BUCKET_NO HASH NAME -- ---------- --------- -------- -------- ------------------------------------- 2 RESULT Published 3870 83370270 /*+ RESULT_CACHE */ 3 FROM DUAL 1 RESULT Published 2222 93590190 /*+ RESULT_CACHE */ 2 FROM DUAL 0 RESULT Published 3414 7988310 /*+ RESULT_CACHE */ 1 FROM DUAL Part 1 ORACLE 29

[ 패키지실행 ] EXEC DBMS_RESULT_CACHE.FLUSH ID, TYPE, STATUS, BUCKET_NO, HASH,NAME FROM V$RESULT_CACHE_OBJECT; [ 결과값 ] no rows selected RESULT CACHE 기능활용하기 과거고객사의예를들어보면온라인시간에상품들에대해실적을보는업무가있었다. 실적테이블은 1 년동안보관되며 NON PARTITION 된테이블이 12 개존재하고있다. 특정월에해당하는실적을보려면해당테이블에모든데이터를읽어야만한다. 그렇게되면 I/O 가많이발생해빠른성능을기대하기어렵다. 그래서매일야간배치가실행되어전일기준으로실적집계테이블을갱신하고있었다. 많은개선의효과가있지만여전히읽어야할데이터가많기때문에많은블록 I/O 가발생하고있었다. 여기서각달에해당하는실적조회를 RESULT CACHE 기능을사용한다면많은성능개선이있을것이다. 특정월에해당하는실적테스트데이터를생성하여 RESULT CACHE 기능을활용해보도록하자. 테이블생성스크립트 [DDL 실행 ] CREATE TABLE TB_RC_TEST_YYYYMMDD AS TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,31)),'09')) 30 2013 기술백서 White Paper

TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(0,23)),'09')) TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(00,59)),'09')) SDATE, DBMS_RANDOM.STRING('U',2) PROD, ROUND(DBMS_RANDOM.VALUE(100,100000)) AMT1, ROUND(DBMS_RANDOM.VALUE(10,10000)) AMT2, ROUND(DBMS_RANDOM.VALUE(1000,100)) AMT3 FROM DUAL CONNECT BY LEVEL <= 10000000 ; RESULT CACHE 기능을사용하지않은경우 (X) SUBSTR(SDATE,1,6) SDATE, PROD, SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3 FROM TB_RC_TEST_YYYYMMDD PROD [ 실행계획 ] call count cpu elapsed disk QUERY current rows Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 47 15.62 15.80 46944 46948 0 676 total 49 15.62 15.80 46944 46948 0 676 Rows (1st) Row Source OPERATION ---------- --------------------------------------------------- 676 SORT GROUP BY (cr=46948 pr=46944 pw=0 time=15803245) 10000000 TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=46948 pr=46944 pw=0 time=3144528) 실행결과를보면 38,670 개의 BLOCK I/O 가발생하였다. RESULT CACHE 기능을이용하여테스트를다시수행해보자. Part 1 ORACLE 31

RESULT CACHE 기능을사용한경우 (O) ( 최초실행 ) /*+ RESULT_CACHE */ SUBSTR(SDATE,1,6) SDATE, PROD, SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3 FROM TB_RC_TEST_YYYYMMDD PROD [ 실행계획 ] call count cpu elapsed disk QUERY current rows Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 47 15.62 15.80 46944 46948 0 676 total 49 15.62 15.80 46944 46948 0 676 Rows (1st) Row Source OPERATION ---------- --------------------------------------------------- 676 SORT GROUP BY (cr=46948 pr=46944 pw=0 time=15803245) 10000000 TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=46948 pr=46944 pw=0 time=3144528) 수행한결과여전히 38,670 개의 BLOCK I/O 가발생하고있다. 최초 QUERY 실행시 CACHING 된영역에 OBJECT 가존재하지않았기때문이다. 이제 VIEW 를보면 CACHE 영역 에등록되어있는것을볼수있다. CACHING 된 OBJECT 보기 ID, TYPE, STATUS, BUCKET_NO, HASH, 32 2013 기술백서 White Paper

FROM NAME V$RESULT_CACHE_OBJECT; [ 결과값 ] ID TYPE STATUS BUCKET_NO HASH NAME ---- --------------- -------- --------- ---------- --------- ------------------- 0 Dependency Published 660 319061 DBAADM.TB_RC_TEST_YYYYMMDD 1 RESULT Published 2011 159411 /*+ RESULT_CACHE */ 이제 CACHE 등록이되어있는것을확인했으니다시한번수행해보자. RESULT CACHE 기능을사용한경우 (O) ( 반복실행 )) /*+ RESULT_CACHE */ SUBSTR(SDATE,1,6) SDATE, PROD, SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3 FROM TB_RC_TEST_YYYYMMDD PROD [ 실행계획 ] call count cpu elapsed disk QUERY current rows Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 47 0.00 0.00 0 0 0 676 total 49 0.00 0.00 0 0 0 676 Rows (1st) Row Source OPERATION ---------- --------------------------------------------------- 676 RESULT CACHE gnxqgpxppdavj80b6rddg4qsqj (cr=0 pr=0 pw=0 time=20 us) 0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 ) 0 TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=0 pr=0 pw=0 time=0 ) Part 1 ORACLE 33

실행계획을보면 I/O 가전혀발생하지않았다. I/O 가전혀발생되지않아 SQL 을반복수행하더라도성능을향상시켜준다. 여기서 RESULT CACHE 라는 OPERATION 을볼수있다. 결과값이 CACHING 된것이다. INLINE VIEW 나 WITH 문과같은쿼리에서도 RESULT CACHE 기능을사용할수있다. 독립적으로 QUERY 블록에 CACHING 이가능하기때문이다. 테스트를통해알아보도록하자. 테이블생성스크립트 [DDL 실행 ] CREATE TABLE TB_RC_TEST_SYSDATE AS TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,31)),'09')) TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(0,23)),'09')) TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(00,59)),'09')) SDATE, DBMS_RANDOM.STRING('U',2) PROD, ROUND(DBMS_RANDOM.VALUE(100,100000)) AMT1, ROUND(DBMS_RANDOM.VALUE(10,10000)) AMT2, ROUND(DBMS_RANDOM.VALUE(1000,100)) AMT3 FROM DUAL CONNECT BY LEVEL <= 300000 ; INLINE VIEW 와 WITH 문사용예제 [INLINE VIEW 사용 QUERY] SDATE, SUM(SUM_AMT) FROM ( SDATE, NVL(AMT1,0) + NVL(AMT2,0) + NVL(AMT3,0) SUM_AMT FROM ( /*+ RESULT_CACHE */ SUBSTR(SDATE,1,6) SDATE, PROD, SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3 34 2013 기술백서 White Paper

FROM TB_RC_TEST_YYYYMMDD PROD ) UNION ALL SUBSTR(SDATE,1,6) SDATE, SUM(AMT1) + SUM(AMT2) + SUM(AMT3) SUM_AMT FROM TB_RC_TEST_SYSDATE WHERE SDATE < :SDATE PROD ) GROUP BY SDATE; [WITH 문사용 QUERY] WITH W_TB_RC_TEST_YYYYMMDD AS ( SDATE, NVL(AMT1,0) + NVL(AMT2,0) + NVL(AMT3,0) SUM_AMT FROM ( /*+ RESULT_CACHE */ SUBSTR(SDATE,1,6) SDATE, PROD, SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3 FROM TB_RC_TEST_YYYYMMDD PROD )) SDATE, SUM(SUM_AMT) FROM ( SDATE, SUM_AMT FROM W_TB_RC_TEST_YYYYMMDD UNION ALL SUBSTR(SDATE,1,6) SDATE, SUM(AMT1) + SUM(AMT2) + SUM(AMT3) SUM_AMT FROM TB_RC_TEST_SYSDATE WHERE SDATE < :SDATE PROD Part 1 ORACLE 35

) GROUP BY SDATE; [ 실행계획 ] call count cpu elapsed disk QUERY current rows Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.18 0.18 0 1413 0 2 total 4 0.18 0.19 0 1413 0 2 Rows (1st) Row Source OPERATION ---------- --------------------------------------------------- 2 SORT GROUP BY (cr=1413 pr=0 pw=0 time=188846 ) 1352 VIEW (cr=1413 pr=0 pw=0 time=2355 ) 1352 UNION-ALL (cr=1413 pr=0 pw=0 time=2230 us) 676 VIEW (cr=0 pr=0 pw=0 time=578 ) 676 RESULT CACHE gnxqgpxppdavj80b6rddg4qsqj (cr=0 pr=0 pw=0 time=126 us) 0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 ) 0 TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=0 pr=0 pw=0 time=0 ) 676 SORT GROUP BY (cr=1413 pr=0 pw=0 time=187432 ) 104850 TABLE ACCESS FULL TB_RC_TEST_SYSDATE (cr=1413 pr=0 pw=0 time=57449 ) RESULT CACHE 사용시고려사항 DML 사용시고려사항 CACHING 된 OBJECT 에 DML( 변경사항 ) 이발생되면변경된시점에서 RESULT CACHE 기능은무효화된다. 그이유는 CACHING 된 OBJECT 가변경된다면 QUERY 의결과값에대한정합성을보장할수없기때문이다. 따라서 DML 이많이발생하는 OBJECT 는 RESULT CACHE 기능을사용하지않는것이좋다. BIND 사용시고려사항 BIND 변수의변경이많은 QUERY 에대해서도비효율이발생된다. 아래테스트결과를보면 BIND 변수값에따라독립적으로 CACHING 되는것을볼수있다. 그렇기때문에 BIND 변수가 36 2013 기술백서 White Paper

많아지면특정 QUERY 가 CACHE 의영역을모두사용할것이고 CACHE 하고자하는각기다 른쿼리들에의해 CACHE 의등록과해지가빈번하게발생되어 CACHE 의효율은떨어지게된 다. 같은 QUERY 에서 BIND 변수값이다양한경우 VAR NUM NUMBER; EXEC :NUM := 1; /*+ RESULT_CACHE */ 'CACHING Count' FROM DUAL WHERE 1 = :NUM; EXEC :NUM := 2; /*+ RESULT_CACHE */ 'CACHING Count' FROM DUAL WHERE 1 = :NUM; EXEC :NUM := 3; /*+ RESULT_CACHE */ 'CACHING Count' FROM DUAL WHERE 1 = :NUM; ID, TYPE, STATUS, BUCKET_NO, HASH,NAME FROM V$RESULT_CACHE_OBJECT; [ 결과값 ] ID TYPE STATUS BUCKET_NO HASH NAME - ---- --------- --------- ----------- -------------------------------------------- 3 RESULT Published 1882 2742746 /*+ RESULT_CACHE */ 'CACHING Count' FROM DUAL WHERE 1 = :A 2 RESULT Published 1714 7906098 /*+ RESULT_CACHE */ 'CACHING Count' FROM DUAL WHERE 1 = :A 1 RESULT Published 3880 95977768 /*+ RESULT_CACHE */ 'CACHING Count' FROM DUAL WHERE 1 = :A 0 RESULT Published 3157 98148181 /*+ RESULT_CACHE */ 'CACHING Count' FROM DUAL WHERE 1 = :A Part 1 ORACLE 37

특정 OBJECT 사용시고려사항 아래내용은쿼리결과집합을 CACHING 하지못하는경우이다. 임시테이블또는 DICTIONARY OBJECT (DBA_*, V$_*, GV$_* 등 ) 참조시 시퀀스 CURRVAL 및 NEXTVAL COLUMN 호출시 QUERY 에서 SQL 함수를사용할경우 - CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, SYS_GUID, SYSDATE, SYSTIMESTAMP 등 결론 기존 QUERY 들은 I/O 가발생해야만결과집합을알수있다. 많지않은 BLOCK 이라도많은세션들이동시에같은 BLOCK 을사용한다면 WAIT EVENT 가발생한다. 이는좋은성능을기대하기어렵지만 RESULT CACHE 기능을사용하게되면 I/O 를전혀발생하지않는다는부분에있어상당한성능개선의효과가있다. 하지만그기능의장점과단점을잘알고사용해야만시스템을안정적으로사용할수있을것이다. 또한운영하고있는시스템을살펴보면 RESULT CACHE 기능을사용할수있는부분이있을것이다. 이기능을활용해서 I/O 성능을더욱개선시킨다면더욱안정화되고최적화된시스템으로발전하게될것이다. 38 2013 기술백서 White Paper