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