About DATABASE

                                                                                    

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 정보

먼저 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 때와 비슷한 상황으로 분석된다.

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의 경우 이런 케이스를 커버하지 못한다는 단점이 있는 것이다.

Oracle에서 OLTP 업무를 위한 SQL 구현시 Literal SQL을 사용하지 말라는 얘기를 한다. 모든 SQL에 일관되게 적용할 수는 없겠지만 몇몇 예외를 제외하고는 Bind Variable을 사용하여 구현하는 것이 좋다.

따라서, DBA들이 개발자에게 Literal SQL을 검색해서 수정 요청을 하는 경우가 많은데 Literal SQL을 조회하는 방법에 대해서 알아보도록 하자.

크게 두가지 방법으로 Literal SQL을 검색한다. 첫번째는 sql_text를 이용한 검색이고, 두번째는 plan_hash_value를 통해서 찾을 수가 있다.

1. sql_text 이용한 검색 방법

사용된 sql의 text를 50자를 짜라서 하나의 데이터로 간주해서 동일한 sql을 찾는 방법이다.
물론 이 방법도 100% 정확하지는 않다. 왜냐하면 50자는 동일하지만 그 다음의 sql_text는 다를 수 있기 때문이다.  다양하게 구현할 수 있겠지만 간략하게 literal sql은 아래와 같다.

SELECT hash_value,
       module,
       first_load_time,
       SUBSTR (sql_text, 1, 50)
FROM   v$sqlarea
WHERE  SUBSTR (sql_text, 1, 50) IN (SELECT SUBSTR (sql_text, 1, 50)
        FROM   v$sqlarea
        HAVING COUNT (*) > 4
        GROUP BY SUBSTR (sql_text, 1, 50))
ORDER BY sql_text;



2. plan_hash_value 이용한 검색 방법

동일한 sql 이지만 literal 을 사용하여 다른 sql 으로 시스템에 사용된 sql은 대부분 동일한 실행계획을 가질 확률이 높다. 따라서 plan_hash_value을 이용하여 Literal SQL을 찾을 수도 있다.
plan_hash_value를 통해서 찾은 sql도 100% 다 Literal SQL은 아니고 Literal SQL candidates할 수 있겠다.


select *    
from   (select PLAN_HASH_VALUE hash,
               count(PLAN_HASH_VALUE) cnt ,
               substr(sql_text, 0, 50)
        from   v$sql
        where  plan_hash_value > 0
        group by PLAN_HASH_VALUE , substr(sql_text, 0, 50)
        order by count(PLAN_HASH_VALUE) )
where  cnt > 5

그 밖에 좋은 방법이 있다면 공유를 해주시기 바랍니다.

덤프 : 일회성으로 그 순간의 상태정보를 가집니다.
트레이스 : 10046, 10053 , 4031 등의 이벤트를 걸게 되면 순간의 상태(immediate) 또는
세션이 close될때까지의 정보(trace name context forever) 를 trace로
남깁니다. 4031같은 오라클 내부 에러에 대해서는 immediate,
10046, 10053 처럼 sql, plan에 대해서 확인하는 것은 forever로
하지요.

참고로 udump 에 없다고 해서 고민하지 말구요. bdump를 찾아보세요.
background process를 이용해서 dump를 뜨는 경우에는 bdump에 나오겠죠.

- 문제발생시 덤프 뜨는 방법
문제발생 세션에 대한 10046 event, truss output, errorstack dump
OS engineer의 system state dump
system state dump 2~3회
hang analyze dump 2~3회
system state dump 1~2회
hang analyze dump 1~2회

- 에러스택 뜨기(꼭 수행할 때마다 exit나와서 다시 서버에 접속해서 뜰 것)
oradebug setospid XX
oradebug unlimit
oradebug dump errorstack 3
oradebug tracefile_name

- hanganalyze, systemstate dump 뜨기(꼭 수행할 때마다 exit나와서 다시 서버에 접속해서 뜰 것)
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
oradebug dump systemstate 10

- oradebug로 event 걸기(10046 event는 sql 트레이스 정보, 10053은 optimizer에 대한 트레이스 정보)
oradebug setospid XX
oradebug unlimit
oradebug event 10046 trace name context forever, level 12;
oradebug event 10053 trace name context forever, level 1;
oradebug tracefile_name
oradebug event 10046 trace name context off;
oradebug event 10053 trace name context off;

- 문제가 있는 세션의 process state dump 뜨기
oradebug setospid
oradebug unlimit
oradebug dump processstate 10

- alter session, alter system 명령으로 event걸기(session 이 logout할때까지 수행됨)
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='10046evnt1';
alter session set events '10046 trace name context forever , level 12';
alter session set events '10053 trace name context forever , level 1';
alter session set events '10046 trace name context off';
alter session set events '10053 trace name context off';

