Shulou(Shulou.com)06/01 Report--

Old Bear: http://www.laoxiong.net/an-ora-04031-case.html

Https://blog.csdn.net/h354541060/article/details/48530303

First: the cause analysis and solution of ORA-04031

Several possibilities and solutions are analyzed below.

Basically, there are several possibilities for problems with ORA-04031

a. Unbound size results in too many shared_pool fragments and too small shared_pool_ size.

This should be quite common, and it is also mentioned most frequently by Oracle.

This is usually recommended to use binding variables, or simply increase shared_pool. Or the temporary solution is alter system flush shared_pool.

Http://www.dbanotes.net/Oracle/Ora-04031.htm

B. Large_pool,Java_ Pool is too small.

This can be easily judged by the error message (Ora-04031 cannot allocate.. Memeory in [large_pool])

The solution is simply to increase the Large_pool or Java_pool.

c. Turn on the CURSOR too much without shutting it down.

-- this problem occurs more and more frequently, especially in the JAVA operating environment. Increasing Shared_pool or flush shared_pool can only delay the occurrence of problems, not avoid them.

-- method of judgment:

Select count (*) from v$open_cursor

Select * from v$sysstat

Where name = 'opened cursors current'

If the value is very large (in tens of thousands), it can be basically determined that this is the reason.

The solution to this problem is to check the program to see if cursor is not shutting down properly (in the case of JAVA, Statement is not closed). Or select sql_text from v$open_cursor, see which cursor is not closed, and then check the car program.

-- there are also programs that use to keep a certain amount of cursor open all the time, so as to avoid turning on cursor too many times to improve performance. In this case, you should choose the appropriate shared_pool_size and the amount of cursor that controls the keep_opening.

It is also possible that the Oracle parameter session_cached_cursors is too large, and the solution is to reduce it to the appropriate value.

Diagnose and resolve ORA-04031 errors

When we fail to allocate large chunks of contiguous memory in a shared pool, Oracle first clears all objects that are not currently in use in the pool and merges free memory blocks. If there are still not enough individual chunks of memory to satisfy the request, an ORA-04031 error will occur.

When this error occurs, you get an error explanation message similar to the following:

04031, 00000, "unable to allocate% s bytes of shared memory (\"% s\ ",\"% s\ ")"

/ / * Cause: More shared memory is needed than was allocated in the shared

/ / pool.

/ / * Action: If the shared pool is out of memory, either use the

/ / dbms_shared_pool package to pin large packages

/ / reduce your use of shared memory, or increase the amount of

/ / available shared memory by increasing the value of the

/ / INIT.ORA parameters "shared_pool_reserved_size" and

/ / "shared_pool_size".

/ / If the large pool is out of memory, increase the INIT.ORA

/ / parameter "large_pool_size".

1. Instance parameters related to shared pool

Before continuing, it is necessary to understand the following instance parameters:

A.SHARED_POOL_SIZE

This parameter specifies the size of the shared pool in bytes. You can accept numeric values or numbers followed by the suffix "K" or "M". "K" stands for kilobytes and "M" for megabytes.

B.SHARED_POOL_RESERVED_SIZE

Specifies the shared pool space reserved for shared pool memory for large consecutive requests. This parameter can be used in conjunction with the SHARED_POOL_RESERVED_MIN_ALLOC parameter to avoid performance degradation when shared pool fragmentation forces Oracle to find and release large chunks of unused pools to satisfy the current request.

c. The ideal value of this parameter should be large enough to satisfy any request scan for memory in the reserved list without refreshing the object from the shared pool. Since operating system memory can limit the size of shared pools, in general, you should set this parameter to 10% of the size of the SHARED_POOL_SIZE parameter.

The value of the parameter d.SHARED_POOL_RESERVED_MIN_ALLOC controls the allocation of reserved memory. If a large chunk of memory of sufficient size cannot be found in the shared pool free list, memory allocates a space larger than this value from the reserved list. The default value is sufficient for most systems. If you increase this value, the Oracle server will allow less allocation from the reserved list and will request more memory from the shared pool list. This parameter is hidden in Oracle 8i and later. Submit the following statement to find the parameter value:

SELECT nam.ksppinm NAME, val.ksppstvl VALUE

FROM x$ksppi nam, x$ksppsv val

WHERE nam.indx = val.indx AND nam.ksppinm LIKE'% shared%'

ORDER BY 1

10g Note: a new feature of Oracle 10g called automatic memory Management allows DBA to retain a shared memory pool for shared pool,buffer cache, java pool and large pool. In general, when a database needs to allocate a large object to a shared pool and cannot find contiguous free space, it will automatically use free space from other SGA structures to increase the size of the shared pool. Since space allocation is automatically managed by Oracle, the possibility of ora-4031 error will be greatly reduced. Automatic memory management is activated when the initialization parameter SGA_TARGET is greater than 0. The current settings are available by querying the v$sga_dynamic_components view. Please refer to the 10g management manual for more information.

Ethical film http://www.dotdy.com/

two。 Diagnosing ORA-04031 error

Note: most common ORA-4031 generation is related to SHARED POOL SIZE, and most of the diagnostic steps in this article are about shared pools. For other aspects such as Large_pool or Java_pool, memory allocation algorithms are similar, generally because the structure is not large enough.

The ORA-04031 may be because the SHARED POOL is not large enough, or the database cannot find a sufficiently large block of memory because of a fragmentation problem.

ORA-04031 errors are usually due to fragments in the library cache or shared pools that retain space. Consider adjusting the application when increasing the shared pool size, use the shared SQL and adjust the following parameters:

