Sam,
33 seconds of wait time and 533 waits on 10k of data seems
very slow to me.
Jared
SID: 14 User: CPAS_ADMIN on UNIX
client bytes : 10590
client round trips : 169
client avg packet size: 63
TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT
CPAS_ADMIN on UNIX
SQL*Net message to client 169 0 0 0
SQL*Net more data to client 533 0 33.21
6.23
SQL*Net Msg from client 168 0 37.06
Sam Bootsma <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/30/02 02:25 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Performance Conundrum Selecting varchar2(2000) Column
Hello,
Thanks very much for your help Jared. I have included a summary of the
results below. I believe that SQL*Net Msg from client means that Oracle
is
waiting for more work from the client (but I stand to be corrected). I
think the other two events are similar in nature. If anybody can clarify
these waits for me, I'd appreciate it. I wasn't able to get information
on
SDU and TDU settings (Metalink was down).
It looks to me that the network is fine (because there are no waits on the
NT box). I figure either UNIX is forming packets inefficiently, or UNIX
is
on another subnet that is causing delays in the network. I appreciate any
feedback the list can provide.
Here is a summary of the relevant results:
SID: 9 User: CPAS_ADMIN on NT
client bytes : 15485
client round trips : 169
client avg packet size: 92
SID: 14 User: CPAS_ADMIN on UNIX
client bytes : 10590
client round trips : 169
client avg packet size: 63
TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT
CPAS_ADMIN on NT
SQL*Net message to client 169 0 0 0
SQL*Net more data to client 539 0 0 0
SQL*Net Msg from client 168 0 0 0
CPAS_ADMIN on UNIX
SQL*Net message to client 169 0 0 0
SQL*Net more data to client 533 0 33.21
6.23
SQL*Net Msg from client 168 0 37.06
22.06
Thanks for any responses!
Sam.
-----Original Message-----
Sent: January 29, 2002 7:50 PM
To: Multiple recipients of list ORACLE-L
On Tuesday 29 January 2002 15:32, Sam Bootsma wrote:
> Hello,
>
> Most of our clients can run our application with very good performance,
on
> both UNIX and Windows platforms. However, ...
>
* get some network stats for the various tests
* investigate SDU and TDU settings - set metalink note 71920.1
* you will also need to ask the network admin for MDU
here are some scripts to investigate packet sizes being transmitted
through oracle and sqlnet waits:
Jared
------------ sqlnet waits ------------
select
sess.username,
sess.sid,
se.event,
se.total_waits,
se.total_timeouts,
se.time_waited/100 time_waited,
se.average_wait
from v$session_event se, v$session sess
where event like '&uevent%'
and sess.sid = se.sid
and sess.username is not null
order by username, sid;
------------- sqlnet packet sizes per session ----------
-- sqlnet_packet_size.sql
-- show average size of sqlnet packets
set serveroutput on size 1000000
declare
cursor c_client_bytes ( sid_in number )
is
--select decode(sum(value),0,1, sum(value)) value
select decode(value ,0,1,value)
from v$sesstat stat, v$statname name
where
stat.sid = sid_in
and stat.statistic# = name.statistic#
and name.name like '%bytes%client'
order by stat.sid, name.name;
cursor c_client_roundtrips ( sid_in number )
is
select decode(value ,0,1,value)
from v$sesstat stat, v$statname name
where
stat.sid = sid_in
and stat.statistic# = name.statistic#
and name.name like '%%roundtrip%client'
order by stat.sid, name.name;
cursor c_dblink_bytes ( sid_in number )
is
--select sum(value) value
select decode(value ,0,1,value)
from v$sesstat stat, v$statname name
where
stat.sid = sid_in
and stat.statistic# = name.statistic#
and name.name like '%bytes%dblink'
order by stat.sid, name.name;
cursor c_dblink_roundtrips ( sid_in number )
is
--select value
select decode(value ,0,1,value)
from v$sesstat stat, v$statname name
where
stat.sid = sid_in
and stat.statistic# = name.statistic#
and name.name like '%%roundtrip%dblink'
order by stat.sid, name.name;
cursor c_session is
select sid, username
from v$session
order by sid;
client_packet_size number(7,2);
dblink_packet_size number(7,2);
dblink_bytes number;
client_bytes number;
dblink_roundtrips number;
client_roundtrips number;
begin
for sess_rec in c_session
loop
open c_client_bytes(sess_rec.sid);
fetch c_client_bytes into client_bytes;
if c_client_bytes%notfound then
client_bytes := 1;
end if;
close c_client_bytes;
open c_client_roundtrips(sess_rec.sid);
fetch c_client_roundtrips into
client_roundtrips;
if c_client_roundtrips%notfound then
client_roundtrips := 1;
end if;
close c_client_roundtrips;
open c_dblink_bytes(sess_rec.sid);
fetch c_dblink_bytes into dblink_bytes;
if c_dblink_bytes%notfound then
dblink_bytes := 1;
end if;
close c_dblink_bytes;
open c_dblink_roundtrips(sess_rec.sid);
fetch c_dblink_roundtrips into
dblink_roundtrips;
if c_dblink_roundtrips%notfound then
dblink_roundtrips := 1;
end if;
close c_dblink_roundtrips;
--dbms_output.put_line( ' client bytes: '
||
to_char(client_bytes) || '
client roundtrips: ' || to_char(client_roundtrips));
--dbms_output.put_line( ' dblink bytes: '
||
to_char(dblink_bytes) || '
dblink roundtrips: ' || to_char(dblink_roundtrips));
client_packet_size := round( client_bytes
/
client_roundtrips );
dblink_packet_size := round( dblink_bytes
/
dblink_roundtrips );
dbms_output.put_line( 'SID: ' || to_char(
sess_rec.sid ) ||
' User: ' ||
sess_rec.username );
dbms_output.put( ' client
bytes : ' );
if client_bytes > 1 then
dbms_output.put_line(
to_char( client_bytes ));
else
dbms_output.put_line( 'NO
PACKETS');
end if;
dbms_output.put( ' client
round trips : ' );
if client_roundtrips > 1 then
dbms_output.put_line(
to_char( client_roundtrips ));
else
dbms_output.put_line( 'NO
PACKETS');
end if;
dbms_output.put( ' client
avg packet size: ' );
if client_packet_size > 1 then
dbms_output.put_line(
to_char( client_packet_size
));
else
dbms_output.put_line( 'NO
PACKETS');
end if;
dbms_output.put( ' dblink
bytes : ' );
if dblink_bytes > 1 then
dbms_output.put_line(
to_char( dblink_bytes ));
else
dbms_output.put_line( 'NO
PACKETS');
end if;
dbms_output.put( ' dblink
round trips : ' );
if dblink_roundtrips > 1 then
dbms_output.put_line(
to_char( dblink_roundtrips ));
else
dbms_output.put_line( 'NO
PACKETS');
end if;
dbms_output.put( ' dblink
avg packet size: ' );
if dblink_packet_size > 1 then
dbms_output.put_line(
to_char( dblink_packet_size
));
else
dbms_output.put_line( 'NO
PACKETS');
end if;
dbms_output.put_line( chr(10) );
end loop;
end;
/
�
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sam Bootsma
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
