Incorrect Column Statistics Causing Queries To Take Bad Plans (Doc ID 552363.1)

Last updated on MARCH 03, 2022

Applies to:

Symptoms

Column statistics for character columns is not captured correctly by the dbms_stats.

For example:-

Lets consider a table test containing 143362 rows.

SQL> Select count (*) from test;

COUNT (*)
----------
143362


SQL> Begin
           Dbms_stats.gather_table_stats (
           ownname => '<owner>', 
           tabname => 'test',
           estimate_percent => 100,
           block_sample => false,
           method_opt => 'for all indexed columns size 250',
           degree => 2,
           granularity => 'all',
           cascade => true);
         End;
/

SQL> Select count (1), count (distinct (RESOURCE_PATH)) from test;

COUNT (1)   COUNT (DISTINCT (RESOURCE_PATH))
----------       ------------------------------
143362          143356


SQL> Select table_name, column_name, num_distinct, last_analyzed
           From user_tab_columns
           where table_name = 'TEST' and column_name=’RESOURCE_PATH’;

TABLE_NAME   COLUMN_NAME       NUM_DISTINCT     LAST_ANALYZED
-----------------   ------------------------  ---------------------  ---------------------
TEST                   RESOURCE_PATH       362                            30-JAN-08

The number of distinct values that it shows for column resource_path is 362
and the actual number of distinct values is 143356.

Cause

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