SHARED_POOL_SIZE

SHARED_POOL_RESERVED_SIZE

SHARED_POOL_RESERVED_MIN_ALLOC

It is first determined whether the ORA-04031 error is caused by fragments of the library cache in the shared pool reserve space. Query submitted:

SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures

Last_failure_size

FROM v$shared_pool_reserved

If:

REQUEST_FAILURES > 0 and LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

Then the ORA-04031 error is due to the lack of contiguous space in the shared pool reservation space. To solve this problem, consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to reduce the number of objects buffered into the shared pool reserved space, and increasing SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the memory available in the shared pool reserved space.

If:

REQUEST_FAILURES > 0 and LAST_FAILURE_SIZE

< SHARED_POOL_RESERVED_MIN_ALLOC 或者 REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 那么是因为在库高速缓冲缺少连续空间导致ORA-04031 错误。 第一步应该考虑降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的对象到共享池保留空间中并且加大SHARED_POOL_SIZE。 3.解决ORA-04031 错误 1).ORACLE BUG Oracle推荐对你的系统打上最新的PatchSet。大多数的ORA-04031错误都和BUG 相关，可以通过使用这些补丁来避免。 下面表中总结和和这个错误相关的最常见的BUG、可能的环境和修补这个问题的补丁。

2)。 ORA-4031 that appears when compiling Java code

If there is a memory overflow when you compile Java code, you will see an error:

A SQL exception occurred while compiling::

ORA-04031: unable to allocate bytes of shared memory

("shared pool", "unknown object", "joxlod: init h", "JOX: ioc_allocate_pal")

The solution is to close the database and set the parameter JAVA_POOL_SIZE to a larger value. The "shared pool" mentioned in the error message here is actually misleading to share a global area (SGA) overflow, which does not mean that you need to increase the SHARED_POOL_SIZE. Instead, you must increase the value of the JAVA_POOL_SIZE parameter, then restart the system and try again. Reference:.

3)。 Small shared pool size

In many cases, too small a shared pool can lead to ORA-04031 errors. The following information helps you resize the shared pool:

a. Library cache hit rate

The hit ratio helps you measure the use of shared pools and how many statements need to be parsed rather than reused. The following SQL statement helps you calculate the hit ratio of the library cache:

SELECT SUM (PINS) "EXECUTIONS"

SUM (RELOADS) "CACHE MISSES WHILE EXECUTING"

FROM V$LIBRARYCACHE

b. Shared pool size calculation

To calculate the shared pool size that best suits your workload, please refer to:

4)。 Shared pool fragments

Each time a parsed form of a SQL or PL/SQL statement that needs to be executed is loaded into the shared pool requires a specific contiguous space. The first resource that the database scans is the free available memory in the shared pool. Once the free memory is exhausted, the database looks for a piece of memory that has been allocated but not yet used for reuse. If a chunk of memory of this exact size is not available, continue to look for it according to the following criteria:

i. The chunk size is larger than the requested size

ii. Space is continuous.

iii. Large chunks of memory are available (not in use)

Such large chunks of memory are separated and the rest are added to the corresponding free space list. When the database operates in this way for a period of time, the shared pool structure is fragmented.

When there is a fragmentation problem in the shared pool, it takes more time to allocate a piece of free space, and database performance degrades (throughout the operation, "chunk allocation" is controlled by a latch called "shared pool latch") or an ORA-04031 error errors (when the database cannot find a contiguous free block of memory).

If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are caused by dynamic SQL fragments in the shared pool. The possible reasons are as follows:

i. Non-shared SQL

ii. Generate unnecessary parsing calls (soft parsing)

iii. No binding variables are used

To reduce the production of debris, you need to identify several possible factors described above. There are some methods that can be taken, of course, not limited to these: application tuning, database tuning, or instance parameter tuning.

The following view helps you identify the SQL/PLSQL that is not shared in the shared pool:

A.V$SQLAREA view

This view holds information about SQL statements and PL/SQL blocks executed in the database. The following SQL statement can show you a statement with literal or a statement with bound variables:

SELECT SUBSTR (sql_text, 1,40) "SQL", COUNT (*)

SUM (executions) "TotExecs"

FROM v$sqlarea

WHERE executions

< 5 GROUP BY SUBSTR (sql_text, 1, 40) HAVING COUNT (*) >

thirty

ORDER BY 2

Note: the value "30" after Having can be adjusted as needed to get more detailed information.

B.X$KSMLRU view

This fixed table x$ksmlru tracks applications in the shared pool that result in other object swapping (age out). This fixed table can be used to mark what leads to large applications.

If many objects are periodically refreshed in the shared pool, it may cause response time problems and may cause library cache latch contention problems when the objects are reloaded into the shared pool.

One unusual thing about this x$ksmlru table is that if someone selects content from the table, the contents of the table will be erased. In this way, the fixed table stores only the largest allocation that has ever occurred. This value is reset after selection so that the next large allocation can be marked, even if they are not as large as the previous allocation. Because of such a reset, the results after the query is submitted cannot be obtained again, and the output from the table should be carefully saved. Monitor this fixed table to run the following actions:

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0

This table can only be queried with SYS login.

C.X$KSMSP view (similar to heap Heapdump information)

Using this view, you can find out the currently allocated free space and help you understand the extent of shared pool fragmentation. As we described earlier, the first place to find enough chunks of memory allocated for cursors is the free list (free list). The following statement shows a large chunk of memory in the free list:

SELECT'0 (

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.