[Sql튜닝] 옵티마이저에게 실행 계획을 제안하는 오라클 패키지 함수와 오라클 아키텍처 동작 원리에 따른 퍼포먼스

Posted Apr 3, 2024 Updated Sep 24, 2024

By leewooyup 26 min read

DBMS, ORACLE

🐀 Oracle은 database 관리(저장, 추출, 삭제, 변경) 시스템이다

🍞 Oracle database는 데이터 파일들을 가지고 있으며, 이 파일들에 데이터가 저장된다.
즉, 어딘가에 데이터 파일을 저장해놓고, 사용하는 시스템을 말한다.

데이터 저장 단위는 물리적, 논리적 단위로 나눌 수 있는데,
물리적 단위는 (디스크 상에 존재하는) 파일을 뜻하고,
논리적 단위는 data block -> extent -> segment -> table space 순으로 커진다.
즉, 데이터 논리적 저장 단위 중 가장 상위에 있는 단위를 table space라고 한다.

이 oracle의 논리적인 공간에 테이블, 인덱스, 프로시저, 뷰 등의 객체를 저장해 놓는다.

🎯 table space를 사용하는 이유는 애플리케이션마다 데이터를 독립적으로 관리하며 database 관리 작업을 효율적으로 수행하기 위해서이다.

DBMS_XPLAN.DISPLAY_CURSOR

🐀 오라클 DB에서 제공하는 패키지 함수 중 하나로, SQL문의 실행 계획을 표시한다

📘 SQL 튜닝이란
SQL문의 올바른 해석으로 최적의 성능을 낼 수 있는 SQL 작성 방법을 찾는 것을 말한다.

📕 쿼리 실행 순서
FROM(테이블 지정) -> ON(JOIN 조건 명시) -> JOIN(테이블 결합) -> WHERE(필터링) -> GROUP BY(컬럼기준, 행 그룹화) ->
HAVING(그룹에 대한 조건) -> SELECT(컬럼 반환, 표현식 지정) -> DISTINCT(중복 제거) -> ORDER BY(정렬) ->
OFFSET / FETCH FIRST(범위 제한)

⚠️ SELECT절에서 컬럼명에 Alias 붙인 것을 WHERE절에서는 모른다.

📘 Oracle, cursor
SQL문장의 처리를 위해 할당된 작업 영역을 가리킨다.
자식 커서란 오라클 DB에 여러 개의 커서가 생성되는 경우에 사용된다.

(1) PL/SQL 블록 내에서 반복적으로 실행되는 쿼리나 커서가 있는 경우
: PL/SQL은 오라클 DB에서 프로시저와 함수를 작성 및 관리하는데 사용되는 프로그래밍 언어다.
(2) 하나의 쿼리 내에 다른 쿼리가 중첩되어 있는 경우
: 각각의 서브쿼리는 별도의 자식 커서를 가질 수 있다.

🎯 자식 커서 번호를 사용하면 특정 반복이나 서브쿼리의 실행 결과를 추적할 수 있다.

DB가 SQL문을 어떻게 실행할 것인지에 대한 상세한 정보를 제공하며,
옵티마이저가 결정한 각 단계의 비용, 조인 방법, 인덱스 사용 여부를 포함한다.

☑️ 옵티마이저란
옵티마이저는 DB가 쿼리를 어떻게 실행할 지 결정하는 역할을 한다.
오라클에서 /*+ .. */ 구문은 힌트를 나타낸다.
힌트는 SQL쿼리를 실행할 때 옵티마이저에게 특정 실행 계획을 제안하는 방법이다.
힌트를 사용하면 옵티마이저에게 인덱스 사용, 조인 순서, 병렬 처리에 대한 선호도를 알릴 수 있다.

또한, SQL문이 실행되었을 때의 실제 실행 통계도 보여준다.
⚠️ SQL_ID를 사용하여 실행 계획을 검색할 수 있다.

Outline Data : 오라클 내부 힌트 제공.
Predicate Information : Join 조건, Access 조건, Filter 조건에 대한 구분이 쉬움.
출력 포맷 설정 가능

