当前位置: 首页 > Oracle, oracle 11g > 正文

PARTITION RANGE EMPTY是什么鬼

我同学小何遇到了一个很有趣的问题,在查询一张分区表的时候性能比较差,执行计划竟然出现了PARTITION RANGE EMPTY,这是什么鬼?分区不可用,那么为什么会出现这个呢?

下面是他的SQL语句:

SELECT /*+parallel*/ count(1) cnt  FROM xxxxxx  partition(P_2017M07) where DATA_DATE= '2017-08-29';

他指定了分区查询的,下面是这条SQL的执行计划:

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    40 |   788   (4)| 00:00:10 |       |       |
|   1 |  SORT GROUP BY                |          |     1 |    40 |   788   (4)| 00:00:10 |       |       |
|   2 |   NESTED LOOPS                |          |     1 |    40 |   788   (4)| 00:00:10 |       |       |
|   3 |    NESTED LOOPS               |          |    25 |    40 |   788   (4)| 00:00:10 |       |       |
|   4 |     PARTITION RANGE EMPTY     |          |     1 |    24 |   788   (4)| 00:00:10 |INVALID|INVALID|
|*  5 |      TABLE ACCESS FULL        | xxxxx    |     1 |    24 |   788   (4)| 00:00:10 |INVALID|INVALID|
|*  6 |     INDEX RANGE SCAN          | IDX_xxx  |    25 |       |     2   (0)| 00:00:01 |       |       |
|   7 |    TABLE ACCESS BY INDEX ROWID| xxxxxxxxx|    25 |   400 |     5   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------

他为了证明这是分区表,还发了下这个分区的创建信息,如下:

partition P_2017M07 values less than ('2017-08-01')
    tablespace TBS_XXX
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    )

其实到这里就已经暴露了导致PARTITION RANGE EMPTY的原因,仔细观察上面的SQL和分区信息就会发现,SQL中指定的时间和这个分区的时间范围是不一致的,也就是说,他查询的时间范围在这个分区是不存在的,这就导致了PARTITION RANGE EMPTY。

为了验证是不是这样,下面进行一下实验,在我的测试库找到一张按天分区的分区表,查询一下分区信息,随便找到一个分区,这里使用SYS_P18983这个分区,查看一下这个分区存的是哪天的数据。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT INST_DTM from TABLE_NAME_XXX PARTITION (SYS_P18983) where rownum=1;

INST_DTM
-------------------
2016-10-17 18:07:38

这是按天分区的分区表,这个分区只能存放2016-10-17这一天的数据,查询这个分区并且指定查询时间不是2016-10-17,看下执行计划是不是也会出现PARTITION RANGE EMPTY。

