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: @OracleSK
Jessica Mason <jessica.masson85@xxxxxxxxx> 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).
(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
