Allocationand Reuse of Memory in the Shared Pool

Allocationand Reuse of Memory in the Shared Pool(ZYD)

The database allocates shared pool memory when a new SQL statement is parsed. The memory size depends on the complexity of the statement.

In general, an item in the shared pool stays until it is removedaccording to an LRU algorithm. Thedatabase allows shared pool items used by many sessions to remain in memory as longas they are useful, even if the process that created the item terminates. Thismechanism minimizes the overhead and processing of SQL statements.

If space is needed fornew items, then the database frees memory for infrequently used items. A shared SQL area can be removed from the shared pooleven if the shared SQL area corresponds to an open cursor that has not beenused for some time. If the open cursor issubsequently used to run its statement, then Oracle Database reparses thestatement and allocates a new shared SQL area.(Library Cache 太小)

The database also removes a shared SQL area from the shared pool in the followingcircumstances(ZYD)

·        If the databasegathers statistics for a table, table cluster, orindex, then by default the database gradually removes all shared SQL areas thatcontain statements referencing the analyzed object after a period.The next time a removed statement is run, the database parses it ina new shared SQL area to reflect the new statistics for the schema object.

·        If a schemaobject is referenced in a SQL statement, and if this object is later modifiedby a DDL statement, then the database invalidates the shared SQL area. Theoptimizer must reparse the statement the next time it is run.

·        If you changethe global database name, then the database removes all information from theshared pool.

To assess theperformance that can be expected after a database instance restart, use the ALTER SYSTEM FLUSH SHARED_POOL statement to manually remove all information in the shared pool.