SQLP 3-5-2 Sort Tuning

in Devlopment on SQLP


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가 있는 컬럼에 대해서는 발생하지 않음) ```sql SELECT * FROM EMP ORDER BY SAL;

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

```sql
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를 붙여줘야지만 순서를 보장함 ```sql 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)

```sql
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 사용할 경우에는 발생하지 않을 수 있음) ```sql 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 수행시
```sql
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가 발생한다.

이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)