Skip to Main Content
  • Questions

  • Avoiding both a soft and hard parse through use of "SESSION_CACHED_CURSORS"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 03, 2016 - 7:28 pm UTC

Last updated: April 04, 2016 - 3:28 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Good Sunday Afternoon,

I was reviewing some old notes regarding avoiding both a soft and hard parse by keeping the cursor open or for applications that close them, using SESSION_CACHED_SESSION. Is it considered a band-aid to use SESSION_CACHED_SESSION? Is the best solution for the application to keep to only close the cursors when the application shutdown that way other schema session with the same schema name can re-use the cursors? BTW: this goes against the advise given to me 15 years ago stating that cursors should be closed at the end of the calls. Can two sessions that use the same schema share cursors with each other?

Also, in one of Tom Kite's presentation that I attended at Hotsos, he did say that setting SESSION_CACHED_SESSION too high could cause error ORA-04031. First, what determines the size of the cursor? Also, now with ASSM and AMM, I wouldn't think that ORA-04031 would occur much.

Please explain so that I can do the right thing in order to avoid both soft and hard parses.

thanks,

John

and Connor said...

This is probably the best AskTom thread on it

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865497961356

Its a little out of date. Where is referenced "open_cursors" for PLSQL, thats been changed, but the principle remains unchanged, namely:


worst case - hard parse everything, every SQL a new one

better - we do a parse call on everything, but because (for example) we are using bind variables, many of those parses become a soft parse

better again - as per above, but we set session_cached_cursor, so that those soft parses become "softer" (ie, they cost less). The price for that is we're holding onto more stuff in memory (hence the *potential* risk for ora-4031, but I would say unlikely).

best - parse once (hard parse) and then never parse again (not even soft) by holding cursors open as required. Sounds tough to do (and it is), but if you're using PL/SQL for all of your SQL... we do all of the heavy lifting for you. Otherwise, it becomes your job to manage.


So if you can get to "best" (ie, use PLSQL) then you're pretty much optimal. If you can't, then aim for "better again".

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you!

A reader, April 05, 2016 - 2:42 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.