【SQL】Oracle SQL monitor

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，指的是执行计划特定的一行操作，例如：

上面执行计划的第一列，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为例：

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

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

状态列一般包含以下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的展示页面，是不是很炫酷？

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

5.1 一般信息

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

例如：

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

点击show SQL Binds：

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

当然就像上面提到的，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 *这样的查询，返回的数据量越大，这个值也会越大。

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

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

5.2 时间和等待统计信息

这一部分内容非常少，但是信息量非常大，显示的是持续时间、数据库时间和等待活动百分比。把鼠标移动到数据库时间的条状图上，会看到消耗在各类等待事件和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），如果网络传输量比较大的话，这个值会比较大。

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

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

5.4 数据库时间

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

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

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

5.5 IO统计信息

显示了语句执行时的一些关键的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 详细信息

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

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

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

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

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