1.对指定session进行trace
使用oradebug进行trace
获取监控的session spid
[oracle@myoracle ~]$ ora active
\n=============Thu Nov 5 22:52:54 CST 2020===================\n
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
SID S# SPID MACHINE PROGRAM EVENT P123 WT SQL_ID USERNAME
------ ------ ---------- ------------ --------------- ------------------------------ ------------------ ---- ------------------ ----------
192 35 3993 myoracle sqlplus@myoracl PL/SQL lock timer 0/0/0 0 70wz09hkxrcux DINGJUN123
e (TNS V1-V3)
--oradebug
sys@ORADB> oradebug setospid 3993
Oracle pid: 23, Unix process pid: 3993, image: oracle@myoracle (TNS V1-V3)
sys@ORADB> oradebug unlimit
Statement processed.
sys@ORADB> oradebug event 10046 trace name context forever,level 12;
Statement processed.
sys@ORADB> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_3993.trc
--查看
tail -10f /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_3993.trc
--结束
sys@ORADB> oradebug event 10046 trace name context off
sys@ORADB> !tkprof /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_3993.trc oradb_ora_3993.txt
--tkprof解析
[oracle@myoracle ~]$ more oradb_ora_3993.txt
TKPROF: Release 11.2.0.3.0 - Development on Thu Nov 5 22:57:02 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_3993.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: bunvx480ynf57 Plan Hash: 1388734953
SELECT 1
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 62 0.00 0.00 0 0 0 0
Fetch 62 0.00 0.00 0 0 0 62
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 124 0.00 0.00 0 0 0 62
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90 (recursive depth: 1)
********************************************************************************
declare
vi number;
begin
for i in 1 .. 100 loop
select 1 into vi from dual;
dbms_lock.sleep(2);
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.06 200.13 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.06 200.13 0 0 0 1
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PL/SQL lock timer 63 2.00 126.05
SQL*Net message to client 1 0.00 0.00
********************************************************************************
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.06 200.13 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.06 200.13 0 0 0 1
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PL/SQL lock timer 63 2.00 126.05
SQL*Net message to client 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 62 0.00 0.00 0 0 0 0
Fetch 62 0.00 0.00 0 0 0 62
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 124 0.00 0.00 0 0 0 62
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_3993.trc
Trace file compatibility: 11.1.0.7
Sort options: default
2 sessions in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
460 lines in trace file.
124 elapsed seconds in trace file.
2.对指定sql进行trace
对特定SQL进行trace可以使用：
alter system set events 'sql_trace [sql: &sql_id] level 12';
alter system set events 'sql_trace [sql: &sql_id] off';
这样更加便利。
