一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。

Physical Reads

Executions

Reads per Exec

%Total

Elapsed Time (s)

%CPU

%IO

SQL Id

SQL Module

13,092,700

0

9.47

294.8

52.7

41.06

6src3hcd9mpt3

T.O.A.D.

SQL Text

select * from mo1_memo where entity_id in (11889308, 11889311, 11888262, 11888261, 11889301) order by memo_date desc

这条语句看起来很简单,自己印象中这个表中有一个相关的索引。

INDEX_NAME                               INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    	 TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
MO1_MEMO_1IX                              FUNCTION-BASED NORMAL  NONUNIQUE YES SYS_NC00031$,ENTITY_TYPE_ID,APP_ID TABLE      N/A     554899259 01-APR-15 N                                                                                                  
MO1_MEMO_2IX                              NORMAL     NONUNIQUE YES MEMO_EXTERNAL_ID               TABLE      N/A     478847583 01-APR-15 N
MO1_MEMO_PK                               NORMAL     UNIQUE    YES MEMO_ID,APP_ID,ENTITY_KEY,PERIOD_KEY TABLE      N/A     554900387 01-APR-15 N

有一个基于函数的索引,我们可以通过exp 或者dbms_metadata来得到相关的语句,发现索引是类似下面的形式。 create index MO1_MEMO_1IX on MO1_MEMO(entity_id desc,entity_type_id,app_id); 这是一个降序索引。对于这种降序索引,会在表中创建一个隐藏列。

SQL> select owner,column_name from all_tab_cols where table_name='MO1_MEMO' and hidden_column='YES';
APPO     SYS_NC00031$

有了这些信息,感觉应该是可以走索引扫描的。 但是得到的执行计划中却走了全表扫描,对一个数据量5亿多数据的表走全表扫描,杀伤力是很大的。 但是奇怪的是使用下面两种形式就没有任何问题,索引都能正常启用。 select * from mo1_memo where entity_id in (11889308) order by memo_date desc select * from mo1_memo where entity_id =11889308 order by memo_date desc 一般来说降序索引在其值不为空的情况会启用,根据目前的表结构来看entity_type_id和app_id有着not null constraint,所以应该能够启用才对。 带着这个问题,我在11g的环境中简单模拟了一把。

SQL> create table test as select *from all_objects where rownum<10000;
Table created.
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                 NOT NULL NUMBER
 EDITION_NAME                                       VARCHAR2(30)
SQL> create index idx_test on test(object_id desc,object_name,object_type);
Index created.
SQL> select object_id from test where rownum<3;
 OBJECT_ID
----------
   2880583
   2880575
SQL> set autot trace exp stat

下面两种情况的执行计划是一致的。

SQL> select *from test where object_id in (2880583,2880575);
SQL> select *from test where object_id=2880583;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   127 | 20066 |     5 |
|*  1 |  TABLE ACCESS FULL| TEST |   127 | 20066 |     5 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=2880575 OR "OBJECT_ID"=2880583)

SQL> select *from test where object_id in (2880583)
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     2 |   316 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     2 |   316 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3BFCA6F9ABFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=2880583)

根据上面的输出,感觉降序索引的细节上还有存在一定的问题,在优化器中可能没有很好的支持,查看MOS也没有找到相关的bug. 但是在12c的环境中,结果却明显不同,可见再优化器内部对于这种场景已经做了优化。

SQL> create table test as select *from all_objects where rownum<10000;
Table created.
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 OBJECT_NAME                               NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(23)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                 NOT NULL NUMBER
 EDITION_NAME                                       VARCHAR2(128)
 SHARING                                            VARCHAR2(13)
 EDITIONABLE                                        VARCHAR2(1)
 ORACLE_MAINTAINED                                  VARCHAR2(1)
SQL> create index idx_test on test(object_id desc,object_name,object_type);
Index created.
SQL> select object_id from test where rownum<3;
 OBJECT_ID
----------
     10359
     10358
SQL> set autot trace exp stat
SQL> select object_name from test where object_id in(10359,10358);
Execution Plan
----------------------------------------------------------
Plan hash value: 3459894390
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     2 |   158 |     3   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |          |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEST |     2 |   158 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3CFDFBC4FF') OR
              SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3CFDFBC3FF'))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=10358 OR
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=10359)

所以技术的进步总是一点一滴,新版本中已经做了修复,但是目前来看11g还是主流,所以我们在创建降序索引的时候还是需要注意,避免一些不必要的情况发生。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-04-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

3104
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3178
来自专栏杨建荣的学习笔记

oracle坏块修复实例

最近几天发现库里有坏块了,环境是11gR2, linux平台的64位的库。以下是我的修复办法,基于dbms_repair做的在线修复,也可以基于备份rman来修...

3569
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

1643
来自专栏杨建荣的学习笔记

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

3655
来自专栏黑泽君的专栏

day05_MySQL学习笔记_02

    注:每个表中至少要有一个主键。 主键用于唯一地标识表中的每一条记录,可以定义一列或多列为主键。 

942
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

2155
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

28211
来自专栏me的随笔

T-SQL基础(五)之增删改

在前面的文章中对T-SQL的查询做了基本总结,接下来我们看下SQL中的另外一个常用操作——数据的修改。

872
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

2992

扫码关注云+社区

领取腾讯云代金券