다양한 wait가 발생하는 사례 연구 3

# 상황 : latch: library cache, latch: shard pool wait 상황

- bind 변수를 사용하지 않는 경우 -

 

(1)

 

SQL>

 

conn / as sysdba
startup force

 

drop table mystat purge;

 

create table mystat
as
select 0 as no, event, total_waits, time_waited
from v$system_event
where 1 = 2;

 

insert into mystat
select 1 as no, event, total_waits, time_waited
from v$system_event;

 

set lines 200
set pages 200
clear break

col event format a30

 

select * from mystat order by event;

 

create or replace procedure hr.without_bind_variable
is
    type rc is ref cursor;
    l_rc rc;
    l_dummy all_objects.object_name%type;
    l_start number default dbms_utility.get_time;
begin
    for i in 1 .. 1000
    loop
        open l_rc for
        'select object_name
           from all_objects
          where object_id = ' || i;
        fetch l_rc into l_dummy;
        close l_rc;
    end loop;
    dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
      ' seconds...' );
end;
/

 

 

(2)

 

alter system set job_queue_processes=20;

 

declare
  job_no number;
begin
  for idx in 1 .. 100 loop
    dbms_job.submit(job_no, 'hr.without_bind_variable;');
    commit;
  end loop;
end;
/

 

 

(3)

 

SQL> select count(*) from dba_jobs_running;

 
  COUNT(*)
-------------
              0

 

SQL> insert into mystat
     select 2 as no, event, total_waits, time_waited
     from v$system_event;

 

SQL> break on event skip 1

SQL> select event, no, total_waits, time_waited
     from mystat order by event, no;

 

- bind 변수를 사용하는 경우 -

 

(1)

 

SQL>

 

conn / as sysdba
startup force

 

drop table mystat purge;

 

create table mystat
as
select 0 as no, event, total_waits, time_waited
from v$system_event
where 1 = 2;

 

insert into mystat
select 1 as no, event, total_waits, time_waited
from v$system_event;

 

set lines 200
set pages 200
clear break

col event format a30

 

select * from mystat order by event;

 

create or replace procedure hr.with_bind_variable
is
    type rc is ref cursor;
    l_rc rc;
    l_dummy all_objects.object_name%type;
    l_start number default dbms_utility.get_time;
begin
    for i in 1 .. 1000
    loop
        open l_rc for
        'select object_name
           from all_objects
          where object_id = :x'
        using i;
        fetch l_rc into l_dummy;
        close l_rc;
    end loop;
    dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
      ' seconds...' );
end;
/

 

 

(2)

 

SQL>

 

alter system set job_queue_processes=20;

 

declare
  job_no number;
begin
  for idx in 1 .. 100 loop
    dbms_job.submit(job_no, 'hr.with_bind_variable;');
    commit;
  end loop;
end;
/

 

 

(3)

 

SQL> select count(*) from dba_jobs_running;

 
  COUNT(*)
-------------
              0

 

SQL> insert into mystat
     select 2 as no, event, total_waits, time_waited
     from v$system_event;

 

SQL> break on event skip 1

 

SQL> select event, no, total_waits, time_waited
     from mystat order by event, no;

 

출처 : http://gseducation.blog.me/20098186084?Redirect=Log