Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance Issue (8i to 9i migration)

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

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 6 May 2004 09:24:13 -0700
Message-ID: <1ac7c7b3.0405060824.538b2378@posting.google.com>


kongkong99_at_yahoo.com (kong) 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.
>
> Thanks.
>
>
> Warmest Regards,
> Felicia

Felicia,

what amount of testing of the application code took place on 9.2.0.5? Did you rollback the migration?
How severely is this change in execution time impacting your business? Do you have time to learn how to fix this yourself, or does it need to be fixed immediately?
Are you looking for a duct-tape/tourniquet type of solution, or do you want to fix this fundementally?

If you have a budget and little time, bring in a consultant, say Hotsos or someone like Jonathan Lewis, Wolfgang Breitling.

If you have a budget and lots of time, attend some seminars, say like those offered by Hotsos.

if you have neither a budget nor time, and cannot roll back the migration, I would offer a tourniquet of the following:

  1. check that statistics have been properly gathered, using dbms_stats.gather_schema_stats with the "cascade=>true" option.
  2. check that the optimizer parameters are set properly in the init.ora or spfile, and also check parameters such as cursor_sharing.
  3. set the parameter optimizer_features_enable=8.1.7.

this may get you by. As Howard mentions, without seeing an explain plan of the statement, we're just guessing.

please generate 10046 trace and 10053 trace for the statements in question.

Check out Cary Milsap's book "Optimizing Oracle Performance".

hth.

Pd Received on Thu May 06 2004 - 11:24:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US