| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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.0622.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, ...
>
here are some scripts to investigate packet sizes being transmitted through oracle and sqlnet waits:
Jared
select
sess.username,
sess.sid,
se.event, se.total_waits, se.total_timeouts, se.time_waited/100 time_waited, se.average_wait
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) || '
--dbms_output.put_line( ' dblink bytes: ' ||
to_char(dblink_bytes) || '
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: ' ||
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;
Ó
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: jkstill_at_cybcon.com 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: ListGuru_at_fatcity.com (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: SamB_at_cpas.com 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: ListGuru_at_fatcity.com (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).Received on Wed Jan 30 2002 - 16:33:26 CST
 |
 |