ORA-04031及相关基础知识【转帖】

现象: ora8 在执行存储过程时, 经常报 ORA-04031 错误, 而在 ora7 上执行就不存在这样的问题.
现象: ora8 在执行存储过程时, 经常报 ORA-04031 错误, 而在 ora7 上执行就不存在这样的问题.
系统的相关配置:
    ora7:
        SVRMGR> show parameter shared
        NAME                                                    TYPE        VALUE
        -----------------------------------                     -------         ------------------------------       
        shared_pool_reserved_min_alloc      integer     5000                         
        shared_pool_reserved_size                integer     0                            
        shared_pool_size                                 integer     100000000
    ora8:
        SVRMGR> show parameter shared
        NAME                                                    TYPE        VALUE
        -----------------------------------                     -------         ------------------------------       
        shared_pool_reserved_min_alloc      integer     5000                         
        shared_pool_reserved_size                integer     0                            
        shared_pool_size                                 integer     100000000
诊断:
             select free_space,avg_free_size,used_space,
                avg_used_size,request_failures,last_failure_size
             from v$shared_pool_reserved
视图 v$shared_pool_reserved 可以帮助我们优化调整 share pool 中的 reserved pool 及其空间大小. free_space,avg_free_size,used_space,avg_used_size 这四个参数只有在初始化参数 SHARED_POOL_RESERVED_SIZE 被设置为有效的数值时才有意义:
        FREE_SPACE           NUMBER   Total amount of free space on the reserved list 
        AVG_FREE_SIZE    NUMBER   Average size of the free memory on the reserved list 
        USED_SPACE          NUMBER   Total amount of used memory on the reserved list 
        AVG_USED_SIZE   NUMBER   Average size of the used memory on the reserved list
 其他两个参数 request_failures,last_failure_size 即使初始化参数 SHARED_POOL_RESERVED_SIZE 没有被设置, 也是有意义的:
        REQUEST_FAILURES   NUMBER  Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-4031 occurred)  
        LAST_FAILURE_SIZE    NUMBER  Request size of the last failed request (that is, the request size for the last ORA-4031 error)
       
       
