性能调优分析之：Oracle SQL执行计划报告生成器

samt007 · 发表于 2017-3-11 11:28

本帖最后由 samt007 于 2017-3-12 20:35 编辑

Oracle SQL执行计划分析器功能的创建3步曲：
1 首先，编译XYG_ALD_SESS_PKG的Package头。（XYG_ALD_SESS_PKG.sql）
2 接着要建立好下面的4个视图对象。因为XYG_ALD_SESS_PKG包体会用到。（View Create Script v1.0.sql）
  如果出现没权限访问GV$SQL_PLAN_MONITOR这些对象，请先用sys用户赋值。
3 最后，编译XYG_ALD_SESS_PKG的包体。完工！（XYG_ALD_SESS_PKG_BODY.sql）

功能特点说明;
优点：
1 主要是使用方便，效率高。
个人觉得是一个非常高效而且非常简洁（也可以说是简单）的一个SQL执行计划分析工具。
主要是借用了标准功能的一些执行计划分析表（例如GV$SQL_PLAN_MONITOR等），通过二次封装开发得出的一个完整的SQL执行计划分析报告。
而且自己添加一些查看执行计划的时候需要关注的信息。自己写的东西，只要是内容不会出错，还是很好用的。
另外，使用方便是体现在，如果要具体分析某个SQL的执行计划，分析其性能瓶颈，甚至不需要花费时间找所运行的脚本对应是哪个SQL_ID，
只需要在Hint输入足够唯一的关键字，然后根据关键字，就可以直接得出该SQL的完整执行计划报告。
而且执行计划报告里面也会非常直观体现一些关键的性能指标的报告信息，使得查看SQL的执行计划，分析SQL的性能瓶颈变得如此的简单。
2 SQL报告的执行速度快。
Oracle数据库自带功能的dbms_sqltune.report_sql_monitor，在分析一个比较复杂的SQL，一般都需要2分钟以上。而这个开发的功能，只需要6秒左右就可以出结果。
当然，速度快主要是因为开发的功能只需要考虑一种txt执行计划报告，而自带的dbms_sqltune需要同时考虑多种不同样式的报告（例如html）的可能，所以肯定复杂很多。
3 因为是自己开发的，所以，有一些有分析意义的栏位也可以自己添加。
例如SQL读取缓存区的总字节数,还有用到的索引范围扫描和索引跳跃扫描的信息，以及Predicate Information和实际执行的时候的绑定变量窥探的值等。
可以非常直观地协助分析对应的执行计划是否正确！

缺点：
分区表的详细执行计划信息方面支持程度不高。主要是考虑到执行计划的栏位太多的话，看起来乱。
另外，为了精简执行计划的长度，精简了E-bytes字段。有需要的兄台可以自己修改源码，添加。
另外，只可以是txt的结果报告。

特别说明：
1 该功能是由本人(samt007@qq.com)编写，如果要分享，请必须注明转载。
2 该功能的原型是dbms_sqltune.report_sql_monitor（开发的时候也有参考了它的源码）。同时也参考了 DBMS_XPLAN.DISPLAY_CURSOR 的源码（例如绑定变量窥探的值的获取等）。
  所以，该脚本请在数据库版本11g（包括11g版本）以上的Oracle数据库中使用。
  低于11g的版本的Oracle不支持实时监控sql 的功能，所以本脚本也就无法使用了。这点必须要注意！
3 还有，脚本的和size相关的结果和一个系统参数的配置有关系，就是db_block_size（SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size'）
  默认=8192的。如果修改了这个配置，不是8192（8K）的话，请对应修改一些代码（*8的部分，不明白的可以找我）。否则算出来的size可能有误。
4 该脚本已经全部通过测试，在EBS环境（11gr2，apps用户）以及一个新的Oracle数据库环境（12C，sys用户）测试通过。
5 如果有报错，最好自己先解决一下。实在无法解决的请联系我：samt007@qq.com
6 如果发现有bug请反馈给我，我好进一步修正！谢谢！

View Create Script v1.0.sql (8.46 KB, 下载次数: 253)

XYG_ALD_SESS_PKG.sql (5.2 KB, 下载次数: 226)

XYG_ALD_SESS_PKG_BODY.sql (51.39 KB, 下载次数: 239)

---使用实例：