- sqlplus 로 접속이 불가능할 경우 백그라운드 프로세스의 systemstate 덤프 뜨기
(위험한 명령임, 서비스 중엔 사용금지, 도저히 문제 발생으로 smon 프로세스를 죽이던지 해서 DB를 내리기전에 수행)

OS debuger를 사용하면 특정 process에게 특정 function을 호출하도록 할 수
있습니다. 이러한 점을 이용하면 ORACLE에서 systemstate dump를 요청할 때
사용하는 ksudss function을 호출할 수 있으며, 절차는 아래와 같습니다.

1. 먼저 attach할 ORACLE process에 대한 OS PID를 알아 둡니다.
(여기서는 PMON process를 예를 들었습니다.)
$ ps ?ef | grep $ORACLE_SID | grep pmon
aprdbms 1432 1 0 23:14:50 ? 0:00 ora_pmon_APR920U6
2. Pmon process에 debuger를 사용하여 attach합니다.
$gdb $ORACLE_HOME/bin/oracle 1432
3. Ksudss function을 호출합니다.
gdb) call ksudss (10)
4. Pmon은 ksudss를 호출하여 systemstate dump를 받게 됩니다.
attach한 process가 ksudss function call 요청을 받아 들이기 위해서는
system call을 수행 중에 있지 않아야 합니다.

- alter session 명령으로 ORA-4031 에러에 대한 event 걸기(immediate로 즉각 떨어지도록)
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='4031evnt1';
alter session set events '04031 trace name errorstack level 3';
alter session set events '04031 trace name systemstate level 10';

원본 출처 http://www.oracleclub.com/article/23893

[ 출처 ]
1. 10g Optimizer개념 및 통계치 생성 방법,SQL Tuning 방법
http://www.dbguide.net/club/board/download.jsp?maskname=274&fileName=10g+Optimizer+to+public.pdf

2.  10g 자동화 통계정보 수집에 관하여... [2008/05/11 Update]
http://blog.naver.com/sungeunn/120051268815

3.  어떤 STATISTICS_LEVEL 을 사용할 것인가 ?
http://cafe.naver.com/prodba/9293

4. 메타링크
  4.1 Two types of automatic statistics collected in 10g [ 559029.1  ]
  4.2 How to Disable Automatic Statistics Collection in 10G ? [ 311836.1 ]
5. 10G References [ STATISTICS_LEVEL ]
  http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#REFRN10214

 [ 결론 ]
1. System 통계정보는 따로 생성 하지 않는다.
    어느 시간이 최적인지 모른다. DBA 가 알수 있으면 정기적으로 생성해서 활용한다.
2. 자동 통계정보 수집(CBO) 는 disable 하고, 수동으로 통계정보를 생성한다.
   운영 중에 통계정보 생성으로 인해 업무의 성능 저하를 사전에 예방하자.
3. STATISTICS_LEVEL = TYPICAL 로 유지 하자

4. 자동 통계정보 수집 대상은 User Objects,  Sys/System Object 가 대상이다.

  
[ 요약 ]

대상	설명	TATISTICS_LEVEL=BASIC	STATISTICS_LEVEL=TYPICAL
System Statistics	System 성능 ( CPU, DISK )
Fixed Objects Statistics	DBMS 성능 ( X$, V$)
Dictionary Statistics	SYS/SYSTEM USER TABLE	자동 수집되지 않는다.	자동 수집된다.
User Table Statistics	일반 유저 TABLE	자동 수집되지 않는다	자동 수집된다.

 

통계정보의 종류
: 크게 4가지로 구분할 수 있다.
[ 1.System Statistics ]
: 개요 - System Statistics 는 System Hardware 의 I/O, CPU 의 특성을 분석하여
         Optimizer 가 CPU Costing 을 계산할 때 사용하는 정보로써,
         이를 기반으로 Optimizer 가 임의의 SQL에 대한 실행 계획을 수립할 때
         이를 기반으로 계산하게 된다.
  수행주기 - 초기 1회
             시스템 자원의 변경이 생겼을 경우 ( Memory, CPU, I/O 등 )
  9i - 처음 소개된 개념이고, DBA가 수동으로 수집 하지 않으면 기본적으로
       존재하지 않는 정보이다. 기본적으로 I/O Model로 비용산정
       System Statistics 정보가 있으면 Optimizer 가 비용 산정을 CPU Model 로 하고,
       System Statistics 정보가 없다면 Optimizer 가 비용 산정을 I/O Model 로 한다.
  10G - System Statistics 정보를 수집 하지 않는다면 Noworkload System Statistics 가
       사용된다. 10G 에서는 Optimizer 가 비용 산정을 CPU Model 로 한다. [ Default ]
       [ CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED 로 구성된다. ]
       수동으로 System Statistics 수집 시에는 Workload System Statistics 라고 한다.
       결론적으로 운영 시스템의 최적의 System Statistics 를 생성하여, 적절히
       적용 하여 사용하는 것이 최선이나, 보통은 NoWorkload System Statistics 를
       그대로 사용한다.
 주의사항 : RAC 에서 NODE 가 서로 같은 시스템 사양을 같지 않을 경우에는
       System Statistics 를 Node 별로 나누어 관리 되지 않으므로 전체 System 의
       대표성을 가지는 Node 에서 수행을 한다.
       특히 위와 같은 결정을 하기 위해서는, 각각의 Node 별로 통계치를 생성해
       보고 비교해 본 후에 결정할 수 있다.
       노드의 사양이 동일한 경우 가장 일반적인 Node 에서 수행한다.  

