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