Histogram Statistics Are Regathered Unnecessarily For Unchanged Partitions Using Incremental Statistics (Doc ID 2532635.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• Incremental statistics is activated newly for partitioned tables in 12.2.0.1.
• Histogram statistics are collected for all partitions that are unchanged with incremental option.
• METHOD_OPT AUTO is used in DBMS_STATS command.
• Same problem does not happen in the similar clone TEST environment.
• DBMS_STATS trace shows the following:
  DBMS_STATS: gather stats on partition SYS_P%%: synopsis not gathered yet; histograms need to be updated
  DBMS_STATS: gather stats on partition SYS_P%%: synopsis not gathered yet; histograms need to be updated
  DBMS_STATS: gather stats on partition SYS_P%%: synopsis not gathered yet; histograms need to be updated
  DBMS_STATS: gather stats on partition SYS_P%%: synopsis not gathered yet; histograms need to be updated
  ..
  SQL> select PARTITION_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED, STALE_STATS from dba_tab_statistics where table_name='<table_name>';
  PARTITION_NAME SUBPARTITION_NAME NUM_ROWS LAST_ANALYZED
  -------------------- -------------------- ---------- ------------------
  ...
  SYS_P123 58396012 20-JAN-19
  SYS_P%%% 962796 20-JAN-19
  SYS_P%%% 1543295 20-JAN-19
  SYS_P%%% 1165357 20-JAN-19
  SYS_P%%% 116984610 20-JAN-19
  SYS_P%%% 1871086 20-JAN-19
    

In the above example, the changes happened to only SYS_P123 partition which is only stale but the statistics are collected for other partitions as well that do not have any changes reported. Since histograms need to be updated, it scans the partitions that are not stale and histograms are regathered and updated unnecessarily for unchanged partitions.

Changes

 Upgrade to 12.2.0.1

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.