zx@ORCL>alter session set events '10046 trace name context forever,level 12';
Session altered.
zx@ORCL>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
......
14 rows selected.
zx@ORCL>alter session set events '10046 trace name context off';
Session altered.
zx@ORCL>select value from v$diag_info where;
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.trc
zx@ORCL>!
[oracle@rhel6 trace]$ tkprof /u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.trc 10046.trc
TKPROF: Release 11.2.0.4.0 - Development on Thu Feb 16 21:38:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@rhel6 trace]$ cat 10046.trc
TKPROF: Release 11.2.0.4.0 - Development on Thu Feb 16 21:38:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: /u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.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: ggqns3c1jz86c Plan Hash: 3956160932
select *
from
scott.emp
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 2 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 14
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=81 us cost=3 size=532 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 0.00 0.00
********************************************************************************
......省略部分内容
参考文档：https://blogs.oracle.com/askmaclean/entry/maclean教你读oracle_10046_sql_trace
MOS文档EVENT: 10046 "enable SQL statement tracing (including binds/waits)" (文档 ID 21154.1)
How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (文档 ID 376442.1)