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

상황 : 넓은 범위에 대한 질의를 인덱스를 이용해서 처리하도록 유도하는 상황이다.

 

(1) 서버 시작

 

SQL> conn / as sysdba

SQL> startup force

 

 

(2) wait의 변화를 확인하기 위해 테이블 생성 및 스냅샷 생성

 

SQL> drop table mystat purge;

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

 

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

 

SQL> set lines 200
SQL> set pages 200
SQL> clear break
SQL> col event format a30

SQL> select * from mystat order by event;

 

 

(3) 20만건이 입력된 테이블 생성 및 비효율적인 select를 수행하는 프로시져 생성

 

SQL>

 

drop table hr.t1 purge;

 

create table hr.t1(id number, name char(100));

 

insert into hr.t1 (id, name)
select level as no, 'A'
from dual
connect by level <= 200000;

 

select count(*) from hr.t1;

 

create index hr.t1_idx on hr.t1(id);

 

create or replace procedure hr.up_select
is
begin
  for x in (select /*+ rule index(t hr.t1_idx) */ * 
             from hr.t1 t where id >= 0) loop
    null;
  end loop; 
end;
/

 

 

(4) 동시에 20개의 세션에서 프로시져를 수행하도록 job 생성

 

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.up_select;');
    commit;
  end loop;
end;
/

 

 

(5) job이 모두 종료된 뒤에 추가로 스냅샷을 생성하고 이전 스냅샷과 비교한다.

 

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; 

 

 

        - 주요 지표들 -

• LGWR wait for redo copy
• buffer busy waits    
• latch: cache buffers chains   
• latch: library cache          
• latch: library cache lock     
• latch: redo allocation        
• latch: row cache objects      
• latch: shared pool            
• library cache load lock       
• library cache pin             
• log buffer space              
• read by other session         
• row cache lock  

참고 : OWI 교재


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