이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=22052903&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

SQL 분석 사례

SQL 커서의 구조에 기반해서 성능 문제를 분석하는 방법 소개

부모 커서와 차일드 커서를 분석하는 방법
X$KGLOB 테 이블을 사용하는 방법
가짜 커서를 분석하는 방법

부모커서와 차일드 커서

하나의 부모 커서와 하나 이상의 차일드 커서로 구성됨을 확인

SQL> create table t1
  2  as
  3  select mod(level, 100 ) as c1
  4  from dual
  5  connect by level <= 100000 ;

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

SQL>
SQL> -- OPTIMIZER_INDEX_COST_ADJ 파라미터의 값을 10000, 100, 50, 1 로 변경하면서 동일한 SQL 문장을 수행합니다
SQL>
SQL> alter session set optimizer_index_cost_adj = 10000 ;

세션이 변경되었습니다.

SQL>
SQL> select count (*) from t1 where c1 = 1;

  COUNT(*)
----------
      1000

SQL>
SQL> alter session set optimizer_index_cost_adj = 100;

세션이 변경되었습니다.

SQL>
SQL> select count (*) from t1 where c1 = 1;

  COUNT(*)
----------
      1000

SQL>
SQL> alter session set optimizer_index_cost_adj = 50;

세션이 변경되었습니다.

SQL>
SQL> select count (*) from t1 where c1 = 1;

  COUNT(*)
----------
      1000

SQL>
SQL> alter session set optimizer_index_cost_adj = 1;

세션이 변경되었습니다.

SQL>
SQL> select count (*) from t1 where c1 = 1;

  COUNT(*)
----------
      1000

SQL>
SQL> -- 직전 SQL_ID 확인
SQL> col prev_sql_id new_value sql_id
SQL> select prev_sql_id from v$session where sid = userenv('sid');

PREV_SQL_ID
-------------
58ccx36sbcnw2

SQL>
SQL> -- SQL_ID로 SQL문장과 해당정보를 확인
SQL> select sql_id,
  2         version_count,
  3         substr(sql_text, 1, 40) as sql_text
  4  from  v$sqlarea
  5  where sql_id = '&sql_id';
구   5: where sql_id = '&sql_id'
신   5: where sql_id = '58ccx36sbcnw2'

SQL_ID        VERSION_COUNT SQL_TEXT
------------- ------------- --------------------------------------------------------------------------------
58ccx36sbcnw2             4 select count (*) from t1 where c1 = 1

SQL>
SQL>
SQL> -- VERSION_COUNT = 4 (동일한 SQL 문에 대해 4개의 버전이 있음을 의미)
SQL> -- v$sql을 통해 차일드 커서를 확인할 수 있으며 특정파라미터값을 추적할 수 있음.
SQL>
SQL> select s.sql_id,
  2         s.child_number,
  3        (select value
  4           from v$sql_optimizer_env e
  5          where e.sql_id = s.sql_id
  6            and e.child_number = s.child_number
  7            and e.name = 'optimizer_index_cost_adj') as oica,
  8         substr(s.sql_text, 1, 40) as sql_text,
  9         plan_hash_value
 10   from v$sql s
 11  where s.sql_id = '&sql_id';
구  11: where s.sql_id = '&sql_id'
신  11: where s.sql_id = '58ccx36sbcnw2'

SQL_ID        CHILD_NUMBER OICA     SQL_TEXT                             
------------- ------------ -------- -------------------------------------
58ccx36sbcnw2            0 10000    select count (*) from t1 where c1 = 1
58ccx36sbcnw2            1 100      select count (*) from t1 where c1 = 1
58ccx36sbcnw2            2 50       select count (*) from t1 where c1 = 1
58ccx36sbcnw2            3 1        select count (*) from t1 where c1 = 1

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

SQL 커서의 구조에 기반해서 성능 문제를 분석하는 방법 소개

부모 커서와 차일드 커서를 분석하는 방법
X$KGLOB 테 이블을 사용하는 방법
가짜 커서를 분석하는 방법

X$KGLOB

V$SQLAREA 뷰(부모 커서)와 V$SQL 뷰(자식 커서)를 통해 얻을 수 없을 경우
V$SQL 뷰의 베이스 테이 블인 X$KGLOB 테이블을 사용

X$KGLOB 테이블을 사용하는 방법

=========================================================================
SQL> -------------------------------------
SQL> -- long_parsing을 수행하는 SQL작성 --
SQL> -------------------------------------
SQL> set echo off
SQL> set termout off
SQL> set pagesize 0
SQL> set heading off
SQL> set verify off
SQL> set feedback off
SQL> set serveroutput on
SQL> set timing off
SQL> set scan off
SQL>
SQL> var v_sql clob;
SQL>
SQL> begin
  2    :v_sql := 'select count(*) from ';
  3
  4    for r in (select t1.table_name
  5                from user_tables t1,
  6                     user_tables t2
  7               where rownum <= 302
  8                 and t1.table_name not like '%$%') loop
  9        :v_sql := :v_sql || r.table_name || ', ';
 10    end loop;
 11  end;
 12  /
