태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'table function'에 해당되는 글 3건

  1. 2009.07.01 Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - Trace 파일을 SQL로 읽기 (3)
  2. 2009.01.19 Table Function과 Join (4)
  3. 2008.12.18 IN (...) 절에 Item을 무제한으로 넣고 싶다. (2)

Oracle 성능 Troubleshooting을 위해 꼭 알아야 할 프로그래밍 지식 - Trace 파일을 SQL로 읽기

오라클 2009.07.01 12:39
제목에서는 "꼭"이라고 했지만, 더 정확한 표현은 "알고 있으면 유용한" 정도가 더 맞겠다. 제목에 낚이지 말기를.

Oracle이 제공하는 다양한(너무 다양해서 다 알기조차 힘든) Trace/Dump 파일은 성능 Troubleshooting을 하고자 하는 사람들에게는 축복같은 존재이다. 적어도 엔터프라이즈 환경의 제품이라면 이 정도의 성능 정보를 제공해야 한다. 우리는 블랙박스가 아니라 화이트박스를 필요로 한다. 적어도 나는 세탁기는 블랙박스처럼 잘 동작하기를 원하지만, 오라클만큼은 화이트박스처럼 다루고 싶은 사람이다.

이런 유용한 각종 Trace/Dump 파일을 SQL을 이용해서 불러 들이고, SQL을 이용해서 분석하는 것이 대단히 유용할 때가 많다. Unix 환경이라면 Shell Script같은 것을 이용할 수 있지만, SQL이 제공하는 강력한 분석 기능(집계 함수, 분석 함수, 계층 쿼리 등)을 이용할 수 있다면 금상 첨화일 것이다. 바로 내가 종종 이용하는 방법이기도 하고...

이 방법을 이해하려면 우선 Table Function과 Pipelined Table Function의 개념을 이해할 필요가 있다. 아래와 같이 Table Function을 이용하면 PL/SQL Collection을 마치 RDB의 Table 처럼 사용할 수 있다.

UKJA@ukja116> create or replace type varchar2_array as table of varchar2(4000);
  2  /

Type created.

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116> create or replace function get_array(p_start number, p_end number)
  2  return varchar2_array
  3  is
  4     v_return        varchar2_array := varchar2_array();
  5  begin
  6     v_return.extend(p_end-p_start+1);
  7     for idx in p_start .. p_end loop
  8             v_return(idx-p_start+1) := idx || 'th value';
  9     end loop;
 10
 11     return v_return;
 12  end;
 13  /

Function created.

Elapsed: 00:00:00.04
UKJA@ukja116>
UKJA@ukja116> select * from table(get_array(100, 112));

COLUMN_VALUE
--------------------------------------------------------------------------------
100th value
101th value
102th value
103th value
104th value
105th value
106th value
107th value
108th value
109th value
110th value
111th value
112th value

13 rows selected.

Elapsed: 00:00:00.03

Collection을 Return하는 함수에 pipelined 예약어를 사용하면 Collection을 Pipe처럼 취급해서 개별 아이템을 Pipe에 넣어주는 것만으로도 Collection을 Return한 것과 같은 효과를 지닌다. 이 기능은 너무나 강력하고 편리해서 처음 접하면 "와~ 역시 오라클이야"라는 탄성을 부르게 한다. 이 정도면 천재적인 발상이라고 본다.

UKJA@ukja116> create or replace function get_array(p_start number, p_end number)
  2  return varchar2_array
  3  pipelined
  4  is
  5     v_row           varchar2(4000);
  6  begin
  7     for idx in p_start .. p_end loop
  8             v_row := idx || 'th value';
  9             pipe row(v_row);
 10     end loop;
 11
 12     return;
 13  end;
 14  /

Function created.

Elapsed: 00:00:00.09
UKJA@ukja116>
UKJA@ukja116> select * from table(get_array(100, 112));

COLUMN_VALUE
--------------------------------------------------------------------------------
100th value
101th value
102th value
103th value
104th value
105th value
106th value
107th value
108th value
109th value
110th value
111th value
112th value

13 rows selected.

Elapsed: 00:00:00.04

이 기능이 너무나 편리하기 때문에 Oracle 스스로도 매우 즐겨쓰고 있다. 가령 실행 계획을 보여주는 dbms_xplan 패키지는 모두 Pipelined Table Function으로 구현되어 있다.

UKJA@ukja116> explain plan for select * from t1;

Explained.

Elapsed: 00:00:00.01
UKJA@ukja116>
UKJA@ukja116> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   112K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  1000 |   112K|     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

12 rows selected.

이 기능을 잘 이용하면 다음과 같이 Oracle User Dump Directory에 존재하는 Trace 파일을 Table Function을 이용해서 편리하게 읽을 수 있다. 아래의 함수 정의를 보면 쉽게 이해할 수 있을 것이다.

