How to solve the ORA-04031 error of Oracle
This article introduces the knowledge of "how to solve the ORA-04031 error of Oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
The cause of the ORA-04031 error:
1. A lot of hard parsing appears.
two。 Suddenly a big SQL appeared.
Solution to 04031 error:
(1) execute SQL > alter system flush shared_pool directly
This method can be alleviated, but there is no fundamental solution to the problem.
(2) convert an unshared SQL into a shared SQL. (use bind variables)
(3) modify cursor_sharing parameters
SQL > alter system set cursor_sharing='force'
After modifying this parameter, you can force binding variables to implement a shared SQL. However, if the SQL cannot be shared due to irregular writing, such as spaces, carriage returns, etc., it cannot be solved at this time.
(4) ① execution @? / rdbms/admin/dbmspool.sql
② SQL > select * from v$db_object_cache where sharable_mem > 10000 and (type = 'PACKAGE' or type =' PACKAGE BODY' or type = 'FUNCTION' or type =' PROCEDURE') and kept = 'NO'
③ executes dbms_shared_pool.keep ('object name')
(5) increase shared_pool space.
SQL > select component,current_size/1024/1024 M from v$sga_dynamic_components
SQL > show parameter sga_target
SQL > show parameter sga_max_size
SQL > alter system set shared_pool_size=150M scope=both
Note: sga_max_size is used to limit the size of sga_targat. Sga_target can never exceed sga_max_size.
(6) reserved area
The reservation is dedicated to caching large SQL.
SQL > select request_misses from v$shared_pool_reserved
This query is the number of times the query request failed in the reserved area. This value is preferably, when this value exists, there is bound to be a 04031 error.
Resize the reservation:
SQL > show parameter shared_pool_reserved_size
When there is a wrong value in request_misses, we need to increase the shared_pool_reserved_size.
This is the end of the content of "how to solve the ORA-04031 error of Oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
