Optimizing Oracle Optimizer (2009년)
통계정보 조작 - 실사례 0 0 52,968

by 구루비스터디 Cardinility [2018.07.14]


  1. Cardinality 제어하기
  2. 1. 시나리오
  3. 2. 테스트
    1. 1) 테스트 데이터 만들기
    2. 2) 통계정보 확인
    3. 3) 실행계획 확인
    4. 4) C1 컬럼에 10건 'Y' 값을 Insert함
    5. 5) 다시 실행계획 테스트
    6. 6) C1 컬럼 통계정보 변경
    7. 7) 플랜 확인
    8. 8) 다시 플랜 확인
    9. 9) Active Session에 '통계정보 갱신'과 '통계정보 변경' 테스트
  4. 3. 정리
    1. 1) 통계정보 변경 관련 프로시저
    2. 2) 통계정보 변경에 대한 내용 정리
  5. 4. 추가 테스트
    1. 1) 추가 테스트


Cardinality 제어하기

  • Cardinality는 오라클이 실행계획을 수립할 때 가장 중요한 정보로 사용을 하며, 그렇기 때문에 실행계획 예측정보에서 Cardinality 값이 정확하게 나오는것이 중요하다.
  • 하지만 정말 운이 좋은 경우에서만 Cardinality 값을 정확하게 예측할 수 있고 대부분은 정확한 예측을 할 수 없다.
  • 그렇다면 힌트를 사용하여 Plan을 고정시키지 않고 오라클이 정확한 Cardinality를 예측할 수 있도록 유도하는 방법은 없는가?


구분설명
1. 통계정보조작
  • DBMS_STATS Package를 이용하면 통계정보를 조작할 수 있고,

    Cardinality를 변경할 수 있다.
2. CARDINALITY Hint
  • 힌트를 이용하여 Cardinality 값을 변경할 수 있다.
3. OPT_ESTIMATE Hint
  • Scale Factor를 이용하여 Cardinality를 변경할 수 있다.
4. Dynamic Sampling
  • 실시간으로 Sampling을 수행하여 Cardinality를 계산할 수 있다.


1. 시나리오

  • 1) 'INDEX FULL SCAN'을 하는 SQL에 통계정보 갱신과 통계정보 조작을 하여 PLAN 변경여부 확인
  • 2) Active Session에 통계정보 갱신과 통계정보 조작을 하여 cursor가 invalidate 되는지 확인


2. 테스트

1) 테스트 데이터 만들기


-- create object
DROP TABLE XSOFT_T PURGE;

CREATE TABLE XSOFT_T(C1 CHAR(1),
                     C2 NUMBER
                     );

CREATE INDEX XSOFT_T_N1 ON XSOFT_T(C1);
CREATE INDEX XSOFT_T_N2 ON XSOFT_T(C2);

-- create data
INSERT INTO XSOFT_T
SELECT NULL, -- 일부로 NULL값으로 채움
       LEVEL -- DISTINCT를 1,000,000 으로 생성
FROM   DUAL
CONNECT BY LEVEL <= 10000000
;

COMMIT;

-- gather stats "without" histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'XSOFT_T', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);


2) 통계정보 확인


-- Table
SELECT TABLE_NAME,
       NUM_ROWS,
       BLOCKS,
       SAMPLE_SIZE,
       TO_CHAR(LAST_ANALYZED, 'YYYY/MM/DD HH24:MI:SS') AS LAST_ANAL
FROM   USER_TAB_STATISTICS
WHERE  TABLE_NAME = UPPER('XSOFT_T')
;


TABLE_NAMENUM_ROWSBLOCKSSAMPLE_SIZELAST_ANAL
XSOFT_T10024252164065509572009/03/25 20:16:22



-- Column
SELECT S.TABLE_NAME,
       S.COLUMN_NAME,
       S.NUM_DISTINCT,
       S.NUM_NULLS,
       S.DENSITY,
       S.LOW_VALUE,
       S.HIGH_VALUE,
       S.HISTOGRAM
FROM   USER_TAB_COLS S
WHERE  S.TABLE_NAME = UPPER('XSOFT_T')
;


TABLE_NAMECOLUMN_NAMENUM_DISTINCTNUM_NULLSDENSITYLOW_VALUEHIGH_VALUEHISTOGRAM
XSOFT_TC21002425209.9758E-08C21950C40A643943NONE
XSOFT_TC10100242520NONE



-- Histogram
SELECT TABLE_NAME,
       COLUMN_NAME,
       ENDPOINT_NUMBER,
       ENDPOINT_VALUE || '(' || ENDPOINT_ACTUAL_VALUE || ')' AS ENDPOINT_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = UPPER('XSOFT_T')
ORDER  BY COLUMN_NAME,
          ENDPOINT_NUMBER
;


TABLE_NAMECOLUMN_NAMEENDPOINT_NUMBERENDPOINT_VALUE
XSOFT_TC202479()
XSOFT_TC219995666()


3) 실행계획 확인


VAR B1 NUMBER;
VAR B2 NUMBER;

EXEC :B1 := 1;
EXEC :B2 := 1;

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(1)
FROM   XSOFT_T
WHERE  C2 IN (SELECT C2
              FROM   XSOFT_T
              WHERE  C2 = :B1 -- 1
              )
AND    C1 IS NOT NULL
;

  COUNT(1)
----------
         0

1 row selected.
Elapsed: 00:00:00.03

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'IOSTATS COST LAST'));

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |            |      1 |      1 |            |      1 |00:00:00.01 |       1 |      1 |
|   2 |   NESTED LOOPS SEMI           |            |      1 |      1 |     2   (0)|      0 |00:00:00.01 |       1 |      1 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XSOFT_T    |      1 |      1 |     0   (0)|      0 |00:00:00.01 |       1 |      1 |
|*  4 |     INDEX FULL SCAN           | XSOFT_T_N1 |      1 |      1 |     0   (0)|      0 |00:00:00.01 |       1 |      1 |
|*  5 |    INDEX RANGE SCAN           | XSOFT_T_N2 |      0 |      1 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("C2"=:B1)
   4 - filter("C1" IS NOT NULL)
   5 - access("C2"=:B1)
       filter("C2"="C2")
;


4) C1 컬럼에 10건 'Y' 값을 Insert함


INSERT INTO XSOFT_T
SELECT 'Y',
       LEVEL + 10000000
FROM   DUAL
CONNECT BY LEVEL <= 1000000
;

COMMIT;


5) 다시 실행계획 테스트


