| 구분 | INDEX BY | REF CUROSR | 비고 |
|---|---|---|---|
| 첫 번째 행까지의 시간 | 825ms | 25ms | -800ms |
| 마지막 행까지의 시간 | 1,375ms | 850ms | -515ms |
| 모든 행을 인출하는 데 걸린 시간 | 2,220ms | 885ms | -1,315ms |
| TRUNC(AVG(UGA)) | 76,988 | 76,988 | |
| TRUNC(AVG(PGA)) | 4,266,132 | 244,793 |
create or replace package demo_pkg
as
type varchar2_array is table of varchar2(30) index by binary_integer;
procedure index_by ( p_owner in varchar2, p_object_name out varchar2_array,
p_object_type out varchar2_array, p_timestamp out varchar2_array );
end;
/
create or replace package body demo_pkg
as
procedure index_by ( p_owner in varchar2, p_object_name out varchar2_array,
p_object_type out varchar2_array, p_timestamp out varchar2_array )
is
begin
select object_name, object_type, timestamp
bulk collect into p_object_name, p_object_type, p_timestamp
from t
where owner = p_owner;
end;
end;
/
create or replace package demo_pkg
as
type rc is ref cursor;
procedure ref_cursor ( p_owner in varchar2, p_cursor in out rc );
end;
/
create or replace package body demo_pkg
as
procedure ref_cursor ( p_owner in varchar2, p_cursor in out rc )
is
begin
open p_cursor for
select object_name, object_type, timestamp from t where owner = p_owner;
end;
end;
/
데모#1 - REF CURSOR
18:43:59 SQL> create or replace package pkg_ref_cursor as
type rc is ref cursor return all_objects%rowtype;
procedure p(v out rc);
end;
/18:44:01 2 18:44:01 3 18:44:01 4 18:44:01 5
패키지가 생성되었습니다.
경 과: 00:00:00.00
18:44:03 SQL> create or replace package body pkg_ref_cursor as
procedure p(v out rc) is
begin
open v for select * from all_objects where rownum < 1001;
end;
end;
/18:44:06 2 18:44:06 3 18:44:06 4 18:44:06 5 18:44:06 6 18:44:06 7 18:44:06 8 18:44:06 9
패키지 본문이 생성되었습니다.
경 과: 00:00:00.00
18:44:07 SQL> var rcv refcursor
18:44:10 SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.04
18:44:13 SQL> exec pkg_ref_cursor.p(:rcv);
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.00
18:44:18 SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.02
18:44:19 SQL> print :rcv;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL TIMESTAMP STATUS T G S
------------------------------ --------------- ------------------------------ ---------- -------------- ------------------- -------- -------- ------------------- --------------- - - -
SYS DUAL 258 258 TABLE 05/06/30 05/06/30 2005-06-30:19:10:20 VALID N N N
...
(중략)
...
SYS ALL_CONS_COLUMN 2541 VIEW 05/06/30 08/07/08 2005-06-30:19:11:19 VALID N N N
S
1000 개의 행이 선택되었습니다.
경 과: 00:00:00.31
18:44:22 SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 671 hsecs
Run2 ran in 493 hsecs
run 1 ran in 136.11% of the time
Name Run1 Run2 Diff
STAT...index fetch by key 0 1,915 1,915
STAT...buffer is pinned count 0 3,014 3,014
STAT...table fetch by rowid 0 3,422 3,422
STAT...table scan rows gotten 0 3,468 3,468
STAT...index scans kdiixs1 0 8,056 8,056
STAT...buffer is not pinned co 0 16,298 16,298
STAT...no work - consistent re 0 21,015 21,015
STAT...consistent gets 9 21,460 21,451
STAT...consistent gets from ca 9 21,460 21,451
STAT...session logical reads 29 21,482 21,453
LATCH.cache buffers chains 3,719 43,408 39,689
STAT...bytes sent via SQL*Net 1,638 42,182 40,544
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
6,179 45,446 39,267 13.60%
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.02