반응형
실행계획을 확인하는 방법에는 여러가지가 있습니다.
그 중에서 간단하게 확인할 수 있는 방법을 포스팅하겠습니다.
실행계획 확인하기
1.쿼리 수행시 explain plan for 적기
해당 명령문을 포함하여 쿼리를 실행하면 결과값이 나오지 않고, statement processed라는 명령어가 나옵니다. 이 명령어는 SQL의 예상 실행계획을 만들어서 PLAN 테이블에 저장하기만 합니다. PLAN테이블에 저장된 실행계획은 DBMS_XPLAN.DISPLAY명령어를 사용해서 확인 할 수 있습니다.
SQL> explain plan for
select * from emp where employee_id = 100;
Statement Processed.
SQL Execution Time > 00:00:00.000
2.DBMS_XPLAN.DISPLAY 명령어로 확인
SQL> select * from table(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 69 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
13 rows selected.
SQL Execution Time > 00:00:00.547
Total Elapsed Time > 00:00:00.579
실제 실행계획 확인하기
앞에서 봤던 실행계획은 예상 실행계획입니다. 실제 실행계획에는 오퍼레이션별로 실제 소모한 시간과 실제 수행한 IO 횟수 등이 나옵니다. 성능 개선 전과 후를 정확히 비교하려면 실제 실행계획을 이용해야합니다.
실제 실행계획을 확인하려면 SQL에 'GATHER_PLAN_STATISTICS' 힌트를 사용하면 됩니다. 이 외에도 세션에 트레이스(Trace)를 거는 방법이 있지만 저는 GATHER_PLAN_STATISTICS를 사용하는 방법을 포스팅하겠습니다.
힌트가 포함된 SQL이 실행 완료된 후에는 DBMS_XPLAN.DISPLAY_CURSOR를 이용해 실행계획을 확인할 수 있으며, 이를 이용하기 위해서는 아래의 뷰에 select 권한이 필요합니다.
V_$SQL
V_$SQL_PLAN_STATISTICS_ALL
V_$SQL_PLAN
V_$SESSION
1.쿼리 수행 (힌트에 GATHER_PLAN_STATISTICS 추가)
SQL> select /*+ GATHER_PLAN_STATISTICS */
* from emp
where employee_id > 100
and employee_id < 200
and salary > 2000;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------------------- -------------------- ------------------------- ------------------------- ------------------------ -------------------- ------------ ------------ ---------------------------- -------------------- --------------------------
101 Neena Kochhar NKOCHHAR 515.123.4568 2005/09/21 00:00:00 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001/01/13 00:00:00 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 2006/01/03 00:00:00 IT_PROG 9000 102 60
...
99 rows selected.
2.해당 쿼리의 SQL_ID,CHILD_NUMBER 확인
v$sql 뷰를 활용하여 수행한 SQL의 ID를 확인하는 쿼리입니다.
SQL_ID는 334u82f798stx 이고 CHILD_NUMBER=0인것을 확인할 수 있습니다.
SQL> select sql_id,child_number,sql_text
from v$sql
where sql_text like '%GATHER_PLAN_STATISTICS%'
order by last_active_time desc;
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
348k14p7uucxt 0 select sql_id,child_number,sql_text from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%' order by last_active_time desc
334u82f798stx 0 select /*+ GATHER_PLAN_STATISTICS */ * from emp where employee_id > 100 and employee_id < 200 and salary > 2000
2 rows selected.
3.DBMS_XPLAN.DISPLAY_CURSOR 수행
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('334u82f798stx',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 334u82f798stx, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from emp where employee_id > 100
and employee_id < 200 and salary > 2000
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.01 | 11 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 100 | 99 |00:00:00.01 | 11 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("EMPLOYEE_ID"<200 AND "EMPLOYEE_ID">100 AND "SALARY">2000))
19 rows selected.
E-ROWS는 예상 데이터 건수이고, A-ROWS는 실제 데이터 건수입니다.
반응형
'SQL(쿼리)' 카테고리의 다른 글
| Decode 함수와 형 변환 (0) | 2021.12.29 |
|---|---|
| Oracle RANK 함수 (0) | 2021.07.06 |
| 트리거 생성(IF문 활용해서 Not Null인 컬럼만 트리거 작동) (0) | 2020.12.07 |
| SCOTT스키마예제를 통해 계층구조 쿼리 정리 (0) | 2020.11.25 |
| ROLLUP을 대신하는 방법 (0) | 2020.11.24 |
댓글