"PLSQL 성능분석"의 두 판 사이의 차이
DB CAFE
Goldlight111 (토론 | 기여) (→PLSQL 성능분석) |
|||
| (사용자 2명의 중간 판 44개는 보이지 않습니다) | |||
| 1번째 줄: | 1번째 줄: | ||
== PLSQL 성능분석 == | == PLSQL 성능분석 == | ||
| − | + | {{틀:고지상자 | |
| − | = | + | |제목 = PLSQL 성능분석 실습 순서 |
| − | # 실습용 | + | |내용 = |
| + | # 실습용 SP_SAMPLE_BATCH_PROCEDURE 프로시져 컴파일 하기 ( 프로시져 내부 3개 쿼리 존재 ) | ||
## 프로시저 시작과 끝에 DBMS_APPLICATION_INFO.set_module 처리 (필수) | ## 프로시저 시작과 끝에 DBMS_APPLICATION_INFO.set_module 처리 (필수) | ||
## 프로시저 내부 SQL 수행 전 DBMS_APPLICATION_INFO.set_action 처리 (옵션) | ## 프로시저 내부 SQL 수행 전 DBMS_APPLICATION_INFO.set_action 처리 (옵션) | ||
# 프로시져 실행 전 alter session set statistics_level = all; 셋팅 | # 프로시져 실행 전 alter session set statistics_level = all; 셋팅 | ||
# 프로시져 실행 | # 프로시져 실행 | ||
| − | # Script (plsqlxplann) 호출 하여, Sublime 편집기에 실행계획 | + | ## Script (plsqlxplann) 호출 하여, Sublime 편집기에 실행계획 출력하기 |
| + | }} | ||
| + | |||
| + | |||
| + | |||
=== 실습 하기 === | === 실습 하기 === | ||
| − | + | ==== 프로시져 생성==== | |
| + | <source lang=sql> | ||
CREATE OR REPLACE PROCEDURE SP_SAMPLE_BATCH_PROCEDURE | CREATE OR REPLACE PROCEDURE SP_SAMPLE_BATCH_PROCEDURE | ||
IS | IS | ||
| 75번째 줄: | 81번째 줄: | ||
dbms_output.put_line ( sqlerrm ); | dbms_output.put_line ( sqlerrm ); | ||
END SP_SAMPLE_BATCH_PROCEDURE; | END SP_SAMPLE_BATCH_PROCEDURE; | ||
| + | </source> | ||
| − | + | ||
| − | + | ==== 프로시져 실행 전, 세션 통계정보 레벨 셋팅 ==== | |
| − | + | <source lang=sql> | |
| + | - 아래 문장을 수행해야만, 실행 후 실행 계획을 얻을수 있다. pagesize, linesize 조정은 가독성 좋게 하는 효과가 있다. ( sql> @setting 대체 가능 ) | ||
sql> alter session set statistics_level = all; | sql> alter session set statistics_level = all; | ||
sql> set pagesize 2000; | sql> set pagesize 2000; | ||
sql> set linesize 2000; | sql> set linesize 2000; | ||
| + | </source> | ||
| + | |||
| − | + | ==== 프로시져 실행 ==== | |
| + | <source lang=sql> | ||
sql> exec sp_sample_batch_procedure; | sql> exec sp_sample_batch_procedure; | ||
| + | </source> | ||
| | ||
| − | |||
| − | |||
| − | |||
| − | *Settinig.sql 내용 | + | ==== Script 호출 후, Sublime 편집기에 실행계획 출력하기 ==== |
| − | alter session set statistics_level = all; | + | <source lang=sql> |
| − | set pagesize 2000; | + | sql> @plsqlxplann SP_SAMPLE_BATCH_PROCEDURE; ( @plsqlxplann 모듈명 ) |
| − | set linesize 2000; | + | </source> |
| + | |||
| + | |||
| + | ==== 프로시져 모든 SQL 실행계획 ==== | ||
| + | <source lang=sql> | ||
| + | ±¸ 10: WHERE MODULE = '&1' -- module명 ë°”ì¸ë”© | ||
| + | AND PLAN_HASH_VALUE <> 0 | ||
| + | ½Å 10: WHERE MODULE = 'SP_SAMPLE_BATCH_PROCEDURE' -- module명 ë°”ì¸ë”© | ||
| + | AND PLAN_HASH_VALUE <> 0 | ||
| + | |||
| + | PLAN_TABLE_OUTPUT EXECUTIONS CHILD_NUMBER | ||
| + | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ------------ | ||
| + | SQL_ID 3qtyvu1syqaaj, child number 1 2 1 | ||
| + | ------------------------------------- 2 1 | ||
| + | SELECT /*+ SP_SAMPLE_BATCH_PROCEDURE_001 */ COUNT(*) FROM TB_PLSQL_TEST 2 1 | ||
| + | 2 1 | ||
| + | Plan hash value: 3930280971 2 1 | ||
| + | 2 1 | ||
| + | ---------------------------------------------------------------------------------------------------------------------- 2 1 | ||
| + | | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | 2 1 | ||
| + | ---------------------------------------------------------------------------------------------------------------------- 2 1 | ||
| + | | 0 | SELECT STATEMENT | | 1 | | 11 (100)| | 1 |00:00:00.01 | 38 | 2 1 | ||
| + | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 38 | 2 1 | ||
| + | | 2 | TABLE ACCESS FULL| TB_PLSQL_TEST | 1 | 9000 | 11 (0)| 00:00:01 | 9000 |00:00:00.01 | 38 | 2 1 | ||
| + | ---------------------------------------------------------------------------------------------------------------------- 2 1 | ||
| + | 2 1 | ||
| + | Query Block Name / Object Alias (identified by operation id): 2 1 | ||
| + | ------------------------------------------------------------- 2 1 | ||
| + | 2 1 | ||
| + | 1 - SEL$1 2 1 | ||
| + | 2 - SEL$1 / TB_PLSQL_TEST@SEL$1 2 1 | ||
| + | 2 1 | ||
| + | Outline Data 2 1 | ||
| + | ------------- 2 1 | ||
| + | 2 1 | ||
| + | /*+ 2 1 | ||
| + | BEGIN_OUTLINE_DATA 2 1 | ||
| + | IGNORE_OPTIM_EMBEDDED_HINTS 2 1 | ||
| + | OPTIMIZER_FEATURES_ENABLE('12.1.0.2') 2 1 | ||
| + | DB_VERSION('12.1.0.2') 2 1 | ||
| + | ALL_ROWS 2 1 | ||
| + | OUTLINE_LEAF(@"SEL$1") 2 1 | ||
| + | FULL(@"SEL$1" "TB_PLSQL_TEST"@"SEL$1") 2 1 | ||
| + | END_OUTLINE_DATA 2 1 | ||
| + | */ 2 1 | ||
| + | 2 1 | ||
| + | Column Projection Information (identified by operation id): 2 1 | ||
| + | ----------------------------------------------------------- 2 1 | ||
| + | 2 1 | ||
| + | 1 - (#keys=0) COUNT(*)[22] 2 1 | ||
| + | 2 - (rowset=1020) 2 1 | ||
| + | 2 1 | ||
| + | Note 2 1 | ||
| + | ----- 2 1 | ||
| + | - dynamic statistics used: dynamic sampling (level=2) 2 1 | ||
| + | 2 1 | ||
| + | SQL_ID 327zu0t04qfhn, child number 1 20 1 | ||
| + | ------------------------------------- 20 1 | ||
| + | INSERT /*+ SP_SAMPLE_BATCH_PROCEDURE_002 */ INTO TB_PLSQL_TEST SELECT 20 1 | ||
| + | LEVEL, LEVEL, LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000 20 1 | ||
| + | 20 1 | ||
| + | Plan hash value: 1236776825 20 1 | ||
| + | 20 1 | ||
| + | ------------------------------------------------------------------------------------------------------------------------------------------------------------ 20 1 | ||
| + | | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | 20 1 | ||
| + | ------------------------------------------------------------------------------------------------------------------------------------------------------------ 20 1 | ||
| + | | 0 | INSERT STATEMENT | | 1 | | 2 (100)| | 0 |00:00:00.01 | 17 | | | | 20 1 | ||
| + | | 1 | LOAD TABLE CONVENTIONAL | TB_PLSQL_TEST | 1 | | | | 0 |00:00:00.01 | 17 | | | | 20 1 | ||
| + | | 2 | CONNECT BY WITHOUT FILTERING| | 1 | | | | 1000 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)| 20 1 | ||
| + | | 3 | FAST DUAL | | 1 | 1 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | | | | 20 1 | ||
| + | ------------------------------------------------------------------------------------------------------------------------------------------------------------ 20 1 | ||
| + | 20 1 | ||
| + | Query Block Name / Object Alias (identified by operation id): 20 1 | ||
| + | ------------------------------------------------------------- 20 1 | ||
| + | 20 1 | ||
| + | 1 - SEL$1 20 1 | ||
| + | 3 - SEL$1 / DUAL@SEL$1 20 1 | ||
| + | 20 1 | ||
| + | Outline Data 20 1 | ||
| + | ------------- 20 1 | ||
| + | 20 1 | ||
| + | /*+ 20 1 | ||
| + | BEGIN_OUTLINE_DATA 20 1 | ||
| + | IGNORE_OPTIM_EMBEDDED_HINTS 20 1 | ||
| + | OPTIMIZER_FEATURES_ENABLE('12.1.0.2') 20 1 | ||
| + | DB_VERSION('12.1.0.2') 20 1 | ||
| + | ALL_ROWS 20 1 | ||
| + | OUTLINE_LEAF(@"SEL$1") 20 1 | ||
| + | OUTLINE_LEAF(@"INS$1") 20 1 | ||
| + | FULL(@"INS$1" "TB_PLSQL_TEST"@"INS$1") 20 1 | ||
| + | NO_CONNECT_BY_FILTERING(@"SEL$1") 20 1 | ||
| + | CONNECT_BY_COMBINE_SW(@"SEL$1") 20 1 | ||
| + | END_OUTLINE_DATA 20 1 | ||
| + | */ 20 1 | ||
| + | 20 1 | ||
| + | Column Projection Information (identified by operation id): 20 1 | ||
| + | ----------------------------------------------------------- 20 1 | ||
| + | 20 1 | ||
| + | 2 - LEVEL[4] 20 1 | ||
| + | 20 1 | ||
| + | SQL_ID 5sbpsm2154zsk, child number 1 20 1 | ||
| + | ------------------------------------- 20 1 | ||
| + | DELETE /*+ SP_SAMPLE_BATCH_PROCEDURE_003 */ TB_PLSQL_TEST WHERE ROWNUM 20 1 | ||
| + | <= 100 20 1 | ||
| + | 20 1 | ||
| + | Plan hash value: 1650093290 20 1 | ||
| + | 20 1 | ||
| + | ----------------------------------------------------------------------------------------------------------------------- 20 1 | ||
| + | | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | 20 1 | ||
| + | ----------------------------------------------------------------------------------------------------------------------- 20 1 | ||
| + | | 0 | DELETE STATEMENT | | 1 | | 11 (100)| | 0 |00:00:00.01 | 111 | 20 1 | ||
| + | | 1 | DELETE | TB_PLSQL_TEST | 1 | | | | 0 |00:00:00.01 | 111 | 20 1 | ||
| + | |* 2 | COUNT STOPKEY | | 1 | | | | 100 |00:00:00.01 | 6 | 20 1 | ||
| + | | 3 | TABLE ACCESS FULL| TB_PLSQL_TEST | 1 | 10000 | 11 (0)| 00:00:01 | 100 |00:00:00.01 | 6 | 20 1 | ||
| + | ----------------------------------------------------------------------------------------------------------------------- 20 1 | ||
| + | 20 1 | ||
| + | Query Block Name / Object Alias (identified by operation id): 20 1 | ||
| + | ------------------------------------------------------------- 20 1 | ||
| + | 20 1 | ||
| + | 1 - DEL$1 20 1 | ||
| + | 3 - DEL$1 / TB_PLSQL_TEST@DEL$1 20 1 | ||
| + | 20 1 | ||
| + | Outline Data 20 1 | ||
| + | ------------- 20 1 | ||
| + | 20 1 | ||
| + | /*+ 20 1 | ||
| + | BEGIN_OUTLINE_DATA 20 1 | ||
| + | IGNORE_OPTIM_EMBEDDED_HINTS 20 1 | ||
| + | OPTIMIZER_FEATURES_ENABLE('12.1.0.2') 20 1 | ||
| + | DB_VERSION('12.1.0.2') 20 1 | ||
| + | ALL_ROWS 20 1 | ||
| + | OUTLINE_LEAF(@"DEL$1") 20 1 | ||
| + | FULL(@"DEL$1" "TB_PLSQL_TEST"@"DEL$1") 20 1 | ||
| + | END_OUTLINE_DATA 20 1 | ||
| + | */ 20 1 | ||
| + | 20 1 | ||
| + | Predicate Information (identified by operation id): 20 1 | ||
| + | --------------------------------------------------- 20 1 | ||
| + | 20 1 | ||
| + | 2 - filter(ROWNUM<=100) 20 1 | ||
| + | 20 1 | ||
| + | Column Projection Information (identified by operation id): 20 1 | ||
| + | ----------------------------------------------------------- 20 1 | ||
| + | 20 1 | ||
| + | 2 - "TB_PLSQL_TEST".ROWID[ROWID,10], ROWNUM[8] 20 1 | ||
| + | 3 - "TB_PLSQL_TEST".ROWID[ROWID,10] 20 1 | ||
| + | 20 1 | ||
| + | Note 20 1 | ||
| + | ----- 20 1 | ||
| + | - dynamic statistics used: dynamic sampling (level=2) 20 1 | ||
| + | 20 1 | ||
| + | |||
| + | 139 ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù. | ||
| + | </source> | ||
| + | |||
| + | |||
| + | ==== 참고 ==== | ||
| + | <source lang=sql> | ||
| + | 1. Settinig.sql 내용 | ||
| + | alter session set statistics_level = all; | ||
| + | set pagesize 2000; | ||
| + | set linesize 2000; | ||
| − | + | 2. plsqlxplann.sql 내용 | |
| − | SPOOL C:\app\myscript\xplan_list\plsqlxplan.txt | + | SPOOL C:\app\myscript\xplan_list\plsqlxplan.txt |
SELECT B.PLAN_TABLE_OUTPUT, TA.EXECUTIONS, TA.CHILD_NUMBER | SELECT B.PLAN_TABLE_OUTPUT, TA.EXECUTIONS, TA.CHILD_NUMBER | ||
| 116번째 줄: | 286번째 줄: | ||
SPOOL OFF | SPOOL OFF | ||
| − | + | $C:\Program Files\Sublime Text 3\sublime_text.exe C:\app\myscript\xplan_list\plsqlxplan.txt | |
| + | </source> | ||
| + | [[category:oracle]] | ||
2021년 5월 10일 (월) 17:34 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 PLSQL 성능분석[편집]
1.1 실습 하기[편집]
1.1.1 프로시져 생성[편집]
CREATE OR REPLACE PROCEDURE SP_SAMPLE_BATCH_PROCEDURE
IS
/* -- TEST TABLE 생성하기
CREATE TABLE TB_PLSQL_TEST
(
COL1 VARCHAR2(100)
,COL2 VARCHAR2(100)
,COL3 VARCHAR2(100)
,COL4 VARCHAR2(100)
);
-- 권한 부여 하기 ( SYS로 접속하여 TBA_DBA(접속계정)에 권한을 주자. DBMS_LOCK.SLEEP 수행하기 위해서. )
GRANT EXECUTE ON DBMS_LOCK TO TBA_DBA;
*/
PROCID VARCHAR2(50) := 'SP_SAMPLE_BATCH_PROCEDURE';
V_CNT NUMBER;
BEGIN
DBMS_APPLICATION_INFO.set_module( PROCID ,'Task Topic'); -- MODULE INPUT
BEGIN
DBMS_APPLICATION_INFO.set_action( PROCID || '_001' ); -- ACTION INPUT1
SELECT /*+ SP_SAMPLE_BATCH_PROCEDURE_001 */ COUNT(*)
INTO V_CNT
FROM TB_PLSQL_TEST;
DBMS_LOCK.sleep(10);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
DBMS_APPLICATION_INFO.set_action( PROCID || '_002' ); -- ACTION INPUT2
FOR V_REF IN 1 .. 10 LOOP
INSERT /*+ SP_SAMPLE_BATCH_PROCEDURE_002 */ INTO TB_PLSQL_TEST
SELECT LEVEL, LEVEL, LEVEL, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 1000;
END LOOP;
DBMS_LOCK.sleep(10);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
DBMS_APPLICATION_INFO.set_action( PROCID || '_003'); -- ACTION INPUT3
FOR V_REF IN 1 .. 10 LOOP
DELETE /*+ SP_SAMPLE_BATCH_PROCEDURE_003 */ TB_PLSQL_TEST
WHERE ROWNUM <= 100;
END LOOP;
DBMS_LOCK.sleep(10);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;
DBMS_APPLICATION_INFO.set_module( '','' ); -- MODULE INPUT
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ( sqlerrm );
END SP_SAMPLE_BATCH_PROCEDURE;
1.1.2 프로시져 실행 전, 세션 통계정보 레벨 셋팅[편집]
- 아래 문장을 수행해야만, 실행 후 실행 계획을 얻을수 있다. pagesize, linesize 조정은 가독성 좋게 하는 효과가 있다. ( sql> @setting 대체 가능 )
sql> alter session set statistics_level = all;
sql> set pagesize 2000;
sql> set linesize 2000;
1.1.4 Script 호출 후, Sublime 편집기에 실행계획 출력하기[편집]
sql> @plsqlxplann SP_SAMPLE_BATCH_PROCEDURE; ( @plsqlxplann 모듈명 )
1.1.5 프로시져 모든 SQL 실행계획[편집]
±¸ 10: WHERE MODULE = '&1' -- module명 ë°”ì¸ë”©
AND PLAN_HASH_VALUE <> 0
½Å 10: WHERE MODULE = 'SP_SAMPLE_BATCH_PROCEDURE' -- module명 ë°”ì¸ë”©
AND PLAN_HASH_VALUE <> 0
PLAN_TABLE_OUTPUT EXECUTIONS CHILD_NUMBER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ------------
SQL_ID 3qtyvu1syqaaj, child number 1 2 1
------------------------------------- 2 1
SELECT /*+ SP_SAMPLE_BATCH_PROCEDURE_001 */ COUNT(*) FROM TB_PLSQL_TEST 2 1
2 1
Plan hash value: 3930280971 2 1
2 1
---------------------------------------------------------------------------------------------------------------------- 2 1
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | 2 1
---------------------------------------------------------------------------------------------------------------------- 2 1
| 0 | SELECT STATEMENT | | 1 | | 11 (100)| | 1 |00:00:00.01 | 38 | 2 1
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 38 | 2 1
| 2 | TABLE ACCESS FULL| TB_PLSQL_TEST | 1 | 9000 | 11 (0)| 00:00:01 | 9000 |00:00:00.01 | 38 | 2 1
---------------------------------------------------------------------------------------------------------------------- 2 1
2 1
Query Block Name / Object Alias (identified by operation id): 2 1
------------------------------------------------------------- 2 1
2 1
1 - SEL$1 2 1
2 - SEL$1 / TB_PLSQL_TEST@SEL$1 2 1
2 1
Outline Data 2 1
------------- 2 1
2 1
/*+ 2 1
BEGIN_OUTLINE_DATA 2 1
IGNORE_OPTIM_EMBEDDED_HINTS 2 1
OPTIMIZER_FEATURES_ENABLE('12.1.0.2') 2 1
DB_VERSION('12.1.0.2') 2 1
ALL_ROWS 2 1
OUTLINE_LEAF(@"SEL$1") 2 1
FULL(@"SEL$1" "TB_PLSQL_TEST"@"SEL$1") 2 1
END_OUTLINE_DATA 2 1
*/ 2 1
2 1
Column Projection Information (identified by operation id): 2 1
----------------------------------------------------------- 2 1
2 1
1 - (#keys=0) COUNT(*)[22] 2 1
2 - (rowset=1020) 2 1
2 1
Note 2 1
----- 2 1
- dynamic statistics used: dynamic sampling (level=2) 2 1
2 1
SQL_ID 327zu0t04qfhn, child number 1 20 1
------------------------------------- 20 1
INSERT /*+ SP_SAMPLE_BATCH_PROCEDURE_002 */ INTO TB_PLSQL_TEST SELECT 20 1
LEVEL, LEVEL, LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000 20 1
20 1
Plan hash value: 1236776825 20 1
20 1
------------------------------------------------------------------------------------------------------------------------------------------------------------ 20 1
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | 20 1
------------------------------------------------------------------------------------------------------------------------------------------------------------ 20 1
| 0 | INSERT STATEMENT | | 1 | | 2 (100)| | 0 |00:00:00.01 | 17 | | | | 20 1
| 1 | LOAD TABLE CONVENTIONAL | TB_PLSQL_TEST | 1 | | | | 0 |00:00:00.01 | 17 | | | | 20 1
| 2 | CONNECT BY WITHOUT FILTERING| | 1 | | | | 1000 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)| 20 1
| 3 | FAST DUAL | | 1 | 1 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | | | | 20 1
------------------------------------------------------------------------------------------------------------------------------------------------------------ 20 1
20 1
Query Block Name / Object Alias (identified by operation id): 20 1
------------------------------------------------------------- 20 1
20 1
1 - SEL$1 20 1
3 - SEL$1 / DUAL@SEL$1 20 1
20 1
Outline Data 20 1
------------- 20 1
20 1
/*+ 20 1
BEGIN_OUTLINE_DATA 20 1
IGNORE_OPTIM_EMBEDDED_HINTS 20 1
OPTIMIZER_FEATURES_ENABLE('12.1.0.2') 20 1
DB_VERSION('12.1.0.2') 20 1
ALL_ROWS 20 1
OUTLINE_LEAF(@"SEL$1") 20 1
OUTLINE_LEAF(@"INS$1") 20 1
FULL(@"INS$1" "TB_PLSQL_TEST"@"INS$1") 20 1
NO_CONNECT_BY_FILTERING(@"SEL$1") 20 1
CONNECT_BY_COMBINE_SW(@"SEL$1") 20 1
END_OUTLINE_DATA 20 1
*/ 20 1
20 1
Column Projection Information (identified by operation id): 20 1
----------------------------------------------------------- 20 1
20 1
2 - LEVEL[4] 20 1
20 1
SQL_ID 5sbpsm2154zsk, child number 1 20 1
------------------------------------- 20 1
DELETE /*+ SP_SAMPLE_BATCH_PROCEDURE_003 */ TB_PLSQL_TEST WHERE ROWNUM 20 1
<= 100 20 1
20 1
Plan hash value: 1650093290 20 1
20 1
----------------------------------------------------------------------------------------------------------------------- 20 1
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | 20 1
----------------------------------------------------------------------------------------------------------------------- 20 1
| 0 | DELETE STATEMENT | | 1 | | 11 (100)| | 0 |00:00:00.01 | 111 | 20 1
| 1 | DELETE | TB_PLSQL_TEST | 1 | | | | 0 |00:00:00.01 | 111 | 20 1
|* 2 | COUNT STOPKEY | | 1 | | | | 100 |00:00:00.01 | 6 | 20 1
| 3 | TABLE ACCESS FULL| TB_PLSQL_TEST | 1 | 10000 | 11 (0)| 00:00:01 | 100 |00:00:00.01 | 6 | 20 1
----------------------------------------------------------------------------------------------------------------------- 20 1
20 1
Query Block Name / Object Alias (identified by operation id): 20 1
------------------------------------------------------------- 20 1
20 1
1 - DEL$1 20 1
3 - DEL$1 / TB_PLSQL_TEST@DEL$1 20 1
20 1
Outline Data 20 1
------------- 20 1
20 1
/*+ 20 1
BEGIN_OUTLINE_DATA 20 1
IGNORE_OPTIM_EMBEDDED_HINTS 20 1
OPTIMIZER_FEATURES_ENABLE('12.1.0.2') 20 1
DB_VERSION('12.1.0.2') 20 1
ALL_ROWS 20 1
OUTLINE_LEAF(@"DEL$1") 20 1
FULL(@"DEL$1" "TB_PLSQL_TEST"@"DEL$1") 20 1
END_OUTLINE_DATA 20 1
*/ 20 1
20 1
Predicate Information (identified by operation id): 20 1
--------------------------------------------------- 20 1
20 1
2 - filter(ROWNUM<=100) 20 1
20 1
Column Projection Information (identified by operation id): 20 1
----------------------------------------------------------- 20 1
20 1
2 - "TB_PLSQL_TEST".ROWID[ROWID,10], ROWNUM[8] 20 1
3 - "TB_PLSQL_TEST".ROWID[ROWID,10] 20 1
20 1
Note 20 1
----- 20 1
- dynamic statistics used: dynamic sampling (level=2) 20 1
20 1
139 ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.
1.1.6 참고[편집]
1. Settinig.sql 내용
alter session set statistics_level = all;
set pagesize 2000;
set linesize 2000;
2. plsqlxplann.sql 내용
SPOOL C:\app\myscript\xplan_list\plsqlxplan.txt
SELECT B.PLAN_TABLE_OUTPUT, TA.EXECUTIONS, TA.CHILD_NUMBER
FROM
(
SELECT T.MODULE, T.ACTION, T.LAST_ACTIVE_TIME, T.SQL_ID, T.CHILD_NUMBER, T.EXECUTIONS,T.ELAPSED_TIME, T.BUFFER_GETS, T.LAST_ACTIVE_TIME
FROM
(
SELECT a.*
, RANK() OVER( PARTITION BY ACTION ORDER BY LAST_ACTIVE_TIME DESC ) AS RANK
FROM V$SQL A
WHERE MODULE = '&1' -- module명 바인딩
AND PLAN_HASH_VALUE <> 0
) T
WHERE RANK = 1
) TA
, TABLE( DBMS_XPLAN.DISPLAY_CURSOR( TA.SQL_ID, TA.CHILD_NUMBER, 'ADVANCED ALLSTATS LAST') ) B
;
SPOOL OFF
$C:\Program Files\Sublime Text 3\sublime_text.exe C:\app\myscript\xplan_list\plsqlxplan.txt