2014년 5월 21일 수요일

[오라클 힌트강좌](DYNAMIC_SAMPLING Oracle Hint, 동적샘플링,gather_plan_statistics, dbms_xplan [ORACLE/SQL/HINT/오라클/교육/강의/강좌/SQL교육/오라클 PLSQL/강의/강좌/오라클교육/ORACLE초보교육/오라클실무강좌]

[오라클 힌트강좌](DYNAMIC_SAMPLING Oracle Hint, 동적샘플링,gather_plan_statistics, dbms_xplan [ORACLE/SQL/HINT/오라클/교육/강의/강좌/SQL교육/오라클 PLSQL/강의/강좌/오라클교육/ORACLE초보교육/오라클실무강좌]


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 | 

오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급 
www.oraclejavacommunity.com

평일주간(9:30~18:20) 개강
(5/26)SQL초보에서실전전문가까지
(5/26)안드로이드개발자과정
(5/26)C#4.0,ADO.NET,Network 프로그래밍
(5/26)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(5/27)JAVA,ORACLE 실무개발자과정
(5/28)[교육전취업확정]Spring,MyBatis,XPlatform실무프로젝트과정
(5/30)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 
(5/30)[기업100%환급]SQL기초에서 Schema Object까지
(6/09)[기업100%환급]PL/SQL,ORACLE HINT,TUNING

평일야간(19:00~21:50) 개강
(5/22)안드로이드개발자과정
(5/22)C#,ASP.NET마스터
(5/26)웹퍼블리싱 마스터
(5/28)Spring3.X, MyBatis, Hibernate실무과정
(5/28)SQL초보에서실전전문가까지
(5/29)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지

주말(10:00~17:50) 개강
(5/24)Spring3.X, MyBatis, Hibernate실무과정
(5/24)C#,ASP.NET마스터
(5/24)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(5/24)안드로이드개발자과정
(5/24)SQL초보에서실전전문가까지
(5/24)실무예제로 배워보는 jQuery(개발자/디자이너를위한)

주말저녁(18:30~22:20) 개강
(5/24)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)