SQL> alter session set events '10046 trace name context forever ,level 12' ;    会话已更改。 --开启本会话的10046事件

SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true);      
PL/SQL 过程已成功完成。       -- 开启其他会话的10046事件

SQL> alter session set events '10046 trace name context off' ;

会话已更改。 --关闭本会话的10046事件

SQL> exec dbms_monitor.session_trace_disable(267,996); 
PL/SQL 过程已成功完成。            -- 关闭其他会话的10046事件

示例：

SQL> select userenv('sid') from dual;

USERENV('SID')

--------------

           144

SQL> alter session set events '10046 trace name context forever ,level 12' ;

Session altered.

SQL> select * from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/ECOAL/test1.dbf

         8 TEST1                            20971520       2560 AVAILABLE

           8 NO           0          0            0   20905984        2552

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/tempundotbs.dbf

         6 TEMPUNDOTBS                     104857600      12800 AVAILABLE

           6 NO           0          0            0  104792064       12792

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/test_undo.dbf

         5 TEST_UNDO                        41943040       5120 AVAILABLE

           5 NO           0          0            0   41877504        5112

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/users01.dbf

         4 USERS                             5242880        640 AVAILABLE

           4 YES 3.4360E+10    4194302          160    5177344         632

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/sysaux01.dbf

         3 SYSAUX                          304087040      37120 AVAILABLE

           3 YES 3.4360E+10    4194302         1280  304021504       37112

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/undotbs01.dbf

         2 UNDOTBS1                        377487360      46080 AVAILABLE

           2 YES 3.4360E+10    4194302          640  377421824       46072

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/system01.dbf

         1 SYSTEM                          943718400     115200 AVAILABLE

           1 YES 3.4360E+10    4194302         1280  943652864      115192

SYSTEM

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/ECOAL/test.dbf

         7 TEST                             20971520       2560 AVAILABLE

           7 NO           0          0            0   20905984        2552

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/catalog.dbf

         9 CATALOG_TBS                      20971520       2560 AVAILABLE

           9 NO           0          0            0   20905984        2552

ONLINE

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- ---------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

ONLINE_

-------

/dat/DBData/oradata/NACEC/liusm.dbf

        10 LIUSM                            10485760       1280 AVAILABLE

          10 NO           0          0            0   10420224        1272

ONLINE

10 rows selected.

SQL> select count(*) from te;

  COUNT(*)

----------

    100724

SQL> alter session set events '10046 trace name context off' ;

Session altered.

查看跟踪文件

[oracle@localhost ~]$ tail -n 300 7322.txt

TKPROF: Release 10.2.0.4.0 - Production on Wed Mar 26 09:34:19 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: nacec_ora_7322.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

********************************************************************************

select text

from

 view$ where rowid=:1

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          1          2          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.00       0.00          1          2          0           1

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: CHOOSE

Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=0 time=406 us)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       1        0.00          0.00

  SQL*Net message from client                     1       19.56         19.56

  db file sequential read                         1        0.00          0.00

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,

  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,

  spare1, spare2, avgcln

from

 hist_head$ where obj#=:1 and intcol#=:2

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      7      0.00       0.04          0          0          0           0

Fetch        7      0.00       0.00          0         14          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       15      0.00       0.04          0         14          0           0

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: RULE

Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=228 us)

      0   INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=163 us)(object id 257)

********************************************************************************

select *

from

 dba_data_files

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.04          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0         46         10          10

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.02       0.04          0         46         10          10

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows     Row Source Operation

