cursor_sharing 파라미터에 대한 테스트

                                                                                    

cursor_sharing 파라미터에 대한 테스트

오라클 데이터베이스의 파라미터 중 cursor_sharing의 value는 exact, similar, force의 값을 가진다. 값의 설정에 따른 영향도와 정확한 동작 방식을 확인하고자 테스트 진행

테스트 시나리오

child cursor이 생기지 않는 형식의 테스트(bind peeking 사용)

1.   테스트 데이터 생성(num의 값이 unique한 10만건 테이블, 인덱스 생성)

2.   cursor_sharing 변경(exact, similar, force)

3.   10개의 JOB 등록(각 JOB은 1만번의 루프를 수행하며 루프 마다 ‘where num = 값’의 조건절 중 값이 리터럴하게 변경되면서 수행)

4.   모니터링

child cursor가 생기는 방식의 테스트(bind peeking 사용)는 위의 방식과 동일한 방식으로 진행되지만 조건절을 ‘where num between A and 값’ 형식으로 값을 변경해 가면서 테스트 진행한다.

 

사용스크립트

       simulation_hard_parsing.sh

#!/bin/bash   ## $1 => Sample Data Row Count ## 등록되는 job을 10 개로 함 ## Hard Parsing Simulation   sqlplus /nolog <<EOF   CONN / AS SYSDBA GRANT EXECUTE ON DBMS_SCHEDULER to HR; DECLARE BEGIN    FOR C IN 1 .. 10    LOOP       SYS.DBMS_SCHEDULER.DROP_JOB('HR.TEST_SCHEDULE' || '_' || C);    END LOOP; END; /   CONN HR/HR -- Make Test Data @/home/oracle/script/make_sample_data.sql ALL_OBJECTS TEST $1   -- Index 생성하여 index scan 유도 CREATE INDEX TEST_IDX ON TEST(NUM,OBJECT_NAME) TABLESPACE USERS;   -- Creation Procedure for Load DEFINE P_TOTAL = $1 CREATE OR REPLACE PROCEDURE CURSOR_SHARING_TEST(P_CNT IN NUMBER DEFAULT 0 ) AS    V_NUM          NUMBER;    V_OBJECT_NAME  VARCHAR2(128);    V_OBJECT_ID    NUMBER;    V_CNT          NUMBER;    V_STMT         VARCHAR2(4000); BEGIN    V_CNT       := P_CNT;      FOR C IN 1 .. &P_TOTAL/10    LOOP       V_CNT       := V_CNT + 1;       V_STMT      := 'SELECT NUM, OBJECT_NAME, OBJECT_ID FROM TEST WHERE NUM = ' || V_CNT;         EXECUTE IMMEDIATE V_STMT INTO   V_NUM, V_OBJECT_NAME, V_OBJECT_ID;    END LOOP; END; /   -- Register 10 Test Schedules CONN / AS SYSDBA EXECUTE DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'HR',TABNAME => 'TEST' , METHOD_OPT => 'for all columns size skewonly' ,DEGREE  => 4   ,CASCADE  => TRUE ); EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; ALTER SYSTEM SET CURSOR_SHARING = EXACT; ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE;   DECLARE BEGIN    FOR C IN 1 .. 10    LOOP       BEGIN          SYS.DBMS_SCHEDULER.CREATE_JOB(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C                                       ,JOB_TYPE    => 'STORED_PROCEDURE'                                       ,JOB_ACTION  => 'HR.CURSOR_SHARING_TEST'                                       ,START_DATE  => CURRENT_TIMESTAMP                                       ,JOB_CLASS   => 'DEFAULT_JOB_CLASS'                                       ,COMMENTS    => 'cursor_sharing value test'                                       ,AUTO_DROP   => FALSE                                       ,NUMBER_OF_ARGUMENTS => 1                                       ,ENABLED     => FALSE                                       );          SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(NAME        => 'HR.TEST_SCHEDULE' || '_' || C                                          ,ATTRIBUTE   => 'logging_level'                                          ,VALUE       => DBMS_SCHEDULER.LOGGING_FULL                                          );          SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C                                                   ,ARGUMENT_POSITION => 1                                                   ,ARGUMENT_VALUE => &P_TOTAL/10 * C                                                   );          SYS.DBMS_SCHEDULER.ENABLE('HR.TEST_SCHEDULE' || '_' || C);       END;    END LOOP; END; /   EXIT EOF

       ALTER SYSTEM SET CURSOR_SHARING=EXACT 부분을 바꾸어 가면서 테스트 진행

 

