새로쓴 대용량 데이터베이스솔루션 1 (2011년)
실행계획의 제어 0 0 59,637

by 구루비스터디 Hint 힌트 [2013.09.11]


실행계획의 제어


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


힌트 활용 기준
  • 힌트 목적
    • 과거 : 옵티마이저의 실수를 보완
    • 현재 : 옵티마이저에 조언 (옵티마이저가 모르는 정보의 보완, 사용자의 목적)
  • 힌트 특성
    • 힌트는 "훈수" 다 - 옵티마이저 마음대로 에러 없이 무시
    • 자습적(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;

"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/2612

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입