by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

PLSQL 패키지

Version 16 by 윤은경
on 3월 10, 2012 09:18.

<< Changes from 15 to 16
compared with
Current by 윤은경
on 3월 10, 2012 09:26.

 
Key
These lines were removed. This word was removed.
These lines were added. This word was added.

View page history


There are 1 changes. View first change.

 h1. 1.5 PL/SQL 패키지
 오라클이 제공하는 모든 관리 기능들은 반드시 관련된 PL/SQL 패키지와 함께 제공된다. 그러므로, PL/SQL 패키지의 사용법을 아는 것은 많은 도움이 된다.
  
 테스트 서버
 {code}
 ORACLE@CX3WAS1 >select * from v$version;
  
 BANNER
 --------------------------------------------------------------------------------
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 PL/SQL Release 11.2.0.2.0 - Production
 CORE 11.2.0.2.0 Production
 TNS for Linux: Version 11.2.0.2.0 - Production
 NLSRTL Version 11.2.0.2.0 - Production
  
 {code}
  
 h3. 1.5.2 DBMS_UTILITY
 다음과 같은 작업을 할 수 있다.
  
 h4. 시간값 얻기
 특정 작업의 소요 시간을 알기위해 DBMS_UTILITY.GET_TIME 함수를 사용할 수 있다.
  
 단위: 1/100초
 사용 방법: 절대값은 의미없고, 특정 기간의 차이 값을 통해 소요 시간을 구할 수 있다.
 예제는 다음과 같다.
  
 {code}
 SQL> col cur_hsec new_value cur_hsec
 SQL> select dbms_utility.get_time as cur_hsec from dual;
 -- 작업 진행
 SQL> select trunc((dbms_utility.get_time - &cur_hsec)/100, 2) as elapsed from dual;
 {code}
 test
 {code}
 ORACLE@CX3WAS1 >col cur_hsec new_value cur_hsec
 ORACLE@CX3WAS1 >select dbms_utility.get_time as cur_hsec from dual;
  
  CUR_HSEC
 ----------
 -1.488E+09
  
 ORACLE@CX3WAS1 >select count(*) from all_objects;
  
  COUNT(*)
 ----------
  6974
  
 ORACLE@CX3WAS1 >select trunc((dbms_utility.get_time - &cur_hsec)/100, 2) as elapsed from dual;
 old 1: select trunc((dbms_utility.get_time - &cur_hsec)/100, 2) as elapsed from dual
 new 1: select trunc((dbms_utility.get_time - -1.488E+09)/100, 2) as elapsed from dual
  
  ELAPSED
 ----------
  -1755.34
  
 {code}
  
 V$TIMER을 읽어서 DBMS_UTILITY.GET_TIME과 같은 일을 할 수 있다.
  
 일반계정인 경우
 {code}
 ORACLE@CX3WAS1 >select hsecs from V$TIMER;
 select hsecs from V$TIMER
  *
 ERROR at line 1:
 ORA-00942: table or view does not exist
  
  
 ORACLE@CX3WAS1 >desc V$TIMER;
 ERROR:
 ORA-04043: object "SYS"."V_$TIMER" does not exist
 {code}
 {code}
 SYS@CX3WAS1 >desc V$TIMER;
  Name Null? Type
  ----------------------------------------------------------------- -------- --------------------------------------------
  HSECS NUMBER
  
 SYS@CX3WAS1 >select hsecs from V$TIMER;
  
  HSECS
 ----------
 2806856002
  
 {code}
  
 h4. DBA 값 얻기
 16진수나 10진수로 표현된 DBA(Data Block Address)값을 파일번호+블록 번호로 변환하는 방법을 알아보자. 이때, DBMS_UTILITY의 DATA_BLOCK_ADDRESS_FILE 함수와 DATA_BLOCK_ADDRESS_BLOCK 함수를 사용한다.
 테스트 테이블은 다음과 같이 만들었다.
 {code}
 ORACLE@CX3WAS1 >create table t1 as select level as c1 from dual connect by level <= 100;
 Table created.
 ORACLE@CX3WAS1 >create index t1_n1 on t1(c1);
 Index created.
 {code}
 id 정보를 확인하고 트레이스를 생성한다. (sys)
 {code}
 SYS@CX3WAS1 >col data_object_id new_value obj_id
 SYS@CX3WAS1 >select data_object_id from dba_objects
 where owner = 'ORACLE' and object_name = 'T1_N1'; 2
  
 DATA_OBJECT_ID
 --------------
  65268
  
 SYS@CX3WAS1 >alter session set events 'immediate trace name treedump level &obj_id';
 old 1: alter session set events 'immediate trace name treedump level &obj_id'
 new 1: alter session set events 'immediate trace name treedump level 65268'
  
 {code}
 trace file에서 블록 정보를 확인한다.
 {code}
 ----- begin tree dump
 leaf: 0x1c0004b 29360203 (0: nrow: 100 rrow: 100)
 ----- end tree dump
 {code}
 여기서 [0x1c0004b]은 DBA의 16진수값이며, [29360203]은 DBA의 10 진수값이다.
 REGEXP_REPLACE함수를 사용하면 위이 정보로 10진수의 DBA값을 추출할 수 있다.
 {code}
 SQL> col dba new_value dba
 SQL> select regexp_replace(column_value,
 'leaf: 0x[[:xdigit:]]+ ([[:digit:]]+) [[:print:]]+', '\1') as dba
 from table(tpack.get_tracefile_contents(tpack.get_tracefile_name))
 where column_value like 'leaf:%'
 and rownum = 1;
 {code}
 하지만 tpack이 없어서 잘 안된다.
  
 이와 같이 추출한 DBA값을 DBMS_UTILITY패키지를 이용해서 변환 할 수 있다.
 {code}
 SQL> col file_no new_value file_no
 SQL> col block_no new_value block_no
 SQL> select dbms_utility.data_block_address_file(&dba) as file_no,
 dbms_utility.data_block_address_block(&dba) as block_no
 from dual;
 {code}
 이와 같이 파일 번호와 블록 번호를 얻은 후 블록 덤프를 수행하거나 기타 필요한 작업을 수행하면 된다.
 {code}
 SQL> alter system dump datafile &file_no block &block_no;
 {code}
  
 테스트는 다음과 같다.
 {code}
 SYS@CX3WAS1 >col file_no new_value file_no
 SYS@CX3WAS1 >col block_no new_value block_no
 SYS@CX3WAS1 >select dbms_utility.data_block_address_file(29360203) as file_no,
  2 dbms_utility.data_block_address_block(29360203) as block_no
  3 from dual;
  
  FILE_NO BLOCK_NO
 ---------- ----------
  7 75
  
 SYS@CX3WAS1 >alter system dump datafile &file_no block &block_no;
 old 1: alter system dump datafile &file_no block &block_no
 new 1: alter system dump datafile 7 block 75
  
 System altered.
 {code}
  
 h3. 1.5.2 DBMS_LOCK
 이 패키지는 User Lock을 구현하기 위한 목적으로 제공되는 패키지 이다. 하지만 여기서는 이 목적 보다는 sleep을 사용하기 위해 많이 사용한다.
 {code}
 SQL> exec dbms_lock.sleep(0.1);
 -- 0.1초 지연한다.
 {code}
 다음과 같이 함수로 구현하여 쿼리 실행 시 사용이 가능하다.
 {code}
 SQL> create or replace function fsleep (p_dummy number, p_sleep number)
 return number
 is
 begin
 dbms_lock.sleep(p_sleep);
 return 1;
 end;
 /
  
 SQL> select count(*) from dual where fsleep(level, 0.1) = 1
 connect by level <= 100;
  
 SQL> insert into t1 select level from dual where fsleep(level,0.1) = 1
 connect by level <= 100;
 {code}
  
 테스트는 다음과 같다.
 {code}
 SYS@CX3WAS1 >exec dbms_lock.sleep(0.1);
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >create or replace function fsleep (p_dummy number, p_sleep number)
 return number
 is
 begin 2 3 4
 dbms_lock.sleep(p_sleep);
 return 1;
 end;
 /
  5 6 7 8
 Function created.
  
 SYS@CX3WAS1 >select count(*) from dual where fsleep(level, 0.1) = 1
 connect by level <= 100;
  2
  
  COUNT(*)
 ----------
  100
  
 {code}
  
 h3. 1.5.3 DBMS_PIPE
 이 패키지를 사용하면 하나의 인스턴스에서 여러 세션간의 작업 순서를 제어할 수 있다.
 DBMS_PIPE 패키지중에 RECEIVE_MESSAGE를 사용하면 누군가가 메시지를 보낼때까지 기다리게 하는 것이 가능하다.
 {code}
 SQL> create or replace procedure wait_for_signal(p_maxwait number default dbms_pipe.maxwait)
 is
 v_status number;
 v_received varchar2(1);
 begin
 v_status := dbms_pipe.receive_message('signal',p_maxwait); //named pipe의 메세지를 로컬 버퍼로 전송(파이프명,대기시간)
 if v_status = 0 then
 dbms_pipe.unpack_message(v_received);//로컬 버퍼로부터 메세지를 읽는다. (값)
 end if;
 end wait_for_signal;
 /
 {code}
 메시지를 보내는 함수를 만들어 보자.
 {code}
 SQL> create or replace procedure signal
 is
 v_status number;
 begin
 dbms_pipe.pack_message('Y'); //로컬 메세지 버퍼에 메세지를 생성한다. (값)
 v_status := dbms_pipe.send_message('signal'); //로컬 버퍼에 있는 메세지를 named pipe에 보낸다. (파이프명)
 // 해당 파이프가 명시적으로 생성된 것이 아니면, 암시적으로 해당 파이프명을 가지는 Public 파이프를 생성한다.
 end signal;
 /
 {code}
 다음과 같이 실행하면 된다.
 {code}
 --- session #1
 SQL> exec dbms_application_info.set_client_info('session1');
 SQL> exec wait_for_signal;
  
 --- session #2
 SQL> exec signal;
 {code}
 singal 실행 후 session 1에 작업이 가능하게 된다.
 {code}
 --- session #1
 SQL> select count(*) from all_objects;
 {code}
  
 session 1 입장에서의 테스트 내용은 다음과 같다.
 {code}
 SYS@CX3WAS1 >exec dbms_application_info.set_client_info('session1');
  
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >exec wait_for_signal;
  
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >select count(*) from all_objects;
  
  COUNT(*)
 ----------
  23870
  
 SYS@CX3WAS1 >col event format a30
 SYS@CX3WAS1 >select * from ( select session_id, event, sql_id from v$active_session_history
  2 where session_id = &sid
  3 order by sample_time desc ) where rownum <= 5;
 Enter value for sid: 400
 old 2: where session_id = &sid
 new 2: where session_id = 400
  
 SESSION_ID EVENT SQL_ID
 ---------- ------------------------------ -------------
  400 9tz4qu4rj9rdp
  400 log file sync
  400 696urnsam1bqj
  400
  400 g58hdppd54vru
  
 {code}
 h3. 1.5.4 DBMS_RANDOM
 이 패키지는 랜덤 값을 생성하는데 사용한다.
 {code}
 SQL> select dbms_random.random from dual;
 SQL> select dbms_random.value(1,10000) from dual;
 SQL> select trunc(dbms_random.value(1,10000)) from dual;
 SQL> select level from dual connect by level <= 10
 order by dbms_random.random;
 {code}
 테스트는 다음과 같다.
 {code}
 ORACLE@CX3WAS1 >select dbms_random.random from dual;
  
  RANDOM
 ----------
  253530142
  
 ORACLE@CX3WAS1 >select dbms_random.value(1,10000) from dual;
  
 DBMS_RANDOM.VALUE(1,10000)
 --------------------------
  2931.38688
  
 ORACLE@CX3WAS1 >select trunc(dbms_random.value(1,10000)) from dual;
  
 TRUNC(DBMS_RANDOM.VALUE(1,10000))
 ---------------------------------
  5172
  
 ORACLE@CX3WAS1 >select level from dual connect by level <= 10
 order by dbms_random.random;
  2
  LEVEL
 ----------
  9
  2
  3
  10
  7
  4
  5
  8
  1
  6
  
 10 rows selected.
 {code}
  
 h3. 1.5.5 DBMS_APPLICATION_INFO
 각 세션별로 적절한 모듈명 (V$SESSION.MODULE) 또는 Client 정보 (V$SESSION.CLIENT_INFO)을 부여하여 테스트의 효율성을 높힐 때 사용한다.
 {code}
 SQL> exec dbms_application_info.set_module('module','action1');
 SQL> select sid from v$session where module = 'module1';
 SQL> exec dbms_application_info.set_client_info('client1');
 SQL> select sid from v$session where client_info = 'client1';
 {code}
 테스트는 다음과 같다.
 {code}
 SYS@CX3WAS1 >exec dbms_application_info.set_module('module1','action1');
  
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >select sid from v$session where module = 'module1';
  
  SID
 ----------
  10
  
 SYS@CX3WAS1 >exec dbms_application_info.set_client_info('client1');
  
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >select sid from v$session where client_info = 'client1';
  
  SID
 ----------
  10
 {code}
  
 h3. 1.5.6 DBMS_XPLAN
 이 패키지를 사용하면 실행계획과 관련된 정보를 빠르고 정확하게 얻을 수 있다.
 테스트를 하기 위한 데이터는 다음과 같이 작업 한다.
 {code}
 ORACLE@CX3WAS1 >create table t1 as select level as c1, decode(level, 10000, 'one','many') as c2
  2 from dual connect by level <= 10000;
 Table created.
  
 ORACLE@CX3WAS1 >create table t2 as select level as c1, 'x' as c2 from dual
  2 connect by level <= 10000;
 Table created.
  
 ORACLE@CX3WAS1 >create index t1_n1 on t1(c1);
 Index created.
  
 ORACLE@CX3WAS1 >create index t1_n2 on t1(c2);
 Index created.
  
 ORACLE@CX3WAS1 >create index t2_n1 on t2(c1);
 Index created.
  
 {code}
 {code}
 SYS@CX3WAS1 >exec dbms_stats.gather_table_stats('oracle','t1',method_opt=>'for all columns size skewonly');
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >exec dbms_stats.gather_table_stats('oracle','t2');
 PL/SQL procedure successfully completed.
  
 {code}
 h4.예상실행계획 얻기
 예상 실행 계획은 DBMS_XPLAN.DISPLAY 함수를 통해 얻을 수 있다.
 {code}
 SQL> explain plan for
 select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
 SQL>select * from table(dbms_xplan.display);
 {code}
 실제 테스트 하면 다음과 같이 출력된다.
 {code}
 ORACLE@CX3WAS1 >select * from table(dbms_xplan.display);
  
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------
 Plan hash value: 1838229974
  
 ---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | 5000 | 75000 | 8 (13)| 00:00:01 |
 |* 1 | HASH JOIN | | 5000 | 75000 | 8 (13)| 00:00:01 |
 |* 2 | TABLE ACCESS FULL| T1 | 5000 | 45000 | 4 (0)| 00:00:01 |
 | 3 | TABLE ACCESS FULL| T2 | 10000 | 60000 | 3 (0)| 00:00:01 |
 ---------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
  
  1 - access("T1"."C1"="T2"."C1")
  2 - filter("T1"."C2"=:B1)
  
 16 rows selected
  
 {code}
 h4.실제실행계획얻기
 실제 실행 계획을 얻는 방법은 DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용하는 것이다.
  
 {code}
 SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
 SQL> select * from table(dbms_xplan.display_cursor);
 {code}
  
 dbms_xplan.display_cursor는 v$session에 대한 접근 권한을 가지고 있어야 한다.
 테스트는 다음과 같다.
 {code}
 SYS@CX3WAS1 >var b1 varchar2(10);
 SYS@CX3WAS1 >exec :b1 := 'one';
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >select * from oracle.t1 t1, oracle.t2 t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
  C1 C2 C1 C
 ---------- ---- ---------- -
  10000 one 10000 x
  
 SYS@CX3WAS1 >select * from table(dbms_xplan.display_cursor);
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------
 SQL_ID 0mxyvwd9348p9, child number 0
 -------------------------------------
 select * from oracle.t1 t1, oracle.t2 t2 where t1.c1 = t2.c1 and t1.c2
 = :b1
  
 Plan hash value: 1838229974
  
 ---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 8 (100)| |
 |* 1 | HASH JOIN | | 5000 | 75000 | 8 (13)| 00:00:01 |
 |* 2 | TABLE ACCESS FULL| T1 | 5000 | 45000 | 4 (0)| 00:00:01 |
 | 3 | TABLE ACCESS FULL| T2 | 10000 | 60000 | 3 (0)| 00:00:01 |
 ---------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
  1 - access("T1"."C1"="T2"."C1")
  2 - filter("T1"."C2"=:B1)
  
 22 rows selected.
 {code}
  
 DBMS_XPLAN.DISPLAY_CURSOR 함수는 V$SESSION 의 PREV_SQL_ID컬럼과 PREV_CHILD_NUMBER컬럼 값을 이용하여 방금 전에 실행한 쿼리를 인식한다. 그러므로, 방금전 실행한 쿼리의 정보를 가져올 필요가 없다.
  
 즉, 실제로는 아래와 같은 방법을 사용한다.
 {code}
 SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
 SQL> col prev_sql_id new_value prev_sql_id
 SQL> col prev_child_number new_value prev_child_number
 SQL> select prev_sql_id, prev_child_number from v$session where sid = userenv('sid');
 SQL> select * from table( dbms_xplan.display_cursor ('&prev_sql_id' , '&prev_child_number'));
 {code}
  
 하지만, 이런 이유로 SET SERVEROUTPUT ON 명령을 수행하면 DBMS_XPLAN.DISPLAY_CURSOR 함수가 정상적으로 동작하지 않는다.
 {code}
 SQL> set serveroutput on
 SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
 SQL> select * from table(dbms_xplan.display_cursor);
 {code}
 실제 테스트 해보면 다음과 같이 출력됨을 확인 할 수 있다.
 {code}
 SYS@CX3WAS1 >set serveroutput on
 SYS@CX3WAS1 >select * from oracle.t1 t1, oracle.t2 t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
  
  C1 C2 C1 C
 ---------- ---- ---------- -
  10000 one 10000 x
  
 SYS@CX3WAS1 >select * from table(dbms_xplan.display_cursor);
  
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------
 SQL_ID 9babjv8yq8ru3, child number 0
  
 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
  
 NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
  Please verify value of SQL_ID and CHILD_NUMBER;
  It could also be that the plan is no longer in cursor cache (check v$sql_plan)
  
 8 rows selected
  
 {code}
  
 DBMS_XPLAN.DISPLAY_CURSOR함수의 가장 큰 장점은 실행 계획 뿐아니라 일량 정보 (Plan Statistics)도 같이 보여준다는 것이다. 쿼리 수행 시 GATHER_PLAN_STATISTICS 힌트를 부여하고 DBMS_XPLAN.DISPLAY_CURSOR 함수 호출 시 FORMAT 옵션에 "ALLSTATS LAST"값을 부여하면 된다.
 예제에서 SQL_ID와 CHILD_NUMBER값이 null인 것은 현재 세션에서 방금 실행한 쿼리라는 의미이다.
 {code}
 SQL> var b1 varchar2(10);
 SQL> exec :b1 := 'one';
 SQL> select /*+ gather_plan_statistics */ * from t1, t2
 where t1.c1 = t2.c1 and t1.c2 = :b1;
 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
 {code}
  
 테스트하면 다음과 같이 출력됨을 확인할 수 있다.
 {code}
 SYS@CX3WAS1 >var b1 varchar2(10);
 SYS@CX3WAS1 >exec :b1 := 'one';
  
 PL/SQL procedure successfully completed.
  
 SYS@CX3WAS1 >select /*+ gather_plan_statistics */ * from oracle.t1 t1, oracle.t2 t2
  2 where t1.c1 = t2.c1 and t1.c2 = :b1;
  
  C1 C2 C1 C
 ---------- ---- ---------- -
  10000 one 10000 x
  
 SYS@CX3WAS1 >select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
  
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------
 SQL_ID cnp2wr3gmdvgx, child number 0
 -------------------------------------
 select /*+ gather_plan_statistics */ * from oracle.t1 t1, oracle.t2 t2
 where t1.c1 = t2.c1 and t1.c2 = :b1
  
 Plan hash value: 1838229974
  
 ----------------------------------------------------------------------------------------------------------------
 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
 ----------------------------------------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 22 | | | |
 |* 1 | HASH JOIN | | 1 | 5000 | 1 |00:00:00.01 | 22 | 1088K| 1088K| 416K (0)|
 |* 2 | TABLE ACCESS FULL| T1 | 1 | 5000 | 1 |00:00:00.01 | 12 | | | |
 | 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.02 | 10 | | | |
 ----------------------------------------------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
  
  1 - access("T1"."C1"="T2"."C1")
  2 - filter("T1"."C2"=:B1)
  
  
 22 rows selected.
  
 {code}
  
 SQLID와 CHILD_NUMBER값을 지정하면 현재 라이브러리 캐시에 등록된 특정 쿼리를 지정할 수 있다.
  
 여기서 두가지 유의 사항이 있다.
 * SQL_ID만으로 특정 세션이 수행 중인 쿼리를 지정할 수 없다.
 동일한 SQL 문장이 여러 개의 차일드 커서를 가질 수 있고, 차일드 커서마다 실행계획이 다를 수 있기 때문에 SQL_ID와 CHILD_NUMBER를 합쳐야 한다.
 * GATHER_PLAN_STATISTICS 힌트를 부여하고 쿼리를 실행하면 Plan Statistics가 계산된다. 단, 그 결과는 쿼리가 끝난 이후에 저장된다. 즉, 쿼리가 실행 중이라면 DBMS_XPLAN.DISPLAY_CURSOR호출시 Plan Statistics정보를 얻을 수 없다.
  
 h4. Plan Statistics 얻기
  
 Plan Statistics가 어떤 정보를 포함하는지 보자.
 정보를 정확히 확인하기 위해 다음과 같이 정렬 작업이 발생하게 정렬 작업의 크기를 최소화하고 작업을 실행계획을 만들어 보자.
 {code}
 SYS@CX3WAS1 >alter session set workarea_size_policy=manual;
  
 Session altered.
  
 SYS@CX3WAS1 >alter session set sort_area_size = 0;
  
 Session altered.
  
 SYS@CX3WAS1 >select /*+ gather_plan_statistics leading(oracle.t1) use_hash(oracle.t2) */
  2 t1.c1, t2.c2 from oracle.t1 t1, oracle.t2 t2
  3 where t1.c1 = t2.c1
  4 and t1.c2 = 'many'
  5 order by t1.c1, t1.c2;
 ...
  9994 x
  9995 x
  9996 x
  9997 x
  9998 x
  9999 x
  
 9999 rows selected.
  
 SYS@CX3WAS1 >select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
  
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------
 SQL_ID 5yp2kdm32y34k, child number 0
 -------------------------------------
 select /*+ gather_plan_statistics leading(oracle.t1)
 use_hash(oracle.t2) */ t1.c1, t2.c2 from oracle.t1 t1, oracle.t2 t2
 where t1.c1 = t2.c1 and t1.c2 = 'many' order by t1.c1, t1.c2
  
 Plan hash value: 2097371403
  
 ---------------------------------------------------------------------------------------------------------------------------------------------
  
 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
  
 ---------------------------------------------------------------------------------------------------------------------------------------------
  
 | 0 | SELECT STATEMENT | | 1 | | 9999 |00:00:00.05 | 25 | 29 | 29 | | | | |
  
 | 1 | SORT ORDER BY | | 1 | 9999 | 9999 |00:00:00.05 | 25 | 29 | 29 | 234K| 234K| 193K (1)| 1024 |
  
 |* 2 | HASH JOIN | | 1 | 9999 | 9999 |00:00:00.03 | 22 | 16 | 16 | 1156K| 1156K| 492K (1)| 1024 |
  
 | 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.02 | 10 | 0 | 0 | | | | |
  
 |* 4 | TABLE ACCESS FULL| T1 | 1 | 9999 | 9999 |00:00:00.02 | 12 | 0 | 0 | | | | |
  
 ---------------------------------------------------------------------------------------------------------------------------------------------
  
  
 Predicate Information (identified by operation id):
  
 PLAN_TABLE_OUTPUT
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
  2 - access("T1"."C1"="T2"."C1")
  4 - filter("T1"."C2"='many')
  
  
 24 rows selected.
  
 SYS@CX3WAS1 >
 {code}
  
 항목의 의미를 알아보자
 || 항목 || 설명 ||
 | Starts | 현재 단계의 오퍼레이션이 호출된 회수, Nested Loops Join인 경우 후행테이블을 접근하는 만큼 Start값이 증가한다. |
 | E-Rows | 옵티마이져가 실행 계획을 만들때 예상한 row 건수 |
 | A-Rows | 실제 실행한 후 계산한 row 건수 E-Rows와 값이 비슷할 수록 옵티마이져의 예상이 정확하다는 것을 의미한다. |
 | Buffer | Logical Reads를 의미한다. |
 | Reads | Physical Reads를 의미한다. |
 | Writes | Physical Writes를 의미한다. |
 Buffer와 Reads와 Writes는 트리 구조에서 하위 레벨의 오퍼레이션에서 발생한 값을 합친 누적치 이므로 분석시 참고해야 한다.
 부가 정보에 대한 설명은 다음과 같다.
 || 항목 || 설명 ||
 | 0Mem | Optimal 소트에 필요할 것으로 예상되는 정렬 작업 영역의 크기 |
 | 1Mem | One Pass 소트에 필요할 것으로 예상되는 정렬 작업 영역의 크기 |
 | Used-Mem | 실제로 사용된 정렬 작업 영역의 크기 (0) Optimal 소트 (1) Onepass (2) Multipass |
 \[참고\]
 * Optimal 소트 : 메모리에서 정렬이 완료되는 것을 의미한다.
 * One Pass 소트 : 읽은 데이터가 너무 커서 메모리에 더 이상 수용할 수 없을때 나타나는 것으로 데이터를 읽어 들이면서 정렬을 수행하다가 가용메모리가 차게 되면 정렬된 데이터집합을 디스크로 덤프하게되는데 이러한 과정은 모든 입력 데이터를 처리할 때까지 반복하여 run(집합)을 만든다. 결국, 몇 개의 정렬된 run(집합)이 남게 되고 이것을 단일 데이터 집합으로 머지하는데, 정렬된 모든 run을 전부 읽어 들일 정도로 메모리가 충분하여 한번에 로딩하면 이것을 One Pass라고 한다.
 * Multi Pass 소트 : onepass가 동작원리는 같은데 run이 한번에 메모리에 로딩하지 못하는 결국 반복하는 정렬 작업을 말한다. 이때 정렬된 정보를 반복해서 읽어야 하는 횟수를 merge Pass라고 한다.
  
 위에서 설명한 정렬 작업 영역의 크기에 대한 정보는 엄격하게 말하면 Plan Statistics 정보가 아니다. Plan Statistics정보는 V$SQL_PLAN_STATISTICS 에서 얻는 정보를 의미한다.
 {code}
 SQL> desc v$sql_plan_statistics
 {code}
 부가정보의 정렬 작업에 사용된 정보는 V$SQL_WORKAREA 에서 얻은 정보이다.
 {code}
 SQL> desc v$sql_workarea
 {code}
 실제로 위에서 실행한 쿼리에 대한 정보를 V$SQL_WORKAREA에서 검색해보면 DBMS_XPLAN.DISPLAY_CURSOR함수와 동일한 결과를 얻을 수 있다.
 {code}
 SQL> select * from v$sql_workarea where sql_id = 'XXX';
 {code}
  
 h3. 1.5.7 DBMS_SQL
 동적인 SQL을 생성할 때 사용할 수 있는 패키지이다. 현재는 Native Dynamic SQL을 사용하지만, 그 이전에는 DBMS_SQL를 사용했다.
 하지만, 필자의 경우 V$SQL_SHARED_CURSOR를 조회할때에는 이 패키지를 사용한다. V$SQL_SHARED_CURSOR는 많은 컬럼이 Y,N값을 가지기 때문에 가독성이 떨어지기 때문이다.
 DBMS_SQL패키지를 이용하여 컬럼 값이 Y인 컬럼만 출력하게 하면 가독성이 높혀진다.
 {code}
 SQL> set serveroutput on
 SQL> declare
 c number;
 col_cnt number;
 col_rec dbms_sql.desc_tab;
 col_value varchar2(4000);
 ret_val number;
 begin
 c := dbms_sql.open_cursor;
 dbms_sql.parse(c, 'select q.sql_text, s.* from v$sql_shared_cursor s, v$sql q
 where s.sql_id = q.sql_id and s.child_number = q.child_number
 and s.sql_id = ''&sql_id''', dbms_sql.native);
 dbms_sql.describe_columns(c, col_cnt, col_rec);
  
 for idx in 1 .. col_cnt loop
 dbms_sql.define_column(c, idx, col_value, 4000);
 end loop;
  
 ret_val := dbms_sql.execute(c);
  
 while ( dbms_sql.fetch_rows(c) > 0 ) loop
 for idx in 1 .. col_cnt loop
 dbms_sql.column_value(c, idx, col_value);
 if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
  'CHILD_NUMBER','SQL_TEXT') then
 dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
 elsif col_value = 'Y' then
 dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
 end if;
 end loop;
  
 dbms_output.put_line('----------------------------------------------------------');
 end loop;
 dbms_sql.close_cursor(c);
 end;
 /
 {code}
 톰 카이트(Tom Kyte)가 작성한 PRINT_TABLE이라는 프로시져도 DBMS_SQL을 사용하고 있다.
 {code}
 create or replace procedure print_table(p_query in varchar2)
 AUTHID CURRENT_USER
 is
 l_theCursor integer default dbms_sql.open_cursor;
 l_columnValue varchar2(4000);
 l_status integer;
 l_descTbl dbms_sql.desc_tab;
 l_colCnt number;
 begin
 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native);
 dbms_sql.describe_columns (l_theCursor, l_colCnt, l_descTbl);
  
 for I int 1 .. l_colCnt loop
 dbms_sql.define_column(l_theCursor, I, l_columnValue, 4000);
 end loop;
  
 l_status := dbms_sql.execute(l_theCursor);
 while(dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 for i in 1 .. l_colCnt loop
 dbms_sql.column_value(l_theCursor, I, l_columnValue);
 dbms_output.put_line(rpad(l_descTbl(i).col_name, 30) || ': ' || l_columnValue);
 end loop;
 dbms_output.put_line('---------------------------------------');
 end loop;
 dbms_sql.close_cursor(l_theCursor);
 exception
 when others then
 raise;
 end;
 /
 {code}
  
 h3. 1.5.8 DBMS_METADATA
 특정 오브젝트의 DDL을 얻고자 하는 경우 이 패키지를 이용한다.
 {code}
 SQL> set long 100000
 SQL> select dbms_metadata.get_ddl( object_type => 'TABLE', name => 'T1')
 from dual;
 {code}
 테스트 하면 다음과 같이 출력된다.
 {code}
 ORACLE@CX3WAS1 >set long 100000
 ORACLE@CX3WAS1 >select dbms_metadata.get_ddl( object_type => 'TABLE', name => 'T1')
 from dual;
  2
  
 DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'T1')
 --------------------------------------------------------------------------------
  
  CREATE TABLE "ORACLE"."T1"
  ( "C1" NUMBER,
  "C2" VARCHAR2(4)
  ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
 LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MA
 XEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GRO
 UPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
 FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USER1"
 {code}
 DBMS_METADATA.SET_TRANSFORM_PARAM 프로시져를 이용하면 불필요한 정보를 제거할 수 있다.
 {code}
 SQL> exec dbms_metadata.st_transform_param ( dbms_metadata.session_transform , 'STORAGE', false);
 SQL> exec dbms_metadata.st_transform_param ( dbms_metadata.session_transform , 'TABLESPACE', false);
 SQL> exec dbms_metadata.st_transform_param ( dbms_metadata.session_transform , 'SEGMENT_ATTRIBUTES', false);
 {code}
  
 h3. 1.5.9 DBMS_ROWID
 ROWID값으로부터 Object Id, File #, Block #, Row #를 얻고자 하는 경우 사용하는 패키지 이다. 반대로 Object Id, File #, Block #, Row #를 사용하여 ROWID를 만들 수도 있다.
 다음과 같이 현재 경합이 발생하는 rowid를 먼저 추출해 본다.
 {code}
 SQL> col obj_no new_value obj_no
 SQL> col file_no new_value file_no
 SQL> col block_no new_value block_no
 SQL> col row_no new_value row_no
 SQL> select sid, row_wait_obj# as obj_no, row_wait_file# as file_no,
 row_wait_block# as block_no, row_wait_row# as row_no
 from v$session
 where event = 'enq: TX - row lock contention';
 {code}
 다음과 같이 rowid값을 만들 수 있다.
  
 \[참고\]
 rowid_type 1값은 DBMS_ROWID.ROWID_TYPE_EXTENDED을 말한다.
 다른값으로 DBMS_ROWID.ROWID_TYPE_RESTRICTED가 있고 이 값은 0이다.
 Extended RowID는 8.Xi버전 이상에서만 사용된다.
  
 {code}
 SQL> col rid new_value rid
 SQL> select dbms_rowid.rowid_create(rowid_type => 1,
 object_number => &obj_no, relative_fno => &file_no,
 block_number => &block_no, row_number => &row_no) as rid
 from dual;
 {code}
 위에서 추출한 값을 기반으로 어떤 로우에 해당하는지 검색할 수 있다.
 {code}
 SQL> select * from t1 where rowid = '&rid';
 {code}
 반대로 rowid값으로부터 Object Id, File #, Block #, Row #를 얻을 수도 있다.
 {code}
 SQL> select
 dbms_rowid.rowid_type('&rid') as rtype,
 dbms_rowid.rowid_object('&rid') as obj_no,
 dbms_rowid.rowid_relative_fno('&rid') as file_no,
 dbms_rowid.rowid_block_number('&rid') as block_no,
 dbms_rowid.rowid_row_number('&rid') as row_no
 from dual;
 {code}
  
 테스트는 다음과 같다.
 {code}
 SYS@CX3WAS1 >col obj_no new_value obj_no
 SYS@CX3WAS1 >col file_no new_value file_no
 SYS@CX3WAS1 >col block_no new_value block_no
 SYS@CX3WAS1 >col row_no new_value row_no
 SYS@CX3WAS1 >select sid, row_wait_obj# as obj_no, row_wait_file# as file_no,
 row_wait_block# as block_no, row_wait_row# as row_no
 from v$session
 where event = 'enq: TX - row lock contention';
  
  SID OBJ_NO FILE_NO BLOCK_NO ROW_NO
 ---------- ---------- ---------- ---------- ----------
  1149 65277 7 83 0
  
 SYS@CX3WAS1 >col rid new_value rid
 SYS@CX3WAS1 >select dbms_rowid.rowid_create(rowid_type => 1,
 object_number => &obj_no, relative_fno => &file_no,
 block_number => &block_no, row_number => &row_no) as rid
 from dual;
 old 2: object_number => &obj_no, relative_fno => &file_no,
 new 2: object_number => 65277, relative_fno => 7,
 old 3: block_number => &block_no, row_number => &row_no) as rid
 new 3: block_number => 83, row_number => 0) as rid
  
 RID
 ------------------
 AAAP79AAHAAAABTAAA
  
 SYS@CX3WAS1 >select * from oracle.t1 where rowid = '&rid';
 old 1: select * from oracle.t1 where rowid = '&rid'
 new 1: select * from oracle.t1 where rowid = 'AAAP79AAHAAAABTAAA'
  
  C1 C2
 ---------- ----
  1 many
  
 SYS@CX3WAS1 >select
 dbms_rowid.rowid_type('&rid') as rtype,
 dbms_rowid.rowid_object('&rid') as obj_no,
 dbms_rowid.rowid_relative_fno('&rid') as file_no,
 dbms_rowid.rowid_block_number('&rid') as block_no,
 dbms_rowid.rowid_row_number('&rid') as row_no
 from dual;
 old 2: dbms_rowid.rowid_type('&rid') as rtype,
 new 2: dbms_rowid.rowid_type('AAAP79AAHAAAABTAAA') as rtype,
 old 3: dbms_rowid.rowid_object('&rid') as obj_no,
 new 3: dbms_rowid.rowid_object('AAAP79AAHAAAABTAAA') as obj_no,
 old 4: dbms_rowid.rowid_relative_fno('&rid') as file_no,
 new 4: dbms_rowid.rowid_relative_fno('AAAP79AAHAAAABTAAA') as file_no,
 old 5: dbms_rowid.rowid_block_number('&rid') as block_no,
 new 5: dbms_rowid.rowid_block_number('AAAP79AAHAAAABTAAA') as block_no,
 old 6: dbms_rowid.rowid_row_number('&rid') as row_no
 new 6: dbms_rowid.rowid_row_number('AAAP79AAHAAAABTAAA') as row_no
  
  RTYPE OBJ_NO FILE_NO BLOCK_NO ROW_NO
 ---------- ---------- ---------- ---------- ----------
  1 65277 7 83 0
  
 SYS@CX3WAS1 >
 {code}
  
 h3. 1.5.10 UTL_FILE
 오라클 서버에 존재하는 파일을 읽고 쓰기 위해 이 패키지를 사용한다. 이 기능을 사용하면 오라클이 제공하는 각종 트레이스 파일과 로그 파일을 SQL문을 이용해서 접근할 수 있다.
 먼저 디렉토리 객체를 만든다.
 {code}
 SQL> col dir_name new_value dir_name
 SQL> select value as dir_name
 from v$parameter
 where name = 'user_dump_dest';
 SQL> create or replace directory my_dump_dest as '&dir_name';
 {code}
 필요한 트레이스 파일의 이름을 얻는다.
 {code}
 SQL> col tracefile_name new_value tracefile_name
 SQL> select
 i.value || '_ora_' || p.spid || decode(t.value,null, '','_'||t.value)||'.trc' as tracefile_name
 from v$process p, v$session s,
 (select value from v$parameter where name = 'instance_name') i,
 (select value from v$parameter where name = 'tracefile_identifier') t
 where p.addr = s.paddr and s.sid = userenv('sid');
 {code}
 이제 트레이스 파일을 열고 현재 사이즈를 구해보자.
 {code}
 SQL> var cur_pos number;
 SQL> declare
 file_no utl_file.file_type;
 v_exists boolean;
 v_blk_size number;
 begin
 file_no := utl_file.fopen( 'MY_DUMP_DEST', '&tracefile_name', 'R', 32767);
 utl_file.fgetattr('MY_DUMP_DEST', '&tracefile_name',v_exists, :cur_pos, v_blk_size);
  // 파일의 속성정보를 가져온다. 존재하는지 크기는 얼마인지, 블럭 사이즈는 어떻게 되는지.
 utl_file.fclose(file_no);
 end;
 /
 {code}
 UTL_FILE.GET_LINE 프로시져를 이용해서 트레이스 파일을 한줄 씩 읽으면서 db file sequential read 대기 이벤트의 발생 횟수를 카운트 하는 프로시져를 생성해 보자.
 {code}
 SQL> set serveroutput on
 SQL> declare
 file_no utl_file.file_type;
 v_len number;
 v_line varchar2(32767);
 v_count number := 0;
 begin
 file_no := utl_file.fopen('MY_DUMP_DEST', '&tracefile_name','R',32767);
 utl_file.fseek(file_no, :cur_pos);
 loop
 begin
 utl_file.get_line(file_no, v_line);
 if v_line like '%db file sequential read %'
 then v_count := v_count +1;
 end if;
 exception
 when no_data_found then exit;
 end;
 end loop;
 utl_file.fclose(file_no);
 dbms_output.put_line('count of db file sequential read = ' || v_count);
 end;
 /
 {code}
  
 h3. 1.5.11 UTL_RAW
 RAW 타입의 값을 분석해야 할 경우 사용하는 패키지이다.
 {code}
 SQL> select utl_raw.cast_to_number('C102') from dual;
 SQL> select utl_raw.cast_to_varchar2('6D616E79') from dual;
 {code}
 다음과 같이 응용도 가능하다.
 {code}
 SQL> select column_name, data_type,
 decode(data_type, 'NUMBER', utl_raw.cast_to_number(low_value)||'', 'VARCHAR2', utl_raw.cast_to_varchar2(low_value), low_value||'') as low_value,
 decode(data_type, 'NUMBER', utl_raw.cast_to_number(high_value)||'', 'VARCHAR2', utl_raw.cast_to_varchar2(high_value), high_value||'') as high_value
 from user_tab_cols where table_name = 'T1';
 {code}
  
 h3. 1.5.12 DBMS_STAT
 이 패키지는 통계 정보를 수집하고 제어하는 목적으로 사용된다. 이 패키지의 CONVERT_RAW_VALUE 프로시져를 사용하면 UTL_RAW 의 CAST_TO_[TYPE]함수와 거의 동일한 작업을 할 수 있다. 하지만, 프로시져이기 때문에 함수 형식으로 사용하려면 아래와 같이 사용자 정의 함수를 만들어야 한다.
 {code}
 SQL> create or replace function convert_me( p_value in raw, p_type in varchar2)
 return varchar2
 is
 v_number number;
 v_varchar2 varchar2(4000);
 begin
 if (p_type = 'NUMBER') then
 dbms_stats.convert_raw_value(p_value, v_number);
 return v_number||'';
 elsif (p_type = 'VARCHAR2') then
 dbms_stats.convert_raw_value(p_value, v_varchar2);
 return v_varchar2;
 else
 return p_value || '';
 end if;
 end;
 /
 {code}
 다음과 같이 사용이 가능하다.
 {code}
 SQL> select convert_me('C102', 'NUMBER') from dual;
 SQL> select convert_me('6D616E79', 'VARCHAR2') from dual;
 SQL> select column_name, data_type,
 convert_me(low_value, data_type) as low_value,
 convert_me(high_value, data_type) as high_value
 from user_tab_cols where table_name = 'T1';
 {code}
  
 h3. 1.5.13 TO_DEC, TO_HEX
 숫자의 진수 변환을 수행하는 가장 쉬운 방법으로 TO_CHAR, TO_NUMBER함수를 사용하는 것이다.
 {code}
 SQL> select to_char(100, 'xxxxxxxxxx') from dual;
 SQL> select to_number('64','xxxxxxxxxx') from dual;
 {code}
 진수 변환을 위해 톰 카이트의 라이브러리를 이용하는 것도 좋다.
 {code}
 -- 진수 변환을 위한 핵심 함수
 SQL> create or replace function to_base(p_dec in number, p_base in number)
 return varchar2
 is
 l_str varchar2(255) default NULL;
 l_num number default p_dec;
 l_hex varchar2(16) default '0123456789ABCDEF';
 begin
 if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
 raise PROGRAM_ERROR;
 end if;
 loop
 l_str := substr(l_hex, mod(l_num, p_base)+1, 1) || l_str;
 l_num := trunc( l_num/p_base);
 exit when (l_num = 0);
 end loop;
 return l_str;
 end to_base;
 /
 -- 10진수로 변환
 SQL> create or replace function to_dec(p_str in varchar2, p_from_base in number default 16) return number
 is
 l_num number default 0;
 l_hex varchar2(16) default '0123456789ABCDEF';
 begin
 for i in 1 .. length(p_str) loop
 l_num := l_num*p_from_base + instr(l_hex, upper(substr(p_str,i,1))) -1;
 end loop;
 return l_num;
 end to_dec;
 /
 -- 16진수로 변환
 SQL> create or replace function to_hex(p_dec in number) return varchar2
 is
 begin
 return to_base(p_dec, 16);
 end to_hex;
 /
 --2진수로 변환
 SQL> create or replace function to_bin(p_dec in number) return varchar2
 is
 begin
 return to_base(p_dec,2);
 end to_bin;
 /
 -- 8진수로 변환
 SQL> create or replace function to_oct(p_dec in number) return varchar2
 is
 begin
 return to_base(p_dec, 8);
 end to_oct;
 /
 {code}
 TO_HEX, TO_DEC 함수를 이용하면 TO_CHAR, TO_NUMBER함수를 이용한 것과 동일한 진수 변환을 진행할 수 있다.
 {code}
 SQL> select to_hex(100) from dual;
 SQL> select to_dec('64') from dual;
 {code}