一条SQL语句的执行计划变化探究

最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落在了DB这边,希望DB能够给点意见,是否存在一些性能瓶颈。
    我们从开发同学那里得到的一个基本的SQL语句,根据关键字从v$sql中做了提取,发现对应的SQL语句的执行时间还是OK的。

得到的SQL语句如下:
SQL_ID        SQL_FULLTEXT
------------- ----------------------------------------------------------------------------------------------------
6h4w0u8stp3z0 select APP_ID,GOODS_ID,ORDER_ID,ORDER_STATUS,GOODS_REGISTER_ID,GOODS_NUMBER,GROUP_ID,GOODS_PRICE,US
              ER_ID,ROLE_ID,ROLE_NAME,CHANNEL_ID,PUSH_NUM,PUSH_INFO from TB_ORDE
              R WHERE ORDER_ID=:1  AND USER_ID=:2  AND SUBSTR(CHANNEL_ID,0,4)=:3
这样一个语句,如此来看性能上应该是没有多少改进的空间了。我查看了数据库层面的相关统计信息,发现DB time极低,比如Elapsed time为60分钟,DB time就不到1分钟,
类似下面这样的输出。
              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      3295 24-Aug-16 13:00:17        38        .9
  End Snap:      3296 24-Aug-16 14:00:20        44        .9
   Elapsed:               60.05 (mins)
   DB Time:                0.03 (mins)
如此之低的情况下,很难和性能问题联系起来。通过得到的数据情况分析,细化到ASH报告也没有发现任何异常,所以我们可以说DB层面没有性能瓶颈,这个问题还需要进一步的确认。
当然交代完这件事情,主要任务就完成了。就简单再看了看这个问题。
执行计划的情况如下,看到这样的执行计划似乎也没有任何可挑剔的。

谓词信息如下:

看到这里我开始有一些疑惑,作为一个订单表,订单号应该是作为主键的,看到索引的情况,发现确实是。

表结构如下所示,在分析之前还是需要说明这些基本情况的。

那么问题就来了,按道理是需要走唯一性索引代价最低,为什么执行计划缺走了另外一个索引,由期望中的唯一性索引扫描变为了范围索引扫描,这是疑点1.
解答这个问题的过程中发现,其实会引出更多的问题,原本的问题只是开始,因为后面要走的路还有很多。
对于这个问题,我们得求助于10053事件,这个诊断事件能够从根本上解释清楚这个原因来。
当然开启10053,我开启了1级,日志量相对要更多一些。
    开启10053事件
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
    运行SQL语句
    结束10053事件
ALTER SESSION SET EVENTS '10053 trace name context off';
    其中需要说明一点的是,如果采用如下的这种方式开启诊断事件,是不行的。
alter session set current_schema=ordermob;
select 。。。。
    from OP_ORDER WHERE ORDER_ID='160824165342672424'  AND USER_ID='15000501196112'  AND SUBSTR(CHANNEL_ID,0,4)=5046 ;
可以使用如下的方式来代替。
select 。。。
    from ordermob.OP_ORDER WHERE ORDER_ID='160824165342672424'  AND USER_ID='15000501196112'  AND SUBSTR(CHANNEL_ID,0,4)=5046 ;
