【SQL】Oracle SQL monitor


【SQL】Oracle SQL monitor


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

DBAplus社群 | 2015-11-26 07:00

转载声明:本文为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监控。

wps2D39.tmp

wps2D49.tmp

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

wps2D5A.tmp

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

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

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

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

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

wps2D5B.tmp

方法二

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

wps2D6C.tmp

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

wps2D7C.tmp

方法三

如果您没有配置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执行计划后,

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

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

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

wps2D7D.tmp

wps2D8E.tmp

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

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

wps2D9E.tmp

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

wps2DAF.tmp

我们看到该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表,该视图的定义如下:

wps2DB0.tmp

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

wps2DC1.tmp

四总结

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

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

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

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

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

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




  SQL Monitor,你值得掌握的一个特性  
    对于线上的SQL语句,看着执行计划cost还不错,但是实际执行的时候效果却有千壤之别,这是为什么呢?
    对于一个庞大的SQL语句,看着得到的执行计划却不知道瓶颈在哪里,SQL语句太复杂,但是执行计划更复杂,要读明白它掌握要领也不是一件容易的事情。
    碰到很多朋友问我,怎么去读一个执行计划,这个无论说的怎么细,似乎都不是很容易去理解,语言描述,纯文字描述和图形的效果还是有很大的差别。
    如果你在11g的版本中,SQL Monitor就是一个大大的福利,你值得掌握,如果你还没有好好掌握它,就实在太可惜了。
至于SQL Monitor更多的细节就不一一描述了,11g推出的这个特性其实和MySQL里的慢日志有些类似,MySQL里面的阈值要更低一些,SQL Monitor是5秒。一旦达到这个标准,就会进收集到v$sql_monitor这个视图中,可以得到详细的会话信息和执行计划。
    如果想单独定制,那么也不是一件难事。可以使用hint monitor来完成。比如这样的形式
select /*+ monitor */ count(*) from emp where  xxxxx
    如果确认不需要放入监控范围,也可以使用no_monitor来定制,比如这样的形式
select /*+ no_monitor */ count(*) from emp where  xxxxx
    查看生成的监控信息,可以使用如下的方式:
select dbms_sqltune.report_sql_monitor from dual;
    当然这些都是SQL Monitor常规的一些知识点,这些还不足以让我兴趣大开。我感兴趣的是它强大的UI展现能力。当然听起来这个似乎和这个特性好像关联不大,你看到效果就知道了。一个很复杂,抽象的事物如果用图形表示,要比文字丰富形象的多。
    得到SQL Monitor的报告,大体有以下几种格式。TEXT,HTML,ACTIVE三种
我们打一个比方。很多手机都会按照配置来冠以各种名号。



SQL Monitor的报告也是如此,TEXT格式是标准版,HTML是高配版,ACTIVE是尊享版
我们不来虚的,来实际看看效果。

文本格式的效果如下:


HTML格式的效果如下:
执行概览,会话信息,执行计划一目了然,非常贴心。

那么ACTIVE格式是什么意思呢,大体就是最炫,最全面的效果,还有一个小的flash效果。


有的朋友可能看到会说,这和HTML的效果有啥差别啊,有的,我再给一张图。
执行计划原来可以这么读。全表扫描,索引扫描,表连接信息都一目了然,越是复杂的执行计划这种方式越省事。


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

好吧,看起来这么炫,想得到这个报告难不难呢,很简单就一个SQL语句就能搞定,绝对没有标题党的意思。
如果想写成shell脚本,也就是嵌入一个SQL语句即可。
tmp_sql_id=$1
sqlplus -s  / as sysdba<<eof
set pages 0
set long 99999999
set linesize 300
col comm format a200
set long 99999
SELECT dbms_sqltune.report_sql_monitor(
sql_id => '${tmp_sql_id}',
report_level => 'ALL',
type=>'TEXT',
base_path =>'http://www.jeanron100/sqlmon'
) comm 
FROM dual;
EOF
唯一的差别就是在type的地方。TEXT,HTML的就设置为TEXT,HTML即可。如果是ACTIVE格式的,这个咱得稍说一些背景。
这个功能在Enterprise Manager中查看是很自然的一件事情,如果没有安装EM,我们不能因为这个专门去部署一个EM来不是。要达到同样的效果,就需要连接网络下载相应的格式,当然退一步来说,要做以下的一些辅助工作,还可以下载几个脚本到本地即可。
比如我们设置一个本地的目录结构,设置类似的站点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
生成SQL语句的active报告语句如下:
SELECT dbms_sqltune.report_sql_monitor(
sql_id => '${tmp_sql_id}',
report_level => 'ALL',
type=>'ACTIVE',
base_path =>'http://www.jeanron100/sqlmon'
) comm 
FROM dual;
把拷贝到的结果以HTML格式保存,在本地的目录下打开即可。其实感兴趣可以读一下里面的内容,里面大量使用了xml解析的方式。效果还是蛮不错的。值得推荐。
</eof<>


