RE: SQL Query tuning - Index stats

Extended column statistics might be on target. After the tutorial I’d read the writings of JL and Chris Antognini if you’re still having trouble.  

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jessica Mason Sent: Thursday, June 09, 2016 11:41 AM
To: ORACLE-L
Subject: SQL Query tuning - Index stats  

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        

--
http://www.freelists.org/webpage/oracle-l