Re: Performance

Comments embedded.
Jack wrote:
> > Possibly the real issue here is the fact that production is on 9.0.1.4
> > and test is using 9.2.0.4 and changes to the CBO have been implemented.
> > In 9.2, for example, dynamic sampling is available (it is not in
> > 9.0.1); this can make a difference in how the optimiser 'sees' the data
> > since in the absence of computed/estimated statistics the dynamic
> > sampling mechanism will provide some aid to the optimiser. You'll also
> > need to note which bugs in 9.0.1 were fixed in 9.2.0. Jonathan Lewis
> > probably has on his website information on how the optimiser has
> > changed between 9.0.1.x and 9.2.0.x; I would visit
> > http://www.jlcomp.demon.co.uk/ to find out more.
> >
> > Since your environments aren't the same for both systems a comparison
> > between them isn't worth much; 9.0.1.4 on some version of Solaris (no
> > mention of O/S release or patch level and no mention of whether this is
> > x86 or SPARC) compared to 9.2.0.4 running on a Win XP box (no mention
> > of which service packs are installed). Also no mention of the
> > available memory, disk configuration, user load, server load, whether
> > these connections are local or networked, network speed, network health
> > ... too much information is missing to assess this situation
> > accurately.
> >
> >
> > David Fitzjarrell
> >
> Hi!
>
> Well, you are rigth in many point.
> But this is real life situation, none testlab case.
>
> The point is how to fix it?

I point you back to the second paragraph of my post; I would read that again and post the relevant information, such as WHICH version of Solaris, WHICH processor type is it using, how much RAM is installed, the init.ora file used, the disk configuration for the 9.0.1.4 installation. Then, for the 9.2.0.4 installation, which SP is installed on your XP box, how much RAM is installed, the init.ora for THAT configuration, how your disks are configured. And for both how many users are concurrently connected? Possibly run Statspack on both machines in a 15-minute interval which includes this 'troublesome' query. Or, better yet, set up 10046 traces at level 12 to see exactly what is going on with the query plans. And a 10053 trace to see what the CBO is actually doing.

> Or just say that network is busy, server old/slow and db version old.

Sure, if you don't want to know what Oracle is doing, don't want to know why you have performance issues and if you don't want the possibility of fixing this.

> But software is ok, because it is fast in old desktop pc ;)

Hmmm, how did you get a Solaris version of Oracle installed on an 'old desktop PC', presumably running an old version of Windows?

>
> Jack

David Fitzjarrell Received on Fri Sep 15 2006 - 15:02:20 CDT