第一章  掌握SQL Monitoring这些特性,SQL优化通通不在话下

DBAplus社群 | 2015-12-29 07:01

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

目录

术语说明

概述

什么SQL会被SQL MONITORING监控到

找到Real Time SQL Monitoring入口

详解Real Time SQL Monitoring

1术语说明

在正式介绍Real Time SQL Monitoring之前,我们先对接下来要用到一些术语做集中的介绍。

Table Queue,消息缓冲区,在并行操作中使用,用于PX进程之间的通信,或者PX进程与QC进程之间的通信,是内存中的一些page,每个消息缓冲区的大小由参数parallel_execution_message_size控制,11GR2版本默认为16K,之前的各个大版本这个值都不一样,详细请参考ORACLE官方文档。

墙面时间、持续时间指的是物理时间、钟表时间。

HASH JOIN左边,the build side of hash join,一般为小表。

HASH JOIN右边,the probe side of hash join,一般为大表。

M代表百万

行源 row source,指的是执行计划特定的一行操作,例如:

wps847F.tmp

上面执行计划的第一列,Id列0-5,每一行都是一个行源

2概述

Oracle每个版本总有一些新特性惊艳到我们,SQL MONITORING对我来说就是这样一个新特性,虽然它还未广为人知,它在11GR1版本被提供,而且后续的几个版本(11GR2,12CR1)这个功能也被不断的加强,说明ORACLE对它非常的重视,它能够把查询涉及到的所有关键性能统计信息集中在一个页面上,特别是对于并行查询的语句会自动启用这个特性。这个功能在国外的ORACLE 用户组被多次的分享,但是目前国内对它的介绍还非常少,本文主要介绍Oracle Real Time SQL Monitoring的核心特性,意图使DBA能够有一种新的手段(更先进的手段)来诊断SQL性能,进而提升优化效率。

SQL优化是一个DBA必备的技能,然而即使一个有丰富SQL优化经验的老DBA估计碰到几十行甚至上百行的执行计划也要皱皱眉头,他如何能快速知道:

在这么庞大的执行计划中哪一行源消耗的资源最多。如果一个SQL的执行计划包含5个行源,行源1消耗的DB TIME占取了3%,那你即使把这3%的DB TIME全部消灭掉,也只让SQL的性能提升了3%,对于整体的DB TIME提升效果并不明显。

如何知道整个SQL执行过程中消耗的哪一类资源最多,IO?CPU?,这让我们对SQL的性能有一个整体的认识,你可能观察性能指标后会说,奥,这是一个IO比较重的SQL,如果需要大幅提升SQL性能,也许要考虑提升数据库系统IO的能力。

对于一个正在执行的SQL语句,如何知道它当前执行到哪一步了?甚至知道执行完这一步还需要多久?

如何知道执行这个SQL语句都经历了哪些等待事件,甚至知道这些等待里哪一类等待最为严重?

要想知道这些问题的答案,在11G之前都是非常不容易的,要通过各种V$视图的关联去获取,而且展示的结果不够一目了然。 11G以后这些信息全部可以在SQL MONITORING中找到答案,SQL MONITORING提供的功能还不仅仅是上面提到的这些,通过SQL MONITORING还可以轻松获取语句的绑定变量、监控索引的整个创建过程及创建完索引剩余的工作量。 文本会着重讲解SQL MONITORING的核心功能,其他的相关信息就请读者们去尽情挖掘吧。

3什么SQL会被SQL MONITORING监控到

