虽然oracle会分解大的内存请求为多个小的内存空间请求,但是在某些系统上仍然会出现请求连续的大的共享池内存空间。如果在共享池中没有足够的空闲空间,oracle会寻找并释放足够的共享池内存空间来满足这个请求。This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.这可能会带来较为严重的性能下降。
因此 oracle内部在共享池中保留了一块小的内存空间,以供共享池没有足够的空间时使用。保留池使大的连续的内存空间分配更加高效。
默认地,oracle配置了空间比较小的保留池。这部分内存用于PL/SQL或触发器编译,或用于装载JAVA对象的临时空间。这些分配出去的内存一旦释放后将返回给保留池。
oracle分配大的连续的共享池内存的顺序:
1.从共享池非保留部分进行分配。如果共享池非保留部分有足够的内存空间,则OK;如果没有,则执行第2步。
2.如果共享池非保留部分没有足够的内存空间,oracle检查共享池中的保留池是否有足够的内存空间。如果保留池有足够的内存空间,则OK;如果保留池也没有足够的内存空间,则执行第3步。
3.这时,共享池非保留部分和保留池都没有足够的内存空间,oracle试图释放足够的内存空间。然后重试共享池中的非保留部分与保留池,即步骤1,2。
使用SHARED_POOL_RESERVED_SIZE
--默认地最小的保留池分配大小为4400字节,可由隐藏参数_shared_pool_reserved_min_alloc来查看。
--默认保留列表被配置,SHARED_POOL_RESERVED_SIZE默认为SHARED_POOL_SIZE大小的5%。
--如果设置SHARED_POOL_RESERVED_SIZE > 50%SHARED_POOL_SIZE,oracle会显示错误。
--通常,设置SHARED_POOL_RESERVED_SIZE为SHARED_POOL_SIZE大小的10%。
--保留池内存会占用共享池内存,即增大保留池内存大小后,共享池非保留部分内存空间会相应的变小。
3.
共享池太小
V$SHARED_POOL_RESERVED视图可以看出SHARED_POOL_SIZE是否设置的过小。 如果REQUEST_FAILURES 远大于0并且正在增长,说明SHARED_POOL_SIZE设置过小。
如果启用了保留列表,可以减小SHARED_POOL_RESERVED_SIZE的大小;
如果没有启用保留列表,可以增加SHARED_POOL_SIZE的大小。
最后 留意ORA-04031错误,因为在以下池中都有可能遇到ORA-04031:
1) Shared pool
2) Large Pool
3) Java Pool
4)Streams pool
Configuring the Reserved Pool
http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm#sthref488
SHARED_POOL_RESERVED_SIZE参数的设置及作用
http://www.dbtan.com/2009/12/shared_pool_reserved_size-parameter.html
共享池中保留池的调整(shared_pool_reserved_size)
因此 oracle内部在共享池中保留了一块小的内存空间,以供共享池没有足够的空间时使用。保留池使大的连续的内存空间分配更加高效。
默认地,oracle配置了空间比较小的保留池。这部分内存用于PL/SQL或触发器编译,或用于装载JAVA对象的临时空间。这些分配出去的内存一旦释放后将返回给保留池。
oracle分配大的连续的共享池内存的顺序:
1.从共享池非保留部分进行分配。如果共享池非保留部分有足够的内存空间,则OK;如果没有,则执行第2步。
2.如果共享池非保留部分没有足够的内存空间,oracle检查共享池中的保留池是否有足够的内存空间。如果保留池有足够的内存空间,则OK;如果保留池也没有足够的内存空间,则执行第3步。
3.这时,共享池非保留部分和保留池都没有足够的内存空间,oracle试图释放足够的内存空间。然后重试共享池中的非保留部分与保留池,即步骤1,2。
使用SHARED_POOL_RESERVED_SIZE
--默认地最小的保留池分配大小为4400字节,可由隐藏参数_shared_pool_reserved_min_alloc来查看。
--默认保留列表被配置,SHARED_POOL_RESERVED_SIZE默认为SHARED_POOL_SIZE大小的5%。
--如果设置SHARED_POOL_RESERVED_SIZE > 50%SHARED_POOL_SIZE,oracle会显示错误。
--通常,设置SHARED_POOL_RESERVED_SIZE为SHARED_POOL_SIZE大小的10%。
--保留池内存会占用共享池内存,即增大保留池内存大小后,共享池非保留部分内存空间会相应的变小。
1.
//保留池太小
if(REQUEST_FAILURES > 0 && REQUEST_FAILURES increasing){
//保留池太小
//增大SHARED_POOL_RESERVED_SIZE并相应增大SHARED_POOL_SIZE的大小。
}
2.
//保留池太大
if((REQUEST_MISSES == 0 || REQUEST_MISSES is not increasing)
|| FREE_MEMORY is greater than or equal to 50% of HARED_POOL_RESERVED_SIZE minimum ){
//保留池太大
//减小SHARED_POOL_RESERVED_SIZE的大小
}
3.
共享池太小
V$SHARED_POOL_RESERVED视图可以看出SHARED_POOL_SIZE是否设置的过小。 如果REQUEST_FAILURES 远大于0并且正在增长,说明SHARED_POOL_SIZE设置过小。
如果启用了保留列表,可以减小SHARED_POOL_RESERVED_SIZE的大小;
如果没有启用保留列表,可以增加SHARED_POOL_SIZE的大小。
最后 留意ORA-04031错误,因为在以下池中都有可能遇到ORA-04031:
1) Shared pool
2) Large Pool
3) Java Pool
4)Streams pool
V$SHARED_POOL_RESERVED
This fixed view lists statistics that help you tune the reserved pool and space within the shared pool.
Column | Datatype | Description |
---|---|---|
The following columns of V$SHARED_POOL_RESERVED are valid only if the initialization parameter SHARED_POOL_RESERVED_SIZE is set to a valid value. See Also: "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 |
FREE_COUNT | NUMBER | Number of free pieces of memory on the reserved list |
MAX_FREE_SIZE | NUMBER | Size of the largest free piece of 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 |
USED_COUNT | NUMBER | Number of used pieces of memory on the reserved list |
MAX_USED_SIZE | NUMBER | Size of the largest used piece of memory on the reserved list |
REQUESTS | NUMBER | Number of times that the reserved list was searched for a free piece of memory |
REQUEST_MISSES | NUMBER | Number of times the reserved list did not have a free piece of memory to satisfy the request, and started flushing objects from the LRU list |
LAST_MISS_SIZE | NUMBER | Request size of the last request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list |
MAX_MISS_SIZE | NUMBER | Request size of the largest request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list |
The following columns of V$SHARED_POOL_RESERVED contain values which are valid even if SHARED_POOL_RESERVED_SIZE is not set. | ||
REQUEST_FAILURES | NUMBER | Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-04031 occurred) |
LAST_FAILURE_SIZE | NUMBER | Request size of the last failed request (that is, the request size for the last ORA-04031 error) |
ABORTED_REQUEST_THRESHOLD | NUMBER | Minimum size of a request which signals an ORA-04031 error without flushing objects |
ABORTED_REQUESTS | NUMBER | Number of requests that signalled an ORA-04031 error without flushing objects |
LAST_ABORTED_SIZE | NUMBER | Last size of the request that returned an ORA-04031 error without flushing objects from the LRU list |
SHARED_POOL_RESERVED_SIZE
Property | Description |
---|---|
Parameter type | Big integer |
Syntax | SHARED_POOL_RESERVED_SIZE = integer [K | M | G] |
Default value | 5% of the value of SHARED_POOL_SIZE |
Modifiable | No |
Range of values | Minimum: 5000 Maximum: one half of the value of |
SHARED_POOL_RESERVED_SIZE
specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.
Configuring the Reserved Pool
http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm#sthref488
SHARED_POOL_RESERVED_SIZE参数的设置及作用
http://www.dbtan.com/2009/12/shared_pool_reserved_size-parameter.html
共享池中保留池的调整(shared_pool_reserved_size)
http://blog.csdn.net/robinson_0612/article/details/6562894