오라클 Optimizer Mode를 변경하는 힌트(ALL_ROWS) , 오라클 힌트 옵티마이저

오라클 Optimizer Mode를 변경하는 힌트(ALL_ROWS) , 오라클 힌트 옵티마이저

Optimizer Mode를 변경하는 힌트(ALL_ROWS)

 

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

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

[개강임박강좌, 오프라인교육장에 오시면 보다 자세히 배울 수 있습니다.]

오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)

[주간]
  [11/25]Spring3.X, MyBatis, Hibernate실무과정
  [11/25]안드로이드개발자과정
  [11/25]iPhone 하이브리드 앱 개발 실무과정
  [12/02][기업100%환급]Spring3.X,MyBatis,PL/SQL,Oracle Hint
  [12/09]초보자를위한실전SQL

[평일야간]
  [11/22]자바초보에서안드로이드까지
  [11/26]JAVA&WEB프레임워자실무과정
  [11/26]iPhone하이브리드앱개발실무과정
  [11/26]웹퍼블리싱 마스터
  [11/27]SQL초보에서실전전문가까지
  [11/28]Spring3.X, MyBatis, Hibernate실무과정

[주말]
  [11/23]JAVA&WEB프레임워크실무과정
  [11/23]웹퍼블리싱 마스터
  [11/23]C#,ASP.NET마스터
  [11/30]SQL초보에서전문가까지
  [11/30]Spring3.X,MyBatis,Hibernate실무과정