Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터를사용자에게전송하게되며 Parsing 단계에서실행계획이생성된다. Bind 변수를사용하는 SQL 에서실행계획이생성될때실제로사용되는변수값을모르기때문에성능에문제를발생시킬수있는실행계획이세워질수있다. 이런 Bind 변수의단점을보완하기위해 10g 에서 Bind Peeking 이라는기능이소개되었는데 Bind 변수를사용하는 SQL 에서최초실행되는시점의실제 Bind 값이무엇이냐에따라실행계획이결정되는것을말한다. 이렇게되면실제사용되는 Bind 변수의값을이용해실행계획을세울수있어보다성능에유리한실행계획을세울수있을것이라생각되지만 Bind Peeking 또한한계를가지고있다. 최초에 Bind 변수값에따라서실행계획이고정되기때문에최초값이 SQL 의성능을결정하게되어 Table Full Scan 으로실행계획이생성되었다면이 SQL 은새로운실행계획이생성되기전까지 Table Full Scan 이발생하게된다. 이러한문제로이기능은전혀사용하지못하는기능이되어버렸다. 결국 Bind 변수의단점을보완하기위해 Bind Peeking 이라는기능이소개되었지만여전히이기능도한계를가지고있다. 진화된 Cursor Sharing 필요 이러한 Bind Peeking 기능의한계를보완하기위해서 Oracle 11g 에서는 Adaptive Cursor Sharing ( 적응적커서공유 ) 이라는기능이소개되었는데 Adaptive Cursor Sharing 뜻은상황에맞게유연하게 Cursor Share 하겠다는의미이다. 기능을사용하게되면여러개의실행계 124 2013 기술백서 White Paper

