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

From: Jeff Kish <jeff.kish_at_ait-mmii.com>
Date: Tue, 09 Oct 2001 17:16:03 -0400
Message-ID: <lco6st8gc18r5qb8cff37coa5k6sjnocjo_at_4ax.com>


[Quoted] 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
>> >
>> >
>>
>
Received on Tue Oct 09 2001 - 23:16:03 CEST

Original text of this message