RE: SQL Query tuning - Index stats

Of course in my quick typing that is rubbish because I left out the from clauses.

select /* test */ count(*) from
(
select mb.gds_id from f1 mb where (mb.gds_id = '124') and mb.month_id between '2502' and '2513' union all
select mb.gds_id from f1 mb where (mb.gds_id = '126') and mb.month_id between '2502' and '2513' )

-----Original Message-----
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 2:02 PM
To: contact_at_soocs.de; jessica.masson85_at_gmail.com Cc: 'ORACLE-L'
Subject: RE: SQL Query tuning - Index stats

And just to humor a dinosaur, maybe try this:

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' select /* test */ count(*) from
(
select mb.gds_id where (mb.gds_id = '124') and mb.month_id between '2502' and '2513' union all
select mb.gds_id where (mb.gds_id = '126') and mb.month_id between '2502' and '2513' )

and if your types happen to be numeric, try your original query and my revision with numeric predicates.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Koehler Sent: Thursday, June 09, 2016 1:29 PM
To: jessica.masson85_at_gmail.com
Cc: ORACLE-L
Subject: Re: SQL Query tuning - Index stats

Hey Jessica,
ok, but this execution plan makes way more sense :)

> (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 ?

Yes or closely related to (if global).

> (2) If so, then why Oracle is not collecting the correct number. Even the fresh stats on the indexes return the same NUM_ROWS.

How did you gather the statistics? Your table (and indexes??) is partitioned and the query spans partition 193 to 204 - so global statistics are used in this case. Did you also updated the global stats?

> (4) How can this issue be fixed?

Can not answer this with the provided information. However you can create a SQLd360 report (https://mauro-pagano.com/2015/02/16/sqld360-sql-diagnostics-collection-made-faster/) for this particular SQL and drop by mail.

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> hat am 9. Juni 2016 um 18:21 geschrieben:
>
> 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 :
>
> Thanks
> JM.

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


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


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