Search My Oracle Blog

Custom Search

27 July, 2007

DBAs can "steal" data ?!

News that makes me uncomfortable with headlines that go "Database Administrator ... stole data ..."

19 July, 2007

Using ARRAYSIZE to reduce RoundTrips and number of FETCH calls

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 ?

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com