[오라클자바커뮤니티,구로디지털단지,오라클힌트교육강좌강의]Optimizer Mode를 변경하는 힌트(ALL_ROWS) , CBO

[오라클자바커뮤니티,구로디지털단지,오라클힌트교육강좌강의]Optimizer Mode를 변경하는 힌트(ALL_ROWS) , CBO

 

구로디지털 오엔제이프로그래밍실무교육센터

www.onjprogramming.co.kr

 

Optimizer_MODE 값인 ALL_ROWS, FIRST_ROWS, CHOOSE, RULE등을 initSID.ora or spfileSID.ora에 설정을 하게 되면 전체 DB 시스템에 영향을 미치지만 단일 SQL문장에서 힌트 형태로 사용된다면 해당 쿼리 내에서만 영향을 줍니다.

 

먼저 ALL_ROWS에 대해 알아보겠습니다.

 

ALL_ROWS는 CBO에서의 default이며 전체 데이터를 가지고 오는데 최적의 경로를 찾으므로 FULL SCAN을 할 가능성이 많이 있습니다. 그러므로 OLTP성 업무보다 Batch성 업무에 적합하다고 할 수 있습니다.

 

scott 계정의 emp, dept를 가지고 test 한다면 ALL_ROWS 힌트에 의해 아래의 Query는 Nested Loop Join을 수행하는 형태로 되는데 상황(데이터 건수나 인덱스 또는 hash_area_size값)에 따라 nested loop join을 할건지 hash join을 할건지는 다양한 형태로 나타날 수 있지만 분명한 것은 ALL_ROWS 힌트를 사용한다면 반드시 통계정보를 생성해야 한다는 것입니다.

 

아래는 Oracle 11g에서 수행한 예 입니다. 여러분들은 대량의 데이터를 가지고 있는 테이블에서 test 해서 ALL_ROWS를 사용 했을 때와 사용하지 않았을 때를 비교하여 보시기 바랍니다.

 

[실습]

 

-- emptest 테이블은 250만건

    CREATE TABLE emptest 

    AS

      SELECT ROWNUM                    AS empno

      ,      MOD(ROWNUM,5)              AS deptno--5개씩 그룹핑 (0,1,2,3,4값만가짐)

      ,      '홍길동'||ROWNUM          AS ename  --랜덤 문자5개

      ,      '서울'||ROWNUM            AS addr 

      ,      DBMS_RANDOM.STRING('u',5)  AS random --랜덤문자 5개

      FROM  dual

      CONNECT BY ROWNUM <= 2500000  --250만건 만들자...

     

     

    -- depttest 테이블은 5건 

    CREATE TABLE depttest 

    AS

      SELECT

              MOD(ROWNUM,5)              AS deptno--5개씩 그룹핑 (0,1,2,3,4값만가짐)

      ,      '부서명'||MOD(ROWNUM,5)    AS dname  --랜덤 문자5개     

      FROM  dual

      CONNECT BY ROWNUM <= 5  --5건 만들자... 

     

    -- emptest의 deptno로 인덱스를 만들자. 

    create index idx_emptest_deptno on emptest(deptno);

   

    -- 통계정보 생성

    exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPTEST')

   

    exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT')

 

 

 

SQL> conn / as sysdba

연결되었습니다.

 

SQL> alter system set optimizer_mode = CHOOSE;

 

SQL> conn scott/tiger

 

SQL> select /*+ ALL_ROWS */ count(e.ename)

  2  from depttest d, emptest e

  3  where d.deptno = e.deptno;

 

COUNT(E.ENAME)

--------------

      2500000

 

경  과: 00:00:00.48

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2494404156

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------------

|  0 | SELECT STATEMENT    |          |    1 |    30 |  4259  (1)|

|  1 |  SORT AGGREGATE    |          |    1 |    30 |            |         

|*  2 |  HASH JOIN        |          |  2500K|    71M|  4259  (1)|

|  3 |    TABLE ACCESS FULL| DEPTTEST |    5 |    65 |    3  (0)|

|  4 |    TABLE ACCESS FULL| EMPTEST  |  2500K|    40M|  4243  (1)|

-------------------------------------------------------------------------------- 

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecHINT&wr_id=108

• 자바
• 오라클/빅데이터
• 아이폰/안드로이드
• 닷넷/WPF
• 표준웹/HTML5
• 채용/취업무료교육
• 초보자코스

[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지	총	5일	40시간		02-05
[기업100%환급]Spring ,MyBatis,Hibernate실무과정	총	5일	40시간		02-24
자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지	총	24일	72시간		02-06
Spring3.X, MyBatis, Hibernate실무과정	총	12일	36시간		02-11
Spring3.X, MyBatis, Hibernate실무과정	총	5일	35시간		02-15
자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지	총	10일	70시간		02-15
[주말저녁]자바기초에서JSP,Servlet,Ajax,jQUERY,스프링,마이바티스,하이버네이트	총	18일	72시간		02-22
JAVA&WEB프레임워크실무과정	총	14일	98시간		03-08

[기업100%환급]SQL기초에서 Schema Object까지	총	5일	40시간		02-10
[기업100%환급]PL/SQL,ORACLE HINT,TUNING	총	5일	40시간		02-17
SQL초보에서실전전문가까지	총	18일	54시간		02-11
SQL초보에서실전전문가까지	총	8일	56시간		02-22