** 데이터 진흥원 - SQL 전문가 가이드의 [과목5 : SQL 옵티마이저] 내용
1) SQL 옵티마이징 원리
◼︎ 옵티마이저 : 규칙과 비용을 고려하여 실행계획을 수립하는 엔진 (최근에는 Self-Learning 기능들이 추가되고 있다.)
◼︎ 고려 요인
(1) 연산자 형태
(2) 옵티마이징 팩터 (인덱스, IOT, 클러스터링, 파티셔닝, MV 등)
-> 사용자가 최적의 옵티마이징 팩터를 제공하지 않으면 성능은 나빠질 수밖에 없다.
(3) DBMS 제약 설정 (PK, FK, Check, Not Null 등)
(4) 옵티마이저 힌트
(5) 통계정보 (비용 기반 계획 수립의 토대)
-> 통계정보를 수집/관리하는데 막대한 리소스가 필요하여 관리 복잡도가 높아질수록 잘못된 근거가 될 수 있다.
(6) 옵티마이저 관련 파라미터 (DBMS 버전에 따라 변경됨)
(7) DBMS 버전과 종류
(8) 바인딩 변수의 사용 여부
-> 프로시저와 같이 변수를 사용하는 경우, 균등 분포를 가정하고 보편적인 실행계획을 세워 저장한 뒤,
변수만 바꿔가면서 동일한 실행 순서를 진행한다. (분포가 불균일한 데이터의 경우 비효율이 발생할 수 있다)
◼︎ 비용 계산 원리 (사용 통계정보)
| 통계유형 | 세부 통계 항목 |
| 테이블 통계 | 전체 레코드 수, 총 블록 수, 빈 블록 수, 한 행당 평균 크기 등 |
| 인덱스 통계 | 인덱스 높이, 리프 블록 수, 클러스터링 팩터, 인덱스 레코드 수 등 |
| 칼럼 통계 | 값의 수, Min/Max 값, 밀도, null 개수, 컬럼 히스토그램 등 |
| 시스템 통계 | CPU속도, 평균 I/O 속도, 초당 I/O 처리량 등 |
선택도 산정 -> 카디널리티 측정 -> 비용 산출 -> 엑세스 방식 / 조인 순서 / 조인 방법 결정
** 선택도 : 전체 중 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율 (기본적으로는 균등분포를 가정하나, 히스토그램을 참조)
+ 히스토그램의 x축은 특정 구간일 수도(도수분포), 동일한 갯수의 행을 갖는 범위(높이균형)일 수도 있다.
+ 조건이 다중 컬럼을 참조하면 계산의 복잡도가 더 높아진다.
균등 분포일 때 선택도는 "1 / distinct value의 개수"가 된다
** 카디널리티 : "전체 행수 * 선택도" : 특정 조건을 취했을 때 결과로 받을 행의 갯수를 의미
** 비용(I/O Call 비용)
| 산식 | 항 별 설명 |
| blevel -- 인덱스 수직적 탐색 비용 + (리프 블록 수 * 유효 인덱스 선택도) -- 인덱스 수평적 탐색 비용 + (클러스터링 팩터 * 유효 테이블 선택도) -- 테이블 랜덤 엑세스 비용 |
blevel : 브랜치 레벨 (리프 블록까지 읽게 될 블록의 개수) 유효 인덱스 선택도 : 전체 인덱스 중 조건을 만족하는 스캔 대상 비율 (방문 비율) 클러스터링 팩터 : 특정 컬럼에서 같은 값들이 모여있는 정도 유효 테이블 선택도 : 필터 완료 후, 방문 예상 되는 블록수 / 테이블 전체 블록 수 |
2) SQL 공유 및 재사용
◼︎ 실행계획의 최적화 과정은 상당한 리소스를 소모하는 과정이다. 할 수 있다면 최대한 재활용해야 한다.
[SQL문 수행 프로세스]
(1) 문법/권한 검사
(2) 해시 함수로 반환된 해시 값으로 **(라이브러리 캐시 or 프로시저 캐시) 내 해시버킷 탐색 (SQL문 자체가 Key값인 해시 값)
(3) 해시버킷에 체인으로 연결된 엔트리를 차례로 스캔하면서 같은 SQL문 탐색
(4-1) SQL 문장을 찾으면 실행계획을 공유 받음 : soft 파싱
(4-2) 못 찾으면 최적화 수행 : hard 파싱
(5) 공유 받거나, 최적화한 실행계획을 탐색했던 해시버킷 체인에 연결
(6) 실행계획의 실행
** SQL과 실행계획이 저장되는 영역을 Orcale은 라이브러리 캐시, SQL Server는 프로시저 캐시라고 한다.
[SQL 해시 값이 달라져 실행계획이 공유되지 못하는 경우 6가지]
| 공유 불가 상황 | SQL 문 예시 |
| (1) 공백 문자 또는 줄바꿈이 추가된 경우 | select * from table select * from table (공백이 추가된 경우) |
| (2) 대소문자의 구분 | select * from table select * from TABLE (대소문자 변경된 경우) |
| (3) 주석(Comment) 추가된 경우 | select * from table select /* 주석문 */ * from table (주석문 추가) |
| (4) 테이블 Owner 명시 | select * from table; select * from dbo.table; (테이블 소유자 명시) |
| (5) 옵티마이져 힌트 사용 | select * from table select /*+ all_rows */ * from table (옵티마이져 힌트 추가) |
| (6) 조건절 비교 값 | select * from table where col = 'A' select * from table where col = 'B' (조건절이 변경된 경우) |
| 다른 경우는 Ad-Hoc query의 가능성이 높음으로 크게 문제가 되지 않겠지만, 6번이 자주 사용되는 경우 문제가 심각할 수 있다. 오히려, DW, OLAP 등 정보계 시스템에 사용되는 Long Running 쿼리들은 오히려 새로 계획을 수립하는 것이 도움이 되는 경우, 히스토그램을 새롭게 반영하고 싶은 경우에는 재수립을 일부러 유도한다. |
|
◼︎ 바인드 변수 활용 : (6) 조건절 비교 값의 해결책
-> 프로시저에 변수를 넘겨 실행시키는 것을 생각한다. 해당 프로시저의 실행 계획은 고정되고, 어떤 변수가 들어오든 동일하게 실행된다.
-> 최근에는 상수 값을 자동으로 변수로 변경하여 수행하는 기능이나,
(SQL Server : 단순 매개 변수화, 자동 매개 변수화 옵션 on/off), (Oracle : cursor_sharing 옵션을 FORCE나 SIMILAR로 설정)
해당 시점의 데이터 분포를 참고하여 실행계획을 수립하는 기능이 제공되고 있으나
(SQL Server : Parameter Sniffing), (Oracle : 바인드 변수 peeking)
해당 기능들은 데이터의 분포가 변하면 성능을 보장하지 못한다는 문제점들이 있어 결국 아직은 관리자의 노력이 필요하다.
-> 변수의 특성에 따라 쿼리나 프로세스를 분리하여 다른 실행계획을 사용하는 방식도 고려해볼 수 있다.
◼︎ 어플리케이션 커서 캐싱 : 어플리케이션 단에서 반복 실행시킬 때, 파싱을 1회만 수행하는 방법
-> PL/SQL은 자동으로 커서를 캐싱하며, 각 언어마다 방법이 다름으로 필요 시, 추가 학습할 것
3) 쿼리 변환
결과만 같다면 더 효율적인 쿼리로 변경을 시도하는 기법 (버전이 업데이트 될수록 더 과감한 접근들이 이뤄지고 있다)
휴리스틱 : 규칙 기반으로 무조건 수행하는 변환 / 비용기반 : 비용 우위 시 쿼리 변환 2가지로 구분된다.
[변환이 이뤄지는 경우 분류]
◼︎ 서브쿼리의 Unnesting
> 서브 쿼리 구문을 Join으로 변경했을 때, 득실을 따져 변경하는 것을 의미한다.
| sql 쿼리 | 실행계획 | |
| 변경 전 | select * from dept where deptno in (select deptno from emp) |
id | OPERATION | NAME | ROWS | BYTES | COST (%) ----------------------------------------------------------------- 0 | SELECT STATMENT | | 3 | 99| 3 (0) *1 | FILTER | | | | 2 | TABLE ACCESS FULL | EMP | 10 | 330| 3 (0) *3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | 2| 0 (0) ----------------------------------------------------------------- predicate information ----------------------------------------------------------------- 1-filter(exists (select 0 from "dept" "dept" where "deptno"=:B1)) 3-access("deptno"=:B1) |
| 변경 후 | select * from (select deptno from emp) a ,dept b where b.deptno = a.deptno -> select emp.* from dept, emp where emp.deptno = dept.deptno |
id | OPERATION | NAME | ROWS | BYTES | COST (%) ----------------------------------------------------------------- 0 | SELECT STATMENT | | 10 | 350 | 2 (0) 1 | TABLE ACCESS BY INDEX ROWID | EMP | 3 | 99 | 1 (0) 2 | NESTED LOOPS | | 10 | 350| 2 (0) 3 | INDEX FULL SCAN | DEPT_PK | 4 | 8 | 1 (0) 4 | INDEX RANGE SCAN | EMP_DEPONO_IDX | 3| | 0 (0) ----------------------------------------------------------------- predicate information ----------------------------------------------------------------- 4-access("DEPTNO"="DEPTNO") |
| 변경 전에는 dept에 해당하는 행들을 emp에서 풀 스캔했다면, 변경 후에는 NL JOIN을 사용하는 것을 볼 수 있다. 위 경우 1:M 의 관계를 가정하고 진행했지만 그렇지 않을 경우에는 (1) 미진행 (2) sort unique (3) semi 조인 등을 활용한다. |
||
◼︎ 뷰 Merging
> 인라인 뷰를 일반적인 where 조건으로 변경하였을 때, 득실을 따져 변경하는 방법이다.
| 변경 전 sql 쿼리 | 변경 후 SQL 쿼리 |
| select * from (select * from emp where job = 'A') a ,(select * from dept where loc = 'B') b where a.deptno = b.deptno |
select * from emp a, dept b where a.deptno = b.deptno and a.job = 'A' and b.loc = 'B' |
| 사용자가 명시한 순서를 옵티마이저가 풀어서 기술한 후, 더 다양한 최적화를 진행해볼 수 있게 된다. ** group by, distinct 등이 포함된 쿼리를 해체하면 오히려 성능이 더 나빠지는 경우들이 있다. ** 집합 연산자(UNION ...),connect by, ROWNUM pseudo 컬럼, select-list에 집계 함수, 분석함수 등에는 불가능하다. |
|
◼︎ 조건절 Pushing
여러 테이블에 기재된 조건 절들을 다른 테이블로 push,pull하는 방식을 의미한다.
| ㅊ | 예시 SQL | 설명 |
| 조건절 predicate pushdown : 쿼리 밖의 조건 절을 밀어 넣는 것 |
select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) a where deptno = 30 |
쿼리의 where을 인라인 뷰에 먼저 적용할 수 있다면 group by 대상을 줄일 수 있다. (필터의 순서를 앞으로 당긴다) |
| 조건절 predicate pullup : 쿼리 내의 조건을 밖으로 빼고 다시 활용 |
select * from (select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1 (select deptno, min(sal), max(sal) from emp group by deptno) e2 whre e1.deptno = e2.deptno |
e1 인라인뷰의 deptno 조건을 e2에도 적용해줄 수 있다면 읽을 량을 줄일 수 있다. |
| 조건절 join predicate pushdown : NL 중 읽은 조건을 Inner 쪽에 대입 활용 |
select d.deptno, d.dname, e.avg_sal from dept d, (select /*+ no_merge push_pred*/ deptno, avg(sal) avg_sal from emp group by deptno) e where e.deptno(+) = d.deptno |
조회해야할 행들에 대해서만 인라인 뷰에서 group by를 수행한다. |
◼︎ 조건절 이행
조건절들의 논리적 관계를 고려해 통합시키는 과정이다.
| 변경 전 sql 쿼리 | 변경 후 SQL 쿼리 |
| select * from dept d, emp e where e.job = 'A' and e.deptno = 10 and d.deptno = e.deptno |
select * from dept d, emp e where e.job = 'A' and e.deptno = 10 and d.deptno = 10 |
◼︎ 불필요한 조인절 제거
-> PK/FK가 설정되어 있는 테이블에 한하여, 만약 조인절에는 기재되었지만 사용되지 않는 테이블이 있다면 계획에서 읽지 않는다.
◼︎ or 조건을 union all로 변환
조건절을 활용한 인덱스 사용이 가능하도록 or을 union all로 전개한다. (or-expansion이라고 지칭한다)
| 변경 전 sql 쿼리 | 변경 후 SQL 쿼리 |
| select * from emp where job = 'A' and deptnop = 20 |
select * from emp where job = 'A' union all select * from emp where deptno = 20 |
◼︎ 기타 쿼리 변환
(1) 집합(minus)에서 발생하는 sort를 제거하기 위해 join으로 변경하는 경우
(2) 조인 컬럼에 "is not null"을 추가하여 null인 값이 조인 대상이 되지 않도록 변환하는 경우
(3) between에서 " between Min and Max" 가 아니라 "between Max and Min"을 입력해버린 경우, 순서를 바꿔준다.
(4) where 조건에 기술된 내용들의 순서를 바꿔 필터가 더 많이 되는 것부터 실행한다.
'Data Engineering > OnPrem_DataBase' 카테고리의 다른 글
| [DataBase] 고급 SQL 튜닝 - DML / DB CALL 튜닝 (0) | 2024.05.06 |
|---|---|
| [DataBase] 고급 SQL 튜닝 - Sort 튜닝 (0) | 2024.05.03 |
| [DataBase] 조인의 원리 & 튜닝 (0) | 2024.04.10 |
| [DataBase] I/O 메커니즘 - 튜닝 지표 (0) | 2024.03.31 |
| [DataBase] 인덱스의 원리 & 스캔 & 최적화 (0) | 2024.03.31 |
