"만화로 보는 오라클 튜닝" 을 통해 배운 Oracle SQL 튜닝

실무에서 SQL 성능 문제를 접할 때마다 인덱스를 추가하거나 실행계획만 확인하곤 했다.

 

 

 

그러나 이번에 "만화로 보는 오라클 튜닝"을 읽으면서, Oracle의 뷰와 실행계획 원리를 활용해 더 체계적으로 접근할 수 있다는 것을 알게 되었다. 책을 통해 정리한 주요 내용은 다음과 같다.

 

 

1. Dictionary View로 문제 시점 확인

SQL이 언제, 어떤 상황에서 부하를 일으켰는지는 DBA 성능 뷰(Dynamic Performance View) 로 확인할 수 있다.

V$SQL, V$SESSION, DBA_HIST_SQLSTAT 등을 이용하면 SQL 실행 횟수, 소요 시간, 블록 I/O를 추적할 수 있다.

 

V$SQL – SQL별 누적 성능 확인

SELECT sql_id,
       executions,
       elapsed_time/1000000 AS elapsed_sec,
       buffer_gets,
       disk_reads
  FROM v$sql
 WHERE parsing_schema_name = 'APPUSER'
 ORDER BY elapsed_time DESC;

 

 

예시 결과

SQL_ID	EXECUTIONS	ELAPSED_SEC	BUFFER_GETS	DISK_READS
6s8gk2y3z	1024	350.23	124500	4500
9ah5jd7sq	12	210.54	84500	12000
1d2fgh4kl	5678	90.15	32400	500

→ 누적 시간이 긴 SQL이나 Buffer Gets가 많은 SQL을 쉽게 찾을 수 있다.

 

 

V$SESSION – 현재 실행 중인 세션 확인

SELECT sid, serial#, username, status,
       sql_id, event, wait_class, seconds_in_wait
  FROM v$session
 WHERE username IS NOT NULL;

 

예시 결과

SID	SERIAL#	USERNAME	STATUS	SQL_ID	EVENT	WAIT_CLASS	SECONDS_IN_WAIT
101	12345	APPUSER	ACTIVE	6s8gk2y3z	db file seq read	User I/O	15
204	33445	APPUSER	ACTIVE	9ah5jd7sq	CPU + Wait for CPU	Scheduler	8

→ 현재 어떤 세션이 어떤 SQL을 돌리고 있고, 어떤 이벤트에서 대기 중인지 알 수 있다.

 

 

DBA_HIST_SQLSTAT – 특정 기간 동안의 SQL 성능 추적

SELECT snap_id,
       sql_id,
       executions_delta AS execs,
       elapsed_time_delta/1000000 AS elapsed_sec,
       buffer_gets_delta
  FROM dba_hist_sqlstat
 WHERE sql_id = '6s8gk2y3z'
 ORDER BY snap_id;

 

예시 결과

SNAP_ID	SQL_ID	EXECS	ELAPSED_SEC	BUFFER_GETS_DELTA
101	6s8gk2y3z	120	35.4	10500
102	6s8gk2y3z	450	140.7	52000
103	6s8gk2y3z	300	174.1	62000

→ AWR 기반으로 시간대별 SQL 성능을 추적할 수 있다.

 

 

단순히 느리다는 인식에서 벗어나, 실제 병목 시점을 데이터로 확인할 수 있다.

 

 

2. Table Full Scan의 효율성

Table Full Scan은 무조건 비효율이라고 단정할 수 없다.

값의 동류(NDV)가 적은 컬럼에서는 Full Scan이 오히려 효율적일 수 있다.

예를 들어 성별(Gender) 컬럼은 남/여 두 가지 뿐이므로 인덱스를 사용하면 Random Access가 증가해 더 느려질 수 있다.

 

Random Access란?
인덱스를 통해 조건에 맞는 RowID를 찾은 후, 실제 데이터를 읽기 위해 테이블 블록에 무작위로 접근하는 방식이다.
결과 건수가 많으면 테이블 블록을 여기저기 흩어 읽어야 하므로 I/O가 급격히 늘어나고 성능이 떨어질 수 있다.
반대로 Table Full Scan은 순차적으로 블록을 읽으므로 대량 건수 조회에는 오히려 효율적일 수 있다.

 

 

즉 Full Scan은 상황에 따라 합리적인 선택일 수 있다.

 

3. 인덱스를 타지 못하는 원인

3.1 통계정보 불확실성

Optimizer는 통계정보를 기반으로 실행계획을 수립한다.

하지만 개발 환경에서 수집된 오래된 통계정보는 실제 운영 데이터와 달라서 Cardinality가 잘못 계산될 수 있다.

그 결과 인덱스가 있음에도 Full Scan을 선택하는 경우가 발생한다.

이를 확인하기 위해 ALL_TAB_COLUMNS같은 Dictionary View나 DBMS_XPLAN.DISPLAY_CURSOR의 Predicate Information을 활용할 수 있다.

 

 

ALL_TAB_COLUMNS 예시 – 컬럼 통계 확인