关于 The Shared Pool 的相关基础知识 ( from Oracle documentation: Oracle7 Server Concepts Manual )
 The shared pool 是 SGA 中的内存区域, 它包含三个主要的区域: library cache, dictionary cache, and control structures.
 The shared pool 的总的大小是由初始化参数 SHARED_POOL_SIZE 来决定的, 增加这个参数的大小就会增加为 shared pool 保留的内存空间, 也就是增加为 shared SQL areas 保留的内存空间.
 Library Cache: 包括 shared SQL areas, private SQL areas, PL/SQL procedures and packages, and control structures ( 例如 locks and library cache handles ).
 Shared SQL Areas and Private SQL Areas: Oracle 用一个 shared SQL area 和一个 private SQL area 来表示每个要执行的SQL语句. 当两个用户执行相同的SQL语句, Oracle 会重新组织然后重用相同的共享部分. 当然, 每个用户必须在 private SQL area 有这个语句的独立的拷贝. Shared SQL area 包含了单个的 SQL 语句的分析树和执行计划. 当一个 SQL 语句被分析, Oracle 就会从 shared pool 中为它分配空间, 空间的大小取决于语句的复杂程度. Shared SQL area 总是被相同的 SQL 语句所共享. Private SQL area 是一个包含诸如绑定信息和运行时缓存的内存区域. 每个发送 SQL 语句的 session 都会有一个 private SQL area. 对于每个提交相同 SQL 语句的用户来说, 每个用户有自己的 private SQL area, 多个 private SQL area 和一个相同的 shared SQL area 相关联.
 一个 private SQL area 有一个 persistent area 和一个 runtime area.
 Persistent area: 包含在执行过程中一直保持的绑定信息, 数据类型转换的代码( 对于那些定义的数据类型和要选择的列的数据类型不相同 ) 和其他的状态信息( 像递归或者远程的游标或者并行查询 ). Persistent area 的大小取决于绑定和语句中指定的列的数量. 举一个例子来说, 如果一个查询中指定了很多列, 那么 persistent area 就会比较大.
 Runtime area: 包含 SQL 语句正在被执行的一些信息. Runtime area 的大小取决于正在被执行的 SQL 语句的类型和复杂程度, 以及这个语句处理的行的大小. 一般来说, insert, update 和 delete 语句的 runtime area 要比 select 的要小.
 Runtime area 在一个执行请求的第一步就被创建, 对 insert, update 和 delete 语句来说, 当这个语句执行结束, runtime area 就会被释放. 对查询语句来说, 只有当所有的行都被获取或者查询被取消, runtime area 才会被释放.
 Private SQL area 一直存在到相应的游标被关闭. 当语句完成, Oracle 释放 runtime area, 还有 persistent area 还在等待. 已过用程序开发者应该关闭所有的不再使用的游标以减少内存的使用.
 private SQL area 的位置取决于 session 建立连接的方式. 如果是连接到专有服务器上, private SQL area 就在用户的 PGA 中, 如果连接到 mts 服务器上, 那么 persistent areas 和 select 语句的 runtime area 在 SGA 中.
 用户进程如何管理 private SQL areas? 管理 private SQL areas 是用户进程的职责. Private SQL areas 的分配和释放在很大程度上取决于你在使用什么样的应用工具. 虽然一个用户进程可以分配的 private SQL area 被初始化参数 OPEN_CURSORS 所限制. 这个参数的默认值是 50 .
 Oracle 如何管理 shared SQL areas? 由于 shared SQL areas 必须同时被多个用户所使用, 所以它肯定在 SGA 中, library cache 和 dictionary cache 的大小被 shared pool 的大小所限制. 分配给 shared pool 的内存的大小由初始化参数 SHARED_POOL_SIZE 决定, 这个参数的默认值是 3.5M . 当整个shared pool 都已经被分配, 如果一个用户进程试图去分配一个 shared SQL area, Oracle 会按照 LRU 的算法来释放内存空间, 直到有足够的空间. 如果一个 shared SQL area 被释放, 如果相关的 SQL 语句再一次执行, 那么这个语句必须被重新分析和被重新分配另外一个 shared SQL area.
 PL/SQL Program Units and the Shared Pool: Oracle 处理 PL/SQL 程序( 存储过程, 函数, 包, 匿名块和数据库触发器 ) 和 处理单个的 SQL 语句是相同的. Oracle 分配一个共享的区域来存放程序的被分析的, 编译的形式, Oracle 分配一个私有的区域来保存执行PL/SQL 程序的这个 session 特有的信息, 包括局部变量, 全局变量和包变量. 如果多个用户执行相同的程序单元, 共享区被所有的用户使用, 而每个用户则维持一个独立的 private SQL area, 以存放这个 session 所特有的数值.
 Dictionary Cache: 数据字典中保存有以下信息:
 @数据库中所有表和视图的名称
 @数据库表中列的名称和数据类型
 @所有Oracle 用户的权限
 由于数据字典是如此频繁地被 Oracle 访问, 内存中两个特别的结构被设计用来数据字典. 一个是 data dictionary cache , 也就是 row cache. 另一个是 library cache.
 Shared pool 中内存的分配和重新使用: 一般来说, shared pool 中的条目会一直保持直到按照改进 LRU 算法被刷新. 当新的条目需要被分配给 shared pool 中的空间时, 那些不在被常规使用的条目会被释放. 通过使用改进 LRU 算法, shared pool 中的条目可以被很多 session 所共享, 即使创建这个条目的进程已经结束. 
 当一个 SQL 语句被提交给 Oracle 去执行, 有下面这些特殊的步骤要考虑. 对每一个 SQL 语句来说, Oracle 都会自动地执行下面的内存分配步骤:
 1. Oracle 会检查 shared pool 以确定同一个语句的 shared SQL area 是否已经存在. 如果已经存在, 那么这个 shared SQL area 就会被用来执行这个语句. 因此, 对多个相同的 DML 语句来说, 在 shared pool 中只存在一个 shared SQL area 而不是多个, 极大地节省了内存空间, 特别是多个用户执行相同的应用程序.
 如果, 这个语句没有 shared SQL area, 那么一个新的 shared SQL area 就会在 shared pool 中被分配.
 注意: 一个 shared SQL area 可以被刷新, 即使相关的打开游标有一段时间没有被使用. 如果接下来需要使用这个游标, Oracle 会重新分析这个语句, 在 shared pool 中分配一个新的 shared SQL area.
 2. Oracle 会根据不同的 session ( 是 mts 服务器还是专有服务器 )来分配 private SQL area.
 一些独特的环境也会导致 shared pool 中的 shared SQL area 被刷新. 当使用 analyze 命令来更新或者删除表, 索引或簇的统计0信息时, 所以和这些对象相关的 shared SQL area 都会被刷新. 这些语句下一次执行的时候, 会在一个新的 shared SQL area 中重新被分析, 以反映对象的新的统计信息. Shared SQL area 也依赖对应语句所引用的对象. 如果一个引用对象被修改了, 那么 shared SQL area 就会被标记为无效, 下一次这个语句要重新分析.
 如果你改变了数据库的 global database name, 那么 shared pool 中的所有信息都会被刷新. 系统管理员可以手工地刷新 shared pool 中的所有信息以提高系统的性能, 而不必关闭实例. ( alter system fulsh shared_pool )

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

转载于:http://blog.itpub.net/92530/viewspace-531916/