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

장시간 수행되는 쿼리의 바민드 값알아내기

사용 중인 SQL바인드 변수조회 방법

v$sql_bind_capture : 최초파스 때 15분 이후에 동일한 동일한 SQL이 들어올 때
trace 10046 level 4 or 12 : Trace를 수행할 때
SQL Monitor (11g) : 5초이상 수행되는 쿼리 또는 병렬쿼리 수행 시
에러스택덤프


==============================================================================================
-------------------------
-- 테스트환경 상황설정 --
-------------------------
SQL> -- session2
SQL> create table t1(c1, c2)
  2  as
  3  select 2, 2
  4    from dual
  5   union all
  6  select 1, 1
  7    from dual
  8  connect by level <= 1000;

테이블이 생성되었습니다.

SQL> exec dbms_application_info.set_client_info('session1');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> -- at first, run short running query
SQL> var b1 number ;
SQL> exec :b1 := 2;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> -- then long running query
SQL> var b1 number ;
SQL> exec :b1 := 1;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
SQL> select count (*)
  2    from t1 a , t1 b, t1 c
  3   where a.c1 = :b1
  4     and b.c1 = :b1
  5     and c.c1 = :b1;

  COUNT(*)
----------
1000000000

==============================================================================================



세션 #1 이 현재 사용 중인 바인드 변수의 값 검색
==============================================================================================
SQL> -- session2
SQL> col sid new_value sid1
SQL> col sql_id new_value sql_id1
SQL> col sql_child_number new_value child_number1;
SQL>
SQL> select sid, sql_id, sql_child_number
  2    from  v$session
  3   where client_info = 'session1';

       SID SQL_ID        SQL_CHILD_NUMBER
---------- ------------- ----------------
        16 acr6jrsr68gs6                0

SQL>
SQL> -- method 1 : bind capture
SQL> col name format a10
SQL> col value_string format a40
SQL> select name, value_string
  2    from v$sql_bind_capture
  3   where sql_id = '&sql_id1'
  4     and child_number = &child_number1;
구   3:  where sql_id = '&sql_id1'
신   3:  where sql_id = 'acr6jrsr68gs6'
구   4:    and child_number = &child_number1
신   4:    and child_number =          0

NAME       VALUE_STRING
---------- ----------------------------------------
:B1        2
:B1        2
:B1        2

SQL>
SQL> -- method3 : sql monitor
SQL> set long 10000
SQL>
SQL> select binds_xml
  2    from v$sql_monitor
  3   where sid = &sid1
  4     and sql_id = '&sql_id1';
구   3:  where sid = &sid1
신   3:  where sid =         16
구   4:    and sql_id = '&sql_id1'
신   4:    and sql_id = 'acr6jrsr68gs6'

BINDS_XML
--------------------------------------------------------------------------------

<binds><bind name=":B1" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="2">1</bind></binds>


SQL>
SQL> -- 에러 스택 덤프
SQL> conn / as sysdba
연결되었습니다.
SQL> col sid new_value sid1
SQL> select sid from v$session where client_info = 'session1' ;

       SID
----------
        16

SQL>
SQL> col spid new_value spid1
SQL> select spid
  2  from v$process
  3   where addr = (select paddr from v$session where sid = &sid1);
구   3:  where addr = (select paddr from v$session where sid = &sid1)
신   3:  where addr = (select paddr from v$session where sid =         16)

SPID
------------------------
5468

SQL>
SQL> oradebug setospid &spid1
Oracle pid: 26, Windows thread id: 5468, image: ORACLE.EXE (SHAD)
SQL>
SQL> oradebug dump callstack 3
명령문을 처리했습니다.
SQL>
SQL> oradebug tracefile_name
c:\app\woong\diag\rdbms\orcl\orcl\trace\orcl_ora_5468.trc
SQL>

-- 11.2.0.2/10.2.0.1 window/aix버전 모두 current cursor정보 안나옴

==============================================================================================