(on) 실행계획의 제어

– http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94937
– V$SQL_HINT : 263 개 힌트가 정의되어 있음 (11.2.0.1.0 기준)

|{code:none|borderStyle=solid}
* 힌트 목적
  * 과거 : 옵티마이저의 실수를 보완
  * 현재 : 옵티마이저에 조언 (옵티마이저가 모르는 정보의 보완, 사용자의 목적)
* 힌트 특성
  * 힌트는 "훈수" 다 - 옵티마이저 마음대로 에러 없이 무시
  * 자습적(Heuristic) 기법에 의한 초기치 선택(Cutoff)을 하는 전략
  * 버전 증가에 따른 힌트 추가/삭제 숙지 필요 - 힌트 변경 유형에 따른 액세스가 있다는 의미
* 힌트 이슈
  * 10% 이상의 쿼리에 힌트가 적용 되었다면 원인 파악 필요
  * 불필요한 힌트는 인덱스 구성 변경이 어려워지고, 옵티마이저에 의한 더 좋은 실행계획 선택을 막는다

|

(on) 데모 준비

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;

|

h2. (on) [Hints for Optimization Approaches and Goals]
h2. (on) [Hints for Join Orders]
h2. (on) [Hints for Join Operations]
h2. (on) [Hints for Parallel Execution]
h2. (on) [Hints for Access Paths]
h2. (on) [Hints for Query Transformations]
h2. (on) [Additional Hints]
h2. (on) [Hints for Online Application Upgrade]
h2. (on) [Undocumented Hints]