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% 동작하지 않을 수 있음을 감안해야 한다.
여러분이 관리하는 운영 시스템에서 수정이 불가한 애플리케이션에 문제가 발생했을 때 이런 제어 방법을 알고 있다면 장애요인 예방이나 안정적인 운영에 큰 도움이 되리라 생각한다.
'ORACLE > SQL' 카테고리의 다른 글
| 피벗(2) - PIVOT, UNPIVOT, (행을 열로, 열을 행으로) (0) | 2020.12.29 |
|---|---|
| FETCH .. BULK COLLECT INTO (0) | 2020.07.22 |
| SQL trace _ 참조 (0) | 2019.10.02 |
| 복수행 함수 (그룹 함수) (0) | 2018.12.13 |
| 윈도우 함수(WINDOW FUNCTION) (0) | 2018.11.01 |