| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Tue Jan 11 2005 - 10:11:36 CST
 |
 |