VAR B1 NUMBER;

EXEC :B1 := 10000001;

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(1)
FROM   XSOFT_T
WHERE  C2 IN (SELECT C2
              FROM   XSOFT_T
              WHERE  C2 = :B1 -- 10000001
              )
AND    C1 IS NOT NULL
;

  COUNT(1)
----------
         1

1 row selected.

Elapsed: 00:00:02.81

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'IOSTATS COST LAST'));

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |            |      1 |      1 |            |      1 |00:00:02.71 |    7847 |   4835 |
|   2 |   NESTED LOOPS SEMI           |            |      1 |      1 |     2   (0)|      1 |00:00:02.71 |    7847 |   4835 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XSOFT_T    |      1 |      1 |     0   (0)|      1 |00:00:02.71 |    7844 |   4834 |
|*  4 |     INDEX FULL SCAN           | XSOFT_T_N1 |      1 |      1 |     0   (0)|   1000K|00:00:01.00 |    6054 |   3044 |
|*  5 |    INDEX RANGE SCAN           | XSOFT_T_N2 |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |      1 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("C2"=:B1)
   4 - filter("C1" IS NOT NULL)
   5 - access("C2"=:B1)
       filter("C2"="C2")


6) C1 컬럼 통계정보 변경


-- 현재 XSOFT_T.C1 컬럼 정보
SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       NUM_DISTINCT,
       DENSITY,
       NUM_NULLS,
       AVG_COL_LEN
FROM   DBA_TAB_COLUMNS
WHERE  OWNER       = USER
AND    TABLE_NAME  = 'XSOFT_T'
AND    COLUMN_NAME = 'C1'
;

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY  NUM_NULLS AVG_COL_LEN
----- ---------- ----------- ------------ ------- ---------- -----------
USER  XSOFT_T    C1                     0       0   10024252           1



-- 통계정보 변경
BEGIN
    DBMS_STATS.SET_COLUMN_STATS(OWNNAME => USER,
                                TABNAME => 'XSOFT_T',
                                COLNAME => 'C1',
                                DISTCNT => 1,
                                DENSITY => 0.1,
                                NULLCNT => 10024252,
                                AVGCLEN => 1
                                );
END;
/

-- 통계정보 변경 확인
SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       NUM_DISTINCT,
       DENSITY,
       NUM_NULLS,
       AVG_COL_LEN
FROM   DBA_TAB_COLUMNS
WHERE  OWNER       = USER
AND    TABLE_NAME  = 'XSOFT_T'
AND    COLUMN_NAME = 'C1'
;


OWNERTABLE_NAMECOLUMN_NAMENUM_DISTINCTDENSITYNUM_NULLSAVG_COL_LEN
USERXSOFT_TC11.1100242521


7) 플랜 확인


VAR B1 NUMBER;

EXEC :B1 := 10000001;

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(1)
FROM   XSOFT_T
WHERE  C2 IN (SELECT C2
              FROM   XSOFT_T
              WHERE  C2 = :B1 -- 10000001
              )
AND    C1 IS NOT NULL
;

  COUNT(1)
----------
         1

1 row selected.
Elapsed: 00:00:01.56

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'IOSTATS COST LAST'));

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |            |      1 |      1 |            |      1 |00:00:01.51 |    7072 |
|   2 |   NESTED LOOPS SEMI           |            |      1 |      1 |     2   (0)|      1 |00:00:01.51 |    7072 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XSOFT_T    |      1 |      1 |     0   (0)|      1 |00:00:01.51 |    7069 |
|*  4 |     INDEX FULL SCAN           | XSOFT_T_N1 |      1 |      1 |     0   (0)|   1000K|00:00:01.00 |    5279 |
|*  5 |    INDEX RANGE SCAN           | XSOFT_T_N2 |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("C2"=:B1)
   4 - filter("C1" IS NOT NULL)
   5 - access("C2"=:B1)
       filter("C2"="C2")
;


  • 수행결과 플랜이 변경되야 하는지 변경이 되지 않았다.
  • 그래서 그 이유를 살펴보니 컬럼의 통계정보는 변경되었지만 인덱스 통계정보에 있는 DISTINCT값이 0이기 때문이었다.



-- 인덱스 통계정보 확인
SELECT OWNER,
       INDEX_NAME,
       NUM_ROWS,
       DISTINCT_KEYS,
       CLUSTERING_FACTOR,
       BLEVEL,
       LEAF_BLOCKS
FROM   DBA_INDEXES
WHERE  TABLE_NAME  = 'XSOFT_T'
AND    TABLE_OWNER = USER
ORDER  BY OWNER,
          INDEX_NAME
;


OWNERINDEX_NAMENUM_ROWSDISTINCT_KEYSCLUSTERING_FACTORBLEVELLEAF_BLOCKS
USERXSOFT_T_N1{*}0{*}{*}0{*}{*}0{*}{*}0{*}{*}0{*}
USERXSOFT_T_N110198899996982836719221559



-- 통계정보 변경
BEGIN
    DBMS_STATS.SET_INDEX_STATS(OWNNAME  => USER,
                               INDNAME  => 'XSOFT_T_N1',
                               NUMROWS  => 1000000,
                               NUMLBLKS => 100,
                               NUMDIST  => 1000000,
                               CLSTFCT  => 200,
                               INDLEVEL => 2
                               );
END;
/

-- 인덱스 통계정보 확인
SELECT OWNER,
       INDEX_NAME,
       NUM_ROWS,
       DISTINCT_KEYS,
       CLUSTERING_FACTOR,
       BLEVEL,
       LEAF_BLOCKS
FROM   DBA_INDEXES
WHERE  TABLE_NAME  = 'XSOFT_T'
AND    TABLE_OWNER = USER
ORDER  BY OWNER,
          INDEX_NAME
;


OWNERINDEX_NAMENUM_ROWSDISTINCT_KEYSCLUSTERING_FACTORBLEVELLEAF_BLOCKS
USERXSOFT_T_N1{*}1000000{*}{*}1000000{*}{*}200{*}{*}2{*}{*}100{*}
USERXSOFT_T_N110198899996982836719221559


8) 다시 플랜 확인


-- XPLAN.DISPLAY 먼저 확인
EXPLAIN PLAN FOR
SELECT COUNT(1)
FROM   XSOFT_T
WHERE  C2 IN (SELECT C2
              FROM   XSOFT_T
              WHERE  C2 = :B1 -- 10000001
              )
