跟踪Oracle session 诊断问题

1、获取session相关信息
select sid,SERIAL#,USERNAME ,OSUSER,MACHINE,PROCESS,MACHINE,TERMINAL,PROGRAM,event, LOGON_TIME,sql_id  from v$session where program='QLYGDataExchange.exe';
select sql_id from v$session where program='QLYGDataExchange.exe';
select SQL_FULLTEXT ,sql_id from v$sql where sql_id in (select sql_id from v$session where program='QLYGDataExchange.exe')

2、根据查出的信息设置跟踪
参考：http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm

exec dbms_system.set_sql_trace_in_session(9,437,true);
exec dbms_system.set_sql_trace_in_session(9,437,false);

查询跟踪文件路径：

SELECT    d.VALUE
       || '/'
       || LOWER (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$parameter
         WHERE NAME = 'user_dump_dest') d

使用tkprof 处理跟踪文件，查看结果文件寻找问题线索。

3、查看sql语句执行计划
参考：http://space.itpub.net/354732/viewspace-606354
select * from table(dbms_xplan.display_cursor('5m5x2xs9w5gnv', '0', 'ALL ALLSTATS'));
select * from table(dbms_xplan.display_cursor('5m5x2xs9w5gnv', '1', 'ALL ALLSTATS'));


4、影响执行计划
查找要用的索引名字：
select c.column_name,t.OWNER,t.CONSTRAINT_NAME,t.INDEX_NAME from dba_cons_columns c,dba_constraints t
where c.constraint_name = t.constraint_name and t.constraint_type='P'
and t.table_name='INF_APPLY_PROCESS';

使用hit影响执行计划：
UPDATE /*+ index(INF_APPLY_PROCESS SYS_C0011007)*/ DZJC.INF_APPLY_PROCESS SET SYNC_SIGN=:SYNC_SIGN,
READ_DATE=:READ_DATE
WHERE DZJC.INF_APPLY_PROCESS.NO = :NO0