태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Long Running Query의 바인드 값 알아내기

오라클 2010.07.27 13:53
이런 질문을 받았습니다.
  • 현재 장시간 수행되는 쿼리가 있습니다. 이 쿼리가 사용한 바인드 값을 알아낼 수 있나요?
종종 이런 요구사항이 생기게 됩니다. 아주 단순해 보이는 이 요구사항을 만족시키기 어려운 이유는 이것입니다.
  • 바인드 값은 프로세스의 값이다. 즉 PGA에 존재하는 값이다. 개별 프로세스의 메모리 영역에 존재하는 값이므로 이것을 직접 조회하는 것은 불가능하다.
즉, 오라클의 아키텍처로 인해 V$ 뷰를 조회하는 것과 같은 일반적인 방법으로는 불가능합니다. V$SQL_BIND_CAPTURE 뷰는 특정 SQL이 사용했던 바인드 정보를 보여주지만 1) 15분마다 캡처하며, 2) WHERE 절에 사용된 바인드 변수만 캡처하며, 3) 바인드 변수가 많을 경우 일부만 캡처하는 등의 문제가 있습니다. 무엇보다 현재의 값을 캡처하지 않는다는 제약이 걸림돌입니다.

레벨 4로 10046 이벤트를 수행하는 방법도 있지만, 현재 수행 중인 쿼리인 경우에는 바인드 값의 지정 단계가 이미 지났을 것이기 때문에 바인드 값이 기록되지 않는다는 문제가 있습니다.

이때 사용 가능한 방법이 Error Stack Dump입니다. 간단한 예를 볼까요?

1. Session #1에서 다음과 같이 장시간(30초) 수행되는 쿼리를 실행합니다. 바인드 변수 b1을 선언하고 값으로 'X'를 지정합니다.

-- session #1
create or replace function fsleep(p1 number) return number
is
begin
	dbms_lock.sleep(p1);
	return 1;
end;
/

exec dbms_application_info.set_client_info('SESSION1');

var b1 varchar2(1);
exec :b1 := 'X';

select fsleep(30) from dual where dummy =  :b1;
...
2. Session #1에서 Long Running Query를 수행하는 동안 Session #2에서 oradebug를 이용해 Error Stack Dump(Level 3)를 남깁니다.
conn sys/oracle@ukja1106 as sysdba

col pid new_value pid
select spid as pid from v$process 
where addr = (select paddr from v$session where client_info = 'SESSION1');

oradebug setospid &pid

oradebug dump errorstack 3
oradebug tracefile_name
3. 트레이스 파일을 열어보면 이런 부분이 있습니다. Cursor 번호 10번이 현재 실행중인 Cursor라는 의미입니다.
----- Session Cursor Dump -----
Current cursor: 10, pgadep=0
...
이 값을 이용해 트레이스 파일 내에서 Cursor#10을 찾으면 됩니다.
----------------------------------------
Cursor#10(0x0C731064) state=FETCH curiob=0x0D7809E4
 curflg=46 fl2=0 par=0x00000000 ses=0x345175D4
----- Dump Cursor sql_id=aanj0fskcmr4t xsc=0x0D7809E4 cur=0x0C731064 -----
Dump Parent Cursor sql_id=aanj0fskcmr4t phd=0x26D23F78 plk=0x3487F4A8
 sqltxt(0x26D23F78)=select fsleep(30) from dual where dummy =  :b1
  hash=227eaca42a9d50c9a552207624c9dc99
  parent=0x21F95C00 maxchild=02 plk=0x3487F4A8 ppn=n
cursor instantiation=0x0D7809E4 used=1279600018 exec_id=16777219 exec=1
 child#1(0x2C49E808) pcs=0x21F9594C
  clk=0x3487A930 ci=0x21F94710 pn=0x349254BC ctx=0x24DB5C80
 kgsccflg=0 llk[0x0D7809E8,0x0D7809E8] idx=0
 xscflg=c0110476 fl2=5200009 fl3=42222008 fl4=100
----- Bind Byte Code (IN) -----
  Opcode = 2   Bind Twotask Scalar Sql In (may be out) Copy 
  oacdef = 21F944CC   Offsi = 36, Offsi = 0
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=846 siz=32 off=0
  kxsbbbfp=0ceb0e3c  bln=32  avl=01  flg=05
  value="X"
4. 티팩을 사용하면 위의 과정을 어느 정도 자동화할 수 있습니다. 티팩에서 제공하는 SQL Detail Report에서 CURSOR_DUMP 옵션을 사용하면 위의 과정을 자동화해서 그 결과를 보여줍니다.

Session #2에서 다음과 같이 티팩의 SQL Detail Report를 생성합니다.

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

col name format a15
col value format a60
set pages 10000
set long 100000
select * from table(tpack.sql_detail(session_id=>&sid, options=>'cursor_dump'));
아래와 같이 현재 Cursor를 찾아서 출력해줍니다.
NAME            VALUE
--------------- ------------------------------------------------------------
SQL ID          aanj0fskcmr4t
Child Number    1
SQL Text        select fsleep(30) from dual where dummy =  :b1
Hash Value      617208985
Address         26D23F78
Plan Hash Value 272002086
Bind Sensitive  Y
Bind Aware      N
Cursor Dump     Cursor#7(0x0C730F44) state=FETCH curiob=0x0D7809E4
                 curflg=46 fl2=0 par=0x00000000 ses=0x345175D4
                ----- Dump Cursor sql_id=aanj0fskcmr4t xsc=0x0D7809E4 cur=0x
                0C730F44 -----
                Dump Parent Cursor sql_id=aanj0fskcmr4t phd=0x26D23F78 plk=0
                x3487F4A8
                 sqltxt(0x26D23F78)=select fsleep(30) from dual where dummy
                =  :b1
                  hash=227eaca42a9d50c9a552207624c9dc99
                  parent=0x21F95C00 maxchild=02 plk=0x3487F4A8 ppn=n
                cursor instantiation=0x0D7809E4 used=1279600616 exec_id=1677
                7221 exec=2
                 child#1(0x2C49E808) pcs=0x21F9594C
                  clk=0x3487A930 ci=0x21F94710 pn=0x349254BC ctx=0x24DB5C80
                 kgsccflg=1 llk[0x0D7809E8,0x0D7809E8] idx=9a
                 xscflg=c0110476 fl2=5200009 fl3=42222008 fl4=100
                ----- Bind Byte Code (IN) -----
                  Opcode = 2   Bind Twotask Scalar Sql In (may be out) Copy

                  oacdef = 21F944CC   Offsi = 36, Offsi = 0
                ----- Bind Info (kkscoacd) -----
                 Bind#0
                  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
                  oacflg=03 fl2=1000000 frm=01 csi=846 siz=32 off=0
                  kxsbbbfp=0d776cc4  bln=32  avl=01  flg=05
                  value="X"
                 Frames pfr 0x0DA46A30 siz=2904 efr 0x0DA46858 siz=2528
꼭 필요한 경우에 활용할 수 있으면 아주 효과적인 방법이 될 수 있겠습니다.

요즘은 Jennfier같은 Java 모니터링 툴이 보편화되면서 바인드 변수의 값을 보는 것이 가능해졌습니다. 하지만 이런 툴들도 JDBC를 통해 호출된 경우에만 모니터링 가능하기 때문에 여전히 제약이 있다고 하겠습니다.

저작자 표시
신고
tags : ,
Trackback 0 : Comment 0

Write a comment

티스토리 툴바