Re: SQL*Net message to|from client

  • From: Sanjay Mishra <smishra_97@xxxxxxxxx>
  • To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • Date: Thu, 10 Aug 2006 08:49:01 -0700 (PDT)

THanks Niall
  

Niall Litchfield <niall.litchfield@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxx> 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
> --
> //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.

Other related posts: