| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> FW: Arraysize setting for Client Application<->DB on WAN ?
Hi
We are using ADO and not ODBC. So we don't really configure any data
source.
We just add the database' entry in tnsnames.ora.
Any advice in such a scenario?
Thanks again
-----Original Message-----
From: Igor Neyman [mailto:ineyman_at_perceptron.com]=20
Sent: Tuesday, January 11, 2005 10:02 PM
To: VIVEK_SHARMA=20
Subject: 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-----
Sent: Tuesday, January 11, 2005 11:14 AM
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.=3D20
Qs. How can "arraysize" parameter be set at the Application/Oracle Client/DB level for ALL SQL queries intiated by the Application?
DATA:-
=3D3D=3D3D=3D3D=3D3D
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
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:
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)
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-lReceived on Tue Jan 11 2005 - 23:20:53 CST
 |
 |