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

PLSQL 패키지

You are viewing an old version (v. 16) of this page.
The latest version is v. 17, last edited on 3월 10, 2012 (view differences | )
<< View previous version | view page history | view next version >>



1.5 PL/SQL 패키지

오라클이 제공하는 모든 관리 기능들은 반드시 관련된 PL/SQL 패키지와 함께 제공된다. 그러므로, PL/SQL 패키지의 사용법을 아는 것은 많은 도움이 된다.

테스트 서버

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

1.5.2 DBMS_UTILITY

다음과 같은 작업을 할 수 있다.

시간값 얻기

특정 작업의 소요 시간을 알기위해 DBMS_UTILITY.GET_TIME 함수를 사용할 수 있다.

단위: 1/100초
사용 방법: 절대값은 의미없고, 특정 기간의 차이 값을 통해 소요 시간을 구할 수 있다.
예제는 다음과 같다.

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;

test

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

V$TIMER을 읽어서 DBMS_UTILITY.GET_TIME과 같은 일을 할 수 있다.

일반계정인 경우

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
SYS@CX3WAS1 >desc V$TIMER;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 HSECS                                                                      NUMBER

SYS@CX3WAS1 >select hsecs from V$TIMER;

     HSECS
----------
2806856002

DBA 값 얻기

16진수나 10진수로 표현된 DBA(Data Block Address)값을 파일번호+블록 번호로 변환하는 방법을 알아보자. 이때, DBMS_UTILITY의 DATA_BLOCK_ADDRESS_FILE 함수와 DATA_BLOCK_ADDRESS_BLOCK 함수를 사용한다.
테스트 테이블은 다음과 같이 만들었다.

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.

id 정보를 확인하고 트레이스를 생성한다. (sys)

 
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'

trace file에서 블록 정보를 확인한다.

 
----- begin tree dump
leaf: 0x1c0004b 29360203 (0: nrow: 100 rrow: 100)
----- end tree dump

여기서 [0x1c0004b]은 DBA의 16진수값이며, [29360203]은 DBA의 10 진수값이다.
REGEXP_REPLACE함수를 사용하면 위이 정보로 10진수의 DBA값을 추출할 수 있다.

 
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;

하지만 tpack이 없어서 잘 안된다.

이와 같이 추출한 DBA값을 DBMS_UTILITY패키지를 이용해서 변환 할 수 있다.

 
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;

이와 같이 파일 번호와 블록 번호를 얻은 후 블록 덤프를 수행하거나 기타 필요한 작업을 수행하면 된다.

 
SQL> alter system dump datafile &file_no block &block_no;

테스트는 다음과 같다.

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.

1.5.2 DBMS_LOCK

이 패키지는 User Lock을 구현하기 위한 목적으로 제공되는 패키지 이다. 하지만 여기서는 이 목적 보다는 sleep을 사용하기 위해 많이 사용한다.

 
SQL> exec dbms_lock.sleep(0.1); 
-- 0.1초 지연한다. 

다음과 같이 함수로 구현하여 쿼리 실행 시 사용이 가능하다.

 
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;

테스트는 다음과 같다.

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

1.5.3 DBMS_PIPE

이 패키지를 사용하면 하나의 인스턴스에서 여러 세션간의 작업 순서를 제어할 수 있다.
DBMS_PIPE 패키지중에 RECEIVE_MESSAGE를 사용하면 누군가가 메시지를 보낼때까지 기다리게 하는 것이 가능하다.

 
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;
/

메시지를 보내는 함수를 만들어 보자.

  
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;
/

다음과 같이 실행하면 된다.

 
--- session #1
SQL> exec dbms_application_info.set_client_info('session1');
SQL> exec wait_for_signal;

--- session #2
SQL> exec signal;

singal 실행 후 session 1에 작업이 가능하게 된다.

 
--- session #1
SQL> select count(*) from all_objects;

session 1 입장에서의 테스트 내용은 다음과 같다.

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

1.5.4 DBMS_RANDOM

이 패키지는 랜덤 값을 생성하는데 사용한다.

  
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;

테스트는 다음과 같다.

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.

1.5.5 DBMS_APPLICATION_INFO

각 세션별로 적절한 모듈명 (V$SESSION.MODULE) 또는 Client 정보 (V$SESSION.CLIENT_INFO)을 부여하여 테스트의 효율성을 높힐 때 사용한다.

 
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';

테스트는 다음과 같다.

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

1.5.6 DBMS_XPLAN

이 패키지를 사용하면 실행계획과 관련된 정보를 빠르고 정확하게 얻을 수 있다.
테스트를 하기 위한 데이터는 다음과 같이 작업 한다.

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.

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.

예상실행계획 얻기

예상 실행 계획은 DBMS_XPLAN.DISPLAY 함수를 통해 얻을 수 있다.

  
SQL> explain plan for 
select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
SQL>select * from table(dbms_xplan.display);

실제 테스트 하면 다음과 같이 출력된다.

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

실제실행계획얻기

실제 실행 계획을 얻는 방법은 DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용하는 것이다.

 
SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
SQL> select * from table(dbms_xplan.display_cursor);

dbms_xplan.display_cursor는 v$session에 대한 접근 권한을 가지고 있어야 한다.
테스트는 다음과 같다.

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.

DBMS_XPLAN.DISPLAY_CURSOR 함수는 V$SESSION 의 PREV_SQL_ID컬럼과 PREV_CHILD_NUMBER컬럼 값을 이용하여 방금 전에 실행한 쿼리를 인식한다. 그러므로, 방금전 실행한 쿼리의 정보를 가져올 필요가 없다.

즉, 실제로는 아래와 같은 방법을 사용한다.

 
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'));

하지만, 이런 이유로 SET SERVEROUTPUT ON 명령을 수행하면 DBMS_XPLAN.DISPLAY_CURSOR 함수가 정상적으로 동작하지 않는다.

 
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);

실제 테스트 해보면 다음과 같이 출력됨을 확인 할 수 있다.

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

DBMS_XPLAN.DISPLAY_CURSOR함수의 가장 큰 장점은 실행 계획 뿐아니라 일량 정보 (Plan Statistics)도 같이 보여준다는 것이다. 쿼리 수행 시 GATHER_PLAN_STATISTICS 힌트를 부여하고 DBMS_XPLAN.DISPLAY_CURSOR 함수 호출 시 FORMAT 옵션에 "ALLSTATS LAST"값을 부여하면 된다.
예제에서 SQL_ID와 CHILD_NUMBER값이 null인 것은 현재 세션에서 방금 실행한 쿼리라는 의미이다.

 
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'));

테스트하면 다음과 같이 출력됨을 확인할 수 있다.

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.

SQLID와 CHILD_NUMBER값을 지정하면 현재 라이브러리 캐시에 등록된 특정 쿼리를 지정할 수 있다.

여기서 두가지 유의 사항이 있다.

  • SQL_ID만으로 특정 세션이 수행 중인 쿼리를 지정할 수 없다.
    동일한 SQL 문장이 여러 개의 차일드 커서를 가질 수 있고, 차일드 커서마다 실행계획이 다를 수 있기 때문에 SQL_ID와 CHILD_NUMBER를 합쳐야 한다.
  • GATHER_PLAN_STATISTICS 힌트를 부여하고 쿼리를 실행하면 Plan Statistics가 계산된다. 단, 그 결과는 쿼리가 끝난 이후에 저장된다. 즉, 쿼리가 실행 중이라면 DBMS_XPLAN.DISPLAY_CURSOR호출시 Plan Statistics정보를 얻을 수 없다.

Plan Statistics 얻기

Plan Statistics가 어떤 정보를 포함하는지 보자.
정보를 정확히 확인하기 위해 다음과 같이 정렬 작업이 발생하게 정렬 작업의 크기를 최소화하고 작업을 실행계획을 만들어 보자.

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 >

항목의 의미를 알아보자

항목 설명
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 에서 얻는 정보를 의미한다.

 
SQL> desc v$sql_plan_statistics

부가정보의 정렬 작업에 사용된 정보는 V$SQL_WORKAREA 에서 얻은 정보이다.

 
SQL> desc v$sql_workarea

실제로 위에서 실행한 쿼리에 대한 정보를 V$SQL_WORKAREA에서 검색해보면 DBMS_XPLAN.DISPLAY_CURSOR함수와 동일한 결과를 얻을 수 있다.

 
SQL> select * from v$sql_workarea where sql_id = 'XXX';

1.5.7 DBMS_SQL

동적인 SQL을 생성할 때 사용할 수 있는 패키지이다. 현재는 Native Dynamic SQL을 사용하지만, 그 이전에는 DBMS_SQL를 사용했다.
하지만, 필자의 경우 V$SQL_SHARED_CURSOR를 조회할때에는 이 패키지를 사용한다. V$SQL_SHARED_CURSOR는 많은 컬럼이 Y,N값을 가지기 때문에 가독성이 떨어지기 때문이다.
DBMS_SQL패키지를 이용하여 컬럼 값이 Y인 컬럼만 출력하게 하면 가독성이 높혀진다.

 
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;
/

