ORA-4031 error solution, ora-4031 Solution

Source: Internet
Author: User

ORA-4031 error solution, ora-4031 Solution
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, bind variables, and find SQL statements without variable binding: If a series of SQL statements with different word denominations are found in the results, you can modify the cursor_sharing parameter: alter system set cursor_sharing = 'force'; to forcibly bind variables. 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_poolNAME TYPE VALUE parameter ----------- specify parameter big integer 4Mshared_pool_size big integer 0 alter system set shared_pool_reserved = xxM scope = both; 4) use limit ('object name ') will use a large memory object k Eep in memory: Run :@? /Rdbms/admin/dbmspool. sqlSQL> @? /Rdbms/admin/dbmspool. sqlPackage created. Grant succeeded. View created. Package body created.
Then we can 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 ---------- specified partition tables ------------ SYS Partition TABLE/procedure package 33215 SYSMAN EMD_COLLECTION body package body contains partition body package body 12644SYS SYS $ rawtoany table/procedure function 12640 SYSMAN Partition TABLE/ procedure package 29030 SYSMAN internal body package body 62930 SYSMAN internal body package body 218914 SYSMAN EM_PING body package body contains body package body 95519 SYSMAN EMD_LOADER TABLE/procedure package 12641 SYSMAN EMD_LOADER body package body 71861SYS internal body package body 43624 SYSMAN internal TABLE/procedure package contains standard body package body 24960 SYSMAN internal body package body contains PRVT_ADVISOR TABLE/procedure package 12640 SYSMAN MGMT_GLOBAL TABLE/procedure package contains DBMS_STANDARD TABLE/PROCEDURE 24929SYS DBMS_ADVISOR body package body 25000SYS internal TABLE/procedure package 16732SYS PRVT_ADVISOR body package body 66780SYS internal TABLE/procedure package 43295SYS standard table/procedure package 438648SYS internal 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 limit ------------ shared pool 75497472 large pool 4194304 java 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 0ASM Buffer Cache 0
13 rows selected.
Sga_max_size: maximum value allowed by SGA. The modification must be restarted; sga_target: 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, shared_pool_size_for_estimate estd_sp_size, estd_lc_time_saved_factor parse_time_factor, case when then + adjustment_s <0 then 0 else then + adjustment_s end response_timefrom (select distinct, shared_pool_size_factor, estd_lc_time_saved_factor,. 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.