-- create directory and varchar2 array
col value new_value user_dump_directory;

select value from v$parameter where name = 'user_dump_dest';

create or replace directory user_dump_dir as '&user_dump_directory';

create or replace type varchar2_array as table of varchar2(4000);
/

-- create get_trace_file1 function
create or replace function get_trace_file1(s_id number default userenv('sid'))
return varchar2_array
pipelined
as
  v_handle  utl_file.file_type;
  v_filename  varchar2(2000);
  v_line      varchar2(4000);
begin
  -- get exact file_name
  select i.value||'_ora_'||p.spid||'.trc' into v_filename
  from v$process p, v$session s,
    (select value from v$parameter where name = 'instance_name') i
  where p.addr = s.paddr
        and s.sid = s_id
  ;

  v_handle := utl_file.fopen('USER_DUMP_DIR', v_filename, 'R', 32767);

  loop
    begin
      utl_file.get_line(v_handle, v_line);
    exception when no_data_found then
      exit;
    end;

    pipe row(v_line);
  end loop;
    utl_file.fclose(v_handle);
   
  return;
end;
/

-- create get_trace_file2 function
create or replace function get_trace_file2(file_name in varchar2)
return varchar2_array
pipelined
as
  v_handle  utl_file.file_type;
  v_line      varchar2(20000);
begin

  v_handle := utl_file.fopen('USER_DUMP_DIR', file_name, 'R', 32767);

  loop
    begin
      utl_file.get_line(v_handle, v_line);
    exception when no_data_found then
      exit;
    end;

    pipe row(v_line);
  end loop;

    utl_file.fclose(v_handle);
   
  return;
end;
/

위의 함수를 이용하면 다음과 같이 Trace 파일을 SQL에서 마치 Table에 존재하는 데이터처럼 처리할 수 있다.

@trace_on 10046 8
select * from t1;
@trace_off

select count(*) from table(get_trace_file1);

Trace 파일을 읽어서 패턴을 분석하거나 추가적인 정보를 가공하고 싶은 경우에 대단히 유용한다. 아래 블로그 포스트에서 이미 그런 기능을 소개한 적이 있다.

http://ukja.tistory.com/214

몇 가지 예를 보자. Index Tree Dump를 보면서 Index Leaf Block을 Dump하고 싶은 경우가 있다. 이때 몇가지 귀찮은 수작업이 동반된다. 하지만 다음과 같이 Dump 명령문을 Copy&Paste하기만 하면 되도록 거의 자동화시킬 수 있다.

exec tree_dump('t1_n1');

select
    prefix||
    type ||
    ' max_rows=' || nrow ||', '||
    'cur_rows=' || rrow ||', '||
    'dump=alter system dump datafile ' ||
            dbms_utility.data_block_address_file(to_dec(dba)) || ' block ' ||
            dbms_utility.data_block_address_block(to_dec(dba))
from (
    select
        regexp_substr(column_value, '^[[:space:]]+') as prefix,
        regexp_substr(column_value, '(branch|leaf)') as type,
        regexp_replace(regexp_substr(column_value, '(branch:|leaf:) [^ ]+'),
                            '(branch:|leaf:) 0x', '') as dba,
        substr(regexp_substr(column_value, 'nrow: [[:digit:]]+'), 7) as nrow,
        substr(regexp_substr(column_value, 'rrow: [[:digit:]]+'), 7) as rrow
    from table(get_trace_file1)
    where regexp_like(column_value, '(branch:|leaf:)')
)
;

PREFIX||TYPE||'MAX_ROWS='||NROW||','||'CUR_ROWS='||RROW||','||'DUMP=ALTERSYSTEMD
--------------------------------------------------------------------------------
branch max_rows=17, cur_rows=, dump=alter system dump datafile 7 block 2804
   leaf max_rows=62, cur_rows=62, dump=alter system dump datafile 7 block 2805
   leaf max_rows=62, cur_rows=62, dump=alter system dump datafile 7 block 2806
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 7 block 2807
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 7 block 2808
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10969
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10970
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10971
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10972
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10973
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10974
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10975
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10976
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9866
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9867
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9868
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9869
   leaf max_rows=22, cur_rows=22, dump=alter system dump datafile 11 block 9870

위의 Query를 보면 알겠지만, Trace/Dump 파일을 SQL을 이용해 분석할 때는 Regular Expression(10g부터 제공)이 특히 유용하다.

아래 예는 PGA Heap Dump(Subheap까지 포함한) 파일을 분석하는 예제이다.

oradebug setospid 6620
-- this is a very convenient option!
oradebug dump heapdump 0x20000001

