Oracle Cursors Sharing

Sys-recursive SQL statements are generated by oracle to query the data dictionary (system catalog) to find out information on objects and relations to interpret SQL statement.  Oracle keep some bootstrap objects in the shared pool (marked as fixed object) to allow it to start query processing.

When a SQL is passed to Oracle, it will hash the SQL and check if there is an entry in the library cache with the same hash value.  If there is a match, Oracle will then compare the SQL to the lib cached one to make sure they are indeed the same.  This is called cursor authentication.

Session cursor caching is to keep the frequently used cursors in session memory so that you do not need to search for it in the library cache.  Session cursor caching happened after the cursor authentication.

call 1 - oracle optimize the SQL
call 2 - cursor authentication and pick it up from library cache
call 3 - cache the cursor in session memory after call completed
call 4 - reuse the cursor in session memory

If someone is using a particular query, it will already be optimized in the library cache.  When you use the same query, you go straight to call 2 scenario and the cursor will cache in your session memory after call 2.