꿈꾸는 개발자, DBA 커뮤니티 구루비

현재 데이터베이스 업무를 맡고 있다면 서비스가 갑자기 느려질 경우, 인덱스 생성이나 힌트 보정으로 문제를 해결할 수 있지 않을까라고 한번쯤은 생각해봤을 것이다. 하지만 이런 작업의 대부분은 애플리케이션을 수정해야 하는 경우가 많으며 이는 수정 단계에서 많은 시간을 소모한다.

예로 오라클의 ERP(전사적자원관리시스템), SCM(공급망관리시스템), CRM(고객관계관리시스템) 등의 솔루션 패키지를 업무에서 사용 중이라면 직접적인 SQL 수정이 어려울 것이다. 이 시간에는 SQL을 수정하지 않고 어떻게 하면 성능을 개선할 수 있을지를 알아보자.

오라클은 업그레이드나 패치 적용, 파라미터 변경 등에서 발생되는 실행계획 변경으로 성능을 보장받지 못할 경우 이를 해결할 수 있는 두 가지 방안을 만들었다. 그것이 Stored Outline과 SQL Profile이다.

각각의 기능은 조금씩 다르지만 애플리케이션 SQL을 수정하지 않고 DBMS 자체적으로 실행계획을 변경해 제어할 수 있다는 것이 큰 매력이며 튜닝 시 실무에서도 유용하게 사용된다.

그 중에 성능 이슈로 인한 애플리케이션 수정이 어려울 때 효과적으로 사용 가능한 SQL Profile에 대해 설명해본다.

Stored Outline

Stored Outline은 Oracle 8i에서 추가된 기능으로 어떤 환경의 변화가 있어도 수행자가 원하는 방향의 실행계획이 나올 수 있도록 SQL의 HINT를 직접 수정해 제어하는 데 그 목적이 있으며, 약간의 트릭으로 성능 개선 대안을 찾을 수 있다. 그럼 절차에 대해 간단히 정리해 보자.

1. 성능 이슈가 있는 튜닝 전 SQL에 대해 Stored Outline을 생성한다.
2. 튜닝 후 SQL에 대해 Stored Outline을 생성한다.
3. 튜닝 후 Outline의 실행계획 데이터를 튜닝 전 Outline에 덮어쓴다.
4. ALTER SYSTEM CREATE_SOTRED_OUTLINES=TRUE 또는 ALTER SESSION SET USE_STORED_OUTLINES=TRUE로 OUTLINE을 활성화시켜 튜닝 전 SQL이 튜닝 후 SQL Outline과 동일하게 실행계획이 수행되도록 한다.

SQL Profile

SQL Profile은 Oracle 10g부터 도입된 기능으로 그 내부는 Stored Outline과 유사한 방식으로 구현됐다. Stored Outline이 실행계획을 고정해 튜닝하는 것이 목적이라고 본다면 SQL Profile은 실질적인 SQL Tuning이라고 보면 되며 그 절차는 다음과 같다.

1. 성능 이슈가 있는 SQL의 실행계획에서 쿼리 블록을 가리키는 SEL$ 정보를 확인한다.
2. 튜닝된 SQL의 실행계획에서 쿼리 블록을 가리키는 SEL$ 정보를 확인한다.
3. 튜닝하고자 하는 SEL$ 정보에 대한 수정 부분을 Hidden Procedure인 DBMS_SQLTUNE. IMPORT_SQL_PROFILE을 이용해 수정한다.
4. Outline을 적용 후 실행계획이 의도대로 변경됐는지를 확인한다.

SQL Profile을 이용하는 튜닝 절차는 Stored Outline과 조금 다르지만 Outline을 변경해 튜닝한다는 점에서 그 기능이 유사하다. 튜닝할 부분의 Outline 구간을 정확히 인식한다면 Stored Outline보다 쉽게 변경할 수 있는 장점이 있다.

그럼 이를 이용해 어떻게 튜닝을 진행하면 되는지 알아보자. [리스트 1]은 실행계획 및 SQL_ID와 Outline을 확인하는 단계로 성능 이슈가 있는 구간을 탐색하는 방법을 보여주고 있다. 이 예제에서는 성능 이슈가 중첩루프 조인에서 발생된다고 가정해보자.

[리스트 1] 성능 이슈가 되는 SQL 구문 조회

SELECT /*+ USE_NL(A B) */
       A.EMPLOYEE_ID, A.FIRST_NAME, B.DEPARTMENT_ID, B.DEPARTMENT_NAME
  FROM EMPLOYEES A, DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
   AND A.JOB_ID = 'SA_REP' AND A.DEPARTMENT_ID > 1;
   
   