📘 뷰(view) 란, DB에 존재하는 일종의 가상 테이블
실제 데이터를 저장하고 있진 않지만, DML 작업이 가능한 가상의 테이블이다.
직접 테이블에 접근하는 것이 아니라, 사용자가 필요로 하는 부분만 가져와서 사용할 수 있는 데이터의 집합이다.

🎯 뷰는 복잡한 쿼리를 단순화시킬 수 있다.
: 뷰는 하나 이상의 테이블이나 다른 뷰의 결과를 기반으로 생성될 수 있다. 즉, 뷰를 통해 데이터 모델을 추상화할 수 있다.
여러 테이블 간의 조인을 수행하고 결과를 뷰로 정의하면, 단일 테이블처럼 쿼리할 수 있다.

🎯 뷰는 사용자에게 필요한 정보만 접근하도록 접근을 제한할 수 있다.

OR REPLACE : 뷰를 수정할 때, DROP없이 수정 가능하다.
FORCE : 뷰를 생성할 때, 쿼리문의 테이블, 컬럼, 함수 등이 존재하지 않아도 생성할 수 있다.
COLUMN_ALIASES : SELECT 컬럼의 별칭을 미리 정의할 수 있다.
WITH READ ONLY : SELECT만 가능하다.
WITH CHECK OPTION : WHERE 절의 조건에 해당하는 데이터만 저장, 변경이 가능하다.

  
CREATE OR REPLACE VIEW v_emp
AS
    SELECT empNo
        ,  eName
        ,  job
        ,  hireDate
    FROM   emp
;

오라클 DB에서 제공되는 세가지 뷰인 V$SQL V$SESSION V$SQL_SESSION V$SQL_PLAN_STATISTICS_ALL에 대한 읽기 권한이 필요하다.

V$SQL: 현재 실행중인 SQL문에 대한 정보를 제공하는 뷰; SQL문의 텍스트, 실행횟수, 실행 계획을 포함
V$SESSION: 현재 DB에 연결된 세션에 대한 정보를 제공하는 뷰; 세션은 사용자나 응용프로그램이 DB에 연결되어 있는 상태를 나타낸다.; 세션ID, 사용자 이름, 프로그램 이름, 작업 상태를 포함
V$SQL_SESSION: 세션과 SQL문 간의 매핑정보를 제공하는 뷰; 각 세션에서 실행 중인 SQL문에 대한 정보 제공; SQL문의 수행 상태, 실행 계획을 포함
V$SQL_PLAN_STATISTICS_ALL: SQL 쿼리의 실행 계획과 관련된 통계정보를 제공하는 뷰; SQL문의 고유 식별자, 실행 계획의 고유 해시값, 연산자 종류 / 옵션, 예상 바이트수, 실행 계획 단계의 비용, 예상 시간 등 포함

해당 뷰를 읽기 위해서 권한을 부여해야한다.
권한을 부여하기 위해서 V_$XX형태로 스크립트를 작성해야 한다.

  
GRANT SELECT ON V_$SQL TO C##DEV;
GRANT SELECT ON V_$SESSION TO C##DEV;
GRANT SELECT ON V_$SQL_PLAN TO C##DEV;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO C##DEV;

  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'TYPICAL'));
/* 첫번째 인자: 표시할 SQL문의 SQL_ID, 기본값은 마지막으로 실행된 SQL문의 SQL_ID*/
/* 두번째 인자: SQL문의 특정 자식 커서 번호, 기본값 0 */
/* ⚠️ 첫번째와 두번째 인자에 NULL을 넘기면 가장 최근에 실행된 SQL문을 의미한다 */
/* 세번째 인자는 표시할 정보의 양을 말한다, ADVANCED ALLSTATS LAST 옵션은 가장 많은 정보를 출력 */

실행 계획 내용

🐙 실행 계획 내용

기본 항목
Operation	각각 실행 작업
Name	Operation이 Access하는 테이블/인덱스

옵티마이저 예상값
E-Rows	각 Operation이 끝났을 때 반환되는 예상 건 수
E-Bytes	각 Operation이 반환한 예상 바이트 수
E-Temp	각 Operation이 Temporary Space를 사용한 예상 양
Cost(%CPU)	각 Operation의 코스트(CPU 코스트의 백분율)
E-Time	예측 수행 시간

실제 실행 정보
Starts	각 Operation을 시도한 건 수
A-Rows	각 Operation이 실제 반환한 건 수
A-Time	실제 실행 시간
Buffers	각 Operation이 메모리에서 읽은 블록 수
Reads	각 Operation이 디스크에서 읽은 블록 수
Writers	각 Operation이 디스크에서 쓴 블록 수

PGA 사용 (PGA는 Group by 작업, 정렬, 해시 조인 시 사용)
OMem	Optional 실행에 필요한 메모리(예측치)
IMem	One-pass 실행에 필요한 메모리(예측치)
Used-Mem	마지막 실행 시 사용한 메모리

쿼리 블록
[ WITH 절 ] [ SELECT 문장 ] [ FROM 절 ] [ WHERE 절 ] [ GROUP BY 절 ]

Outline Data
오라클 내부적으로 사용한 힌트와 사용자가 사용한 힌트

Predicate Information
인덱스 Access, Filter, 테이블 Filter, 조인 정보

Column Projection Information
실행 계획에서 id별로 SELECT 되는 컬럼 정보

예제: 포맷만들기

⚔️ 예제: 포맷만들기

+ 와 - 기호로 원하는 정보를 추가하거나 삭제할 수 있다.

  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -ROWS +OUTLINE +ALIAS'));
/* -ROWS: 통계정보의 예측치를 빼는 것 */
/* +OUTLINE: 힌트 정보를 추가로 제공 */
/* +ALIAS: 쿼리 블록 정보를 추가로 제공 */

오라클 아키텍처

🐁 SELECT 요청은 서버 프로세스가 직접 처리하고, DML 요청은 서버 프로세스가 접수하여 백그라운드 프로세스가 처리한다

📕 PGA란 (Program Global Area)
대량의 데이터를 처리할 때 사용하는 버퍼다.
메모리에 저장된 데이터를 일괄적으로 조작하기 위해 사용된다.

🎯 데이터 흐름을 최적화하여 성능을 향상시킨다.
🎯 사용하는 메모리의 양을 줄여 보다 효율적인 데이터 관리를 가능하게 한다.

주로 쿼리 실행 중에 사용되며, 쿼리 결과를 임시로 저장하는 역할을 한다.
⚠️ 데이터 변환, 정렬, 그룹화 등의 작업을 수행하는데 사용된다.

  
CREATE TABLE INSERT_TEST(
    NUM NUMBER
);

INSERT INTO INSERT_TEST(NUM) VALUES(10);

해당 쿼리문을 실행했을 때, 실행된 insert문이 문법에 맞는 지를 확인한다.
그 후, INSERT_TEST라는 테이블이 있는 지,
NUM이라는 컬럼이 있는 지 확인한 후
10 이라는 값을 저장할 수 있는 지를 확인한다.
또한 insert 명령을 할 수 있는 권한을 가지고 있는지 검사한다.

검사가 모두 통과되면 insert문을 재사용하기 위해 해당 쿼리를 Shared Pool⛱️에 저장하고, 쿼리가 실행되면 10이라는 값을 리두 로그 버퍼에 저장한다.
그 후, DB 버퍼 캐시에 저장한다.

쿼리를 실행한 순간에 물리적으로 파일에 데이터를 저장하는게 아니라,
COMMIT 명령을 내려면 LGWR(Log Writer)라는 프로세스가 리두 로그 버퍼에 있는 내용을
리두 로그 파일에 저장한다.
그러면 DB 버퍼 캐시는 더티 버퍼로 상태가 바뀌고, CheckPointer는 데이터 파일에 저장하기 위해
DBWR(Database Writer)에게 신호를 보내고 DBWR은 데이터 파일에도 기록한다.

  
SELECT * FROM INSERT_TEST;

해당 쿼리문을 실행했을 때, 실행된 SELECT문이 문법에 맞는 지를 확인한다.
쿼리문에 열거된 테이블이 있는 지, 컬럼이 있는 지 확인한 후,
SELECT 명령을 할 수 있는 권한을 가지고 있는 지 검사한다.

