-
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!
-
Re: perf tuning
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.
Last edited by quester; 02-06-2003 at 01:02 AM.
-
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
-
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!
-
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
A Wise Man Knows How much he doesn't know !!!
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Can we change PL/SQL code to batch requests to a remote
database rathen than accessing the remote database
once per request?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|