Allocation and Reuse of Memory in the Shared Pool(91)

In general, any item (shared SQL area or dictionary row) in the shared pool remains
until it is flushed according to a modified LRU algorithm. The memory for items that
are not being used regularly is freed if space is required for new items that must be
allocated some space in the shared pool. A modified LRU algorithm allows shared
pool items that are used by many sessions to remain in memory as long as they are
useful, even if the process that originally created the item terminates. As a result, the
overhead and processing of SQL statements associated with a multiuser Oracle system
is minimized.
When a SQL statement is submitted to Oracle for execution, Oracle automatically
performs the following memory allocation steps:
1. Oracle checks the shared pool to see if a shared SQL area already exists for an
identical statement. If so, that shared SQL area is used for the execution of the
subsequent new instances of the statement. Alternatively, if there is no shared SQL
area for a statement, Oracle allocates a new shared SQL area in the shared pool. In
either case, the user’s private SQL area is associated with the shared SQL area that
contains the statement.
2. Oracle allocates a private SQL area on behalf of the session. The location of the
private SQL area depends on the type of connection established for the session.
Oracle also flushes a shared SQL area from the shared pool in these circumstances:
■ When the ANALYZE statement is used to update or delete the statistics of a table,
cluster, or index, all shared SQL areas that contain statements referencing the
analyzed schema object are flushed from the shared pool. The next time a flushed
statement is run, the statement is parsed in a new shared SQL area to reflect the
new statistics for the schema object.
■ If a schema object is referenced in a SQL statement and that object is later modified
in any way, the shared SQL area is invalidated (marked invalid), and the
statement must be reparsed the next time it is run.
■ If you change a database’s global database name, all information is flushed from
the shared pool.
■ The administrator can manually flush all information in the shared pool to assess
the performance (with respect to the shared pool, not the data buffer cache) that
can be expected after instance startup without shutting down the current instance.
The statement ALTER SYSTEM FLUSH SHARED_POOL is used to do this.

共享区内存的分配与再使用
1. 一般来说, 共享区中的条目一直保存直到根据LRU算法将它FLUSH
2. 当新的SQL语句提交执行, Oracle进行以下步骤来分配内存
a. 首先查询共享SQL区域是否有相同的语句, 如果有则利用以前的执行计划
如果没有, 则分配一个新的共享内存区域
b. Oracle分配一个私有SQL区域给会话, 该位置与建立连接的会话有关.
3. 以下情况下Oracle FLUSH共享SQL区域
a. 使用analyze语句来更新或者删除表, 索引的统计信息
b. 如果一个SQL语句引用的对象被修改, 共享内存区域被标记为失效的
c. 改变数据库的全局数据库名
d. 手工执行ALTER SYSTEM FLUSH SHARED_POOL

[@more@]

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

转载于:http://blog.itpub.net/10599713/viewspace-978507/