Solution to ORA-4031 errors

Solution to ORA-4031 errors

In the face of ORA-4031 error, your heart will not send? The ORA-4031 can easily cause an abnormal database crash, which will crash when Oracle's core processes fail to get the Shared Pool memory. Of course, ORA-4031 is like a yellow light, And it reminds you that you need to take an emergency strategy right away. It doesn't mean that when it appears, the database will be down immediately.

Topic discussion:
1. ORA-4031 is the most annoying error in Oracle. What are the reasons for its appearance?

2. If the error 4031 occurs, that is, the memory in the shared pool is insufficient, how can we release the memory? Have you ever encountered any situations? Remember how it was solved?

3. There is a saying that the loss of vehicles and maintenance personnel, the execution plan is the key to the SQL core, does it mean that the execution plan should not be covered when the memory is released? How can we optimize the execution plan when it occupies a large amount of memory?

4. there are two main types of objects that consume the memory of the Shared Pool. One is SQL, but such executable objects as stored procedures, functions, and packages. When a 4031 error occurs, which objects do you think need to be released first?

Problem Analysis:

1. The cause of ORA-4031 error, usually a large number of hard parse causes the free list in the shared pool to generate a large number of small memory fragments, when an SQL statement that requires a large amount of memory for hard parse arrives, the memory cannot be found from the free list. Even if the memory is released, the corresponding memory block cannot be found. So as to report ORA-4031 error.

2. ORA-4031 error solution:

1) alter system flush shared_pool; clear all memory in the shared pool. This method is temporary.
2) shared SQL statement: standardize the writing of SQL statements; use bind variables; find SQL statements that do not use bind variables:
If you find a series of SQL statements with different word denominations, you can modify the cursor_sharing parameter:
Alter system set cursor_sharing = 'force'; to force variable binding.
3) use the reserved area in the shared pool:
Select request_misses from v $ shared_pool_reserved;
If the result is greater than 0, you can increase the size of shared_pool_reserved;
SQL> show parameter shared_pool
NAME TYPE VALUE
-----------------------------------------------------------------------------
Shared_pool_reserved_size big integer 4 M
Shared_pool_size big integer 0
Alter system set shared_pool_reserved = xxM scope = both;
4) use dbms_shared_pool.keep ('object name') to keep the keep of objects with large memory in the memory:
Run :@? /Rdbms/admin/dbmspool. SQL
SQL> @? /Rdbms/admin/dbmspool. SQL
Package created.
Grant succeeded.
View created.
Package body created.

Then find out the objects that require keep:
SQL> select owner, name, namespace, type, sharable_mem from v $ db_object_cache where sharable_mem> 10000
2 and (type = 'package' or type = 'package body' or type = 'function' or type = 'processed') and kept = 'no ';
Owner name namespace type SHARABLE_MEM
--------------------------------------------------------------------------------
SYS DBMS_BACKUP_RESTORE TABLE/PROCEDURE packages 33215
SYSMAN EMD_COLLECTION body package body 33233
SYS DBMS_SHARED_POOL body package body 12644
SYS $ rawtoany table/procedure function 12640
SYSMAN EMD_MAINTENANCE TABLE/PROCEDURE packages 29030
SYSMAN EMD_MAINTENANCE body package body 62930
SYSMAN MGMT_JOB_ENGINE body package body 218914
SYSMAN EM_PING body package body 29086
SYS DBMS_BACKUP_RESTORE body package body 95519
SYSMAN EMD_LOADER TABLE/procedure package 12641
SYSMAN EMD_LOADER body package body 71861
SYS PRVT_HDM body package body 43624
SYSMAN MGMT_JOB_ENGINE TABLE/PROCEDURE packages 24938
Sys standard body package body 24960
SYSMAN EM_SEVERITY_REPOS body package body 33236
SYS PRVT_ADVISOR TABLE/procedure package 12640
SYSMAN MGMT_GLOBAL TABLE/procedure package: 29902
SYS DBMS_STANDARD TABLE/PROCEDURE packaging 24929
SYS DBMS_ADVISOR body package body 25000
SYS PRVT_HDM TABLE/PROCEDURE packaging 16732
SYS PRVT_ADVISOR body package body 66780
SYS DBMS_RCVMAN TABLE/PROCEDURE packages 43295
Sys standard table/PROCEDURE packages 438648
SYS DBMS_RCVMAN body package body 375759

