by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

통계정보 조작 - 실사례

Added by 김종원, last edited by 김종원 on 4월 30, 2009



목차

Cardinality 제어하기

1. 시나리오

2. 테스트

3. 정리

4. 추가 테스트

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_NAME NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANAL
XSOFT_T 10024252 16406 550957 2009/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_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE HIGH_VALUE HISTOGRAM
XSOFT_T C2 10024252 0 9.9758E-08 C21950 C40A643943 NONE
XSOFT_T C1 0 10024252 0     NONE 

-- 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_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
XSOFT_T C2 0 2479()
XSOFT_T C2 1 9995666()

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'
;
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS AVG_COL_LEN
USER XSOFT_T C1 1 .1 10024252 1

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
;
OWNER INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS
USER XSOFT_T_N1 0 0 0 0 0
USER XSOFT_T_N1 10198899 9969828 36719 2 21559 

-- 통계정보 변경
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
;
OWNER INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS
USER XSOFT_T_N1 1000000 1000000 200 2 100
USER XSOFT_T_N1 10198899 9969828 36719 2 21559

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;

⑤ 결과

  • 출력결과
    CNT SQL_ID OBJECT_STATUS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS
    1 53vphkmzq39pj VALID 0 0 0 0 0
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    115 53vphkmzq39pj VALID 0 0 0 0 0
    116 53vphkmzq39pj VALID 1000000 1 1790 2 2968
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    1000 53vphkmzq39pj VALID 1000000 1 1790 2 2968
  • 트레이스 
    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회로 줄임)
    CNT SQL_ID OBJECT_STATUS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS
    1 53vphkmzq39pj VALID 0 0 0 0 0
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    25 53vphkmzq39pj VALID 0 0 0 0 0
    26 53vphkmzq39pj VALID 1000000 1000000 200 2 100
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    100 53vphkmzq39pj VALID 1000000 1000000 200 2 100
  • 트레이스 
    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;
/

⑨ 결과

  • 출력결과
    CNT SQL_ID OBJECT_STATUS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS
    1 53vphkmzq39pj VALID 0 0 0 0 0
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    124 53vphkmzq39pj VALID 0 0 0 0 0
    125 53vphkmzq39pj VALID 1000000 1000000 200 2 100
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    ... ... ... ... ... ... ... ...
    1000 53vphkmzq39pj VALID 1000000 1000000 200 2 100
  • 트레이스 
    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 = TRUE TRUE Table, Index, Column, Histogram 모두 생성
cascade = FLASE TRUE Table, 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' 값 비교

구분 이전 이후 비교
ACTION     TRUE
ACTION_HASH 0 0 TRUE
ADDRESS 070000049BF985D8 070000049BF985D8 TRUE
APPLICATION_WAIT_TIME 0 0 TRUE
BIND_DATA BEDA0... BEDA... FALSE
BUFFER_GETS 212727 273 FALSE
CHILD_LATCH 5 5 TRUE
CLUSTER_WAIT_TIME 0 0 TRUE
COMMAND_TYPE 3 3 TRUE
CONCURRENCY_WAIT_TIME 0 0 TRUE
CPU_TIME 54367833 7731 FALSE
DIRECT_WRITES 0 0 TRUE
DISK_READS 0 0 TRUE
ELAPSED_TIME 54373633 7731 FALSE
END_OF_FETCH_COUNT 44 39 FALSE
EXACT_MATCHING_SIGNATURE 4.13E+18 4.13E+18 TRUE
EXECUTIONS 45 39 FALSE
FETCHES 44 39 FALSE
FIRST_LOAD_TIME 2009-04-03/11:30:37 2009-04-03/11:30:37 TRUE
FORCE_MATCHING_SIGNATURE 4.13E+18 4.13E+18 TRUE
HASH_VALUE 2039976539 2039976539 TRUE
INVALIDATIONS 0 1 FALSE
IS_OBSOLETE N N TRUE
JAVA_EXEC_TIME 0 0 TRUE
KEPT_VERSIONS 0 0 TRUE
LAST_ACTIVE_CHILD_ADDRESS 07000004AAD4CD00 07000004AAD4CD00 TRUE
LAST_ACTIVE_TIME 2009-04-03 오전 11:31:32 2009-04-03 오전 11:31:53 FALSE
LAST_LOAD_TIME 2009-04-03 오전 11:30:37 2009-04-03 오전 11:31:53 FALSE
LITERAL_HASH_VALUE 0 0 TRUE
LOADED_VERSIONS 1 1 TRUE
LOADS 1 2 FALSE
MODULE SQL*Plus SQL*Plus TRUE
MODULE_HASH -625018272 -625018272 TRUE
OBJECT_STATUS VALID VALID TRUE
OLD_HASH_VALUE 2443669276 2443669276 TRUE
OPEN_VERSIONS 1 0 FALSE
OPTIMIZER_COST 2 6 FALSE
OPTIMIZER_ENV E289F... E289... TRUE
OPTIMIZER_ENV_HASH_VALUE 2121471620 2121471620 TRUE
OPTIMIZER_MODE ALL_ROWS ALL_ROWS TRUE
OUTLINE_CATEGORY     TRUE
OUTLINE_SID     TRUE
PARSE_CALLS 1 0 FALSE
PARSING_SCHEMA_ID 44 44 TRUE
PARSING_SCHEMA_NAME APPS APPS TRUE
PARSING_USER_ID 44 44 TRUE
PERSISTENT_MEM 5704 5768 FALSE
PLAN_HASH_VALUE 1253868099 1317217955 FALSE
PLSQL_EXEC_TIME 0 0 TRUE
PROGRAM_ID 0 0 TRUE
PROGRAM_LINE# 25 25 TRUE
PX_SERVERS_EXECUTIONS 0 0 TRUE
REMOTE N N TRUE
ROWS_PROCESSED 44 39 FALSE
RUNTIME_MEM 4776 4840 FALSE
SERIALIZABLE_ABORTS 0 0 TRUE
SHARABLE_MEM 23214 23214 TRUE
SORTS 0 0 TRUE
SQL_FULLTEXT <CLOB> <CLOB> TRUE
SQL_ID 6judr71wtg4kv 6judr71wtg4kv TRUE
SQL_PROFILE     TRUE
SQL_TEXT SELECT COUNT(1) FROM XSOFT_T WHERE ... SELECT COUNT(1) FROM XSOFT_T WHERE ... TRUE
USERS_EXECUTING 1 0 FALSE
USERS_OPENING 0 0 TRUE
USER_IO_WAIT_TIME 0 0 TRUE
VERSION_COUNT 1 1 TRUE

④ 'V$SQLAREA' DESCRIPTION

reference site : http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/dynviews_2112.htm#REFRN30259

