[SQLP] 실행 계획 (Execution plan)

1. 실행 계획(Execution plan)

sql을 실행할 때 일련의 처리 과정을 나타낸다.

각각의 단계들이란 데이터베이스의 연산들을 의미한다.

 

읽는 방법

EXPLAIN plan for
select * from orders CROSS join RETURNS;
##EXPLAIN 하여 실행 계획 출력

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
##실행 계획 결과 확인하기

Plan hash value: 857115545
 
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |    71M|    55G|   218K  (1)| 00:00:09 |
|   1 |  MERGE JOIN CARTESIAN|         |    71M|    55G|   218K  (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL  | ORDERS  | 61376 |    46M|   481   (1)| 00:00:01 |  
|   3 |   BUFFER SORT        |         |  1173 | 52785 |   217K  (1)| 00:00:09 |
|   4 |    TABLE ACCESS FULL | RETURNS |  1173 | 52785 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

1. 들여쓰기가 되어있는 ID 가 먼저 실행된다.  
2. 들여쓰기가 같이 되어있는 실행은 위에서 부터 실행된다.   > 들여쓰기가 동일할 경우 동일한 행의 첫번째 부터 읽는다.
예) ID 2 번이 먼저 실행된 다음 바로 더 들여쓰여있는 4번을 실행한다.  

3. ID 4번이 ID 3번보다 더 들여쓰기 되어있기 때문에 ID 4번이 먼저 실행된다 
따라서 실행 순서는 ID 순으로 2-4-3-1-0 이다.

 

 2. AutoTrace 사용 방법 ★★★

autotrace는 sqlplus 에서 사용하는 명령어 이다.

 

주관식으로 많이 출제된다.

SET AUTOTRACE ON;

SET AUTOTRACE ON [EXPLAIN|STATISTICS];
##STATISTICS: 사용량 통계 정보를 출력해준다.
##테이터블록을 얼마나 읽었는가, CPU, MEM 은 얼마나 읽었는가를 표현해준다.
##EXPLAIN: 실행계획

SET AUTOTRACE TRACE[ONLY] ON [EXPLAIN|STATISTICS];

 

예제)

SQL> SET AUTOTRACE ON STATISTICS;
SQL> SELECT * FROM EMP WHERE SAL >=4000;


Statistics
-------------------------------------------------------------
      0    recursive calls ## 내부의 call
      0    db block gets  
      8    consistent gets  ##버퍼캐쉬 읽은 블럭 
      0    physical reads   ## 디스크를 읽은 블럭
      0    redo size
   1034    bytes sent via SQL*Net to client
    519    bytes received via SQL*Net from client
      2    SQL*Net roundtrips to/from client   ##user call 수
      0    sorts(memory)    ##PGA 공간에서 보통 발생한 sorts
      0    sorts(disk)      ##PGA 공간이 차서 disk 에서 발생한 sorts
      1    rows processed 
      
      ##sort: 전체 데이터의 정렬 작업

 

 

  3. Gather statistic 힌트 사용법

/*+GATHER_PLAN_STATISTICS*/* 의 명령어를 사용하여 실제 쿼리를 실행하고

그 실행한 결과에 대한 내역을 조회하는 힌트를 사용한다.

아래의 예시를 보면 차이 점이 Buffers 가 추가되었으며, 실제 블럭을 얼마나 사용했는지를 알 수 있다.

SELECT /*+GATHER_PLAN_STATISTICS*/* FROM ORDERS CROSS JOIN returns;

SELECT * FROM TABLE (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


SQL_ID  975uuzkyuuc5r, child number 0
-------------------------------------
SELECT /*+GATHER_PLAN_STATISTICS*/* FROM ORDERS CROSS JOIN returns
 
Plan hash value: 857115545
 
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |      1 |        |     50 |00:00:00.01 |      18 |     20 |       |       |          |
|   1 |  MERGE JOIN CARTESIAN|         |      1 |     60M|     50 |00:00:00.01 |      18 |     20 |       |       |          |
|   2 |   TABLE ACCESS FULL  | ORDERS  |      1 |  51290 |      2 |00:00:00.01 |       3 |      6 |       |       |          |
|   3 |   BUFFER SORT        |         |      1 |   1173 |     50 |00:00:00.01 |      15 |     14 | 80896 | 80896 |71680  (0)|
|   4 |    TABLE ACCESS FULL | RETURNS |      1 |   1173 |   1173 |00:00:00.01 |      15 |     14 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

E-Rows:  통계 정보에 근거한 예측 row 수

A-Rows:  실제 쿼리 수행 결과에 따른 row 수 

A-Time: 쿼리 실행 결과에 따른 실제 수행 시간

Buffers: 논리적인 get block 의 수, 옵티마이저가 일한 총량으로 튜닝에서 중요하게 생각하는 요소

 

따라서 실제 쿼리를 한 결과와 Execution plan을 한 결과가 다른걸 비교할 수 있다.