ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 15533|回复: 42

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

[复制链接]
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
发表于 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, 下载次数: 123)
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
发表于 2017-3-11 11:29 | 显示全部楼层
本帖最后由 samt007 于 2017-3-11 11:33 编辑

下面是一个需要执行时间较长的SQL的分析。可以直接看执行计划在哪一步的时候,在等待什么事件:
等待事件的例子.jpg

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2016-11-25 16:52:36
发表于 2017-3-11 16:03 | 显示全部楼层
666,支持一个

使用道具 举报

回复
论坛徽章:
264
布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-05-20 15:09:32射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13
发表于 2017-3-11 17:33 | 显示全部楼层
感谢分享! good job!

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
发表于 2017-3-11 17:45 | 显示全部楼层

有兴趣可以试试。发现bug的话直接跟帖回复即可。能解决的一定解决~

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
发表于 2017-3-11 17:47 | 显示全部楼层
solomon_007 发表于 2017-3-11 17:33
感谢分享! good job!

有分享精神才可以提高水平!
我自己也看了别人很多分享的功能,学到了很多东西。。。

使用道具 举报

回复
论坛徽章:
264
布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-05-20 15:09:32射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13
发表于 2017-3-11 19:02 | 显示全部楼层
samt007 发表于 2017-3-11 17:47
有分享精神才可以提高水平!
我自己也看了别人很多分享的功能,学到了很多东西。。。

有没有注意,我也是 007

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
发表于 2017-3-11 21:50 | 显示全部楼层
solomon_007 发表于 2017-3-11 19:02
有没有注意,我也是 007

有。。。都是国产007~

使用道具 举报

回复
论坛徽章:
264
布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-05-20 15:09:32射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13
发表于 2017-3-11 22:33 | 显示全部楼层
samt007 发表于 2017-3-11 21:50
有。。。都是国产007~

   

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
发表于 2017-3-11 23:38 | 显示全部楼层
你是怎么看到系统包源码的?自己UNWRAP?
这些代码原创的比例有多少?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表