데모#1 - 단일 행 선택을 위해 암시적 커서 사용하기

SQL> create table t ( object_id primary key, object_name ) organization index
as
select object_id, object_name from all_objects;  2    3

테이블이 생성되었습니다.

SQL> create or replace procedure explicit
  2  as
  3    l_object_name t.object_name%type;
  4    l_dummy       t.object_name%type;
  5
  6    cursor c ( l_object_id in number )
  7    is
  8    select object_name from t where object_id = l_object_id;
  9  begin
 10    for i in 1 .. 30000
 11    loop
 12      open c(i);
 13      fetch c into l_object_name;
 14      if ( c%notfound )
 15      then
 16        l_object_name := null;
 17      end if;
 18      fetch c into l_dummy;
 19      if ( c%found )
 20      then
 21        raise too_many_rows;
 22      end if;
 23      close c;
 24    end loop;
 25  end;
 26  /

프로시저가 생성되었습니다.

SQL> create or replace procedure implicit
  2  as
  3    l_object_name t.object_name%type;
  4  begin
  5    for i in 1 .. 30000
  6    loop
  7      begin
  8        select object_name into l_object_name
  9          from t
 10         where object_id = i;
 11      exception
 12        when no_data_found then
 13          l_object_name := null;
 14      end;
 15    end loop;
 16  end;
 17  /

프로시저가 생성되었습니다.

SQL> exec runstats_pkg.rs_start;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec explicit;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec runstats_pkg.rs_middle;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec implicit;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1082 hsecs
Run2 ran in 497 hsecs
run 1 ran in 217.71% of the time

Name                                  Run1        Run2        Diff
LATCH.cache buffers chains          63,349      60,927      -2,422
STAT...recursive calls              83,462      36,544     -46,918

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
69,270      63,883      -5,387    108.43%

PL/SQL 처리가 정상적으로 완료되었습니다.

데모#2 - 제한된 수의 행을 가진 결과 집합에 암시적 커서 사용하기

SQL> create or replace procedure explicit
as
  l_rec dept%rowtype;

  cursor c
  is
  select * from dept;
begin
  open c;
  loop
    fetch c into l_rec;
    exit when c%notfound;
  end loop;
  close c;
end;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16

프로시저가 생성되었습니다.

SQL> create or replace procedure implicit
as
begin
  for x in ( select * from dept )
  loop
    null;
  end loop;
end;
/  2    3    4    5    6    7    8    9

프로시저가 생성되었습니다.

SQL> exec runstats_pkg.rs_start;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> begin
for i in 1 .. 30000
loop
explicit;
end loop;
end;
/  2    3    4    5    6    7

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec runstats_pkg.rs_middle;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> begin
for i in 1 .. 30000
loop
implicit;
end loop;
end;
/
  2    3    4    5    6    7

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 655 hsecs
Run2 ran in 478 hsecs
run 1 ran in 137.03% of the time

Name                                  Run1        Run2        Diff
STAT...session logical reads       180,030      90,039     -89,991
STAT...consistent gets from ca     180,010      90,013     -89,997
STAT...consistent gets             180,010      90,013     -89,997
STAT...table scan blocks gotte     120,000      30,000     -90,000
STAT...no work - consistent re     120,000      30,000     -90,000
STAT...recursive calls             180,001      30,003    -149,998
LATCH.cache buffers chains         360,944     180,970    -179,974
STAT...table scan rows gotten      480,000     120,000    -360,000

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
362,233     182,149    -180,084    198.87%

PL/SQL 처리가 정상적으로 완료되었습니다.