Re: shared pool evictions
Date: Tue, 3 Mar 2020 16:23:36 -0500
Message-ID: <CAMHX9J+mJy5F6XPYdRu2zfjTKyneUdwfVcdmd09FFKTrHH7fjw_at_mail.gmail.com>
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> _at_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_at_vontobel.com> wrote:Received on Tue Mar 03 2020 - 22:23:36 CET
> 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/
>
>
-- http://www.freelists.org/webpage/oracle-l