对于绝大多数OLTP系统来说,SQL相对比较简单,每次的运行时间都非常快,绝大部分SQL的响应时间都应该在10MS以下,优化的复杂度也比较低,SQL MONITORING功能的出现并不是为了帮助DBA发现、诊断OLTP SQL的性能问题,而是为了加快DBA优化数据仓库类SQL的效率,这些SQL是偏OLAP系统的,特点是并发量低、运行时间久、SQL复杂度高。满足以下条件的任意SQL都会被SQL MONITORING监控到:

如果串行执行的SQL,消耗的CPU时间或IO时间超过5秒,那么这些SQL 将会被监控到,通过修改隐含参数_sqlmon_threshold可以控制这一行为,默认为5秒,如果设置为0将关闭SQL MONITORING功能 。注意我这里提到的是SQL消耗的CPU时间或IO时间,而不是SQL的执行时间,之所以需要限制CPU时间或IO时间是为了防止数据库某一时刻如果有大量lock/latch的话,那么将有大量的SQL满足5秒执行时间的条件,而SQL监控本身比较消耗资源,需要拷贝运行时的性能统计信息到SGA,每一个受监控的SQL都有一个单独的内存结构,在11G可能会导致大量的latch竞争,CPU飙高,12C对这个问题做了优化不存在该问题了。如果你发现你的SQL运行时间明显超过了5秒但是却没被SQL MONITORING监控到,那么你该仔细检查是否是由于SQL本身消耗的CPU或IO并没有超过5秒(由于锁、网络?)。

并行执行的SQL将全部被监控到,不需要等待CPU或IO时间超过5秒。对于这一点也比较好理解,一般并行查询的SQL都是报表类或比较重的任务类的SQL,因此会自动打开SQL MONITORING的功能。

增加HINT /+ monitor /的SQL会立即开启SQL MONITORING功能。

除了以上条件外,你还需要检查一些系统参数是否设置正确:

statistics_level需要为TYPICAL(默认)或者ALL.

control_management_pack_access需要为DIAGNOSTIC+TUNING(默认)

SQL MONITORING并不会监控非常大的执行计划,默认不会监控超过300行的执行计划,可以通过隐含参数_sqlmon_max_planlines来控制。具体请参照MOS ID:1613163.1

4找到Real Time SQL Monitoring入口

可以从这几个位置找到、使用SQL Monitoring的功能:

Enterprise Manager

Enterprise Manager Cloud Control(EMCC)

SQL Developer

DBMS_SQLTUNE包

这里我们主要介绍通过Enterprise Manager Cloud Control(EMCC)页面来使用Real Time SQL Monitoring,文章的后面也会有单独一节简单介绍如何通过SQL包DBMS_SQLTUNE来获取Real Time SQL Monitoring的输出。

首先登陆Oracle EMCC,进入到【数据库】页面,选择你需要监控的数据库,这里以clouddb1为例:

wps8480.tmp

进入到clouddb1数据库后,选择【性能】下的SQL监视功能。

wps8491.tmp

然后就进入到了SQL MONTORING的主界面,这里包含了最近被监控到的SQL,查看状态列,可以知道SQL当前的执行状态。

wps84A2.tmp

状态列一般包含以下4种状态:运行、完成、排队、错误,将鼠标放在状态列的符号上面,会看到符号所代表的状态。这几个状态除了【排队】状态都比较好理解,排队这个状态只会在大于11GR2版本出现。ORACLE 11GR2在并行设置方面起了很大变化:自动并行度调整、并行语句队列、内存并行执行,启用此三项新特性需要设置参数parallel_degree_policy为auto,默认值为MANUAL。设置为auto后,三项新特性全部被打开,这里我们主要说下并行语句队列,启用该特性后,通过设置parallel_servers_target来指定总的可用的并行子进程的目标数,运行语句时,如果发现可用的并行进程数已经小于待运行语句的目标数,那么语句将会放入队列中等待直到拥有足够可用的并行进程。一旦被加入队列,你就会在SQL MONITORING的监控页面上看到,语句的当前运行状态为【排队】。

SQL监视页面显示的SQL列表不是针对特定的SQL文本的,而是针对特定SQL语句的一次特定的执行,因此如果两个会话运行相同的语句,你会在此列表中看到两个单独的条目,因此它与v$sql的表现行为不一样,如果2个会话在运行同一个SQL,在 v$sql里只能对应到汇总了SQL执行统计信息的一个记录。针对SQL MONITOR的这个特点,允许你针对一个特定的用户(他正在抱怨性能问题)检查这个语句到底出了什么问题,而不是首先观察汇总了所有用户的语句级的性能指标,像v$sql里的,再试着从中发现你所关注的用户问题。