위의 명령에 의해 생긴 PGA Heap Dump 파일을 heap_analyze.sql을 이용해 분석해 보자. heap_analyze.sql은 위에서 정의한 get_trace_file 함수를 이용해 Trace 파일을 읽으면서 집계 함수와 Regular Expression을 이용해 패턴 분석을 해서 어떤 Chunk들이 주로 Heap을 점유하고 있는지 추측하게 해준다.

@heap_analyze heap_dump_utl_file.trc
                                                                               
HEAP_NAME                   HSZ                                                
-------------------- ----------                                                
session heap               29.6                                                
top uga heap               29.6                                                
koh-kghu sessi             22.0                                                
top call heap                .5                                                
callheap                     .5                                                
pga heap                     .4                                                
diag pga                     .0                                                
PLS non-lib hp               .0                                                
Alloc environm               .0                                                
kxs-heap-b                   .0                                                
Alloc server h               .0                                                
pesom.c:Proces               .0                                                
KFK_IO_SUBHEAP               .0                                                
                                                                               
13 rows selected.                                                              
                                                                               
Elapsed: 00:00:00.03                                                           
                                                                               
HEAP_NAME            CHUNK_TYPE           CNT         SZ        HSZ HRATIO     
-------------------- --------------- -------- ---------- ---------- ------     
Alloc environm       freeable               2         .0         .0   50.8     
Alloc environm       perm                   4         .0         .0   45.1     
Alloc environm       recreate               1         .0         .0    4.1     
Alloc server h       freeable               3         .0         .0   88.1     
Alloc server h       perm                   2         .0         .0   11.9     
KFK_IO_SUBHEAP       freeable               1         .0         .0   96.4     
KFK_IO_SUBHEAP       free                   2         .0         .0    3.6     
PLS non-lib hp       freeable              24         .0         .0   84.9     
PLS non-lib hp       perm                   2         .0         .0    8.8     
PLS non-lib hp       free                   4         .0         .0    6.3     
callheap             freeable          23,570         .5         .5   99.7     
callheap             free                   4         .0         .5     .3     
diag pga             freeable             124         .0         .0   87.2     
diag pga             perm                   2         .0         .0   12.3     
diag pga             free                   6         .0         .0     .5     
koh-kghu sessi       freeable           1,410       22.0       22.0  100.0     
kxs-heap-b           freeable              17         .0         .0   79.3     
kxs-heap-b           perm                   2         .0         .0   14.3     
kxs-heap-b           free                   2         .0         .0    6.4     
pesom.c:Proces       free                   4         .0         .0   68.4     
pesom.c:Proces       freeable               3         .0         .0   31.6     
pga heap             perm                  30         .2         .4   52.4     
pga heap             freeable              39         .1         .4   34.9     
pga heap             free                  16         .0         .4    7.2     
pga heap             recreate               7         .0         .4    5.5     
session heap         freeable           1,879       22.3       29.6   75.2     
session heap         free               1,028        7.3       29.6   24.5     
session heap         perm                   4         .1       29.6     .3     
session heap         recreate              22         .0       29.6     .1     
top call heap        freeable             439         .5         .5   92.4     
top call heap        free                  14         .0         .5    6.9     
top call heap        recreate               2         .0         .5     .4     
top call heap        perm                   4         .0         .5     .2     
top uga heap         freeable             473       29.5       29.6   99.7     
top uga heap         recreate               1         .1       29.6     .2     
top uga heap         free                 948         .0       29.6     .1     
                                                                               
36 rows selected.                                                              
                                                                               
Elapsed: 00:00:00.03                                                           
                                                                               
