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

PARTITION RANGE EMPTY是什么鬼

Sep062017
作者：dbdream   发布：2017-09-06 17:07   分类：Oracle, oracle 11g     抢沙发  

我同学小何遇到了一个很有趣的问题，在查询一张分区表的时候性能比较差，执行计划竟然出现了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类型的字段做的范围分区，这就导致了他这张分区表，数据分布根本就不是那么回事，大部分数据都在同一个分区内，性能非常低下。

本文固定链接: https://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