5详解Real Time SQL Monitoring

找到Real Time SQL Monitoring的入口后,可以点击SQL_ID进入到Real Time SQL Monitoring的展示页面,是不是很炫酷?

wps84B2.tmp

也许你对页面上的很多指标还感觉很陌生,别着急,我接下来会对页面上的核心部分进行介绍。

5.1 一般信息

wps84C3.tmp

一般信息部分显示了SQL执行的一些基本的细节,例如SQL的文本,SQL语句采用的并行度,SQL的执行开始时间,本次统计值最后一次的刷新时间,执行SQL的用户、取数操作等。注意【SQL文本】后带有…的标记(红色框标记),点击它,你会获得完整的SQL语句文本,从版本11.2.0.2开始,你还会看到绑定变量的值。

例如:

wps84C4.tmp

上面的代码我对一个带有绑定变量的SQL以并行度6执行了查询,然后通过EMCC的SQL MONITORING页面点击【SQL文本】后面部分的…,这时会出现一个新窗口:

点击show SQL Binds:

wps84C5.tmp

这时就可以看到绑定变量的列表,是不是非常的方便?

当然就像上面提到的,SQL MONITORING主要用于监控数据仓库类型的SQL,对于长时间运行查询,是否应该使用绑定变量是另外一个单独的话题,在数据仓库中,一般的最佳实践为:不该为长时间运行的查询使用绑定变量,因为相对于整个SQL的运行时间,SQL解析只占到可以忽略不计的时间,而且数据仓库的系统本身的SQL执行频率也非常低,因此我们可以考虑对所有查询都进行硬解析,即使这样会消耗掉一些响应时间(可以忽略不计),但我们尽可能保证每一组参数组合都能生成一个最优的执行计划,因为就绑定变量和文本常量来说,文本常量是最适合产生最优执行计划的。

言归正传,就像上面已经演示的,得到被监控查询的绑定变量值在Oracle 11.2.0.2或更高版本上变得容易,以前你可能需要查询v$sql_bind_capture,dba_hist_sqlbind来获取绑定变量的值(历史绑定值),甚至通过oradebug errorstack这类命令来获得当前运行SQL的正在使用的绑定变量的值,现在你终于可以抛弃这些方法了。

【一般信息】模块还包含了取数操作(fetch calls)的次数,简单来说它代表了数据库和应用端网络交互的次数,如果是count,sum这类操作,一般这个值为1,只需要一次网络交互,但是对于select *这样的查询,返回的数据量越大,这个值也会越大。

wps84D6.tmp

例如从上图我们知道,返回1000条记录一共分了11次才传输完成。每次传输90条记录。

取数操作我们在后面的章节还会继续讲到,这里先点到为止。

5.2 时间和等待统计信息

wps84D7.tmp

这一部分内容非常少,但是信息量非常大,显示的是持续时间、数据库时间和等待活动百分比。把鼠标移动到数据库时间的条状图上,会看到消耗在各类等待事件和CPU上的时间,这让你知道你的SQL时间都花哪了,是IO操作多,还是CPU操作多,这非常的棒。等待活动百分比条状图则显示了详细的等待事件分布,例如上图中蓝色部分代表整个语句执行过程中,45%的等待发生在direct path read temp这个等待事件上,注意这里等待活动百分比条状图的100(100%)指的是数据库里的等待事件时间,而不是整个数据库时间,即并不包括CPU时间,这部分的信息可以告诉DBA在SQL执行过程中,遭遇了哪些等待事件,以及这些等待事件占取的比例,当然,你应该关注这些等待事件里最为惹眼的。

你可能对持续时间和数据库时间的含义感到疑惑,没关系,我们接下来就会着重介绍这两个时间代表的含义和两者的差异:

5.3 持续时间

持续时间(墙面时间)是用户非常关注的时间,它显示一个语句已经处于活动状态多久,它代表着语句从开始执行直到结束的时间跨度,对于正在运行的语句,则是从开始执行到当前的时间。当然,最终用户可能还要等待更长的时间,因为除了数据库的响应时间之外,时间还可能会被花在应用系统上,或者是数据库和应用服务器之间的网络上。例如SQL的持续时间为5秒,但是应用本身处理这些数据需要1秒,那么用户端感受到的时间就可能是6秒,而不是数据库端看到的持续时间5秒。

