RE: SQL Query tuning - Index stats

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 9 Jun 2016 20:11:38 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D8A67_at_EXMBX01.thus.corp>

The quick answer to the num_rows question is that it's the number of "rows" i.e. index entries in the bitmap index, and a single bitmap index entry can span literally millions of rows in the table. For small data sets you often find that the index num_rows = distinct_keys because each index entry covers the entire table, and then clustering_factor = num_rows = distinct_keys.

Stefan has answered your question about the missing numbers - i.e. "last" doesn't help with parallel queries. The final point about the silly numbers comes down to a different aspect of bitmap index usage (and one I haven't looked at closely).

The A-row numbers are clearly silly:
  you can't OR 427 items with 0 items and end up with FEWER items.   You can MERGE 1331 items and end up with 107 items

I think this means that Oracle is counting the bit-strings that it acquires, manipulates and generates. At which point you CAN merge 1331 bit strings, from multiple values into a small number of bit strings because the starting strings have a huge overlap. I think the only way you'd see numbers representing "real" table row counts is if you saw a "bitmap conversion to rowid" operation.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jessica Mason [jessica.masson85_at_gmail.com] Sent: 09 June 2016 16:40
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

Received on Thu Jun 09 2016 - 22:11:38 CEST