AND    C1 IS NOT NULL
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    12 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |            |     1 |    12 |            |          |
|   2 |   NESTED LOOPS SEMI           |            |     1 |    12 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XSOFT_T    |     1 |     6 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | XSOFT_T_N2 |     1 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | XSOFT_T_N2 |     1 |     6 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("C1" IS NOT NULL)
   4 - access("C2"=TO_NUMBER(:B1))
   5 - access("C2"=TO_NUMBER(:B1))
       filter("C2"="C2")
;

-- XPLAN.DISPLAY_CURSOR 확인
VAR B1 NUMBER;

EXEC :B1 := 10000001;

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(1)
FROM   XSOFT_T
WHERE  C2 IN (SELECT C2
              FROM   XSOFT_T
              WHERE  C2 = :B1 -- 10000001
              )
AND    C1 IS NOT NULL
;

  COUNT(1)
----------
         1

1 row selected.
Elapsed: 00:00:00.03

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'IOSTATS COST LAST'));

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |            |      1 |      1 |            |      1 |00:00:00.01 |       7 |
|   2 |   NESTED LOOPS SEMI           |            |      1 |      1 |     6   (0)|      1 |00:00:00.01 |       7 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XSOFT_T    |      1 |      1 |     4   (0)|      1 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | XSOFT_T_N2 |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | XSOFT_T_N2 |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("C1" IS NOT NULL)
   4 - access("C2"=:B1)
   5 - access("C2"=:B1)
       filter("C2"="C2")
;


9) Active Session에 '통계정보 갱신'과 '통계정보 변경' 테스트

① 시나리오
  • 통계정보 변경 이전의 SQL을 가지고 1,000회 Looping을 수행하여 '통계정보 갱신'과 '통계정보 변경' 테스트를 한다.
  • 테스트 결과는 Trace로 확인한다.
  • SQL invalid를 정확히 테스트하기 위해 테스트 중간에 'ALTER SYSTEM FLUSH SHARED_POOL' 구문을 실행한다.
  • 목표는 '통계정보 갱신'과 '통계정보 변경'이 Active Session에게 어떤 영향을 주는지 파악하는 것임


② 테스트 데이터 생성 스크립트

-- create object
DROP TABLE XSOFT_T PURGE;

CREATE TABLE XSOFT_T(C1 CHAR(1),
                     C2 NUMBER
                     );

CREATE INDEX XSOFT_T_N1 ON XSOFT_T(C1);
CREATE INDEX XSOFT_T_N2 ON XSOFT_T(C2);

-- create data
INSERT INTO XSOFT_T
SELECT NULL, -- 일부로 NULL값으로 채움
       LEVEL -- DISTINCT를 1,000,000 으로 생성
FROM   DUAL
CONNECT BY LEVEL <= 10000000
;

COMMIT;

-- gather stats "without" histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'XSOFT_T', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);

-- add 1,000,000 data
INSERT INTO XSOFT_T
SELECT 'Y',
       LEVEL + 10000000
FROM   DUAL
CONNECT BY LEVEL <= 1000000
;

COMMIT;


③ 세션에서 수행할 SQL

ALTER SYSTEM FLUSH SHARED_POOL; -- 모든 cursor invalidate 시킴

DECLARE
    val_1 NUMBER;
    p_val NUMBER := 10000001;

    v_sid           NUMBER;
    v_sql_id        VARCHAR2(1000);
    v_object_status VARCHAR2(1000);

    v_num_rows           NUMBER;
    v_distinct_keys      NUMBER;
    v_clustering_factor  NUMBER;
    v_blevel             NUMBER;
    v_leaf_blocks        NUMBER;

    CURSOR cur_val IS
        -- 1000번 looping
        SELECT LEVEL CNT
        FROM   DUAL
        CONNECT BY LEVEL <= 1000;

BEGIN
    SELECT S.SID
    INTO   v_sid
    FROM   V$PROCESS P,
           V$SESSION S
    WHERE  P.ADDR = S.PADDR
    AND    S.AUDSID = USERENV('SESSIONID');

    FOR rec_val IN cur_val LOOP
        SELECT COUNT(1)
        INTO   val_1
        FROM   XSOFT_T
        WHERE  C2 IN (SELECT C2
                      FROM   XSOFT_T
                      WHERE  C2 = p_val
                      )
        AND    C1 IS NOT NULL
        ;

        SELECT S.SQL_ID,
               S.OBJECT_STATUS
        INTO   v_sql_id,
               v_object_status
        FROM   V$SESSION SES,
               V$SQL     S
        WHERE  SES.SQL_ID = S.SQL_ID
        AND    SES.SID    = v_sid
        AND    ROWNUM = 1
        ;

        SELECT NUM_ROWS,
               DISTINCT_KEYS,
               CLUSTERING_FACTOR,
               BLEVEL,
               LEAF_BLOCKS
        INTO   v_num_rows,
               v_distinct_keys,
               v_clustering_factor,
               v_blevel,
               v_leaf_blocks
        FROM   DBA_INDEXES
        WHERE  TABLE_NAME  = 'XSOFT_T'
        AND    INDEX_NAME  = 'XSOFT_T_N1'
        AND    TABLE_OWNER = USER
        ORDER  BY OWNER,
                  INDEX_NAME
        ;

        DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec_val.cnt, '999,999') || ', ' ||
                             v_sql_id                        || ', ' ||
                             v_object_status                 || ', ' ||
                             v_num_rows                      || ', ' ||
                             v_distinct_keys                 || ', ' ||
                             v_clustering_factor             || ', ' ||
                             v_blevel                        || ', ' ||
                             v_leaf_blocks);
    END LOOP;
END;
/


④ 인덱스 통계정보 즉시 갱신

BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(OWNNAME       => USER,
                                  INDNAME       => 'XSOFT_T_N1',
                                  NO_INVALIDATE => FALSE -- 즉시 변경
                                  );
END;


⑤ 결과


출력결과
CNTSQL_IDOBJECT_STATUSNUM_ROWSDISTINCT_KEYSCLUSTERING_FACTORBLEVELLEAF_BLOCKS
153vphkmzq39pjVALID00000
........................
........................
........................
{*}115{*}{*}53vphkmzq39pj{*}{*}VALID{*}{*}0{*}{*}0{*}{*}0{*}{*}0{*}{*}0{*}
{*}116{*}{*}53vphkmzq39pj{*}{*}VALID{*}{*}1000000{*}{*}1{*}{*}1790{*}{*}2{*}{*}2968{*}
........................
........................
........................
100053vphkmzq39pjVALID10000001179022968


