실행계획
실행계획 확인 방법(도구)
- explain plan(파스만)
- plann_table에 저장(temporary table, $ORACLE_HOME/rebms/admin/utlxplan(영구테이블))
- dbms_xplan.display
- from table(dbms_xplan.display)
====================================================================================================
vi setup.sql
SET echo on
CONN / AS SYSDBA
DROP USER user01 CASCADE ;
DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES ;
CREATE TABLESPACE userdata DATAFILE '/u01/app/oracle/orcl2/userdata.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ;
CREATE OR REPLACE DIRECTORY DATA_DIR AS '/home/oracle/sqlt' ;
HOST impdp system/oracle DIRECTORY=DATA_DIR DUMPFILE=user01.dmp
EXECUTE dbms_stats.gather_schema_stats('USER01') ;
CONN user01/oracle
SET echo off
====================================================================================================
SYS@orcl2> @setup
USER01@orcl2> @idx
Enter value for tab_name: emp
INDEX_NAME INDEX_TYPE UNIQUENESS COLUMNS
------------------------------ --------------- --------------- ------------------------------
EMP_JOB_IX NORMAL NONUNIQUE JOB
EMP_MGR_IX NORMAL NONUNIQUE MGR
EMP_SAL_IX NORMAL NONUNIQUE SAL
EMP_COMM_IX NORMAL NONUNIQUE COMM
EMP_EMPNO_IX NORMAL UNIQUE EMPNO
EMP_ENAME_IX NORMAL NONUNIQUE ENAME
EMP_DEPTNO_IX NORMAL NONUNIQUE DEPTNO
EMP_HIREDATE_IX NORMAL NONUNIQUE HIREDATE
8 rows selected.
Elapsed: 00:00:00.23
USER01@orcl2> explain plan for
select * from emp where deptno = 10;
Explained.
Elapsed: 00:00:00.02
USER01@orcl2> select * from plan_table;
-- 요놈을 간단하게 한 것이 아래
USER01@orcl2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1672835495
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO_IX | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
-- Rows : 옵티마이저가 예측한 행 수를 표시
-- Bytes : 옵티마이저가 예측한 바이트 수를 표시
-- Cost : 옵티마이저 비용정보를 표시
-- Predicate : 술어 섹션을 표시
14 rows selected.
Elapsed: 00:00:00.08
USER01@orcl2> explain plan set statement_id='demo01' for
select * from emp
where deptno = 10;
Explained.
USER01@orcl2> explain plan set statement_id='demo02' for
select * from emp
where job='CLERK';
Explained.
USER01@orcl2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 825126280
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"='CLERK')
14 rows selected.
USER01@orcl2> select * from table(dbms_xplan.display('plan_table', 'demo01', 'TYPICAL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1672835495
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO_IX | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
USER01@orcl2> select * from table(dbms_xplan.display('plan_table', 'demo02', 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 825126280
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"='CLERK')
14 rows selected.
USER01@orcl2> select * from table(dbms_xplan.display(null, null, 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 825126280
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"='CLERK')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9],
"EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "JOB"[VARCHAR2,9]
28 rows selected.
- autotrace
- plustrace role이 필요
- SQL> @$ORACLE_HOME/sqlplus/admin/plustrce
- 예를 들어 scott이라는 유저는 v$를 볼 권한이 없다. 실행계획은 v$에 있기 때문에 권한 부여가 필요
- SQL> set autot on exp
SCOTT@orcl2> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SYS@orcl2> !ls $ORACLE_HOME/sqlplus/admin
glogin.sql help libsqlplus.def plustrce.sql pupbld.sql
SYS@orcl2> @$ORACLE_HOME/sqlplus/admin/plustrce
====================================================================================================
SYS@orcl2> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SYS@orcl2> create role plustrace;
Role created.
SYS@orcl2>
SYS@orcl2> grant select on v_$sesstat to plustrace;
Grant succeeded.
SYS@orcl2> grant select on v_$statname to plustrace;
Grant succeeded.
SYS@orcl2> grant select on v_$mystat to plustrace;
Grant succeeded.
SYS@orcl2> grant plustrace to dba with admin option;
Grant succeeded.
SYS@orcl2> set echo off
====================================================================================================
SYS@orcl2> grant plustrace to scott, hr;
Grant succeeded.
SCOTT@orcl2> set autotrace on
SCOTT@orcl2> select * from emp where empno = 7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Statistics --SQL문장이 실행된 통계
----------------------------------------------------------
468 recursive calls
0 db block gets
82 consistent gets
4 physical reads
0 redo size
771 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
- recursive calls : 유저 레벨과 시스템 레벨에서 생성되는 recursive call의 수
- db block gets : CURRENT 블록이 요청된 횟수
- consistent gets : 블록에 대해 일관성 읽기가 요청된 횟수
- physical reads : 디스크에서 읽은 총 데이터 블록 수
- redo size : 총 리두 양(바이트)
- bytes sent via SQL*Net to client : 포그라운드 프로세스에서 클라이언트로 보낸 총 바이트 수
- bytes received via SQL*Net from client : Oracle Net을 통해 클라이언트에서 받은 총 바이트
- SQL*Net roundtrips to/from client : 클라이언트와 주고 받은 총 Oracle Net 메시지 수
- sorts (memory) : 메모리에서 완전히 수행되어 디스크 쓰기가 필요하지 않은 정렬 작업 수
- sorts (disk) : 최소한 하나의 디스크 쓰기가 필요한 정렬 작업 수
- rows processed : 작업 중에 처리된 행 수
SYS@orcl2> set autotrace off
SCOTT@orcl2> set autot
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SYS@orcl2> set autot on exp
-- 통계 정보를 제외한 explain만 보고싶을 경우
SCOTT@orcl2> select * from emp where empno = 7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
SCOTT@orcl2> set autot off
SCOTT@orcl2> set autot traceonly
-- 실행 결과를 보여주지 않는다.
SCOTT@orcl2> select * from emp where empno = 7788;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
771 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- v$sql_plan(실행한 결과까지 확인)
- Library Cache(Memory)의 plan(예측통계 : 파싱할때 객체통계와 시스템 통계를 가지고 옵티마이져가 예측한것)
- v$sql_plan_statistics_all( v$sql_plan_statistics, v$sql_workarea) - 실행통계 : 실제 실행됬을때의 통계
- dbms_xplan.display_cursor
- dbms_xplan.display_cursor( '{SQL_ID}', {child number} , '{option}' )
USER01@orcl2> select * from emp where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
USER01@orcl2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 557p4j1ggw222, child number 0
-------------------------------------
select * from emp where deptno=10
Plan hash value: 1672835495
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO_IX | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
19 rows selected.
USER01@orcl2> select employee_id, salary
from employees
where department_id = 60;
EMPLOYEE_ID SALARY
----------- ----------
103 9000
104 6000
105 4800
106 4800
107 4200
USER01@orcl2> select *
from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0q2bvjpdxft15, child number 0
-------------------------------------
select employee_id, salary
from employees
where department_id = 60
Plan hash value: 3013043712
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 110 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=60)
USER01@orcl2> select *
from table(dbms_xplan.display_cursor('0q2bvjpdxft15', null, null));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0q2bvjpdxft15, child number 0
-------------------------------------
select employee_id, salary from employees where
department_id = 60
Plan hash value: 3013043712
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 110 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=60)
USER01@orcl2> select /* LEE */ * from employees
where department_id = 10;
USER01@orcl2> select sql_id, sql_text
from v$sql
where sql_text like '%LEE%';
SQL_ID
-------------
SQL_TEXT
-------------------------------------------------------------------
712jg8qzprwd2
select /* LEE */ * from employees where department_id = 10
USER01@orcl2> select *
from table(dbms_xplan.display_cursor('712jg8qzprwd2'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 712jg8qzprwd2, child number 0
-------------------------------------
select /* LEE */ * from employees where department_id = 10
Plan hash value: 3013043712
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 690 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=10)
SCOTT@orcl2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
SYS@orcl2> grant select on v_$session to scott;
Grant succeeded.
SYS@orcl2> grant select on v_$sql_plan to scott;
Grant succeeded.
SYS@orcl2> grant select on v_$sql to scott;
Grant succeeded.
SYS@orcl2> grant select on v_$sql_plan_statistics_all to scott;
Grant succeeded.
SYS@orcl2> grant select on v_$sql_plan_statistics to scott;
Grant succeeded.
SCOTT@orcl2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID dyk4dprp70d74, child number 0
-------------------------------------
SELECT DECODE('A','A','1','2') FROM DUAL
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
SCOTT@orcl2> select * from table(dbms_xplan.display_cursor(null, null, 'all'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID dt0hqkzcdq5ny, child number 1
-------------------------------------
select * from table(dbms_xplan.display_cursor)
Plan hash value: 3713220770
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 38 | 76 | 29 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4A78348A / KOKBF$@SEL$E112F6F0
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - VALUE(A0)[300]
Note
-----
- cardinality feedback used for this statement
SCOTT@orcl2> select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0d56ryuj0hacw, child number 0
-------------------------------------
select * from dept where deptno = 10
Plan hash value: 2852011669
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
2 - SEL$1 / DEPT@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]
2 - "DEPT".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
SCOTT@orcl2> select * from dept where deptno = 10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@orcl2> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0d56ryuj0hacw, child number 0
-------------------------------------
select * from dept where deptno = 10
Plan hash value: 2852011669
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Note
-----
- 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
-- 해당 오류는 SYS에서 parameter을 수정하여야한다.
SYS@orcl2> show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SYS@orcl2> alter system set statistics_level='ALL';
System altered.
SYS@orcl2> show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string ALL
SCOTT@orcl2> select * from dept where deptno = 10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@orcl2> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0d56ryuj0hacw, child number 1
-------------------------------------
select * from dept where deptno = 10
Plan hash value: 2852011669
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
-- Id : 각 Operation ID (* 가 있는 경우 Predicate Information에 access, filter에 관한 정보)
-- Operation: 실행되는 job
-- Name : Operation이 액세스 하는 테이블, 인덱스
-- Starts : 각 Operation을 반복 수행한 건수
-- E-Rows : 각 Operation 이 끝났을 때 return되는 건수 (예상치 임)
-- A-Rows : 각 Operation 이 끝났을 때 return되는 건수 (실제)
-- A-Time : 실제 실행시간, child operation의 합친 누적치
-- Buffers : 각 Operation의 logical block의수
-- Reads : 각 Operation의 physical block의수
-- Write : 각 Operation의 disk에 write한 block의수
USER01@orcl2> !pwd
/home/oracle/sqlt
USER01@orcl2> select * from employees, departments;
2889 rows selected.
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c14kuppyuqs1p, child number 0
-------------------------------------
select * from employees, departments
Plan hash value: 1162840532
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2889 |00:00:00.02 | 33 | 11 | | | |
| 1 | MERGE JOIN CARTESIAN| | 1 | 2889 | 2889 |00:00:00.02 | 33 | 11 | | | |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 29 | 6 | | | |
| 3 | BUFFER SORT | | 27 | 107 | 2889 |00:00:00.01 | 4 | 5 | 11264 | 11264 |10240 (0)|
| 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 4 | 5 | | | |
----------------------------------------------------------------------------------------------------------------------------------
- SQL TRACE
USER01@orcl2> select * from v$diag_info;
INST_ID NAME VALUE
---------- ------------------------- ------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/orcl2/orcl2
1 Diag Trace /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_8634.trc
1 Active Problem Count 0
1 Active Incident Count 0
USER01@orcl2> alter session set sql_trace = true;
Session altered.
USER01@orcl2> select * from employees where employee_id = 100;
.......
USER01@orcl2> alter session set sql_trace=false;
Session altered.
USER01@orcl2> !ls /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_8634.trc
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_8634.trc
USER01@orcl2> ! tkprof /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_8634.trc sql_trc.out sys=no explain=user01/oracle
TKPROF: Release 11.2.0.1.0 - Development on Thu May 2 14:20:19 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
-- formating을 해서 현재 경로에 sql_trc.out로 생성한다.
-- sys=no : recursive calls가 안보이게 한다.
USER01@orcl2> ed sql_trc.out
TKPROF: Release 11.2.0.1.0 - Development on Thu May 2 14:20:19 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_8634.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 1hgzr5xxpmt7h
Plan Hash: 0
alter session set sql_trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 25 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 25 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 (USER01)
********************************************************************************
...............
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 25 0 0
Fetch 2 0.00 0.00 1 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 1 27 0 1
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_8634.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
USER01.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
49 lines in trace file.
15 elapsed seconds in trace file.
- AWR
- dbmx_xplan.display_awr
'DATABASE(oracleDB 11g) > Tuning' 카테고리의 다른 글
| [SQL Tuning]인덱스(Index)의 기초 (0) | 2019.05.20 |
|---|---|
| [SQL Tuning]인덱스를 사용하지 못하는 사례 6가지와 해결책 (0) | 2019.05.03 |
| [SQL Tuning]옵티마이저 연산자 (0) | 2019.05.03 |
| [SQL Tuning]튜닝의 기초와 옵티마이져 (0) | 2019.05.03 |
| [SQL Tuning] Hinst란 (0) | 2019.02.12 |