| 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
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 <SamB_at_cpas.com>
Sent by: root_at_fatcity.com
01/30/02 02:25 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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
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: '
||
--dbms_output.put_line( ' dblink bytes: '
||
client_packet_size := round( client_bytes
/
dblink_packet_size := round( dblink_bytes
/
dbms_output.put_line( 'SID: ' || to_char(
sess_rec.sid ) ||
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.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 - 17:24:04 CST
 |
 |