트레이스

SELECT COUNT(1)
FROM
 XSOFT_T WHERE C2 IN (SELECT C2 FROM XSOFT_T WHERE C2 = :B1 ) AND C1 IS NOT
  NULL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.05       0.06          0          0          0           0
Fetch     1000    171.98     168.14          0     561615          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001    172.03     168.20          0     561615          0        1000

Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    884  SORT AGGREGATE (cr=6188 pr=0 pw=0 time=68037 us)
    884   NESTED LOOPS SEMI (cr=6188 pr=0 pw=0 time=56231 us)
    884    TABLE ACCESS BY INDEX ROWID XSOFT_T (cr=3536 pr=0 pw=0 time=30185 us)
    884     INDEX RANGE SCAN XSOFT_T_N2 (cr=2652 pr=0 pw=0 time=21079 us)(object id 6144373)
    884    INDEX RANGE SCAN XSOFT_T_N2 (cr=2652 pr=0 pw=0 time=10784 us)(object id 6144373)


⑥ 인덱스 통계정보 차후 변경

BEGIN
    DBMS_STATS.SET_INDEX_STATS(OWNNAME  => USER,
                               INDNAME  => 'XSOFT_T_N1',
                               NUMROWS  => 1000000,
                               NUMLBLKS => 100,
                               NUMDIST  => 1000000,
                               CLSTFCT  => 200,
                               INDLEVEL => 2
                               );
END;


⑦ 결과


출력결과(건당 수행시간이 오래걸려 1,000회에서 100회로 줄임)
CNTSQL_IDOBJECT_STATUSNUM_ROWSDISTINCT_KEYSCLUSTERING_FACTORBLEVELLEAF_BLOCKS
153vphkmzq39pjVALID00000
........................
........................
........................
{*}25{*}{*}53vphkmzq39pj{*}{*}VALID{*}{*}0{*}{*}0{*}{*}0{*}{*}0{*}{*}0{*}
{*}26{*}{*}53vphkmzq39pj{*}{*}VALID{*}{*}1000000{*}{*}1000000{*}{*}200{*}{*}2{*}{*}100{*}
........................
........................
........................
10053vphkmzq39pjVALID100000010000002002100


트레이스

SELECT COUNT(1)
FROM
 XSOFT_T WHERE C2 IN (SELECT C2 FROM XSOFT_T WHERE C2 = :B1 ) AND C1 IS NOT
  NULL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute    100      0.01       0.01          0          0          0           0
Fetch      100    145.69     142.40          0     479184          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201    145.71     142.41          0     479184          0         100

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  SORT AGGREGATE (cr=479184 pr=0 pw=0 time=142402476 us)
    100   NESTED LOOPS SEMI (cr=479184 pr=0 pw=0 time=142399408 us)
    100    TABLE ACCESS BY INDEX ROWID XSOFT_T (cr=478884 pr=0 pw=0 time=142392928 us)
100000000     INDEX FULL SCAN XSOFT_T_N1 (cr=299884 pr=0 pw=0 time=100002768 us)(object id 6144460)
    100    INDEX RANGE SCAN XSOFT_T_N2 (cr=300 pr=0 pw=0 time=2461 us)(object id 6144461)


⑧ 인덱스 통계정보 즉시 변경

BEGIN
    DBMS_STATS.SET_INDEX_STATS(OWNNAME       => USER,
                               INDNAME       => 'XSOFT_T_N1',
                               NUMROWS       => 1000000,
                               NUMLBLKS      => 100,
                               NUMDIST       => 1000000,
                               CLSTFCT       => 200,
                               INDLEVEL      => 2,
                               NO_INVALIDATE => FALSE -- 즉시 변경
                               );
END;
/


⑨ 결과


출력결과
CNTSQL_IDOBJECT_STATUSNUM_ROWSDISTINCT_KEYSCLUSTERING_FACTORBLEVELLEAF_BLOCKS
153vphkmzq39pjVALID00000
........................
........................
........................
{*}124{*}{*}53vphkmzq39pj{*}{*}VALID{*}{*}0{*}{*}0{*}{*}0{*}{*}0{*}{*}0{*}
{*}125{*}{*}53vphkmzq39pj{*}{*}VALID{*}{*}1000000{*}{*}1000000{*}{*}200{*}{*}2{*}{*}100{*}
........................
........................
........................
100053vphkmzq39pjVALID100000010000002002100


트레이스

SELECT COUNT(1)
FROM
 XSOFT_T WHERE C2 IN (SELECT C2 FROM XSOFT_T WHERE C2 = :B1 ) AND C1 IS NOT
  NULL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.06       0.06          0          0          0           0
Fetch     1000    184.63     180.47          0     604428          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001    184.69     180.54          0     604428          0        1000

Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    875  SORT AGGREGATE (cr=6125 pr=0 pw=0 time=68991 us)
    875   NESTED LOOPS SEMI (cr=6125 pr=0 pw=0 time=57064 us)
    875    TABLE ACCESS BY INDEX ROWID XSOFT_T (cr=3500 pr=0 pw=0 time=30890 us)
    875     INDEX RANGE SCAN XSOFT_T_N2 (cr=2625 pr=0 pw=0 time=22000 us)(object id 6144464)
    875    INDEX RANGE SCAN XSOFT_T_N2 (cr=2625 pr=0 pw=0 time=10597 us)(object id 6144464)


3. 정리

1) 통계정보 변경 관련 프로시저

① 테이블

DBMS_STATS.SET_TABLE_STATS(OWNNAME       VARCHAR2,
                           TABNAME       VARCHAR2,
                           PARTNAME      VARCHAR2 DEFAULT NULL,
                           STATTAB       VARCHAR2 DEFAULT NULL,
                           STATID        VARCHAR2 DEFAULT NULL,
                           NUMROWS       NUMBER   DEFAULT NULL,
                           NUMBLKS       NUMBER   DEFAULT NULL,
                           AVGRLEN       NUMBER   DEFAULT NULL,
                           FLAGS         NUMBER   DEFAULT NULL,
                           STATOWN       VARCHAR2 DEFAULT NULL,
                           NO_INVALIDATE BOOLEAN  DEFAULT TO_NO_INVALIDATE_TYPE(GET_PARAM('NO_INVALIDATE')),
                           CACHEDBLK     NUMBER   DEFAULT NULL,
                           CACHEHIT      NUMBER   DEFAULT NULL,
                           FORCE         BOOLEAN  DEFAULT FALSE);


