Uncategorized

Pending statistics and partition queries

Posted by

This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I thought I would bring it to your attention.  The issue occurs when optimizing a query that accesses a single partition via pending statistics.



SQL> create table t
  2  partition by range(x)(
  3     partition p1 values less than (2),
  4     partition p2 values less than (3),
  5     partition p3 values less than (4),
  6     partition p4 values less than (5),
  7     partition p5 values less than (6) )
  8  as
  9  select a.*, mod(rownum,5)+1 x
 10  from all_objects a
 11  where rownum <=10;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   172 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("X"=2)

So far so good. The table is tiny (only 10 rows, and 2 rows per partition) and hence the optimizer has got this estimate spot on. Now we will

  • inflate one of the partitions with another ~100,000 rows.
  • turn off the publishing of statistics
  • gather a set of pending statistics with the new data

SQL>
SQL> set autotrace off
SQL>
SQL> insert into t
  2  select a.*, 2 x
  3  from all_objects a;

96983 rows created.

SQL>
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
----------------------------------------------------------------------------------
TRUE

1 row selected.

SQL>
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL>
SQL> show parameter optimizer_use_pend

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
-------------------------------------------------------------------------------
FALSE

1 row selected.

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2         19 12-OCT-16

1 row selected.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2         19 12-OCT-16

1 row selected.

SQL>
SQL> select num_rows,blocks
  2  from user_tab_pending_stats
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS
---------- ----------
     96985       2032

1 row selected.

At this point, optimization should be unchanged, because we have only collected pending statistics – and we can see that this is the case


SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   172 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("X"=2)

Once again, so far so good. Now we will change our session so that it picks up the pending statistics


SQL> alter session set optimizer_use_pending_statistics=true;

Session altered.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   172 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   172 |     7   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   172 |     7   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("X"=2)

And….. ker-splat Sad smile. We still didn’t pick up the pending statistics. This is the known issue, and it occurs when accessing pending statistics for a single partition. If we go ahead and publish the statistics, then we’ll see the correct numbers come into the optimizer consideration.


SQL> exec dbms_stats.PUBLISH_PENDING_STATS(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      | 96976 |    10M|   556   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      | 96976 |    10M|   556   (1)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    | 96976 |    10M|   556   (1)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("X"=2)

SQL>

So this is just something to be aware of if you are using pending statistics and partitioned tables.

And there is an easy way to fix this – just move to our Exadata Cloud Express, where the issue is no longer present !



SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 14:01:26 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 11 2016 10:58:30 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production


SQL> create table t
  2  partition by range(x)(
  3     partition p1 values less than (2),
  4     partition p2 values less than (3),
  5     partition p3 values less than (4),
  6     partition p4 values less than (5),
  7     partition p5 values less than (6) )
  8  as
  9  select a.*, mod(rownum,5)+1 x
 10  from all_objects a
 11  where rownum <=10;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     2 |   214 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE    |      |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------

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

   2 - storage("X"=2)
       filter("X"=2)

SQL>
SQL> set autotrace off
SQL>
SQL> insert into t
  2  select a.*, 2 x
  3  from all_objects a;

62286 rows created.

SQL>
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
------------------------------------------------------------------------
TRUE

SQL>
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL>
SQL> show parameter optimizer_use_pend

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
SQL>
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
--------------------------------------------------------------------------------
FALSE

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2          1 12-OCT-16

SQL>
SQL> --exec dbms_stats.gather_table_stats(user,'T',partname=>'P2');
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2          1 12-OCT-16

SQL>
SQL> select num_rows,blocks
  2  from user_tab_pending_stats
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS
---------- ----------
     62288       1273

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     2 |   214 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE    |      |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------

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

   2 - storage("X"=2)
       filter("X"=2)

SQL>
SQL> alter session set optimizer_use_pending_statistics=true;

Session altered.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      | 62282 |  8271K|   348   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE    |      | 62282 |  8271K|   348   (1)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    | 62282 |  8271K|   348   (1)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------

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

   2 - storage("X"=2)
       filter("X"=2)

See ? The cloud fixes everything Smile

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.