실습
: 시스템 통계정보는 Optimizer 가 실행 계획 세움에 있어서 지대한 영향을
  미치므로, 항상 기존의 시스템 통계자료를 백업 후 진행 하자.
  OLTP 와 OLAP 성 통계정보를 생성하고, IMPORT 해보자

 
-- 1. 시스템 통계정보 확인
SELECT * FROM SYS.AUX_STATS$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33
SYSSTATS_INFO                  FLAGS                        0
SYSSTATS_MAIN                  CPUSPEEDNW                1489
SYSSTATS_MAIN                  IOSEEKTIM                   10
SYSSTATS_MAIN                  IOTFRSPEED                4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

-- 2. 기존 통계정보 백업 받을 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- 기존 통계정보 백업
SQL> execute DBMS_STATS.EXPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS
PL/SQL procedure successfully completed.
-- 백업 받은 시스템 통계정보 데이타 확인
SQL> SELECT STATID, C1, C2, C3 FROM ORIGIN ;
--  C1 = COMPLETED     --> 수집 완료

-- 3. 시스템 통계정보 수집
-- 사전에 JOB PROCESS 확인 ( 1보다 커야 한다.)
SQL> show parameters job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL> alter system set job_queue_processes = 5 ;
System altered.

SQL> show parameters job_queue
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     5

 

-- 4.  OLTP 성 시스템 통계정보 수집하기
-- OTLP용 시스템 통계정보 생성을 위한 통계정보 테이블 생성
-- OWNER, TABLE 이름, TABLESPACE 이름 순
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLTP’,’USERS’);
PL/SQL procedure successfully completed.
--  지금 부터 INTERVAL 로 지정된 시간 동안 시스템 통계정보를 생성 하라..
--  2분동안 시스템 통계정보를 수집 하라
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLTP’, STATID => ’OLTP’);
PL/SQL procedure successfully completed.
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP                 AUTOGATHERING                  02-24-2009 08:54               02-24-2009 08:56
OLTP
--  C1 = AUTOGATHERING --> 수집 중
--  C1 = COMPLETED     --> 수집 완료 로 변경된다. [ 2분 경과 후 ]

--  10분동안 시스템 통계정보를 수집 하라 [앞서 수행한 2분동안 통계정보는 Update 된다.  ]
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID => ’OLTP’);
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP                 AUTOGATHERING                  02-24-2009 08:56               02-24-2009 08:58
OLTP
--  C1 = AUTOGATHERING --> 수집 중

-- 시스템 통계정보 수집 중지 하기 [ gathering_mode=>’STOP’ ]
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’STOP’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID => ’OLTP’);

-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
--  C1 = AUTOGATHERING --> 수집 중
--  C1 = COMPLETED     --> 수집 완료 [ 강제로 중지 하여도 COMPLETED 로 나온다. ]

 

--  5. OLAP 성 시스템 통계정보 수집하기
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLAP’,’USERS’);
PL/SQL procedure successfully completed.
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLAP’, STATID => ’OLAP’);
SQL> SELECT STATID, C1, C2, C3 FROM OLAP ;

STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLAP                 COMPLETED                      02-24-2009 09:04               02-24-2009 09:06
OLAP
-- C1 => AUTOGATHERING -- 수집 중
-- C1 => COMPLETED     -- 수집 완료 로 변경된다.  [ 2분 경과 후 ]

 

-- 6. 생성한 OLAP_STATS 시스템 통계정보 IMPORT 하기
-- 기존 통계정보 삭제
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- 기존 통계정보를 삭제 하면,
-- SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 값만 DELETE 시점으로 변경된다.
SQL> select  * from sys.aux_stats$;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-24-2009 09:08
SYSSTATS_INFO                  DSTOP                          02-24-2009 09:08

-- OLTP 시스템 통계정보 IMPORT 하기
execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’OLTP’, STATID =>’OLTP’,STATOWN =>’SYS’);
-- Import 된 시스템 통계정보 확인
-- OLTP 시스템 통계정보를 수행한 시간으로 SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP
-- 값이 변경되어 진다.
SQL> select * from sys.aux_stats$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-24-2009 08:56
SYSSTATS_INFO                  DSTOP                          02-24-2009 08:58

-- 7. 다시 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33
--  SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 로 ORIGIN 으로 변경됨