② 컬럼

DBMS_STATS.SET_COLUMN_STATS(OWNNAME       VARCHAR2,
                            TABNAME       VARCHAR2,
                            COLNAME       VARCHAR2,
                            PARTNAME      VARCHAR2 DEFAULT NULL,
                            STATTAB       VARCHAR2 DEFAULT NULL,
                            STATID        VARCHAR2 DEFAULT NULL,
                            DISTCNT       NUMBER   DEFAULT NULL,
                            DENSITY       NUMBER   DEFAULT NULL,
                            NULLCNT       NUMBER   DEFAULT NULL,
                            SREC STATREC           DEFAULT NULL,
                            AVGCLEN       NUMBER   DEFAULT NULL,
                            FLAGS         NUMBER   DEFAULT NULL,
                            STATOWN       VARCHAR2 DEFAULT NULL,
                            NO_INVALIDATE BOOLEAN  DEFAULT TO_NO_INVALIDATE_TYPE(GET_PARAM('NO_INVALIDATE')),
                            FORCE         BOOLEAN   DEFAULT FALSE);


③ 인덱스

DBMS_STATS.SET_INDEX_STATS(OWNNAME       VARCHAR2,
                           INDNAME       VARCHAR2,
                           PARTNAME      VARCHAR2 DEFAULT NULL,
                           STATTAB       VARCHAR2 DEFAULT NULL,
                           STATID        VARCHAR2 DEFAULT NULL,
                           NUMROWS       NUMBER   DEFAULT NULL,
                           NUMLBLKS      NUMBER   DEFAULT NULL,
                           NUMDIST       NUMBER   DEFAULT NULL,
                           AVGLBLK       NUMBER   DEFAULT NULL,
                           AVGDBLK       NUMBER   DEFAULT NULL,
                           CLSTFCT       NUMBER   DEFAULT NULL,
                           INDLEVEL      NUMBER   DEFAULT NULL,
                           FLAGS         NUMBER   DEFAULT NULL,
                           STATOWN       VARCHAR2 DEFAULT NULL,
                           NO_INVALIDATE BOOLEAN  DEFAULT TO_NO_INVALIDATE_TYPE(GET_PARAM('NO_INVALIDATE')),
                           GUESSQ        NUMBER   DEFAULT NULL,
                           CACHEDBLK     NUMBER   DEFAULT NULL,
                           CACHEHIT      NUMBER   DEFAULT NULL,
                           FORCE         BOOLEAN  DEFAULT FALSE);


④ 주요 파라미터 내용(기준은 FND_STATS이나 FND_STATS이 DBMS_STATS을 호출하는 구조이므로 비슷함)
파라미터기본값설명
invalidate = 'Y''Y'생성 즉시 Cursor를 Invalid 시킴
invalidate = 'N''Y'통계정보 갱신을 해도 Cursor를 Invalid 시키지 않음

'_optimizer_invalidation_period=18000'에 의해 임의 시점에 적용됨
cascade = TRUETRUETable, Index, Column, Histogram 모두 생성
cascade = FLASETRUETable, Column, Histogram 만 통계정보 생성
granularity ='DEFAULT''DEFAULT'Partition Table 시 1차 파티션만 통계정보 수행.

SubPartition 통계정보 수행 안함.
granularity ='PARTITION''DEFAULT'Partition Table 시 1차 파티션만 통계정보 수행.

SubPartition 통계정보 수행 안함.
granularity ='ALL''DEFAULT'Partition Table 시 1차/2차 파티션만 통계정보 수행


2) 통계정보 변경에 대한 내용 정리

  • 통계정보의 값을 임의로 변경하여 신속한 대처를 할 수 있다. 하지만 그로 인해 PLAN 변경의 영향을 받는 프로그램이 다수 생길 수 있으므로 조심해야 한다.
  • 통계정보 갱신 또는 변경을 할 ? 메모리에 있는 cursor를 즉시 invalidate 할 수도 있고 안 할 수도 있다.
  • DBMS_STATS 패키지를 사용하면 해당 Object를 사용하는 cursor를 선택적으로 invalidate 할 수 있는

파라미터가 있으나

