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

Thread: cursor

  1. #1
    Join Date
    Aug 2001
    Posts
    36
    Dears,

    While reading metalink i have gone through following lines.

    "By far the best approach to use in OLTP type applications is to parse a statement only once and hold the cursor open, executing it as required. This results in only the initial parse for each statement (either soft or hard). Obviously there will be some statements which are rarely executed and so maintaining an open cursor for them is a wasteful overhead. "

    What is a Cursor here?
    Can anybody elaborate more on cursors?
    what is the significance of cursor during parsing?

    Regards,

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    the pointer in PGA that points to the shared SQL in the shared pool

  3. #3
    Join Date
    Aug 2001
    Posts
    36
    Dear Pando,

    Thanks a lot for your reply. I have some more questions.

    According to you pointer in PGA that points to shared SQL. That means for each user trying to execute same SQL will have seperate cursor opened(As each user will be having seperate PGA's). Then what is the meaning of cursor sharing. Can you elaborate more on that.

    Regards,



  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well cursor is per session like you said, cursor sharing is really SQL statement sharing.... but it seems like Oracle like to use shared cursors and shared SQL like synonyms hehe

  5. #5
    Join Date
    Aug 2001
    Posts
    36
    Dear Pando,

    Thanks and I agree with you. I have one last question.

    When you execute a select statement values or result set will be captured in a memory and a pointer(cursor) will be used to access it right..

    Will this cursor is same as the cursor(cursor sharing) mentioned?

    The memory used to hold the result set will be part of PGA or Shared pool?

    Regards,


  6. #6
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    As the initial post metioned "hold the cursor open", how could that opened cursor be reused by the same session or another session?
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    session_cached_cursors would cache cursors locally avoiding super-soft parse

    if I remember right (I think I read it from some forum on Metalink) that when cursors are closed they are *not* really closed, they can still be referenced. On metalink search for v$open_cursor and you could see some topic which says v$sysstat and v$sesstat statistic not match or something like that

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by sursujmad
    Dear Pando,

    Thanks and I agree with you. I have one last question.

    When you execute a select statement values or result set will be captured in a memory and a pointer(cursor) will be used to access it right..

    Will this cursor is same as the cursor(cursor sharing) mentioned?

    The memory used to hold the result set will be part of PGA or Shared pool?

    Regards,


    hmm I always thought result set are in data cache

    But then again I can see you are confused with what I never understood or confused, you are talking about Pl/SQL cursors and Cursors in PGA. I never understood how a pointer points to a row in data cache so I cant answer that. Now I just assume Pl/SQL cursor and cursors in v$open_cursor has nothing to do with each other, I may be wrong but at least I dont confuse myself even more hehe

  9. #9
    Join Date
    Aug 2001
    Posts
    36
    Dear Pando,

    What is the answer to my questions?

    Regards,


  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    eh?

    I dont want to cut and paste from my previous post

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