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

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

方法一

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执行计划后，

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执行计划有无问题。