被埋没的SQL优化利器——Oracle SQL monitor

周俊 2015-11-26 09:37:31

转载声明:本文为DBA+社群原创文章,转载必须连同本订阅号二维码全文转载,并注明作者名字及来源:DBA+社群(dbaplus)。

 

 

据说,在Oracle企业版数据库中有一个免费的工具,乃SQL优化之利器,那就是Oracle SQL monitor。下面,由DBA+社群原创专家周俊,给大家科普一下这一被埋没的神器。

 

专家简介

 
 

周俊

DBA+社群原创专家

 

具有14年以上Oracle数据库技术支持经验,在IBM的7年间担任华东区非IBM logo产品技术支持团队team leader,同时是IBM中国区Oracle 软件支持服务的技术负责人。目前任职于Oracle公司,专注于Oracle数据集成方案设计和实施。获得Oracle 8i~11g OCP、Oracle 10g OCM、DB2 administrator、PMP等证书。

 

 

前言
 

 

说实话,我以前也不太爱用花哨的图形界面工具进行SQL优化,最近参加了Oracle RWP培训,我发现Oracle 11g 引入的SQL monitor确实蛮好用的,是个被埋没的SQL优化利器。最重要的是Oracle SQL monitor在Oracle企业版数据库中是免费供大家使用的。下面我和大家分享如何利用SQL monitor简化我们的SQL优化工作。

 

如何打开SQL monitor report
 

 

方法一

 

Step1:打开Oracle EM console主页,切换到性能页面,点击右下角的SQL监控。

 

 

Step2:选择 时间范围,可以按照持续时间或者数据库时间对SQL语句进行排序。

 

 

在Oracle 11g中,当SQL满足以下条件之一就会被sql monitor捕获到,监控数据被记录在v$sql_monitor视图中。

 

  1. 当SQL并行执行时,会立即被实时监控到

  2. 当SQL单进程运行时,如果消耗超过5秒的CPU或I/O时间,它也会被监控到

  3. 使用/*+ monitor */提示的sql语句

 

Step3:选择您想要进行SQL优化语句前,点击第一列状态栏中勾号,Oracle就会将该SQL语句的执行情况华丽丽的展现在您面前。

 

 

方法二

 

在性能页面左下角的顶级会话中,点击您想要查看的SQL语句ID。

 

 

在SQL监控页面点击第一列状态栏中的图标。

 

 

方法三

 

如果您没有配置Oracle EM,但是知道待优化SQL语句对应的SQLID,可以通过以下脚本利用SQL monitor查看SQL语句在数据库中真实的执行计划。

 

在SecureCRT中启用log跟踪,选择保存的日志文件(后缀html)

在SQLPLUS 中执行

set trimspool on

set arraysize 512

set trim on

set pagesize 0

set linesize 1000

set long 1000000

set longchunksize 1000000

spool sqlmon.html

select /*+ noparallel */ dbms_sqltune.report_sql_monitor (sql_id=>'input your SQL ID', report_level=>'ALL', type=>'ACTIVE') from dual;

spool off

cat sqlmon.html

在SecureCRT中关闭log跟踪,打开保存的文件就可以看到SQL执行计划了。

 

如何利用SQL monitor进行SQL优化
 

 

使用SQL monitor打开SQL执行计划后,

 

  1. 我们通常会根据最右边CPU和wait的activity,找到SQL执行计划中资源消耗较高的步骤。

  2. 然后查看一下Oracle估算的返回行数和实际的返回行数是否相差很大,如果估算的行数和实际的行数相差不大,至少表明目前对应数据库对象上的统计信息是准确的。

  3. 本例中Oracle估算的返回行数和实际返回行数相差不大,Oracle优化器采用了布隆过滤和HASH 右连的执行计划,接下去我们通常会检查SQL的筛选条件,判断是否使用了正确的索引等优化手段,这里我就不一一展开了。

 

 

下面是我最近遇到的一个利用SQL monitor进行快速SQL优化的案例分享。

 

SQL Text:SQL语句比较长 ,我截选了其中部分有代表性的SQL。

 

 

