Btw these are different things:
1) Shared pool chunk position in the LRU list (transient vs recurrent) -
moved around after 2nd exec (after 3rd unpin)
2) Whether a cursor is cached in session cursor cache - cursor kept open
after 3rd exec
Yes, agreed, Oracle-L is a good place to hang out at! :-)
--
Tanel Poder
https://tanelpoder.com/conference
On Tue, Mar 3, 2020 at 6:55 PM Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
wrote:
Ø How many times did you execute that child cursor?
Ah, that’s the point. Only after the 3rd execution the cursor gets cached
by the session. After that it doesn’t get evicted from the shared pool that
easily. Consequently, I’ll try to extend the life expectancy in the shared
pool by increasing session_cached_cursors. On a low-activity test database
the SQL doesn’t get evicted for the long time even when the cursor isn’t
cached by a session.
By the way, this list is attended by many smart people, and it trumps any
payed support by far. Whenever I ask a (at least, for me) non-trivial
question not only do I quickly get a solution, but I also learn a bunch of
new stuff. I’m so grateful for all the advice I’ve received so far.
Best regards,
Nenad
https://nenadnoveljic.com/blog/
*From:* Tanel Poder <tanel@xxxxxxxxxxxxxx>
*Sent:* Dienstag, 3. März 2020 22:24
*To:* Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
*Cc:* jonathan@xxxxxxxxxxxxxxxxxx; ORACLE-L (oracle-l@xxxxxxxxxxxxx) <
oracle-l@xxxxxxxxxxxxx>
*Subject:* Re: shared pool evictions
How many times did you execute that child cursor?
If you execute it only once (pinned only twice), the cursor's recreatable
chunk will stay in the transient part of the LRU list. If you execute it
more times, but a new child gets created after the last exec, then the new
child would have been executed only once and can age out quicker than
previous multi-executed cursors.
I don't know of any X$ or dumps that would tell you whether a chunk is in
transient or recurrent part of the LRU list, but you can see the current
totals from X$KGHLU:
SQL> @kghlu
SUB SSUB FLUSHED LRU LIST *RECURRENT* *TRANSIENT*
FREE UNPIN LAST FRUNP RESERVED RESERVED RESERVED RESERVED
POOL POOL CHUNKS OPERATIONS *CHUNKS* *CHUNKS*
UNSUCCESS UNSUCC SIZE SCANS MISSES MISS SIZE MISS MAX SZ
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------- ---------- ---------- ---------- -----------
1 0 4966364 15091924 14034 14898
0 0 8694 0 0 0