톰 카이트(Tom Kyte)가 작성한 PRINT_TABLE이라는 프로시져도 DBMS_SQL을 사용하고 있다.

 
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;
/

1.5.8 DBMS_METADATA

특정 오브젝트의 DDL을 얻고자 하는 경우 이 패키지를 이용한다.

 
SQL> set long 100000
SQL> select dbms_metadata.get_ddl( object_type => 'TABLE', name => 'T1')
from dual;

테스트 하면 다음과 같이 출력된다.

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"

DBMS_METADATA.SET_TRANSFORM_PARAM 프로시져를 이용하면 불필요한 정보를 제거할 수 있다.

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);

1.5.9 DBMS_ROWID

ROWID값으로부터 Object Id, File #, Block #, Row #를 얻고자 하는 경우 사용하는 패키지 이다. 반대로 Object Id, File #, Block #, Row #를 사용하여 ROWID를 만들 수도 있다.
다음과 같이 현재 경합이 발생하는 rowid를 먼저 추출해 본다.

 
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';

다음과 같이 rowid값을 만들 수 있다.

[참고]
rowid_type 1값은 DBMS_ROWID.ROWID_TYPE_EXTENDED을 말한다.
다른값으로 DBMS_ROWID.ROWID_TYPE_RESTRICTED가 있고 이 값은 0이다.
Extended RowID는 8.Xi버전 이상에서만 사용된다.

 
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;

위에서 추출한 값을 기반으로 어떤 로우에 해당하는지 검색할 수 있다.

 
SQL> select * from t1 where rowid = '&rid';

반대로 rowid값으로부터 Object Id, File #, Block #, Row #를 얻을 수도 있다.

 
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;

테스트는 다음과 같다.

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 >

1.5.10 UTL_FILE

오라클 서버에 존재하는 파일을 읽고 쓰기 위해 이 패키지를 사용한다. 이 기능을 사용하면 오라클이 제공하는 각종 트레이스 파일과 로그 파일을 SQL문을 이용해서 접근할 수 있다.
먼저 디렉토리 객체를 만든다.

 
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';

필요한 트레이스 파일의 이름을 얻는다.

 
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');

이제 트레이스 파일을 열고 현재 사이즈를 구해보자.

 
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;
/

UTL_FILE.GET_LINE 프로시져를 이용해서 트레이스 파일을 한줄 씩 읽으면서 db file sequential read 대기 이벤트의 발생 횟수를 카운트 하는 프로시져를 생성해 보자.

 
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;
/

1.5.11 UTL_RAW

RAW 타입의 값을 분석해야 할 경우 사용하는 패키지이다.

 
SQL> select utl_raw.cast_to_number('C102') from dual;
SQL> select utl_raw.cast_to_varchar2('6D616E79') from dual;

다음과 같이 응용도 가능하다.

 
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';

1.5.12 DBMS_STAT

이 패키지는 통계 정보를 수집하고 제어하는 목적으로 사용된다. 이 패키지의 CONVERT_RAW_VALUE 프로시져를 사용하면 UTL_RAW 의 CAST_TO_[TYPE]함수와 거의 동일한 작업을 할 수 있다. 하지만, 프로시져이기 때문에 함수 형식으로 사용하려면 아래와 같이 사용자 정의 함수를 만들어야 한다.

 
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;
/

다음과 같이 사용이 가능하다.

 
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';

1.5.13 TO_DEC, TO_HEX

숫자의 진수 변환을 수행하는 가장 쉬운 방법으로 TO_CHAR, TO_NUMBER함수를 사용하는 것이다.

 
SQL> select to_char(100, 'xxxxxxxxxx') from dual;
SQL> select to_number('64','xxxxxxxxxx') from dual;

진수 변환을 위해 톰 카이트의 라이브러리를 이용하는 것도 좋다.

 
-- 진수 변환을 위한 핵심 함수 
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;
/

TO_HEX, TO_DEC 함수를 이용하면 TO_CHAR, TO_NUMBER함수를 이용한 것과 동일한 진수 변환을 진행할 수 있다.

 
SQL> select to_hex(100) from dual;
SQL> select to_dec('64') from dual;

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.