Tom Kyte has an article on his blog . which deals with explain plans .
This is really interesting since i have recently been very intigued by explain plans. I'mstill investigiating an issue where an explain plan changed between 2 idrntical systems.
and where there was less data but enough to use an index and all the columns that the join was based upon were indexed. after a routine stats gathering the explain plan changed from nested loops to a huge hash join with full tablescans of all involved tables.
whne i hinted indexes it ran in 9 secs . and when i used first_rows hint it showed a very bad explain plan but the query finished in 768 msecs.
i've put that workaround in place till i get time to figure out what is going on .
the database is 9.2.0.8 with optimizer_mode =choose and optimizer_features_enable-8.1.7
and optimizer_index_cost_adj=40
Please note it was working till stats were gathered in a routine stats gathering exercise. since we did nt backup old stats.
i have unable to get it back to where it was regardless of what types of stats i gather or indexes i rebuild.
No comments:
Post a Comment