| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Arraysize setting for Client Application<->DB on WAN ?
Since it's VB, you must be using ODBC (on top of SQL*Net).
When configuring ODBC Data Source, there is a parameter called "Fetch
Buffer Size", check it's value and increase if needed.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of VIVEK_SHARMA
Sent: Tuesday, January 11, 2005 11:14 AM
To: oracle-l_at_freelists.org
Subject: Arraysize setting for Client Application<->DB on WAN ?
OBSERVATION:-
With "set arraysize 1000" , Firing an SQL Query Directly from Oracle
Client SQL prompt over WAN onto DB Server takes 44 seconds.
With "set arraysize 1" , Firing same SQL Query Directly from Oracle Client SQL prompt over WAN onto DB Server takes 5 minutes.
ISSUE - Our Application is a thick VB client which has all the business logic is installed on a Win2k/XP Desktop PC. This Application connects to the database server via SQL*Net i.e. Oracle client. This Application Desktop & Database server(HP UX 11i Box) communicate over WAN.=20
Qs. How can "arraysize" parameter be set at the Application/Oracle Client/DB level for ALL SQL queries intiated by the Application?
DATA:-
=3D=3D=3D=3D
Oracle 9.2
Using Oracle Client from windows Desktop (over WAN)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=
=3D
=20
Elapsed time for SQL Query : 00:00:44.00
"autotrace on" Statistics:-
0 recursive calls
0 db block gets
23 consistent gets
20 physical reads
0 redo size
50826 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
758 rows processed
with arraysize 1:
Statistics
0 recursive calls
0 db block gets
401 consistent gets
0 physical reads
0 redo size
66574 bytes sent via SQL*Net to client
2887 bytes received via SQL*Net from client
380 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
758 rows processed
>From UNIX over LAN - Over LAN arraysize change makes little difference
in Elapsed time (shown below)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
With arraysize 1000:
Elapsed time for SQL Query: 00:00:22.20
Statistics
0 recursive calls
0 db block gets
23 consistent gets
20 physical reads
0 redo size
52087 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
758 rows processed
with arraysize 1:
Elapsed time for SQL Query: 00:00:23.94
Statistics
0 recursive calls
0 db block gets
401 consistent gets
21 physical reads
0 redo size
120505 bytes sent via SQL*Net to client
4625 bytes received via SQL*Net from client
380 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
758 rows processed
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 11 2005 - 10:34:08 CST
 |
 |