Re: SQL*Net message to|from client

Sanjay

Assuming you are using an Oracle supplied ODBC driver then the fetch size can be, to some extent, controlled by setting the Fetch Buffer Size on the Oracle tab of the ODBC configuration. Quoting from the help

*Fetch Buffer Size* - the amount of memory used to determine how many rows of data the ODBC Driver will pre-fetch at a time from an Oracle database regardless of the number of rows the application program requests in a single query. However, the number of pre-fetched rows depends on the width and number of columns specified in a single query. Applications that typically fetch fewer than 20 rows of data at a time will see an improvement in response time, particularly over slow network connections or to heavily loaded servers. Setting this too high can actually make response time worse or consume large amounts of memory.

regards

On 8/10/06, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
>
> Steve
> Thanks for the update. THe current siebel server installation is on
> Windows and so I think it is using odbc. DB is on Unix. The strange part is
> that I am trying to insert somestuff on Front end which will insert into
> several tables and as soon as I press Save, I can see the data from Sql plus
> in all tables but the control on Front end shows Hour glass for few minutes
> before showing as completed. Even If I logon to other Siebel Window , I can
> see the Data
>
> Trace looks like
> First Insert
> EXEC #1:c=0,e=4905,p=0,cr=0,cu=17,mis=0,r=1,dep=0,og=2,tim=2314469165990
> WAIT #1: nam='SQL*Net message to client' ela= 11 p1=1413697536 p2=1 p3=0
> WAIT #1: nam='SQL*Net message from client' ela= 1340 p1=1413697536 p2=1
> p3=0
> EXEC
> #5:c=10000,e=6491,p=0,cr=0,cu=5,mis=0,r=1,dep=0,og=2,tim=2314469175259
> WAIT #5: nam='SQL*Net message to client' ela= 7 p1=1413697536 p2=1 p3=0
> WAIT #5: nam='SQL*Net message from client' ela= 794 p1=1413697536 p2=1
> p3=0
> XCTEND rlbk=0, rd_only=0
> WAIT #0: nam='log file sync' ela= 1517 p1=40 p2=0 p3=0
> WAIT #0: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
> WAIT #0: nam='SQL*Net message from client' ela= 11548 p1=1413697536 p2=1
> p3=0
> STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=3 pr=0 pw=0 time=2850
> us)'
> STAT #7 id=2 cnt=1 pid=1 pos=1 obj=59141 op='TABLE ACCESS BY INDEX ROWID
> S_EVT_ACT (cr=3 pr=0 pw=0 time=616 us)'
> STAT #7 id=3 cnt=1 pid=2 pos=1 obj=59148 op='INDEX SKIP SCAN S_EVT_ACT_F14
> (cr=1 pr=0 pw=0 time=467 us)'
>
>
>
>
>
> *Steve Perry <sperry_at_sprynet.com>* wrote:
>
> Julian Dyke has a presentation logicalio.ppt.
> there's a slide that shows the different ways to set the prefetch
> that Christian mentions.
>
> Row Prefetching
>
> Applies to
> OCI OCI_ATTR_PREFETCH_ROWS
> Pro*C Host Array
> JDBC setRowPrefetch ()
> PL/SQL BULK COLLECT
> SQL*Plus SET ARRAYSIZE
>
> OCI default prefetch value is 1 (returns 2 rows per fetch)
>
>
>
> On Aug 9, 2006, at 04:53 PM, Christian Antognini wrote:
>
> > Hi
> >
> >> I am working on Siebel application and have found few stuff are
> > working
> >> slow and doing detailed traces with Waits shows that only wait events
> > are
> >> SQL*Net message to client'
> >> SQL*Net message from client'
> >
> > You have only "SQL*Net message to/from client" probably because the
> > needed data is already in the buffer cache.
> >
> > The application is fetching only two rows at time (strangely enough
> > the
> > first time 3...). Since the fetches are performed without any possible
> > user's "think time", you should increase the number of rows which are
> > fetched for each database call. I.e. you should increase the fetch
> > size.
> >
> > HTH
> > Chris
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>
>
> ------------------------------
> Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates
> starting at 1¢/min.
> <http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39666/*http://messenger.yahoo.com>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l