SQL Query tuning - Index stats

  • From: Jessica Mason <jessica.masson85@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 9 Jun 2016 21:10:30 +0530

Dear List,


Oracle version - 11.2.0.4.5
OS - Redhat Linux
3-node RAC cluster hosting  dataware house database, size 17 Tb


The execution plan of a simple SELECT statement  is showing  different
values for estimated rows (96m)  and actual rows (0).


select /* test */ count(*) From F1 MB where  (MB.GDS_ID    IN ('124') OR
MB.GDS_ID IN ('126')) AND MB.MONTH_ID BETWEEN '2502' AND '2513' ;


---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Starts |
E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |      1 |
     |      1 |00:00:10.97 |      36 |       |       |          |
|   1 |  SORT AGGREGATE                   |                    |      1 |
   1 |      1 |00:00:10.97 |      36 |       |       |          |
|   2 |   PX COORDINATOR                  |                    |      1 |
     |     12 |00:00:10.97 |      36 |       |       |          |
|   3 |    PX SEND QC (RANDOM)            | :TQ10000           |      0 |
   1 |      0 |00:00:00.01 |       0 |       |       |          |
|   4 |     SORT AGGREGATE                |                    |      0 |
   1 |      0 |00:00:00.01 |       0 |       |       |          |
*|   5 |      PX PARTITION RANGE ITERATOR  |                    |      0 |
    96M|      0 |00:00:00.01 |       0 |       |       |          |*
*|   6 |       BITMAP CONVERSION COUNT     |                    |      0 |
    96M|      0 |00:00:00.01 |       0 |       |       |          |*
|   7 |        BITMAP AND                 |                    |      0 |
     |      0 |00:00:00.01 |       0 |       |       |          |
|   8 |         BITMAP OR                 |                    |      0 |
     |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |          BITMAP INDEX SINGLE VALUE| F1_GDS_ID_IDX   |      0 |
   |      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |          BITMAP INDEX SINGLE VALUE| F1_GDS_ID_IDX   |      0 |
   |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |         BITMAP MERGE              |                    |      0 |
     |      0 |00:00:00.01 |       0 |  1024K|   512K|          |
|* 12 |          BITMAP INDEX RANGE SCAN  | F1_MONTH_ID_IDX |      0 |
   |      0 |00:00:00.01 |       0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------




As per the Oracle blogs and most of the SQL tuning articles, this is an
indication of bad statistics, so I started looking into the statistics of
table and indexes.  The statistics for the table F1 are correct and
recently gathered.

SQL> select NUM_ROWS, BLOCKS , EMPTY_BLOCKS, AVG_ROW_LEN from user_tables
where table_name = 'F1' ;

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ---------- ------------ -----------
2063134584   14734427            0         173


The table has 2063 million rows ( it is a FACT table).


SQL> select count(*) from  F1 ;

  COUNT(*)
----------
2063134584


But, for the indexes, the NUM_ROWS columns is showing only few thousands
rows -


INDEX_NAME                     DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
NUM_ROWS
------------------------------ ------------- -----------------------
----------
FGODM_GDS_ID_IDX                           5                    4733
*46203*
FGODM_MONTH_ID_IDX                       155                     144
*43341*


As per Oracle documention, NUM_ROWS for an index shows number of rows in an
index. Then I thought,  may be there are rows with NULL values and as these
entries are not stored in an index, the NUM_ROWS column for an index could
be lesser than NUM_ROWS for the table ( i guess this is only applicable to
b-tree indexes and the indexes in question are bitmap indexes, still ). But
there are no rows with NULL value either.

SQL> select count(*) from F1 where *gds_id is not null* ;

  COUNT(*)
----------
2063134584

SQL> select count(*) from F1 where *month_id is not null* ;

  COUNT(*)
----------
2063134584




So, my questions are -

(1) Shouldn't  the NUM_ROWS for both the indexes be 2063134584 as there are
no rows with NULL value and these are bitmap indexes ?
(2) If so, then why Oracle is not collecting the correct number. Even the
fresh stats on the indexes return the same NUM_ROWS.
(3) Why and how the CBO is estimating 96m rows where as actual rows is 0?
(4) How can this issue be fixed?

Is this a bug or I'm missing something?




Thanks
JM

Other related posts: