[ORACE] Oracle trace 之一 trace

Oracle trace
1 前提
    有足够的CPU时间
    有够的trace空间
    确保trace的文件有足够的磁盘空间

2 级别

    system:     alter system set sql_trace=true;  --很少使用
    session:    alter session set sql_trace=true;

3 方法
3.1trace当前会话:

    alter session set sql_trace=true;     --启用当前session trace
    select count(*) dba_objects;        --此SQL操作被追踪
    alter session set sql_trace=false;  --结束追踪
    --查看trace文件
    SELECT d.VALUE || '/' ||  RTRIM (i.INSTANCE, CHR (0)) || '_ora_' || p.spid || '.trc' trace_file_name FROM
     (SELECT p.spid FROM v$mystat m, v$session s, v$process p  WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
     (SELECT t.INSTANCE FROM v$thread t, v$parameter v  WHERE v.NAME = 'thread'  AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
     (select value from v$diag_info where name='Diag Trace') d ;

    
3.2 trace其它用户会话:

SQL> select sid,serial#,username from v$session where username is not null;

       SID    SERIAL# USERNAME
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
     3    62469 SYS
       214    20158 C##SAPR3
       217    44968 C##SAPR3
       395    39248 C##SAPR3
       596    21546 SYS
    execute dbms_system.set_sql_trace_in_session(395,39248,true)  ---启用用户session trace
    execute dbms_system.set_sql_trace_in_session(395,39248,false) ---结束追用户session trace

      
3.3指定SQL_ID

    alter system set events 'SQL_TRACE [SQL:&&SQL_ID] bind=true,wait=true';

4. 使用tkprof 查看