SQL 커서의 구조에 기반해서 성능 문제를 분석하는 방법 소개
하나의 부모 커서와 하나 이상의 차일드 커서로 구성됨을 확인
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 커서의 구조에 기반해서 성능 문제를 분석하는 방법 소개
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바인드 변수조회 방법
==============================================================================================
-------------------------
-- 테스트환경 상황설정 --
-------------------------
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정보 안나옴
==============================================================================================