"PLSQL 성능분석"의 두 판 사이의 차이
DB CAFE
Goldlight111 (토론 | 기여) (→실습 순서) |
Goldlight111 (토론 | 기여) (→PLSQL 성능분석) |
||
2번째 줄: | 2번째 줄: | ||
=== 실습 순서 === | === 실습 순서 === | ||
− | 1) 아래 TB_PLSQL_TEST 프로시져 컴파일 하기 ( 프로시져 내부 3개 쿼리 존재 ) | + | 1) 아래 TB_PLSQL_TEST 프로시져 컴파일 하기 ( 프로시져 내부 3개 쿼리 존재 ) |
- 프로시저 시작과 끝에 DBMS_APPLICATION_INFO.set_module 처리 (필수) | - 프로시저 시작과 끝에 DBMS_APPLICATION_INFO.set_module 처리 (필수) | ||
- 프로시저 내부 SQL 수행 전 DBMS_APPLICATION_INFO.set_action 처리 (옵션) | - 프로시저 내부 SQL 수행 전 DBMS_APPLICATION_INFO.set_action 처리 (옵션) | ||
9번째 줄: | 9번째 줄: | ||
4) Script (plsqlxplann) 호출 하여, Sublime 편집기에 실행계획 출력 되게 하기 | 4) Script (plsqlxplann) 호출 하여, Sublime 편집기에 실행계획 출력 되게 하기 | ||
− | === | + | === 실습 하기 === |
− | + | 1) 프로시져 생성 | |
− | + | ||
− | + | CREATE OR REPLACE PROCEDURE SP_SAMPLE_BATCH_PROCEDURE | |
− | 2) 프로시져 실행 전 alter session set statistics_level = all; | + | 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; | ||
+ | |||
+ | 2) 프로시져 실행 전, 세션 통계정보 레벨 셋팅 | ||
+ | : 셋팅을 해야지만 실제 실행계획을 얻을수 있고 pagesize, linesize 조정을 해야 실행계획이 가독성 좋게 출력된다. | ||
+ | ( sql> @setting 으로 대체 가능 ) | ||
+ | sql> alter session set statistics_level = all; | ||
+ | sql> set pagesize 2000; | ||
+ | sql> set linesize 2000; | ||
+ | |||
+ | 3) 프로시져 실행 | ||
+ | sql> exec sp_sample_batch_procedure; | ||
+ | | ||
+ | 4) Script 호출 하여, Sublime 편집기에 바로 실행계획이 보이게 하기 | ||
+ | sql>@plsqlxplann SP_SAMPLE_BATCH_PROCEDURE; ( @plsqlxplann 모듈명 ) | ||
+ | - 4) 수행 결과 아래 이미지와 같이 서브라임 편집기에 프로시져의 모든 SQL 실행계획이 출력됨. | ||
+ | |||
+ | |||
+ | *Settinig.sql 내용 | ||
+ | alter session set statistics_level = all; | ||
+ | set pagesize 2000; | ||
+ | set linesize 2000; | ||
+ | |||
+ | *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 | ||
+ | |
2020년 12월 2일 (수) 22:59 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 PLSQL 성능분석[편집]
1.1 실습 순서[편집]
1) 아래 TB_PLSQL_TEST 프로시져 컴파일 하기 ( 프로시져 내부 3개 쿼리 존재 ) - 프로시저 시작과 끝에 DBMS_APPLICATION_INFO.set_module 처리 (필수) - 프로시저 내부 SQL 수행 전 DBMS_APPLICATION_INFO.set_action 처리 (옵션) 2) 프로시져 실행 전 alter session set statistics_level = all; 셋팅 3) 프로시져 실행 4) Script (plsqlxplann) 호출 하여, Sublime 편집기에 실행계획 출력 되게 하기
1.2 실습 하기[편집]
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;
2) 프로시져 실행 전, 세션 통계정보 레벨 셋팅
: 셋팅을 해야지만 실제 실행계획을 얻을수 있고 pagesize, linesize 조정을 해야 실행계획이 가독성 좋게 출력된다. ( sql> @setting 으로 대체 가능 )
sql> alter session set statistics_level = all; sql> set pagesize 2000; sql> set linesize 2000;
3) 프로시져 실행 sql> exec sp_sample_batch_procedure; 4) Script 호출 하여, Sublime 편집기에 바로 실행계획이 보이게 하기 sql>@plsqlxplann SP_SAMPLE_BATCH_PROCEDURE; ( @plsqlxplann 모듈명 ) - 4) 수행 결과 아래 이미지와 같이 서브라임 편집기에 프로시져의 모든 SQL 실행계획이 출력됨.
- Settinig.sql 내용
alter session set statistics_level = all; set pagesize 2000; set linesize 2000;
- 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