perf tuning

Hi All,

I have a PL/SQL application. The code looks like:

open a_cur for
select a_column from a_table@a_remote_database;
loop
fetch a_cur into a_variable;
exit when a_cur%notfound;
-- process other stuff locally including inserts, etc...
end loop;

My questions are:
1. Does each fetch grab A row from a_remote_database,
or Oracle fetch ALL the data into local SGA?
2. If each fetch will be through Net, how can I tune
this application?

Thank you!

I could be wrong, but I think the way it works is -

When Oracle Executes the Cursor Statement, it will PARSE (if it is not already) and load "all" the data into the SGA as defined by the cursor - and the "Internal Cursor Pointer" points to the First Row in the Data Set.

The "Fetch" process moves the data into the Cursor Variable, each time the Pointer moves to the next record within the data set. So I think, it doesn't go through the remote database for each fetch, but only once to load the data into the SGA.



PS: It would be nice if someone confirmed my contention.

Hi

well imagine that your remote table contains a million rows..so will the cursor fetch million rows and insert it into the local buffer cache nope..this is not true..it will return the first rows as quickly as possible..if your using sqlplus for the above operation try varying the arraysize parameter and look what happens..there would be a point of diminishing return where you need to stop altering your array size

regards
Hrishy

Quote:

---

Originally posted by hrishy
Hi

well imagine that your remote table contains a million rows..so will the cursor fetch million rows and insert it into the local buffer cache nope..this is not true..it will return the first rows as quickly as possible..
regards
Hrishy

---

Okay. Anyway, the answer the original question is that it doesn't go to the remote server for each fetch.

Hi,

Though Oracle does not need to go the remote database
for each row, as I read from the previous postings,
it may still need multiple rounds to get all of the data.
If this is case, how can I improve the performance
by increasing batch size (it will be best if there
is only one round of Net traffic)?

Thanks!
:confused:

Quote:

---

it may still need multiple rounds to get all of the data.

---

Faster Network , More and More Bandwidth.

Catch your Network admin :)

Regards


Viraj
-----------
OCP 9i

fetch does NOT fetch everything into buffer cache, what if you have 1 billion rows and 100mb SGA?

So fetch just gets a row into data buffer once a time?

No, the unit of operation in buffer cache is a database block, not a row. So a fetch can get zero, one or DB_FILE_MULTIBLOCK_READ_COUNT number of database blocks into buffer cache.

Can we change PL/SQL code to batch requests to a remote
database rathen than accessing the remote database
once per request?

Quote:

---

Can we change PL/SQL code to batch requests to a remote
database rathen than accessing the remote database
once per request?

---

Why dont you change your application in any suitable way u prefer
1. On Periodic basis collect data from source using export import
2. Get Incremental data in files and Use Sql loader for loading into destination.
3. Create MV at Local database and refresh it periodically

Regards


Virajvk
------------------
OCP 9i DBA