10053事件中查询转换结果如下:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "OP_ORDER"."APP_ID" "APP_ID",。。。。 FROM "ORDERMOB"."TB_ORDER" "OP_ORDER" WHERE "OP_ORDER"."ORDER_ID"='160824165342672424' AND "OP_ORDER"."USER_ID"='15000501196112' AND TO_NUMBER(SUBSTR(TO_CHAR("OP_ORDER"."CHANNEL_ID"),0,4))=5046
kkoqbc: optimizing query block SEL$1 (#0)

统计信息的一些明细信息如下:比如CLUF是集群因子。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: OP_ORDER  Alias: OP_ORDER
    #Rows: 2143  #Blks:  50  AvgRowLen:  157.00  ChainCnt:  0.00
Index Stats::
  Index: IDX_OP_ORDER_PUSH_STATE  Col#: 25
    LVLS: 1  #LB: 6  #DK: 1  LB/K: 6.00  DB/K: 50.00  CLUF: 50.00
  Index: IND_ORDER_USERID  Col#: 15
    LVLS: 1  #LB: 10  #DK: 230  LB/K: 1.00  DB/K: 2.00  CLUF: 625.00
  Index: SYS_C0011155  Col#: 4
    LVLS: 1  #LB: 9  #DK: 2143  LB/K: 1.00  DB/K: 1.00  CLUF: 1271.00

而下面的这段内容就让人更加疑惑了。Density代表列的密度,可以看到Density的值ORDER_ID对应的为0.000467,而USER_ID对应的为0.000233,
表中目前存在2000多条记录,在Oracle中,表里没有直方图信息的时候,是按照1/NDV的形式来计算的。而这里OERDER_ID对应的值,其实就是1/2143得到的值就是0.000467
而user_id的值如果按照1/NDV的形式的话,应该是0,0043478这样的值,很显然这里不是这个值。
***************************************
1-ROW TABLES:  OP_ORDER[OP_ORDER]#0
Access path analysis for OP_ORDER
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for OP_ORDER[OP_ORDER]
  Column (#4): ORDER_ID(
    AvgLen: 19 NDV: 2143 Nulls: 0 Density: 0.000467
  Column (#15):
    NewDensity:0.000233, OldDensity:0.000233 BktCnt:2143, PopBktCnt:2084, PopValCnt:171, NDV:230
  Column (#15): USER_ID(
    AvgLen: 15 NDV: 230 Nulls: 0 Density: 0.000233
    Histogram: Freq  #Bkts: 230  UncompBkts: 2143  EndPtVals: 230

这是为什么呢,我们来看看直方图的信息。可以看到ORDER_ID列是没有直方图信息的,而USER_ID列却含有。
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM
        FROM DBA_TAB_COL_STATISTICS WHERE OWNER='ORDERMOB' AND TABLE_NAME='OP_ORDER'
COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
APP_ID                                    1           1 FREQUENCY
CHANNEL_ID                                6           6 FREQUENCY
ACCOUNT                                 230           1 NONE
ORDER_ID                               2143           1 NONE
GOODS_ID                                 28           1 NONE
USER_ID                                 230         230 FREQUENCY
CREATE_DATE                            2010         254 HEIGHT BALANCED
这里需要提一下直方图分为两种,一种是频率直方图,显示为:FREQUENCY,另外一种是高度平衡直方图,显示为:HEIGHT BALANCED
高度均衡直方图适用于 数据分布不均匀 ,由于列中数据很多,这时数据比较密集,不利于分析和评估,这时直方图需要均衡化。
频率直方图适用于数据分布很均匀的情况。当然如果数据很平均,其实也没有太大的意义,直方图本身就是适用于对应列中数据分布比较倾斜的列(不均匀)
那么问题似乎有了一些眉目,我们知道在Oracle中收集统计信息的时候是推荐使用FOR ALL COLUMNS SIZE AUTO的选项的。
收集统计信息的语句大概是这样的形式。
exec dbms_stats.gather_table_stats(tabname => 'OP_ORDER',ownname => 'ORDERMOB',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
所以我大胆做了一个测试,那就是取消直方图的信息。
exec dbms_stats.gather_table_stats(tabname => 'OP_ORDER',ownname => 'ORDERMOB',method_opt => 'FOR ALL COLUMNS SIZE 1');
再次查看执行计划,发现就会采用唯一性索引扫描了,达到了我们预期的效果。
当然问题来了,这个是为什么呢,收集统计信息中的auto选项是什么含义呢。为什么两个数据类型一样的(varchar2(64))的列,境遇却大大不同。且听我下回慢慢道来。

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

Oracle之不可见索引 - 2016-08-27 17:08:09

Oracle 之不可见索引 1    BLOG 文档结构图   2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  Oracle 不可见索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新
【故障处理】序列 cache 值过小导致 CPU 利用率过高 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① enq: SQ - contention 等待事件的解决 ② 一般等待事件的解决办法 ③  DFS lock handle 等待事件 ④ 与序列有关的等待事件   Tips: ① 本文在 ITpub ( http://blog.itpub.
这两天做性能测试碰见一个问题,比较有意思。 一条SQL,使用了绑定变量,查看V$SQLAREA发现version_count是2,  查看V$SQL,发现有两条记录,分别对应了0和1两个child cursor:  再查看这两个child cursor对应的执行计划:  child cursor:0 -----------------------------------------------------------------------------------------------------| I

OCP如何查看历史成绩(2) - 2016-08-25 14:08:08

OCP如何查看历史成绩( 2 ) 之前写过一篇文章 OCP如何查看历史成绩: http://blog.itpub.net/26736162/viewspace-1624996/  , 但是现在界面好像有所变动,而且有的连接也找不见了,今天有朋友问如何查询历史的成绩,晚上小麦苗就抽了点时间来找找,顺便记录下来。最近心情不好,不想写技术文章了。       首先登陆网址: https://education.oracle.com/ https://education.oracle.com/pls/eval-e
下午的时候收到这么一条报警。 ZABBIX- 监控系统 : ------------------------------------ 报警内容 : Too many parallel sessions on xxxxx_ xx机房 _xxxxx ------------------------------------ 报警级别 : PROBLEM ------------------------------------ 监控项目 : parallel_session_cnt : 66 -----------

【MySQL】Tokudb安装测试初探 - 2016-08-24 14:08:02

一 前言    TokuDB 是一个高性能、支持MVCC的MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是数据压缩功能出色,对高写压力的支持,由美国TokuTek公司(http://www.tokutek.com/) 研发,该公司于2015年4月份被Percona收购,理所当然地提供了TokuDB版本的Percona Server。本文使用Peronca server 5.6.30 版本进行测试安装。 二 安装前的准备     请参考官方文档 Percona Server YUM源

通过shell脚本添加备库日志 - 2016-08-21 17:08:14

今天下午的时候,准备顺手写一个简单的脚本,但是发现很多事情较真起来真是寸步难行。在写脚本的过程中碰到了太多的问题,很多时候感觉像要实现的功能更通用,就得做更多的检查,更多的校验也就意味着有更多的预先条件,这些条件里面有些是规范和建议,有些是按照已有的配置情况,尽管如此,自己感觉还是缺少了太多的检查。   先来说说今天尝试的简单脚本,就是给主库添加standby logfile,这个需求听起来非常简单,都甚至在我的半自动化脚本中隐去了,但是把这个需求要落到纸面上来,简直了。 首先这个需求会涉及到下面的几个数据
今天在测试时遇到一个ORA-38706ORA-38707报错,乍一看到报错内容竟然没有回过神儿来。 ORA-38706ORA-38707报错 点击( 此处 )折叠或打开 SYS @ HOEGH select banner from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition R

Oracle之虚拟索引 - 2016-08-21 14:08:07

Oracle 之虚拟索引 1    BLOG 文档结构图     2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  Oracle 虚拟索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新 ②

MySQL 事务提交过程 - 2016-08-18 17:08:13

开发老大要求通过binlog查询一条被修改的数据,数据被查出后问我,有没有可能binlog中不会记录,回答不会,因为数据被修改,若失败直接回滚,不会在binlog中记录,此刻一个朋友用了洪荒之力告诉我,失败的话也会记录,坐地无语,因为他sqlserver dba,用sqlserver的思维考虑mysql,哈哈哈哈哈,用实验让他闭嘴! 简单测试步骤如下: root(yoon) flush logs; Query OK, 0 rows affected (0.01 sec) root((none)) show