Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.

On Nov 7, 5:31 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> "lsllcm" <lsl..._at_gmail.com> wrote in message>> news:871566ee-22ef-4895-bcc6-753269a7bc3a_at_f18g2000prf.googlegroups.com...>> > The trace file is too long, I use three parts.>> > *************************************> > PARAMETERS WITH ALTERED VALUES> > ******************************> > Compilation Environment Dump> > optimizer_features_enable = 11.1.0.6> > optimizer_mode = first_rows_100> > optimizer_index_cost_adj = 10> > optimizer_index_caching = 90> > _optimizer_cost_based_transformation = off>> Sorry about picking this one up so late.>> I've had a quick read through the posts I can see at the moment.> The critical issue is (as Charles and Randolf have pointed out)> the use of first_rows_100, combined with Randolf's observation> that the expected result set is 40 million rows.>> >> First K Rows: K = 100.00, N = 40640900.00> >> First K Rows: Setup end>> When you don't have the order by clause, Oracle is working on a> plan that will pick up any 100 rows as quickly as possible. When> you add the order by the optimizer "knows" it has to acquire 40M> rows and sort them before returning the first 100. Consequently> any path that avoids doing the sorting is likely to be a good path -> hence the choice of index to drive the query.>> --> Regards>> Jonathan Lewishttp://jonathanlewis.wordpress.com>> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html>> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html