오라클이 제공하는 모든 관리 기능들은 반드시 관련된 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
다음과 같은 작업을 할 수 있다.
특정 작업의 소요 시간을 알기위해 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
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.
이 패키지는 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
이 패키지를 사용하면 하나의 인스턴스에서 여러 세션간의 작업 순서를 제어할 수 있다.
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
이 패키지는 랜덤 값을 생성하는데 사용한다.
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.
각 세션별로 적절한 모듈명 (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
이 패키지를 사용하면 실행계획과 관련된 정보를 빠르고 정확하게 얻을 수 있다.
테스트를 하기 위한 데이터는 다음과 같이 작업 한다.
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.
h4.예상실행계획 얻기
예상 실행 계획은 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
h4.실제실행계획얻기
실제 실행 계획을 얻는 방법은 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값을 지정하면 현재 라이브러리 캐시에 등록된 특정 쿼리를 지정할 수 있다.
여기서 두가지 유의 사항이 있다.
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 |
[참고]
위에서 설명한 정렬 작업 영역의 크기에 대한 정보는 엄격하게 말하면 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';
동적인 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;
/
특정 오브젝트의 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);
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 >
오라클 서버에 존재하는 파일을 읽고 쓰기 위해 이 패키지를 사용한다. 이 기능을 사용하면 오라클이 제공하는 각종 트레이스 파일과 로그 파일을 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;
/
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';
이 패키지는 통계 정보를 수집하고 제어하는 목적으로 사용된다. 이 패키지의 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';
숫자의 진수 변환을 수행하는 가장 쉬운 방법으로 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;