오라클은 9i부터 plan_table에 실행계획들을 저장하기 시작했고, DBMS_XPLAN패키지를 이용해 plan_table에 저장된 실행계획들을 좀 더 쉽게 출력해 볼 수 있게 되었습니다. 10g부터는 라이브러리 캐시에 캐싱돼 있는 SQL커서에 대한 실행계획은 물론 Row Source별 수행통계까지 손쉽게 출력해 볼 수 있도록 기능이 확장되었습니다. AWR에 수집된 과거 수행했던 SQL에 대한 실행계획을 확인하는 것도 가능합니다.
(1)예상 실행계획 출력
@?/rdbms/admin/utlxpls.sql 스크립트를 통해 실행계획을 출력하는 방법이 있는데, 그 스크립트를 열어보면 내부적으로 dbms_xplan패키지를 호출하는 것을 볼 수 있습니다.
utlxpls 내부
Rem
Rem $Header: rdbms/admin/utlxpls.sql /main/11 2017/05/28 22:46:13 stanaya Exp $
Rem
Rem utlxpls.sql
Rem
Rem Copyright (c) 1998, 2017, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem utlxpls.sql - UTiLity eXPLain Serial plans
Rem
Rem DESCRIPTION
Rem script utility to display the explain plan of the last explain plan
Rem command. Do not display information related to Parallel Query
Rem
Rem NOTES
Rem Assume that the PLAN_TABLE table has been created. The script
Rem utlxplan.sql should be used to create that table
Rem
Rem With SQL*plus, it is recomended to set linesize and pagesize before
Rem running this script. For example:
Rem set linesize 100
Rem set pagesize 0
...생략...
set markup html preformat on
Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Force serial option for backward compatibility
Rem
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
첫번째 인자에는 실행계획이 저장된 plan table명을 입력하고(기본적으로 sys.plan_Table$ -> synonym으로 PLAN_TABLE) 두번째 인자에는 statement_id를 입력하면 됩니다. NULL일 경우 마지막 explain plan 명령에 사용했던 쿼리의 실행계획을 보여 줍니다.
병렬 쿼리는 사용할 수 없으며 병렬 쿼리에 대한 정보를 볼려면 @?/rdbms/admin/utlxplp 스크립트를 수행해야 합니다.
그 외에도 dbms_xplan.display함수를 직접 쿼리하면 아래와 같이 세 번째 인자를 통해 다양한 포맷을 선택할 수 있습니다.
SQL> explain plan set statement_id = 'SQL1' for
2 select * from emp e, dept d
3 where e.deptno=d.deptno
4 and e.sal > 1000;
Explained.
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','SQL1','BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 844388907
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 3 | INDEX FULL SCAN | PK_DEPT |
| 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL | EMP |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------
12 rows selected.
1* select * from table(dbms_xplan.display('PLAN_TABLE','SQL1','TYPICAL'))
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 649 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 11 | 649 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 429 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 11 | 429 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL">1000)
19 rows selected.
1* select * from table(dbms_xplan.display('PLAN_TABLE','SQL1','SERIAL'))
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 649 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 11 | 649 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 429 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 11 | 429 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL">1000)
19 rows selected.
basic 옵션을 사용하면 ID,Operation,Name 컬럼만 보이는데 format 인자를 아래 처럼 구사한 Rows,Bytes,Cost컬럼까지 출력해줍니다.
1* select * from table(dbms_xplan.display('PLAN_TABLE','SQL1','BASIC ROWS BYTES COST'))
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 649 | 6 (17)|
| 1 | MERGE JOIN | | 11 | 649 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
| 4 | SORT JOIN | | 11 | 429 | 4 (25)|
| 5 | TABLE ACCESS FULL | EMP | 11 | 429 | 3 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
12 rows selected.
이 외에도 아래와 같은 옵션을 사용할 수 있습니다.
PARTITION
PARALLEL
PREDICATE
PROJECTION
ALIAS
REMOTE
NOTE
ALL 사용시 모든 항목을 다 보여줍니다.
1* select * from table(dbms_xplan.display('PLAN_TABLE','SQL1','ALL'))
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 649 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 11 | 649 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 429 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 11 | 429 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL">1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
43 rows selected.
OUTLINE 옵션 사용시 같은 실행계획을 수립하는 데 필요한 힌트 목록을 보여줍니다.
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 649 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 11 | 649 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 429 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 11 | 429 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_MERGE(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL">1000)
36 rows selected.
ADVANCED는 ALL과 OUTLINE을 함께 사용한 것과 같습니다.
select * from table(dbms_xplan.display('PLAN_TABLE','SQL1','ADVANCED'))
(2) 캐싱된 커서의 실제 실행계획 출력
커서는 하드 파싱과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계회그 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말합니다. 오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql뷰를 제공합니다. 이 뷰와 sql_id 값으로 조인해서 사용할 수 있도록 제공되는 뷰가 더 있는데 v$sql_plan과 v$sql_plan_statistics입니다. 이 두 뷰를 합쳐서 보여주는 것이 v$sql_plan_statistics_all 입니다.
SQL> set serveroutput off
SQL> select * from emp e,dept d
2 where e.deptno=d.deptno
3 and e.sal > 1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- -------------- -------------
7839 KING PRESIDENT 81/11/17 5000 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 81/06/09 2450 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 82/01/23 1300 10 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 81/12/03 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 81/04/02 2975 20 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 30 SALES CHICAGO
위와 같은 쿼리를 수행하고 나면 캐싱된 커서 정보를 v$sql에서 조회할 수 있고, v$sql_plan을 통해 실제 수행하면서 사용했던 실행계획까지 확인 해 볼 수 있습니다. v$sql_plan을 조회하려면 마지막 수행한 SQL의 sql_id와 child_number값을 알아야하는데, 쿼리 수행전에 serveroutput을 받드시 off 시켜야합니다. 그렇지 않으면 dbms_output.disable프로시저를 호출하는 커서의 sql_id를 찾게 됩니다.
방금 수행했던 sql의 sql_id와 child_number보는 쿼리입니다.
SQL> select sql_text,sql_id,child_number from v$sql where sql_text like 'select * from emp e'||'%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
------------- ------------
select * from emp e,dept d where e.deptno=d.deptno and e.sal > 1000
f3apb9d5vbzx8 0
v$sql_plan뷰를 일반 plan_table처럼 쿼리해서 원하는 방식으로 포맷팅할 수 있지만, dbms_xplan.display_cursor함수를 이용해도 됩니다.
sql_id와 child_number를 매번 찾는게 귀찮다면 NULL을 넣어주면 됩니다. 마지막 인자에는 dbms_xplan.display 함수에서 사용했던 옵션들을 그대로 사용할 수 있습니다.
SQL> select * from table(dbms_xplan.display_cursor('f3apb9d5vbzx8',0,'BASIC ROWS BYTES COST PREDICATE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from emp e,dept d where e.deptno=d.deptno and e.sal > 1000
Plan hash value: 844388907
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)|
| 1 | MERGE JOIN | | 11 | 649 | 6 (17)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
|* 4 | SORT JOIN | | 11 | 429 | 4 (25)|
|* 5 | TABLE ACCESS FULL | EMP | 11 | 429 | 3 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - filter("E"."SAL">1000)
24 rows selected.
dbms_xplan.display_awr 함수를 이용하면 AWR에 수집된 과거 수행했던SQL에 대해서도 같은 분석작업을 진행할 수 있습니다.
(3) 캐싱된 커서의 Row Source별 수행 통계 출력
SQL문에 gather_plan_Statistics 힌트를 사용하거나, 시스템 또는 세션 레벨에서 statistic_level 파라미터를 all로 설정하면 오라클은 실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션 단계(Row Source)별로 수행 통계를 수집합니다.
'_rowsource_execution_statistics'파라미터를 true로 설정하거나 SQL 트레이스를 걸어도 Row Source별 수행 통계가 수집됩니다.
조회할 때는 v$sql_plan_statistics 또는 v$sql_plan_statistics_all뷰를 이용하면 됩니다.
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ *
2 from emp e, dept d
3 where e.deptno=d.deptno
4 and e.sal > 1000;
이제 Row Source별 수행 통계가 수집되었습니다.
v$sql_plan_statistics를 조회하려면 마지막 수행한 SQL의 sql_id와 child_number값을 알아야 합니다.
1 select sql_text,sql_id,child_number from v$sql where sql_text like 'select /*+ gather_plan_statistics */'
2* ||'%'
SQL> /
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
------------- ------------
select /*+ gather_plan_statistics */ * from emp e, dept d where e.deptno=d.deptno and e.sal > 1000
0zd7bahaba3kt 0
아래뷰의 컬럼을 적허라게 사용하여 확인할 수 있습니다.
select * From v$sql_plan_statistics_all
where sql_id='0zd7bahaba3kt'
and child_number=0
order by id
SQL트레이스를 걸어도 같은 결과를 얻을 수 있으므로 TKProf이용 전에 먼저 Row Source Operation을 확인하고 Call 통계 정보는 필요할 때만 확인하는 식으로 이용할 수 있습니다.
v$sql_plan_statistics뷰에는 모든 통계항목에 대해 마지막 수행 통계치와 누적 통계치를 조회할 수 있도록 컬럼이 두 개씩 제공됩니다.
-output_rows,last_output_rows
-cr_buffer_gets,last_cu_buffer_gets
-disk_reads,last_disk_reads
따라서 마지막 수행통계나 누적 수행통계를 자유롭게 뽑아볼 수 있습니다.
dbms_xplan.display_cursor함수는 dbms_xplan.display 함수에는 없던 iostats,memstats,allstats옵션을 사용할 수 있으며 실제 수행 시 Row Source별 수행통계를 보여줍니다.
SQL> select * from table(dbms_xplan.display_cursor('0zd7bahaba3kt',0,'ALLSTATS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0zd7bahaba3kt, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from emp e, dept d where
e.deptno=d.deptno and e.sal > 1000
Plan hash value: 844388907
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 10 | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 11 | 10 |00:00:00.01 | 10 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 4 | 11 | 10 |00:00:00.01 | 6 | 2048 | 2048 | 1/0/0|
|* 5 | TABLE ACCESS FULL | EMP | 1 | 11 | 10 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL">1000)
25 rows selected.
'스터디 > 오라클 성능고도화 원리와 해법1' 카테고리의 다른 글
| CH03.오라클 성능관리 - 07. Response Time Analysis 방법론과 OWI 08.Statspack / AWR (0) | 2020.01.04 |
|---|---|
| CH03.오라클 성능관리 - 05.V$SYSSTAT,06.V$SYSTEM_EVENT (0) | 2020.01.02 |
| CH03.오라클 성능 관리 - 03.SQL 트레이스 (0) | 2020.01.01 |
| CH03.오라클 성능 관리 - 01.Explain plan 02.AutoTrace (0) | 2019.12.31 |
| CH2. 트랜잭션과 LOCK - 05.오라클 Lock (0) | 2019.12.29 |
댓글