session_Cached_cursors

[satish]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

You asked ;

Will syntactic and symantic checks takes place with session_Cached_cursors?Will oracle takes latches with session_Cached_cursors?

And also pls let us know whether oracle uses pga OR sga for session_cached_cursors


My answer is yes.
It requires latching, but it will ease the soft parse.
In your pga, a pointer will be stored. That pointer will point to the shared pool where the cursor resides.
So in the next parse attempt, the pointer will be used and your session's work will be easier. (in terms of parsing)

[satish]

Dear Erman,

Thanks for the update.

regarding your statement "It requires latching, but it will ease the soft parse." which means still parsing happens but it will ease the soft parse?
will this get recorded as parse in tkprof?

Thanks,
satish

[ErmanArslansOracleBlog]

Yes. The parse count should be there.
session_cached_cursors eases parsing by by avoiding syntax and semantics checks.
However; parse count will be there for every execution.
As I said, those are pointer, so validity of the curors in the library cache (inside shared pool) should be checked every time.

However; if you use PLSQL block to execute the sql while session_cached_cursors is enabled, then things change. Because, PL/SQL can cache cursor handles in PL/SQL cache and hence reduce parsing.
So session_cached_cursors eases parsing in general, but reduces parsing only when the same SQL statements are submitted using PL/SQL.

here is a good article for you. I recommend you to review it ->

https://blog.toadworld.com/2017/05/11/session-cursor-caching-part-two

[Sri]

Thank you. All clear