SQL>
SQL> spool long_parse.sql
SQL> exec dbms_output.put_line(:v_sql);
select count(*) from 읽을 수 있는 모든 테이블들을 cartesian product...
SQL> spool off
SQL>
SQL> set echo on
SQL> set termout on
SQL> set pagesize 100
SQL> set heading on
SQL> set verify on
SQL> set feedback on
SQL> set serveroutput off
SQL> set timing on
SQL> set scan on
SQL>
SQL> ed long_parse


SQL> ---------------------------
SQL> -- long_parsing.sql 수행 --
SQL> ---------------------------
SQL>
SQL> -- session #1
SQL> exec dbms_application_info.set_client_info('session1');

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

SQL>
SQL> @long_parse


SQL> ---------------------------
SQL> -- long_parsing.sql 추적 --
SQL> ---------------------------
SQL> -- session #2
SQL> col sid new_value sid1
SQL> select sid from v$session where client_info = 'session1';

       SID
----------
       158

SQL> -- 10g
SQL> col sql_id new_value sql_id
SQL> col sql_text format a30
SQL>
SQL> select /*+ no_query_transformation leading(x) use_nl(s) */
  2         sql_id,
  3         sql_child_number,
  4         logical_read,
  5         substr(sql_text, 1, 60) as sql_text
  6  from (select level
  7          from dual
  8         where WOONG_PKG.SLEEP(1) = 1
  9         connect by level <= 100) x,
 10       (select s.sql_id,
 11               s.sql_child_number,
 12               (select sql_text
 13                  from v$sql x
 14                 where x.sql_id = s.sql_id
 15                   and x.child_number = s.sql_child_number) sql_text,
 16               (select x.value
 17                  from v$sesstat x,
 18                       v$statname n
 19                 where x.sid = s.sid
 20                   and x.statistic# = n.statistic#
 21                   and n .name = 'session_logical_reads') as logical_read
 22          from v$session s
 23         where s.sid = &sid1
 24       );
구  23:        where s.sid = &sid1
신  23:        where s.sid =        158

SQL_ID        SQL_CHILD_NUMBER LOGICAL_READ SQL_TEXT
------------- ---------------- ------------ ------------------------------
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
88vqr4bx7c8r8                0
...

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


SQL> ------------------------------
SQL> -- 안보이는 10g를 위한 방법 --
SQL> ------------------------------
SQL> select kglnaobj
  2  from x$kglob
  3  where kglobt03 = '&sql_id';
구   3: where kglobt03 = '&sql_id'
신   3: where kglobt03 = '88vqr4bx7c8r8'

KGLNAOBJ
--------------------------------------------------------------------------------
select count(*) from     DUAL ,SYSTEM_PRIVILEGE_MAP ,TABLE_PRIVILEGE_MAP ,STMT_A
UDIT_OPTION_MAP      ,AUDIT_ACTIONS
,OGIS_SPATIAL_REFERENCE_SYSTEMS ,OGIS_GEOMETRY_COLUMNS ,SDO_UNITS_OF_MEASURE ,SD
O_PRIME_MERIDIANS ,SDO_ELLIPSOIDS ,SDO_DATUMS ,SDO_COORD_SYS ,SDO_COORD_AXIS_NAM
ES ,SDO_COORD_AXES ,SDO_COORD_REF_SYS ,SDO_COORD_OP_METHODS ,SDO_COORD_OPS ,SDO_
PREFERRED_OPS_SYSTEM ,SDO_PREFERRED_OPS_USER ,SDO_COORD_OP_PATHS ,SDO_COORD_OP_P
ARAMS ,SDO_COORD_OP_PARAM_USE ,SDO_COORD_OP_PARAM_VALS  ,SDO_PROJECTIONS_OLD_SNA
PSHOT ,SDO_ELLIPSOIDS_OLD_SNAPSHOT ,SDO_DATUMS_OLD_SNAPSHOT    ,SDO_XML_SCHEMAS
,MGMT_NOTIFY_QTABLE ,AQ$_MGMT_NOTIFY_QTABLE_S  ,MGMT_VERSIONS ,MGMT_TABLE_SIZES
,MGMT_INDEX_SIZES ,MGMT_REBUILD_INDEXES ,MGMT_LICENSES ,MGMT_AVAILABILITY ,MGMT_
CURRENT_AVAILABILITY ,MGMT_AVAILABILITY_MARKER ,MGMT_MASTER_AGENT ,MGMT_MASTER_C
HANGED_CALLBACK ,MGMT_TARGET_BASELINES ,MGMT_TARGET_BASELINES_DATA ,MGMT_METRICS
,MGMT_METRICS_EXT ,MGMT_TARGET_TYPES 

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

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

사용 중인 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정보 안나옴

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

문서정보

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=22052903&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

SQL 분석 사례

SQL 분석 사례

부모커서와 차일드 커서

X$KGLOB

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

문서정보

Labels