SQL 실행계획 트레이스

오라클에서 Explain을 통해 실행계획을 보는 방법

 

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |    18 |   221 |     9  (34)| 00:00:01 |

|   1 |  SORT UNIQUE         |      |    18 |   221 |     9  (34)| 00:00:01 |

|   2 |   UNION-ALL          |      |       |       |            |          |

|   3 |    HASH GROUP BY     |      |    17 |   221 |     5  (40)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| EMP  |    17 |   221 |     3   (0)| 00:00:01 |

|   5 |    SORT AGGREGATE    |      |     1 |       |     4  (25)| 00:00:01 |

 

 

- Id : 실행계획에서 구분자

- Operation : 각 단계에서 어떤 작업 일어났는지 표시

- Name : 테이블 명 / index 명

- Rows : 해당 쿼리 계획단계 안에서 예상한 행 수

- Bytes : 행 길이에 따라 결정되며, 각 단계에 따른 데이터 바이트

- Cost : CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성하며 모든 쿼리에 대해 비용을 할당함

- Time : 각 단계별 수행시간

 

 

---

 

 

 

일반컬럼으로 조회

select * from crime where cname = '절도';

 

실행계획 :

 

Execution Plan

----------------------------------------------------------

Plan hash value: 351533637

---------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |      |    27 |  1539 |    3  (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| CRIME |    27 |  1539 |    3  (0)| 00:00:01 |    -> 테이블 전체 접근

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

 

predicate 정보

: 어떤 컬럼 값으로 access/filter 되었는지 확인

 

access : 인덱스 읽는 범위를 줄여주는 조건

filter : 인덱스에서 필터만 하는 조건

 

> 실행계획이 복잡해지면 where절 특정조건이 실행계획 각 단계에서 어떻게 적용되는지 확인

> Query Transformation 이라는 특별한 과정 때문에 Predicate의 변형이 발생할 때는 이 정보가 특히 중요함

 

 

  1 - filter("CNAME"='절도')

 

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        9  consistent gets

          0  physical reads

          0  redo size

      1469  bytes sent via SQL*Net to client

        427  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        27  rows processed

 

 

Statistics 출력

 

- recursive calls : 사용자의 SQL문을 실행하기 위하여 수행된 SQL 문의 수

- db block gets : 현재 모드(current mode)에서 버퍼 캐시로부터 읽어온 블록의 총 수

- consistent gets : 버퍼 캐시의 블록에 대한 일관된 읽기의 요청 횟수

- physical reads : 물리적으로 데이터 파일을 읽어 버퍼 캐시에 넣은 횟수

- redo size : 해당 문이 실행되는 동안 생성된 redo의 전체 크기를 바이트 단위로 나타낸 수

- bytes sent via SQL*Net to client : 서버로부터 클라이언트에 전송된 총 바이트 수

- bytes received via SQL*Net from client : 클라이언트로부터 받은 총 바이트 수

- SQL*Net roundtrips to/from client : 클라이언트로(부터) 전송된 SQL*Net 메시지의 총 수. 다중 행 결과 집합으로부터 꺼내오기 위한 왕복을 포함.

- sorts(memory) : 사용자의 세션 메모리(정렬 영역)에서 수행된 정렬 sort_area_size 데이터베이스 매개변수에 의해 제어됨

- sorts(disk) : 사용자의 정렬 영역의 크기를 초과하여 디스크(임시 테이블 영역)를 사용하는 정렬

- rows processed : 수정되거나 select 문으로부터 반환된 행

 

 

 

---

 

 

 

index 생성 후

create index crime_cname_index on crime(cname);

 

실행계획 :

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2905372382

--------------------------------------------------------------------------------

| Id  | Operation                          | Name              | Rows  | Bytes

| Cost (%CPU)| Time    |

--------------------------------------------------------------------------------

|  0 | SELECT STATEMENT                    |                  |    27 |  1539

|    2  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CRIME            |    27 |  1539

|    2  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN                 | CRIME_CNAME_INDEX |    27 |       -> 인덱스 접근

|    1  (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  2 - access("CNAME"='절도')

 

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        7  consistent gets

          0  physical reads

          0  redo size

      1655  bytes sent via SQL*Net to client

        427  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        27  rows processed

 

 

> 9 -> 7로 block읽은 개수 낮아짐.

> 읽은 block개수 낮아야 성능이 좋다.

 

 

- consistent gets : 메모리에서 읽은 block 개수

- physical reads : 디스크에서 읽은 block 개수

 

 

 

참고 :

https://m.blog.naver.com/sophie_yeom/220891529668

https://positivemh.tistory.com/364