Re: drop in sql plus query performance from 7.3.4 to 8i

Jeff Kish wrote:
>
> Sorry Sybrand Bakker. I appreciate your feedback.
>
> Well, to start with the server is Oracle 8.1.6 (I guess 8i could mean
> lots of things).
>
> The server is running internally for a limited (8) number of people.
> Because I there are so few people running against the server, and the
> results are very repeatable, I have made the assumption that network
> traffic from others besides me are not curving the results (i.e. that
> I am "seeing" a real problem).
>
> I first had a report about an application we wrote which had a vast
> performance difference between 8.0.4 and 8.1.6 clients. After
> witnessing it first hand, I asked the user to run a simple select *
> from table on the two pc's in question using SQL Plus (to take our
> application out of the arena). I know that the sql plus applications
> can vary also from release to release, but hoped that seeing a
> difference there would allow me to troubleshoot better..
>
> The select was about 8 seconds on the 8.0.4 client, and about 25
> seconds on the 8.1.6 client. This was against the exact same
> table/database/instance.
>
> I had support come in and upgrade the 8.0.4 client to 8.1.6. The same
> pc which previously ran in about 8 seconds with 8.0.4, ran in about 25
> seconds.
>
> So now I am looking at (I think. I realize there is a small sample set
> here) given the same pc, with the only variation being the Oracle
> client, the version of sql plus, and the time of day (how busy the
> database gets) a big performance difference.
>
> The only reason I am ignoring the possibility of database traffic
> causing the problem is because the problems seem so repeatable.
>
> Finally, someone suggested that I run:
>
> SELECT * from V$SESSION_EVENT where SID = your-sid;
> before and after the query in question to get some stats both as a 7
> and 8 client, and compare the before/after 7 differences with the
> before/after 8 differences.
>
> That should be available shortly.
>
> Thanks,
> Jeff
>
> On Tue, 9 Oct 2001 19:15:06 +0200, "Sybrand Bakker"
> <postbus_at_sybrandb.demon.nl> wrote:
>
> >Not when you continue not to provide *any* detail about your configuration,
> >like *which* version of the server,
> >how you have determined the performance drop
> >(sql trace should provide identical results, shouldn't it)
> >etc, etc, etc.
> >I 'm not clairvoyant and I don't have a crystall ball here.
> >
> >Regards,
> >
> >Sybrand Bakker
> >Senior Oracle DBA
> >
> >
> >
> >"Jeff Kish" <jeff.kish_at_ait-mmii.com> wrote in message
> >news:f7p5st0rnvuquhc71ejbddbkor6p6n6oek_at_4ax.com...
> >> The server is an Oracle 8i server.
> >> Any thoughts on how I could trouble shoot this?
> >>
> >> Thanks
> >> Jeff
> >>
> >> On Thu, 4 Oct 2001 19:35:52 +0200, "Sybrand Bakker"
> >> <postbus_at_sybrandb.demon.nl> wrote:
> >>
> >> >
> >> >"Jeff Kish" <jeff.kish_at_ait-mmii.com> wrote in message
> >> >news:parorts0mfkmtf4q7gkuscoompse50gka8_at_4ax.com...
> >> >> Greetings.
> >> >>
> >> >> I am finding that with a simple select * from table
> >> >> on an nt 4.0 workstation, if I have oracle 7.3.4 client installed, it
> >> >> runs 3x faster (against the same NT Oracle 8i database) than it runs
> >> >> if I upgrade JUST the oracle client software to 8.1.6.
> >> >>
> >> >>
> >> >> Any suggestions on troubleshooting this, or fixing the performance
> >> >> drop? This is causing much sadness at my customers site.
> >> >> thanks
> >> >> Jeff Kish
> >> >>
> >> >
> >> >Which version of the database?
> >> >While sqlnet 2 can talk to net8, and net8 seems to be talking to sqlnet2
> >> >they changed fundamentally the messaging between client and server in net
> >8.
> >> >There might be some sort of conversion going on.
> >> >
> >> >Hth,
> >> >
> >> >Sybrand Bakker, Senior Oracle DBA
> >> >
> >> >
> >>
> >
Jeff,

[Quoted] There were some changes from 7x to 8i, but they should not affect performance that dramatically. I doubt very much if there is a problem with performance with reagards to SQL*Plus. I think you need to look at the database for waits, etc.

[Quoted] Do you have any DB performance monitoring going on? If not, I suggest you at least install STATSPACK and go from there. There are several articles published at http://www.oreview.com. You can also look at running utlbstat and utlestat. These two things should be able to pinpoint your DB issues.

Next, have your queries been tuned? How ofter do you gather CBO statistics? Traditionally, the biggest bang for the buck is query tuning.

[Quoted] Also, you should look at the application. Is there a web server issue involved? Some newtwork (non-Oracle) issue?

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.