query performance went bad with the most updated statistics
I have a query performance issue which I believe is related to statistics collection.
There are 3 tables in the query and one of the table is 30GB. The other 2 tables are small.
When there is no statistics in the large table, the query takes about 6 minutes to complete.
After gathering the statistics, the query takes 15 HOURS to complete.
Why a 100% accurate statistics would result in a far worse performance?
What could have gone wrong?
Here is the way I collect the statistics:
(The query does not use bind variables.)
ownname => 'DBADMIN',
tabname => 'MY_BIG_TABLE',
estimate_percent => 100,
degree => 16,
granularity => 'ALL',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE 1'
you will need to take traces and compare the plans and see why the database is doing what it is doing
Click Here to Expand Forum to Full Width