SELECT table_name,
       column_name,
       num_distinct,
       num_nulls,
       density,
       histogram
  FROM all_tab_columns
 WHERE table_name = 'CUSTOMER'
   AND column_name = 'GENDER';

 

 

예시 결과

TABLE_NAME	COLUMN_NAME	NUM_DISTINCT	NUM_NULLS	DENSITY	HISTOGRAM
CUSTOMER	GENDER	2	0	0.5	NONE

• NUM_DISTINCT → 서로 다른 값 개수(NDV)
• DENSITY → 값 분포에 따른 선택도 추정치
• Optimizer는 이 정보를 활용해 Cardinality를 계산한다.

 

 

DBMS_XPLAN.DISPLAY_CURSOR 예시 – Predicate Information 확인

SELECT * 
  FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '6s8gk2y3z', format => 'ALLSTATS LAST'));

 

 

예시 결과 (부분)

------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost | Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |  120 |          |
|*  1 |  TABLE ACCESS FULL          | CUSTOMER  | 10000 |   500K|  120 | 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR("CUSTOMER_ID")='1001')

• Predicate Information에서 TO_CHAR("CUSTOMER_ID")='1001' 같은 변환 흔적을 확인할 수 있다.
• 이처럼 데이터 형 변환이 있으면 인덱스를 타지 못한다는 것을 바로 알 수 있다.

 

 

3.2 데이터 형 변환 문제

인덱스 컬럼이 암묵적 또는 명시적으로 데이터 형 변환을 겪으면 인덱스를 사용할 수 없다.

예: WHERE TO_CHAR(order_id) = '1001' → 인덱스 미사용

해결 방법은 SQL 수정이 우선이며, 불가피하다면 Function-Based Index(FBI) 를 활용할 수 있다.

 

 

4. Cardinality와 NDV

Cardinality는 실행계획 단계별 예상 결과 행 수이다.

NDV(Num Distinct Values)는 특정 컬럼의 서로 다은 값 개수이다.

두 값은 Optimizer가 인덱스 스캔 여부, 조인 방식을 선택하는 기준이 된다.

따라서 성능 문제를 분석할 때 Cardinality와  NDV를 반드시 점검해야 한다.

 

 

5. WITH 절 활용

WITH 절은 동일 데이터를 반복 조회하는 SQL의 성능을 개선한다.

Oracle 9i부터 지원되며, 두 가지 방식으로 동작한다.

 

Materialize 방식: 임시 테이블스페이스에 저장

Inline View 방식: 실행 시마다 치환

 

Materialize 방식 예시

WITH 절 결과를 임시 세그먼트(Temporary Segment)에 저장해서 재사용하는 방식이다.

동일한 데이터를 여러 번 참조하는 경우 성능이 좋아진다.

-- Materialize 힌트를 강제로 지정한 예시
WITH dept_avg AS (
    SELECT /*+ MATERIALIZE */
           deptno, AVG(sal) AS avg_sal
      FROM emp
     GROUP BY deptno
)
SELECT e.ename, e.sal, d.avg_sal
  FROM emp e
  JOIN dept_avg d
    ON e.deptno = d.deptno
 WHERE e.sal > d.avg_sal;

 

설명

• dept_avg 결과를 임시 테이블스페이스에 저장
• 이후 메인 쿼리에서 여러 번 참조해도 동일한 데이터가 반복 계산되지 않음
• 동일한 집계 결과를 여러 번 사용할 때 효율적임

 

 

Inline View 방식 예시

WITH 절 결과를 실제로 저장하지 않고, 단순히 Inline View처럼 매번 치환해서 실행하는 방식이다.

-- Inline 힌트를 강제로 지정한 예시
WITH dept_avg AS (
    SELECT /*+ INLINE */
           deptno, AVG(sal) AS avg_sal
      FROM emp
     GROUP BY deptno
)
SELECT e.ename, e.sal
  FROM emp e
 WHERE e.sal > (
        SELECT d.avg_sal
          FROM dept_avg d
         WHERE e.deptno = d.deptno
       );

 

설명

• dept_avg가 별도로 저장되지 않고, 서브쿼리처럼 매번 실행됨
• 참조 횟수가 적거나 단순한 쿼리일 때 유리
• 불필요한 임시 공간 사용을 피할 수 있음

상황에 따라 성능 차이가 발생하므로 SQL 성격에 맞게 선택해야 한다.

 

 

마무리

이번 책을 통해 다음과 같은 사실을 알게 되었다.

Table Full Scan은 무조건 비효율이 아니다.

통계정보와 Cardinality가 잘못되면 인덱스를 타지 못한다.

NDV는 인덱스 효율성을 판단하는 핵심 지표이다.

SQL 작성 시 데이터 형 변환을 주의해아 한다.

WITH절은 반복 연산을 줄이는 데 효과적이다.

 

SQL 성능 최적화는 단순히 인덱스를 추가하는 것이 아니라, 통계정보, Cardinality, NDV, 실행계획, Dictionary View를 종합적으로 이해하고 분석하는 과정임을 깨달았다.