꿈꾸는 개발자, DBA 커뮤니티 구루비

래치 프로파일링

래치 획득과 관련된 뷰를 빠른 빈도로 읽음으로써 래치 획득과 경합에 대한 보다 상세한 정보를 얻는 기법.
래치 경합 문제 해석에 주로 사용되는 뷰인 V$LATCH, V$LATCH_CHILDREN 뷰는 래치별 누적치 정보를 가지고 있는 스냅샷 데이터에 해당함.
보다 상세한 분석을 위한 프로파일링 데이터용 뷰는 V$LATCHHOLDER 뷰임.
V$LATCHHOLDER 뷰 : 특정 세션이 특정 래치를 획득할 때 잠깐 기록되었다가 래치를 해제하면 기록이 사라지는 휘발성 데이터를 가진 뷰.

V$LATCHHOLDER 뷰의 정의


desc v$latchholder

이름      널?      유형                        
--------- -------- -----------------
PID                NUMBER
SID                NUMBER
LADDR              RAW(4)
NAME               VARCHAR2(64)
GETS               NUMBER

세션#1 아주 많은 수의 SQL 문장을 하드 파싱하는 작업 수행



exec dbms_application_info.set_client_info('session1');

declare

    v_cnt number;
    
begin

    for idx in 1..1000 loop
        for r in (select table_name from dba_tables) loop
            begin
                execute immediate 'select count(*) from ' || r.table_name || ' where 1 = 0' into v_cnt;
            exception when others then
                null;
            end;
        end loop;
    end loop;
    
end;
/

세션#2 에서 세션#1 이 작업을 수행하면서 어떤 래치를 얼마나 획득하는지 프로파일링함



col sid new_value sid1
select sid from v$session where client_info = 'session1';

-- simple profiling
select /*+ no_query_transformation ordered use_nl(h) */
       name, count(*) as hits
from  (select level from dual connect by level <= 100000) x
     , v$latchholder h
where  sid = &sid1
group by name
order by 2 desc
;

NAME                                           HITS
---------------------------------------- ----------
shared pool                                    3783
row cache objects                               867
enqueues                                        370
cache buffers chains                            264
enqueue hash chains                             243
shared pool simulator                           108
simulator lru latch                              45
SQL memory manager workarea list latch            4
simulator hash latch                              2
MinActiveScn Latch                                1

10 개의 행이 선택되었습니다.

Shared Pool Latch 와 Row Cache Objects Latch 에 대한 액세스가 가장 빈번한 것을 알 수 있음.

V$LATCHHOLDER 뷰의 베이스 테이블인 X$KSUPRLAT 테이블을 이용하면 보다 다양한 정보를 얻을 수 있음



desc x$ksuprlat

이름         널?      유형                        
------------ -------- -----------------
ADDR                  RAW(4)
INDX                  NUMBER
INST_ID               NUMBER
KSUPRPID              NUMBER
KSUPRSID              NUMBER
KSUPRLLV              NUMBER
KSUPRLTY              NUMBER
KSUPRLAT              RAW(4)
KSUPRLNM              VARCHAR2(64)
KSUPRLMD              VARCHAR2(64)
KSULAWHY              NUMBER
KSULAWHR              NUMBER
KSULAGTS              NUMBER

-- get current time
SQL> col cur_time new_value cur_time

SQL> select dbms_utility.get_time as cur_time from dual;

  CUR_TIME
----------
   1245612

SQL> col latch_name format a20
SQL> col code_name format a20

-- 작업이 수행되는 동안 대기이벤트가 잠깐씩 나타나므로 시간을 100에서 10000으로 늘려서 테스트함.
SQL> select /*+ opt_param('_optimizer_connect_by_cost_based', 'false')
  2             NO_QUERY_TRANSFORMATION */
  3         latch_name,
  4         code_name,
  5         trunc(100*(latch_hits/(max(total_hits) over())), 1) as held_pct,
  6         trunc(1000*5 * (latch_hits/(max(total_hits) over())), 1) as held_time,
  7         trunc(1000*5 * ((latch_hits/dist_hits) /(max(total_hits) over())), 1) as avg_held_time
  8  from  (select /*+ ordered use_nl(l) */
  9                l.sid,
 10                latch_name,
 11                code_name,
 12                NULL as sql_id,
 13                count(*) as latch_hits,
 14                count(distinct gets) as dist_hits,
 15                max(lvl) as total_hits
 16         from  (select /*+ no_merge */
 17                       level as lvl
 18                from   dual
 19                connect by level <= 1000000000000
 20                and (dbms_utility.get_time - &cur_time) <= 10000) t1,
 21               (select /*+ no_merge */
 22                       ksuprsid as sid,
 23                       ksuprlat as latch_addr,
 24                       ksulagts as gets,
 25                      (select ksllwnam from sys.x$ksllw where indx = ksulawhr) as code_name,
 26                      (select ksusesqi from sys.x$ksuse where indx = ksuprsid) as sql_id,
 27                       ksuprlnm as latch_name,
 28                       to_char(ksulawhy, 'xxxxxxxxxxxxxxxx') as obj_why
 29                from   sys.x$ksuprlat
 30                where  ksuprsid = &sid1) l
 31  where  latch_name like '%%'
 32  group by l.sid, latch_name, code_name
 33  order by count(*) desc
 34  );
구  20:               and (dbms_utility.get_time - &cur_time) <= 10000) t1,
신  20:               and (dbms_utility.get_time -    1245612) <= 10000) t1,
구  30:               where  ksuprsid = &sid1) l
신  30:               where  ksuprsid =        131) l

LATCH_NAME           CODE_NAME              HELD_PCT  HELD_TIME AVG_HELD_TIME
-------------------- -------------------- ---------- ---------- -------------
shared pool          kghalo                      1.5       78.9             0
shared pool          kghfrh                       .7         38             0
shared pool          kghfre                       .5         29             0
row cache objects    kqrpre: find obj             .5       26.5             0
shared pool          kghupr1                      .5       25.3             0
shared pool          kghalp                       .3       19.4             0
...
simulator hash latch kcbsacc: lookup dba           0          0             0

25 개의 행이 선택되었습니다.

이러한 정보들은 특히 오라클 버그와 같은 비정상적인 상황에 의한 래치 경합을 해석하는데 큰 도움이 됨.
어떤 래치를 어떤 내부 코드를 수행하면서 얼마나 획득하는지를 분석할 수 있기 때문.