검사가 모두 통과되면 SELECT문을 재사용하기 위해 해당 쿼리를 Shared Pool⛱️에 저장하고, INSERT_TEST 테이블의 데이터가 어디에 있는 지 확인한다.
서버 프로세스는 먼저 DB 버퍼 캐시에 있는 지 확인하고,
없으면 디스크로 가서 해당 테이블 데이터를 DB 버퍼 캐시로 복사한다.
그러면, DB 버퍼 캐시에 적재된 데이터 중 SQL문의 결과를 유저 프로세스로 전송 한다.

SQL문을 확인하는 과정은 같지만,
데이터를 쓸 때는 LGWR, DBWR이 관여하고, 불러올 때는 서버 프로세스가 관여한다.

퍼포먼스

🌋 퍼포먼스

  
--#1.
SELECT e.ename
     , d.dname
  FROM emp e
  JOIN dept d
    ON e.deptno = d.deptno
 WHERE d.deptno = 10;

--#2.
SELECT e.ename
     , d.dname
  FROM emp e
  JOIN (SELECT *
          FROM dept
         WHERE deptno = 10) d
    ON e.deptno = d.deptno;

📘 #2의 퍼포먼스가 더 좋다
⚠️ emp 테이블의 레코드가 10개라 하고, dept 테이블의 레코드가 100개라고 가정.

#1은 emp 레코드 하나당 dept 레코드 100개를 검사해야 하므로,
총 1000번의 비교연산이 들어간다.

#2는 인라인 뷰에서 dept레코드 100개 중 deptno = 10인 레코드(60개라 가정)를 뽑고 (100번의 비교연산)
emp 레코드 하나 당 dept 레코드 60개를 검사해야 하므로,
총 700번의 비교연산이 들어간다.

따라서 #2의 퍼포먼스가 더 좋다.

서브쿼리

🐀 서브쿼리는 메인 쿼리 내부에 작성하는 SELECT 쿼리를 뜻한다

주로, 3가지 서브쿼리로 분류한다.

스칼라 서브쿼리: 단일 컬럼 단일 행 반환, SELECT절에 사용되는 서브쿼리; 하나의 값만 조회되어야 한다. (아닐 시, 🚫 ORA-01427 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.); ⚠️ 만약을 대비해서 서브쿼리 마지막에 AND ROWNUM=1을 붙이기도 함.

  
SELECT e.empno
  ,  e.ename
  ,  e.deptno
  , (SELECT d.dname
       FROM dept d
      WHERE d.deptno = e.deptno) AS dept_name
  FROM emp e
 WHERE e.sal >= 3000;
 -- 아우터 조인으로 변경해도 된다.

-- SELECT절에서 CASE표현식 조건으로 서브쿼리를 사용하면
-- 다중 컬럼, 다중 행의 서브쿼리를 사용할 수 있다.
SELECT d.deptno
  ,  d.dname
  ,  CASE WHEN d.deptno IN (SELECT DISTINCT e.deptno
                              FROM emp e
                             WHERE e.job = 'MANAGER')
          THEN 'Y'
      END AS manager_yn
  FROM dept d;

인라인 뷰: FROM절, JOIN절에 사용되는 서브쿼리 (해당 쿼리에서만 유효한 임시 테이블/뷰); 뷰와 사용적인 측면에서 동일; 기존에 존재하지 않았던 컬럼을 만들어, 이를 주 쿼리의 데이터 소스로 활용할 수 있다.; ⚠️ 인라인 뷰는 🐖 인덱스가 없기 때문에 레코드가 많으면 쿼리문이 느려질 수 있다.

  
SELECT deptno
     , SUM(e.bonus) AS "sum bonus"
  FROM (SELECT deptno
             , sal
             , CASE WHEN job = 'CLERK'
                    THEN sal*1.1
                    WHEN job = 'MANAGER'
                    THEN sal*1.15
                    WHEN job = 'ANALYST'
                    THEN sal*1.20
                    ELSE 0
                END AS bonus
          FROM emp
         WHERE deptno IS NOT NULL) e
GROUP BY deptno;

-- 인라인 뷰는 테이블(or 뷰)처럼 메인 쿼리의 테이블과 조인하여 사용할 수 있다.
SELECT e.empno
     , e.ename
     , e.job
     , d.mgr_name
     , d.mgr_dept
  FROM emp e
  JOIN (SELECT ee.empno AS mgr_no
             , ee.ename AS mgr_name
             , d.dname AS mgr_dept
          FROM emp ee, dept d
         WHERE e.deptno = d.deptno) d
    ON e.mgr = d.mgr_no
 WHERE e.deptno = 10;