24 rows selected.
5) increase the size of shared_pool_size:
SQL> select component, current_size from v $ sga_dynamic_components;

COMPONENT CURRENT_SIZE
----------------------------------------------------------------------------
Shared pool 75497472.
Large pool 4194304
Java Server pool 4194304
Streams pool 0
DEFAULT buffer cache 130023424
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2 K buffer cache 0
DEFAULT 4 K buffer cache 0
DEFAULT 8 K buffer cache 0
DEFAULT 16 K buffer cache 0
DEFAULT 32 K buffer cache 0
ASM Buffer Cache 0

13 rows selected.

Sga_max_size: maximum value allowed by SGA. The modification must be restarted;
Sga_target: The value must be smaller than sga_max_size, which indicates the maximum value of the current SGA;
Alter system set shared_pool_size = xxM scope = both;

3. Use V $ SHARED_POOL_ADVICE to set the shared pool size.
V $ SHARED_POOL_ADVICE displays information about estimated parse time in the shared pool for different pool sizes. the sizes range from 10% of the current shared pool size or the amount of pinned library cache memory (whichever is higher) to 200% of the current shared pool size, in equal intervals. the value of the interval depends on the current size of the shared pool.
Column Datatype Description
SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER Shared pool size for the estimate (in megabytes)
SHARED_POOL_SIZE_FACTOR NUMBER Size factor with respect to the current shared pool size
ESTD_LC_SIZE NUMBER Estimated memory in use by the library cache (in megabytes)
ESTD_LC_MEMORY_OBJECTS NUMBER Estimated number of library cache memory objects in the shared pool of the specified size
ESTD_LC_TIME_SAVED NUMBER Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size. this is the time that wowould have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory.
ESTD_LC_TIME_SAVED_FACTOR NUMBER Estimated parse time saved factor with respect to the current shared pool size
ESTD_LC_LOAD_TIME NUMBER Estimated elapsed time (in seconds) for parsing in a shared pool of the specified size
ESTD_LC_LOAD_TIME_FACTOR NUMBER Estimated load time factor with respect to the current shared pool size
ESTD_LC_MEMORY_OBJECT_HITS NUMBER Estimated number of times a library cache memory object was found in a shared pool of the specified size

You can use the following SQL statement to estimate the size of the shared pool:
Select 'shared Pool 'component, incluestd_sp_size, incluparse_time_factor, case when limit + adjustment_s <0 then 0 else Limit + adjustment_s end response_timefrom (select distinct,. estd_lc_time_saved, e. value/100current_parse_time_elapsed_s, c. estd_lc_time_saved-. estd_lc_time_saved adjustment_s from v $ shared_pool_advice a, (select * from v $ sysstat where name = 'parse time elapsed ') e, (select estd_lc_time_saved from v $ region where region = 1) c); COMPONENT ESTD_SP_SIZE PARSE_TIME_FACTOR RESPONSE_TIME -------------- ----------------- ------------------------- ------------- Shared Pool 64. 9989 294.37 Shared Pool 72 1 257.37 Shared Pool 80 1.0009 226.37 Shared Pool 88 1.0016 201.37 Shared Pool 96 1.0022 181.37 Shared Pool 104 1.0027 Shared Pool 166.37 112 1.0029 Shared Pool 156.37 120 1.0032 Shared Pool 149.37 1.0033 144.37 Shared Pool 136 1.0034 141.37 Shared Pool 144 1.0034
11 rows selected.