So what does the Oracle database do when a SQL statement comes in to be processed?
A SQL is HARD PARSED the first time it comes into the database:
Execution Plan in a format the SQL engine can use
A SOFT PARSE is where the last 2 steps are skipped as the SQL is found in memory.
• Soft parses, less expensive than hard parses, nevertheless incur a cost, that of needing the use of Shared Pool and Library Cache latches (these are serial
operations), that can lead to performance issues in OLTP systems
• To minimize the above impact session cursors of reused (used > 3 times) SQL can be stored in the Session Cursor Cache (UGA/PGA)
• What is actually stored is a pointer to the location in the Library Cache of where the cursor existed when it was closed
• The presence of a cursor in the Session Cursor Cache guarantees the validity of the SQL’s syntax and semantics so the first 2 steps of Parsing are skipped
• Instead of searching for the cursor in the Library Cache the Server process follows the pointer and uses the cursor (if present and still valid)
• One can use the above feature by setting a value to session_cached_cursors (BMC recommends a starting value of 100)