掌握SQL Monitor这些特性,SQL优化将如有神助!

杨建荣 2016-10-11 10:04:22

 

SQL分析的苦与痛

 

对于线上的SQL语句,很多DBA都总会有一些疑问,看着执行计划cost还不错,但是实际执行的时候效果却有天壤之别,这是为什么呢? 

 

对于一个庞大的SQL语句,看着得到的执行计划却不知道瓶颈在哪里,SQL语句太复杂,执行计划看起来更复杂,要读明白它掌握要领也不是一件容易的事情。
 

偶尔会有一些朋友问我,怎么去读一个执行计划,这个无论说得怎么细,似乎都不得要领,毕竟纯文字描述和图形的效果还是有很大的差别。
    

如果你在11g的版本中,SQL Monitor就是一个大大的福利,上面的问题可能都会在此化解,这个特性你值得掌握,如果你还没有好好掌握它,就实在太可惜了。
  

至于SQL Monitor更多的细节就不一一描述了,11g推出的这个特性其实和MySQL里的慢日志有些类似,MySQL里面的阈值要更低一些,SQL Monitor是5秒。一旦达到这个标准,就会进收集到v$sql_monitor这个视图中,可以得到详细的会话信息和执行计划。

 

指定的SQL使用SQL Monitor监控

 

如果某条语句想使用SQL Monitor来监控,单独来定制,也不是一件难事。可以使用Hint monitor来完成。比如这样的形式:

select /*+ monitor */ count(*) from emp where  xxxxx

 

如果确认不需要放入监控范围,也可以使用no_monitor来定制,比如这样的形式

select /*+ no_monitor */ count(*) from emp where  xxxxx

 

查看SQL Monitor报告的方式:

 

查看生成的监控信息,可以使用如下的方式:

select dbms_sqltune.report_sql_monitor from dual;

 

除了调用DBMS_SQLTUNE包得到报告外,此外可以采用以下2种方式来得到SQL Monitor内容:

 (1)EM的:Performance —〉右下角的SQL Monitoring —〉Monitored SQL Executions 
(2) SQL Developer的:Tools Monitor SQL

 

格式丰富的SQL Monitor报告

 

当然这些都是SQL Monitor常规的一些知识点,还不足以让我兴趣大开。我感兴趣的是它强大的UI展现能力。听起来这个似乎和这个特性好像关联不大,你如果看到效果就知道了,还是那句话,一个很复杂,抽象的事物如果用图形表示,要远比文字丰富形象得多。
 

SQL Monitor报告格式,大体有以下几种格式。TEXT,HTML,ACTIVE,XML四种,不过我们着重来说一下前三种,虽然看起来格式繁多,我们打一个比方就容易理解了。有些手机会按照配置来冠以各种名号。


 

SQL Monitor的报告也可以这么区分,TEXT格式是标准版,HTML是高配版,ACTIVE是尊享版,我们不来虚的,来实际看看效果。

 

文本格式的效果如下:
 

 

 

HTML格式的效果如下:
 

 

SQL信息,会话信息,执行计划一目了然,非常贴心。

 

 
那么ACTIVE格式是什么意思呢,就是最炫最全面的效果,打开的时候竟然还有一个小的flash效果。

 

ACTIVE格式的报告效果如下:
 

 

 

有的朋友可能看到会说,这和HTML的效果有啥差别啊,有的,我再给一张图你就明白了。


 

执行计划原来可以这么读。全表扫描,索引扫描,表连接信息都一目了然,越是复杂的执行计划这种方式越省事。

SQL文本和绑定变量的信息,点击SQL_ID就会弹出一个小窗口来。


 

得到TEXT,HTML报告的脚本
 

 

好吧,看起来这么炫,想得到这个报告难不难呢,其实很简单,就一个SQL语句就能搞定,绝对没有标题党的意思。


如果想写成shell脚本,就是嵌入一个SQL语句,本质就是调用dbms_sqltune.report_sql_monitor即可。脚本内容如下:

tmp_sql_id=$1
sqlplus -s  / as sysdba<

set trimspool on trim on

set pages 0 linesize 1000

set long 1000000 longchunksize 1000000
SELECT dbms_sqltune.report_sql_monitor(
sql_id => '${tmp_sql_id}',
report_level => 'ALL',
type=>'TEXT') 
FROM dual;
EOF

 

唯一的差别就是在type的地方。TEXT,HTML的就设置为TEXT,HTML即可。

 

得到ACTIVE格式报告的方法
 

 

如果是ACTIVE格式的,这个咱得稍说一下背景。


这个功能在Enterprise Manager中查看是很自然的一件事情,如果没有安装EM,我们不能因为这个专门去部署一个EM来不是。要达到同样的效果,有两种方式,一种是通过网络下载所需的脚本,即在线查看,另外一种是离线查看,需要提前把几个脚本下载到本地即可。所以要得到一个报告并显示出来,这些工作都可以搞定,办法总比困难多。

 

如果要得到一个在线的报告,可以使用如下的方式得到,脚本内容如下:

tmp_sql_id=$1
sqlplus -s  / as sysdba<

set trimspool on trim on

set pages 0 linesize 1000

