SQL语句优化实践之一SQL_TRACE

SQL语句优化实践之一SQL_TRACE
 
环境：在PL/sql上调试数据
Pl/sql developer工具连接实例后即作为一个用户进程占用一个session；
 
select * from  v$session t  where t.PROGRAM='plsqldev.exe' and  t.USERNAME='DZJC'
查询结果显示了几个关键的字段
SADDR   RAW(4)  Session address 内存地址
SID  NUMBER  Session identifier  唯一标识
SERIAL#  NUMBER 
Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID 
STATUS  VARCHAR2(8) 
Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA), SNIPED (session inactive, waiting on the client) 当期状态
MACHINE  VARCHAR2(64)  Operating system machine name 
PROGRAM  VARCHAR2(48)  Operating system program name 应用程序名称。
打开窗口依次执行如下命令：
启动当期sesison级别 跟踪
启动SQL跟踪
实例级别
Alter system set sql_trace=true  scope=both;
当期session级别
Alter session set sql_trace=true;
或
Execute dbms_session.set_sql_trace(true);
EXECUTE dbms_system.set_sql_trace_in_session
(session_id, serial_id, true);
 
 
 
 
 
 
 
 
 
Alter  session  set sql_trace=true;
执行待调试的语句
select c.code, nvl(b.con, 0)
  from t_sys_codemap c,
       (select COUNT(1) con, m.bljg
          from V_JC_XZXK_BUSI_TIMELIMIT t, V_JC_XZXK_BUSIINDEX m
         where t.busiindexid = m.ywlsh
           and t.LIMITTYPE = 1
           and trunc(m.tjsj) >= trunc(SYSDATE, 'year')
&nb