-------  ---------------------------------------------------

     10  VIEW  DBA_DATA_FILES (cr=46 pr=0 pw=0 time=1510 us)

     10   UNION-ALL  (cr=46 pr=0 pw=0 time=1498 us)

      0    NESTED LOOPS  (cr=12 pr=0 pw=0 time=807 us)

      0     NESTED LOOPS  (cr=12 pr=0 pw=0 time=801 us)

      0      NESTED LOOPS  (cr=12 pr=0 pw=0 time=793 us)

     10       FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=548 us)

      0       TABLE ACCESS BY INDEX ROWID FILE$ (cr=12 pr=0 pw=0 time=237 us)

     10        INDEX UNIQUE SCAN I_FILE1 (cr=2 pr=0 pw=0 time=122 us)(object id 41)

      0      FIXED TABLE FIXED INDEX X$KCCFE (ind:1) (cr=0 pr=0 pw=0 time=0 us)

      0     TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us)

      0      INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us)(object id 7)

     10    NESTED LOOPS  (cr=34 pr=0 pw=0 time=645 us)

     10     NESTED LOOPS  (cr=34 pr=0 pw=0 time=1789 us)

     10      NESTED LOOPS  (cr=22 pr=0 pw=0 time=1476 us)

     10       NESTED LOOPS  (cr=0 pr=0 pw=0 time=945 us)

     10        FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=247 us)

     10        FIXED TABLE FIXED INDEX X$KTFBHC (ind:1) (cr=0 pr=0 pw=0 time=601 us)

     10       TABLE ACCESS CLUSTER TS$ (cr=22 pr=0 pw=0 time=482 us)

     10        INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=109 us)(object id 7)

     10      TABLE ACCESS BY INDEX ROWID FILE$ (cr=12 pr=0 pw=0 time=227 us)

     10       INDEX UNIQUE SCAN I_FILE1 (cr=2 pr=0 pw=0 time=88 us)(object id 41)

     10     FIXED TABLE FIXED INDEX X$KCCFE (ind:1) (cr=0 pr=0 pw=0 time=3362 us)

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

  control file sequential read                   48        0.00          0.00

  SQL*Net message from client                     2       12.84         12.84

********************************************************************************

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)

FROM

 (SELECT /*+ NO_PARALLEL("TE") FULL("TE") NO_PARALLEL_INDEX("TE") */ 1 AS C1,

  1 AS C2 FROM "TE" SAMPLE BLOCK (4.552023 , 1) SEED (1) "TE") SAMPLESUB

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.01       0.08        144         65          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.02       0.09        144         65          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=65 pr=144 pw=0 time=88595 us)

   4396   TABLE ACCESS SAMPLE TE (cr=65 pr=144 pw=0 time=60595 us)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                         5        0.04          0.04

  db file scattered read                         10        0.01          0.03

********************************************************************************

select count(*)

from

 te

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.01          0          2          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.07       0.16        547       1389          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.08       0.18        547       1391          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=1389 pr=547 pw=0 time=167759 us)

 100724   TABLE ACCESS FULL TE (cr=1389 pr=547 pw=0 time=302583 us)

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

  db file scattered read                         48        0.02          0.10

  db file sequential read                         3        0.00          0.00

  SQL*Net message from client                     2       88.75         88.75

********************************************************************************

alter session set events '10046 trace name context off'

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        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Parsing user id: SYS

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.01       0.06          0          2          0           0

Execute      3      0.00       0.00          0          0          0           0

Fetch        4      0.08       0.17        547       1435         10          11

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       10      0.10       0.23        547       1437         10          11

Misses in library cache during parse: 2

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       5        0.00          0.00

  SQL*Net message from client                     5       88.75        121.16

  db file sequential read                         3        0.00          0.00

  control file sequential read                   48        0.00          0.00

  db file scattered read                         48        0.02          0.10

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.00       0.00          0          0          0           0

Execute      9      0.00       0.04          0          0          0           0

Fetch        9      0.01       0.08        145         81          0           2

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       21      0.03       0.14        145         81          0           2

Misses in library cache during parse: 3

Misses in library cache during execute: 2

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                         6        0.04          0.04

  db file scattered read                         10        0.01          0.03

    3  user  SQL statements in session.

    9  internal SQL statements in session.

   12  SQL statements in session.

********************************************************************************

Trace file: nacec_ora_7322.trc

Trace file compatibility: 10.01.00

Sort options: default

       1  session in tracefile.

       3  user  SQL statements in trace file.

       9  internal SQL statements in trace file.

      12  SQL statements in trace file.

       6  unique SQL statements in trace file.

     351  lines in trace file.

     101  elapsed seconds in trace file.