{+}ANALYZE 명령어는 무조건 invalidate{+}를 시키므로 주의해서 사용해야 한다.

  • cursor invalidate 시킬 때 반드시 알아야 할 사항들(reference : http://ukja.tistory.com/78)
    • 통계 정보 수집으로 통계 정보가 변경된다.
    • 통계 정보가 변경되면 관련된 SQL Cursor들이 invalidate된다.
    • SQL Cursor가 invalidate되면 다음 번 Access때 hard parse가 발생한다.
    • hard parse시에는 LCO에 대해 library cache pin을 exclusive모드로 획득한다.
    • hard parse가 진행 중인 LCO를 실행하려는 다른 session들은 library cache pin 이벤트를 대기한다.
    • 이로 인해 서버에 wait event가 다수 생기게 되고 결국 서버를 내려야 하는 상황을 초래할 수 있다.
  • 통계정보 변경 또는 갱신 시 NO_INVALIDATE 값을 'TRUE'로 하게 되면?(cursor를 invalidate 하지 않음)
    • 히든 파라미터중 '_optimizer_invalidation_period' 값에 의해 random으로 invalidate 됨
    • 기본값은 18000초로 이 시간 이후에 invalidate 되는 것이 아니라 이 시간 사이에 invalidate 됨
  • 그렇다면 모든 cursor를 invalidate 시키지 않고 특정 cursor만 invalidate 하려면?(reference : http://ukja.tistory.com/122)
    • 10.2.0.4 버전부터 'SYS.DBMS_SHARED_POOL.PURGE'를 이용하여 개별 cursor를 invalidate 할 수 있다.


  • example

UKJA@ukja11> select * from t_plan where c1 = 'X';

no rows selected

Elapsed: 00:00:00.00
UKJA@ukja11> select sql_id, address, hash_value
  2  from v$sql
  3  where sql_text like 'select * from t_plan where c1%';

SQL_ID                                  ADDRESS  HASH_VALUE
--------------------------------------- -------- ----------
bp49t90rx4nvf                           2269CB44  802313070

1 row selected.

Elapsed: 00:00:00.09

UKJA@ukja11> exec sys.dbms_shared_pool.purge('2269CB44.802313070', 'C');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32
UKJA@ukja11> select sql_id, address, hash_value
  2  from v$sql
  3  where sql_text like 'select * from t_plan where c1%';

no rows selected

Elapsed: 00:00:00.06


4. 추가 테스트

  • {*}{+}위에서 SID값으로 조회한 SQL로는 정확히 해당 SQL을 찾기 어려운것을 확인함.+{*}



SELECT S.SID
INTO   v_sid
FROM   V$PROCESS P,
       V$SESSION S
WHERE  P.ADDR = S.PADDR
AND    S.AUDSID = USERENV('SESSIONID');

SELECT S.SQL_ID,
       S.OBJECT_STATUS
INTO   v_sql_id,
       v_object_status
FROM   V$SESSION SES,
       V$SQL     S
WHERE  SES.SQL_ID = S.SQL_ID
AND    SES.SID    = v_sid
AND    ROWNUM     = 1;


  • 이를 해결하기 위해 {*}+'V$SQLAREA'에서 해당 SQL을 찾는 로직으로 다시 테스트 함.+{*}


1) 추가 테스트

① A 세션 수행

ALTER SYSTEM FLUSH SHARED_POOL; -- 모든 cursor invalidate 시킴

DECLARE
    -- 테스트 변수
    val_1 VARCHAR2(1000);
    p_val NUMBER := 10000001;

    -- V$SQLAREA 저장 변수
    v_sql_text        VARCHAR2(1000);
    v_sql_id          VARCHAR2(1000);
    v_invalidations   NUMBER;
    v_hash_value      NUMBER;
    v_parse_calls     NUMBER;
    v_plan_hash_value NUMBER;

    -- DBA_INDEXES 저장 변수
    v_num_rows           NUMBER;
    v_distinct_keys      NUMBER;

    -- 1000번 looping
    CURSOR cur_val IS
        SELECT LEVEL CNT
        FROM   DUAL
        CONNECT BY LEVEL <= 1000;

BEGIN
    FOR rec_val IN cur_val LOOP
        SELECT COUNT(1)
        INTO   val_1
        FROM   XSOFT_T
        WHERE  C2 IN (SELECT C2
                      FROM   XSOFT_T
                      WHERE  C2 = p_val
                      )
        AND    C1 IS NOT NULL
        ;

        SELECT NUM_ROWS,
               DISTINCT_KEYS
        INTO   v_num_rows,
               v_distinct_keys
        FROM   DBA_INDEXES
        WHERE  TABLE_NAME  = 'XSOFT_T'
        AND    INDEX_NAME  = 'XSOFT_T_N1'
        AND    TABLE_OWNER = USER
        ORDER  BY OWNER,
                  INDEX_NAME
        ;

        DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec_val.cnt, '0999') || ', ' ||
                             v_sql_text                   || ', ' ||
                             v_sql_id                     || ', ' ||
                             v_invalidations              || ', ' ||
                             v_hash_value                 || ', ' ||
                             v_parse_calls                || ', ' ||
                             v_plan_hash_value            || ', ' ||
                             v_num_rows                   || ', ' ||
                             v_distinct_keys
                             );
    END LOOP;
END;
/


② B 세션

BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(OWNNAME       => USER,
                                  INDNAME       => 'XSOFT_T_N1',
                                  NO_INVALIDATE => FALSE -- 즉시 변경
                                  );
END;


③ C 세션에서 통계정보 변경 전과 변경 후의 'V$SQLAREA' 값 비교
구분이전이후비교
ACTIONTRUE
ACTION_HASH00TRUE
ADDRESS070000049BF985D8070000049BF985D8TRUE
APPLICATION_WAIT_TIME00TRUE
BIND_DATABEDA0...BEDA...FALSE
BUFFER_GETS212727273FALSE
CHILD_LATCH55TRUE
CLUSTER_WAIT_TIME00TRUE
COMMAND_TYPE33TRUE
CONCURRENCY_WAIT_TIME00TRUE
CPU_TIME543678337731FALSE
DIRECT_WRITES00TRUE
DISK_READS00TRUE
ELAPSED_TIME543736337731FALSE
END_OF_FETCH_COUNT4439FALSE
EXACT_MATCHING_SIGNATURE4.13E+184.13E+18TRUE
EXECUTIONS4539FALSE
FETCHES4439FALSE
FIRST_LOAD_TIME2009-04-03/11:30:372009-04-03/11:30:37TRUE
FORCE_MATCHING_SIGNATURE4.13E+184.13E+18TRUE
HASH_VALUE20399765392039976539TRUE
{*}{_}INVALIDATIONS{_}{*}{*}{_}0{_}{*}{*}{_}1{_}{*}{*}{_}FALSE{_}{*}
IS_OBSOLETENNTRUE
JAVA_EXEC_TIME00TRUE
KEPT_VERSIONS00TRUE
LAST_ACTIVE_CHILD_ADDRESS07000004AAD4CD0007000004AAD4CD00TRUE
LAST_ACTIVE_TIME2009-04-03 오전 11:31:322009-04-03 오전 11:31:53FALSE
LAST_LOAD_TIME2009-04-03 오전 11:30:372009-04-03 오전 11:31:53FALSE
LITERAL_HASH_VALUE00TRUE
LOADED_VERSIONS11TRUE
LOADS12FALSE
MODULESQL*PlusSQL*PlusTRUE
MODULE_HASH\-625018272\-625018272TRUE
OBJECT_STATUSVALIDVALIDTRUE
OLD_HASH_VALUE24436692762443669276TRUE
OPEN_VERSIONS10FALSE
OPTIMIZER_COST26FALSE
OPTIMIZER_ENVE289F...E289...TRUE
OPTIMIZER_ENV_HASH_VALUE21214716202121471620TRUE
OPTIMIZER_MODEALL_ROWSALL_ROWSTRUE
OUTLINE_CATEGORYTRUE
OUTLINE_SIDTRUE
PARSE_CALLS10FALSE
PARSING_SCHEMA_ID4444TRUE
PARSING_SCHEMA_NAMEAPPSAPPSTRUE
PARSING_USER_ID4444TRUE
PERSISTENT_MEM57045768FALSE
{*}{_}PLAN_HASH_VALUE{_}{*}{*}{_}1253868099{_}{*}{*}{_}1317217955{_}{*}{*}{_}FALSE{_}{*}
PLSQL_EXEC_TIME00TRUE
PROGRAM_ID00TRUE
PROGRAM_LINE#2525TRUE
PX_SERVERS_EXECUTIONS00TRUE
REMOTENNTRUE
ROWS_PROCESSED4439FALSE
RUNTIME_MEM47764840FALSE
SERIALIZABLE_ABORTS00TRUE
SHARABLE_MEM2321423214TRUE
SORTS00TRUE
SQL_FULLTEXT<CLOB><CLOB>TRUE
SQL_ID6judr71wtg4kv6judr71wtg4kvTRUE
SQL_PROFILETRUE
SQL_TEXTSELECT COUNT(1) FROM XSOFT_T WHERE ...SELECT COUNT(1) FROM XSOFT_T WHERE ...TRUE
USERS_EXECUTING10FALSE
USERS_OPENING00TRUE
USER_IO_WAIT_TIME00TRUE
VERSION_COUNT11TRUE


④ 'V$SQLAREA' DESCRIPTION


CNTCOLUMNDATATYPEDESCRIPTION
1ACTIONVARCHAR2(64)"Contains the name of the action that was executing at the time

that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION"
2ACTION_HASHNUMBERHash value of the action that is named in the ACTION column
3ADDRESSRAW(4 8)Address of the handle to the parent for this cursor
4APPLICATION_WAIT_TIMENUMBERApplication wait time
5BUFFER_GETSNUMBERSum of buffer gets over all child cursors
6CHILD_LATCHNUMBERChild latch number that is protecting the cursor
7CLUSTER_WAIT_TIMENUMBERCluster wait time
8COMMAND_TYPENUMBEROracle command type definition
9CONCURRENCY_WAIT_TIMENUMBERConcurrency wait time
10CPU_TIMENUMBERCPU time (in microseconds) used by this cursor for parsing/executing/fetching
11DIRECT_WRITESNUMBERSum of the number of direct writes over all child cursors
12DISK_READSNUMBERSum of the number of disk reads over all child cursors
13ELAPSED_TIMENUMBERElapsed time (in microseconds) used by this cursor for parsing/executing/fetching
14END_OF_FETCH_COUNTNUMBER"Number of times this cursor was fully executed since the cursor was brought into the library cache.

The value of this statistic is not incremented when the cursor is partially executed, either because

it failed during the execution or because only the first few rows produced by this cursor are fetched

before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT

column should be less or equal to the value of the EXECUTIONS column."
15EXECUTIONSNUMBERTotal number of executions, totalled over all the child cursors
16FETCHESNUMBERNumber of fetches associated with the SQL statement
17FIRST_LOAD_TIMEVARCHAR2(19)Timestamp of the parent creation time
18HASH_VALUENUMBERHash value of the parent statement in the library cache
19INVALIDATIONSNUMBERTotal number of invalidations over all the child cursors
20IS_OBSOLETEVARCHAR2(1)"Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number

of child cursors is too large."
21JAVA_EXEC_TIMENUMBERJava execution time
22KEPT_VERSIONSNUMBERNumber of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package
23LOADED_VERSIONSNUMBERNumber of child cursors that are present in the cache and have their context heap (KGL heap 6) loaded
24LOADSNUMBERNumber of times the object was loaded or reloaded
25MODULEVARCHAR2(64)"Contains the name of the module that was executing at the time that the SQL statement was first parsed

as set by calling DBMS_APPLICATION_INFO.SET_MODULE"
26MODULE_HASHNUMBERHash value of the module that is named in the MODULE column
27OLD_HASH_VALUENUMBEROld SQL hash value
28OPEN_VERSIONSNUMBERThe number of child cursors that are currently open under this current parent
29OPTIMIZER_MODEVARCHAR2(25)Mode under which the SQL statement was executed
30PARSE_CALLSNUMBERSum of all parse calls to all the child cursors under this parent
31PARSING_SCHEMA_IDNUMBERSchema ID that was used to parse this child cursor
32PARSING_USER_IDNUMBERUser ID of the user that has parsed the very first cursor under this parent
33PERSISTENT_MEMNUMBER"Fixed amount of memory used for the lifetime of an open cursor.

If multiple child cursors exist, the fixed sum of memory used for the lifetime of all the child cursors."
34PLSQL_EXEC_TIMENUMBERPL/SQL execution time
35PROGRAM_IDNUMBERProgram identifier
36ROWS_PROCESSEDNUMBERTotal number of rows processed on behalf of this SQL statement
37RUNTIME_MEMNUMBER"Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, the fixed

sum of all memory required during execution of all the child cursors."
38SERIALIZABLE_ABORTSNUMBERNumber of times the transaction fails to serialize, producing ORA-08177 errors, totalled over all the child cursors
39SHARABLE_MEMNUMBER"Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of

all shared memory used by all child cursors."
40SORTSNUMBERSum of the number of sorts that were done for all the child cursors
41SQL_IDVARCHAR2(13)SQL identifier of the parent cursor in the library cache
42SQL_TEXTVARCHAR2(1000)First thousand characters of the SQL text for the current cursor
43USER_IO_WAIT_TIMENUMBERUser I/O Wait Time
44USERS_EXECUTINGNUMBERTotal number of users executing the statement over all child cursors
45USERS_OPENINGNUMBERNumber of users that have any of the child cursors open
46VERSION_COUNTNUMBERNumber of child cursors that are present in the cache under this parent


⑤ 'V$SQLAREA'에서 발췌할 컬럼

SELECT SUBSTR(SQL_TEXT, 1, 30),
       SQL_ID,
       HASH_VALUE,
       INVALIDATIONS,
       PARSE_CALLS,
       PLAN_HASH_VALUE
INTO   v_sql_text,
       v_sql_id,
       v_hash_value,
       v_invalidations,
       v_parse_calls,
       v_plan_hash_value
FROM   V$SQLAREA
WHERE  UPPER(SQL_TEXT) LIKE 'SELECT COUNT(1)%';


⑥ 다시 테스트

-- 1. Sample 데이터 생성
-- create object
DROP TABLE XSOFT_T PURGE;

CREATE TABLE XSOFT_T(C1 CHAR(1),
                     C2 NUMBER
                     );

CREATE INDEX XSOFT_T_N1 ON XSOFT_T(C1);
CREATE INDEX XSOFT_T_N2 ON XSOFT_T(C2);

-- create data
INSERT INTO XSOFT_T
SELECT NULL, -- 일부로 NULL값으로 채움
       LEVEL -- DISTINCT를 1,000,000 으로 생성
FROM   DUAL
CONNECT BY LEVEL <= 100000
;

COMMIT;

-- gather stats "without" histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'XSOFT_T', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);

-- add 1,000,000 data
INSERT INTO XSOFT_T
SELECT 'Y',
       LEVEL + 100000
