다른 명령
PLSQL 성능분석
실습 하기
프로시져 생성
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;
프로시져 실행 전, 세션 통계정보 레벨 셋팅
- 아래 문장을 수행해야만, 실행 후 실행 계획을 얻을수 있다. pagesize, linesize 조정은 가독성 좋게 하는 효과가 있다. ( sql> @setting 대체 가능 ) sql> alter session set statistics_level = all; sql> set pagesize 2000; sql> set linesize 2000;
프로시져 실행
sql> exec sp_sample_batch_procedure;
Script 호출 후, Sublime 편집기에 실행계획 출력하기
sql> @plsqlxplann SP_SAMPLE_BATCH_PROCEDURE; ( @plsqlxplann 모듈명 )
프로시져 모든 SQL 실행계획
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. 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