Re: Performance Conundrum Selecting varchar2(2000) Column

From: Jared Still <jkstill_at_cybcon.com>
Date: Tue, 29 Jan 2002 16:59:25 -0800
Message-ID: <F001.003FFCC0.20020129165018@fatcity.com>

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

Received on Tue Jan 29 2002 - 18:59:25 CST