[ORACLE] SPM ( SQL Plan Management ) 로 실행 계획 관리하기

SQL 의 성능은 시스템 운영에 매우 중요합니다.

예를들어 사용자가 평소 1초내로 응답받던 결과가, 갑자기 느려지면 사용자는 그 서비스를 더이상 사용하지 않을수도 있습니다.

이번 포스팅은 SPM(SQL Plan Management 실행계획 관리) 을 통해 SQL 성능이 갑자기 느려졌을 때, 대처하는 방법입니다.

SPM 을 관리함으로써 아래와 같은 효과를 얻을 수 있습니다.

 - 성능 안정화

 - 성능 개선

먼저 SPM 아키텍처에 대해 알아봅니다.

SPM 은 SQL 성능이 저하될 경우를 대비해 미리 실행계획을 저장합니다.

이렇게 저장된 실행계획은 'SQL 계획 베이스라인' 이라고 불립니다.

실행계획은 SQL 단위로 다수개가 저장될 수 있습니다.

SPM 은 SQL 관리 베이스라는 딕셔너리 뷰에서 관리되며, SYSAUX 테이블 스페이스에 저장됩니다.

SQL 관리 베이스는 SQL 별로 실행계획이 저장되며 SQL 계획 이력과 베이스라인이 있습니다.

말로는 어려우니 그림으로 좀 더 쉽게 풀어보겠습니다.

SQL 관리 베이스	설명
SQL 계획 이력	특정 SQL 과 관련된 모든 실행계획  미승인된 이력들은 SQL 실행계획에 사용되지 않음
SQL 계획 베이스라인	승인되어 사용될 수 있는 SQL 실행계획  하나 이상의 실행 계획이 존재하며, 이 때는 적합한 실행계획을 옵티마이저가 채택하여 사용

SPM 은 베이스라인에 저장된 실행계획으로만 SQL 구문을 실행해 성능에 영향을 미치는 것을 방지합니다.

만약 특정 SQL 의 베이스라인에 하나의 실행계획만 저장한다면, 갑자기 실행계획이 변하는 일을 방지할 수 있습니다.

그렇다면, 이 베이스라인은 어디에 저장될까요?

SQL 계획 베이스라인은 DBA_SQL_PLAN_BASELINES 딕셔너리 뷰에서 확인할 수 있습니다.

SQL 계획 베이스라인을 자동캡쳐로 사용하기 위해서는 아래 프로퍼티처럼 설정되어 있어야 합니다.

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 는 실행계획을 자동으로 캡쳐하는,

OPTIMIZER_USE_SQL_PLAN_BASELINES 가 TRUE 인 경우 SPM 을 사용해 SQL 구문을 파싱, FALSE 라면 일반적인 하드파싱으로 실행계획을 결정합니다.

SQL> SHOW PARAMETER SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     FALSE

DBA_SQL_PLAN_BASELINES 뷰에서는 생성된 실행계획을 위해 다음과 같은 컬럼을 확인합니다.

컬럼	설명
SQL_HANDLE	SPM 으로 SQL 을 식별하기 위한 판별자
PLAN_NAME	SPM 에서 실행 계획을 식별하기 위한 식별자
CREATED	SQL 이력의 생성 시간
ACCEPTED	승인 여부
ENABLED	실행 계획을 사용할 수 있는지 여부
SQL_TEXT	SQL 텍스트

SQL> SELECT SQL_HANDLE, PLAN_NAME,    
  2         TO_CHAR(CREATED, 'YYYY/MM/DD HH24:MI:SS') CREATED,
  3         ACCEPTED, ENABLED, SQL_TEXT
  4  FROM DBA_SQL_PLAN_BASELINES;

SQL_HANDLE           PLAN_NAME                      CREATED             ACC ENA SQL_TEXT
-------------------- ------------------------------ ------------------- --- --- -----------------------------------------------------------------
SQL_115024ccba5e158c SQL_PLAN_12n14tkx5w5cc52d2775d 2018/08/27 16:16:48 YES YES DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
SQL_294c437e331fa51f SQL_PLAN_2km23gstjz98zdf463620 2018/08/27 16:16:48 YES YES SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABL
                                                                                E', :1))

