Optimizer Mode를
변경하는 힌트(ALL_ROWS) [ORACLE/JAVA/.NET/C# 교육학원]
구로디지털
오엔제이프로그래밍실무교육센터
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
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)|
--------------------------------------------------------------------------------
[개강임박강좌, 오프라인교육장에 오시면 보다 자세히 배울 수 있습니다.]
오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주간]
[11/18]Spring3.X, MyBatis, Hibernate실무과정
[11/25]초보자를위한실전SQL
[11/25]안드로이드개발자과정
[11/25]iPhone 하이브리드 앱 개발 실무과정
[평일야간]
[11/19]iPhone하이브리드앱개발실무과정
[11/19]안드로이드개발자과정
[11/21]JAVA&WEB프레임워자실무과정
[11/21]Spring3.X, MyBatis, Hibernate실무과정
[11/27]SQL초보에서실전전문가까지
[주말]
[11/16]JAVA&WEB프레임워크실무과정
[11/16]웹퍼블리싱 마스터
[11/23]SQL초보에서전문가까지
[11/23]C#,ASP.NET마스터
[11/30]PL/SQL,오라클힌트,SQL튜닝,사례연구
[11/31]Spring3.X,MyBatis,Hibernate실무과정
오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주간]
[11/18]Spring3.X, MyBatis, Hibernate실무과정
[11/25]초보자를위한실전SQL
[11/25]안드로이드개발자과정
[11/25]iPhone 하이브리드 앱 개발 실무과정
[평일야간]
[11/19]iPhone하이브리드앱개발실무과정
[11/19]안드로이드개발자과정
[11/21]JAVA&WEB프레임워자실무과정
[11/21]Spring3.X, MyBatis, Hibernate실무과정
[11/27]SQL초보에서실전전문가까지
[주말]
[11/16]JAVA&WEB프레임워크실무과정
[11/16]웹퍼블리싱 마스터
[11/23]SQL초보에서전문가까지
[11/23]C#,ASP.NET마스터
[11/30]PL/SQL,오라클힌트,SQL튜닝,사례연구
[11/31]Spring3.X,MyBatis,Hibernate실무과정
댓글 없음:
댓글 쓰기