Re: SQL Query tuning - Index stats

Hi,

As Maria wrote, I prefer sql monitor by far above run stats when it comes to parallel query.
To find out why the estimates are wrong I suggest divide and conquer. Check out the estimates for

select /* test */ count(*) From F1 MB where (MB.GDS_ID IN ('124') OR MB.GDS_ID IN ('126')) and

select /* test */ count(*) From F1 MB where MB.MONTH_ID
BETWEEN '2502' AND '2513'
.

What is really missing here is information about column statistics. What is the result of:

select column_name, num_distinct, density, num_buckets where table_name='F1' and column_name in ('GD_ID','MONTH_ID'); ?
One possible explanation would be no histograms. (num_buckets=0)

Regards

Lothar

On 09.06.2016 18:21, Jessica Mason wrote:
> Hi Stefan,
>
>
> The execution plan was captured using the following statement -
>
> *select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS
> LAST')) ;*
>
>
>
> Here is the output with '*ALLSTATS ALL*' option. The E-rows and A-rows
> are still way off :
>
>
>
>
> COUNT(*)
> ----------
> 56148530
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SQL_ID 24j25th1b63kz, child number 1
> -------------------------------------
> 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'
>
> Plan hash value: 1181118696
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts |
> E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT|
> PQ Distrib | A-Rows |
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | |
> | 1029 (100)| | | | | |
> | 1 |
> | 1 | SORT AGGREGATE | | 1 | 1 |
> 8 | | | | | | |
> | 1 |
> | 2 | PX COORDINATOR | | 1 | |
> | | | | | | |
> | 12 |
> | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0
> | 1 | 8 | | | | | Q1,00 | P->S | QC
> (RAND) | 0 |
> | 4 | SORT AGGREGATE | | 4 | 1 |
> 8 | | | | | Q1,00 | PCWP |
> | 4 |
> | 5 | PX PARTITION RANGE ITERATOR | | 4 | *96M*|
> 739M| 1029 (39)| 00:00:12 | 193 | 204 | Q1,00 | PCWC |
> | *105* |
> | 6 | BITMAP CONVERSION COUNT | | 4 | *96M*|
> 739M| 1029 (39)| 00:00:12 | | | Q1,00 | PCWP |
> | *105* |
> | 7 | BITMAP AND | | 4 | |
> | | | | | Q1,00 | PCWP |
> | 105 |
> | 8 | BITMAP OR | | 4 | |
> | | | | | Q1,00 | PCWP |
> | 105 |
> |* 9 | BITMAP INDEX SINGLE VALUE| F1_GDS_ID_IDX | 4 |
> | | | | 193 | 204 | Q1,00 | PCWP |
> | 429 |
> |* 10 | BITMAP INDEX SINGLE VALUE| F1_GDS_ID_IDX | 4 |
> | | | | 193 | 204 | Q1,00 | PCWP |
> | 0 |
> | 11 | BITMAP MERGE | | 4 | |
> | | | | | Q1,00 | PCWP |
> | 107 |
> |* 12 | BITMAP INDEX RANGE SCAN | F1_MONTH_ID_IDX | 4 |
> | | | | 193 | 204 | Q1,00 | PCWP |
> | 1331 |
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
>
>
> Thanks
> JM.
>
> On Thu, Jun 9, 2016 at 9:27 PM, Stefan Koehler <contact_at_soocs.de
> <mailto:contact_at_soocs.de>> wrote:
>
> Hey Jessica,
> without explaining anything about statistics and the CBO.
>
> How did you capture this execution plan - possibly with DBMS_XPLAN
> "ALLSTATS LAST"? It looks suspicious as you are using PX and all
> "Starts" and
> "A-Rows" under the "PX COORDINATOR" are 0. Please try it with
> DBMS_XPLAN "ALLSTATS ALL" and check again. I am pretty sure that
> this is your issue.
>
> For more details please check Maria's blog post here:
> https://blogs.oracle.com/optimizer/entry/how_do_i_know_if
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Jessica Mason <jessica.masson85_at_gmail.com
> <mailto:jessica.masson85_at_gmail.com>> hat am 9. Juni 2016 um 17:40
> geschrieben:
> >
> > 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