DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: perf tuning

  1. #1
    Join Date
    Jun 2000
    Posts
    295

    Question 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!

  2. #2
    Join Date
    Mar 2002
    Posts
    200

    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.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    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

  4. #4
    Join Date
    Mar 2002
    Posts
    200
    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.

  5. #5
    Join Date
    Jun 2000
    Posts
    295
    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!

  6. #6
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    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 !!!

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    fetch does NOT fetch everything into buffer cache, what if you have 1 billion rows and 100mb SGA?

  8. #8
    Join Date
    Jun 2000
    Posts
    295
    So fetch just gets a row into data buffer once a time?

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  10. #10
    Join Date
    Jun 2000
    Posts
    295
    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
  •  


Click Here to Expand Forum to Full Width