RE: Arraysize setting for Client Application<->DB on WAN ?
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <VIVEK_SHARMA@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Jan 2005 11:31:50 -0500
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@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of VIVEK_SHARMA
Sent: Tuesday, January 11, 2005 11:14 AM
To: oracle-l@xxxxxxxxxxxxx
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
with arraysize 1000:
-------------------
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:
----------------
Elapsed time for SQL Query: 00:05:00.00
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