-- 생략 --
SELECT PLAN_TABLE_OUTPUT  
  FROM TABLE(dbms_xplan.DISPLAY_CURSOR('', '', 'OUTLINE'));
----------------------------------------------------------
SQL_ID  88j03p40kgkwt, child number 0


/*+   -- 생략 --
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("DEPARTMENTS". "DEPARTMENT_ID"))
INDEX(@"SEL$1" "A"@"SEL$1" ("EMPLOYEES"."JOB_ID" "EMPLOYEES"."DEPARTMENT_ID"))
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
USE_NL(@"SEL$1" "A"@"SEL$1")
END_OUTLINE_DATA
*/

[리스트 2]는 [리스트 1]을 튜닝한 결과에 대해 실행계획 및 Outline을 확인하는 단계를 보여준다. 이 예제에서는 성능 이슈를 해시 조인으로 해결한다고 가정해보자.

[리스트 2] 성능 이슈가 되는 SQL 구문 튜닝

SELECT /*+ USE_HASH(A B) */
       A.EMPLOYEE_ID, A.FIRST_NAME, B.DEPARTMENT_ID, B.DEPARTMENT_NAME
  FROM EMPLOYEES A, DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
   AND A.JOB_ID = 'SA_REP'  
   AND A.DEPARTMENT_ID > 1;
   
--------------------------------------------------------
|   0 | SELECT STATEMENT                |               |  
|*  1 |  HASH JOIN                      |               |  
|   2 |   TABLE ACCESS BY INDEX ROWID   | DEPARTMENTS   | 
|*  3 |    INDEX RANGE SCAN             | DEPT_ID_PK    |
|*  4 |   TABLE ACCESS FULL             | EMPLOYEES     | 
--------------------------------------------------------


SELECT PLAN_TABLE_OUTPUT  
  FROM TABLE(dbms_xplan.DISPLAY_CURSOR('', '', 'OUTLINE')) ; 
  /*+  -- 생략 --
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("DEPARTMENTS". "DEPARTMENT_ID"))
      FULL(@"SEL$1" "A"@"SEL$1")
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
      USE_HASH(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA
 */

[리스트 3]은 성능 이슈 SQL_ID의 FULLTEXT를 찾아 이슈 구간의 쿼리 블록을 가리키는 SEL$ 정보에 대해 튜닝 적용 후의 Profile 정보로 등록해주는 예제다.

[리스트 3] Profile 수정

declare L_SQLTEXT CLOB; 
begin 
-- 튜닝전 SQL_ID 
SELECT SQL_FULLTEXT INTO L_SQLTEXT 
  FROM V$SQL 
 WHERE SQL_ID='88j03p40kgkwt';
  
  dbms_sqltune.import_sql_profile
  ( sql_text=> L_SQLTEXT
  , profile => sqlprof_attr('USE_HASH(@"SEL$1" "A"@"SEL$1")') );
end;  /

[리스트 4]는 [리스트 3]에서 Profile 등록 후 동일한 SQL이 재수행될 때 SQL Profile(SYS_SQLPROF_014efe78724 e0006)을 참조해 실행계획이 수행되는지를 보여주는 예제다.

[리스트 4] 중첩루프 조인 시 SQL Profile을 참조해 해시 조인으로 수행

SELECT /*+ USE_NL(A B) */
       A.EMPLOYEE_ID, A.FIRST_NAME, B.DEPARTMENT_ID, B.DEPARTMENT_NAME
  FROM EMPLOYEES A, DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
   AND A.JOB_ID = 'SA_REP' AND A.DEPARTMENT_ID > 1;
---------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |
|*  1 |  HASH JOIN                       |               |
|   2 |   TABLE ACCESS BY INDEX ROWID    | DEPARTMENTS   |
|*  3 |    INDEX RANGE SCAN              | DEPT_ID_PK    |  
|*  4 |   TABLE ACCESS FULL              | EMPLOYEES     |   
---------------------------------------------------------
Note
- SQL profile "SYS_SQLPROF_014efe78724e0006" used for this statement

비교적 간단하게 Outline 수정으로 실행계획을 최적으로 유도해 성능을 개선하는 방법을 설명했다. 이는 긴급하게 성능 개선이 필요할 때 유용하게 쓰일 수는 있으나 Outline은 의미 그대로 요약본을 저장하고 있다가 이를 참조해 실행계획을 수립하는 것이므로 상황에 따라 옵티마이저에 의해 100% 동작하지 않을 수 있음을 감안해야 한다.

여러분이 관리하는 운영 시스템에서 수정이 불가한 애플리케이션에 문제가 발생했을 때 이런 제어 방법을 알고 있다면 장애요인 예방이나 안정적인 운영에 큰 도움이 되리라 생각한다.