Re: Performance Issue (8i to 9i migration)

From: Stan <stan0074_at_removethis.yahoo.com>
Date: Fri, 07 May 2004 03:19:09 GMT
Message-ID: <409B0035.2070608@removethis.yahoo.com>

Adding to what richard had said, there was another now a undocumented parameter _unnest_subquery been set to true, but prior to 9.x release it was set to false. This could be your problem, if the query in question had subquery in it.

As howard pointed out, start comparing the execution plans between 8i/9i, that should get you off a good start.

Stan

Richard Foote wrote:
> "kong" <kongkong99_at_yahoo.com> wrote in message
> news:cc50806d.0405060023.195eb3be_at_posting.google.com...
>

>>Hi All,
>>
>>Seems that 8i to 9i migration give me a big shock.
>>While we were using 8i, it took 2minutes to execute a package/view.
>>But 9i takes more than 3 hours..
>>It doesn't make sense.
>>
>>So, I'm wondering is there any thing i've missed out?
>>For example some queries can not run in proper way as it is in 8i?
>>Or, there are major changes in queries that i need to do?
>>
>>Can someone please help me or provide me as much information ..
>>I appreciate your help so much.
>>

>
>
> Hi Felicia,
>
> Simplistically, you need to learn how to trace a session and see what's
> going on. The number of people that tune databases as if it's a game of
> pinning the tale on the donkey never ceases to amaze me. Learn to trace and
> take the blind fold off is the best advice I can give.
>
> However, with my Gemini powers of mental telepathy, give me the pin and
> let's have a go ....
>
> In 9i, there was a now undocumented parameter called "_b_tree_bitmap_plans"
> that changed it's default value from false to true. This has resulted in
> many sites complaining about sub-optimal execution plans after moving from
> 8i to 9i.
>
> Did I at least hit near the donkey's bum ?
>
> Good Luck
>
> Richard
>
>

-- 
-Stan

To reply remove the obvious ones from my email address

Received on Thu May 06 2004 - 22:19:09 CDT