SQL_85372e07e425b213 SQL_PLAN_8adtf0zk2bchm3e1c8782 2018/08/27 16:18:58 YES YES SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'s
                                                                                tring',3,'integ

SQL_9c0d7998b1d28680 SQL_PLAN_9s3btm2sx51n0c51fbe40 2018/08/27 16:14:19 YES YES SELECT * FROM DBA_SQL_PLAN_BASELINES
SQL_dc84ded2dcd1bea1 SQL_PLAN_dt16yubfd3gp14ae4c79a 2018/08/27 16:16:48 YES YES SELECT * FROM EMP, DEPT ORDER BY 1

ACCEPTED 컬럼이 YES 라면 승인된 SQL 계획 베이스라인 입니다.

ACCEPTED 와 ENABLED 컬럼 모두 YES 일 때 실행계획으로 사용할 수 있습니다.

SQL_HANDLE 컬럼은 SQL 을 구분하기 위한 값이며, 같은 SQL 이라면 같은 SQL_HANDLE 을 가집니다.

PLAN_NAME 컬럼은 실행 계획을 식별하는 식별자이므로 각각 다른 값을 가집니다.

만약 실행 계획을 변화 없이 유지하고자 하는 SQL 구문은, 성능이 좋은 실행 계획을 SQL 계획 베이스라인으로 저장한 뒤

초기화 매개변수 OPTIMIZER_USE_SQL_PLAN_BASELINES 를 TRUE 로 설정하여 진행합니다.

등록한 실행 계획을 사용할 수 없는 경우도 있습니다.

만약 인덱스를 타는 실행계획을 베이스라인에 저장했는데 인덱스가 삭제되는 경우, 더이상 그 실행계획은 사용될 수 없습니다.

그렇다면 베이스라인에 등록된 실행계획이 실제로 사용되는지 확인은 어떻게하는지 알아봅니다.

DBMS_XPLAN.DISPLAY_CURSOR 의 NOTE, V$SQL 의 SQL_PLAN_BASELINE 컬럼에서 확인이 가능하지만, 현재 커서가 열려있는 경우에만 확인이 가능합니다.

커서가 닫힌 경우에도 확인을 원하는 경우에는

DBA_SQL_PLAN_BASELINES 의 LAST_EXECUTED 컬럼을 보면 베이스라인을 사용한 마지막 시간을 출력합니다.

하지만 이 컬럼은 한번 지정되면 6.5 일 동안 사용되더라도 변경되지 않으므로 항상 최신은 아닙니다.

만약 어떤 이유로 실행계획이 사용되지 못할때는 REPRODUCED 컬럼 값이 NO 로 변경되므로 이 컬럼으로 현재 사용되는지 확인이 가능합니다.

SQL> SELECT SQL_HANDLE, PLAN_NAME, LAST_EXECUTED, SQL_TEXT FROM DBA_SQL_PLAN_BASELINES;

SQL_HANDLE           PLAN_NAME                      LAST_EXECUTED     SQL_TEXT
-------------------- ------------------------------ ----------------- -----------------------------------------------------------------
SQL_115024ccba5e158c SQL_PLAN_12n14tkx5w5cc52d2775d 27-AUG-18 04.16.4 DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
                                                    8.000000 PM

SQL_294c437e331fa51f SQL_PLAN_2km23gstjz98zdf463620 27-AUG-18 04.16.4 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABL
                                                    8.000000 PM       E', :1))

SQL_435692ce8c04cbda SQL_PLAN_46pnktu609kyub73cade2 27-AUG-18 04.57.2 SELECT SQL_ID, SQL_PLAN_BASELINE, SQL_TEXT FROM V$SQL
                                                    6.000000 PM

SQL_85372e07e425b213 SQL_PLAN_8adtf0zk2bchm3e1c8782 27-AUG-18 04.18.5 SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'s
                                                    8.000000 PM       tring',3,'integ

SQL_9c0d7998b1d28680 SQL_PLAN_9s3btm2sx51n0c51fbe40 27-AUG-18 04.14.1 SELECT * FROM DBA_SQL_PLAN_BASELINES
                                                    9.000000 PM

SQL_dc84ded2dcd1bea1 SQL_PLAN_dt16yubfd3gp14ae4c79a 27-AUG-18 04.16.4 SELECT * FROM EMP, DEPT ORDER BY 1
                                                    8.000000 PM