| # 요약 | |
| ■ Explain Plan select * from all_synonyms where synonym_name = 'PLAN_TABLE'; explain plan for select * from hr.employees where employee_id = 100; select * from sys.plan_table$; select * from table(dbms_xplan.display(null,null,'basic')); select * from table(dbms_xplan.display(null,null,'typical')); explain plan SET statement_id = 'demo1' for select * from hr.employees where employee_id = 100; select * from table(dbms_xplan.display('PLAN_TABLE','demo1','typical')); select * from table(dbms_xplan.display('PLAN_TABLE','demo1','all')); select * from table(dbms_xplan.display('PLAN_TABLE','demo1','outline')); select * from table(dbms_xplan.display('PLAN_TABLE','demo1','advanced')); select * from table(dbms_xplan.display('PLAN_TABLE','demo1','advanced -Projection -Outline')); ■ AutoTrace set autotrace on set autotrace off set autotrace on explain set autotrace on statistics set autotrace traceonly set autotrace traceonly explain set autotrace traceonly statistics ■ v$sql_plan 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; select * from table(dbms_xplan.display_cursor); select * from table(dbms_xplan.display_cursor(null,null,'all')); select * from table(dbms_xplan.display_cursor(null,null,'advanced')); select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); /*+ gather_plan_statistics */ /*+ gather_plan_statistics index(e EMP_DEPARTMENT_IX) */ ■ Buffer Pinning ■ SQL TRACE show parameter user_dump_dest alter session set tracefile_identifier = 'test'; alter session set sql_trace = true; alter session set sql_trace = false; tkprof ora11g_ora_18923_hr_test.trc hr01.ext sys=no |
|
■ Explain Plan
- optimizer가 SQL문 실행에 사용하는 실행계획을 생성
- plan_table에 저장
- 실행 SQL문을 실행하지 않는다.
- plan_table 생성 스크립트(~9i)
SQL> $ORACLE_HOME/rdbms/admin/utlxplan.sql
- 10g부터는 DB 설치 시 기본적으로 sys.plan_table$ 테이블이 만들어진다.
| SYS@ora11g> select * from all_synonyms where synonym_name = 'PLAN_TABLE'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ---------- ------------------------------ ------------------------------ -------------------- --------------- PUBLIC PLAN_TABLE SYS PLAN_TABLE$ SYS@ora11g> explain plan for select * from hr.employees where employee_id = 100; Explained. SYS@ora11g> select * from sys.plan_table$; ... 길다 ↓ 보기 편하게 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 | ----------------------------------------------------- |
|
| 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): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=100) - Id : 각 Operation 번호, ID * 있는 경우 Predicate Information에 access, filter에 관한 정보 - Operation : 실행되는 job. 안쪽부터 해석 - Name : Operation이 엑세스하는 테이블, 인덱스 - Rows : 각 Operation이 끝났을 때 return되는 행의 수(예상치) - Bytes : Operation이 수행했을 때 byte 값 (예상치) - Cost (%CPU) : 각 Operation의 cost, 누적치 - Time : 각 Operation의 수행시간, 누적치(예상치) - access predicate : 실제 block을 읽기 전에 어떤 방법으로 block을 읽을것인가를 결정 (찾고자하는 행이 있는 블록의 위치를 알고 있을 때) - filter predicate : 실제 block을 읽은 후에 데이터를 걸러 내기 위해 사용된다. (찾고자하는 행이 어느 블록에 있는지 위치를 모를 때) |
|
| SYS@ora11g> explain plan SET statement_id = 'demo1' for select * from hr.employees where employee_id = 100; 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): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=100) |
|
| SYS@ora11g> select * from table(dbms_xplan.display('PLAN_TABLE','demo1','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): ------------------------------------------------------------- 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): ----------------------------------------------------------- 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] |
|
| 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 -- 만들기 위해 사용한 힌트 ------------- /*+ 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 */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=100) |
|
| 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): ------------------------------------------------------------- 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")) 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): --------------------------------------------------- 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] 2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22] |
|
| SYS@ora11g> select * from table(dbms_xplan.display('PLAN_TABLE','demo1','advanced -Projection -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 | --------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMPLOYEES@SEL$1 2 - SEL$1 / EMPLOYEES@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=100) |
|
■ AutoTrace
- sql*plus, sql developer 기능
- plan_table, plustrace role 필요 (통계정보를 access)
- SQL문 실행 후 실행계획 및 실행 결과, 실행 통계 정보를 출력
| # 설정 | |
| SYS@ora11g> @ $ORACLE_HOME/sqlplus/admin/plustrce.sql SYS@ora11g> drop role plustrace; Role dropped. SYS@ora11g> create role plustrace; Role created. 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> set echo off |
|
| # 유저에 권한부여 | |
| HR> HR@ora11g> set autot on SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report > 권한이 없다. SYS> SYS@ora11g> grant plustrace to hr; Grant succeeded. HR> 재접속 HR@ora11g> select * from session_roles; ROLE ------------------------------ RESOURCE PLUSTRACE |
|
1. set autotrace on
SQL문을 실제 수행하고 결과와 함께 실행계획, 실행통계 정보 출력
| HR@ora11g> set autotrace on | |
| 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 ---------------------------------------------------------- 147 recursive calls 0 db block gets 235 consistent gets 0 physical reads 0 redo size 1022 bytes sent via SQL*Net to client 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 12 sorts (memory) 0 sorts (disk) 1 rows processed |
|
| HR@ora11g> update hr.employees set salary = salary * 1.1 where employee_id = 100; 1 row updated. Execution Plan ---------------------------------------------------------- Plan hash value: 751015319 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | UPDATE STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 | | 1 | UPDATE | EMPLOYEES | | | | | |* 2 | INDEX UNIQUE SCAN| EMP_EMP_ID_PK | 1 | 8 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=100) Statistics ---------------------------------------------------------- 139 recursive calls 1 db block gets 125 consistent gets 7 physical reads 316 redo size 675 bytes sent via SQL*Net to client 644 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed |
|
| 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 2200 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 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 1022 bytes sent via SQL*Net to client 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
|
| recursive calls : 유저 레벨과 시스템 레벨에서 생성되는 재귀호출의 수를 나타낸다. 실행문을 위해 내부적으로 수행한 작업(오퍼레이션) 수 db block gets : dml 작업 시에 체크, current 블록이 요청된 수 메모리에서 access한 블록의 수 consistents gets : select 작업시에 체크, 블록에 대해 일관성 읽기가 요청된 블럭 수 메모리에서 access한 블록의 수 physical reads : 디스크에서 읽은 데이터 블록 수 select 작업시에 체크, dml 작업 시에 체크, redo size : dml 작업 시 사용한 리두 사이트(바이트) 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 : 처리된 행의 수 HR@ora11g> rollback; Rollback complete. |
|
2. set autotrace on explain
SQL문을 실제 수행하고 결과와 함께 실행계획 출력
| 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 2003-06-17 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
SQL문을 실제 수행하고 결과와 함께 실행통계 출력
| HR@ora11g> set autotrace on statistics | |
| 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 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 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
|
| 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 4400 101 10 Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1031 bytes sent via SQL*Net to client 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
|
| 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 4400 101 10 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 1031 bytes sent via SQL*Net to client 408 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
SQL문을 실제 수행하고 결과는 출력하지 않고 실행계획, 실행통계 출력
| 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 408 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 explain
SQL문을 실제 수행하지 않고 실행계획 출력 = explain 동일
| HR@ora11g> set autotrace traceonly explain | |
| 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) |
|
6. 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 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
|
# set autotrace off : 종료
HR@ora11g> set autotrace off
■ v$sql_plan
- library cache에 있는 실행계획을 표시
| SYS@ora11g> select * from hr.employees where employee_id = 110; > 1건 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'; PREV_SQL_ID PREV_CHILD_NUMBER SQL_TEXT ------------- ----------------- -------------------------------------------------- 2sgjc8u8ha0m4 0 select * from hr.employees where employee_id = 100 SYS@ora11g> select * from table(dbms_xplan.display_cursor('2sgjc8u8ha0m4',0,'typical')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 2sgjc8u8ha0m4, child number 0 ------------------------------------- select * from hr.employees where employee_id = 100 Plan hash value: 1833546154 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 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)| | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=100) SYS@ora11g> select * from v$sql_plan where sql_id = '2sgjc8u8ha0m4'; ... 길다 ↓ SQL_ID 찾기 번거롭다. SYS@ora11g> select * from hr.employees where employee_id = 110; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER ----------- -------------------- ------------------------- ------------------------- -------------------- HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID ---------- ---------- ---------- -------------- ---------- ------------- 110 John Chen JCHEN 515.124.4269 2005-09-28 FI_ACCOUNT 8200 108 100 # 직전에 실행한 SQL문에 대한 실행계획 출력 SYS@ora11g> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 0hwtf95twr2mb, child number 0 ------------------------------------- select * from hr.employees where employee_id = 110 Plan hash value: 1833546154 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 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)| | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=110) |
|
| HR@ora11g> select * from hr.employees where employee_id = 110; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER ----------- -------------------- ------------------------- ------------------------- -------------------- HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID --------- ---------- ---------- -------------- ---------- ------------- 110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100 HR@ora11g> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- User has no SELECT privilege on V$SESSION # 일반 유저들이 dbms_xplan.display_cursor 를 사용하려면 권한이 필요하다. SYS@ora11g> 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@ora11g> select * from hr.employees where employee_id = 110; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER ----------- -------------------- ------------------------- ------------------------- -------------------- HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID --------- ---------- ---------- -------------- ---------- ------------- 110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100 HR@ora11g> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 0hwtf95twr2mb, child number 0 ------------------------------------- select * from hr.employees where employee_id = 110 Plan hash value: 1833546154 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 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)| | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=110) |
|
| 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> select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 2sgjc8u8ha0m4, child number 0 ------------------------------------- select * from hr.employees where employee_id = 100 Plan hash value: 1833546154 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 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)| | --------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 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): ----------------------------------------------------------- 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] |
|
| 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> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 2sgjc8u8ha0m4, child number 0 ------------------------------------- select * from hr.employees where employee_id = 100 Plan hash value: 1833546154 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 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)| | --------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMPLOYEES@SEL$1 2 - SEL$1 / EMPLOYEES@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 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] 2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22] |
|
| 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> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 2sgjc8u8ha0m4, 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 > statistics_level 설정을 바꾸던가, gather_plan_statistics 힌트를 사용해라 |
|
| # gather_plan_statistics dbms_xplan을 실행하기 위한 힌트 |
|
| HR@ora11g> select /*+ gather_plan_statistics */ * 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> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 6qmqh498r56x4, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ * from hr.employees where employee_id = 100 Plan hash value: 1833546154 ------------------------------------------------------------------------------------------------------- | 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| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 2 | |* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=100) |
|
| 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 | ----------------------------------------------------------------------------------------- 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 수 |
|
| HR@ora11g> select /*+ gather_plan_statistics */ * from hr.employees where department_id = 20; > 2건 HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID c48y19gs7zk95, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ * from hr.employees where department_id = 20 Plan hash value: 2056577954 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPARTMENT_ID"=20) > 2건 데이터를 엑서스 하고자 인덱스 래인지 스캔 (departemt_id : 논유니크 인덱스 ) EMP_DEPARTMENT_IX 버퍼 2 : 루트-브런치-리프 순인데 데이터양이 작아 모두 한곳에 있다 buffer pinning 이 돌아가서 2까지 ㄴ 3번 block을 access하면 4번째에 block pinning 걸림 > latch 잡더라도 점유시간 짧아짐 : block 위치를 알기 때문 rowid로 2건 찾으면서 block i/o 2번 발생 |
|
| HR@ora11g> select /*+ gather_plan_statistics */ * from hr.employees where department_id = 50; > 45건 HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID gchkassr4n0kk, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ * 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 | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPARTMENT_ID"=50) |
|
| HR@ora11g> select /*+ gather_plan_statistics index(e EMP_DEPARTMENT_IX) */ * from hr.employees e where department_id = 50; > 45건 HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID g8gaabfw95tgn, child number 0 ------------------------------------- select /*+ gather_plan_statistics index(e EMP_DEPARTMENT_IX) */ * from hr.employees e where department_id = 50 Plan hash value: 2056577954 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 9 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 | |* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 | 45 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPARTMENT_ID"=50) 토탈 버퍼 9번. 풀스캔이나 인덱스스캔이나 동일 원래 인덱스 스캔이라면 45행이니 i/o 45+45+1번=91번 발생해야하는데 buffer pinning이 돌아가서 4번만 발생한거임 통계정보가 있기 때문 ------------------- 1번 리프에서 10번 찾음 rowid -> 2번 10번이 있는 블럭 액서스(랜덤io) 3번 리프에서 10번 또있나? rowid -> 4번 랜덤 io 5번 리프 -> 6번 랜덤io 7번 리프 10번 또있나? 없다 종료 3건 : 7번 io 발생 인덱스 오퍼레이션에서 4번, 테이블 오퍼레이션에서 3번 실행계획 버퍼 table 7 (누적) index 4 ------------------- INDEX RANGE SCAN = 1+1 스캔 > 계속 리프블럭을 봐야하니 execute level에서 핀 해제하지말자 = buffer pinning 리프 블럭안에 찾을 데이터가 많네? > buffer pinning을 먼저 찍고 시작 가능 1번 리프 io rowid 봐서 같은 블럭이네? -> 2번 테이블에도 버퍼피닝 찍고 -> 총 2개 -------------------- >> 경우의 수가 많으니 버퍼피닝이 돌아서 io 발생이 줄었구나 이해 > 인덱스 조인 시 효과적 |
|
■ 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 단계에서 어느 부분에서 시간이 오래 걸리는지 시간 통계 정보를 표시
| SYS@ora11g> show parameter user_dump_dest NAME TYPE VALUE ------------------------------------ ------- ------------------------------ user_dump_dest string /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace = alert log file 위치 동일 HR@ora11g> alter session set tracefile_identifier = 'hr_test'; Session altered. HR@ora11g> alter session set sql_trace = true; Session altered. HR@ora11g> select * from hr.employees where employee_id = 100; > 1건 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 *test.trc ora11g_ora_18923_hr_test.trc [oracle@oracle trace]$ tkprof ora11g_ora_18923_hr_test.trc hr01.ext 또는 [oracle@oracle trace]$ tkprof ora11g_ora_18923_hr_test.trc hr02.ext sys=no [oracle@oracle trace]$ cat hr01.ext ... 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=17 us cost=1 size=69 card=1) 1 1 1 INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=11 us cost=0 size=0 card=1)(object id 87910) ******************************************************************************** - SELECT : Fetch 라인에서 disk, query - DML : Execute 라인에서 dist, current - Parse : SQL문을 파싱하고 실행계획을 생성하는데 대한 통계 정보 - Execute : DML 문에 대한 통계정보 - Fetch : SELECT 문에 대한 통계정보 ㄴ 구분을 하고자 표기일 뿐 블럭 I/O는 execute에서 발생한다. - count : 실행횟수 - cpu : 처리에 소요되는 시간(초) - elapsed : 처리에 소요되는 총 경과시간(총) (대기포함) - disk : 물리적 블록 읽은 수 - query : 일관성있는 (consistent) 읽기를 위한 논리적 버퍼 읽기 수(SELECT 작업 시 체크) - current : 현재 모드에서 논리적 버퍼 읽기 수(DML 작업 시 체크) - rows : 처리한 행의 수 - cr : consistent 블록 읽기 수 - pr : 물리적 블록 읽은 수 - pw : 물리적 블록 쓰기 수 - time : 수행 단계에서 소요된 시간(ms, microsecond) ******************************************************************************** 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 ******************************************************************************** |
|
| 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 department_id = 20; > 2건 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_18923_hr1.trc [oracle@oracle trace]$ tkprof ora11g_ora_18923_hr1.trc hr01.txt [oracle@oracle trace]$ cat hr01.txt ... SQL ID: 1nq4du390k3q6 Plan Hash: 2056577954 select * from hr.employees where department_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 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 4 0 2 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 ---------- ---------- ---------- --------------------------------------------------- 2 2 2 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=4 pr=0 pw=0 time=16 us cost=2 size=138 card=2) 2 2 2 INDEX RANGE SCAN EMP_DEPARTMENT_IX (cr=2 pr=0 pw=0 time=26 us cost=1 size=0 card=2)(object id 87907) ******************************************************************************** |
|
| HR@ora11g> alter session set tracefile_identifier = 'hr50'; Session altered. HR@ora11g> alter session set sql_trace = true; Session altered. HR@ora11g> select * from hr.employees where department_id = 50; > 45건 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 *hr50.trc ora11g_ora_18923_hr50.trc [oracle@oracle trace]$ tkprof ora11g_ora_18923_hr50.trc hr50.txt [oracle@oracle trace]$ cat hr50.txt ... SQL ID: 0ac1ytgpdkya9 Plan Hash: 1445457117 select * from hr.employees where department_id = 50 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 4 0.00 0.00 0 9 0 45 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 9 0 45 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 ---------- ---------- ---------- --------------------------------------------------- 45 45 45 TABLE ACCESS FULL EMPLOYEES (cr=9 pr=0 pw=0 time=19 us cost=3 size=3105 card=45) |
|
| HR@ora11g> create table hr.ind_random as select * from all_objects order by dbms_random.value; 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(*) ---------- 68162 HR@ora11g> alter session set tracefile_identifier = 'hr55'; Session altered. HR@ora11g> alter session set events '10046 trace name context forever, level 8'; Session altered. > 8 level : 문장~ wait event 포함 HR@ora11g> select count(*) from hr.ind_random where owner = 'SYS'; COUNT(*) ---------- 29853 HR@ora11g> alter session set events '10046 trace name context off'; Session altered. HR@ora11g> ! [oracle@oracle ~]$ cd /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace [oracle@oracle trace]$ ls *hr55.trc ora11g_ora_27059_hr55.trc [oracle@oracle trace]$ tkprof ora11g_ora_27059_hr55.trc hr55.txt [oracle@oracle trace]$ cat hr55.txt ... 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 65 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 984 987 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 984 1052 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=987 pr=984 pw=0 time=4153 us) 29853 29853 29853 TABLE ACCESS FULL IND_RANDOM (cr=987 pr=984 pw=0 time=2994 us cost=275 size=505648 card=29744) 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 24 0.00 0.00 SQL*Net message from client 2 11.13 11.13 ******************************************************************************** - SQL*Net message to client : client에게 메시지를 보냈는데 client가 바쁘거나 네트워크 부하때문에 메시지를 잘 받았다는 신호가 늦게 도착하는 경우 - SQL*Net message from client : 오라클 서버프로세스가 사용자에게 결과를 전송하고 다음 fetch call이 올때까지 대기한 시간을 더한 값, 4번 발생하는 동안 44.73초를 대기 - direct path read : 데이터버퍼캐시를 안거치고 커서에서 읽기 (풀스캔이니 이게 더 효율) 주로 병렬처리할 때 나오는 wait event. 24번 올렸다 11g~ > cr 전체 io 수 987에서 메모리 pr 디스크에서 읽은 i/o 984를 제외하면 메모리 i/o는 3건 |
|
'Oracle 11g > Tuning' 카테고리의 다른 글
| 85 Row Chaining, Row Migration, B*tree, Bitmap Index, session_cached_cursors (0) | 2024.02.15 |
|---|---|
| 84 Serial direct read, Cluster Factor (0) | 2024.02.14 |
| 82 TX LOCK, Full Table Scan, Rowid Scan, Index Scan (1) | 2024.02.08 |
| 81 Trans, Lock, Redo, Append Hint (1) | 2024.02.07 |
| 80 Data Buffer Cache, Buffer Busy Wait, LRU, LRUW (1) | 2024.02.06 |