Re: To increase a packet size for local connection (SDU,TDU,IPC,BEQ)

Hello Stano,

From the trace file bit you sended, there might be a lot of waits, but they seem all to be performed in the same 1/100th of a second (tim=1229996800). I think that you should investigate, for the full month-end batch job, the aggregated wait events, their times and counts. IPC communication is usually pretty fast, times faster than tcp/ip. Therefore, I guess, enlarging the packetsize will not help much.

Usually, improving a few bad-performing sql-statements might shorten the duration times significantly.

Stano wrote:

>Hi there,
>
>a month-end batch process reads a lot of data from a table and
>the data is subsequently being processed. It runs on the same server
>where the database resides (DEC 8400). The trace reveals a lot waits
>for 'SQL*Net more data to client':
>
>EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
>WAIT #1: nam='db file sequential read' ela= 0 p1=31 p2=31179 p3=1
>WAIT #1: nam='db file sequential read' ela= 0 p1=51 p2=281868 p3=1
>WAIT #1: nam='db file sequential read' ela= 0 p1=35 p2=230590 p3=1
>WAIT #1: nam='db file sequential read' ela= 0 p1=40 p2=281 p3=1
>WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1229996800 p2=1 p3=0
>FETCH #1:c=0,e=0,p=4,cr=37,cu=0,mis=0,r=1,dep=0,og=4,tim=0
>WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1229996800 p2=1 p3=0
>WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1229996800 p2=1 p3=0
>WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1229996800 p2=2014 p3=0
>WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1229996800 p2=2017 p3=0
>.... plenty of WAITS ...
>WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1229996800 p2=2016 p3=0
>WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1229996800 p2=2010 p3=0
>FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=229,dep=0,og=4,tim=0
>WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1229996800 p2=1 p3=0
>WAIT #1: nam='SQL*Net more data from client' ela= 0 p1=1229996800 p2=6 p3=0
>
>Each data "chunk" (packet may not be 100% correct) is about 2K (see p2=...),
>a default size of SQL*Net. Is there a way to increase this size (just for local
>connection)?
>
>I have tried 2(3) different methods to connect:
>
>1) BEQ - from tnsnames.ora:
>TST6_BEQ =
> (DESCRIPTION =
> (SDU=8192)
> (TDU=8192)
> (ADDRESS =
> (PROTOCOL = BEQ)
> (PROGRAM = /oracle1/app/oracle/product/8.1.6/bin/oracle)
> (ARGV0 = oracleTST6_BEQ)
> (ARGS = '(DESCRIPTION=(LOCAL=TESTING)(ADDRESS=(PROTOCOL=BEQ)))')
> (ENVS = 'ORACLE_HOME=/oracle1/app/oracle/product/8.1.6,ORACLE_SID=TST6')
> )
> )
>
> with no sucsess - still only 2K data packets used.
>
>2) IPC - listener.ora and tnsnames.ora changed, tnsnames.ora:
>
> TST6_IPC=(DESCRIPTION=
> (SDU=8192)
> (TDU=8192)
> (ADDRESS=(PROTOCOL=IPC)(KEY=TST6))
> (CONNECT_DATA=(SID=TST6))
> )
>
> with no success either (2K data packets).
>
>3) I also tried using TCP connection but it still connects using BEQ protocol.
> Moreover I am not inclined to use TCP since the batch process runs on
> the same server as the database.
>
>I have searched Metalink, but the opinions differ, some suggest that
>it actually might be possible to increase the packet size for a local
>connections, though Oracle says NO.
>
>Has anyone successfully tried it or is Oracle right (it's not possible)?
>
>Thanks for any reply,
>
>Stano Vnuk.
Received on Tue Jun 25 2002 - 06:23:38 CDT