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 17:52:53 -0800 (PST)
Message-ID: <6e7c7add-48f8-4902-9536-39c0f6d1ca42_at_y10g2000prg.googlegroups.com>
On Nov 8, 3:19 am, Randolf Geist <mah..._at_web.de> wrote:
> On Nov 7, 10:31 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:
>
> > 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
>
> Jonathan,
>
> that was one of my first thoughts as well but I was a bit reluctant to
> draw this conclusion because - probably due to the "dynamic proration"
> feature - there are more ALL_ROWS plans further down the trace file,
> leading e.g. to this one:
>
> First K Rows: K = 100.00, N = 73255.00
>
> which makes the sort operation very likely look much cheaper, so I
> wasn't entirely sure that this is the root cause of the problem, given
> that we obviously don't see the complete trace file.
>
> Regards,
> Randolf
>
> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> SQLTools++ for Oracle (Open source Oracle GUI for Windows):http://www.sqltools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/
Date: Sat, 7 Nov 2009 17:52:53 -0800 (PST)
Message-ID: <6e7c7add-48f8-4902-9536-39c0f6d1ca42_at_y10g2000prg.googlegroups.com>
On Nov 8, 3:19 am, Randolf Geist <mah..._at_web.de> wrote:
> On Nov 7, 10:31 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:
>
> > 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
>
> Jonathan,
>
> that was one of my first thoughts as well but I was a bit reluctant to
> draw this conclusion because - probably due to the "dynamic proration"
> feature - there are more ALL_ROWS plans further down the trace file,
> leading e.g. to this one:
>
> First K Rows: K = 100.00, N = 73255.00
>
> which makes the sort operation very likely look much cheaper, so I
> wasn't entirely sure that this is the root cause of the problem, given
> that we obviously don't see the complete trace file.
>
> Regards,
> Randolf
>
> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> SQLTools++ for Oracle (Open source Oracle GUI for Windows):http://www.sqltools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/
Hi Randolf,
The trace file is too long, I have saperated into 6 parts, to see whole information of each part, please click read more link in the left bottom. Received on Sat Nov 07 2009 - 19:52:53 CST