획을관리할수있는데최초입력된값에의해실행계획이결정되더라고이후에새로운 Bind 변수값이사용되면이에맞게적절한실행계획을선택하여 SQL 을실행하게된다. 즉 Bind Peeking 의한계를보완한것이다. Cursor Sharing 동작원리 이기능이동작하려면기본적으로조건 Column 에히스토그램이생성되어있어야한다. 그상태에서 Bind 변수가포함된쿼리를실행하였을때, 옵티마이저가히스토그램분포도에따라실행계획이크게달라질것이라판단한다면, 해당커서를 Bind Sensitive 커서라는상태로두게된다. 이러한 Bind Sensitive 커서에해당하는구문이재차실행되었을때에, 특정변수값에서성능이크게저하되었다고판단되면해당커서를 Bind Aware 커서상태로바꾼다. 이상태가되면, 종전의성능저하된다판단된커서에대해서는기존의실행계획을사용하지않고, child 커서를생성하고새로운실행계획을생성하여저장하게되고새로운실행계획을생성해낸뒤에는선택도가비슷한것으로판단되는 Cursor 는같은실행계획을사용하게된다. Adaptive Cursor Sharing 설정방법 Adaptive Cursor Sharing 기능을설정하는방법을알아보자. Parameter 을이용한 Adaptive Cursor Sharing 설정 Adaptive Cursor Sharing 기능을사용할지의여부를지정하며기본값은 True 이다. Alter [ System Session ] Set "_optimizer_adaptive_cursor_sharing" = TRUE; 통계정보수집을하여 Histogram 을사용할수있어야함. Adaptive Cursor Sharing 기능을사용하기위해서는입력되는변수들의분포도를알아야하 는데그정보는 Histogram 을통해알수있다. Part 1 ORACLE 125

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, TABLE, ESTIMATE_PERCENT=>100, METHOD_OPT=>'FOR COLUMNS SIZE '); Adaptive Cursor Sharing 고려사항 Adaptive Cursor Sharing 을사용하지않을경우에는쿼리가실행될때마다캐시에저장된실행계획을실행하게되지만 Adaptive Cursor Sharing 을사용하게되면 Bind 변수의값이변경되었을때실행계획을재작성해야하는지의여부를판단하게된다. 이기능을모든 SQL 이사용하게된다면시스템에많은과부하가발생하게된다. 따라서모든 SQL 에대해서이기능을사용하기보다는 Parameter 을 False 적용한후에이기능이꼭필요한 SQL 에서세션단위로 Parameter 을 True 로변경하여사용하는것이좋다. Adaptive Cursor Sharing 활용방안 조회하는 SQL 에서조회조건의 Column 의중복값의분포가고르지못하여실행계획을분리해야하는경우가있는데같은 SQL 에서입력값에따라 Index Scan 이나 Full Table Scan 을배타적으로실행해야성능에유리한경우가있다. 이러한경우 Adaptive Cursor Sharing 기능을사용하게되면입력값에따라최적에실행계획을생성하여 SQL 의성능을향상시킬수있다. 테스트데이터생성 ACCT_NO 값이각각 10 만건과 10 건으로데이터를생성해보았다. CREATE TABLE TB_DPS_TRSC_BASE AS SELECT 1 ACCT_NO, 'BANK' CUST_NO, ROUND(DBMS_RANDOM.VALUE(10,100)) AMT FROM DUAL CONNECT BY LEVEL <= 100000; INSERT INTO TB_DPS_TRSC_BASE SELECT 99,'NAME',ROUND(DBMS_RANDOM.VALUE(10,100) FROM ALL_OBJECTS WHERE ROWNUM <= 10; COMMIT; 인덱스생성 CREATE INDEX TB_DPS_TRSC_BASE_1IX ON TB_DPS_TRSC_BASE (ACCT_NO) ; Parameter 설정 126 2013 기술백서 White Paper

Alter Session Set "_optimizer_adaptive_cursor_sharing" = TRUE ; 통계정보를생성하지않고 10 만건의경우와 10 건의경우를테스트 SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM FROM DBA_TAB_COLS WHERE TABLE_NAME='TB_DPS_TRSC_BASE'; TABLE_NAME COLUMN_NAME HISTOGRAM ------------------------- ------------------------- --------------- TB_DPS_TRSC_BASE ACCT_NO NONE TB_DPS_TRSC_BASE CUST_NO NONE TB_DPS_TRSC_BASE AMT NONE -- 테스트를위해 Shared pool 을 Flush 한다. ALTER SYSTEM FLUSH SHARED_POOL; EXEC :A1 := 99; 0 SELECT STATEMENT 1 10 00:00:00.01 3 1 TABLE ACCESS BY INDEX ROWID TB_DPS_TRSC_BASE 1 33467 10 00:00:00.01 3 * 2 INDEX RANGE SCAN IND_01 1 33467 10 00:00:00.01 2 -- SQL 의실행계획이변경될수없어 "N" 값으로표시됨. 0 1 3 N N EXEC :A1 := 1; Part 1 ORACLE 127

0 SELECT STATEMENT 1 100K 00:00:00.01 34454 1 TABLE ACCESS BY INDEX ROWID TB_DPS_TRSC_BASE 1 33467 100K 00:00:00.01 34454 * 2 INDEX RANGE SCAN IND_01 1 33467 100K 00:00:00.01 32 0 1 34454 N N EXEC :A1 := 1; 0 SELECT STATEMENT 1 100K 00:00:00.01 34454 1 TABLE ACCESS BY INDEX ROWID TB_DPS_TRSC_BASE 1 33467 100K 00:00:00.01 34454 * 2 INDEX RANGE SCAN IND_01 1 33467 100K 00:00:00.01 32 0 1 34454 N N 통계정보가존재하지않기때문에 Adaptive Cursor Sharing 기능을사용할수없어동일한실행계획이수립되어실행된다.(Index Rang Scan ) 통계정보생성하여 10 만건의경우와 10 건의경우를테스트 EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TB_DPS_TRSC_BASE', ESTIMATE_PERCENT=>100, METHOD_OPT=>'FOR COLUMNS SIZE 2 ACCT_NO'); SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM FROM DBA_TAB_COLS WHERE TABLE_NAME='TB_DPS_TRSC_BASE'; 128 2013 기술백서 White Paper

TABLE_NAME COLUMN_NAME HISTOGRAM ------------------------- ------------------------- --------------- TB_DPS_TRSC_BASE ACCT_NO HEIGHT BALANCED TB_DPS_TRSC_BASE CUST_NO NONE TB_DPS_TRSC_BASE AMT NONE ALTER SYSTEM FLUSH SHARED_POOL; EXEC :A1 := 99; 0 SELECT STATEMENT 1 10 00:00:00.01 3 1 TABLE ACCESS BY INDEX ROWID TB_DPS_TRSC_BASE 1 33467 10 00:00:00.01 3 * 2 INDEX RANGE SCAN IND_01 1 33467 10 00:00:00.01 2 -- SQL 의실행계획이변경될수있다면 IS_BIND_SENSITIVE 값이 "Y" 로나타난다.(Bind 변수값이 있으면 "Y" 로표시됨.) 0 1 3 Y N EXEC :A1 := 1; 0 SELECT STATEMENT 1 100K 00:00:00.01 34454 1 TABLE ACCESS BY INDEX ROWID TB_DPS_TRSC_BASE 1 33467 100K 00:00:00.01 34454 * 2 INDEX RANGE SCAN IND_01 1 33467 100K 00:00:00.01 32 Part 1 ORACLE 129

0 1 34454 Y N EXEC :A1 := 1; ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ 0 SELECT STATEMENT 1 100K 00:00:00.06 6887 * 1 TABLE ACCESS FULL TB_DPS_TRSC_BASE 1 33467 100K 00:00:00.06 6887 ------------------------------------------------------------------------------------------------ --Bind 변수값에따라실행계획이변경되어야하는지결정하고실행계획이변경되어야한다면 IS_BIND_AWARE 값이 "Y" 로표시됨. 0 2 34454 Y N 1 1 6687 Y Y EXEC :A1 := 99; 0 SELECT STATEMENT 1 10 00:00:00.01 3 1 TABLE ACCESS BY INDEX ROWID TB_DPS_TRSC_BASE 1 33467 10 00:00:00.01 3 * 2 INDEX RANGE SCAN IND_01 1 33467 10 00:00:00.01 2 CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR ------------ ---------- ----------- --------- --------- ---------- 0 2 3454 Y N N 1 1 6687 Y Y Y 130 2013 기술백서 White Paper

2 2 32 Y Y Y 결론 Bind 변수를사용하는 SQL 에서처음사용되는변수값으로실행계획을세우게되어그실행계획이 SQL 의성능에문제를발생시킬수있어 Bind Peeking 기능은사용하지못하는기능이되어버렸다. 하지만이기능의개선으로 Adaptive Cursor Sharing 기능을사용하게되면이문제를해결할수있다. 하지만그기능의장점과단점을잘알고사용해야만시스템을안정적으로사용할수있을것이다. 또한운영하고있는시스템을 Adaptive Cursor Sharing 기능을사용할수있는부분이있을것이다. 이기능을활용해서 SQL 의성능을개선시킨다면더욱안정화되고최적화된시스템으로발전하게될것이다. Part 1 ORACLE 131