FROM   DUAL
CONNECT BY LEVEL <= 10000
;

COMMIT;

-- 2. A 세션
ALTER SYSTEM FLUSH SHARED_POOL; -- 모든 cursor invalidate 시킴

DECLARE
    -- 테스트 변수
    val_1 VARCHAR2(1000);
    p_val NUMBER := 100001;

    -- V$SQLAREA 저장 변수
    v_sql_text         VARCHAR2(1000);
    v_sql_id           VARCHAR2(1000);
    v_invalidations    NUMBER;
    v_hash_value       NUMBER;
    v_parse_calls      NUMBER;
    v_plan_hash_value  NUMBER;
    v_last_load_time   VARCHAR2(1000);

    -- DBA_INDEXES 저장 변수
    v_num_rows           NUMBER;
    v_distinct_keys      NUMBER;

    -- 1000번 looping
    CURSOR cur_val IS
        SELECT LEVEL CNT
        FROM   DUAL
        CONNECT BY LEVEL <= 500;

BEGIN
    FOR rec_val IN cur_val LOOP
        SELECT COUNT(1)
        INTO   val_1
        FROM   XSOFT_T
        WHERE  C2 IN (SELECT C2
                      FROM   XSOFT_T
                      WHERE  C2 = p_val
                      )
        AND    C1 IS NOT NULL
        ;

        BEGIN
            SELECT SUBSTR(SQL_TEXT, 1, 30),
                   SQL_ID,
                   HASH_VALUE,
                   INVALIDATIONS,
                   PARSE_CALLS,
                   PLAN_HASH_VALUE,
                   TO_CHAR(LAST_LOAD_TIME, 'YYYYMMDD HH24:MI:SS')
            INTO   v_sql_text,
                   v_sql_id,
                   v_hash_value,
                   v_invalidations,
                   v_parse_calls,
                   v_plan_hash_value,
                   v_last_load_time
            FROM   V$SQLAREA
            WHERE  UPPER(SQL_TEXT) LIKE 'SELECT COUNT(1) FROM XSOFT_T%';
        EXCEPTION
            WHEN OTHERS THEN
                v_sql_text         := NULL;
                v_sql_id           := NULL;
                v_hash_value       := NULL;
                v_invalidations    := NULL;
                v_parse_calls      := NULL;
                v_plan_hash_value  := NULL;
                v_last_load_time   := NULL;
        END;

        SELECT NUM_ROWS,
               DISTINCT_KEYS
        INTO   v_num_rows,
               v_distinct_keys
        FROM   DBA_INDEXES
        WHERE  TABLE_NAME  = 'XSOFT_T'
        AND    INDEX_NAME  = 'XSOFT_T_N1'
        AND    TABLE_OWNER = USER
        ORDER  BY OWNER,
                  INDEX_NAME
        ;

        DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec_val.cnt, '0999') || ', ' ||
                             v_sql_text                   || ', ' ||
                             v_sql_id                     || ', ' ||
                             v_invalidations              || ', ' ||
                             v_hash_value                 || ', ' ||
                             v_parse_calls                || ', ' ||
                             v_plan_hash_value            || ', ' ||
                             TO_CHAR(v_num_rows, '09999') || ', ' ||
                             v_distinct_keys              || ', ' ||
                             v_last_load_time
                             );

    END LOOP;
END;
/

-- 3. B 세션
BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(OWNNAME       => USER,
                                  INDNAME       => 'XSOFT_T_N1',
                                  NO_INVALIDATE => FALSE -- 즉시 변경
                                  );
END;
/


⑦ 출력 결과
CNTSQL_TEXTSQL_IDINVALIDATIONSHASH_VALUEPARSE_CALLSPLAN_HASH_VALUENUM_ROWSDISTINCT_KEYSLAST_LOAD_TIME
0001SELECT COUNT(1) FROM XSOFT_T W6judr71wtg4kv1220399765391125386809900000020090406 16:34:35
..............................
0102SELECT COUNT(1) FROM XSOFT_T W6judr71wtg4kv1220399765391125386809900000020090406 16:34:35
0103&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{*}{_}{+}10000{+}{_}{*}{*}{_}{+}1{+}{_}{*}&nbsp;
0104SELECT COUNT(1) FROM XSOFT_T W6judr71wtg4kv1320399765390{*}{_}{+}1317217955{+}{_}{*}{*}{_}{+}10000{+}{_}{*}{*}{_}{+}1{+}{_}{*}{*}{_}{+}20090406 16:35:06{+}{_}{*}
..............................
0500SELECT COUNT(1) FROM XSOFT_T W6judr71wtg4kv1320399765390131721795510000120090406 16:35:06


⑧ 10046 Trace 결과

SELECT COUNT(1)
FROM
 XSOFT_T WHERE C2 IN (SELECT C2 FROM XSOFT_T WHERE C2 = :B1 ) AND C1 IS NOT
  NULL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute    500      0.06       0.05          0          0          0           0
Fetch      500      1.58       1.51          0       6110          0         500
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1001      1.65       1.57          0       6110          0         500

Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    397  SORT AGGREGATE (cr=1985 pr=0 pw=0 time=48812 us)
    397   NESTED LOOPS SEMI (cr=1985 pr=0 pw=0 time=36037 us)
    397    TABLE ACCESS BY INDEX ROWID XSOFT_T (cr=1191 pr=0 pw=0 time=20136 us)
    397     INDEX RANGE SCAN XSOFT_T_N2 (cr=794 pr=0 pw=0 time=13745 us)(object id 6384455)
    397    INDEX RANGE SCAN XSOFT_T_N2 (cr=794 pr=0 pw=0 time=4551 us)(object id 6384455)

SELECT SUBSTR(SQL_TEXT, 1, 30), SQL_ID, HASH_VALUE, INVALIDATIONS,
  PARSE_CALLS, PLAN_HASH_VALUE, TO_CHAR(LAST_LOAD_TIME, 'YYYYMMDD HH24:MI:SS')

FROM
 V$SQLAREA WHERE UPPER(SQL_TEXT) LIKE 'SELECT COUNT(1) FROM XSOFT_T%'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute    500      0.02       0.01          0          0          0           0
Fetch      500    137.20     134.24          0          0          0         499
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1001    137.23     134.26          0          0          0         499

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    499  FIXED TABLE FULL X$KGLCURSOR_CHILD_SQLID (cr=0 pr=0 pw=0 time=134222729 us)

"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3886

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입