2014년 2월 20일 목요일

[오라클 힌트강좌](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 | [출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecHINT&wr_id=125 자바 오라클/빅데이터 아이폰/안드로이드 닷넷/WPF 표준웹/HTML5 채용/취업무료교육 초보자코스 [기업100%환급]Spring ,MyBatis,Hibernate실무과정 총 5일 40시간 02-24 [기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 총 5일 40시간 03-03 Spring3.X, MyBatis, Hibernate실무과정 총 12일 36시간 03-03 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 총 24일 72시간 03-14 [주말저녁]자바기초에서JSP,Servlet,Ajax,jQUERY,스프링,마이바티스,하이버네이트 총 18일 72시간 02-22 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 총 10일 70시간 03-01 Spring3.X, MyBatis, Hibernate실무과정 총 5일 35시간 03-09 [기업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

[오라클 힌트강좌](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 | 

댓글 없음:

댓글 쓰기