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).