simulation_hard_parsing_child.sh

#!/bin/bash   ## $1 => Sample Data Row Count ## 등록되는 job을 10 개로 함 ## Hard Parsing Simulation   sqlplus /nolog <<EOF   CONN / AS SYSDBA GRANT EXECUTE ON DBMS_SCHEDULER to HR; DECLARE BEGIN    FOR C IN 1 .. 10    LOOP       SYS.DBMS_SCHEDULER.DROP_JOB('HR.TEST_SCHEDULE' || '_' || C);    END LOOP; END; /   CONN HR/HR -- Make Test Data @/home/oracle/script/make_sample_data.sql ALL_OBJECTS TEST $1   -- Index 생성하여 index scan 유도 CREATE INDEX TEST_IDX ON TEST(NUM,OBJECT_NAME) TABLESPACE USERS;   -- Creation Procedure for Load DEFINE P_TOTAL = $1 CREATE OR REPLACE PROCEDURE CURSOR_SHARING_TEST(P_CNT IN NUMBER DEFAULT 0 ) AS    V_NUM          NUMBER;    V_OBJECT_NAME  VARCHAR2(128);    V_OBJECT_ID    NUMBER;    V_CNT          NUMBER;    V_STMT         VARCHAR2(4000); BEGIN    V_CNT       := P_CNT;      FOR C IN 1 .. &P_TOTAL/10    LOOP       V_CNT       := V_CNT + 1;       V_STMT      := 'SELECT COUNT(*) FROM TEST WHERE NUM BETWEEN ' || P_CNT || ' AND ' || V_CNT;         EXECUTE IMMEDIATE V_STMT INTO   V_NUM;    END LOOP; END; /   -- Register 10 Test Schedules CONN / AS SYSDBA EXECUTE DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'HR',TABNAME => 'TEST' , METHOD_OPT => 'for all columns size skewonly' ,DEGREE  => 4   ,CASCADE  => TRUE ); EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; ALTER SYSTEM SET CURSOR_SHARING = EXACT; ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE;   DECLARE BEGIN    FOR C IN 1 .. 10    LOOP       BEGIN          SYS.DBMS_SCHEDULER.CREATE_JOB(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C                                       ,JOB_TYPE    => 'STORED_PROCEDURE'                                       ,JOB_ACTION  => 'HR.CURSOR_SHARING_TEST'                                       ,START_DATE  => CURRENT_TIMESTAMP                                       ,JOB_CLASS   => 'DEFAULT_JOB_CLASS'                                       ,COMMENTS    => 'cursor_sharing value test'                                       ,AUTO_DROP   => FALSE                                       ,NUMBER_OF_ARGUMENTS => 1                                       ,ENABLED     => FALSE                                       );          SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(NAME        => 'HR.TEST_SCHEDULE' || '_' || C                                          ,ATTRIBUTE   => 'logging_level'                                          ,VALUE       => DBMS_SCHEDULER.LOGGING_FULL                                          );          SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C                                                   ,ARGUMENT_POSITION => 1                                                   ,ARGUMENT_VALUE => &P_TOTAL/10 * C                                                   );          SYS.DBMS_SCHEDULER.ENABLE('HR.TEST_SCHEDULE' || '_' || C);       END;    END LOOP; END; /   EXIT EOF

       리터럴 SQL 문장이 between을 사용하는 형태로 바뀌었다.

 

       make_sample_data.sql

-- usage : 1 => Source Table, 2 => Target Table 3 => target Total Rows   define cnt=idle DROP TABLE &2 PURGE;   CREATE TABLE &2 AS    SELECT   1 NUM, ROWNUM SOURCE_NUM, A.*    FROM     &1 A    WHERE    1 = 2;   COLUMN TBL_CNT NEW_VALUE CNT   SELECT   COUNT( * ) TBL_CNT FROM &1;   INSERT INTO &2    SELECT     A.NUM, B.*    FROM       (SELECT       LEVEL NUM                FROM         DUAL                CONNECT BY   LEVEL <= &3) A, (SELECT   ROWNUM - 1 NUM, A.*                                              FROM     &1 A) B    WHERE      MOD(A.NUM, &CNT) = B.NUM    ORDER BY   A.NUM;   COMMIT;

 

테스트 중 측정할 데이터 리스트

l  AWR Report

l  ASH Report

l  EM Performance Graph

l  OS 레벨의 vmstat 정보

테스트 결과1 (child cursor이 생기지 않는 시나리오)