CNT COLUMN DATATYPE DESCRIPTION
1 ACTION VARCHAR2(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"
2 ACTION_HASH NUMBER Hash value of the action that is named in the ACTION column
3 ADDRESS RAW(4 8) Address of the handle to the parent for this cursor
4 APPLICATION_WAIT_TIME NUMBER Application wait time
5 BUFFER_GETS NUMBER Sum of buffer gets over all child cursors
6 CHILD_LATCH NUMBER Child latch number that is protecting the cursor
7 CLUSTER_WAIT_TIME NUMBER Cluster wait time
8 COMMAND_TYPE NUMBER Oracle command type definition
9 CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time
10 CPU_TIME NUMBER CPU time (in microseconds) used by this cursor for parsing/executing/fetching
11 DIRECT_WRITES NUMBER Sum of the number of direct writes over all child cursors
12 DISK_READS NUMBER Sum of the number of disk reads over all child cursors
13 ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching
14 END_OF_FETCH_COUNT NUMBER "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."
15 EXECUTIONS NUMBER Total number of executions, totalled over all the child cursors
16 FETCHES NUMBER Number of fetches associated with the SQL statement
17 FIRST_LOAD_TIME VARCHAR2(19) Timestamp of the parent creation time
18 HASH_VALUE NUMBER Hash value of the parent statement in the library cache
19 INVALIDATIONS NUMBER Total number of invalidations over all the child cursors
20 IS_OBSOLETE VARCHAR2(1) "Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number
of child cursors is too large."
21 JAVA_EXEC_TIME NUMBER Java execution time
22 KEPT_VERSIONS NUMBER Number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package
23 LOADED_VERSIONS NUMBER Number of child cursors that are present in the cache and have their context heap (KGL heap 6) loaded
24 LOADS NUMBER Number of times the object was loaded or reloaded
25 MODULE VARCHAR2(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"
26 MODULE_HASH NUMBER Hash value of the module that is named in the MODULE column
27 OLD_HASH_VALUE NUMBER Old SQL hash value
28 OPEN_VERSIONS NUMBER The number of child cursors that are currently open under this current parent
29 OPTIMIZER_MODE VARCHAR2(25) Mode under which the SQL statement was executed
30 PARSE_CALLS NUMBER Sum of all parse calls to all the child cursors under this parent
31 PARSING_SCHEMA_ID NUMBER Schema ID that was used to parse this child cursor
32 PARSING_USER_ID NUMBER User ID of the user that has parsed the very first cursor under this parent
33 PERSISTENT_MEM NUMBER "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."
34 PLSQL_EXEC_TIME NUMBER PL/SQL execution time
35 PROGRAM_ID NUMBER Program identifier
36 ROWS_PROCESSED NUMBER Total number of rows processed on behalf of this SQL statement
37 RUNTIME_MEM NUMBER "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."
38 SERIALIZABLE_ABORTS NUMBER Number of times the transaction fails to serialize, producing ORA-08177 errors, totalled over all the child cursors
39 SHARABLE_MEM NUMBER "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."
40 SORTS NUMBER Sum of the number of sorts that were done for all the child cursors
41 SQL_ID VARCHAR2(13) SQL identifier of the parent cursor in the library cache
42 SQL_TEXT VARCHAR2(1000) First thousand characters of the SQL text for the current cursor
43 USER_IO_WAIT_TIME NUMBER User I/O Wait Time
44 USERS_EXECUTING NUMBER Total number of users executing the statement over all child cursors
45 USERS_OPENING NUMBER Number of users that have any of the child cursors open
46 VERSION_COUNT NUMBER Number 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;
/

⑦ 출력 결과

CNT SQL_TEXT SQL_ID INVALIDATIONS HASH_VALUE PARSE_CALLS PLAN_HASH_VALUE NUM_ROWS DISTINCT_KEYS LAST_LOAD_TIME
0001 SELECT COUNT(1) FROM XSOFT_T W 6judr71wtg4kv 12 2039976539 1 1253868099 00000 0 20090406 16:34:35
... ... ... ... ... ... ... ... ... ...
0102 SELECT COUNT(1) FROM XSOFT_T W 6judr71wtg4kv 12 2039976539 1 1253868099 00000 0 20090406 16:34:35
0103             10000 1  
0104 SELECT COUNT(1) FROM XSOFT_T W 6judr71wtg4kv 13 2039976539 0 1317217955 10000 1 20090406 16:35:06
... ... ... ... ... ... ... ... ... ...
0500 SELECT COUNT(1) FROM XSOFT_T W 6judr71wtg4kv 13 2039976539 0 1317217955 10000 1 20090406 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)

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (5)

 

  1. 4월 30, 2009

    김종원 says:

    우선 가볍게 리뷰.. 전제 사항 중 이야기 했던 [ Session 이 연결된 상태에서 ( Cache 된 상태에서 ) ALTER SYSTEM FL...

    우선 가볍게 리뷰..
    전제 사항 중 이야기 했던
    [ Session 이 연결된 상태에서 ( Cache 된 상태에서 )
    ALTER SYSTEM FLUSH SHAED POOL 하여도, V$OPEN_CURSOR 를 조회하면 남아있다.
    WHY ? SESSION_CACHED_CURSOR 에 의한 것이고,
    이 값은 세션이 유지 될때 까지만 유효하다. ]
    커서 관련 테스트 하다가 예전에 남긴 로그야..
    너두 해봤으니 봤을듯...
    내가 당시 내린 결론이야 ㅇ.ㅇ
    낼 뵙세 ㅇ.ㅇ

    타락천사 -

  2. 4월 30, 2009

    김종원 says:

    우선 가볍게 리뷰.. 전제 사항 중 이야기 했던 [ Session 이 연결된 상태에서 ( Cache 된 상태에서 ) ALTER SYSTEM FL...

    우선 가볍게 리뷰..
    전제 사항 중 이야기 했던
    [ Session 이 연결된 상태에서 ( Cache 된 상태에서 )
    ALTER SYSTEM FLUSH SHAED POOL 하여도, V$OPEN_CURSOR 를 조회하면 남아있다.
    WHY ? SESSION_CACHED_CURSOR 에 의한 것이고,
    이 값은 세션이 유지 될때 까지만 유효하다. ]
    커서 관련 테스트 하다가 예전에 남긴 로그야..
    너두 해봤으니 봤을듯...
    내가 당시 내린 결론이야 ㅇ.ㅇ
    낼 뵙세 ㅇ.ㅇ

    타락천사 -

  3. 4월 30, 2009

    김종원 says:

    아.. 테스트 결과 좌절이네여;; 시나리오를 잡고 진행을 하는데 계속 생각지도 못했던 암초들에 부딪혀서리... _ 가혜랑 이번주 스터디 때 서...

    아.. 테스트 결과 좌절이네여;;
    시나리오를 잡고 진행을 하는데 계속 생각지도 못했던 암초들에 부딪혀서리... _

    가혜랑 이번주 스터디 때 서로 숙제 주면서 하자고 한것도 마감, 결산이 껴서 하지도 못하네여 ㅡ.ㅜ

    normal index drop
    FBI index drop
    Histogram drop
    할 때 invalidation이 세션에서 얼마나 걸리는지... library cache pin은 과도하게 걸리는지...
    등등을 해보려고 했는데 못하네여...

    이건 나중에 시간 될 때 계속 업뎃 하겠습니다.

    강정식

  4. 4월 30, 2009

    김종원 says:

    새벽이라 정신도 몽롱하고 정식이가 뭘 테스트 해달라고 했는지 기억도 않나고.... 일단 통계정보 수정시 invalid 확인 테스트 진행했습니다....

    새벽이라 정신도 몽롱하고 정식이가 뭘 테스트 해달라고 했는지 기억도 않나고....
    일단 통계정보 수정시 invalid 확인 테스트 진행했습니다.
    더 고급 정보가 있겠지만 지식이 없는 관계로 일단 가장 쉽게 볼수 있는 v$sqlarea, v$open_cursor로 진행.

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

    힌트안의 숫자만 바꾸어
    1 - 5번수행(pin)
    2 - 6번수행(pin)
    3 - 2번수행
    4 - 5번수행(pin)
    5 - 1번수행

    select substr(SQL_TEXT, 1, 50) sqltxt,SADDR,SID,USER_NAME,ADDRESS,HASH_VALUE,SQL_ID from v$open_cursor
    where user_name = 'KJWON' and sql_text like 'SELECT /*+ GATHER_PLAN_STATISTICS%';

    SQLTXT SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID
    SELECT /*+ GATHER_PLAN_STATISTICS 4 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6A8990 802252279 8fubsc4rx2tgr
    SELECT /*+ GATHER_PLAN_STATISTICS 2 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6A94D0 2925786071 3t1tnukr67wyr
    SELECT /*+ GATHER_PLAN_STATISTICS 5 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6A83F0 2462557118 72jykuf9cg8xy
    SELECT /*+ GATHER_PLAN_STATISTICS 1 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6B1520 3986065000 7ab32w7qtd1m8

    select substr(SQL_TEXT, 1, 50) sqltxt, INVALIDATIONS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, EXECUTIONS,HASH_VALUE,SQL_ID from v$sqlarea
    where sql_text like 'SELECT /*+ GATHER_PLAN_STATISTICS%' order by 1;

    SQLTXT INVALIDATIONS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS HASH_VALUE SQL_ID
    SELECT /*+ GATHER_PLAN_STATISTICS 1 */ COUN 0 1 1 1 5 3986065000 7ab32w7qtd1m8
    SELECT /*+ GATHER_PLAN_STATISTICS 2 */ COUN 0 1 1 1 6 2925786071 3t1tnukr67wyr
    SELECT /*+ GATHER_PLAN_STATISTICS 3 */ COUN 0 1 1 0 2 1074623495 d3uu0at00ux07
    SELECT /*+ GATHER_PLAN_STATISTICS 4 */ COUN 0 1 1 1 5 802252279 8fubsc4rx2tgr
    SELECT /*+ GATHER_PLAN_STATISTICS 5 */ COUN 0 1 1 1 1 2462557118 72jykuf9cg8xy

    ALTER SYSTEM FLUSH SHARED_POOL;

    ==> v$sqlarea pin 여부와 상관없이 모두 flush 됨(invalid), v$open_cursor 데이터는 그대로.

    아래와 같이 쿼리 재 실행
    v$sqlarea

    SQLTXT INVALIDATIONS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS HASH_VALUE SQL_ID
    SELECT /*+ GATHER_PLAN_STATISTICS 1 */ COUN 1 1 1 0 1 3986065000 7ab32w7qtd1m8
    SELECT /*+ GATHER_PLAN_STATISTICS 2 */ COUN 1 1 1 0 1 2925786071 3t1tnukr67wyr
    SELECT /*+ GATHER_PLAN_STATISTICS 3 */ COUN 0 1 1 1 4 1074623495 d3uu0at00ux07
    SELECT /*+ GATHER_PLAN_STATISTICS 4 */ COUN 1 1 1 1 5 802252279 8fubsc4rx2tgr
    SELECT /*+ GATHER_PLAN_STATISTICS 5 */ COUN 1 1 1 1 2 2462557118 72jykuf9cg8xy

    v$open_cursor

    SQLTXT SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID
    SELECT /*+ GATHER_PLAN_STATISTICS 4 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6A8990 802252279 8fubsc4rx2tgr
    SELECT /*+ GATHER_PLAN_STATISTICS 2 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6A94D0 2925786071 3t1tnukr67wyr
    SELECT /*+ GATHER_PLAN_STATISTICS 1 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6B1520 3986065000 7ab32w7qtd1m8

    인덱스 통계정보 즉시 변경
    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;
    /

    ==> v$sqlarea, v$open_cursor 데이터 모두 없어짐.(invalid)

    하나의 쿼리 재 실행시

    v$open_cusor

    SQLTXT SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID
    SELECT /*+ GATHER_PLAN_STATISTICS 1 */ COUN 000007FF14384DE0 159 KJWON 000007FF0F6B1520 3986065000 7ab32w7qtd1m8

    v$sqlarea

    SQLTXT INVALIDATIONS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS HASH_VALUE SQL_ID
    SELECT /*+ GATHER_PLAN_STATISTICS 1 */ COUN 2 1 1 1 1 3986065000 7ab32w7qtd1m8

    테스트 결과

    ALTER SYSTEM FLUSH SHARED_POOL 나 통계정보변경시 해당 오브젝트와 관련된 sql은
    shared pool에서 invalid상태로 빠지고 다시 재 실행시 INVALIDATIONS 값이 증가 하며 EXECUTIONS 값이 새로 셋팅된다.

    하지만 v$open_cursor는 ALTER SYSTEM FLUSH SHARED_POOL 시 invalid로 빠지지 않는다 왜???

    *TeLl2

  5. 4월 30, 2009

    김종원 says:

    time도 추가로 넣어본다.

    time도 추가로 넣어본다.