需要再次强调的是,持续时间衡量的是从SQL开始执行即游标打开直到游标被关闭或取消的时间跨度,这意味着如果数据库1分钟内完成一个查询,但随后产生的数百万结果每次只能返回几行,从应用的角度看,这个查询将需要很长的时间(需要数百万次的网络传输)才能完成,但是数据库端只花了一点时间来处理。对于网络传输的情况,SQL MONITORING会有一个指标进行反应,就是上面已经提到过的取数操作(fetch calls),如果网络传输量比较大的话,这个值会比较大。

wps84D8.tmp

例如我通过monitor这个hint强制让ORACLE监控这个SQL,这个SQL会返回大量的结果集给客户端,我们通过EMCC来监控这个SQL的相关监控信息:

wps84D9.tmp

取数操作值非常大,因为数据库要跟应用(我们这里是SQLPLUS客户端)有大量的网络交互。

5.4 数据库时间

数据库时间即DB Time,显示的是一个查询在数据库中执行花费的总时间,就DML操作来说,一般数据库时间基本等于持续时间,因为DML操作不用返回结果集,没有网络交互时间,但是如果运行的是一个SELECT语句并返回很多行,那么持续时间一般会大于数据库时间,因为很多的网络时间会被统计在持续时间中,数据库会把数据分批发送给应用程序,因此对于一个select语句你可能会看到数据库时间只有30秒,而持续时间却有50秒,其中30秒用于服务你的请求,其余的时间数据库则是空闲的,等待应用取下一批数据。我们再次看一下上面已经使用过的一张图,对于这个查询来说,由于要返回大量的结果集给客户端,因此持续时间远远大于数据库时间。

wps84E9.tmp

对于串行查询来说,持续时间都是大于等于数据库时间的,但是对于并行查询来说,情况有所改变:当运行一个并行查询时,会有多个服务器进程(甚至可能是多个服务器上的服务器进程)参与进来,每个服务器进程都可以使用单独的CPU资源,也就是1分钟的墙面时间(wall time),但是数据库时间可能有1*N分钟(N为CPU逻辑核数),并行查询最终的数据库时间可能会比持续时间(wall time)长的多。

wps84EA.tmp

这个语句的持续时间只有19秒,而数据库时间则有1.8分钟,从【一般信息】的Execution Plan部分可以看到这个语句是以并行度6来运行,这意味着数据库里有多个进程同时服务于这个查询,所有这些会话的数据库时间加上协调进程的时间都被汇总到了数据库时间中。数据库时间代表着大致有多少时间必须在数据库中完成,但是因为该语句是并行的,因此你其实不必等待那么长的时间。

5.5 IO统计信息

wps84EB.tmp

显示了语句执行时的一些关键的IO统计信息。

缓冲区获取数,即buffer gets,显示了数据库层的逻辑IO总数。IO请求包含了总的IO请求次数,例如db file sequential read,db file scattered read,direct path read等等的次数,IO字节代表了读取/写入的IO的总字节数。通过条状图的不同有颜色部分,能获取到更详细的统计值,例如上图中显示10GB的IO字节,其中有42%是读取请求产生的,等等。 需要注意的是,这里的IO请求次数和IO字节数统计可能超出你的预期,例如针对一个1G表的全表扫描按照某个字段排序,不但要统计表本身的IO,而且还要统计排序产生的读、写临时段的IO。

5.6 详细信息

wps84FC.tmp

详细信息页本身包含了一些子tab页面,例如上图中包含了【计划统计信息】、【计划】、【并行】、【活动】、【度量】子tab页。详细信息页主要包含了执行计划在在行源(rowsource)细节上的一些统计信息。

首先我们先看下页面的最左侧一列,如果某个行源操作是被并行执行的,会用多个小人的图标标识,相反如果是被串行执行的会用一个小人的图标标识,如果你观察仔细的话,会发现多个小人的图标有时也会有颜色的区分(下图),这是因为并行执行中,生产者和消费者角色的不同导致的。例如图中的红色是生产者负责扫描表,而图中蓝色的进程是消费者负责把接收过来的数据做排序。

