ARRAYSIZE for queries across DBLinks

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Thu, 19 Jul 2007 23:39:02 +0800
Message-Id: <200707191539.l6JFdBdp002642@smtp41.singnet.com.sg>

I have been able to use ARRAYSIZE in SQLPlus to fetch more rows at each call and reduce the number of round-trips between the SQLPlus Client and the database. [see example below]

However, this does not work when fetching rows from one database to another via a Database Link. (SET ARRAYSIZE is an SQL*Plus command, not an Oracle Database / SQL command).

If I were to fetch a few tens of thousands or hundreds of thousands of rows across a DBLink -- eg for a Materialized View refresh -- how can I tune the size of each fetch and reduce the number of round-trips between the two databases ?

For Example : In SQLPlus I run the query

---

select *
from
  test_txn_table

call     count       cpu    elapsed       disk      query    current 
       rows

------- ------ -------- ---------- ---------- ----------
---------- ----------

Parse        1      0.01       0.03        368         85          0 
          0
Execute      1      0.00       0.00          0          0          0 
          0
Fetch    10284      0.82       4.42       1853      12300          0 
     154239

------- ------ -------- ---------- ---------- ----------
---------- ----------

total    10286      0.84       4.45       2221      12385          0 
     154239


Rows     Row Source Operation
-------  ---------------------------------------------------

  154239 TABLE ACCESS FULL TEST_TXN_TABLE (cr=12300 pr=1853 pw=0 time=797235 us)

Elapsed times include waiting on following events:

   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   SQL*Net message to client                   10284        0.00          0.02
   db file scattered read                        143        0.48          3.29
   SQL*Net message from client                 10284        0.35        151.98
   db file sequential read                        15        0.08          0.20

I see 10,284 round-trips between the client SQLPlus and the server Database. (and the corresponding count of FETCH calls)

---

---

If I re-run it with ARRAYSIZE 100 and PAGESIZE 600 (Pagesize to reduce the overhead that SQLPlus spends in formatting page and column titles every 16 lines ) I get

select *
from
  test_txn_table

call     count       cpu    elapsed       disk      query    current 
       rows

------- ------ -------- ---------- ---------- ----------
---------- ----------

Parse        1      0.00       0.33        348        186          0 
          0
Execute      1      0.00       0.00          0          0          0 
          0
Fetch     1544      0.67       2.81       1851       3680          0 
     154239

------- ------ -------- ---------- ---------- ----------
---------- ----------

total     1546      0.67       3.14       2199       3866          0 
     154239



Rows     Row Source Operation
-------  ---------------------------------------------------

  154239 TABLE ACCESS FULL TEST_TXN_TABLE (cr=3680 pr=1851 pw=0 time=466682 us)

Elapsed times include waiting on following events:

   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   SQL*Net message to client                    1544        0.00          0.00
   db file scattered read                        140        0.08          2.12
   SQL*Net message from client                  1544        0.68         55.60
   SQL*Net more data to client                  3976        0.00          0.08
   db file sequential read                         9        0.05          0.09

I now have only 1,544 round trips

---

How can I get similar savings when the query is to fetch data from one database to another via a DBLink ?

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

"First they ignore you, then they laugh at you, then they fight you, then you win" !"
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 19 2007 - 10:39:02 CDT