-- 8. 수동으로 시스템 통계정보 생성하기 [ 파라미터 개별 설정 ]
-- 기존 시스템 통계정보 삭제 하기
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- CPUSPEED 설정
-- CPUSPEED : Wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’CPUSPEED’, PVALUE=>400);
PL/SQL procedure successfully completed.
-- CPUSPEED 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’CPUSPEED’;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN                  CPUSPEED                   400
-- SREADTIM 설정
-- SREADTIM : wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’SREADTIM’, PVALUE=>100);
PL/SQL procedure successfully completed.
-- SREADTIM 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’SREADTIM’;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN                  SREADTIM                   100

 

-- 9. 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 원복 결과 확인
SQL> select * from sys.aux_stats$ ;

SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33

 

[ 2.Fixed Objects Statistics ]
  개요 -  Dynamic Performance View와 같이 fixed view(x$ tables)에 대한 통계치는
          Manual 한 Gathering 이 필요하다. 이 Fixed Objects Statistics 는
          Database 의 Activity 를 기록하게 되므로 database 가 일반적인 운영 상태
          일때 gathering  하여야 한다.
          일반적으로 Fixed Object Statistics 통계치는 V$ view 를 조회하는
          사용자 Query에 필요하다.
  수행주기 - 초기 1회
             추가적인 Application 이나 변경으로 동시 사용자 등의 변경 발생시
  주의사항 - 자동화 대상이 아니다. [ 자동으로 Fixed Object의 통계정보가 생성되지 않는다. ]
             RAC 에서는 아직 Fixed Objects Statistics 를 Instance 별로 구분하지
             않기 때문에, 가장 부하가 많은 Node(Instance)에서 통계치를 조사한다.

 
실습
: Fixed Object 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
  항상 기존의 Fixed Object 통계정보를 백업 후 진행 하자.
  OLTP 와 OLAP 성 Fixed Object 통계정보를 생성하고, IMPORT 해보자

 

-- 1. 기존의 Fixed Object 통계정보 백업 테이블 생성   
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_ORIGIN’,’USERS’);           
-- EXPORT_FIXED_OBJECTS_STATS 를 통한 백업 수행
SQL> execute DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’,STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

 

-- 2. 신규로 Fixed Object 통계정보 테이블 생성  
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_NEW’,’USERS’); 
-- 신규 Fixed Object 통계정보 수집
SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’);
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 13578
ORA-06512: at "SYS.DBMS_STATS", line 13892
ORA-06512: at "SYS.DBMS_STATS", line 14420
ORA-06512: at line 1

SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’FIX_NEW’);
PL/SQL procedure successfully completed.

 

-- 3. 기존 Fixed Object 통계정보 확인 하기
SQL>  select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA                       
X$KQFVI                       
X$KQFVT                       
X$KQFDT                       

-- 4. 신규 Fixed Object 통계정보 IMPORT
--  FIXED TABLE 의 통계정보 삭제
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_NEW’, STATID =>’FIX_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 5. 신규 Fixed Object 통계정보 확인 하기
SQL>  select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA                        20090224 09:50:34 09:50:34
X$KQFVI                        20090224 09:50:34 09:50:34
X$KQFVT                        20090224 09:50:34 09:50:34
X$KQFDT                        20090224 09:50:34 09:50:34
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.

 

6. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’, STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 복원된 정보 확인 하기
SQL > select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

 
[ 3.Dictionary Statistics ]
  개요 - DBMS_STATS.GATHER_DICTIONARY_STATS 를 이용하여 Sys, System Schema 의
        Object를 Gathering 한다. 이 procedure 는 또한 DRSYS 나 CTX user Schema의
        Object 도 함께 Gathering 한다.
  수행주기 - 초기 1회
             Database Object(사용자 Table, PL/SQl, User생성) 의 변경이 있는 경우
            
실습
: Dictionary 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
  항상 기존의 Dictionary 통계정보를 백업 후 진행 하자.
  OLTP 와 OLAP 성 Dictionary 통계정보를 생성하고, IMPORT 해보자

 

-- 1. 기존의 Dictionary 통계정보 통계정보 백업 테이블 생성   
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- EXPORT_DICTIONARY_STATS 를 통한 백업 수행
SQL>  execute DBMS_STATS.EXPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’,STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 2. 신규로 Dictionary 통계정보 테이블 생성  
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_NEW’,’USERS’); 
PL/SQL procedure successfully completed.
-- 신규 Dictionary 통계정보 수집
SQL> execute DBMS_STATS.GATHER_DICTIONARY_STATS(’SYS_NEW’);
PL/SQL procedure successfully completed.

 

3. 신규 Dictionary 통계정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2
....
X$LOGMNR_OBJ$                  20090224 10:07:20 10:07:20
X$LOGMNR_TABCOMPART$           20090224 10:07:20 10:07:20
X$LOGMNR_USER$                 20090224 10:07:20 10:07:20
SUMDELTA$
SDO_TOPO_DATA$
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.

4. Dictionary 통계정보 IMPORT 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_NEW’, STATID =>’SYS_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- IMPORT 된  Dictionary 통계정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2;
SRS$                           20081229 22:00:05 22:00:05
X$LOGMNR_ATTRIBUTE$            20090224 10:07:19 10:07:19
X$LOGMNR_COLTYPE$              20090224 10:07:19 10:07:19
X$LOGMNR_IND$                  20090224 10:07:19 10:07:19
X$LOGMNR_COL$                  20090224 10:07:19 10:07:19
X$LOGMNR_DICT$                 20090224 10:07:19 10:07:19
==> 테스트 에서는 실제로 Dictionary 통계정보를 가진 Table Count 가 오히려 줄어들었다.
      
5. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’, STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 복원된 정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2;

 

[ 4.User Table Statistics ]
: 사용자의 Object 에 대한 통계치 수집은 10G 에서 기본적인 GATHER_STATS_JOB 을
이용한다. 이 JOB 은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가
변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행한다.
또한 분석하는 순서 역시 우선순위 순으로 수행한다.
만약 Object 에 많은 Data 가 Load 되거나 변경이 있는 경우에는  Manual 하게
수행한다.
- 10G 에서 말하는 자동 통계정보 수집 기능(Automatic Statistics) 의 대상은
  1. AWR(Automactic Workload Repository)
   - 분석, Self-Tuing 및 일반적인 Tuning 을 목적으로 하는 자료로
     특정 시간 동안 데이타 베이스에서 발생한 여러 가지 상황 정보를 이른다.
     Wait Events, Latces, Enqueues, Cpu Consumption, SGA Compoenent, PGA 등에
     대한 자료 이다.
      ’STATISTICS_LEVEL’ 에 의해서 수집되는 자료의 LEVEL 이 결정된다.
     AWR 정보는 디폴트로 7일간 보관한다. [ SYSAUX TABLESPACE 에 ]
    
  2. CBO(Cost-Based Optimizer)
   - Database 의 Object 즉,  Application 및 Oracle Internal (Sys/System) 유저의
     Table, Index 에 대한 통계정보 수집
     이 수집된 정보는 Optimizer 가 수행계획 수립 시에 의해 사용된다.
     GATHER_STATS_JOB 에 의해서 수집 된다. [DB 생성시 자동 생성됨]
     Optimizer historical 통계정보는 디폴트로 31일간 보관한다.
     STATISTICS_LEVEL=BASIC 이면 CBO 통계정보가 수집되지 않는다.
        - Automatic Optimizer Statistics Collection
        - Object level Statistics  
     [ SYSAUX TABLESPACE 에 ]  
    
-- SYSAUX 사용 현황 파악 하기
 SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;
 1. SM/AWR 
  - AWR 정보 수집
  - AWR 정보 수집 옵션 확인
    SQL>  SELECT DBID, RETENTION, TOPNSQL  FROM DBA_HIST_WR_CONTROL ;
          DBID RETENTION                      TOPNSQL
    ---------- ------------------------------ ----------
    2466823093 +00007 00:00:00.0              DEFAULT
    보존 변경은 dbms_workload_repository.modify_snapshot_settings 를 통해서 가능
   
    -- AWR 정보 한달 보관주기로 변경 [ 60*24*31 = 44640 분 ]
    SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 44640 );
    PL/SQL procedure successfully completed.
    -- 변경되 AWR 정보 확인
    SQL> SELECT DBID, RETENTION, TOPNSQL  FROM DBA_HIST_WR_CONTROL ;
   
          DBID RETENTION                      TOPNSQL
    ---------- ------------------------------ ----------
    2466823093 +00031 00:00:00.0              DEFAULT

 2. SM/ADVISOR
    SQL Tuning Advisor, SQL Access Advisor, ADDM 이 사용하는 정보 저장소

 3. SM/OPTSTAT    
    - 구버전(Old) Optimizer 통계정보 저장소
    - SM/OPTSTAT 저장 기간 확인
      SQL> select dbms_stats.get_stats_history_retention from dual;
      GET_STATS_HISTORY_RETENTION
      ---------------------------
                               31
      ==> Default 로 31일
    -- 10일로 조절
    SQL> exec dbms_stats.alter_stats_history_retention(10);
    PL/SQL procedure successfully completed.

    SQL> select dbms_stats.get_stats_history_retention from dual;
    GET_STATS_HISTORY_RETENTION
    ---------------------------
                         10
    -- 원복
    SQL> exec dbms_stats.alter_stats_history_retention(31);
    PL/SQL procedure successfully completed.

  4. SM/OTHER
     - Alert History 등의 저장소

--  STATISTICS_LEVEL PARAMETER
    : Database 와 OS 의 통계정보의 수집 Level 을 제어 하는 파라미터
    1.Typical
      - Default, 일반적인 환경에 가장 적합
    2. ALL
      - typical + Timed OS Statistics + Plan Execution Statistics
    3. BASIC
      - 아래 기능을에 필요한 중요한 통계정보를 수집 할 수 없다.
        - AWR
        - ADDM
        - All Server-Generated Alerts
        - Automatic SGA Memory Management
        - Automatic Optimizer Statistics Collection
        - Object level Statistics
        등...
           
 
실습 : 여기서 말하는 자동 통계수집 이란 CBO 에 대한 것을 이른다.
     - 1. 자동 통계수집일정을 확인하고
     - 2. 자동 통계수집을 Disable 해보자
     - 3. 통계정보 백업 / 복구 하기 
     - 4. 특정 테이블 통계수집 중지 하기
 
-- 1.1 자동통계정보 수집 확인 하기
SQL >  select job_name, job_type, program_name, schedule_name, job_class
      from dba_scheduler_jobs
       where job_name =’GATHER_STATS_JOB’;
JOB_NAME             JOB_TYPE         PROGRAM_NAME         SCHEDULE_NAME                  JOB_CLASS
-------------------- ---------------- -------------------- ------------------------------ ------------------------------
GATHER_STATS_JOB                      GATHER_STATS_PROG    MAINTENANCE_WINDOW_GROUP       AUTO_TASKS_JOB_CLASS

-- 1.2 자동통계정보 수집 시 실행 되는 Program 확인
SQL> select program_Action from dba_scheduler_programs where program_name =’GATHER_STATS_PROG’;

PROGRAM_ACTION
--------------------------------------------------
dbms_stats.gather_database_stats_job_proc

--  1.3 자동통계정보 수집 시 스케줄 확인
SQL> select * from dba_scheduler_wingroup_members where window_group_name =’MAINTENANCE_WINDOW_GROUP’;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

 

--  1.4 자동통계정보 수집 시 스케줄 상세 확인
SQL> select window_name, repeat_interval, duration
     from dba_scheduler_windows
     where window_name in (’WEEKNIGHT_WINDOW’,’WEEKEND_WINDOW’);

WINDOW_NAME          REPEAT_INTERVAL                                                                  DURATION
-------------------- -------------------------------------------------------------------------------- --------------------
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0            +000 08:00:00
WEEKEND_WINDOW       freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                              +002 00:00:00

-- 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행 된다.
-- 토요일 0시에 수행되어 이틀 동안 수행된다.

 

-- 2.1 자동통계정보 수집 중지
-- STATISTICS_LEVEL=BASIC 이면 자동통계정보 수집(CBO)
SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     SCHEDULED

SQL> exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     DISABLED

 

-- 2.2 자동통계정보 수집 재설정
SQL> exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     SCHEDULED

 

-- 3.1 통계정보 백업 / 복구 하기
-- 유저 테이블 통계정보 백업 받을 테이블 생성하기
SQL> execute dbms_stats.create_stat_table(’SYS’,’USER_STATS’,’USERS’);
PL/SQL procedure successfully completed.
-- SCOTT 유저 테이블 통계정보 백업 받기
SQL> execute dbms_stats.export_schema_stats(’SCOTT’,’USER_STATS’,’SCOTT’,’SYS’);
PL/SQL procedure successfully completed.
-- 백업된 SCOTT 유저의 통계정보 확인
SQL> select STATID, C1, C2, C4, D1 from  USER_STATS ;

-- 3.2 신규로 유저 테이블 통계정보 생성
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
     GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
     CASCADE => TRUE );
-- 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

 

-- 3.3 유저 테이블 통계정보 원복하기
SQL> exec dbms_stats.delete_schema_stats(’SCOTT’);
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.import_schema_stats(’SCOTT’,’USER_STATS’,’USER_STATS’,’SYS’);
PL/SQL procedure successfully completed.
 
-- 3.4 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

 

-- 4.1 특정 테이블 통계수집 중지 하기
-- 수동으로 통계정보 수집 하여 LAST_ANALYZED  Update 하기
SQL>  execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
     GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
      CASCADE => TRUE );
PL/SQL procedure successfully completed.
-- 4.2 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

-- 특정 테이블 통계정보 수집 막기
SQL> execute dbms_stats.lock_table_stats(’SCOTT’,’T1’);
PL/SQL procedure successfully completed.

-- 특정 테이블 통계정보 수집 막음 확인
SQL> SELECT owner, table_name, stattype_locked
     FROM dba_tab_statistics
     WHERE OWNER=’SCOTT’
     and stattype_locked is not null;
    
OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
SCOTT                          T1                             ALL

-- 확인을 위해서 수동으로 통계정보 수집 하여 LAST_ANALYZED  Update 하기
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
> GRANULARITY =>’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
> CASCADE => TRUE );
PL/SQL procedure successfully completed.

SQL>  select OWNER,TABLE_NAME, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from  dba_tab_statistics
      WHERE OWNER=’SCOTT’
     and table_name in (’T1’,’EMP’,’DEPT’);

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ ------------------------------ --------------------------
SCOTT                          DEPT                           20090224 11:37:57 11:37:57
SCOTT                          EMP                            20090224 11:37:57 11:37:57
SCOTT                          T1                             20090224 11:32:53 11:32:53

==> Lock 되어진 T1 에 대해서는 테이블 통계정보가 생성되지 않았음을 확인

DUAL에 통계정보가 일반적으로 없는 8i와 9i에서 DUAL 테이블의 UNION ALL 혹은 UNION 의 실행계획에서 cardinality가 1이 아닌 것으로 계산되어 Cost가 높게 계산되는 CBO 버그에 대해서 테스트해봤다..

 

 

-- 다음과 같이 여러가지 경우의 DUAL UNION (ALL) DUAL에 대해 10053 trace를 수행함.
alter session set db_file_multiblock_read_count=32;
alter session set events '10053 trace name context forever,level 1';

 

-- 8.1.7.4는 UNION ALL과 UNION 이 동일하게 card=41임.

-- Block의 수는 1개로 인식됨.

 

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

DB_FILE_MULTIBLOCK_READ_COUNT = 32

QUERY
SELECT /*+ all_rows */ 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DUAL   Alias: DUAL
  TOTAL ::  (NOT ANALYZED)    CDN: 41  NBLKS:  1  TABLE_SCAN_CST: 1  AVG_ROW_LEN:  100
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DUAL     ORIG CDN: 41  CMPTD CDN: 41
  Access path: tsc  Resc:  1  Resp:  1
  BEST_CST: 1.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: DUAL [DUAL]
Best so far: TABLE#: 0  CST:          1  CDN:         41  BYTES:          0
Final:
  CST: 1  CDN: 41  RSC: 1  RSP: 1  BYTES: 0

 

-- 9.2.0.7은 UNION ALL과 UNION, all_rows와 first_rows_1에 따라 다른 결과
-- first_rows_1/UNION ALL인 경우에만 card=1 이고 나머지 경우는 모두 4072임.

-- 4072건으로 인식되는 경우 블럭수가 100개로 인식되는 Case도 있음.
-- 특이사항) 본문에는 없지만 테스트결과
--           first_rows_10이면 card=10, first_rows_100이면 card=100, first_rows_1000이면 card=1000 임.

-- ** 9i에서 dynamic_sampling(2) 힌트를 사용하면 정상적으로 card=1 의 결과를 얻을 수 있다.

 

-- 1. all_rows/UNION ALL

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

DB_FILE_MULTIBLOCK_READ_COUNT = 32

QUERY
SELECT /*+ all_rows */ 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DUAL   Alias: DUAL
  TOTAL ::  (NOT ANALYZED)    CDN: 4072  NBLKS:  100  AVG_ROW_LEN:  100
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DUAL     ORIG CDN: 4072  ROUNDED CDN: 4072  CMPTD CDN: 4072
  Access path: tsc  Resc:  8  Resp:  8
  BEST_CST: 8.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          8  CDN:       4072  BYTES:          0
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 8  CDN: 4072  RSC: 8  RSP: 8  BYTES: 0
  IO-RSC: 8  IO-RSP: 8  CPU-RSC: 0  CPU-RSP: 0

 

-- 2. first_rows_1/UNION ALL

QUERY
SELECT /*+ first_rows(1) */ 4 C FROM DUAL UNION ALL SELECT /*+ first_rows(1) */ 6 C FROM DUAL

-- 2.1 case 1
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: DUAL   Alias: DUAL
  TOTAL ::  (NOT ANALYZED)    CDN: 4072  NBLKS:  100  AVG_ROW_LEN:  100
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: DUAL     ORIG CDN: 4072  ROUNDED CDN: 4072  CMPTD CDN: 4072
  Access path: tsc  Resc:  8  Resp:  8
  BEST_CST: 8.00  PATH: 2  Degree:  1

-- 2.2 case 2
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
  TABLE: DUAL     ORIG CDN: 1  ROUNDED CDN: 1  CMPTD CDN: 1
  Access path: tsc  Resc:  2  Resp:  2
  BEST_CST: 2.00  PATH: 2  Degree:  1
***********************

-- 2.3 final Decision
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          2  CDN:          1  BYTES:          0
Final - First K Rows Plan:
  JOIN ORDER: 1
  CST: 2  CDN: 1  RSC: 2  RSP: 2  BYTES: 0
  IO-RSC: 2  IO-RSP: 2  CPU-RSC: 0  CPU-RSP: 0
  First K Rows Plan

--3. all_rows/UNION

QUERY
SELECT /*+ all_rows */ 6 C FROM DUAL UNION  SELECT 7 C FROM DUAL

***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          8  CDN:       4072  BYTES:          0
    SORT resource      Sort statistics
      Sort width:            3 Area size:       43008 Max Area size:       43008   Degree: 1
      Blocks to Sort:       10 Row size:           10 Rows:       4072
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         14
      Total IO sort cost: 12
      Total CPU sort cost: 0
      Total Temp space used: 0
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 20  CDN: 4072  RSC: 20  RSP: 20  BYTES: 0
  IO-RSC: 20  IO-RSP: 20  CPU-RSC: 0  CPU-RSP: 0

--4. first_rows_1/UNION

-- UNION은 SORT OPERATION이 수행되므로 이 경우 CBO는 'All Rows Plan'만 고려되었음.

QUERY
SELECT /*+ first_rows(1) */ 8 C FROM DUAL UNION SELECT /*+ first_rows(1) */ 9 C FROM DUAL

***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  DUAL[DUAL]#0
Best so far: TABLE#: 0  CST:          8  CDN:       4072  BYTES:          0
    SORT resource      Sort statistics
      Sort width:            3 Area size:       43008 Max Area size:       43008   Degree: 1
      Blocks to Sort:       10 Row size:           10 Rows:       4072
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         14
      Total IO sort cost: 12
      Total CPU sort cost: 0
      Total Temp space used: 0
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 20  CDN: 4072  RSC: 20  RSP: 20  BYTES: 0
  IO-RSC: 20  IO-RSP: 20  CPU-RSC: 0  CPU-RSP: 0

 

 

-- 10.2.0.3 (FAST DUAL)

-- optimizer_mode, union all 혹은 union, _optimizer_cost_model=(io,cpu) 에 관계없이 모두 card=1
-- (dual의 통계정보 존재 여부와도 상관 없음)
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

****************
QUERY BLOCK TEXT
****************
SELECT 1 FROM DUAL
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$2 nbfros=1 flg=0
  fro(0): flg=0 objn=258 hint_alias="DUAL"@"SEL$2"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using WORKLOAD Stats
  CPUSPEED: 1023 millions instructions/sec
  SREADTIM: 1 milliseconds
  MREADTIM: 1 millisecons
  MBRC: 16.000000 blocks
  MAXTHR: 525863936 bytes/sec
  SLAVETHR: 3824640 bytes/sec
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: DUAL  Alias: DUAL
    #Rows: 1  #Blks:  1  AvgRowLen:  2.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: DUAL  Alias: DUAL    
    Card: Original: 1  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  2.01  Resp: 2.01  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7271
      Resp_io: 2.00  Resp_cpu: 7271
  Best:: AccessPath: TableScan
         Cost: 2.01  Degree: 1  Resp: 2.01  Card: 1.00  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  DUAL[DUAL]#0
***********************
Best so far: Table#: 0  cost: 2.0065  card: 1.0000  bytes: 0
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 2.0065  Degree: 1  Card: 1.0000  Bytes: 0
  Resc: 2.0065  Resc_io: 2.0000  Resc_cpu: 7271
  Resp: 2.0065  Resp_io: 2.0000  Resc_cpu: 7271

전통적으로 index 힌트를 사용하는 방법은 다음과 같다.

select /*+ index(t_index t_index_idx1) */ count(*)
from t_index
where c1 > 0 and c2 > 0;


이 방식의 한 가지 단점은 Index 이름이 변경되는 경우에는 해당 인덱스를 사용하지 못할 수도 있다는 것이다. Oracle 10g에서는 다음과 같은 새로운 Syntax가 추가되었다.

select /*+ index(t_index t_index(c1)) */ count(*)
from t_index
where c1 > 0 and c2 > 0;


즉, index 이름 대신 Index를 구성하는 컬럼명을 사용할 수 있다. 다중 컬럼 인덱스인 경우에는 /*+ index(t_index t_index(c1,c2,c3)) */ 와 같이 사용할 수 있다.

Index의 이름이 변경될 때의 피해도 줄일수 있고, 힌트도 훨씬 명확해지는 장점이 있다. 어떤 컬럼을 사용하는 인덱스인지 힌트를 보고 바로 알 수 있다.

10.2.0.3 버전에서는 아래와 같은 힌트가 추가되었다.

    * index_rs
    * index_rs_asc
    * index_rs_desc

Index range scan 전용 힌트가 추가된 셈이다. Optimizer의 일부 문제로 인해 Index 힌트를 사용하는 경우 index range scan이 효율적임에도 불구하고 index full scan(index fast full scan과 헷갈리면 안됨!!)이 선택되는 문제가 발견되었다. 그래서 이런 문제를 원천적으로 해결할 수 있도록 새로운 힌트가 추가된 것이다.

10g 사용자라면 이러한 새로운 힌트를 사용함으로써 index 힌트 사용에서 오는 일부 껄끄러운 문제를 손쉽게 해결할 수 있을 듯하다.