ALTER SESSION SET timed_statistics=true
ALTER SESSION SET max_dump_file_size=unlimited
ALTER SESSION SET tracefile_identifier='my_trace_session'
实验：设置10046事件
alter session set events '10046 trace name context forever ,level 12';
select *
from (select deptno,
ename,
sal,
row_number() over(partition by deptno order by sal desc) rn
from emp)
where rn <= 2;
alter session set events '10046 trace name context off';
当前会话查看生成的trace 文件
select value from v$diag_info where name='Default Trace File';
使用工具格式化trace文件
[oracle@rac1 trace]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor 格式化之后的trace
TKPROF: Release 11.2.0.4.0 - Development on Fri Aug 25 15:51:18 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
********************************************************************************
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
select *
from (select deptno,
ename,
sal,
row_number() over(partition by deptno order by sal desc) rn
from emp)
where rn <= 2
Misses in library cache during parse: 1----硬解析
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1
---访问路径
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
6 6 6 VIEW (cr=6 pr=0 pw=0 time=293 us cost=4 size=644 card=14)
10 10 10 WINDOW SORT PUSHED RANK (cr=6 pr=0 pw=0 time=290 us cost=4 size=182 card=14)
14 14 14 TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=238 us cost=3 size=182 card=14)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 7.60 7.60
********************************************************************************
SQL ID: 5dt9w7dmjqp7a Plan Hash: 0----c此处有SQL_ID
alter session set events '10046 trace name context off'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 16.10 23.71
裸trace文件
PARSING IN CURSOR 部分：
Len: 被解析SQL的长度
Dep: 产生递归SQL的深度
Uid：user id
Otc: Oracle command type 命令的类型
Lid: 私有用户id
Tim：时间戳
Hv： hash value
Ad：SQL address
PARSE,EXEC,FETCH 部分
C: 消耗的CPU time
Trace file /u01/app/oracle/diag/rdbms/prod/PROD1/trace/PROD1_ora_32508.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/11.2.0
System name: Linux
Node name: rac1
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: PROD1
Redo thread mounted by this instance: 1
Oracle process number: 50
Unix process pid: 32508, image: oracle@rac1 (TNS V1-V3)
