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
