[오라클 힌트강좌](DYNAMIC_SAMPLING Oracle Hint,
동적샘플링,gather_plan_statistics, dbms_xplan
DYNAMIC SAMPLING(동적 샘플링)힌트는 통계정보가 존재하지 않은 Table에 대한 정확한 selectivity와 cardinality의 추정치를 결정하여 성능을 향상시키는 것이 목적이다. Oracle은 compile time에 어떤 query를 동적 샘플링을 하는 것이 좋을지 나쁠지를 결정한다.
오라클이 동적 샘플링을 하는 것이 좋다고 판단되면 recursive sql이 해당 query table block의 small random sample을 scan해서 주요한 single table에 추정 선택도(selectivity)를 결정한다. 그만큼 부하가 발생되고 또한 통계정보가 있는 것보다는 부정확하므로 CBO를 사용한다면 되도록이면 꼭 통계정보를 생성하도록 하는 것이 좋다. (물론 통계정보가 생성되어 있더라도 여전히 필요할 수 있으며 Dynamic Sampling은 상황에 따라 적절한 실행 계획을 생성하는 장점이 있다.)
동적 샘플링은 통계 정보에 대한 해석의 오류로 인한 실행 계획의 이상을 방지할 수 있는 도구가 될 수 있다. 통계 정보가 있지만, 이미 수집된 통계 정보로는 정확한 정보를 추출할 수 없다면 Query가 Parse되는 시점에 Dynamic Sampling을 수행해서(Dynamic Sampling에 의한 오버헤드를 감수) 비교적 정확한 정보를 얻을 수 있다.
[형식]
SELECT /*+ dynamic_sampling(e 1) */ count(*)
FROM emp e;
괄호안 수치는 0에서 10이며 샘플링의 정도를 나타낸다.
[실습]
SQL> drop table test purge;
테이블이 삭제되었습니다.
경 과: 00:00:00.17
SQL> CREATE TABLE test
2 AS
3 SELECT ROWNUM AS id
4 , DBMS_RANDOM.STRING('u',30) AS val --랜덤 문자30개
5 FROM DUAL
6 CONNECT BY ROWNUM < 1000000;
테이블이 생성되었습니다.
경 과: 00:00:53.23
SQL>
SQL>
SQL> create index idx_test_id on test(id);
인덱스가 생성되었습니다.
경 과: 00:00:01.87
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:04.96
통계정보가 생성되었으므로 대부분의 쿼리는 Dynamic Smapleing 진행없이 잘 돌아간다.
SQ> conn / as ssydba
SQL> grant dba to scott;
권한이 부여되었습니다.
경 과: 00:00:00.28
SQL> conn scott/tiger
연결되었습니다.
SQL> select /*+ gather_plan_statistics */ count(*) from test;
COUNT(*)
----------
999999
경 과: 00:00:00.10
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0p4u1wqwg6t9z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test
Plan hash value: 1950795681
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.10 | 2
630 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.10 | 2
630 |
| 2 | TABLE ACCESS FULL| TEST | 1 | 999K| 999K|00:00:00.37 | 2
예측 999k, 실제 999k 잘 동작한다.
이번에는 like 검색을 해보자.
SQL> select /*+ gather_plan_statistics */ count(*)
2 from test
3 where val like '%A%';
COUNT(*)
----------
690063
경 과: 00:00:00.93
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
--------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.91 | 5
690 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.91 | 5
690 |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 50000 | 690K|00:00:01.07 | 5
690 |
예측이 5만건이 되어버렸다.(100만건의 5%) 기본적으로 실제건수는 690k/999k 690,063건이다.
( CBO에 의해 고정된 값으로, 통계상 5% 정도만 추청치로 둔다.)
Like를 세 개 써보자.
SQL> select /*+ gather_plan_statistics */ count(*)
2 from test
3 where val like '%O%' and val like '%A%' and val like '%G%';
COUNT(*)
----------
321215
경 과: 00:00:02.01
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.00 | 5
690 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.00 | 5
690 |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 125 | 321K|00:00:02.08 | 5
690 |
2 - filter(("VAL" LIKE '%O%' AND "VAL" LIKE '%A%' AND "VAL" LIKE '%G%'
예측은 125개인데 실제는 321, 215개 이다.
이번에는 조인을 걸어보자.
SQL> set autotrace on explain
SQL> select /*+ gather_plan_statistics */ count(*)
2 from test t1, test t2
3 where t1.id = t2.id
4 and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
COUNT(*)
----------
321215
경 과: 00:00:02.54
Execution Plan
| 0 | SELECT STATEMENT | | 1 | 41 | 1833 (1)| 00:00:2
2 |
| 1 | SORT AGGREGATE | | 1 | 41 | |
|
| 2 | NESTED LOOPS | | 125 | 5125 | 1833 (1)| 00:00:2
2 |
|* 3 | TABLE ACCESS FULL| TEST | 125 | 4500 | 1583 (2)| 00:00:1
9 |
|* 4 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | 5 | 2 (0)| 00:00:0
1 |
위 결과를 보면 중첩 루트조인을 했는데 이는 like를 세개 사용했을 때 예측을 125개로 해서 FULL Scan해서 Index Scan(Random I/O)을 하는 것이 좋을 것 같아서 그렇게 처리한 듯 하다. 동적 샘플링을 하면 HASH_JOIN을 한다. 확인하자.
이번에는 동적 샘플링을 해보자.
SQL> select /*+ gather_plan_statistics dynamic_sampling(t1 1) */ count(*)
2 from test t1, test t2
3 where t1.id = t2.id
4 and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
COUNT(*)
----------
321215
경 과: 00:00:02.60
| 0 | SELECT STATEMENT | | 1 | 41 | | 3746 (
1)| 00:00:45 |
| 1 | SORT AGGREGATE | | 1 | 41 | |
| |
|* 2 | HASH JOIN | | 323K| 12M| 14M| 3746 (
1)| 00:00:45 |
|* 3 | TABLE ACCESS FULL | TEST | 323K| 11M| | 1583 (
2)| 00:00:19 |
| 4 | INDEX FAST FULL SCAN| IDX_TEST_ID | 999K| 4882K| | 612 (
2)| 00:00:08 |
DYNAMIC SAMPLING(동적 샘플링)힌트는 통계정보가 존재하지 않은 Table에 대한 정확한 selectivity와 cardinality의 추정치를 결정하여 성능을 향상시키는 것이 목적이다. Oracle은 compile time에 어떤 query를 동적 샘플링을 하는 것이 좋을지 나쁠지를 결정한다.
오라클이 동적 샘플링을 하는 것이 좋다고 판단되면 recursive sql이 해당 query table block의 small random sample을 scan해서 주요한 single table에 추정 선택도(selectivity)를 결정한다. 그만큼 부하가 발생되고 또한 통계정보가 있는 것보다는 부정확하므로 CBO를 사용한다면 되도록이면 꼭 통계정보를 생성하도록 하는 것이 좋다. (물론 통계정보가 생성되어 있더라도 여전히 필요할 수 있으며 Dynamic Sampling은 상황에 따라 적절한 실행 계획을 생성하는 장점이 있다.)
동적 샘플링은 통계 정보에 대한 해석의 오류로 인한 실행 계획의 이상을 방지할 수 있는 도구가 될 수 있다. 통계 정보가 있지만, 이미 수집된 통계 정보로는 정확한 정보를 추출할 수 없다면 Query가 Parse되는 시점에 Dynamic Sampling을 수행해서(Dynamic Sampling에 의한 오버헤드를 감수) 비교적 정확한 정보를 얻을 수 있다.
[형식]
SELECT /*+ dynamic_sampling(e 1) */ count(*)
FROM emp e;
괄호안 수치는 0에서 10이며 샘플링의 정도를 나타낸다.
[실습]
SQL> drop table test purge;
테이블이 삭제되었습니다.
경 과: 00:00:00.17
SQL> CREATE TABLE test
2 AS
3 SELECT ROWNUM AS id
4 , DBMS_RANDOM.STRING('u',30) AS val --랜덤 문자30개
5 FROM DUAL
6 CONNECT BY ROWNUM < 1000000;
테이블이 생성되었습니다.
경 과: 00:00:53.23
SQL>
SQL>
SQL> create index idx_test_id on test(id);
인덱스가 생성되었습니다.
경 과: 00:00:01.87
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:04.96
통계정보가 생성되었으므로 대부분의 쿼리는 Dynamic Smapleing 진행없이 잘 돌아간다.
SQ> conn / as ssydba
SQL> grant dba to scott;
권한이 부여되었습니다.
경 과: 00:00:00.28
SQL> conn scott/tiger
연결되었습니다.
SQL> select /*+ gather_plan_statistics */ count(*) from test;
COUNT(*)
----------
999999
경 과: 00:00:00.10
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0p4u1wqwg6t9z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test
Plan hash value: 1950795681
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.10 | 2
630 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.10 | 2
630 |
| 2 | TABLE ACCESS FULL| TEST | 1 | 999K| 999K|00:00:00.37 | 2
예측 999k, 실제 999k 잘 동작한다.
이번에는 like 검색을 해보자.
SQL> select /*+ gather_plan_statistics */ count(*)
2 from test
3 where val like '%A%';
COUNT(*)
----------
690063
경 과: 00:00:00.93
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
--------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.91 | 5
690 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.91 | 5
690 |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 50000 | 690K|00:00:01.07 | 5
690 |
예측이 5만건이 되어버렸다.(100만건의 5%) 기본적으로 실제건수는 690k/999k 690,063건이다.
( CBO에 의해 고정된 값으로, 통계상 5% 정도만 추청치로 둔다.)
Like를 세 개 써보자.
SQL> select /*+ gather_plan_statistics */ count(*)
2 from test
3 where val like '%O%' and val like '%A%' and val like '%G%';
COUNT(*)
----------
321215
경 과: 00:00:02.01
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.00 | 5
690 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.00 | 5
690 |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 125 | 321K|00:00:02.08 | 5
690 |
2 - filter(("VAL" LIKE '%O%' AND "VAL" LIKE '%A%' AND "VAL" LIKE '%G%'
예측은 125개인데 실제는 321, 215개 이다.
이번에는 조인을 걸어보자.
SQL> set autotrace on explain
SQL> select /*+ gather_plan_statistics */ count(*)
2 from test t1, test t2
3 where t1.id = t2.id
4 and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
COUNT(*)
----------
321215
경 과: 00:00:02.54
Execution Plan
| 0 | SELECT STATEMENT | | 1 | 41 | 1833 (1)| 00:00:2
2 |
| 1 | SORT AGGREGATE | | 1 | 41 | |
|
| 2 | NESTED LOOPS | | 125 | 5125 | 1833 (1)| 00:00:2
2 |
|* 3 | TABLE ACCESS FULL| TEST | 125 | 4500 | 1583 (2)| 00:00:1
9 |
|* 4 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | 5 | 2 (0)| 00:00:0
1 |
위 결과를 보면 중첩 루트조인을 했는데 이는 like를 세개 사용했을 때 예측을 125개로 해서 FULL Scan해서 Index Scan(Random I/O)을 하는 것이 좋을 것 같아서 그렇게 처리한 듯 하다. 동적 샘플링을 하면 HASH_JOIN을 한다. 확인하자.
이번에는 동적 샘플링을 해보자.
SQL> select /*+ gather_plan_statistics dynamic_sampling(t1 1) */ count(*)
2 from test t1, test t2
3 where t1.id = t2.id
4 and t1.val like '%O%' and t1.val like '%A%' and t1.val like '%G%';
COUNT(*)
----------
321215
경 과: 00:00:02.60
| 0 | SELECT STATEMENT | | 1 | 41 | | 3746 (
1)| 00:00:45 |
| 1 | SORT AGGREGATE | | 1 | 41 | |
| |
|* 2 | HASH JOIN | | 323K| 12M| 14M| 3746 (
1)| 00:00:45 |
|* 3 | TABLE ACCESS FULL | TEST | 323K| 11M| | 1583 (
2)| 00:00:19 |
| 4 | INDEX FAST FULL SCAN| IDX_TEST_ID | 999K| 4882K| | 612 (
2)| 00:00:08 |
 | |||||
|---|---|---|---|---|---|
[기업100%환급]SQL기초에서
Schema Object까지  |
총 | 5일 | 40시간 | 02-24 | |
[기업100%환급]PL/SQL,ORACLE
HINT,TUNING  |
총 | 5일 | 40시간 | 03-03 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 18일 | 54시간 | 03-10 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 8일 | 56시간 | 03-01 | |
| [주말저녁]SQL기초에서
Schema Object까지 |
총 | 10일 | 40시간 | 03-01 | |
댓글 없음:
댓글 쓰기