먼저 EM의 성능 그래프를 확인해 보자.

 

성능 그래프를 확인해 보면 역시 예상대로 exact 부분에서 wait 현상이 발생하는 것을 확인할 수 있다.

EAXCT

AWR Report를 살펴 보자

 

Load Profile

Load Profile ~~~~~~~~~~~~                            Per Second       Per Transaction                                    ---------------       ---------------                   Redo size:              5,209.31             29,990.00               Logical reads:              1,398.13              8,049.05               Block changes:                 16.93                 97.48              Physical reads:                 21.92                126.21             Physical writes:                  5.00                 28.76                  User calls:                  4.18                 24.07                      Parses:                393.62              2,266.07                 Hard parses:                375.25              2,160.33                       Sorts:                 15.11                 87.00                      Logons:                  0.12                  0.67                    Executes:                424.32              2,442.79                Transactions:                  0.17

역시 리터럴 SQL들은 다른 SQL문으로 받아 들여 Hard parse 수치가 높게 나온다.

 

Instance Efficiency Percentages

Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~             Buffer Nowait %:  100.00       Redo NoWait %:  100.00             Buffer  Hit   %:   98.43    In-memory Sort %:  100.00             Library Hit   %:   61.36        Soft Parse %:    4.67          Execute to Parse %:    7.23         Latch Hit %:   99.93 Parse CPU to Parse Elapsd %:   14.10     % Non-Parse CPU:   58.98

역시 Library Hit율이 떨어진다.

 

Top 5 Timed Events

Top 5 Timed Events                                         Avg %Total ~~~~~~~~~~~~~~~~~~                                        wait   Call Event                                 Waits    Time (s)   (ms)   Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- CPU time                                            206          13.3 db file sequential read               4,788         103     21    6.6   User I/O os thread startup                       100          85    852    5.5 Concurrenc latch: library cache                  3,541          58     16    3.7 Concurrenc cursor: mutex X                           6          51   8447    3.3 Concurrenc

Event 중에 latch: library cache에 대한 event가 많음을 확인할 수 있다.

 

Time Model Statistics

^LTime Model Statistics                        DB/Inst: ORCL/ORCL  Snaps: 69-70 -> Total time in database user-calls (DB Time): 1544.8s -> Statistics including the word "background" measure background process    time, and so do not contribute to the DB time statistic -> Ordered by % or DB time desc, Statistic name   Statistic Name                                       Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time                              1,555.0        100.7 parse time elapsed                                    1,220.3         79.0 hard parse elapsed time                                 508.4         32.9 DB CPU                                                  206.1         13.3 PL/SQL execution elapsed time                            19.6          1.3 connection management call elapsed time                  15.3          1.0 PL/SQL compilation elapsed time                           4.1           .3 hard parse (sharing criteria) elapsed time                0.8           .1 repeated bind elapsed time                                0.2           .0 hard parse (bind mismatch) elapsed time                   0.1           .0 sequence load elapsed time                                0.0           .0 DB time                                               1,544.8          N/A background elapsed time                                 221.1          N/A background cpu time                                       1.9          N/A

Time Model의 통계를 확인해 보면 역시나 Hard Parse에 많은 부분을 사용하는 걸 확인할 수 있다.

 

ASH Report 역시 비슷한 결과에 해당하는 내용이 있다.

SIMILAR

Load Profile

Load Profile ~~~~~~~~~~~~                            Per Second       Per Transaction                                    ---------------       ---------------                   Redo size:              2,690.38             22,573.94               Logical reads:              1,233.33             10,348.35               Block changes:                  8.17                 68.58              Physical reads:                 17.18                144.13             Physical writes:                  1.41                 11.81                  User calls:                  4.13                 34.61                      Parses:                363.36              3,048.84                 Hard parses:                  2.68                 22.45                       Sorts:                 11.68                 97.97                      Logons:                  0.11                  0.90                    Executes:                386.53              3,243.26                Transactions:                  0.12

같은 테스트지만 Hard parse 부분이 많은 부분 없어진 걸 확인할 수 있다. 파라미터 값을 similar로 변경하여 테스트에 사용한 리터럴 SQL이 동일한 문장은 아니지만 같은 플랜을 사용하게끔 인식이 되는 것이다. 그래서 이미 Library Cache에 존재하는 parsing 정보를 사용하기에 Hard parse를 하지 않게 되었다. 이하의 결과에도 마찬가지의 정보를 보여준다.

 