再者,我们会看到多人小图标的后面有向右箭头的指示,这个代表了目前的查询正在进行,执行计划正在执行到这个(些)行源(右箭头所在的行源),也就是通过SQL MONITORING可以知道目前执行计划执行到了哪一个行源,nice job!如果SQL执行结束,或者这个(些)行源执行结束,这些向右的小箭头也会消失。

wps850C.tmp

然后我们关注下【估计行数】和【实际行数】,如果两者差异巨大,可能是由于你的数据库系统统计信息缺失或者陈旧,或者是优化器自身的算法导致。这里我并不会介绍CBO优化的相关信息,读者可以自行参阅相关文章。【实际行数】列真实的反应了扫描进程需要产生的工作量。

虽然Oracle CBO技术发展了这么久,相关书籍、文章也非常多,但是笔者认为,阅读、了解CBO最好的书籍仍然是jonathon lewis的基于成本的ORACLE优化法则,不过中文版貌似已经买不到了。


【时间表列】(Timeline)是一个很重要的列,通过它可以知道这个SQL执行计划每一行活跃的时间段。上图中的Timeline列基于执行计划每一行的活动构造出了一个可视化的时间进度图,它基于ASH的采样,从ORACLE 11G开始,ASH基于SQL执行计划的每一行源收集信息,观察上图中Timeline列标题里的括号内的数字,它显示了这个SQL总的运行持续时间,在这里是21秒,现在很容易根据这个,然后观察每个行源上的条状图和条状图位置进行可视化的解析。 然后将我们的目光移动到【临时】列,它代表了SQL运行过程中消耗的临时表空间大小,如果发现SQL消耗的临时表空间非常大,可以查看是否并行度不够或者PGA参数设置过小,不过对于创建索引、大表HASH JOIN、SQL排序等操作来说,完全避免临时段的使用也是一件可望不可即的事。


【IO请求】列代表了每一个行源所产生的IO请求数,根据可视化的条形图,很容易发现哪一个行源产生了最多的IO请求。


【活动百分比】列代表了在此行源产生的等待,CPU也会被统计在内,它的数据是从ASH的基表v$active_session_history获取的,也就是说数据是采样而来的(1秒采样一次)。例如:


wps850D.tmp


wps851E.tmp


wps851F.tmp


11G的v$active_session_history视图新增了SQL_PLAN_LINE_ID、SQL_PLAN_OPERATION、SQL_PLAN_OPERATION列,可以跟踪到具体发生等待的行源信息,非常有利用与DBA做性能诊断。上面SQL执行过程中,ASH共采样到了6条数据,其中五次等待事件为direct path read,一次为空,即为CPU操作。 SQL_PLAN_LINE_ID 代表产生等待的行源,这里为6,观察下图【行ID】列,ID为6的行源执行的正是全表扫描操作,【活动百分比】条状图显示了产生了5次的direct path read等待,与我们从ASH视图里查询的结果完全相符。


wps8530.tmp


因此【活动百分比】列非常重要,通过此列,我们能够知道DB TIME的去向,例如上图中,绝大部分的DB TIME都会花在了全表扫描阶段。除了全表扫描行源,其他行源都为空,但是这并不代表其他行源没有花取数据库时间,而是因为这块的内容是依据ASH采样而来的,如果行源操作执行比较快的话,ASH就不容易捕获到。通过【活动百分比】列,我们可以定位到最消耗资源的行源,然后可以有针对性的做优化,达到事半功倍的效果。


我们来看【详细信息】 的【并行】tab页,这个页面汇集了每一个并行进程完成的工作,由于ORACLE的并行采用了生产者消费者模型,首先是按照实例做的分组,然后再按照生产者和消费者把进程做了分组,我的测试例子里,只有一个RAC节点实例,另一个节点被关闭了。


wps8531.tmp


切换到【详细信息】的【活动】页,展示了从开始执行到结束执行时间跨度内,有多少进程处于活跃状态。由于我们设置了并行度6,因此可以从页面上看到SQL执行的后期大部分时间有6个进程处于活跃状态。


wps8541.tmp


【详细信息】的【度量】页显示了查询 过程中对CPU、内存、IO的占用率。


wps8552.tmp


这里我们基本上把SQL MONITORING的核心内容做了减少,这个页面包含了大量的信息,而且这个页面本身具有非常大的互动性,对这个页面你可以充分发挥你的好奇心,把你的鼠标尽情的放在任何一个可以放的位置,尽情发掘里面的宝藏。


