RE: SQL Query tuning - Index stats

By the way, you don’t necessarily have to TAKE the tutorial. Depending on your level of expertise, just reading the text overview of the tutorial may well be sufficient to pattern match to your exact situation.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Thursday, June 09, 2016 11:49 AM
To: jessica.masson85_at_gmail.com; 'ORACLE-L' Subject: 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