SQLP 요점정리 #18 : 3과목 Tuning - 5장 고급 SQL Tuning - 2절 Sort Tuning

3과목 SQL 고급 활용 및 Tuning

5장 고급 SQL Tuning

5.2 Sort Tuning

5.2.1 Sort Tuning 전략

• Data Modeling 측면에서 검토
  • GROUP BY, DISTINCT, UNION 연산이 자주 일어난다면 정규화를 잘하면 많이 해소된다.
  • M:M 관계에서 조회하려면 Sort 연산이 많이 일어난다.
• Sort 발생하지 않도록 SQL 작성
  • UNION -> UNION ALL
  • DISTINCT -> EXISTS
  • 불필요한 COUNT 연산 제거
• Index를 이용한 Sort 연산 대체
  • Sort Order By, Sort Group By, Min, Max 등...
• Sort Area를 적게 사용하도록 SQL 작성
  • Sort 완료 후 데이터 가공
    • || 연산으로 붙인 후 Sort하지 말고 먼저 Sort한 것을 Inline View로 구한 뒤 결합하자.
  • Top-N Query
• Sort 영역 크기 조정

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = N;

5.2.2 Memory Sort vs Disk Sort

• Memory Sort
  • 전체 정렬 작업을 할당받은 Sort Area (PGA) 내에서 완료
  • Internal Sort, Optimal Sort 라고도 함
• Disk Sort
  • 할당받은 Sort Area 안에서 완료못해서 Disk까지 사용
  • External Sort 라고도 함
    • Onepass Sort : disk에 한 번만 기록
    • Multipass Sort : disk에 여러 번 기록

5.2.3 Sort Operation

1. Sort Aggregate

• 집계 함수 수행. (실제로 Sort가 발생하진 않음)

SELECT SUM(SAL), MAX(SAL), MIN(SAL) FROM EMP;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    SORT (AGGREGATE)
        TABLE ACCESS (FULL) OF `EMP` (TABLE)

2. Sort Order By

• ORDER BY (Index가 있는 컬럼에 대해서는 발생하지 않음)

SELECT * FROM EMP ORDER BY SAL;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    SORT (ORDER BY)
        TABLE ACCESS (FULL) OF 'EMP' (TABLE)

SELECT * FROM EMP ORDER BY EMPNO;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    TABLE ACCESS (BY INDEX ROWID) OF `EMP` (TABLE)
        INDEX (FULL SCAN) OF 'PK_EMP' (INDEX (UNIQUE))

3. Sort Group By

• GROUP BY
  • 예전에는 GROUP BY가 Sort된 형태의 결과를 보장했지만, 요즘은 Optimizer가 대신 Hash를 사용 할 수도 있기 때문에 순서를 보장하지 않음
  • 따로 ORDER BY를 붙여줘야지만 순서를 보장함

SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB ORDER BY JOB;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    SORT (GROUP BY)
        TABLE ACCESS (FULL) OF 'EMP' (TABLE)

SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    HASH (GROUP BY)
        TABLE ACCESS (FULL) OF 'EMP' (TABLE)

4. Sort Unique

• UNION, DISTINCT 연산자 사용시
  • GROUP BY 같이 Sort를 보장해주지 않으므로 ORDER BY를 안붙여주면 Optimizer가 Hash로 실행할 가능성이 높음

SELECT DISTINCT DEPTNO FROM EMP ORDER BY DEPTNO;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    SORT (UNIQUE)
        TABLE ACCESS (FULL) OF 'EMP' (TABLE)

SELECT DISTINCT DEPTNO FROM EMP;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    HASH (UNIQUE)
        TABLE ACCESS (FULL) OF `EMP` (TABLE)

SELECT ENAME FROM EMP WHERE SAL <= 1500
 UNION
SELECT DNAME FROM DEPT;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    SORT (UNIQUE)
        UNION-ALL
            TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
                INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX)
            TABLE ACCESS (FULL) OF 'DEPT' (TABLE)

5. Sort Join

• Sort Merge Join 수행시 (Index 사용할 경우에는 발생하지 않을 수 있음)

SELECT /*+ ordered use_merge(d) */ * FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    MERGE JOIN
        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
            INDEX (FULL SCAN) OF 'EMP_DEPT_IDX' (INDEX)
        SORT (JOIN)
            TABLE ACCESS (FULL) OF 'EMP' (TABLE)

6. Window Sort

• Window 함수에서 ORDER BY 수행시

SELECT EMPNO, ROW_NUMBER() OVER (ORDER BY HIREDATE) FROM EMP;

SELETE STATEMENT OPTIMIZER=ALL_ROWS
    WINDOW (SORT)
        TABLE ACCESS (FULL) OF 'EMP' (TABLE)

5.2.4 Sort가 발생하지 않도록 SQL 작성

1. UNION을 UNION ALL로 대체

• UNION : 중복 제거를 위해 SORT UNIQUE 연산을 수행한다.
• UNION ALL : 중복을 허용하고 두 집합을 단순히 결합한다.
• 두 연산의 결과가 같다는게 보장된다면 UNION보다는 UNION ALL을 사용하는게 성능상 도움이 된다.

SELECT * FROM EMP WHERE DEPTNO = 10
 UNION
SELECT * FROM EMP WHERE DEPTNO = 20;

SELETE STATEMENT OPTIMIZER=ALL_ROWS
    SORT (UNIQUE)
        UNION-ALL
            TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
                INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX' (INDEX)
            TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
                INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX' (INDEX)

SELECT * FROM EMP WHERE DEPTNO = 10
 UNION ALL
SELECT * FROM EMP WHERE DEPTNO = 20;

SELETE STATEMENT OPTIMIZER=ALL_ROWS
    UNION-ALL
        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
            INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX' (INDEX)
        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
            INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX' (INDEX)

실행계획을 보면 나머진 똑같은데 SORT (UNIQUE) 연산이 빠졌다.

2. DISTINCT를 EXISTS Sub-query로 대체

• 중복제거를 위해 DISTINCT를 사용하는게 대표적인데, EXISTS로 대체가 가능하다.
• EXISTS의 경우에는 조건에 맞는 것 1개만 찾으면 바로 다음으로 넘어가버리기 때문에 성능상 유리하다.
• 이럴 경우 DISTINCT한 값들이 들어가 있는 Table (주로 Master Table)이 필수적으로 필요하다.
• Master Table이 없는 경우 별도로 생성하는 경우도 있다. (e.g. 연월 Table)

SELECT DISTINCT DEPTNO FROM EMP ORDER BY DEPTNO;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    SORT (UNIQUE)
        TABLE ACCESS (FULL) OF 'EMP' (TABLE)

SELECT DEPTNO FROM DEPT WHERE EXISTS (SELECT 1 FROM EMP WHERE DEPTNO = DEPT.DEPTNO) ORDER BY DEPTNO;

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    NESTED LOOPS (SEMI)
        INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE))
        INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX' (INDEX)

• Semi Join
  • Subquery unnesting의 대표적인 결과
  • Join 조건에 만족하는 것이 하나라도 있으면 다음으로 넘어감

3. 불필요한 COUNT 연산 제거

이건 근본적으로 잘못 짜여진 SQL에서 비롯된 문제이다.
해당 데이터가 있는지 그 여부를 알기 위해서

• COUNT(*)를 사용하면 : SORT AGGREGATE 가 발생하고
• ROWNUM <= 1 조건을 사용하면 : COUNT STOPKEY가 발생한다.