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

Last updated on OCTOBER 27, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.4 to 11.2.0.0. - Release: 9.0.1 to 11.2
Information in this document applies to any platform.

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 => 'kirti', 
           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

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