- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1343591&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
목차
- 1) 테스트 데이터 만들기
- 2) 통계정보 확인
- 3) 실행계획 확인
- 4) C1 컬럼에 10건 'Y' 값을 Insert함
- 5) 다시 실행계획 테스트
- 6) C1 컬럼 통계정보 변경
- 7) 플랜 확인
- 8) 다시 플랜 확인
- 9) Active Session에 '통계정보 갱신'과 '통계정보 변경' 테스트
Cardinality 제어하기
- Cardinality는 오라클이 실행계획을 수립할 때 가장 중요한 정보로 사용을 하며,
그렇기 때문에 실행계획 예측정보에서 Cardinality 값이 정확하게 나오는것이 중요하다. - 하지만 정말 운이 좋은 경우에서만 Cardinality 값을 정확하게 예측할 수 있고
대부분은 정확한 예측을 할 수 없다. - 그렇다면 힌트를 사용하여 Plan을 고정시키지 않고 오라클이 정확한 Cardinality를
예측할 수 있도록 유도하는 방법은 없는가?
구분 | 설명 |
---|---|
1. 통계정보조작 |
|
2. CARDINALITY Hint |
|
3. OPT_ESTIMATE Hint |
|
4. Dynamic Sampling |
|
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/)를 참고하였습니다..
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1343591&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
Comments (5)
4월 30, 2009
김종원 says:
우선 가볍게 리뷰.. 전제 사항 중 이야기 했던 [ Session 이 연결된 상태에서 ( Cache 된 상태에서 ) ALTER SYSTEM FL...우선 가볍게 리뷰..
전제 사항 중 이야기 했던
[ Session 이 연결된 상태에서 ( Cache 된 상태에서 )
ALTER SYSTEM FLUSH SHAED POOL 하여도, V$OPEN_CURSOR 를 조회하면 남아있다.
WHY ? SESSION_CACHED_CURSOR 에 의한 것이고,
이 값은 세션이 유지 될때 까지만 유효하다. ]
커서 관련 테스트 하다가 예전에 남긴 로그야..
너두 해봤으니 봤을듯...
내가 당시 내린 결론이야 ㅇ.ㅇ
낼 뵙세 ㅇ.ㅇ
타락천사 -
4월 30, 2009
김종원 says:
우선 가볍게 리뷰.. 전제 사항 중 이야기 했던 [ Session 이 연결된 상태에서 ( Cache 된 상태에서 ) ALTER SYSTEM FL...우선 가볍게 리뷰..
전제 사항 중 이야기 했던
[ Session 이 연결된 상태에서 ( Cache 된 상태에서 )
ALTER SYSTEM FLUSH SHAED POOL 하여도, V$OPEN_CURSOR 를 조회하면 남아있다.
WHY ? SESSION_CACHED_CURSOR 에 의한 것이고,
이 값은 세션이 유지 될때 까지만 유효하다. ]
커서 관련 테스트 하다가 예전에 남긴 로그야..
너두 해봤으니 봤을듯...
내가 당시 내린 결론이야 ㅇ.ㅇ
낼 뵙세 ㅇ.ㅇ
타락천사 -
4월 30, 2009
김종원 says:
아.. 테스트 결과 좌절이네여;; 시나리오를 잡고 진행을 하는데 계속 생각지도 못했던 암초들에 부딪혀서리... _ 가혜랑 이번주 스터디 때 서...아.. 테스트 결과 좌절이네여;;
시나리오를 잡고 진행을 하는데 계속 생각지도 못했던 암초들에 부딪혀서리... _
가혜랑 이번주 스터디 때 서로 숙제 주면서 하자고 한것도 마감, 결산이 껴서 하지도 못하네여 ㅡ.ㅜ
normal index drop
FBI index drop
Histogram drop
할 때 invalidation이 세션에서 얼마나 걸리는지... library cache pin은 과도하게 걸리는지...
등등을 해보려고 했는데 못하네여...
이건 나중에 시간 될 때 계속 업뎃 하겠습니다.
강정식
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
4월 30, 2009
김종원 says:
time도 추가로 넣어본다.time도 추가로 넣어본다.