Re: Cursor cache hit ratio over 100%

That's correct.

I've just run a couple of modified versions of Martin's code on 19.11 and the effect is the same.
Cursors in the PL/SQL cursor cache can report under "execute count", and "session cursor cache hits" without being reported under any "parse count XXX". This may be because there is no parse call, but it may be that the code path that issues the parse call in PL/SQL doesn't increment the parse calls count.

You MIGHT get a clue that "session cursor cache hits" is "over-reporting" by looking at "opened cursors cumulative" and "recursive calls" to see if they are similar and might (approximately) account for an "appropriate" (whatever that means) number of session cursor cache hits; but that might be dependent on the client tools and the code strategy used (e.g. execute immediate vs. embedded SQL vs. open/fetch/close vs. "hold_cursor" option)

Regards
Jonathan Lewis

On Tue, 6 Jun 2023 at 07:16, Nenad Noveljic <nenad.noveljic_at_gmail.com> wrote:

> If I understood correctly, there's no way to calculate the percentage of
> parse calls served from session cursor cache, because the session cursor
> cache hits don't always increment the total parse call statistic.
>
> Best regards,
> Nenad
>
> On Mon, Jun 5, 2023 at 9:48 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> A comment by Martin Maletinsky on an old blog note of mine MIGHT be the
>> answer (or part of the answer) to this question.
>> I haven't tried repeating his tests on a recent version of Oracle,
>> though:
>> https://jonathanlewis.wordpress.com/2007/07/03/parse-calls/#comment-39792
>>
>> Regards
>> Jonathan Lewis
>>
>>>
>>>

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