SQL> SELECT COUNT(1) FROM TABLE_NAME_XXX PARTITION (SYS_P18983) WHERE INST_DTM=TO_DATE('2017-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(1)
----------
         0

Execution Plan
----------------------------------------------------------
Plan hash value: 2534380

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE EMPTY|                |     1 |     8 |     3   (0)| 00:00:01 |INVALID|INVALID|
|*  3 |    INDEX RANGE SCAN    | INDEX_NAME_XXX |     1 |     8 |     3   (0)| 00:00:01 |INVALID|INVALID|
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("INST_DTM"=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
       1084  consistent gets
          6  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到,这里也出现了PARTITION RANGE EMPTY,也就验证了,我同学小何遇到这个问题就是因为他查询的SQL使用的时间和指定分区存放的数据不匹配导致的这个问题。

下面看下用正确的时间查询这个分区,正确的执行计划是什么样的。

SQL> SELECT COUNT(1) FROM TABLE_NAME_XXX PARTITION (SYS_P18983) WHERE INST_DTM=SYTO_DATE('2016-10-17 00:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(1)
----------
         0

Execution Plan
----------------------------------------------------------
Plan hash value: 142327796

----------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                |     1 |     8 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|                |     1 |     8 |     1   (0)| 00:00:01 |   381 |   381 |
|*  3 |    INDEX RANGE SCAN     | INDEX_NAME_XXX |     1 |     8 |     1   (0)| 00:00:01 |   381 |   381 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("INST_DTM"=TO_DATE(' 2016-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
         43  consistent gets
          3  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
          1  rows processed

通过上面两个SQL的统计信息可以看到,两个SQL都产生了逻辑读和物理读,那么问题来了,分区表的分区列,查询的时间在不在这个分区里,Oracle是知道的,既然知道这个分区不包含这个时间,那么Oracle还会真正扫描这个表或者索引吗?

Oracle当然不会做那些无用功,如果是普通堆表的话,Oracle是不知道查询的数据是否存在的,必须要扫描表或者索引才会知道结果,但对分区表来说,Oracle通过分区的信息是可以直接知道查询的数据分布在具体哪个分区,是否有满足条件的分区,或者指定的分区是否存在要查询的数据,像这种查询的数据并不存在指定的分区的情况,Oracle通过分区信息直接就可以判断出来那个分区没有想要查询的数据,所以就会走PARTITION RANGE EMPTY这个执行计划,直接返回没有查询到数据的结果,并不需要再去扫描分区或者索引。

那么上面的测试两个SQL都产生了逻辑读和物理读是咋回事?我估计是因为这两个SQL都是第一次执行导致的,还有就是AUTOTRACE采集的统计信息是估算值,并不是非常准确。

再次运行正确的SQL,统计信息变成了两个逻辑读。

SQL> SELECT COUNT(1) FROM TABLE_NAME_XXX PARTITION (SYS_P18983) WHERE INST_DTM=TO_DATE('2016-10-17 00:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(1)
----------
         0

Execution Plan
----------------------------------------------------------
Plan hash value: 142327796

---------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                |    1 |     8 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |                |    1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|                |    1 |     8 |     1   (0)| 00:00:01 |   381 |   381 |
|*  3 |    INDEX RANGE SCAN     | INDEX_NAME_XXX |    1 |     8 |     1   (0)| 00:00:01 |   381 |   381 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("INST_DTM"=TO_DATE(' 2016-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

再运行几次统计信息都是一样,两个逻辑读,而执行使用不匹配分区的时间查询,逻辑读和物理读都是0。

SQL> SELECT COUNT(1) FROM TABLE_NAME_XXX PARTITION (SYS_P18983) WHERE INST_DTM=TO_DATE('2017-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(1)
----------
         0

Execution Plan
----------------------------------------------------------
Plan hash value: 2534380

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE EMPTY|                |     1 |     8 |     3   (0)| 00:00:01 |INVALID|INVALID|
|*  3 |    INDEX RANGE SCAN    | INDEX_NAME_XXX |     1 |     8 |     3   (0)| 00:00:01 |INVALID|INVALID|
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("INST_DTM"=TO_DATE(' 2017-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

多运行几次结果都是一样,这也说明了,在使用不满足范围分区的分区字段进行查询指定分区的时候,Oracle通过分区的特性,可以知道这个分区内不包含要查询的数据,然后直接返回数据没查到的结果,而不会正在去扫描表或者索引。

通过追踪,会发现使用“正确”的时间查询,Oracle底层操作要比使用“不正确”的时间的操作多很多,而且,也会看到使用“正确”的时间查询产生了2个逻辑读,而使用“不正确”的时间查询,并没有产生逻辑读,也就是Oracle发现这个分区没有满足查询条件的数据后,压根就没干活。

但是这些都不是重点,可能已经有人发现了,他的这张表竟然使用的是char类型的字段存放的时间,而且还是用这个char类型的字段做的范围分区,这就导致了他这张分区表,数据分布根本就不是那么回事,大部分数据都在同一个分区内,性能非常低下。

本文固定链接: http://www.dbdream.com.cn/2017/09/partition-range-empty%e6%98%af%e4%bb%80%e4%b9%88%e9%ac%bc/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2017年09月06日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: PARTITION RANGE EMPTY是什么鬼 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , ,

PARTITION RANGE EMPTY是什么鬼:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter