[SQL BOOSTER] 5-2. 성능 개선을 위한 최소한의 지식

📚 SQL BOOSTER (유일환, DBian)

 

CH5. 성능 개선을 위한 기본 지식

5-2. 성능 개선을 위한 최소한의 지식

5-2-1. 옵티마이져(Optimizer)

옵티마이져를 한글로 표현하면 '성능 최적화기'라고 할 수 있다.

옵티마이져는 SQL을 실행하기 전에 실행계획을 만드는 역할을 한다.

 

비용 기반 옵티마이져(CBO, Cost Based Optimizer)

SQL을 처리하는 비용에 기반해 최소의 비용을 목표로 실행계획을 만든다.

 

규칙 기반 옵티마이져(RBO, Rule Based Optimizer)

일정한 규칙에 따라 실행계획을 만든다.

 

오라클을 포함한 현재 대부분의 RDBMS는 비용 기반 옵티마이져를 사용한다.

이때의 비용이란 IO의 횟수, CPU Time, 메모리 사용량을 의미한다.

이러한 비용을 산출할 때 가장 중요한 것이 테이블의 통계 정보다.

 

5-2-2. 소프트 파싱, 하드 파싱

SQL을 실행하면 오라클은 가장 먼저 구문 분석을 한다.

구문 분석 단계에서는 SQL이 문법에 맞는지, SQL에 사용한 오브젝트(테이블, 컬럼, 뷰 등)가 사용 가능한지 검사한다.

구문 분석을 통과하면 실행한 SQL의 실행계획이 메모리에 있는지 검색한다.

이때, 만들어 놓은 실행계획이 있으면 이를 재사용하는데 이것을 소프트 파싱이라고 한다.

반면 구문 분석을 통과한 후 재사용 가능한 실행계획이 메모리에 없으면 옵티마이져가 실행계획을 새로 만드는데 이것을 하드 파싱이라고 한다.

 

하드 파싱은 제법 큰 비용이 소모되기 때문에 데이터베이스 서버의 안정적인 성능을 위해서는 소프트 파싱이 가능하도록 SQL을 작성해야 한다.

 

예시1. 각각 하드 파싱이 수행되는 SQL

SELECT * FROM t_ord WHERE cus_id = 'CUS_0001';
SELECT * FROM t_ord WHERE cus_id = 'CUS_9999';

 

예시2. 바인드 변수로 처리된 SQL

SELECT * FROM t_ord WHERE cus_id = :cusId;

 

예시1의 두 SQL은 cus_id에 대한 조건값 외에는 같은 SQL이다.

하지만 오라클은 위 두 SQL을 다른 SQL로 판단하여 각 SQL마다 실행계획이 포함된 하드 파싱을 한다.

예시2처럼 바인드 변수를 사용하면 처음 SQL을 실행할 때만 하드 파싱이 되고, 이후에는 소프트 파싱으로 처리한다.

 

물론 바인드 변숫값에 따라 SQL 성능 저하가 발생할 수도 있다.

그럼에도 불구하고 하드 파싱은 생각보다 많은 CPU 자원을 사용하기 때문에 OLTP 시스템은 소프트 파싱으로 개발해야 한다.

 

5-2-3. IO(Input, Output)

SQL 성능 개선의 핵심은 IO다.

IO를 줄이면 대부분의 성능 문제를 개선할 수 있다.

IO를 거치지 않고 데이터를 저장하거나 읽는 방법은 없기 때문에 IO는 데이터베이스에서 가장 많이 발생하는 작업이다.

따라서 성능 문제도 IO에서 발생할 확률이 높다.

 

SQL 성능 개선을 위해서는 불필요한 IO가 발생하지 않는지 살펴봐야 한다.

가장 먼저 SQL에서 불필요한 테이블을 사용하고 있는지 살펴봐야 하고, 그 다음은 불필요한 데이터를 조회하지는 않는지도 살펴봐야 한다.

불필요한 부분은 제거해야 한다.

인덱스는 그 다음 고민해야 할 문제다.

 

5-2-4. 블록(Block)

블록은 오라클에서 IO를 처리하는 최소 단위다.

MS-SQL이나 My-SQL에서는 페이지(Page)라는 용어를 사용한다.

 

데이터 한 건을 조회하거나 저장하기 위해서는 하나의 블록을 읽거나 써야 한다.

대부분의 OLTP 시스템은 8KB(8,192 Byte) 크기의 블록을 사용한다.

이 하나의 블록에는 여러 건의 데이터가 들어갈 수도 있고, 한 건의 데이터가 모두 들어가지 못할 수도 있다.