HEAP_NAME            OBJ_TYPE                  CNT         SZ        HSZ HRATIO
-------------------- -------------------- -------- ---------- ---------- ------
Alloc environm       Alloc server h              2         .0         .0   47.3
Alloc environm       perm                        4         .0         .0   45.1
Alloc environm                                   1         .0         .0    7.6
Alloc server h                                   3         .0         .0   88.1
Alloc server h       perm                        2         .0         .0   11.9
KFK_IO_SUBHEAP       KFKPGA                      1         .0         .0   96.4
KFK_IO_SUBHEAP                                   2         .0         .0    3.6
PLS non-lib hp       PL/SQL STACK                2         .0         .0   67.7
PLS non-lib hp       pifictx                     1         .0         .0   10.0
PLS non-lib hp       perm                        2         .0         .0    8.8
PLS non-lib hp                                   4         .0         .0    6.3
PLS non-lib hp       PEIDEF                      1         .0         .0    4.2
PLS non-lib hp       peihstdep                  13         .0         .0    1.7
PLS non-lib hp       pl_lut_alloc                1         .0         .0     .4
PLS non-lib hp       pl_iot_alloc                1         .0         .0     .4
PLS non-lib hp       PLSQL Stack des             2         .0         .0     .2
PLS non-lib hp       pififd.file                 1         .0         .0     .1
PLS non-lib hp       pififd.dirobj               1         .0         .0     .1
PLS non-lib hp       pififd.dir                  1         .0         .0     .1
callheap             pifigdir_lowerc        23,569         .5         .5   99.6
callheap                                         4         .0         .5     .3
callheap             Typecheck heap              1         .0         .5     .0
diag pga             dbgeInitProcess             4         .0         .0   50.3
diag pga             defFile:dbgtfNo             1         .0         .0   13.4
diag pga             perm                        2         .0         .0   12.3
diag pga             dbgeccFCInit:FC             1         .0         .0    9.2
diag pga             dbgr entry                 98         .0         .0    7.0
diag pga             fcctx:dbgeccFCI             1         .0         .0    2.8
diag pga             dbgrim IMDA                 2         .0         .0    1.5
diag pga             dbgr cache entr             4         .0         .0    1.0
diag pga             tctx:dbgtcNotif             1         .0         .0     .7
diag pga                                         6         .0         .0     .5
diag pga             tagctx:dbgeccTa             1         .0         .0     .3
diag pga             dbgeccTbvInit:t             6         .0         .0     .2
diag pga             dbgeInitGlobalC             1         .0         .0     .2
diag pga             dbgdutlHeapAllo             1         .0         .0     .2
diag pga             dbgdInitEventGr             1         .0         .0     .1
diag pga             fstack:dbgtfNot             1         .0         .0     .1
diag pga             dbgr-meta-data              1         .0         .0     .1
koh-kghu sessi       pmucalm coll            1,406       22.0       22.0  100.0
koh-kghu sessi       recursive addr              2         .0       22.0     .0
koh-kghu sessi       pliost struct               2         .0       22.0     .0
kxs-heap-b           bind var buf               17         .0         .0   79.3
kxs-heap-b           perm                        2         .0         .0   14.3
kxs-heap-b                                       2         .0         .0    6.4
pesom.c:Proces                                   4         .0         .0   68.4
pesom.c:Proces       peshm.c:peshmta             2         .0         .0   30.7
pesom.c:Proces       peshm.c: peshmp             1         .0         .0    1.0
pga heap             perm                       30         .2         .4   52.4
pga heap             diag pga                    9         .0         .4   10.9
pga heap             kgh stack                   1         .0         .4    8.4
pga heap             Fixed Uga                   1         .0         .4    7.7
pga heap                                        16         .0         .4    7.2
pga heap             PLS non-lib hp              3         .0         .4    4.6
pga heap             Alloc environm              2         .0         .4    2.7
pga heap             pesom.c:Proces              3         .0         .4    2.1
pga heap             KFK_IO_SUBHEAP              1         .0         .4    1.1
pga heap             kopolal void                5         .0         .4     .6
pga heap             kjztprq struct              1         .0         .4     .5
pga heap             KSFQ heap                   1         .0         .4     .5
pga heap             kpuinit env han             1         .0         .4     .4
pga heap             kews sqlstat st             1         .0         .4     .3
pga heap             joxp heap                   1         .0         .4     .1
pga heap             KGNFS pcontext              1         .0         .4     .1
pga heap             PLS cca hp desc             1         .0         .4     .1
pga heap             bcheapd_kdlwpga             1         .0         .4     .0
pga heap             iovecheapd_kdlw             1         .0         .4     .0
pga heap             sioheapd_kdlwpg             1         .0         .4     .0
pga heap             KFIO PGA struct             1         .0         .4     .0
pga heap             KSFQ heap descr             1         .0         .4     .0
pga heap             regheapd_kdlwpg             1         .0         .4     .0
pga heap             KSZ subheap des             1         .0         .4     .0
pga heap             sdbgrf: iosb                1         .0         .4     .0
pga heap             allocate kzthsm             1         .0         .4     .0
pga heap             dbgdInitEventGr             1         .0         .4     .0
pga heap             KJZT context                1         .0         .4     .0
pga heap             koh-kghu call h             1         .0         .4     .0
pga heap             kzsna:login nam             1         .0         .4     .0
pga heap             external name               1         .0         .4     .0
session heap         koh-kghu sessi          1,422       22.0       29.6   74.3
session heap                                 1,028        7.3       29.6   24.5
session heap         kxsFrame4kPage             10         .1       29.6     .3
session heap         perm                        4         .1       29.6     .3
session heap         kxsFrame16kPage             2         .0       29.6     .1
session heap         kxsFrame32kPage             1         .0       29.6     .1
session heap         kxsc: kkspsc0              51         .0       29.6     .1
session heap         koh-kghu sessio            17         .0       29.6     .1
session heap         kxsFrame8kPage              1         .0       29.6     .1
session heap         kgsc ht segs              323         .0       29.6     .1
session heap         kxs-heap-b                  5         .0       29.6     .0
session heap         PLS non-lib hp              3         .0       29.6     .0
session heap         kxs-heap-p                  5         .0       29.6     .0
session heap         Alloc environm              1         .0       29.6     .0
session heap         kzctxhugi1                  1         .0       29.6     .0
session heap         kgiob                      13         .0       29.6     .0
session heap         kgich                       7         .0       29.6     .0
session heap         kpuinit env han             1         .0       29.6     .0
                                                                               