Instance Efficiency Percentages

Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~             Buffer Nowait %:  100.00       Redo NoWait %:  100.00             Buffer  Hit   %:   98.61    In-memory Sort %:  100.00             Library Hit   %:   97.66        Soft Parse %:   99.26          Execute to Parse %:    5.99         Latch Hit %:   99.76 Parse CPU to Parse Elapsd %:   27.30     % Non-Parse CPU:   91.17

Library Hit율이 97%까지 올라간 결과를 확인할 수 있다.

 

Top 5 Timed Events

Top 5 Timed Events                                         Avg %Total ~~~~~~~~~~~~~~~~~~                                        wait   Call Event                                 Waits    Time (s)   (ms)   Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- CPU time                                             53          13.9 db file sequential read               4,218          25      6    6.7   User I/O library cache load lock                  17           3    195    0.9 Concurrenc os thread startup                        13           3    253    0.9 Concurrenc log file parallel write                 123           3     23    0.7 System I/O

latch: library cache에 대한 event가 사라진 것을 확인할 수 있다.

 

Time Model Statistics

^LTime Model Statistics                        DB/Inst: ORCL/ORCL  Snaps: 71-72 -> Total time in database user-calls (DB Time): 378.6s -> Statistics including the word "background" measure background process    time, and so do not contribute to the DB time statistic -> Ordered by % or DB time desc, Statistic name   Statistic Name                                       Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time                                386.4        102.1 parse time elapsed                                       86.6         22.9 DB CPU                                                   52.6         13.9 PL/SQL execution elapsed time                            38.0         10.0 hard parse elapsed time                                  21.6          5.7 PL/SQL compilation elapsed time                           4.8          1.3 connection management call elapsed time                   0.4           .1 repeated bind elapsed time                                0.2           .1 hard parse (sharing criteria) elapsed time                0.2           .0 failed parse elapsed time                                 0.2           .0 sequence load elapsed time                                0.0           .0 DB time                                                 378.6          N/A background elapsed time                                  12.0          N/A background cpu time                                       1.6          N/A

전체적으로 parsing과 관련된 시간이 줄어든 것을 확인할 수 있으며 이를 통해 sql execute elapsed time이 줄어 든 것이 확인 가능하다. 결론적으로 DB time 자체가 줄어드는 성능적인 이득을 취하게 된다. 이것을 다르게 말하면 전체적으로 시스템의 Response Time이 확연히 증가함을 말한다. End 유저는 확실한 성능 차이를 체감하게 될 것이다.

 

ASH Report로 마찬가지의 결과를 보여주고 있다.

FORCE

Load Profile

Load Profile ~~~~~~~~~~~~                            Per Second       Per Transaction                                    ---------------       ---------------                   Redo size:              2,340.54             21,036.32               Logical reads:                966.83              8,689.63               Block changes:                  8.08                 72.63              Physical reads:                 13.81                124.08             Physical writes:                  1.17                 10.50                  User calls:                  2.42                 21.71                      Parses:                278.51              2,503.18                 Hard parses:                  2.97                 26.66                       Sorts:                  9.02                 81.08                      Logons:                  0.08                  0.76                    Executes:                297.61              2,674.82                Transactions:                  0.11

similar 의 경우와 비슷한 수치를 보이고 있다.

 

Instance Efficiency Percentages

Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~             Buffer Nowait %:  100.00       Redo NoWait %:  100.00             Buffer  Hit   %:   98.58    In-memory Sort %:  100.00             Library Hit   %:   97.35        Soft Parse %:   98.94          Execute to Parse %:    6.42         Latch Hit %:   99.91 Parse CPU to Parse Elapsd %:   33.57     % Non-Parse CPU:   93.78

similar 의 경우와 비슷한 수치를 보이고 있다.

 

Top 5 Timed Events

Top 5 Timed Events                                         Avg %Total ~~~~~~~~~~~~~~~~~~                                        wait   Call Event                                 Waits    Time (s)   (ms)   Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- CPU time                                             77          13.2 db file sequential read               4,384          20      5    3.4   User I/O job scheduler coordinator slav            1           8   7984    1.4      Other Streams AQ: qmn coordinator wa            1           5   4884    0.8      Other os thread startup                        13           4    276    0.6 Concurrenc

latch: library cache에 대한 event가 사라진 것을 확인할 수 있다.

 

Time Model Statistics

