oracle会话跟踪-定位执行慢的sql

--开启oracle会话跟踪，定位sql执行效率问题
/**
https://blog.csdn.net/tianlesoftware/article/details/5632003
tkprof是一个用于分析Oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。
如果一个系统的执行效率比较低，一个比较好的方法是通过跟踪用户的会话并且使用Tkprof工具使用排序功能格式化输出，
从而找出有问题的SQL语句。
*/
--追踪会话表
select sid,serial#,machine,osuser,paddr from v$session where username = user and module like 'JDBC%';
--查询日志文件 当前会话
--F:\APP\ADMINISTRATOR\diag\rdbms\asupp11\asupp11\trace\asupp11_ora_4332.trc
select tracefile from v$process where addr in (
  select paddr from v$session where sid = 15
);
--开启会话跟踪，使用sqlplus命令登录数据库后执行
execute  dbms_system.set_sql_trace_in_session(15, 9381, true);
--关闭会话跟踪
execute  dbms_system.set_sql_trace_in_session(15, 9381, false);
--解释会话跟踪结果   F:\analyst  在cmd命令行中执行，AGGREGATE  如果= NO，则不对多个相同的SQL进行汇总。EXPLAIN     对每条SQL 语句确定其执行规划。并将执行规划写到输出文件中。 alter session set timed_statistics=True; 生成的文件将把最消耗时间的sql放在最前面显示
tkprof F:\APP\ADMINISTRATOR\diag\rdbms\asupp11\asupp11\trace\asupp11_ora_4332.trc F:\analyst\analog.txt explain=testuser/passwd@orcl AGGREGATE=no
 

--定位到的执行效率慢的sql：

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     19.76      19.78          0    1404677          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     19.82      19.83          0    1404677          0           1

elapsed耗时=cpu耗时+cpu等待耗时

这个sql查询耗时19.83秒，查询过程中查询了1404677行数据，是导致查询速度慢的主要原因。

找到sql慢的原因，接下来就要对其进行优化了。

--优化方向
/**
sql优化：
1、使用union all 替代union，Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All
2、使用物理表替换视图，利用表的索引可以提高查询效率
3、谓词推进，缩小检索范围
4、物化视图，可在物化视图上创建索引，提高查询效率。
*/

总结：这次sql查询慢是由于从视图取数时，视图数据量较大，但是谓词却放在了最外层，导致内层查询时查询了很多没有必要查询的数据导致效率降低。通过谓词推进的方式，将谓词过滤条件推进到紧跟视图，缩小检索范围，优化了查询效率。