Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
From: lsllcm <lsllcm_at_gmail.com>
Date: Sat, 7 Nov 2009 06:53:05 -0800 (PST)
Message-ID: <90a3cf64-ea67-4835-8d19-a5627cc28c68_at_g1g2000pra.googlegroups.com>
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
Date: Sat, 7 Nov 2009 06:53:05 -0800 (PST)
Message-ID: <90a3cf64-ea67-4835-8d19-a5627cc28c68_at_g1g2000pra.googlegroups.com>
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
Thanks Jonathan, Received on Sat Nov 07 2009 - 08:53:05 CST