PL/SQL 샘플
DB CAFE
(PL/SQL에서 넘어옴)
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
- 넘겨주기 = PL/SQL =
android 데이터 트랜잭션 처리 능력이나 정보 보호, 데이터에 대한 보안, 예외처리 기능, 객체지향 등 데이터베이스와 관련된 중요한 모든 기능을 지원
- 데이터베이스 업무를 처리하기에 최적화된, 오라클에서 제공하는 프로그래밍 언어
- PL/SQL엔진은 메모리에서 구동됨
목차
1 실행 원리[편집]
- PL/SQL 블록에서의 SQL문장이 수행되어 결과가 돌아옴
- 그 결과를 받고 난 후 선언된 변수와 함께 나머지 PL/SQL 문장을 실행됨
2 기본 구조[편집]
- DECLARE (선언부)
- - 모든 변수나 상수를 선언
- EXECUTABLE (실행부)
- - BEGIN,제어문 반복문, 함수정의 등의 로직을 기술
- EXCEPTION (예외처리부)
- - 실행 도중 에러 발생시 해결하는 문장 기술
3 유형[편집]
- Anonymous PL/SQL Block
- - 익명블록, 일회용, 한번만 쓰고 끝
- Stored PL/SQL Block
- - 저장된블록, 주기적으로 반복해서 사용할 경우
- PL/SQL은 기본적으로 처리된 결과값을 화면에 출력하지 않기 때문에 화명 출력 기능을 활성화 시켜야 함
SCOTT>set serveroutput on ;4 기본적인 문법[편집]
DECLARE
변수선언 ;
BEGIN
SQL 문장 ;
DBMS_OUTPUT.PUT_LINE(출력내용) ; --> 화면출력을 어떻게 할 것인가, END 전에 설정, 출력내용에 써주는 칼럼명은 변수이름을 써줘야한다!)
END ;
/5 주의사항[편집]
- 들여쓰기 권장 - 보기에도 쉬움. 습관을 들이자
- 그룹함수와 DECODE 함수는 SQL 문장에 포함되어야만 사용할 수 없음
- 문자, 날짜는 ' '로 묶어주기
- PL/SQL에서는 DDL, DCL을 지원하지 않음
- DDL - CREATE / ALTER / TRUNCATE / DROP
- DCL - GRAN / REVOKE
5.1 SELECT 문장 사용하기[편집]
- 문법: (실행부에서의 문법)
BEGIN
SELECT 칼럼1,칼럼2 INTO 변수1, 변수2
FROM 테이블명
WHERE 조건 ;
END ;
5.2 DML 문장 사용하기 (INSERT / UPDATE / DELETE / MERGE)[편집]
5.2.1 INSERT[편집]
- 서버에 데이터를 입력해주는 것이니까 변수선언은 없음 즉, DECLARE (선언부)는 없음
- 단, 사용자로부터 입력받은 값으로 테이블에 데이터 입력하는 것은 입력받는 값에 대한 변수는 선언해줘야함
- 문법:
BEGIN
INSERT INTO 테이블명
VALUES (값/변수) ;
END ;5.2.2 UPDATE[편집]
BEGIN
UPDATE 테이블명
SET 칼럼=값
WHERE 조건 ;
END ;5.2.4 MERGE[편집]
- 문법:
BEGIN
MERGE INTO 테이블1
USING 테이블2
ON (병합조건절)
WHEN MATCHED THEN
UPDATE SET 업데이트내용
WHEN NOT MATCHED THEN
INSERT VALUES (칼럼명) ;
END ;
- 알아두기!
- a = 20
- --> 비교를 하는 비교연산자, a가 20인지 비교
- a := 20
- --> 할당 연산자, 20의 값을 a에 할당
- 사용자에게 값을 입력 받아서 변수에 할당 할 때는 &(앰퍼센트) 기호를 사용
- 그래서 사용자한테 입력을 받은 값을 a에 넣고 싶으면
- a := '&abc'
5.2.5 중첩된 PL/SQL 블록 작성 방법[편집]
5.3 PL/SQL 샘플[편집]
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/6 기본 프로시져[편집]
DECLARE
--변수,상수 선언
BEGIN
--실행 가능 SQL문,PL/SQL문
EXCEPTION
--에러처리
END;
/* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */
Declare
v_sale number := 2000;
begin
update test set a = v_sale;
delete from test where a = v_sale;
commit;
end;
/* Procedure에서 Procedure를 호출하는 방법 */
A프로시져에서 "B프로시져명(변수, 변수2);"
PROCEDURE 리턴 여러개
FUNCTION 리턴 한개
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE';
/* 프로시저나 함수 조회 */
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION';
/* PROCEDURE & FUNCTION 삭제하기 */
DROP FUNCTION lee2;
DROP PROCEDURE lee2;7 프로시져 실행 옵션[편집]
AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT)
AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용.- PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가
8 커서 활용 샘플[편집]
CREATE OR REPLACE PROCEDURE RTIS_DBA.SP_OBJ_INVALID_TO_VALID
/*
*
*/
(
IN_DATE IN VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD')
)
AUTHID CURRENT_USER
IS
CURSOR INVALID_OBJECT IS
SELECT A.GRANTEE, A.OWNER, A.TABLE_NAME, A.GRANTOR, A.PRIVILEGE,A.OBJECT_TYPE
FROM TB_MGR_GRANT A
;
-- V_SQL VARCHAR2(200);
V_G_SQL VARCHAR2(200);
-- V_L_SQL VARCHAR2(2000);
V_GRANTEE VARCHAR2(100);
V_OWNER VARCHAR2(100);
V_TABLE_NAME VARCHAR2(100);
V_GRANTOR VARCHAR2(100);
V_PRIVILEGE VARCHAR2(100);
V_OBJECT_TYPE VARCHAR2(100);
V_GRANT_REVOKE_GBN VARCHAR2(100);
V_MSG long;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR V_ROW IN INVALID_OBJECT
LOOP
-- 블록 에러 발생시에도 계속 실행토록
BEGIN
V_GRANTEE:= V_ROW.GRANTEE;
V_OWNER:= V_ROW.OWNER;
V_TABLE_NAME:= V_ROW.TABLE_NAME;
V_GRANTOR:= V_ROW.GRANTOR;
V_PRIVILEGE:= V_ROW.PRIVILEGE;
V_OBJECT_TYPE:= V_ROW.OBJECT_TYPE;
V_GRANT_REVOKE_GBN := 'GRANT TO USER';
-- 권한 추가
V_G_SQL := 'GRANT '||V_ROW.PRIVILEGE||' ON '||V_ROW.OWNER||'.'||V_ROW.TABLE_NAME||' TO '||V_ROW.GRANTEE;
EXECUTE IMMEDIATE V_G_SQL;
-- LOG 기록
INSERT INTO TB_MGR_GRANT_LOG
(GRANTEE , OWNER , TABLE_NAME , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN)
VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
INSERT INTO TB_MGR_GRANT_LOG
(GRANTEE , OWNER , TABLE_NAME , GRANTOR , PRIVILEGE ,OBJECT_TYPE ,GRANT_REVOKE_GBN,GRANT_REVOKE_SQL)
VALUES (V_GRANTEE,V_OWNER,V_TABLE_NAME,V_GRANTOR,V_PRIVILEGE,V_OBJECT_TYPE,V_GRANT_REVOKE_GBN,V_MSG);
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
/9 PL/SQL 변수[편집]
- 변수는 반드시 문자로 시작해야함!
9.1 단순 변수[편집]
- SCALAR 변수
- - 데이터 타입을 직접 지정해 주는 변수
- - number, varchar2, date 등등 으로 직접 데이터타입을 정해주는 것
- Reference변수 = 참조변수
- - 해당 데이터가 들어있는 칼럼 정보를 참조
- v_ename emp.ename%type
- --> 변수 이름을 v_ename 으로 하되 데이터타입은 emp테이블의 ename 칼럼의 데이터 타입과 동일하게
- 칼럼이 많지 않을 경우는 위의 방법을 쓰면 되지만 칼럼이 많을 경우는 rowtype 변수를 사용,
- 하나의 테이블에 여러 칼럼의 값을 한꺼번에 저장할 수 있는 변수
vrow emp%ROWTYPE
<source>
== 복합 변수 ==
* 변수 하나안에 여러가지 다른 유형의 데이터를 포함
=== Record Type ===
: - 레코드 타입 변수
: 여러개의 컬럼에 여러개의 값을 넣음
* 사용방법:
** DECLARE 에서
<source lang=sql>
TYPE 타입이름(보통 테이블명_record_type) IS RECORD
(타입이름에 들어갈 여러 변수들, 일반 변수 선언과 동일한 문법으로 쓰면 됨) ;
변수이름 타입이름 ;
BEGIN에서
SELECT 출력할 칼럼들 INTO 변수이름 FROM 테이블명 WHERE 조건 ;9.1.1 Table Type[편집]
- - 테이블/ 컬렉션 타입 변수
- 하나의 칼럼에 데이터가 여러개
- 하지만 interger라는 칼럼을 만들 수 있는데 관리를 위한 데이터 구분의 역할을 하는 칼럼
- 사용방법:
DECLARE 에서
TYPE 타입이름(보통 tbl_테이블명) IS TABLE OF
(사용할 변수 설정) ; --> 테이블 타입은 칼럼이 하나기 때문에 변수도 하나
INDEX BY BINARY_INTEGER --> interger 칼럼 만드는 옵션
변수이름 타입이름 ;
BEGIN에서
SELECT 출력할 칼럼들 INTO 다른변수이름 FROM 테이블명 WHERE 조건 ;
변수이름(0) := 다른변수;
--> table type의 변수 1번째 칸에 다른변수 즉, 출력해야 할 칼럼에 대한 내용을 입력하라는 뜻.문자열 처리방법
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#sthref344
q'구분자.........구분자'
p_cols=> q''{'||V_INDEX_COLUMNS||'}''10 PL/SQL 제어문[편집]
10.1 조건문[편집]
10.1.1 IF 문[편집]
- 조건에 따라 값이 변하는 칼럼의 변수를 지정해 줄때
:= NULL 로 세팅- IF문 조건에 쓰는 것은 변수를 써야함
IF문 끝나면 꼭 END IF ; 적어주기10.1.2 조건이 여러개일 경우[편집]
- 문법:
IF 조건 THEN
실행문장 ;
ELSIF 조건 THEN
실행문장 ;
ELSIF 조건 THEN
실행문장 ;
ELSE 실행문장;
END IF ;10.2 CASE[편집]
- 조건이 여러개인 IF문을 더 간결하게 쓸 수 있음
- 콤마로 구분하지 않으니까 조심하기!
- CASE문 끝나면 꼭 END ; 적어주기
- 문법:
CASE 조건
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
ELSE
END ;
10.3 반복문[편집]
- 반복 횟수를 알 수 없는 경우
- 기본 LOOP
- WHILE
- 반복 횟수를 지정할 경우
- FOR
10.3.1 기본 LOOP[편집]
- 문법:
LOOP
PL/SQL 문장 ;
PL/SQL 문장 ;
EXIT WHEN 조건 ;
END LOOP ;- 조건이 만족해야지 반복이 끝난다.
- 조건에 맞지 않으면 계속 반복
10.3.3 FOR[편집]
- 문법:
FOR 변수 IN (REVERSE) start값 .. end값 LOOP
PL/SQL 문장 ;
PL/SQL 문장 ;
END LOOP ;- REVERSE는 거꾸로 반복해야할 때 입력해주면 됨.
- 내림차순으로 반복
- 대신 start값에서 더 큰 숫자를 써주는것이 아니라 바뀌지 않고 그대로 작은 값을 써줘야한다.
- FOR 문은 사용할 변수는 미리 선언하지 않아도 사용가능 ** 즉, DECLARE 부문 없이 바로 BEGIN으로 시작해도 됨.
- 꼭 start값 end값은 숫자일 필요는 없음
10.4 PLSQL 성능분석[편집]
10.4.1 실습 하기[편집]
10.4.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;
10.4.1.2 프로시져 실행 전, 세션 통계정보 레벨 셋팅[편집]
- 아래 문장을 수행해야만, 실행 후 실행 계획을 얻을수 있다. pagesize, linesize 조정은 가독성 좋게 하는 효과가 있다. ( sql> @setting 대체 가능 )
sql> alter session set statistics_level = all;
sql> set pagesize 2000;
sql> set linesize 2000;
10.4.1.4 Script 호출 후, Sublime 편집기에 실행계획 출력하기[편집]
sql> @plsqlxplann SP_SAMPLE_BATCH_PROCEDURE; ( @plsqlxplann 모듈명 )
10.4.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 ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.
10.4.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