태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'오라클/대기이벤트'에 해당되는 글 2건

  1. 2010.09.28 대기이벤트가 포함된 10046 진단이벤트 실행하기 - Oracle 11g (4)
  2. 2010.09.15 Library Cache Pin에 대한 Self Deadlock 만들기

대기이벤트가 포함된 10046 진단이벤트 실행하기 - Oracle 11g

오라클/대기이벤트 2010.09.28 15:22
Oracle 11g에서 대기 이벤트가 포함된 10046 진단 이벤트를 실행하는 방법을 정리해봤습니다.

1. 전통적인 10046 진단 이벤트 사용법은 다음과 같습니다. 레벨 8 이상이면 대기 이벤트가 기록됩니다.

alter session set events '10046 trace name context forever, level 8';

select /* 10046 */ count(*) from user_objects;

alter session set events '10046 trace name context off';
2. 11g에서는 다음과 같이 sql_trace라는 "인식 가능한" 이름의 진단 이벤트를 사용할 수 있습니다.
-- sql_trace + level 8
alter session set events 'sql_trace level 8';

select /* sql_trace_1 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true
alter session set events 'sql_trace wait=true';

select /* sql_trace_2 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true, bind=true
alter session set events 'sql_trace wait=true, bind=true';

select /* sql_trace_3 */ count(*) from user_objects;

alter session set events 'sql_trace off';
11g에서 추가된 새로운 진단 이베트 기능은 매우 강력해서 아래와 같이 SQL을 지정할 수도 있습니다. 너무나 유용한 기능이죠.
-- SQL_ID 얻어오고...
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;

col sql_id new_value sql_id1
select sql_id 
from v$sqlarea 
where sql_text = 'select /* sql_trace_4 */ count(*) from user_objects';

col sql_id new_value sql_id2
select sql_id 
from v$sqlarea 
where sql_text = 'select /* sql_trace_5 */ count(*) from user_objects';

-- 하나의 sql에 대해서
alter session set events 'sql_trace [sql: &sql_id1] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';

-- 여러 개의 sql에 대해서
alter session set events 'sql_trace [sql: &sql_id1 | &sql_id2] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';
3. DBMS_MONITOR 패키지가 오라클이 공식적으로 추천하는 방법입니다. DBMS_MONITOR 패키지에서도 SQL을 특정할 수 있는 기능이 제공된다면 좋겠지만 개발자들이 아직 거기까지는 고려하지 못한 것 같습니다.
exec dbms_monitor.session_trace_enable(waits=>true);

select /* dbms_monitor */ count(*) from user_objects;

exec dbms_monitor.session_trace_disable;
4. DBMS_SYSTEM 패키지나 DBMS_SUPPORT 패키지같은 히든 패키지에서도 비슷한 기능을 제공합니다만, DBMS_MONITOR 패키지가 등장한 이상 필요가 없어졌죠.
col sid new_value sid
col serial# new_value se
select sid, serial# 
from v$session 
where sid = userenv('sid');

exec sys.dbms_system.set_ev(&sid, &se, 10046, 8, null);

select /* dbms_system */ count(*) from user_objects;

exec sys.dbms_system.set_ev(&sid, &se, 10046, 0, null);
SQL_ID 값을 이용해서 특정 SQL에 대해서만 진단 이벤트를 수행할 수 있는 기능이 특히 유용해보입니다. 오라클 11g의 확장된 진단 이벤트 기능은 아래 문서에 자세히 소개되어 있습니다.
저작자 표시
신고
Trackback 0 : Comments 4
  1. park1q 2010.09.30 12:33 신고 Modify/Delete Reply

    10G 이상에서는 v$session 보면 어떤 세션이 trace 중인가 하는 칼럼이 sql_trace sql_trace_binds, sql_trace_waits 등이 있어 알기 쉬운에..혹시 9i 에도 이걸 알수 있는 뭔가가 있을까요?

    • 욱짜 2010.09.30 13:42 신고 Modify/Delete

      저도 이 문제를 고민한 적이 있는데 뚜렷한 방법을 찾지 못해서 포기했었더랬습니다. 이후로도 아직 도움이 될만한 정보를 얻지 못하고 있어요.

  2. 물곰 2010.10.06 17:08 신고 Modify/Delete Reply

    해당 명령어로 Trace를 잡을 시 Trace파일의 Prefix를 지정할수 있는 방법은 없을까요?

    • 욱짜 2010.10.06 18:27 신고 Modify/Delete

      아마 질문의 의도가 SQL 트레이스가 동작하는 시점에 트레이스 파일의 Identifier(tracefile_identifier)를 바꾸는 것을 의미하시는 것 같습니다.

      제가 알기로는 아직 그런 기능은 없는 것 같습니다.

Write a comment


Library Cache Pin에 대한 Self Deadlock 만들기

오라클/대기이벤트 2010.09.15 14:19
Library Cache Pin에 대한 Self Deadlock을 아주 간단한게 만들어보겠습니다. 우선 다음과 같이 텅빈 프로시저 TEST_PROC1을 만듭니다.
create or replace procedure test_proc1 
is
begin
	null;
