반응형
Dynamic Sampling 이란?
옵티마이저의 능력을 향상시키기 위해 도입된 기능입니다.
통계정보가 존재하지 않을 경우에 Dynamic Sampling level에 따라 데이터블록들을 Sampling한 후 통계정보를 생성해 SQL문을 수행합니다.(CBO에서만 작동하면 RBO에서는 작동하지 않습니다)
12c부터는 Dynamic Statistics 로 이름이 변경되었습니다.
사용시기?
SQL문을 컴파일 하는 동안 옵티마이저는 통계정보가 실행계획을 생성하기에 충분한지 여부를 고려하여 Dynamic Sampling을 사용할지 말지 여부를 결정합니다.
쿼리에 있는 테이블 중 하나 이상에 통계가 없는 경우 Dynamic Sampling는 옵티마이저가 기본 통계정보를 수집합니다.
SQL문에 복잡한 표현식이 포함되어 있고, 확장된 통계를 사용할 수 없는 경우 Dynamic Sampling을 사용할 수 있습니다.
확장된 통계(Extended Statistics)는 11g부터 도입되었으며 옵티마이저가 복잡한 표현식에 대해 좋은 품질의 통계정보 추정치를 얻는 것을 돕는 기능입니다.
출처 : https://blogs.oracle.com/optimizer/post/dynamic-sampling-and-its-impact-on-the-optimizer
Dynamic sampling and its impact on the Optimizer
Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed Dynamic Statistics in Oracle Database 12c. The most common misconception is that DS can be used as a substitute
blogs.oracle.com
관련 파라미터?
(병렬처리가 아닌) Serial SQL문의 경우 Dynamic Sampling level은 optimizer_dynamic_sampling 파라미터에 의해 제어되지만 12cR1 부터는 쿼리가 컴파일 될때 dynamic statistics(12c부터 이름이 바뀜. Dynamic sampling과 동일) 를 할수도 있습니다. 12c R1의 파라미터는 optimizer_adaptive_features, 12c R2의 파라미터는 optimizer_adaptive_statistics로 제어됩니다. 즉, 12c R1 이후에는 관련 파라미터를 TRUE로 설정하여 활성화된 경우 Dynamic Sampling이 사용됩니다.
| DB 버전 | 파라미터이름 | Default Value |
| Oracle Database 12c Release 1 | optimizer_adaptive_features (OAF) | TRUE |
| Oracle Database 12c Release 2 이상 | optimizer_adaptive_statistics (OAS) | FALSE |
사용방법?
1.힌트로 적용하기
dynamic_sampling([적용할 테이블명 or alias] [level])형식으로 적용 가능합니다.
select /*+ dynamic_sampling(t1 1) */ count(*)
from ds_test t1, ds_test t2
where t1.id = t2.id
and t1.val like '%O%'
2.파라미터 변경 DDL로 적용하기
--세션 레벨
SQL> ALTER SESSION SET optimizer_dynamic_sampling = 0;
--시스템 레벨
SQL> ALTER SYSTEM SET optimizer_dynamic_sampling = 0;
dynamic sampling level?
| 레벨 | 언제 사용되는지 | sample size (blocks) |
| 0 | dynamic statistics 안쓰기 | N/A |
| 1 | SQL문에서 하나 이상의 non-partitioned table에 통계가 없을때 | 32 |
| 2 (default) |
SQL문에서 하나 이상의 table(partition 포함)에 통계가 없을때, | 64 |
| 3 | level2 기준을 충족하는 모든 SQL문 그리고 where 절을 사용하는 하나이상의 표현식이 있는 모든 SQL문 | 64 |
| 4 | level3 기준을 충족하는 모든 SQL문 그리고 복잡한 술어를 포함하는 모든 SQL문. 동일한 테이블에 있는 AND 또는 OR 연산자 | 64 |
| 5 | level4 기준을 충족하는 모든 SQL문 | 128 |
| 6 | level4 기준을 충족하는 모든 SQL문 | 256 |
| 7 | level4 기준을 충족하는 모든 SQL문 | 512 |
| 8 | level4 기준을 충족하는 모든 SQL문 | 1024 |
| 9 | level4 기준을 충족하는 모든 SQL문 | 4086 |
| 10 | 모든 SQL문 | All Blocks |
| 11 | 자동으로 dynamic sampling이 필요한지 여부를 결정 | Automatically determined |
테스트?
여러가지 쿼리를 조회하여 dynamic sampling이 발생하는 상황등에 대해 확인해보겠습니다.
1.테스트용 테이블 생성(DS_TEST) : 통계정보 삭제
--인덱스 생성시 자동으로 통계정보 수집하지 않는 파라미터
alter session set "_optimizer_compute_index_stats" = false;
drop table DS_TEST purge;
CREATE TABLE DS_TEST
AS
SELECT ROWNUM AS id
, DBMS_RANDOM.STRING('a',30) AS val --랜덤 문자30개
FROM DUAL
CONNECT BY ROWNUM < 1000000;
create index idx_ds_test_id on ds_test(id);
--통계정보 삭제
exec dbms_stats.delete_table_stats(user,'ds_test');
2,조회 후 실행계획 확인
조건절이 없는 쿼리는 예상(E-rows)와 실제 결과(A-rows)의 차이가 없습니다.
SQL> select /*+ gather_plan_statistics */ count(*) from ds_test;
COUNT(*)
---------
999999
1 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID 785ncdsanjw5u, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from ds_test
Plan hash value: 3949147434
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 5689 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 5689 |
| 2 | TABLE ACCESS FULL| DS_TEST | 1 | 999K| 999K|00:00:00.04 | 5689 |
----------------------------------------------------------------------------------------
===========================================================================================
tkprof
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 5689 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 5689 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=5689 pr=0 pw=0 time=18058 us starts=1)
999999 999999 999999 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=50051 us starts=1 cost=1577 size=0 card=999999)
3.조건절 추가해서 조회 후 실행계획 확인
간단한 조건절을 추가했을 때에도 예상(E-rows)와 실제 결과(A-rows)의 차이가 없습니다.
SQL> select /*+ gather_plan_statistics */ count(*) from ds_test
where val like '%B%';
COUNT(*)
---------
441676
1 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID 7q6fcsbs75p1m, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from ds_test where val
like '%B%'
Plan hash value: 3949147434
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.24 | 5689 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.24 | 5689 |
|* 2 | TABLE ACCESS FULL| DS_TEST | 1 | 441K| 441K|00:00:00.23 | 5689 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("VAL" LIKE '%B%' AND "VAL" IS NOT NULL))
Note
-----
- statistics feedback used for this statement
============================================================================================
tkprof
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.15 0.24 0 5689 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.15 0.24 0 5689 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=5689 pr=0 pw=0 time=242106 us starts=1)
441676 441676 441676 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=256800 us starts=1 cost=1583 size=1550000 card=50000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 10.73 10.73
4.조건절 여러개 추가 후 실행계획 확인
조건절을 여러 개 추가했을때 예상(E-rows)와 실제 결과(A-rows)의 차이가 있었습니다.
1,000,000건 중 125건 (0.0000125%)로 예상했지만 실제로는 81,450건의 결과값이 나왔습니다.
SQL> select /*+ gather_plan_statistics */ count(*) from ds_test
where val like '%B%'
and val like '%A%'
and val like '%C%';
COUNT(*)
---------
81450
SQL_ID b6dcx3a7z86ty, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from ds_test where val
like '%B%' and val like '%A%' and val like '%C%'
Plan hash value: 3949147434
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.35 | 5689 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.35 | 5689 |
|* 2 | TABLE ACCESS FULL| DS_TEST | 1 | 125 | 81450 |00:00:00.32 | 5689 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("VAL" LIKE '%B%' AND "VAL" LIKE '%A%' AND "VAL" LIKE '%C%' AND
"VAL" IS NOT NULL AND "VAL" IS NOT NULL AND "VAL" IS NOT NULL))
=======================================================================================================
tkprof
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.22 0.34 0 5689 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.22 0.35 0 5689 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=5689 pr=0 pw=0 time=348234 us starts=1)
81450 81450 81450 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=317243 us starts=1 cost=1583 size=3875 card=125)
5.조인으로 쿼리 조회 후 실행계획 확인
조건절을 여러 개 추가했을때 예상(E-rows)와 실제 결과(A-rows)의 차이가 있었습니다.
1,000,000건 중 125건 (0.0000125%)로 예상했지만 실제로는 81,295건의 결과값이 나왔습니다.
SQL> select /*+ gather_plan_statistics */ count(*)
from ds_test t1, ds_test t2
where t1.id = t2.id
and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
COUNT(*)
---------
81295
1 rows selected.
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID f952a6h01zrm0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from ds_test t1,
ds_test t2 where t1.id = t2.id and t1.val like '%O%' and t1.val
like '%A%' and t1.val like '%G%'
Plan hash value: 2992804329
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.56 | 7931 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.56 | 7931 | | | |
|* 2 | HASH JOIN | | 1 | 125 | 81008 |00:00:00.57 | 7931 | 5598K| 3200K| 4451K (0)|
|* 3 | TABLE ACCESS FULL | DS_TEST | 1 | 125 | 81008 |00:00:00.36 | 5689 | | | |
| 4 | INDEX FAST FULL SCAN| IDX_DS_TEST_ID | 1 | 1 | 999K|00:00:00.10 | 2242 | | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
3 - filter(("T1"."VAL" LIKE '%O%' AND "T1"."VAL" LIKE '%A%' AND "T1"."VAL" LIKE '%G%' AND "T1"."VAL" IS NOT NULL
AND "T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL))
Note
-----
- this is an adaptive plan
=======================================================================================================
tkprof
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.35 0.55 0 7931 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.35 0.55 0 7931 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=7931 pr=0 pw=0 time=551500 us starts=1)
81295 81295 81295 HASH JOIN (cr=7931 pr=0 pw=0 time=548997 us starts=1 cost=1833 size=5125 card=125)
81295 81295 81295 NESTED LOOPS (cr=5689 pr=0 pw=0 time=362440 us starts=1 cost=1833 size=5125 card=125)
81295 81295 81295 STATISTICS COLLECTOR (cr=5689 pr=0 pw=0 time=355281 us starts=1)
81295 81295 81295 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=342620 us starts=1 cost=1583 size=4500 card=125)
0 0 0 INDEX RANGE SCAN IDX_DS_TEST_ID (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=5 card=1)(object id 191055)
999999 999999 999999 INDEX FAST FULL SCAN IDX_DS_TEST_ID (cr=2242 pr=0 pw=0 time=95912 us starts=1 cost=2 size=5 card=1)(object id 191055)
6.복잡한 SQL문 작성 후 실행계획 확인
조건절을 여러 개 추가했을때 예상(E-rows)와 실제 결과(A-rows)의 차이가 있었습니다.
1,000,000건 중 6건 (0.0000006%)로 예상했지만 실제로는 1.540건의 결과값이 나왔습니다.
SQL> select /*+ gather_plan_statistics */
count(*)
from ds_test t1, ds_test t2
where t1.id = t2.id
and t1.val in (select val from ds_test where val like '%Z')
and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
SQL_ID ba8p0hrzczjap, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from ds_test t1, ds_test
t2 where t1.id = t2.id and t1.val in (select val from ds_test
where val like '%Z') and t1.val like '%O%' and t1.val like '%A%'
and t1.val like '%G%'
Plan hash value: 2160968040
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.63 | 13619 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.63 | 13619 | | | |
|* 2 | HASH JOIN | | 1 | 1 | 1504 |00:00:00.60 | 13619 | 2401K| 2401K| 1337K (0)|
|* 3 | HASH JOIN SEMI | | 1 | 1 | 1504 |00:00:00.45 | 11377 | 1152K| 1152K| 1358K (0)|
|* 4 | TABLE ACCESS FULL | DS_TEST | 1 | 6 | 1504 |00:00:00.33 | 5689 | | | |
|* 5 | TABLE ACCESS FULL | DS_TEST | 1 | 6 | 1504 |00:00:00.10 | 5688 | | | |
| 6 | INDEX FAST FULL SCAN| IDX_DS_TEST_ID | 1 | 1 | 999K|00:00:00.10 | 2242 | | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
3 - access("T1"."VAL"="VAL")
4 - filter(("T1"."VAL" LIKE '%O%' AND "T1"."VAL" LIKE '%A%' AND "T1"."VAL" LIKE '%G%' AND "T1"."VAL" LIKE '%Z' AND
"T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL))
5 - filter(("VAL" LIKE '%Z' AND "VAL" LIKE '%O%' AND "VAL" LIKE '%A%' AND "VAL" LIKE '%G%' AND "VAL" IS NOT NULL
AND "VAL" IS NOT NULL AND "VAL" IS NOT NULL AND "VAL" IS NOT NULL))
Note
-----
- this is an adaptive plan
==============================================================================================================================
tkprof
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.40 0.63 0 13619 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.41 0.64 0 13619 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=13619 pr=0 pw=0 time=638191 us starts=1)
1504 1504 1504 HASH JOIN (cr=13619 pr=0 pw=0 time=612529 us starts=1 cost=3168 size=72 card=1)
1504 1504 1504 NESTED LOOPS (cr=11377 pr=0 pw=0 time=417013 us starts=1 cost=3168 size=72 card=1)
1504 1504 1504 STATISTICS COLLECTOR (cr=11377 pr=0 pw=0 time=416636 us starts=1)
1504 1504 1504 HASH JOIN SEMI (cr=11377 pr=0 pw=0 time=448116 us starts=1 cost=3166 size=67 card=1)
1504 1504 1504 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=322781 us starts=1 cost=1583 size=216 card=6)
1504 1504 1504 TABLE ACCESS FULL DS_TEST (cr=5688 pr=0 pw=0 time=99138 us starts=1 cost=1583 size=186 card=6)
0 0 0 INDEX RANGE SCAN IDX_DS_TEST_ID (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=5 card=1)(object id 191055)
999999 999999 999999 INDEX FAST FULL SCAN IDX_DS_TEST_ID (cr=2242 pr=0 pw=0 time=107052 us starts=1 cost=2 size=5 card=1)(object id 191055)
7. 5번과 동일한 쿼리지만 힌트로 dynamic sampling 4로 설정 후 쿼리 수행
dynamic sampling을 4로 주고 힌트를 확인결과 예상(E-rows)와 실제 결과(A-rows)의 차이가 거의 없이 나왔습니다.
1,000,000건 중 82,528건 (0.082528%)로 예상했지만 실제로는 81,295건의 결과값이 나왔습니다.
SQL> select /*+ gather_plan_statistics dynamic_sampling(t1 4) */ count(*)
from ds_test t1, ds_test t2
where t1.id = t2.id
and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
SQL_ID cfz04438nt32b, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(t1 4) */ count(*)
from ds_test t1, ds_test t2 where t1.id = t2.id and t1.val
like '%O%' and t1.val like '%A%' and t1.val like '%G%'
Plan hash value: 2992804329
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.55 | 7931 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.55 | 7931 | | | |
|* 2 | HASH JOIN | | 1 | 82528 | 81295 |00:00:00.55 | 7931 | 5598K| 3200K| 4883K (0)|
|* 3 | TABLE ACCESS FULL | DS_TEST | 1 | 82528 | 81295 |00:00:00.35 | 5689 | | | |
| 4 | INDEX FAST FULL SCAN| IDX_DS_TEST_ID | 1 | 999K| 999K|00:00:00.10 | 2242 | | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
3 - filter(("T1"."VAL" LIKE '%O%' AND "T1"."VAL" LIKE '%A%' AND "T1"."VAL" LIKE '%G%' AND "T1"."VAL" IS NOT NULL
AND "T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL))
Note
-----
- dynamic statistics used: dynamic sampling (level=0)
- this is an adaptive plan
=============================================================================================================================
tkprof
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.35 0.56 0 7931 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.36 0.56 0 7934 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=7931 pr=0 pw=0 time=560622 us starts=1)
81295 81295 81295 HASH JOIN (cr=7931 pr=0 pw=0 time=562748 us starts=1 cost=3199 size=3383648 card=82528)
81295 81295 81295 NESTED LOOPS (cr=5689 pr=0 pw=0 time=367760 us starts=1 cost=3199 size=3383648 card=82528)
81295 81295 81295 STATISTICS COLLECTOR (cr=5689 pr=0 pw=0 time=358299 us starts=1)
81295 81295 81295 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=347722 us starts=1 cost=1583 size=2971008 card=82528)
0 0 0 INDEX RANGE SCAN IDX_DS_TEST_ID (cr=0 pr=0 pw=0 time=0 us starts=0 cost=612 size=5 card=1)(object id 191055)
999999 999999 999999 INDEX FAST FULL SCAN IDX_DS_TEST_ID (cr=2242 pr=0 pw=0 time=105774 us starts=1 cost=612 size=4999995 card=999999)(object id 191055)
8. 6번과 동일하지만 dynamic sampling 4 힌트를 주고 쿼리 수행
조건절을 여러 개 추가했을때 예상(E-rows)와 실제 결과(A-rows)의 차이가 있었습니다.
1,000,000건 중 6건 (0.0000006%)로 예상했지만 실제로는 1.540건의 결과값이 나왔습니다.
힌트가 먹히지 않은 것 같습니다.
select /*+ gather_plan_statistics dynamic_sampling(t1 4) */
count(*)
from ds_test t1, ds_test t2
where t1.id = t2.id
and t1.val in (select val from ds_test where val like '%Z')
and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
SQL_ID 8th3gctd5p3qp, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(t1 4) */ count(*)
from ds_test t1, ds_test t2 where t1.id = t2.id and t1.val in
(select val from ds_test where val like '%Z') and t1.val like '%O%'
and t1.val like '%A%' and t1.val like '%G%'
Plan hash value: 2553999775
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.63 | 13620 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.63 | 13620 | | | |
|* 2 | HASH JOIN | | 1 | 1 | 1504 |00:00:00.62 | 13620 | 2401K| 2401K| 1350K (0)|
|* 3 | HASH JOIN RIGHT SEMI| | 1 | 1 | 1504 |00:00:00.42 | 11378 | 1209K| 1209K| 1659K (0)|
|* 4 | TABLE ACCESS FULL | DS_TEST | 1 | 6 | 1504 |00:00:00.11 | 5689 | | | |
|* 5 | TABLE ACCESS FULL | DS_TEST | 1 | 1334 | 1504 |00:00:00.29 | 5689 | | | |
| 6 | INDEX FAST FULL SCAN| IDX_DS_TEST_ID | 1 | 1 | 999K|00:00:00.10 | 2242 | | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
3 - access("T1"."VAL"="VAL")
4 - filter(("VAL" LIKE '%Z' AND "VAL" LIKE '%O%' AND "VAL" LIKE '%A%' AND "VAL" LIKE '%G%' AND "VAL" IS NOT NULL
AND "VAL" IS NOT NULL AND "VAL" IS NOT NULL AND "VAL" IS NOT NULL))
5 - filter(("T1"."VAL" LIKE '%O%' AND "T1"."VAL" LIKE '%A%' AND "T1"."VAL" LIKE '%G%' AND "T1"."VAL" LIKE '%Z' AND
"T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL AND "T1"."VAL" IS NOT NULL))
Note
-----
- dynamic statistics used: dynamic sampling (level=0)
- this is an adaptive plan
====================================================================================================================================
tkprof
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.40 0.62 0 13620 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.40 0.63 0 13623 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=13620 pr=0 pw=0 time=627391 us starts=1)
1504 1504 1504 HASH JOIN (cr=13620 pr=0 pw=0 time=606631 us starts=1 cost=3169 size=72 card=1)
1504 1504 1504 NESTED LOOPS (cr=11378 pr=0 pw=0 time=322834 us starts=1 cost=3169 size=72 card=1)
1504 1504 1504 STATISTICS COLLECTOR (cr=11378 pr=0 pw=0 time=322582 us starts=1)
1504 1504 1504 HASH JOIN RIGHT SEMI (cr=11378 pr=0 pw=0 time=421616 us starts=1 cost=3167 size=67 card=1)
1504 1504 1504 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=105180 us starts=1 cost=1583 size=186 card=6)
1504 1504 1504 TABLE ACCESS FULL DS_TEST (cr=5689 pr=0 pw=0 time=296926 us starts=1 cost=1583 size=48024 card=1334)
0 0 0 INDEX RANGE SCAN IDX_DS_TEST_ID (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=5 card=1)(object id 191055)
999999 999999 999999 INDEX FAST FULL SCAN IDX_DS_TEST_ID (cr=2242 pr=0 pw=0 time=99243 us starts=1 cost=2 size=5 card=1)(object id 191055)
쿼리를 parsing할 때마다 dynamic sampling이 동작하여 통계정보를 일정부분 계속 수집한다면 성능은 자연스럽게 안좋아질것입니다.
테스트는 좀더 명확하게 dynamic sampling의 사용법과 효과에 대해서 잘 드러나지는 않는거 같네요..!
추가적으로 테스트하여 더욱 정확한 정보를 얻는다면 추가하겠습니다.
엑시엄에 오신것을 환영합니다
근래 오라클 9i 버전의 개발 지원 중단으로 인해 10g 또는 11g로 업그레이드를 진행하는 사이트가 늘어났다. 9i 버전의 오라클 Optimizer Mode 디폴트 값은 'CHOOSE'로 통계정보가 생성되어 있다면 CBO(Cost
www.axiominfo.co.kr
반응형
'Oracle > 튜닝' 카테고리의 다른 글
| 자동으로 튜닝을 권고해주는 SQL_TUNE ADVISOR (0) | 2023.03.10 |
|---|---|
| [DBMS_SQLTUNE] Profile로 SQL변경없이 실행계획 변경해보기 (0) | 2023.01.20 |
| Join Factorization (0) | 2022.10.28 |
| AWR 딕셔너리 VIEW을 이용한 SQL Tuning 정보 추출 (0) | 2022.08.30 |
| 실행계획에서 Sort 연산 관련 종류 (0) | 2021.12.14 |
댓글