HEAP_NAME            OBJ_TYPE                  CNT         SZ        HSZ HRATIO
-------------------- -------------------- -------- ---------- ---------- ------
session heap         kxs-heap-d                  1         .0       29.6     .0
session heap         kokl lob id has             1         .0       29.6     .0
session heap         kodpai image                1         .0       29.6     .0
session heap         kgsc:kxsc                   1         .0       29.6     .0
session heap         Session NCHAR l             1         .0       29.6     .0
session heap         session languag             1         .0       29.6     .0
session heap         kgiobdtb                    5         .0       29.6     .0
session heap         PLS cca hp desc             2         .0       29.6     .0
session heap         kokl transactio             1         .0       29.6     .0
session heap         kqdAlloc                    1         .0       29.6     .0
session heap         kokahin kgglk               1         .0       29.6     .0
session heap         kqlpWrntoStr:st             1         .0       29.6     .0
session heap         kxs-krole                   1         .0       29.6     .0
session heap         kwqidwh memory              2         .0       29.6     .0
session heap         kwqmahal                    2         .0       29.6     .0
session heap         kwqb context me             2         .0       29.6     .0
session heap         kwqaalag                    2         .0       29.6     .0
session heap         kwqica hash tab             2         .0       29.6     .0
session heap         kodmcon kodmc               1         .0       29.6     .0
session heap         kzsrcrdi                    1         .0       29.6     .0
session heap         dbgdInitEventGr             1         .0       29.6     .0
session heap         ksulu : ksulueo             1         .0       29.6     .0
session heap         system trigger              1         .0       29.6     .0
session heap         kdlw:UGA state              1         .0       29.6     .0
session heap         koklug hxctx in             1         .0       29.6     .0
session heap         koddcal void                1         .0       29.6     .0
session heap         koklug hlctx in             1         .0       29.6     .0
session heap         kdlu:UGA state              1         .0       29.6     .0
top call heap        callheap                  441         .5         .5   92.9
top call heap                                   14         .0         .5    6.9
top call heap        perm                        4         .0         .5     .2
top uga heap         session heap              474       29.6       29.6   99.9
top uga heap                                   948         .0       29.6     .1
                                                                               
130 rows selected.                                                             
                                                                               
Elapsed: 00:00:00.11                                                           
                                                                               
HEAP_NAME            SUBHEAP                   CNT         SZ        HSZ HRATIO
-------------------- -------------------- -------- ---------- ---------- ------
Alloc environm                                   6         .0         .0   56.8
Alloc environm       ds=0DC5E20C                 1         .0         .0   43.2
Alloc server h                                   5         .0         .0  100.0
KFK_IO_SUBHEAP                                   3         .0         .0  100.0
PLS non-lib hp                                  30         .0         .0  100.0
callheap                                    23,574         .5         .5  100.0
diag pga                                       132         .0         .0  100.0
koh-kghu sessi                               1,410       22.0       22.0  100.0
kxs-heap-b                                      21         .0         .0  100.0
pesom.c:Proces                                   7         .0         .0  100.0
pga heap                                        80         .3         .4   83.6
pga heap             ds=0D7C048C                 8         .0         .4   10.7
pga heap             ds=0DA07578                 2         .0         .4    2.4
pga heap             ds=0DC5F0EC                 1         .0         .4    2.1
pga heap             ds=0DCA9F90                 1         .0         .4    1.1
session heap         ds=0E23F76C             1,407       22.0       29.6   74.2
session heap                                 1,518        7.6       29.6   25.7
session heap         ds=0DA96F34                 3         .0       29.6     .0
session heap         ds=0DA9EA3C                 2         .0       29.6     .0
session heap         ds=0DCE66D4                 1         .0       29.6     .0
session heap         ds=0DA96E2C                 1         .0       29.6     .0
session heap         ds=0E2308E0                 1         .0       29.6     .0
top call heap        ds=0DFFB888               439         .5         .5   92.4
top call heap                                   20         .0         .5    7.6
top uga heap         ds=0DA66A34               473       29.5       29.6   99.7
top uga heap                                   949         .1       29.6     .3
                                                                               
