DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Ref( 2 Million Records Cursor)

  1. #1
    Join Date
    Jul 2002
    Posts
    29

    Ref( 2 Million Records Cursor)

    Hi,

    I have a cursor in pl/sql block which opens 2 million record at a time.
    The size of the data could be 200MB. I would like to know where this data will be stored until the pl/sql block successfully completed.

    I believe PGA, is it right or temp tablespace. The cursor query has order by clause.

    Thanks,

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    It would be stored in the SGA database buffer cache offcourse..but not your 2 million rows..the answer would deepnd on the array size of the calling program ..whaether it oci ,sqlplus,java or etc..and for sorting the sort would be split into sort runs..

    regards
    Hrishy

  3. #3
    Join Date
    Jul 2002
    Posts
    29
    Hi,

    It is a sql file which has the pl/sql block on the databse server.
    It is being called by the oracle apps. I believe sql file is being executed in one of the session made by the oracle apps with database.

    So how do I know the array size of the session made by the oracle apps with DB.

    What is the default session array_size?.


    Thanks,

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    The array size is dependent upon the tool..i am not aware of oracle apps..but for

    sqlplus the default is 15
    JDBC the defualt is 10
    Pro*c the default is 2
    OCI it is 1

    you know what even if your server memory is unlimited and you have an unlimited sga and assuming oracle stored the 2 million rows for you in the SGA then..you need to have all clients with unlimited memory tooo..:-D which i guess is impossible :-D

    if you are sorting these 2 Million record then oracle will materialize this sort in the temp tablespace..you make sure you have a large temporary tablespace..

    regards
    Hrishy

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