--10046事件
Level 0  停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1  标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4  在level 1的基础上增加绑定变量的信息
level 8  在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息


--启用10046
--参数设置
timed_statistics=true:否则不会有CPU时间信息
user_dump_dest:trace文件路径
max_dump_file_size:trace文件最大大小
tracefile_identifier:trace文件标记


alter system set events '10046 trace name context forever,level 1';
alter session set events '10046 trace name context forever,level 1';
或
exec dbms_system.set_ev(sid,serial#,10046,level,'');
或
exec dbms_support.start_trace_in_session(sid => 1234,serial# => 56789,waits => true,binds => true);
exec dbms_support.stop_trace_in_session(sid => 1234,serial# => 56789);
或
exec dbms_monitor.session_trace_enable(session_id => 1234,serial_num => 56789,waits => true,binds => true);
exec dbms_monitor.session_trace_disable(session_id => 1234,serial_num => 56789);
或
select p.SPID os_process_id
  from v$session s,v$process p
 where s.PADDR = p.ADDR
   and s.USERNAME = upper('');
SQL> oradebug setospid os_process_id;
SQL> oradebug unlimit;
SQL> oradebug event 10046 trace name context forever, level 1;
SQL> oradebug event 10046 trace name context off;


--获取trace文件
select tracefile from v$process where addr = (select paddr from v$session where sid = (select distinct sid from v$mystat));


--trace文件解释转换
tkprof ORCL_ora_xxxx.trc ORCL_ora_yyyy.trc report.txt sys=no sort=fchela


TKPROF filename1 --输入文件，可以是多个文件联起来
       filename2 --格式化输出文件
       [ SORT  = option ] --排序
           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
       [ PRINT = integer ] --只列出输出文件的前interger个SQL语句，默认为所有的SQL
       [ AGGREGATE  = YES | NO ] --如果为no，则不对多个相同的SQL汇总
       [ INSERT = filename3 ] --SQL语句的一种，用于将跟踪文件的统计信息存储到数据库中。在TKPROF创建脚本后，在将结果输入到数据库中。
       [ SYS =  YES | NO ] --禁止或启用 将SYS用户所发布的SQL语句列表到输出文件中。
       [ TABLE = schema.table ] --在输出到输出文件前，用于存放临时表的用户名和表名。
       [ EXPLAIN = user/password ] --对每条SQL语句确定其执行规划。并将执行规划写到输出文件中。
       [ RECORD = filename] --
       [ WAIT =  ] --指定输出文件中包含不包含等待事件，默认是包含的。一般都取默认值。