예를 들어, 아이템ID와 아이템명으로 구성된 테이블이 있고, 각 컬럼이 40 Byte를 차지할 경우, 데이터 한 건에는 최대 80 Byte가 필요하다. 

블록 크기가 8KB라면 한 블록에는 약 100건 정도를 저장할 수 있으며, 한 블록을 읽을 때마다 100건의 데이터를 읽는 셈이다.

 

테이블을 설계할 때 최소 크기로 데이터가 저장되도록 해야 한다.

하나의 블록에 많은 데이터가 들어가도록 테이블을 설계하면 IO 횟수를 줄이는 IO 최적화를 할 수 있다.

 

SQL의 실제 실행계획에서 블록 IO 수치를 알 수 있다.

 

예시1. 실제 실행계획 만들기

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM t_ord ord
WHERE ord.ord_dt >= to_date('20170101', 'yyyymmdd')
AND ord.ord_dt < to_date('20170201', 'yyyymmdd');

 

예시2. 실제 실행계획을 만든 SQL의 SQL_ID, CHILD_NUMBER 찾기

SELECT T1.sql_id, T1.child_number, t1.sql_text
FROM V$SQL T1
WHERE T1.sql_text LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY T1.last_active_time DESC;

 

 

예시3. 실제 실행계획 조회하기

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('33q18wn7kna1w',0,'ALLSTATS LAST'));

 

 

실제 실행계획을 보면 Buffers 항목이 있다.

Buffers는 논리적 IO고, 여기에는 없지만 Reads는 물리적 IO다.

논리적 IO는 오라클의 메모리 영역인 버퍼캐시에서 데이터를 읽었음을 뜻하고, 물리적 IO는 물리적 디스크에서 데이터를 읽었음을 뜻한다.

 

실행계획의 Buffers와 Reads는 누적된 값이다.

위 실행계획을 보면 전체 논리적 IO는 22번이다.

블록으로 바꿔 표현하면 버퍼캐시에서 총 22번 블록을 읽었다는 뜻이다.

만약 Reads 항목이 있다면 물리적 IO의 횟수, 즉, 디스크에서 블록을 읽은 총 횟수도 알 수 있다.

 

5-2-5. 논리적 IO와 물리적 IO

실행계획에서 논리적 IO는 Buffers로, 물리적 IO는 Reads로 표시된다.

논리적 IO는 오라클의 메모리 영역에서 데이터를 읽고 쓰는 작업이고, 물리적 IO는 디스크에서 데이터를 읽고 쓰는 작업이다.

이때 메모리가 디스크보다 빠르다.

 

오라클에는 버퍼캐시(Buffer cache)라는 메모리 영역이 있다.

버퍼캐시에서 필요한 데이터를 가져오는 과정을 논리적 IO라고 한다.

오라클은 SQL을 처리할 때 필요한 데이터가 버퍼캐시에 있는지 먼저 확인한다.

필요한 데이터가 버퍼캐시에 모두 있다면 논리적 IO만으로 SQL을 처리할 수 있다.

 

버퍼캐시에 원하는 데이터가 없으면 디스크까지 가서 데이터 블록을 가져와야 한다.

이것이 물리적 IO다.

물리적 IO를 수행할 때, 한 번 사용한 블록은 다시 사용할 가능성이 크므로 버퍼캐시에도 저장해 놓는다.

이를 통해 같은 데이터가 필요해지면 물리적 IO가 아닌 논리적 IO로 처리할 수 있게 된다.

 

메모리(버퍼캐시)는 디스크보다 제한적인 자원이다.

버퍼캐시가 꽉 차 있는 상태에서 새로운 데이터가 들어가야 한다면, 버퍼캐시에서 가장 오래전에 사용된 데이터를 제거한다.

성능 향상을 위해 충분한 메모리가 필요한 것은 사실이지만, 메모리를 늘려 버퍼캐시를 크게 설정한다고 해도 데이터베이스 성능이 선형적으로 좋아지는 것은 아니다.

최소한의 IO로 SQL이 처리될 수 있도록 적절한 인덱스 전략과 함께 적절한 SQL이 선행되어야 한다.

 

실행계획에서 Buffers(논리적 IO) 항목을 주의 깊게 살펴보면 성능이 나빠진 증거를 찾을 수 있다.

 

5-2-6. 부분 범위 처리

부분 범위 처리는 SQL을 실행할 때 조건에 맞는 데이터를 필요한 만큼만 찾아서 먼저 내보내는 처리 방식이다.

예를 들어, SQL 결과 집합의 총 건수가 500건이고 화면에 우선 보여줄 건수가 50건이라고 가정하자.

데이터를 필요한 50건만 먼저 전송하는 것이 부분 범위 처리다.

