SQL Performance Analyzer 사용법 - 오라클

SQL Performance Analyzer SPA는 SQL 성능을 비교 분석하는 오라클 내장 도구입니다. 시스템 변경 전과 변경 후의 SQL 성능을 비교하고 분석하기 쉽도록 유용한 기능을 제공합니다.

모든 유형의 시스템 변경에 사용할 수 있지만, 특히 다음과 같이 전반적인 시스템 변경 전/후 성능 비교 분석에 유용합니다.

데이터베이스 업그레이드
데이터베이스 패치
인덱스 재설계
통계정보 변경
데이터베이스 초기화 파라미터 변경

SQL Performance Analyzer 사용 준비

오라클 SQL Performance Analyzer를 사용하기 위해서는 Oracle RAT(Real Application Testing) 라이센스 옵션이 필요합니다. 참고로 해당 옵션 설치 여부는 아래와 같이 조회할 수 있습니다.

SELECT VALUE 
FROM   V$OPTION 
WHERE  PARAMETER = 'Real Application Testing';

VALUE
-----
TRUE

작업 과정의 수집/분석 결과를 저장하는 테이블은 SYSAUX 테이블스페이스(Tablespace)를 사용합니다. 여유 공간이 충분한지 확인하고 부족하다면 증설해야 합니다.

SQL Performance Analyzer 작업 절차

SPA( SQL Performance Analyzer ) 작업 절차는 분석 환경에 따라 다양한 방법을 사용합니다. 이 글은 테스트 DB가 따로 준비되어있는 환경의 작업 절차를 소개합니다.

예를 들어 데이터베이스 업그레이드를 위해 운영 DB(Oracle 11g)와 동일한 복제 DB(Oracle 19c)를 구성하고 모든 스키마 오브젝트와 데이터를 복제했다고 가정합니다. 이때 작업 절차는 다음과 같습니다.

SQL 실행 정보 수집 (운영DB)
SQL 실행 정보 이관 (운영DB → 복제DB)
SQL 성능 분석 작업 생성
SQL 성능 측정 – Before
SQL 성능 측정 – After
SQL 성능 비교 분석

1. SQL 실행 정보 수집 (운영DB)

SPA(SQL Performance Analyzer) 작업은 대상 시스템 운영DB의 SQL 정보를 수집하는 것으로 시작합니다. SQL 정보는 STS(SQL Tuning Set) 형태로 수집해야 합니다. SPA가 입력으로 STS를 사용하기 때문입니다.

STS는 다음과 같이 시스템에서 실행된 SQL 관련 정보와 실행 환경을 저장한 집합입니다. 참고로 다른 오라클 내장 튜닝 도구(SQL Tuning Adivisor 등)에서도 STS를 입력으로 사용합니다.

SQL 텍스트
파싱 스키마, 바인드 변수, 옵티마이저 파라미터
Executions, Buffer Gets, Disk Reads

아래는 V$SQL 커서 캐시에 저장된 정보를 이용해 STS를 생성하는 예시입니다.

DECLARE
  c1 DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
  DBMS_SQLSET.CREATE_SQLSET( sqlset_name  => 'MYSTS' );
  OPEN c1 FOR
  SELECT VALUE(p)
  FROM   TABLE( 
            DBMS_SQLSET.SELECT_CURSOR_CACHE(
               basic_filter => ' parsing_schema_name = ''DATAWAY'' ')   -- 수집대상 조건
         ) p;
  
  DBMS_SQLSET.LOAD_SQLSET (  
    sqlset_name     => 'MYSTS'
  , populate_cursor =>  c1 
  );
END;
/

DBMS_SQLSET.SELECT_CURSOR_CACHE 프로시저의 basic_filter 파라미터를 통해 STS 수집 조건을 지정할 수 있습니다. 위 예시는 사용자 DATAWAY가 실행한 쿼리만 수집하도록 지정했습니다.

RAC 환경에서 STS를 수집할 때는 위 스크립트를 각 노드별로 실행해야 모든 노드의 SQL을 수집할 수 있습니다.

STS 수집 결과는 아래의 딕셔너리 뷰에서 조회할 수 있습니다.

DBA_SQLSET
DBA_SQLSET_STATEMENTS
DBA_SQLSET_PLANS
DBA_SQLSET_REFERENCES

2. SQL 실행 정보 이관 (운영DB → 복제DB)

수집한 STS를 복제DB로 옮기기 위해서는 별도의 스테이징 테이블에 저장(Pack)하고, 스테이징 테이블을 이관한 후에 다시 Unpack 해줘야 합니다.

2-1. 운영DB 작업

먼저 아래와 같이 스테이징 테이블을 생성합니다.

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('MYSTGTAB');

다음으로 STS를 스테이징 테이블로 저장합니다.

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET( SQLSET_NAME => 'MYSTS', STAGING_TABLE_NAME => 'MYSTGTAB' );

이렇게 생성된 스테이징 테이블의 데이터를 export / import 방식이나 DB Link를 이용해서 복제DB로 옮겨줍니다.

2-2. 복제DB 작업

이번 글에서는 DB LINK를 이용해 복제DB로 옮겨 보겠습니다.

먼저 운영DB와 마찬가지로 스테이징 테이블을 생성합니다.

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('MYSTGTAB');

다음으로 DB LINK를 이용해 스테이징 테이블의 데이터를 마이그레이션 합니다.

INSERT INTO MYSTGTAB SELECT * FROM MYSTG@REALDB;

다음은 스테이징 테이블의 데이터를 STS로 복원합니다.

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( SQLSET_NAME => 'MYSTS', REPLACE => TRUE, STAGING_TABLE_NAME => 'MYSTGTAB' );

생성된 STS가 이상 없는지 확인 합니다. STS는 다음의 딕셔너리 뷰를 통해 확인할 수 있습니다.

SELECT * FROM DBA_SQLSET WHERE SQLSET_NAME = 'MYSTS';

SELECT * FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME = 'MYSTS';

SELECT * FROM DBA_SQLSET_PLANS WHERE SQLSET_NAME = 'MYSTS';

SQL Performance Analyzer 사용법 – 오라클