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_tablecall 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_tablecall 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
-- //www.freelists.org/webpage/oracle-l