사용자가 다음 데이터를 요청해야만 다음 50건을 전송한다.

부분 범위 처리는 사용자에게 빠른 응답 시간을 보장해준다.

 

예시1. 실제 실행계획 만들기 (SQL을 실행하자마자 실행계획 확인하기)

SELECT /*+ GATHER_PLAN_STATISTICS  */
       ord.*
FROM t_ord ord
WHERE ord.ord_dt >= to_date('20170301', 'yyyymmdd');

 

예시2. 실제 실행계획을 만든 SQL의 SQL_ID와 CHILD_NUMBER 찾기

SELECT /*+ GATHER_PLAN_STATISTICS  */
       ord.*
FROM t_ord ord
WHERE ord.ord_dt >= to_date('20170301', 'yyyymmdd');

 

 

예시3. 실제 실행계획 조회하기

SELECT *
FROM TABLE (dbms_xplan.DISPLAY_CURSOR('4ytgj6pxmdmhd',0,'ALLSTATS LAST'));

 

 

내 환경에서 A-Rows는 501이 나왔다.

내 환경에서 우선 요청한 건수가 501이기 때문이다.

 

이번에는 같은 SQL을 재실행한 후에, 모든 결과를 끝까지 조회한 후 실행계획을 확인해보자.

 

예시4. 실제 실행계획 만들기 (결과를 끝까지 조회한 후 실행계획 확인하기)

SELECT /*+ GATHER_PLAN_STATISTICS  */
       ord.*
FROM t_ord ord
WHERE ord.ord_dt >= to_date('20170301', 'yyyymmdd');

 

예시5. 실제 실행계획을 만든 SQL의 SQL_ID와 CHILD_NUMBER 찾기

SELECT T1.sql_id, T1.child_number, T1.sql_text
FROM v$sql T1
WHERE T1.sql_text LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY T1.last_active_time DESC;

 

 

예시6. 실제 실행계획 조회하기

SELECT *
FROM TABLE (dbms_xplan.DISPLAY_CURSOR('4ytgj6pxmdmhd',0,'ALLSTATS LAST'));

 

 

이번에는 실행계획에 A-Rows가 2,606이 나왔다.

즉, 스크롤 바를 끝까지 내리거나 Last Page 조회 버튼을 클릭해서 다음 데이터를 요청해야 추가 데이터를 전송한다는 사실을 알 수 있다.

 

성능이 최적화된 부분 범위 처리는 결과를 내보내는 과정뿐 아니라 데이터를 읽는 과정도 부분 범위 처리되어야 한다.

하지만 모든 SQL을 그렇게 할 수 있는 것은 아니다.

'GROUP BY'나 'SUM', 'AVG'와 같은 집계함수가 사용된 SQL은 데이터를 찾는 과정을 부분 범위 처리하기 쉽지 않다.

데이터 일부분만 읽어서는 집계함수나 분석함수 처리를 할 수 없다.

 

예시7. GROUP BY가 포함된 SQL의 실제 실행계획 확인하기

SELECT /*+ GATHER_PLAN_STATISTICS*/
       to_char(ord.ord_dt, 'yyyymmdd') ord_ymd
       , ord.cus_id
       , sum(ord.ord_amt) ord_amt
FROM t_ord ord
WHERE ord.ord_dt >= to_date('20170301', 'yyyymmdd')
GROUP BY to_char(ord.ord_dt, 'yyyymmdd')
         , ord.cus_id;

 

(V$SQL, DBMS_XPLAN.DISPLAY_CURSOR 생략)

 

 

실행계획 각 단계의 A-Rows를 살펴보자.

2번 TABLE ACCESS FULL 단계의 A-Rows는 2,606이다.

다음 단계인 HASH GROUP BY의 A-Rows는 501이다.

2,606건의 데이터를 찾아내 'GROUP BY' 처리하고 클라이언트에서 우선 요청한 건수 501건만큼만 결과로 내보내고 있다.

최종 결과를 내보낼 때 우선 필요한 건수만큼만 처리했으므로 부분 범위 처리라고 할 수 있다.

하지만 TABLE ACCESS FULL 단계에서 결과에 필요한 2,606건의 데이터를 모두 읽은 상태이므로 데이터를 찾는 과정에서는 부분 범위처리가 이루어지지 못했음을 알 수 있다.

 

'ORDER BY'가 포함된 SQL도 마찬가지다.

정렬된 결과 집합을 얻으려면 전체 데이터를 읽어야 한다.

하지만 'ORDER BY'는 SQL과 인덱스에 따라 데이터 찾는 과정을 손쉽게 부분 처리할 수도 있다.