태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Oracle Library Cache를 이해하기 위한 숙제

오라클 2008.11.09 19:59
Oracle Library Cache를 이해하기 위한 좋은 숙제가 있을까? 이런 숙제를 생각했다.

- Hard Parse(정확하게 말하면 Optimization)에 수십 초 이상 소요되는 Query를 만들어라
- 해당 Query를 두 개 세션이 동시에 수행할 때 발생하는 대기 현상을 보고하라
- 해당 대기가 어떤 SQL문에 의해 발생하는지 역으로 추적해보라

어려운 듯 쉬운 문제이고 쉬운 듯 어려운 문제이다. 아마 이 문제를 쉽게 풀어낸다면 Oracle Library Cache에 대해 어느 정도 이해하고 있다고 할 수 있을 것이다.

1. Hard Parse에 수십 초 이상 소요되는 Query를 만들려면?
여러 가지 방법이 있겠다. 내가 선호하는 방법은 다음과 같이 SQL*Plus와 PL/SQL을 이용해서 아주 많은 수의 Table을 Join하는 Query를 만드는 것이다. 단, where 1 = 0 조건을 추가해서 Logical Reads의 발생을 최소화한다.

ed make_long.sql

-- make_long.sql의 내용은 다음과 같다.
set heading off
set timing off
set feedback off
set serveroutput on size 100000

var v_sql clob;

begin
  :v_sql := 'select count(*) from ';
 
  for r in (select t1.table_name
        from user_tables t1, user_tables t2 where rownum <= 500
          and t1.table_name not like '%$%') loop
    :v_sql := :v_sql || r.table_name || ', ';
   
  end loop;
 
  :v_sql := :v_sql || ' dual where 1 = 0;';
 
end;
/

spool long_parse.sql

exec dbms_output.put_line(:v_sql);

spool off
set heading on
set timing on
set feedback on
set serveroutput off

make_long.sql을 수행하면

@make_long

다음과 같이 long_parse.sql 파일이 생성된다.
매우 긴 SQL 문장이 기록되어 있다.(단, user_tables에 많은 수의 Table이 있어야 한다)

ed long_parse

select count(*) from T_HIST, T_NO_PART, T_PART1, T_PART2, ...,
...,
...,
T_MON_EVENT_TEMP,  dual where 1 = 0;          

Hard Parse를 유발하기 위해 Shared Pool을 Flush한다.

-- flush shared pool
alter system flush shared_pool;

Session #1에서 long_parse.sql을 수행한다. Optimization을 수행하는데 많은 시간을 소비한다.

-- session#1
@long_parse

이 SQL이 Parse에 많은 시간을 보낸다는 것을 어떻게 검증하는가? 10046 Trace를 수행해보면 알 수 있다. 아래에 그 결과가 있다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1    200.43     197.63         15         24          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4    200.45     197.64         15         24          0           1

오로지 Parse 단계에서만 시간을 소비(여기서는 200초 정도)한 것을 알 수 있다.

2. 두 개의 세션이 동시에 long_parse.sql을 수행했을 때 어떤 경합이 발생하는가?

-- session#2
@long_parse

Session #3에서 대기 현상을 관찰하면?

-- session#3
@wait

다음과 같이 library cache pin 대기가 발생한다.

       SID EVENT                     P1              P2              P3
SECONDS_IN_WAIT
---------- ------------------------- --------------- --------------- ----------
---------------
       155 library cache pin         1728675460(6709 1699073336(6545 200(000000
             15
                                     7E84)           CD38)           C8)

이 대기 현상은 Optimization에 의해 Execution Plan을 생성하는 단계에서는 해당 LCO(SQL)에 대해 library cache pin을 Exclusive Mode로 점유한다는 것에서 기인한다. SQL을 실행하려면 library cache pin을 Shared Mode로 획득해야 하므로 대기가 발생하는 것이다.

3. 역으로 어떤 SQL(또는 LCO-Library Cache Object)에 대한 library cache pin 경합이 발생하는지 관찰할 수 있는가? 즉, 2번에서 언급한 사전 지식이 없다면 이를 어떻게 해석할 것인가?

다음과 같이 155번 Session이 어떤 Lock을 점유하고 있는지 확인한다.

@lock_internal 155
       SID TY handle            mod        req
---------- -- ---------- ---------- ----------
       155 LK 67764190            1          0
       155 LK 68FA6B18            1          0
       155 LK 682562E0            1          0
       155 LK 671DEF04            1          0
       155 LK 671DE560            1          0
       155 LK 682B3ECC            1          0
       155 LK 682B3528            1          0
       155 LK 680DDCEC            1          0
       155 LK 67097E84            1          0
       155 LK 6720C2D0            1          0
       155 LK 6720C18C            1          0
       ...
       155 LN 67097E84            0          2

