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 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)|
--------------------------------------------------------------------------------
댓글 없음:
댓글 쓰기