Optimizer / 실행 계획

Optimizer

- SQL 실행계획 생성

- 가장 비용이 적게 드는 계획을 결정한다

- query 변형 / 예측 / 실행계획생성

 

Query 변형

- 기존에 작성된 SQL을 의미상 동일한 SQL로 재작성 가능하는 것이 좋을 지 결정

- OR 확장

-- 1)
SELECT *
 FROM MEMBERS
 WHERE STATUS = 'ACTIVE' OR JOB = 'SWE'
 
-- 2)
SELECT *
 FROM MEMBERS
 WHERE STATUS = 'ACTIVE'
UNION ALL
 SELECT *
 FROM MEMBERS
 WHERE STATUS = 'ACTIVE' AND JOB <> 'SWE'

첫번째 쿼리는 STATUS와 JOB에 INDEX가 있더라도 INDEX를 살리지 못함

 

- SU (SUBQUERY UNNEST)

-- 1)
SELECT *
 FROM MEMBERS
 WHERE ACCOUNT_NO IN
  (SELECT ACCOUNT_NO FROM ACCOUNTS)
 
-- 2)
SELECT *
 FROM MEMBERS, ACCOUNTS
 AND ACCOUNTS.ACCOUNT_NO = MEMBERS.ACCOUNT_NO

subquery를 없애는 것으로 최적화

 

- 뷰 병합

-- 1)
CREATE VIEW MEMBER_VIEW AS
SELECT MEMBER_ID, ACCOUNT_NO, SALARY
 FROM MEMBERS

SELECT MEMBER_ID
 FROM MEMBER_VIEW
 WHERE SALARY > 5000
 
-- 2)
SELECT MEMBER_ID
 FROM MEMBERS
 WHERE SALARY > 5000

2번이 SALARY가 인덱스로 있으면 활용가능하다

distinct, group by, connect by를 포함하지 않는 뷰에만 적용된다

 

- 전이성(Transitive Predicate)

-- 1)
SELECT *
 FROM MEMBERS, ACCOUNTS
 AND ACCOUNTS.ACCOUNT_NO = MEMBERS.ACCOUNT_NO
 AND ACCOUNTS.ACCOUNT_NO LIKE '100%'
 
-- 2)
SELECT *
 FROM MEMBERS, ACCOUNTS
 AND ACCOUNTS.ACCOUNT_NO = MEMBERS.ACCOUNT_NO
 AND ACCOUNTS.ACCOUNT_NO LIKE '100%'
 AND MEMBERS.ACCOUNT_NO LIKE '100%'

ACCOUNT_NO의 인덱스가 MEMBERS에 밖에 없을 경우 조건을 추가한다

 

예측

 - 어떻게 쿼리를 실행해야 빠르게 실행할 수 있는 지 cost를 기반으로 계산

 

통계

- 실행계획을 결정하기 위해 필요

- 컬럼/인덱스/테이블에 대한 정보가 저장된다

- 데이터 딕셔너리 테이블에 저장

EXEC DBMS_STATS.GATHER_TABLE_STATS(계정,테이블);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS --스키마에 해당하는 모든(UNLOCK된) 통계 구하기

- automated maintenance task로 특정 시간에 업데이트 된다

- DML 구문으로 테이블 정보가 변경되었어도 자동반영되지 않는다

- Dynamic Sampling : 통계가 없거나 너무 예전 통계일 경우 sampling을 통해 통계를 구한다

 

선택성(Selectivity)

- 미리 계산된 통계를 통해 행의 비율 계산

- 0~1 사이의 수

- 예시

 salary의 max : 8000 / salary의 min : 800

salary between 2000 and 3000

→ selectivity = (3000 - 2000) / (8000 - 800)

- 데이터가 균등한 분포라고 가정함

- 균등하지 않은 데이터는 히스토그램을 통해 더 정확한 값을 얻을 수 있다

 

Cardinality

- 행의 수

- Cardinality = selectivity * 총행의수

 

Cost

- query를 실행하는 데 필요한 IO / CPU 사용량 / 메모리 사용량 예측 비용

- cost=5 라면 블록을 5번 읽는 만큼 리소스가 필요하다는 의미 (서로다른 DBMS라도 통일된 값)

 

 

 

Mode

ALL_ROWS

전체 쿼리 출력 최적화

FIRST_ROWS

첫 번째 결과가 반환되는 시간을 기준으로 최적화

ALTER SYSTEM SET OPTIMIZER_MODE = ??
ALTER SESSION SET OPTIMIZER_MODE = ??

 

실행계획

쿼리를 수행하기 위한 설명

- 실행 순서 / 인덱스 여부 / 조인 방법 포함

EXPLAIN PLAN SET STATEMENT_ID = '01'
SELECT *
 FROM MEMBERS, ACCOUNTS
 AND ACCOUNTS.ACCOUNT_NO = MEMBERS.ACCOUNT_NO;
 
 SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY('plan_table','01','TYPICAL'));

STATEMENT_ID는 필수값은 아니다, 단지 나중에 조회할 때 용이함

 

 

 

강의정보

https://www.multicampus.com/search?siteAppntSrcwdNm=&siteRalSrcwdNm=&kwd=%EC%97%85%EB%AC%B4%EC%97%90+%EB%B0%94%EB%A1%9C+%EC%93%B0%EB%8A%94+SQL+%ED%8A%9C%EB%8B%9D&input_kwd=%EC%97%85%EB%AC%B4%EC%97%90+%EB%B0%94%EB%A1%9C+%EC%93%B0%EB%8A%94+SQL+%ED%8A%9C%EB%8B%9D&category=TOTAL 

멀티캠퍼스