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)

문서에 대하여

최초작성자 : 강정식
최초작성일 : 2009년 4월 4일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 튜닝한 실사례를 바탕으로 한 것이며 욱짜블로그(*http://ukja.tistory.com/)를 참고하였습니다..

통계정보 조작 - 실사례 (by jongmali) [2009.04.30]

목차

Cardinality 제어하기

1. 시나리오

2. 테스트

1) 테스트 데이터 만들기

2) 통계정보 확인

3) 실행계획 확인

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

5) 다시 실행계획 테스트

6) C1 컬럼 통계정보 변경

7) 플랜 확인

8) 다시 플랜 확인

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

① 시나리오

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

③ 세션에서 수행할 SQL

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

⑤ 결과

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

⑦ 결과

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

⑨ 결과

3. 정리

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

① 테이블

② 컬럼

③ 인덱스

④ 주요 파라미터 내용(기준은 FND_STATS이나 FND_STATS이 DBMS_STATS을 호출하는 구조이므로 비슷함)

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

4. 추가 테스트

1) 추가 테스트

① A 세션 수행

② B 세션

③ C 세션에서 통계정보 변경 전과 변경 후의 'V$SQLAREA' 값 비교

④ 'V$SQLAREA' DESCRIPTION

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

⑥ 다시 테스트

⑦ 출력 결과

⑧ 10046 Trace 결과

문서에 대하여