2024/02/13
■ explain plan
- optimizer가 sql문 실행에 사용하는 실행계획을 사용
- plan_table 저장
- 실행 sql문 실행하지 않는다.
- plan_table 생성 스크립트
- SQL> $ORACLE_HOME/rdbms/admin./utlxplan.sql
- 10g 부터는 db 설치시 기본적으로 sys.plan_table$ 테이블이 만들어진다.
SYS@ora11g> select *
from all_synonyms
where synonym_name = 'PLAN_TABLE'; 2 3
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC PLAN_TABLE SYS PLAN_TABLE$
▶ 실행계획 만들기 (sql developer에서 F10 key)
- 실행계획 만들기
SYS@ora11g> explain plan for
select *
from hr.employees
where employee_id=100; 2 3 4
Explained.- 생성된 실행계획 보기 basice
SYS@ora11g> select * from table(dbms_xplan.display(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1833546154
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK |
-----------------------------------------------------
9 rows selected.- 생성된 실행계획 보기 typical
SYS@ora11g> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
14 rows selected.
✏️ 실행계획 단어들
- Id : 각 operation 번호, id에 * 있는경우 predicate information 에 access, filter 에 관한 정보 (순서:역순)
- access
: 특정값을 알아서 접근할때 ex)rowid scan- filter
: 값을 몰라서 전체적으로 찾을때- Operation : 실행되는 job
- Name : Operation이 엑세스하는 테이블, 인덱스
- ROW : 각 Operation이 끝났을때 return 되는 행수(예상치)
- Byte : Operation이 수행했을때 byte 값 (예상치)
- Cost (%CPU) : 각 Operation의 cost, 누적치
- Time : 각 Operation의 수행시간, 누적치 (예상치)
access predicate : 실제 block을 읽기전에 어떤 방법으로 block 읽을것인가를 결정 (찾고자 하는 행이 있는 블록의 위치를 알고있을때)
filter predicate : 실제 block을 읽은 후에 데이터를 걸러내기 위해 사용된다. (찾고자 하는 행이 어느 블록에 있는지 위치를 모를때)
- 실행계획 생성 set statement_id = 'demo1'
SYS@ora11g> explain plan set statement_id = 'demo1' for
select *
from hr.employees
where employee_id=100; 2 3 4
Explained.- 실행계획조회
SYS@ora11g> select * from table(dbms_xplan.display('PLAN_TABLE','demo1','typical'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
14 rows selected.
- 실행계획 조회 all
Query Block Name
: 문장을 변형시키는작업을 할때 어떻게 변형되었는지
SYS@ora11g> select * from table(dbms_xplan.display('PLAN_TABLE',null,'all'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-- 문장을 변형시키는작업을 할때 어떻게 변형되었는지
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
"EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
"EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20], "EMPLOYEES"."HIRE_DATE"[DATE,7],
"EMPLOYEES"."JOB_ID"[VARCHAR2,10], "EMPLOYEES"."SALARY"[NUMBER,22],
"EMPLOYEES"."COMMISSION_PCT"[NUMBER,22], "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
"EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]
31 rows selected.- 실행계획 조회 outline
SYS@ora11g> select * from table(dbms_xplan.display('PLAN_TABLE','demo1','outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Outline Data
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
28 rows selected.
- 실행계획 조회 advanced
- 조회 필요없는것은
-정보붙혀준다.SYS@ora11g> select * from table(dbms_xplan.display('PLAN_TABLE','demo1','advanced -Projection -Outline'));
SYS@ora11g> select * from table(dbms_xplan.display('PLAN_TABLE','demo1','advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
"EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
"EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20], "EMPLOYEES"."HIRE_DATE"[DATE,7],
"EMPLOYEES"."JOB_ID"[VARCHAR2,10], "EMPLOYEES"."SALARY"[NUMBER,22],
"EMPLOYEES"."COMMISSION_PCT"[NUMBER,22], "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
"EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]
45 rows selected.
■ autotrace
- sql*plus, sql developer 기능
- plan_table, plustrace role 필요 (통계정보를 access)
- sql문 실행후 실행계획 및 실행 결과, 실행 통계정보를 출력
- plustrace role 생성
: 실행결과, 실행통계정보
SYS@ora11g> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SYS@ora11g>
SYS@ora11g> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SYS@ora11g> create role plustrace;
Role created.
SYS@ora11g>
SYS@ora11g> grant select on v_$sesstat to plustrace;
Grant succeeded.
SYS@ora11g> grant select on v_$statname to plustrace;
Grant succeeded.
SYS@ora11g> grant select on v_$mystat to plustrace;
Grant succeeded.
SYS@ora11g> grant plustrace to dba with admin option;
Grant succeeded.
SYS@ora11g>
SYS@ora11g> set echo off- hr유저에 plustrace 권한 부여
SYS@ora11g> grant plustrace to hr;
Grant succeeded.
HR@ora11g> select * from session_roles;
ROLE
------------------------------
RESOURCE
PLUSTRACE
▶ 1. set autotrace on
: 실행결과, 실행계획, 통계정보
HR@ora11g> set autotrace on
-- 안보기 set autotrace off
HR@ora11g> select * from hr.employees where employee_id =100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 2000 90
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
134 recursive calls
0 db block gets
250 consistent gets
1 physical reads
0 redo size
1022 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
Statistics
- recursive calls
: 유저 레벨과 시스템레벨에서 생성되는 재귀호출의 수를 나타낸다. 오라클이 내부적으로 사용하고 있는 sql문- db block gets
: dml 작업시에 체크, current한 블록이 요청된 횟수를 나타낸다.
메모리에서 access한 블록의 수- consistent gets
: select 작업시에 체크, 블록에 대해 일관성 읽기가 요청된 횟수를 나타낸다.
메모리에서 access한 블록의 수- physical reads
: 디스크에서 읽은 데이터 블록의 수- redo size
: dml 작업시 사용한 리두 사이즈(byte)- bytes sent via SQL*Net to client
: 클라이언트로 보낸 총 바이트 값- bytes received via SQL*Net from client
: 클라이언트로 받은 총 바이트 값- SQL*Net roundtrips to/from client
: 클라이언트와 주고 받은 oracle net 메시지 수- sorts (memory)
: 메모리에서 수행되어 디스크 쓰기가 필요하지 않은 정렬작업의 수- sorts (disk)
: 최소한 하나의 디스크 쓰기가 필요한 정렬 작업의 수- rows processed
: 처리된 행의 수
- select 작업할때 확인
- consistent gets, physical reads
- dml 작업할때 확인
- db block gets, physical reads
▶ 2. set autotrace on explain
: 실행 결과, 실행계획
HR@ora11g> set autotrace on explain
HR@ora11g> select * from hr.employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 2000 90
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
▶ 3. set autotrace on statistics
: 실행 결과, 통계정보
HR@ora11g> set autotrace on statistics
HR@ora11g> select * from hr.employees where employee_id = 200;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 9431.79 101 10
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1033 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
▶ 4. set autotrace traceonly
: 실행 계획, 통계정보
HR@ora11g> set autotrace traceonly
HR@ora11g> select * from hr.employees where employee_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1022 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▶ 5. set autotrace traceonly exp
: 실행 계획
-> sql문 실제수행하지 않고 실행계획만 보여준다.
HR@ora11g> set autotrace traceonly exp
HR@ora11g> select * from hr.employees where employee_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
▶ 5. set autotrace traceonly statistics
: 통계정보
-> 실제 sql문 수행한다.
HR@ora11g> set autotrace traceonly statistics
HR@ora11g> select * from hr.employees where employee_id = 100;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1022 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에 있는 실행계획을 표시
📍 실제 실행한 실행계획!
- sql_id, child_number 조회
SYS@ora11g> select s.prev_sql_id, s.prev_child_number, v.sql_text
from v$session s, v$sql v
where s.prev_sql_id = v.sql_id
and s.prev_child_number = v.child_number
and s.username = 'HR'; 2 3 4 5
PREV_SQL_ID PREV_CHILD_NUMBER
------------- -----------------
SQL_TEXT
--------------------------------------------------------------------------------
2sgjc8u8ha0m4 0
select * from hr.employees where employee_id = 100
- 실행계획 조회
select * from table(dbms_xplan.display(sql_id, child_number, 보여주는형식));
HR@ora11g> select * from table(dbms_xplan.display('2sgjc8u8ha0m4','0','typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ERROR: cannot get definition for table '2SGJC8U8HA0M4'
ORA-44004: invalid qualified SQL name
Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
97 consistent gets
0 physical reads
0 redo size
521 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
바로 직전 sql문 실행계획 보기
select * from table(dbms_xplan.display_cursor);
- sql문 실행후 바로 직전 sql문 실행계획보기
SYS@ora11g> select * from hr.employees where employee_id =100;
SYS@ora11g> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
8 rows selected.👉 일반 유저들이 dbms_xplan.display_cursor 를 사용하려면 권한이 필요하다.
grant select on v_$session to hr; grant select on v_$sql to hr; grant select on v_$sql_plan to hr; grant select on v_$sql_plan_statistics to hr; grant select on v_$sql_plan_statistics_all to hr;
- 권한을 받고 hr 유저에서 확인
HR@ora11g> select * from hr.employees where employee_id =100;
HR@ora11g> select * from table(dbms_xplan.display_cursor);
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 | 16 | 32 | 29 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Note
-----
- cardinality feedback used for this statement
17 rows selected.▶ 실행계획 조회 allstats last
HR@ora11g> select * from hr.employees where employee_id =100;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID 8mndh8pwb2skt, child number 0
-------------------------------------
select * from hr.employees where employee_id =100
Plan hash value: 1833546154
--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
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
- hint 를 이용하여 select 후 실행계획 확인
'gather_plan_statistics' is used for the statement or
select /*+ gather_plan_statistics */ * from hr.employees where employee_id =100;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
HR@ora11g> select /*+ gather_plan_statistics */ * from hr.employees;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 2t2kgkwc3z8q5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from hr.employees
Plan hash value: 1445457117
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 14 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 14 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
13 rows selected.PLAN_TABLE_OUTPUT
- Id
: 각 Operation 번호- Operation
: 실행되는 job- Name
: Operation이 수행되는 테이블, 인덱스- Starts
: 각 Operation을 반복 수행한 건수- E-Rows
: 각 Operation을 수행했을때 return 건수(예상치)- A-Rows
: 각 Operation을 수행했을때 return 건수(실제)- A-Time
: 실제 시행시간 누적치- Buffers
: 각 Operation의 logical block의 수 누적치(전체 수행한 블록의 수)- Reads
: 각 Operation의 physical block의 수(디스크에서 읽은 블록의 수)- Write
: 각 Operation의 disk에 write 한 block의 수
👉 3번 이상 block에 access 하면 block pinning이 걸린다.
: block I/O를 줄이기위해서
HR@ora11g> select /*+ gather_plan_statistics */ * from hr.employees where department_id = 50;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID b66ycurnwpgud, child number 1
-------------------------------------
select * from table(dbms_xplan.display_cursor(null,null,'allstats
last'))
Plan hash value: 3713220770
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 2 |
---------------------------------------------------------------------
Note
-----
- cardinality feedback used for this statement
- 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
21 rows selected.
- INDEX hint를 이용하여 select
: 하지만 index스캔하는것보다 full scan하는것이 효율적이라고 생각하여 옵티마이저가 full scan으로 진행하였다.
HR@ora11g> select /*+ gather_plan_statistics index(e EMP_DEPARTMENT_IX) */ * from hr.employees where department_id = 50;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID c80cn0xjv3sfy, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(e EMP_DEPARTMENT_IX) */ * from
hr.employees where department_id = 50
Plan hash value: 1445457117
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 9 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=50)
19 rows selected.
buffer pinning이 처음부터 걸려있으면
index Buffers의 수 1번
table Buffers의 수 1번, 총 2번이다.
■ buffer pinning
: 버퍼를 읽고 나서 버퍼 pin 해제하지 않고 데이터베이스 call 이 진행하는 동안 유지하는 기능.
같은 블록(버퍼)를 반복적으로 읽어갈때 좋은 기능이다.
Latch 점유 시간을 줄일수 있고 블록 I/O 를 줄일수 있는 기능이다.
index range scan을 수행할때 leaf 블록을 다시 access하는 I/O를 줄일 수 있고
인덱스에서 찾은 rowid를 이용해서 실제 블록을 access 할때 같은 block을 다시 access하면 I/O를 줄일수 있다.
■ sql trace
- sql문 수행에 대해서 trace file을 생성
- instance level, session level 수행할 수 있다.
- 구문분석, 실행, fetch 단계에서 어느 부분에서 시간이 오래 걸리는지 시간 통계 정보를 표시
- dump 위치 확인
SYS@ora11g> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/ora
11g/ora11g/trace- tracefile_identifie 변경
SYS@ora11g> alter session set tracefile_identifier = 'test';
Session altered.- sql_trace 활성화
SYS@ora11g> alter session set sql_trace = true;
Session altered.
SYS@ora11g> select * from hr.employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
100 Steven King
SKING 515.123.4567 17-JUN-03 AD_PRES 2000
90
- sql_trace 비활성화
SYS@ora11g> alter session set sql_trace = false;
Session altered.- trace 파일 물리적위치로 이동
[oracle@oracle trace]$ pwd
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *test.trc
ora11g_ora_13265_test.trc
[oracle@oracle trace]$ cat ora11g_ora_13265_test- 변환작업
[oracle@oracle trace]$ tkprof ora11g_ora_14477_test.trc ex01.txt sys=no
[oracle@oracle trace]$ cat ex01.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 17:56:40 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_13265_test.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
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 2 0 3
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 13 0.00 0.00 0 0 0 0
Execute 370 0.01 0.01 0 0 0 0
Fetch 425 0.00 0.00 0 1177 0 3280
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 808 0.01 0.01 0 1177 0 3280
Misses in library cache during parse: 13
Misses in library cache during execute: 13
4 user SQL statements in session.
15 internal SQL statements in session.
19 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_13265_test.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
15 internal SQL statements in trace file.
19 SQL statements in trace file.
19 unique SQL statements in trace file.
1339 lines in trace file.
26 elapsed seconds in trace file.
- hr 세션에서 trace 뜬후 통계정보 확인
- hr 세션에서 sql_trace 뜨기
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set sql_trace = true;
Session altered.
HR@ora11g> select * from hr.employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
100 Steven King
SKING 515.123.4567 17-JUN-03 AD_PRES 2000
90
HR@ora11g> alter session set sql_trace = false;
Session altered.▶ trace 뜬것을 변환작업후 통계정보 확인
- SELECT 문
: Fetch 라인의 disk,query 을 보면된다.- DML문
: Execute 라인의 disk, current 을 보면된다.
👉 물론 select문이나 dml문 둘다 블록의 I/O는 execute시에 발생한다.call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 2 0 1
call
- Parse
: sql문을 파싱하고 실행계획을 생성하는데 대한 통계 정보- Execute
: DML 문에 대한 통계정보- Fetch
: SELECT문에 대한 통계정보
- count
: 실행횟수- cpu
: 처리에 소요되는 시간(초)- elapsed
: 처리에 소요되는 총 경과시간(초)- disk
: 물리적 블록 읽은 수- query
: 일관성있는 (consistent) 읽기를 위한 논리적 버퍼 읽기수 (select 작업시 본다.)- current
: 현재 모드에서 논리적 버퍼 읽기수 (dml 작업시 본다.)- rows
: 처리한 행의수
- cr
: consistent 블록읽기 수- pr
: 물리적 블록 읽은 수- pw
: 물리적 블록 쓰기 수- time
: 수행단계에서 소요되는 시간
[oracle@oracle trace]$ pwd
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_31854_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_31854_hr.trc ex03.txt sys=no
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 18:04:47 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat ex03.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 18:04:47 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_31854_hr.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: 2sgjc8u8ha0m4 Plan Hash: 1833546154
select *
from
hr.employees where employee_id = 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=2 pr=0 pw=0 time=20 us cost=1 size=69 card=1)
1 1 1 INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=13 us cost=0 size=0 card=1)(object id 87910)
********************************************************************************
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 0 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
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.
********************************************************************************
Trace file: ora11g_ora_31854_hr.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.
49 lines in trace file.
8 elapsed seconds in trace file.
- 확ㅇ
HR@ora11g> alter session set tracefile_identifier = 'hr1';
Session altered.
HR@ora11g> alter session set sql_trace = true;
Session altered.
HR@ora11g> select * from hr.employees where employee_id = 20;
no rows selected
HR@ora11g> alter session set sql_trace = false;
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr1.trc
ora11g_ora_31854_hr1.trc
[oracle@oracle trace]$ tkprof ora11g_ora_31854_hr1.trc ex04.txt sys=no
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 18:25:01 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat ex04.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 18:25:01 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_31854_hr1.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: 70w19wnmtnswr Plan Hash: 1833546154
select *
from
hr.employees where employee_id = 20
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=1 pr=0 pw=0 time=4 us cost=1 size=69 card=1)
0 0 0 INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 87910)
********************************************************************************
********************************************************************************
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 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 1 0 0
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 9 0.00 0.00 0 0 0 0
Execute 23 0.00 0.00 0 0 0 0
Fetch 78 0.00 0.00 0 119 0 56
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 110 0.00 0.00 0 119 0 56
Misses in library cache during parse: 1
Misses in library cache during execute: 1
3 user SQL statements in session.
9 internal SQL statements in session.
12 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_31854_hr1.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
9 internal SQL statements in trace file.
12 SQL statements in trace file.
12 unique SQL statements in trace file.
246 lines in trace file.
21 elapsed seconds in trace file.
▶ event를 이용하여 trace 뜨기
- trace 뜨기
고정값이다.
alter session set events '10046 trace name context forever, level 8';
HR@ora11g> alter session set tracefile_identifier = 'hr3';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select * from hr.employees where employee_id = 20;
no rows selected
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.- 뜬 trace 변환작업
HR@ora11g> !
[oracle@oracle ~]$ cd
[oracle@oracle ~]$ cd /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr3.trc
ora11g_ora_31854_hr3.trc
[oracle@oracle trace]$ tkprof ora11g_ora_31854_hr3.trc ex05.txt sys=no[oracle@oracle trace]$ cat ex05.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 19:06:57 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_31854_hr3.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: 70w19wnmtnswr Plan Hash: 1833546154
select *
from
hr.employees where employee_id = 20
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 189 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 190 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=1 pr=0 pw=0 time=4 us cost=1 size=69 card=1)
0 0 0 INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 87910)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 3.51 3.51
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 189 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 190 0 0
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 3.51 6.46
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 23 0.00 0.00 0 0 0 0
Fetch 23 0.00 0.00 0 70 0 134
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 47 0.00 0.00 0 70 0 134
Misses in library cache during parse: 0
2 user SQL statements in session.
4 internal SQL statements in session.
6 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_31854_hr3.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
4 internal SQL statements in trace file.
6 SQL statements in trace file.
6 unique SQL statements in trace file.
260 lines in trace file.
3 elapsed seconds in trace file.
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 3.51 6.46
SQL*Net message to client
: client에게 메시지를 보냈는데 client가 바쁘거나 네트워크 부하 때문에 메시지를 잘받았다는 신호가 늦게 도착하는 경우SQL*Net message from client
: 오라클 서버프로세스가 사용자에게 결과를 전송하고 다음 fetch call이 올떄까지 대기한 시간을 더한 값, 2번 발생하는 동안 6.46초를 대기
HR@ora11g> create table hr.ind_random
as select * from all_objects order by dbms_random.value; 2
Table created.
HR@ora11g> select blocks from user_segments where segment_name = 'IND_RANDOM';
BLOCKS
----------
1024
HR@ora11g> select count(*) from hr.ind_random;
COUNT(*)
----------
68182HR@ora11g> alter session set tracefile_identifier = 'hr5';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select count(*) from hr.ind_random where owner = 'SYS';
COUNT(*)
----------
29856
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
- 변환후 확인
[oracle@oracle ~]$ cd /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr5.trc
ora11g_ora_4669_hr5.trc
[oracle@oracle trace]$ tkprof ora11g_ora_4669_hr5.trc ex06.txt sys=no
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 19:25:03 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle trace]$ cat ex06.txt
TKPROF: Release 11.2.0.4.0 - Development on Mon Jan 29 19:25:03 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ora11g_ora_4669_hr5.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: 6xpydnkm336jb Plan Hash: 3059062694
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("IND_RANDOM") FULL("IND_RANDOM")
NO_PARALLEL_INDEX("IND_RANDOM") */ :"SYS_B_2" AS C1, CASE WHEN
"IND_RANDOM"."OWNER"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2
FROM "HR"."IND_RANDOM" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED
(:"SYS_B_8") "IND_RANDOM") SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 68 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 68 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=68 pr=0 pw=0 time=525 us)
4365 4365 4365 TABLE ACCESS SAMPLE IND_RANDOM (cr=68 pr=0 pw=0 time=405 us cost=19 size=149234 card=5146)
********************************************************************************
SQL ID: 49g6ju0tm1s80 Plan Hash: 3056395443
select count(*)
from
hr.ind_random where owner = 'SYS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 71 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 985 988 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 985 1059 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=988 pr=985 pw=0 time=3455 us)
29856 29856 29856 TABLE ACCESS FULL IND_RANDOM (cr=988 pr=985 pw=0 time=5797 us cost=276 size=511054 card=30062)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
direct path read 22 0.00 0.00
SQL*Net message from client 2 4.03 4.03
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 71 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 985 988 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 985 1059 0 1
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 4.03 6.86
direct path read 22 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 70 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 70 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
3 user SQL statements in session.
1 internal SQL statements in session.
4 SQL statements in session.
********************************************************************************
Trace file: ora11g_ora_4669_hr5.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
1 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
91 lines in trace file.
4 elapsed seconds in trace file.
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
direct path read 22 0.00 0.00
SQL*Net message from client 2 4.03 4.03
********************************************************************************
positive