利用SQL monitor我们可以在执行计划中快速的定位需要重点关注的步骤。

 

 

我们看到该SQL语句已经运行了5.4小时,Oracle估算的返回行数和实际行数相差非常大,表明相关表上的统计信息不准确。

 

我们对DMS_CONTAINERS和DMS_CONTAINER_JN表进行了统计信息收集,统计信息重新收集后Oracle马上使用了DMS_CONTAINER_JN表上正确的IYC_CNTRID字段的索引,但是DMS_CONTAINERS表上仍旧使用了选择度不高TYPE字段索引。

 

进一步查看SQL语句,我们发现该SQL是通过视图YMS_GUI_LOAD_CONTAINERS_VW访问DMS_CONTAINERS表,该视图的定义如下:

 

 

由于在视图where条件中有IYC_TYPE字段,Oracle优先选用IYC_TYPE字段上的索引对DMS_CONTAINERS表进行访问,在CBO下,Oracle不会再去自动选择其他字段上单独的索引进行访问(除非手工设定AND-EQUAL提示),因此没有选择筛选度更高的YC_LSTUPDDT字段上的索引。 如果需要Oracle使用到其他字段上的索引,最简单的方法就是在IYC_TYPE和YC_LSTUPDDT字段上创建联合索引,Oracle在分析索引列的时候自动会分析两个列的组合情况,从而选择该复合索引。

 

 

总结
 

 

通过前面的介绍相信大家对Oracle SQL monitor华丽、直观的界面留下了深刻的印象,下面我再总结一下使用Oracle SQL monitor进行SQL优化的步骤:

 

  1. 通过SQL monitor监控我们可以快速地发现异常运行的SQL语句,如果您知道SQL对应的SQL ID也可以通过脚本利用SQL monitor查看SQL语句在数据库中真实的执行计划。

  2. 查看SQL执行计划,通过CPU和WAIT的活动比重快速找到SQL执行计划中的关键步骤。

  3. 通过比较Oracle估算的行数和实际返回行数能够快速判断是否需要重新收集统计信息,帮助我们分析Oracle优化器选择的SQL执行计划有无问题。

  4. 具体的SQL优化方法大家可以参考之前丁俊大师在DBA+社群分享过的Oracle SQL优化专题(关注DBA+社群微信公众号:dbaplus,回复“001”即可查看此文),我在这就不做进一步展开啦。

 

工欲善其事,必先利其器。小伙伴们,还等什么呢,赶紧去试试Oracle SQL monitor这个被埋没的SQL优化利器吧!

 

即日起,凡是推送在【DBAplus社群】平台的文章,阅读量超过1000,该文章作者可获得赠书一本。大家如有好的干货文章也可以向我们的订阅号投稿,投稿邮箱:1017465571@qq.com。近期赠书有:白鳝《思想的天空》、杨志洪《Oracle核心技术》……

 

 

小编精心为大家挑选了近日最受欢迎的几篇热文:

回复001,看丁俊的《【重磅干货】看了此文,Oracle SQL优化文章不必再看!》;

回复002,看《灾备故障上了红头文件,容灾技术到底哪家强?》;

回复003,看吕海波的《去不去O,谁说了算?》;

回复004,看胡怡文《PG,一道横跨oltp到olap的梦想之桥》;

回复005,看付新《达梦专家解读:国产数据库也疯狂》;

回复006,看郭耀龙《假事务之名,深入研究UNDO与REDO》;

回复007,看宋日杰《Oracle后台专家解决library cache锁争用的终极武器》。

 

 

关于DBA+社群

DBA+社群,是一个以连接全中国DBA为使命的公益性组织,致力于成为DBA界的UBER,充分利用DBA的业余时间,为广大DBA搭建一个学习交流、价值实现、人脉搭建、跨界合作的平台。目前,DBA+社群已于北上广深等全国15大城市成立DBA+城市群,100+业界专家成为联合发起人,6000+跨界DBA加入队伍,每周4次线上技术分享活动同步直播。

扫码关注

DBAplus社群

超越DBA圈子,连接的不仅仅是DBA

活动预告