Relationships between library cache objects must be maintained not only for execution purposes, but also if a change occurs in one of the components. Suppose one table is referenced in 2,000 SQL statements, in 100 functions, and 20 packages. Now suppose one of the table's columns is renamed. Oracle will then invalidate all related SQL statement and programmatic constructs. This can result in a cascade effect that requires latching and locking. The combination of multiple involved sessions, invalidations, recompilations, and timing has resulted in entire Oracle instances locking up. Obviously, Oracle knows about this serious situation and is actively decreasing the likelihood of it occurring. But every DBA needs to know library cache relationships are very complex and can cause problems at times.

Cursors are created when searched for and not found in the library cache. This results in what is called a hard parse. As will become obvious, this is a relatively expensive operation that requires memory management (allocation and possibly deallocation), latching to ensure serialization, locking to prevent inappropriate changes, CPU consumption to run the kernel code, and possibly IO to bring data dictionary information into the row cache.

Cursors are built from data in the shared pool. And if the data is not currently in the shared pool, Oracle creates its own SQL to retrieve the data from the data dictionary tables. Recursive SQL is the name given to the SQL Oracle dynamically creates and runs itself. Some of the data Oracle needs to create a cursor is optimizer statistics, session information, security information, object information, and object relationship information. Books could be written simply about cursor creation.