Cursor Usage and Parsing

Cursor Usage and Parsing

Every developer wants his or her code to run as fast as possible. For code with SQL statements, that means cursor access must be fast. The fastest possible access of a cursor is through the open cursor cache in the session memory of the server session. Every open cursor in the open cursor cache has a pointer to the SGA memory location of that cursor handle. To execute the cursor, the pointer is used; parsing is not required. An open cursor has already been parsed and the cursor handle is in the library cache.

When a cursor is closed, the cursor information is moved into the session’s closed cursor cache, if the SESSION_CACHED_CURSORS parameter has been set to some value. (The default is 0 before version 10.2.0.2, when it changes to 50.)

When a cursor is opened, the session hashes the SQL statement and performs a hash lookup in the closed cursor cache. If the cursor is found, it is moved to the open cursor cache, then the pointer to the cursor handle in the shared pool is used to execute the cursor. No parse required.

If the cursor is not found in the session, then the hash value is used to search the hash chains in the shared pool for the cursor handle. The search is registered as a parse. If the cursor handle is found and the rest of the cursor has not aged out, the cursor is executed. This is a soft parse.

If some part of the cursor has aged out of the shared pool, or the cursor does not exist in the shared pool, then the cursor is constructed. This is a hard parse. The cursor construction requires lookups of the metadata for dependent objects such as tables, indexes, extents, and sequences. If the metadata for these objects is not already cached in the shared pool, then recursive SQL is generated to fetch the information from the data dictionary.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23590362/viewspace-1036181/，如需转载，请注明出处，否则将追究法律责任。