by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

3.3. 실행계획의 제어




실행계획의 제어

실행 계획 제어 힌트 활용 기준
* 옵티마이저에 대한 충분한 이해 → SQL 에 대한 최적의 실행계획 판단 → 최적의 실행계획 유도
* 옵티마이저를 적절한 방법으로 제어 (힌트 사용 / SQL 수정)

-- http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94937
-- V$SQL_HINT : 263 개 힌트가 정의되어 있음 (11.2.0.1.0 기준)
* 힌트 목적
  * 과거 : 옵티마이저의 실수를 보완
  * 현재 : 옵티마이저에 조언 (옵티마이저가 모르는 정보의 보완, 사용자의 목적)
* 힌트 특성
  * 힌트는 "훈수" 다 - 옵티마이저 마음대로 에러 없이 무시
  * 자습적(Heuristic) 기법에 의한 초기치 선택(Cutoff)을 하는 전략
  * 버전 증가에 따른 힌트 추가/삭제 숙지 필요 - 힌트 변경 유형에 따른 액세스가 있다는 의미
* 힌트 이슈
  * 10% 이상의 쿼리에 힌트가 적용 되었다면 원인 파악 필요
  * 불필요한 힌트는 인덱스 구성 변경이 어려워지고, 옵티마이저에 의한 더 좋은 실행계획 선택을 막는다

데모 준비

T1, T2, T3... 초기화

-- T1 / LOCAL DB
DROP TABLE T1;

CREATE TABLE T1 AS
SELECT LEVEL-1 AS N1, TRUNC((LEVEL-1)/10) AS N2, TRUNC((LEVEL-1)/100) AS N3, TRUNC((LEVEL-1)/1000) AS N4 FROM DUAL CONNECT BY LEVEL <= 10000;

ALTER TABLE T1 MODIFY N2 NUMBER NOT NULL;
ALTER TABLE T1 MODIFY N3 NUMBER NOT NULL;
ALTER TABLE T1 MODIFY N4 NUMBER NOT NULL;

ALTER TABLE T1 ADD CONSTRAINT T1PK PRIMARY KEY (N1);
CREATE INDEX T1N2 ON T1 (N2);
CREATE INDEX T1N3 ON T1 (N3);
CREATE INDEX T1N4 ON T1 (N4);
CREATE INDEX T1N4N3 ON T1 (N4, N3);

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'UADMIN', TABNAME => 'T1' , DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1PK', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N2', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N3', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N4', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N4N3', DEGREE => 2);


-- T2 / LOCAL DB
DROP TABLE T2;

CREATE TABLE T2 AS SELECT * FROM T1;

ALTER TABLE T2 MODIFY N1 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N2 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N3 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N4 NUMBER NOT NULL;

ALTER TABLE T2 ADD CONSTRAINT T2PK PRIMARY KEY (N1);
CREATE INDEX T2N2 ON T2 (N2);
CREATE INDEX T2N3 ON T2 (N3);
CREATE INDEX T2N4 ON T2 (N4);
CREATE INDEX T2N4N3 ON T2 (N4, N3);

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'UADMIN', TABNAME => 'T2' , DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2PK', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N2', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N3', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N4', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N4N3', DEGREE => 2);


-- T3 / REMOTE DB
DROP TABLE T3;

CREATE TABLE T3 AS SELECT * FROM T1@TESTDB;


-- T4 / LOCAL DB
DROP CLUSTER H1 INCLUDING TABLES;

CREATE CLUSTER H1 (N4 NUMBER) HASHKEYS 10;

CREATE TABLE T4 CLUSTER H1 (N4) AS SELECT * FROM T1;


-- T5 / LOCAL DB
CREATE CLUSTER H2 (N4 NUMBER);

CREATE INDEX H2N4 ON CLUSTER H2;

CREATE TABLE T5 CLUSTER H2 (N4) AS SELECT * FROM T1;
CREATE TABLE T6 CLUSTER H2 (N4) AS SELECT * FROM T1;

-- M1 / LOCAL DB
CREATE MATERIALIZED VIEW M1 ENABLE QUERY REWRITE AS 
SELECT N4, COUNT(*) AS CNT FROM T1 GROUP BY N4;


-- PT1, PT2 / LOCAL DB
CREATE TABLE PT1 PARTITION BY LIST (N4)
(
  PARTITION PT1_0 VALUES (0),
  PARTITION PT1_1 VALUES (1),
  PARTITION PT1_2 VALUES (2),
  PARTITION PT1_3 VALUES (3),
  PARTITION PT1_4 VALUES (4),
  PARTITION PT1_5 VALUES (5),
  PARTITION PT1_6 VALUES (6),
  PARTITION PT1_7 VALUES (7),
  PARTITION PT1_8 VALUES (8),
  PARTITION PT1_9 VALUES (9)
)
AS SELECT * FROM T1;

CREATE TABLE PT2 PARTITION BY LIST (N4)
(
  PARTITION PT2_0 VALUES (0),
  PARTITION PT2_1 VALUES (1),
  PARTITION PT2_2 VALUES (2),
  PARTITION PT2_3 VALUES (3),
  PARTITION PT2_4 VALUES (4),
  PARTITION PT2_5 VALUES (5),
  PARTITION PT2_6 VALUES (6),
  PARTITION PT2_7 VALUES (7),
  PARTITION PT2_8 VALUES (8),
  PARTITION PT2_9 VALUES (9)
)
AS SELECT * FROM T1;

Hints for Optimization Approaches and Goals

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Hints for Access Paths

Hints for Query Transformations

Additional Hints

Hints for Online Application Upgrade

Undocumented Hints

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.