Re: SQL*Net message to|from client

From: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Thu, 10 Aug 2006 08:49:01 -0700 (PDT)
Message-ID: <20060810154901.77369.qmail@web51306.mail.yahoo.com>

THanks Niall

Niall Litchfield <niall.litchfield_at_gmail.com> wrote:

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.

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

 		
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates starting at 1&cent;/min.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 10 2006 - 10:49:01 CDT