end;
/
그리고 아래의 PL/SQL 블록에서 TEST_PROC1을 실행하고, 곧이어 TEST_PROC1 프로시저를 컴파일합니다. 그러면 행(Hang) 상태에 빠져버립니다.
TPACK@ukja1120> begin
  2     test_proc1;
  3
  4     execute immediate 'alter procedure test_proc1 compile';
  5
  6  end;
  7  /

...
(Hang)
Active Session History를 통해 행 상태에 빠진 세션이 어떤 대기 상태인지 확인해보면 아래와 같이 library cache pin 이벤트를 무한 대기(는 아니고 타임아웃이 발생할 때까지)하고 있는 것을 알 수 있습니다. 그리고 Blocker와 SID가 동일한 것으로부터 Self Deadlock 상태에 빠진 것을 알 수 있습니다.
select * 
from (
	select
		h.session_id as sid,
		to_char(h.sample_time,'mi:ss') as sample_time,
		h.sql_id,
		(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
		event,
		blocking_session as blocker
	from
		v$active_session_history h
	where
		h.session_id = &sid
	order by h.sample_time desc			
) where rownum <= 20	
;					

 SID SAMPL SQL_ID        SQL_TEXT             EVENT         BLOCKER
---- ----- ------------- -------------------- ---------- ----------
 136 49:10                                    library ca        136
                                              che pin

 136 49:09                                    library ca        136
                                              che pin

 136 49:08                                    library ca        136
                                              che pin

 136 49:07                                    library ca        136
                                              che pin

 136 49:06                                    library ca        136
                                              che pin

 136 49:05                                    library ca        136
                                              che pin

 136 49:04                                    library ca        136
                                              che pin

 136 49:03                                    library ca        136
                                              che pin

 136 49:02                                    library ca        136
                                              che pin

 136 49:01                                    library ca        136
                                              che pin

 136 49:00                                    library ca        136
                                              che pin

 136 48:59                                    library ca        136
                                              che pin

 136 48:58                                    library ca        136
                                              che pin

 136 48:57                                    library ca        136
                                              che pin

 136 48:56                                    library ca        136
                                              che pin

 136 48:55                                    library ca        136
                                              che pin

 136 48:54                                    library ca        136
                                              che pin

 136 48:53                                    library ca        136
                                              che pin

 136 48:52                                    library ca        136
                                              che pin

 136 48:51                                    library ca        136
                                              che pin


20 rows selected.
티팩을 통해서 해당 세션 정보를 확인해보면 더욱 명확한 정보를 얻을 수 있습니다.
TPACK@ukja1120> select * from table(tpack.session_detail(136,'wait_detail'))

NAME                           VALUE
------------------------------ --------------------
SID                            136
Serial#                        2797
SPID                           5148
Program                        sqlplus.exe
Process                        5404:672
Module                         SQL*Plus
SQL ID                         9pbva4bn2m25b
Child No                       0
SQL Text                       alter procedure test
                               _proc1 compile

Status                         ACTIVE
Blocking Instance              1
Blocking Session               136
SQL Exec Start                 2010/09/15 13:45:34
Event                          library cache pin
Seq#                           130
P1(P1raw)                      384372376(0000000016
                               E90E98)

P2(P2raw)                      384372376(0000000016
                               DAB608)

P3(P3raw)                      384372376(00014F8500
                               010003)

Seconds in wait                40
State                          WAITING
Wait Event                     library cache pin
Holder SID                     136
Namespace                      TABLE/PROCEDURE
Object                         TEST_PROC1
Holding Mode                   2(S)
위의 결과를 잘 해석해보면 TEST_RPOC1 프로시저를 실행한 후 Library Cache Pin을 해제하지 않고 Shared Mode로 계속해서 점유하고 있는 것이 이유라는 것을 알 수 있습니다. TEST_PROC1을 컴파일하려면 Library Cache Pin을 Exclusive Mode로 획득해야하는데, Shared Mode의 Library Cache Pin이 아직 해제되지 않았기 때문에 대기하게 되는 것입니다.

왜 Shared Mode의 Library Cache Pin을 곧바로 해제하지 않는가가? 그 이유는 MOS 노트 264476.1에서 설명되어 있습니다.

When a stored procedure is called from another stored procedure/function/PLSQL block, 'LIBRARY CACHE PIN' held on called stored procedures is released only at the end of the calling stored procedure or PL/SQL block (not immediately after execution of called procedure ). So, any DDL operation on the called stored procedures will wait on 'LIBRARY CACHE PIN' until full pl/sql block is completed. However, you can execute called the procedure.

이 현상을 좀 더 확장해보면 최악의 경우 아래와 같이 불필요한 Library Cache Pin 대기를 유발할 수 있습니다.

-- Session #1은 실행 중
TPACK@ukja1120> begin
  2     test_proc1;
  3
  4     dbms_lock.sleep(120);
  5
  6  end;
  7  /
... (실행 중)

-- Session #2는 Session #1 때문에 컴파일을 못해서 대기
TPACK@ukja1120> alter procedure test_proc1 compile;
... (대기 중) 

-- Session #3은 Session #2 때문에 실행을 못하고 대기
TPACK@ukja1120> exec test_proc1;
... (대기 중)
저작자 표시
신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바