11g이전 버전에서는 SQL_TRACE, 10046 등의 event는 session이나 instance level로만 설정할 수 있었습니다.
특정 SQL에 대한 dump를 수행하려면 해당 SQL을 수행하는 세션이나 program을 찾아서
sqlplus나 oradebug 등을 통해 sql_trace, 10046 event 를 걸어야 했죠.
다음의 SQL_ID에 대해 event 거는 방안은 sql monitoring에 대단히 유용해 보이네요.
1. SQL_ID 찾기
SQL> select sql_id,sql_text from v$sql where sql_text like 'select /*+ test */%';
SQL_ID SQL_TEXT
---------------- -----------------------------------------------
6vnyysxnuud8b select /*+ test */ * from scott.emp
2. 해당 SQL_ID에 sql_trace event 걸기
SQL> alter session set events 'sql_trace [sql:sql_id=6vnyysxnuud8b]';
Session altered.
3. SQL 수행
SQL> select * from scott.dept;
SQL> select * from scott.emp;
SQL> select /*+ test */ * from scott.emp;
*** 2011-01-27 13:03:14.561
*** SESSION ID:(10979.4995) 2011-01-27 13:03:14.561
*** CLIENT ID:() 2011-01-27 13:03:14.561
*** SERVICE NAME:(SYS$USERS) 2011-01-27 13:03:14.561
*** MODULE NAME:(SQL*Plus) 2011-01-27 13:03:14.561
*** ACTION NAME:() 2011-01-27 13:03:14.561
=====================
PARSING IN CURSOR #5 len=97 dep=1 uid=0 oct=3 lid=0 tim=5959428212907 hv=2759248297 ad='c0000000c0ee11a8' sqlid='aa35g82k7dkd9'
select binaryDefVal, length(binaryDefVal) from ecol$ where tabobj# = :1 and colnum = :2
END OF STMT
PARSE #5:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5959428212907
EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2830718548,tim=5959428244106
FETCH #5:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=2830718548,tim=5959428244106
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=123 op='TABLE ACCESS BY INDEX ROWID ECOL$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=2028 card=1)'
STAT #5 id=2 cnt=1 pid=1 pos=1 obj=126 op='INDEX RANGE SCAN ECOL_IX1 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
CLOSE #5:c=0,e=0,dep=1,type=3,tim=5959428244106
=====================
PARSING IN CURSOR #2 len=35 dep=0 uid=5 oct=3 lid=5 tim=5959428244106 hv=1772958987 ad='c0000000c0ed2e48' sqlid='6vnyysxnuud8b'
select /*+ test */ * from scott.emp
END OF STMT
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2872589290,tim=5959428244106
FETCH #2:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=2872589290,tim=5959428244106
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=2872589290,tim=5959428244106
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=12,dep=0,og=1,plh=2872589290,tim=5959428244106
STAT #2 id=1 cnt=28 pid=0 pos=1 obj=36463 op='TABLE ACCESS FULL EMP (cr=9 pr=0 pw=0 time=0 us cost=3 size=39673 card=409)'
*** 2011-01-27 13:03:25.877
CLOSE #2:c=0,e=0,dep=0,type=0,tim=5959439294741
> tkprof TEST_ora_17091.trc TEST_ora_17091.out explain=scott/tiger sys=no
TKPROF: Release 11.1.0.7.0 - Production on Thu Jan 27 13:05:37 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
TKPROF: Release 11.1.0.7.0 - Production on Thu Jan 27 13:05:37 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: TEST_ora_17091.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 6vnyysxnuud8b
Plan Hash: 2872589290
select /*+ test */ *
from
scott.emp
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 9 0 28
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5 (SYSTEM)
Rows Row Source Operation
------- ---------------------------------------------------
28 TABLE ACCESS FULL EMP (cr=9 pr=0 pw=0 time=0 us cost=3 size=39673 card=409)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
28 TABLE ACCESS (FULL) OF 'EMP' (TABLE)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 9 0 28
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
1 user SQL statements in session.
1 internal SQL statements in session.
2 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: TEST_ora_17091.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
1 user SQL statements in trace file.
1 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
44 lines in trace file.
0 elapsed seconds in trace file.
'Oracle Database' 카테고리의 다른 글
| ORACLE v$sql_bind_capture에서 TIMESTAMP값이 안보이는 문제 workaround (0) | 2011.02.08 |
|---|---|
| Oracle client program의 모듈명, Action명 정의 방법 (0) | 2011.02.08 |
| Oracle의 특정 process가 CPU를 많이 먹을때: kill 대신 suspend (0) | 2011.01.05 |
| Oracle UTL_FILE을 이용한 데이터 Unloading의 간단한 예제 (0) | 2010.12.28 |
| Oracle 11g의 새로운 HINT : IGNORE_ROW_ON_DUPKEY_INDEX (0) | 2010.12.23 |