26 rows selected.                                                              
                                                                               
Elapsed: 00:00:00.04             
                                              
Oracle 성능 Troubleshooting의 새로운 세계에 오신 것을 환영한다!
                                                                               


신고
Trackback 0 : Comments 3
  1. extremedb 2009.07.01 17:35 신고 Modify/Delete Reply

    좋은 정보 얻어갑니다.

  2. 할배 2009.08.20 04:25 신고 Modify/Delete Reply

    예전에 읽었던 글인데 이걸 이용해야 할 일이 생겨서 테스트를 좀 하고 있습니다.
    한마디로 '왕입니다요~'

    trace file뿐 아니라 주기적으로 수행하는 batch작업등이 남기는 log file에 대한 분석에도 아주 유용하겠네요.
    이걸 shell로 짤생각을 하면..

    휴~ 정말 감사합니다.

    • 욱짜 2009.08.20 10:24 신고 Modify/Delete

      유용하게 사용된다니 다행입니다.

      저 스스로도 정말 유용하게 사용하고 있는 기법이기도 합니다. ^^

Write a comment


Table Function과 Join

오라클 2009.01.19 15:59
Table Function을 즐겨 사용하는 사람들이 있을 것이다.

Table Function과 관련해서 잘 알려지지 않은 사실 중 하나는 Join에 관한 것이다. Table Function의 결과는 말 그대로 Table과 같으므로 Join에 문제가  없어야 한다.

하지만 어떻게?

간단한 예를 통해 Table Function을 Join에서 어떻게 사용하는지 알아 보자.

다음과 같이 Object Type과 Collection Type을 선언한다.

-- create objects
create or replace type obj_type1 as object (
  c1 int,
  c2 int
);
/

create or replace type obj_tbl_type1 as table of obj_type1;
/


Collection Type을 Return하는 Pipelined Function을 생성한다.

create or replace function func1
return obj_tbl_type1
pipelined
is
  v_obj obj_type1;
begin
  for idx in 1 .. 100 loop
    v_obj := obj_type1(idx, idx);
    pipe row(v_obj);
  end loop;
end;
/


다음과 같이 사용된다.

select * from table(func1());

        C1         C2
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
...
        99         99
       100        100



좀 더 재미있는 테스트를 위해 다음과 같이 Argument를 받는 Function을 생성한다.

create or replace function func2(p1 int, p2 int, p3 int)
return obj_tbl_type1
pipelined
is
  v_obj obj_type1;
begin
  for idx in 1 .. p3 loop
    v_obj := obj_type1(p1+idx, p2+idx);
    pipe row(v_obj);
  end loop;
end;
/


다음과 같이 사용된다.

select * from table(func2(1, 1, 10))
;
        C1         C2
---------- ----------
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
        11         11


이 함수를 어떻게 다른 Table과 조인하는가?

drop table t1 purge;
create table t1(c1)
as
select level from dual connect by level <= 100
;

이런 방식은 지원되지 않는다.

select *
from t1, table(func2(p1, p2, 10)) x
where t1.c1 = x.c1
;


다음과 같은 문법이 사용된다.

select *
from t1, table(func2(t1.c1, t1.c1, 10))
;

        C1         C1         C2
---------- ---------- ----------
         1          2          2
         1          3          3
...

즉, t1의 결과가 Function의 인자로 바로 사용된다. 이때 순서가 중요하다.
다음과 같이 순서가 바뀌면 Oracle은 처리하지 못한다.

select *
from table(func2(t1.c1, t1.c1, 10)), t1
;
ERROR at line 2:
ORA-00904: "T1"."C1": invalid identifier


이 사실을 응용하면 다음과 같이 자유롭게 Join에 사용할 수 있다.

select *
from
  (select null as c1, null as c2 from dual connect by level <= 100) s,
  table(func2(s.c1, s.c1, 10))
;


잘 이용하면 매우 강력한 Query를 만들 수 있다.


가령 아래 Query를 보자. Shared Pool(v$sql)에 Cache되어 있는 Query들 중 buffer_gets(logical reads) 수치가 높은 순으로 Runtime 실행 계획을 추출한다. 이런 복잡해 보이는 요구 사항도 Table Function의 Join 기능을 잘 이용하면 매우 간단한게 구현할 수 있다.

select plan_table_output
from
  (select * from
    (select s.sql_id, s.child_number
      from v$sql s
      where exists(select 1 from v$sql_plan p where p.plan_hash_value = s.plan_hash_value)
      order by s.buffer_gets desc)
    where rownum <= 10
  ) s,
  table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last'))
;

(출력 문제로 짤림)
PLAN_TABLE_OUTPUT                                                             
-------------------------------------------------------------------------------
SQL_ID  803b7z0t84sq7, child number 0                                         
-------------------------------------                                         
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (
((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and
(this_date is null) order by next_date, job                                   
                                                                              
Plan hash value: 1846751226                                                   
                                                                              
-------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buf
-------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |      1 |      0 |00:00:00.01 |   
|*  2 |   TABLE ACCESS FULL| JOB$ |      1 |      1 |      0 |00:00:00.01 |   
-------------------------------------------------------------------------------
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   2 - filter(((("NEXT_DATE">=:1 AND "NEXT_DATE"<:2) OR ("LAST_DATE" IS NULL AN
              ("FIELD1"=:4 OR ('Y'=:5 AND "FIELD1"=0)) AND "THIS_DATE" IS NULL)
                                                                              
SQL_ID  96g93hntrzjtr, child number 0                                         
-------------------------------------                                         
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample
minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln
hist_head$ where obj#=:1 and intcol#=:2                                       
                                                                              
Plan hash value: 2239883476                                                   
                                                                              
-------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | A-Rows |   A
-------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| HIST_HEAD$        |      1 |      1 |00:0
|*  2 |   INDEX RANGE SCAN          | I_HH_OBJ#_INTCOL# |      1 |      1 |00:0
-------------------------------------------------------------------------------
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   2 - access("OBJ#"=:1 AND "INTCOL#"=:2)                                     
                                                                              
Note                                                                          
-----                                                                         
   - rule based optimizer used (consider using cbo)  
...


(Pipelined) Table Function
은 적재적소에 잘 사용하면 매우 세련된 Query와 Application을 가능하게 해 주는 좋은 기능이라고 생각된다.

               


                                                                              


신고
Trackback 0 : Comments 4
  1. 박병섭 2009.01.30 10:36 신고 Modify/Delete Reply

    안녕하세요..
    욱짜의 오라클 블로그가 조동욱님꺼였네요. 쓰신 책들은 사서(RAC빼고는 모두 샀습니다. ) 아주.. 힘들게 보고 있습니다.
    ㅡㅡ;
    최근엔 옵티마이징 오라클 옵티마이저를 보기 시작했습니다. 한번씩 보고 필요한 책을 다시 볼예정이지만, 너무 어렵네요.

    이런 정보를 주신거에 감사드리는 마음으로 글을 남깁니다.

    공부하면 공부할수록 배울게 너무 많아지네요. 언제쯤이면 중급자가 될수있는지도 모르겠습니다.

  2. 욱짜 2009.01.30 13:34 신고 Modify/Delete Reply

    이번 책 [Optimizing Oracle Optimizer]는 되도록 쉽게 쓸려고 했는데, 마음먹은 대로 되지 않은 것 같습니다.

    하지만 이전 책들보다는 훨씬 쉽고 체계적이라고 생각하고 있습니다.

    책을 보시다가 궁금하거나 이상한 점은 언제든지 문의해주세요.

  3. 유수익 2009.04.03 20:56 신고 Modify/Delete Reply

    plan정보는 어떻게 가져오나요? 음.. 재미있네요

    NOTE: cannot fetch plan for SQL_ID 8x83p6cctmgmb, CHILD_NUMBER: 0
    Was STATISTICS_LEVEL set to ALL for the session that run the statement ?

  4. 욱짜 2009.04.03 21:31 신고 Modify/Delete Reply

    http://121.254.172.39:7777/pls/apex/f?p=101:11:0::::P11_QUESTION_ID:32900346354855

Write a comment


IN (...) 절에 Item을 무제한으로 넣고 싶다.

오라클 2008.12.18 15:16
아쉽게도 Oracle에서 IN (..) 절에 사용 가능한 상수값의 개수에는 제한이 있다.

Oracle 8i까지는 최대 256개까지만 사용가능하다.

Oracle 9i부터는 최대 1000개까지만 사용가능하다.

세상에... 1000개 이상의 값을 IN (...)에 사용할 일이 있는가?라고 묻고 싶겠지만, 알 수 없는 것이 세상이고 사람이다.

아래 예제를 보자.

drop table t1 purge;

create table t1(c1 int, c2 int)
;

insert into t1
select level, level
from dual connect by level <= 10000
;


다음과 같이 총 1101개의 상수값을 사용하는 IN (...) 절을 만든다.

var v_sql clob;

begin
  :v_sql := 'select count(*) from t1 where c1 in (';
 
  for idx in 1 .. 1100 loop
    :v_sql := :v_sql || idx || ', ';
  end loop;
 
  :v_sql := :v_sql || ' 1101);';
 
end;
/

spool long_in.sql


다음과 같은 SQL 문장이 완성된다.

select count(*) from t1 where c1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134,  135, 136, 137, 138, 139, 140, ..., 1101);


이 SQL 문장을 수행하면? 불행하게도 다음과 같은 에러가 발생한다.

ERROR at line 25:
ORA-01795: maximum number of expressions in a list is 1000


미친 짓 같겠지만 어떻게든 이 제약을 피하고 싶다면?
(현실 세계에서는 이런 미친 짓이 실제로 발생하곤 한다)

내가 아는 한에서는 다음과 같은 방법들이 있다.

1. IN (...)을 살짝 비틀기

다음과 같이 무의미한 Multiple Column을 이용하면 1,000개의 제약이 사라진다.

select count(*) from t1 where (1, c1) in ((1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12), (1, 13), (1, 14), (1, 15), (1, 16), (1, 17), (1, 18), (1, 19), (1, 20), (1, 21), (1, 22), (1, 23), (1, 24), (1, 25), (1, 26), (1, 27), (1, 28), (1, 29), (1, 30), (1, 31), (1, 32), (1, 33), (1, 34), (1, 35), (1, 36), (1, 37), (1, 38), (1, 39), (1, 40), (1, 41), (1, 42), (1, 43), (1, 44), (1, 45), (1, 46), (1, 47), (1, 48), (1, 49), (1, 50), (1, 51), (1, 52), (1, 53), (1, 54), (1, 55), (1, 56), (1, 57), (1, 58), (1, 59), (1, 60), (1, 61), (1, 62), (1, 63), (1, 64), (1, 65), (1, 66), (1, 67), (1, 68), (1, 69), (1, 70), (1, 71), (1, 72), (1, 73), (1, 74), (1, 75), (1, 76), (1, 77), (1, 78), (1, 79), (1, 80), (1, 81), (1, 82), (1, 83), (1, 84), (1, 85), (1, 86), (1, 87), (1, 88), (1, 89), (1, 90), (1, 91), (1, 92), (1, 93), (1, 94), (1, 95), (1, 96), (1, 97), (1, 98), (1, 99), (1, 100), (1, 101), (1, 102), (1, 103), (1, 104), (1, 105), (1, 106), (1, 107), (1, 108), (1, 109), (1, 110), ..., (1, 1101));


간단하면서도 놀라운 방법이라고 할 수  있다. 하지만 세련되어 보이지는 않는다.

2. Global Temporary Table 사용
다음과 같이 Global Temporary Table에 원하는 Data를 추가한 후 IN (Subquery)를 사용하는 것이다.

create global temporary table gtt1(c1 int);

insert into gtt1
select level
from dual
connect by level <= 1101
;

select count(*)
from t1
where c1 in (select c1 from gtt1)
;


3. Collection + Table Function 사용
2번째 방법과 기본적으로 동일하지만 Table이 아닌 PL/SQL CollectionTable Pseudo Function을 사용한다는 것이 다르다.

가장 Oracle 다운 방법이라고 할 수 있다.

create or replace type type1 as table of int;
/

create or replace function func1
return type1
is
  v_value type1 := type1();
begin
   for idx in 1 .. 1101 loop
     v_value.extend;
     v_value(idx) := idx;
   end loop;
  
   return v_value;
  
end;
/

   
select count(*) from t1
where c1 in (select * from table(func1))
;


4. Collection + Pipelined Function + Table Function 사용
세번째 방법의 확장판이다. 가장 세련되어 보인다. 하지만 성능 면에서는 세번째 방법에 비해 조금 불리할 수  있다.

create or replace type type1 as table of int;
/

create or replace function func1
return type1
pipelined
is
begin
   for idx in 1 .. 1101 loop
    pipe row(idx);
   end loop;
  
   return;
  
end;
/

select count(*) from t1
where c1 in (select * from table(func1))
;


하나의 문제에 대해 여러 가지의 해결책을 탐색하고, 상황에 따라 가장 최적의 솔루션을 찾을 수 있다는 것이 중요하다.


신고
Trackbacks 6 : Comments 2
  1. 강정식 2008.12.18 17:49 신고 Modify/Delete Reply

    Function 성능 테스트할 때 실제 IN으로 공급해서 SQL을 많이 만드는데
    도움이 많이 될것 같습니다.
    감사합니다. ^^

  2. 호야 2008.12.19 09:40 신고 Modify/Delete Reply

    실제로 1000개 이상의 값을 in에 넣어서 사용하는 경우가 있어서 Global Temporary Table을 사용했었는데 다양한 방법이 있네요.

Write a comment

티스토리 툴바