-
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.)
begin
dbms_stats.gather_table_stats(
ownname => 'DBADMIN',
tabname => 'MY_BIG_TABLE',
estimate_percent => 100,
degree => 16,
granularity => 'ALL',
cascade => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE 1'
);
end;
/
-
you will need to take traces and compare the plans and see why the database is doing what it is doing
https://blogs.oracle.com/optimizer/e...iles_continued
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|