LN = Library Cache Pin을 대기한다. 2 = Shared Mode이다. 즉 Library cache pin을 Shared Mode로 점유하기 위해 대기하는 것이다. 이는 거꾸로 어떤 세션(여기서는 Session#)1이 동일 LCO에 대해 library cache pin을 Exclusive Mode로 점유하고 있다는 것을 의미한다.

어떤 LCO에 대한 점유인가? 다음과 같이 조회해 보면...

@lco 67097E84

KGLHDADR                      : 67097E84
KGLNAOBJ                      : select count(*) from T_HIST, T_NO_PART, T_PART1,
 T_PART2, T_PART3, T_BIND, T_CURSOR, SAWR$SNAPSHOTS, SAWR$SESSIONS, SAWR$SESSION
_EVENTS, SAWR$SESSION_STATS,
SAWR$SESSION_STAT_MODE, T_SOURCE, MV_SOURCE, RUN_STATS, T_BUG2, T_BUG, T_BUG1, T
1_STAT, T, T_PLAN, T_COMPRESS, T_DIRECT, T_BC1, T_BC2, T_INDPART, T_SS, T_SPLIT,
 T_IND_LOCK2, STATS_TABLE, T_LINK,
T_SHORT_COMMIT, T_LONG_COMMIT, T_BATCH_DML, T_BTREE2, T_SYSSTAT, T_INVOKE, T_EXT
, T_1, T_2, T_3, T_4, T_5, T_6, T_7, T_8, T_9, T_10, T_BTREE, T_FULL, T_MON_TEMP
, T_MON_TIME_TEMP, T_MON_LATCH_TEMP,
T_MON_EVENT_TEMP, T_TEMP, T1_TEMP, T_SCATTERED, T_CLUSTERED, T_NOSEG, T_INVISIBL
E, T_STATS, TEST, T_DYNAMIC, T_PARSE, T_UNDO, TAB_A, TAB_B, T_PX1, T_PX2, STOCKT
ABLE, T_SSO_95, T_SSO_96, T_SSO_97,
T_SSO_98, T_SSO_99, T_SSO_100, T_SSO_1, T_SSO_2, T_SSO_3, T_SSO_4, T_SSO_5, T_SS
O_6, T_SSO_7, T_SSO_8, T_SSO_9, T_SSO_10, T_SSO_11, T_SSO_12, T_SSO_13, T_SSO_14
, T_SSO_15, T_SSO_16, T_SSO_17,
T_SSO_18, T_SSO_19, T_SSO_20, T_SSO_21, T_SSO_22, T_SSO_23, T_S
KGLNAOWN                      :
LOCK_HOLDER                   : 155
KGLLKMOD                      : 1
KGLLKREQ                      : 0
PIN_HOLDER                    : 158
KGLPNMOD                      : 3
KGLPNREQ                      : 0
...           

...
KGLHDADR                      : 67097E84
KGLNAOBJ                      : select count(*) from T_HIST, T_NO_PART, T_PART1,
 T_PART2, T_PART3, T_BIND, T_CURSOR, SAWR$SNAPSHOTS, SAWR$SESSIONS, SAWR$SESSION
_EVENTS, SAWR$SESSION_STATS,
SAWR$SESSION_STAT_MODE, T_SOURCE, MV_SOURCE, RUN_STATS, T_BUG2, T_BUG, T_BUG1, T
1_STAT, T, T_PLAN, T_COMPRESS, T_DIRECT, T_BC1, T_BC2, T_INDPART, T_SS, T_SPLIT,
 T_IND_LOCK2, STATS_TABLE, T_LINK,
T_SHORT_COMMIT, T_LONG_COMMIT, T_BATCH_DML, T_BTREE2, T_SYSSTAT, T_INVOKE, T_EXT
, T_1, T_2, T_3, T_4, T_5, T_6, T_7, T_8, T_9, T_10, T_BTREE, T_FULL, T_MON_TEMP
, T_MON_TIME_TEMP, T_MON_LATCH_TEMP,
T_MON_EVENT_TEMP, T_TEMP, T1_TEMP, T_SCATTERED, T_CLUSTERED, T_NOSEG, T_INVISIBL
E, T_STATS, TEST, T_DYNAMIC, T_PARSE, T_UNDO, TAB_A, TAB_B, T_PX1, T_PX2, STOCKT
ABLE, T_SSO_95, T_SSO_96, T_SSO_97,
T_SSO_98, T_SSO_99, T_SSO_100, T_SSO_1, T_SSO_2, T_SSO_3, T_SSO_4, T_SSO_5, T_SS
O_6, T_SSO_7, T_SSO_8, T_SSO_9, T_SSO_10, T_SSO_11, T_SSO_12, T_SSO_13, T_SSO_14
, T_SSO_15, T_SSO_16, T_SSO_17,
T_SSO_18, T_SSO_19, T_SSO_20, T_SSO_21, T_SSO_22, T_SSO_23, T_S
KGLNAOWN                      :
LOCK_HOLDER                   : 158
KGLLKMOD                      : 1
KGLLKREQ                      : 0
PIN_HOLDER                    : 155
KGLPNMOD                      : 0
KGLPNREQ                      : 2
-----------------

긴 SQL 문장이며, 158번 Session(3번, 즉 Exclusive Mode로 점유)과 155번 Session(2번, 즉 Shared Mode로 대기)간의 싸움이 있음을 알 수 있다.

위의 숙제를 다른 사람의 도움 없이 풀 수 있겠는가?

- 우선 PL/SQL과 SQL*Plus에 대한 기본적인 지식이 필요하며
- Parse를 추적하는 방법과
- LCO를 모니터링하는 방법에 대한 이해가 필요하다.

이 외에도 많은 방법이 있을 것이다. 성공적으로 풀었다면 Oracle의 Library Cache 문제를 보다 체계적으로 해결할 수 있는 기초를 갖추었다고 할 수 있겠다.

신고
Trackback 0 : Comment 1
  1. 이명진 2008.11.25 00:01 신고 Modify/Delete Reply

    저를 위해 써주신 글 잘 봤습니다, 감사합니다ㅎ.
    내일 가서 테스트해 보겠습니다^^.
    사실 저번에 보긴 봤는데, 아직도 이런 부분은 좀 어렵게 느껴집니다. 기초가 부실해서 그렇겠죠?ㅠㅠ
    암튼 열심히 하겠습니다.

Write a comment

티스토리 툴바