5.7 通过SQL MONITORING监控索引创建过程


之所以专门写一节通过SQL MONITORING监控索引创建过程是源于一个客户的需求,他由于业务上的需要想要知道重建索引大概需要多久,即使不能知道准确的时间,也希望能大概知道索引创建开始后,已经完成了多少的工作,大约还有多少工作未完成 。如果是使用的11G版本 ,可以很轻松的通过SQL MONITORING实现这个需求。


索引的创建大体上分为两个阶段:第一,全表扫描过程 第二,排序创建索引过程。


我们这里给出一个索引创建的例子,为了保证可以让SQL MONITORING监控到这个语句,我使用了并行语句,当然也可以造取一个足够大的表来确保索引创建语句运行的时间足够长来开启SQL MONITOR功能 。


alter index tt rebuild online parallel 6;


wps8553.tmp


索引创建开始后,我们观察SQL MONITORING页面的【详细信息】部分,【详细信息】页面的最左边列的多人图标显示了这是一个并行执行的语句,这里红色为生产者,负责扫描HASH_T1表,根据全表扫描行源(ID=8)的父操作PX BLOCK ITERATOR可以知道,PX SLAVE按照ROWID划分工作,每个PX SLAVE负责一部分数据块的扫描(我这里为分区表,并没有按照分区划分工作),扫描后的数据写入TQ10000 table queue,消费者PX SLAVE进程从table queue TQ10000读取数据,这样通过table queue TQ10000表,两组并行进程完成了数据的传递。消费者PX SLAVE把接收到的数据做排序并创建索引。


wps8564.tmp


从上图可以看出,向右的箭头处于排序创建索引阶段(行源3,4,5),全表扫描阶段已经结束,正在进行排序和创建索引,根据【实际行数】列可以知道目前排序已经完成的行数是531K,而真正需要的排序量为47M,从其他行源可以知道需要排序的总量(ID=5-8的实际行数列都显示了这个信息)。接下来的工作就是刷新页面看排序的实际行数什么时候能够到47M了。【进度百分比】列显示了已经完成的百分比,这个对于工作量的估算非常的有意义。有一点比较遗憾,【进度百分比】对于很多操作都不能进行显示,很多时候【进度百分比】会在SQL运行过程中动态出现。不过对于索引创建来说,这个并不重要,根据真正的数据量和已经完成的排序量就能大概知道剩余的工作量。


wps8574.tmp



刷新后已经完成了28M的排序工作。



wps8575.tmp



再次刷新,发现索引创建已经完成,因为最左边的已经没有向右的箭头,而且实际行数(行源4)已经为47M,完成了所有数据的排序。我们观察【活动百分比】这一列,整个索引创建过程中,绝大部分的时间都消耗在ID=4这个行源上,即排序和创建索引操作上,从【时间表】(Timeline)列也可以看出它的活跃时间跨度也最长。



5.8 一个例子



上面介绍了相关的SQL MONITORING的核心特性,但是它只能协助我们发现性能问题,如何解决问题它并不能帮助到我们,解决问题的过程还需要我们具备各种SQL优化的技能,如CBO的知识,操作系统的知识等等。



我们来通过一个小例子来进阶我们对于SQL MONITORING的理解:



表的物理信息:



hash_t1 大小 4416M



查询语句



SQL运行时间2分钟,由于使用了并行我们从上图可以看到DB TIME 12.6分钟远大于SQL运行时间2分钟。



wps8586.tmp



根据【活动百分比】这一列看到HASH JOIN本身占取的数据库时间最多,达到了51%(ASH采样的结果),再仔细观察执行计划的相关内容,HASH JOIN的左边使用了广播的分发方式PX SEND BROADCAST,但是需要广播的数据量非常的大,有40M,由于采用了6个并发,因此广播之后,每一个PX进程都接收到了40M的数据量,因此总共的数据量为240M。HASH JOIN右边的表的数据量也是40M,跟HASH JOIN左边的表大小一样,数据的分发方式一般包括广播和HASH,12C新增加了replicat方式,对于HASH JOIN左边是小表的情况一般使用广播方式的分发,对于HASH JOIN的左边、右边都是大表的情况,一般使用HASH分发比较好。这里由于每个PX消费者进程都需要完整持有HASH JOIN左边表的数据,因此消耗的临时段的空间也比较大,达到了4GB。



