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
That's why when I do live demos that require the cursor to be around for a
bit longer, I run my demo queries 2-3 times (executions 2 should be enough
though).
Also, if your shared pool is split into multiple subpools, what happens to
get aged out depends on whatever subpool some other shared pool allocator
defaults to using.
--
Tanel Poder
https://tanelpoder.com/conference
On Tue, Mar 3, 2020 at 12:10 PM Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
wrote:
The following SQL aged out within a minute when executed from my client (I
added a hint to generate a new cursor):
SELECT /*nenad */ INDEX_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE
TABLE_NAME = '...' AND INDEX_NAME IS NOT NULL
However, the query has remained longer in the shared pool when executed by
the application.
This means that the relevant difference is probably neither in the query
itself nor in the data model, but it's hidden elsewhere. I'll keep looking.
Best regards,
Nenad
https://nenadnoveljic.com/blog/
