SQL 튜닝 팁

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

방법 : 

1. 실제 실행계획 출력하실 때 아래 포맷으로 해보세요.

select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last -projection'));

몇 가지 항목이 추가로 출력되는데 아래 2개은 튜닝을 하는데 굉장히 중요한 정보를 제공해 줍니다.

outline data <-- 어떤 힌트들이 적용되었는지

query block  <-- query block name확인 하실수 있어요.

10046트레이스는 굳이 필요없구요. 대부분의 경우 귀찮기만 하지 별도움이 안됩니다.

1_2. 테스트

실행계획 확인할 쿼리 실행

SQL> select E.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;
 
     EMPNO ENAME      DNAME
---------- ---------- --------------
      7839 KING       ACCOUNTING
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7566 JONES      RESEARCH
      7788 SCOTT      RESEARCH
      7902 FORD       RESEARCH
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7654 MARTIN     SALES
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH
      7900 JAMES      SALES
      7934 MILLER     ACCOUNTING

기존 방법(TYPICAL)으로 실행계획확인

SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    b28bvnnn551tr, child number 0
-------------------------------------
select E.empno, e.ename, d.dname from emp e, dept d where e.deptno =
d.deptno
 
Plan hash value: 615168685
 
---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      |      |    6 (100)|      |
|*  1 |  HASH JOIN       |      |    14 |   770 |    6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    4 |    88 |    3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   462 |    3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPTNO"="D"."DEPTNO")
 
Note
-----
   - dynamic sampling used for this statement (level=2)

더 좋은 방법(advanced allstats last -projection)으로 실행계획 확인

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last -projection'));
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID    b28bvnnn551tr, child number 0
-------------------------------------
select E.empno, e.ename, d.dname from emp e, dept d where e.deptno =
d.deptno
 
Plan hash value: 615168685
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|       |       |       |          |
|*  1 |  HASH JOIN       |      |    14 |   770 |     6   (0)| 00:00:01 |  1599K|  1599K|  955K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   462 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / E@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_HASH(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPTNO"="D"."DEPTNO")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

ALLSTATS : Plan Statistics에 있는 모든 정보를 보여 달라는 의미

LAST : 가장 최근에 수행한 정보만 보여달라는 의미

-PROJECTION : Column Projection은 제외하고 보여달라는 의미

몇 가지 항목이 추가로 출력되는데 아래 2개은 튜닝을 하는데 굉장히 중요한 정보를 제공해 줍니다.

outline data <-- 어떤 힌트들이 적용되었는지

query block  <-- query block name확인 하실수 있어요.

2. 복잡한 쿼리의 경우에는 차근차근 뷰를 벗겨 보는게 좋습니다. 각 단계마다 실제실행계획 파일로 저장해 놓고

어떤 단계에서 비효율이 발생하는지 보시면 됩니다.

select *
from (
select ＊ from t1 where ... ;
) where ....;
...

이런식으로 쭉 되어 있다면

select ＊ from t1 where ... ;

요기만 따로 벗겨내서 실행계획 체크해봅니다.

참고내용

DBMS_XPLAN.DISPLAY_CURSOR란?

DBMS_XPLAN.DISPLAY

- 예측정보이므로 정확한 튜닝결과를 알 수 없다.

10046 Trace

- 실측정보이므로 강력하지만 Trc 파일을 얻으려면 서버에 접근해야 함

- 하지만 일반 사이트에서는 개발자들에게 서버 접근 권한을 주지 않음

- 그러므로 개발자들이 Trc 파일을 이용하기에는 버거운 상태임

DBMS_XPLAN.DISPLAY_CURSOR

- 그럼 개발자들은 튜닝을 할 수 없는 것인가?

- 해결방법은 DBMS_XPLAN.DISPLAY_CURSOR

- DISPLAY가 예측치를 보여주는 거라면 DISPLAY_CURSOR는 실측치를 보여줌

- 단일 SQL문에 대해 실제 수행된 실행계획을 보여주는 Function

Function Parameter

FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
                        CURSOR_CHILD_NO INTEGER DEFAULT 0,
                        FORMAT VARCHAR2 DEFAULT 'TYPICAL')

    

파라미터 설명

파라미터	설명
TABLE_NAME	실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다.
	SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다.
	SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다.
STATEMENT_ID	해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
	CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다.
FORMAT	저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터

FORMAT 옵션

구분	FORMAT	내용
예측 내용	BASIC	DBMS_XPLAN.DISPLAY 포멧과 같은 내용
	TYPICAL	DBMS_XPLAN.DISPLAY 포멧과 같은 내용
	ALL	DBMS_XPLAN.DISPLAY 포멧과 같은 내용
	OUTLINE	DBMS_XPLAN.DISPLAY 포멧과 같은 내용
	ADVANCED	DBMS_XPLAN.DISPLAY 포멧과 같은 내용
실측  내용	ALLSTATS	실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여준다.
		수행횟수에 따라 누적된 값을 보여준다.
	ALLSTATS LAST	실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여준다.
		수행횟수에 따라 누적된 값을 보여준다.
	ADVANCED ALLSTATS LAST	DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 Format의 정보를 보여준다.

참조 : https://cafe.naver.com/prodba/48060

http://www.gurubee.net/lecture/2137