Re: shared pool evictions
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Tue, 3 Mar 2020 23:27:58 -0500
Message-ID: <CAMHX9JJiAdHF9Dqc1A5-v1Z8wOaam2S8eyCo7fDei_C+O=hdUw_at_mail.gmail.com>
Date: Tue, 3 Mar 2020 23:27:58 -0500
Message-ID: <CAMHX9JJiAdHF9Dqc1A5-v1Z8wOaam2S8eyCo7fDei_C+O=hdUw_at_mail.gmail.com>
Btw these are different things:
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_at_vontobel.com> wrote:Received on Wed Mar 04 2020 - 05:27:58 CET
> Ø 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_at_tanelpoder.com>
> *Sent:* Dienstag, 3. März 2020 22:24
> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>
> *Cc:* jonathan_at_jlcomp.demon.co.uk; ORACLE-L (oracle-l_at_freelists.org) <
> oracle-l_at_freelists.org>
> *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> _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
>
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l