set long 1000000 longchunksize 1000000
SELECT dbms_sqltune.report_sql_monitor(
sql_id => '${tmp_sql_id}',
report_level => 'ALL',
type=>'ACTIVE') 
FROM dual;
EOF

 

你没有看错,这个和TEXT,HTML的方式几乎一样。可以在浏览器中打开的时候有一些差别,那就是这种方式会尝试从Oracle的站点下载几个脚本,这种方式如果没有网络是无法打开报告的。

 

下载的脚本是哪些呢,这就牵扯出离线查看的内容。

 

我们可以在本地设置一个目录结构,设置一个类似的站点http://www.jeanron100在本地创建一个jeanron100的目录:

mkdir -p jeanron100/sqlmon

 

然后下载相应的脚本:

wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/scripts/flashver.js
wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js
wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/scripts/document.js
wget --mirror --no-host-directories --cut-dirs=1 

http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf


如果在windows上下载,直接输入上面的URL即可。

 

生成SQL语句的ACTIVE报告使用如下的方式即可:

SELECT dbms_sqltune.report_sql_monitor(
sql_id => '${tmp_sql_id}',
report_level => 'ALL',
type=>'ACTIVE',

base_path =>'http://www.jeanron100/sqlmon'
FROM dual;

 

把拷贝到的结果以HTML格式保存,在本地的目录下打开,离线报告建议用IE打开会流畅一些,有的浏览器可能有其他的安全限制。如果感兴趣也可以读一下生成的内容,里面大量使用了XML解析的方式。

 

SQL Monitor List的使用

 

如果在这个基础上还想做些什么的话,其实还有不少,比如我们查看v$sql_monitor的结果。

SQL> select count(*)from v$sql_monitor;

  COUNT(*)

----------

       160

 

竟然有这么多的SQL语句,我们有没有方法来得到一个概览信息呢。

 

 

使用的语句如下:

SELECT dbms_sqltune.report_sql_monitor_list( type => 'HTML',  report_level => 'ALL') AS report  FROM dual;

 

这个方法你用还是不用都在那儿。当然可以直接从v$sql_monitor里抽出数据来也可以,整体而言效果还是蛮不错的。

 

强大的SQL Detail Report

 

还有没有好玩的功能了呢,有的。SQL Detail Report,这个报告比SQL Monitor的ACTIVE报告还要更丰富一些。同样一个语句在不同时间的执行情况都一目了然,这对于分析性能问题尤其有帮助。

 

 

还可以看到历史执行过程中处理的行数,执行次数,DB time的情况,指标非常多,非常全。

 

 

得到这么一个报告麻烦吗,还是一个SQL语句即可,不过调用的是另外一个方法了,注意此处的报告格式还是ACTIVE。

set pages 0

set linesize 200

col comm format a300

set long 99999999

SELECT dbms_sqltune.report_sql_detail(

sql_id => 'xxxx',

report_level => 'ALL',

type=>'ACTIVE'

) comm

FROM dual;

 

如果查看一个指定时间范围内的报告信息,可以采用下面的方式即可。

SELECT dbms_sqltune.report_sql_detail(

sql_id => 'xxxx',

report_level => 'ALL',

type=>'ACTIVE',

start_time=>to_date('xxxx','yyyymmddhh24miss'),

duration=>'xxxx'

)

FROM dual;

 

 

扫雷环节

 

最后来扫几个雷。

 

对于TEXT,HTML格式的报告,这个对环境的依赖很低,对网络没有依赖,是非常稳定的实现,推荐使用。

 

对于SQL Monitor List报告,其实完全可以使用v$sql_monitor来得到,有些环境运行可能有下面的报错信息。

 

 

对于SQL Monitor的ACTIVE格式报告,在线方式还是推荐使用,离线查看可以先行下载脚本到本地。

 

如果报告显示的错误如下:

 

 

对于在线查看来说,可能是生成的报告格式的问题,看看是不是预先设置了这些选项。

set trimspool on trim on

set pages 0 linesize 1000

set long 1000000 longchunksize 1000000

 

对于离线查看可能就是下载的脚本路径问题了,调整一下即可。

 

对于SQL Monitor的更多定制

 

v$sql_monitor类似v$session的机制,某个SQL语句造成的问题已经发生了一段时间,想查看之前的执行情况,v$sql_monitor基本上就无从得知了,因为数据已经被刷出去了。

 

我们可以在后台启用一个JOB不定时的去查找,把这部分数据给缓存起来,比如是采用平面文件的形式来收集,然后通过时间戳来进行区别管理。

 

 

相信到此大家对于SQL Monitor有了一个简单的认识和理解,希望有所帮助。

 

作者介绍  杨建荣

  • DBAplus社群联合发起人。现就职于搜狐畅游,Oracle ACE-A、YEP成员,超7年数据库开发和运维经验,擅长电信数据业务、数据库迁移和性能调优。持Oracle 10G OCP,OCM,MySQL OCP认证,《Oracle DBA工作笔记》作者。

最新评论
访客 2017年06月29日

优化前后的执行计划cost值一致,图贴错了?

访客 2017年06月27日

感觉写着写着上厕所了。。突然停止

访客 2017年06月26日

<div class="content"><span class="blue f12">访…

访客 2017年06月23日

为什么小表驱动大表比较快呢?

访客 2017年06月20日

学习

活动预告