중첩 서브쿼리: 다중 컬럼 다중행 반환, WHERE절, HAVING절에 사용하는 서브쿼리; 🍪 서브쿼리의 기본 특성; 서브쿼리에서 추출되는 데이터가 중복값이 많더라도 Main SQL의 데이터를 증가시키지는 않는다. (Unique값만 처리하므로)

  
SELECT e.empno
     , e.ename
     , e.deptno
     , d.dname
  FROM emp e
  JOIN dept d
    ON e.deptno = d.deptno
 WHERE e.job = 'CLERK'
   AND e.deptno IN (SELECT DISTINCT ee.deptno
                      FROM emp ee
                     WHERE ee.job = 'MANAGER')
 ORDER BY e.deptno;

-- EXISTS 연산자를 사용한 경우
-- 조건을 만족하는 행이 존재하는 지만 확인하는 경우
SELECT e.empno
     , e.ename
     , e.deptno
     , d.dname
  FROM emp e
  JOIN dept d
    ON e.deptno = d.deptno
 WHERE e.job = 'CLERK'
   AND EXISTS (SELECT 1
                 FROM emp ee
                WHERE ee.job = 'MANAGER'
                  AND ee.deptno = e.deptno)
 ORDER BY e.deptno;

조인과 서브쿼리

🌋 통상적으로 서브쿼리를 사용하기보다 조인으로 작성하는 것이 옵티마이저가 정확한 판단을 할 수 있도록 도와준다.

📕 조인(JOIN)
여러 테이블 간에 관계를 기반으로 데이터를 결합할 때 사용된다.
관계를 표현하는데 매우 효율적이며, 인덱스가 잘 구성되어 있다.
옵티마이저가 쿼리를 잘 최적화할 수 있는 경우, 매우 빠른 성능을 보일 수 있다.

🐖 서브쿼리는 메인쿼리의 각 행에 대해 반복적으로 실행되어야 하기 때문에 JOIN보다 느릴 수 있다.

서브쿼리가 인라인 뷰로 사용되는 경우 JOIN과 유사한 성능을 낼 수 있다.
옵티마이저는 이러한 서브쿼리를 자동으로 JOIN으로 변환할 수 있기 때문이다.

🍪 EXIST나 IN을 사용하는 서브쿼리에서 특정 조건을 만족하는 행이 존재하는 지 확인하는 경우에는 JOIN보다 효율적일 수 있다.

서브쿼리가 많은 양의 데이터를 반환하는 경우, JOIN을 사용하는 것이 더 효율적일 수 있다.
서브쿼리는 🐖 임시 결과를 메모리나 디스크에 저장해야 하며, 이는 성능저하를 초래할 수 있다.

SQL문에 🐖 서브쿼리가 여러 개 존재하는 경우 Optimizer가 최적화 과정에서 잘못된 cost 계산을 하는 경우가 발생할 수 있다.

검색속도 향상을 위한, 인덱스

🐀 인덱스란 DB에서 사용되는 데이터 구조로, 데이터가 위치한 장소의 정보를 가진 '일종의 주소록'이다.

일반적으로 SELECT 쿼리를 실행할 때, 먼저 메모리의 DB 버퍼 캐시를 살펴본다.
DB 버퍼 캐시에는 자주 사용되는 테이블이 캐싱되어 있다.
하지만, 인덱스를 사용한다면 이 과정을 거치지 않고, 바로 주소(ROWID)를 통해 찾아간다.
⚠️ 인덱스는 컬럼이 key와 ROWID로 이루어져 있다.

☄️ 인덱스 생성 원리
모든 테이블을 읽고 인덱스를 만드는 동안 데이터가 변경되면 문제가 되므로,
데이터가 변경되지 못하도록 조치를 한 후, 메모리(PGA's Sort Area)에 정렬한다.
즉, 전체 테이블을 스캔한 뒤 메모리에 정렬하고 block들을 기록하는 과정을 거치게 된다.

Sql튜닝

This post is licensed under CC BY 4.0 by the author.