wps8587.tmp



我们来看下尝试使用HASH 分布效果怎么样:SQL运行时间从2分钟缩减到了1.3分钟,DB TIME也从12.6分钟缩减到了8.3分钟。



wps8597.tmp



查看执行计划发现数据分发方式已经变为PX SEND HASH,使用了HASH分发的方式,观察【实际行数列】数据量也和表的记录数相同,临时表空间也从4GB大小缩减为966MB。HASH JOIN操作本身占取的活动百分比也从51%下降为22%。



wps85A8.tmp



5.9 相关视图



Grid Control上所有漂亮的图表都来源于v$或DBA_视图或许没有必要为日常监控和调优任务经常去访问这些v$视图,但知道这些信息来自哪里仍然是很有用处的,因为这会让自定义监控和对问题的高级诊断变得更方便,例如我所在的沃趣科技就在自动研发一套监控系统,也在考虑将SQL MONITORING这个功能增加到我们的监控产品里,如果能了解到这些功能的底层视图就对我们开发这个功能非常有好处。下面是一些要知道的关键视图:



GV$SQL_MONITOR视图包含了语句执行时的监控数据。当有多个会话在运行相同的语句时,这个视图会有多个记录与之对应,所以请确保使用了正确的搜索过滤条件来获得你所关注的SQL执行情况。例如,应该注意SID和INST_ID是不是你正在寻找的会话,对于并行执行则是PX_QCSID和PX_QCINST_ID,如果在诊断一条当前正在运行的查询,则要检查列status是否显示为executing状态。



GV$SQL_PLAN_MONITOR 视图包含了执行计划每一行的性能指标,这些指标会被实时监控和更新。



GV$ACTIVE_SESSION_HISTORY 视图从ORACLE 11GR1开始包含了如SQL_PLAN_LINE_ID、SQL_PLAN_OPERATION和SQL_PLAN_OPTIONS这些列。通过查询这些列,加上SQL_ID,就可以找到一个SQL执行计划中最显著的行源,而不仅仅找到最显著的执行计划。



5.10 控制SQL监控



对于并行执行的SQL,SQL监控会马上对他们启用,而不管他们运行的时间有多久。对于串行执行的SQL,SQL监控不会马上启用,因为SQL监控并不是用来监控通常运行速度很快的OLTP查询的,这类查询每秒都会被执行很多遍。然而,如果一个SQL串行查询消耗多余5S的CPU和IO等待时间,他将会被认为是一个长查询,SQL监控也会对此类查询启用。这是自动发生的,查询语句不需要重新运行。



可以使用提示MONITOR和NO_MONITOR来控制对一个语句的监控与否。如果你对某一个运行时间比较长的SQL不想做监控,可以使用NO_MONITOR这个HINT。需要注意有一个跟NO_MONITOR非常相像的HINT NO_MONITORING,它是与SQL MONITORING功能完全不相关的东西,通过NO_MONITORING这个HINT可以禁止对表里先关字段的谓词使用情况进行监控,这些监控数据会被放入到sys.col_usage$中。



wps85B9.tmp



5.11 使用SQL获取SQL MONITORING的输出



经过上面一系列的介绍,我们再来看通过SQL包获取SQL MONITORING的输出就变得极为简单,这里我不会再花费笔墨来对输出做详细的解释,大部分的信息上面我都已经讲到过了。



这个主要是通过dbms_sqltune.report_sql_monitor来进行的。下面的SQL的含义是,把SQL_ID为4vnz8232nugv9的查询,最近的SQL MONITORING的信息以text格式打印出来:



wps85BA.tmp



wps85BB.tmp



wps85CB.tmp



wps85CC.tmp



wps85DD.tmp



dbms_sqltune.report_sql_monitor包的type除了我例子给出的text外,还可以有HTML,ACTIVE(11GR2),XML。如果你想查看某个SQL历史的执行统计信息而不是最近一次的,可以通过指定SQL_EXEC_ID 参数来进行。详细可以参考:



https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1#active_html_reports_offline




About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。

ico_mailme_02.png
DBA笔试面试讲解
欢迎与我联系

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2080743/,如需转载,请注明出处,否则将追究法律责任。

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:猿与汪的秘密 设计师:我叫白小胖 返回首页

打赏作者

小麦苗DBA宝典

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值