^LTime Model Statistics                        DB/Inst: ORCL/ORCL  Snaps: 73-74 -> Total time in database user-calls (DB Time): 585.2s -> Statistics including the word "background" measure background process    time, and so do not contribute to the DB time statistic -> Ordered by % or DB time desc, Statistic name   Statistic Name                                       Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time                                597.6        102.1 parse time elapsed                                      122.9         21.0 DB CPU                                                   77.4         13.2 PL/SQL execution elapsed time                            44.4          7.6 hard parse elapsed time                                  21.8          3.7 PL/SQL compilation elapsed time                           3.4           .6 hard parse (sharing criteria) elapsed time                0.7           .1 connection management call elapsed time                   0.2           .0 repeated bind elapsed time                                0.2           .0 failed parse elapsed time                                 0.1           .0 sequence load elapsed time                                0.1           .0 hard parse (bind mismatch) elapsed time                   0.0           .0 DB time                                                 585.2          N/A background elapsed time                                  27.2          N/A background cpu time                                       2.3          N/A

similar 때와 비슷한 상황으로 분석된다.

테스트 결과2 (child cursor이 생기는 시나리오)

child cursor의 발생 상황에 대해서 먼저 정의가 필요하다. cursor_sharing 파라미터의 값이 similar일 경우 오라클은 실행계획이 같다고 판단이 되는 경우 동일한 형태의 리터럴 SQL 문장에 대해서 같은 문장으로 인식하고 사용하게끔 한다. 하지만 실행계획은 같지만 포함된 통계정보가 다를 경우 같은 문장으로는 인식을 하지만 재사용을 하는 것이 아니라 child cursor을 생성하여 수행하게 된다. 여기에는 여러 변수가 작용할 수 있다. Bind peeking과 히스토그램, 옵티마이저의 방식 등등이 영향을 미치게 된다.

테스트 시스템은 bind peeking을 사용하며 CBO 방식을 사용했으며 child cursor이 발생하는 환경으로 세팅을 완료했다.

다음은 테스트 결과를 보도록 하겠다.

 

 

Similar의 경우 오히려 더 많은 wait event가 발생한 것을 확인할 수 있다.

 

exact, force인 경우 테스트1의 경우와 비슷한 레벨로 보이고 similar에서는 오히려 성능이 더 떨어지는 그래프를 보이고 있다. 결론적으로 similar인 경우에 대해서는 테스트를 완료할 수 없었다. Child cursor이 3만개 이상 발생하자 Job slave가 terminated 되고 오라클 600에러를 내면서 작업이 fail로 끝나게 된다.

결론

cursor_sharing 파라미터의 경우 많은 부분이 해당 value인 exact, similar, force에 대한 용어적 해석을 통한 접근으로 정확하지 않은 표현으로 가이드 되는 경우가 많다.

테스트의 출발은 위 value가 정확히 어떤 기준으로 동작하는지 확인 하는 것이었다.

Similar인 경우 실행계획이 같은 문장에 한해서는 같은 문장으로 인식하고 library cache에 로딩되어 있는 정보를 그대로 사용한다. 하지만 플랜에 포함된 통계정보가 다르다고 판단이 되는 실행계획일 경우 child cursor을 생성하여 사용하게 된다. 즉 similar인 경우 통계정보까지도 동일한 형태의 실행계획일 경우에 child cursor을 생성하지 않고 사용하게 된다. 반면 force인 경우는 실행계획만 같다면 similar와 다르게 같은 문장으로 인식하여 cache된 정보를 재사용하게 한다.

그렇다면 파라미터에 대해선 어떻게 사용해야할까..?

이는 상황에 따라서 다르겠지만 여러가지를 종합적으로 고려를 해야한다. 분명 가이드는 exact 이다. 그리고 bind 변수 사용에 대해 최대한 권장을 해야한다. 임시방편 격으로 similar, force를 사용 가능하다. 하지만 이것도 고려 사항이 있다. Similar인 경우 child cursor가 발생하는 형식으로 된다면 시스템은 버티기가 힘들 것이다. 즉 사이드 이펙트가 없는 상황일 경우에 쓸만하다는 것이다. 그렇다면 force를 쓰는게 더 낫지 않겠냐는 반문이 나올 것이다. 하지만 이도 문제 요소가 있다. 데이터의 분포가 균등하지 못한 경우 사용되는 변수의 값이 어떤 것이냐에 따라서 적합한 실행계획이 다른 바뀔 것이다. Force의 경우 이런 케이스를 커버하지 못한다는 단점이 있는 것이다.

[출처] cursor_sharing 파라미터에 대한 테스트|작성자 명품 관