FW: Arraysize setting for Client Application<->DB on WAN ?

  • From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
  • To: <ineyman@xxxxxxxxxxxxxx>
  • Date: Wed, 12 Jan 2005 10:45:01 +0530

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@xxxxxxxxxxxxxx]=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@xxxxxxxxxxxxxx

-----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:
----------------
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)

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

Other related posts: