Re: open and closed cursors

From: Orlando L <oralrnr_at_gmail.com>
Date: Thu, 14 Feb 2013 18:00:14 -0600
Message-ID: <CAL8Ae76WpP6x0amhj_6qoHBAKKoY95NLMV_B80qZm0m4JH+2-g_at_mail.gmail.com>

Thank you Hans.
I did some searching, and I found this on asktom site:

"softer soft parse is achieved using session_cached_cursors - there the application actually does close the cursor and we have to reopen it - but - if they close it say three times, we finally catch on and say "oh, you didn't really mean to close that" and when we close it - we keep a pointer to it in the shared pool so that when you say open the next time, we check the shared pool (skipping many of the steps like syntax parse, semantic check) to see if it is still there and if so, just execute it."

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1089433252941#2640730800346712409

Based on this, we could say that the PGA cursor area for the user keeps a pointer to the shared SQL in the shared pool if the cursor is kept 'open'.If the cursor is 'closed', oracle probably has to hash the sql again, and then find the location of the shared sql in shared pool.

Someone can validate this.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 15 2013 - 01:00:14 CET