Partitioned Table Incremental Statistics Gathering Scans Entire Table Instead of Relevant Partitions (Doc ID 1541543.1)

Last updated on APRIL 01, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

Incremental Stats Gathering performs "FULL TABLE SCAN" instead of only relevant or specified partitions.

As per documentation:
If the INCREMENTAL value for a partition table is set to TRUE, and GRANULARITY set to AUTO, and PUBLISH set to TRUE, and AUTO_SAMPLE_SIZE set to AUTO, Oracle will gather statistics and updates the global table statistics by scanning only those partitions and not the entire table.

But it performs a FULL TABLE SCAN as seen in EXPLAIN PLAN.

BEGIN dbms_stats.gather_table_stats('SCOTT','TEST_TABLE','P012'); END;
Explain Plan:
------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id  | Operation                      | Name         | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0   | SELECT STATEMENT               |              |       |       |  2608 |           |      |      |           |       |       |
| 1   |  SORT AGGREGATE                |              |     1 |   246 |       |           |      |      |           |       |       |
| 2   |   PX COORDINATOR               |              |       |       |       |           |      |      |           |       |       |
| 3   |    PX SEND QC (RANDOM)         | :TQ10000     |     1 |   246 |       |           |:Q1000| P->S |QC (RANDOM)|       |       |
| 4   |     SORT AGGREGATE             |              |     1 |   246 |       |           |:Q1000| PCWP |           |       |       |
| 5   |      APPROXIMATE NDV AGGREGATE |              |   26K | 6484K |  2608 |  00:00:32 |:Q1000| PCWP |           |       |       |
| 6   |       PX BLOCK ITERATOR        |              |   26K | 6484K |  2608 |  00:00:32 |:Q1000| PCWC |           | KEY   | KEY   |
| 7   |        TABLE ACCESS FULL       | TEST_TABLE   |   26K | 6484K |  2608 |  00:00:32 |:Q1000| PCWP |           | KEY   | KEY   |
------------------------------------------------------+-----------------------------------+-------------------------+---------------+



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms