Oracle parameter shared_pool_size

Oracle parameter shared_pool_size environment: www.2cto.com [html] idle> select * from v $ version; BANNER -------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod PL/SQL Release 10.2.0.1.0-Production CORE 10.2.0.1.0 Production TNS for Linux: version 10.2.0.1.0-Production NLSRTL Version 10.2.0.1.0-Production ASSM If the value cannot be lower than this value, Memory advisor automatically adjusts [SQL] idle> select component, current_size, min_size, max_size, within the range of sga_target, starting from v $ sga_dynamic_components where component = 'shared pool '; COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE user_specified_size ------------ ---------- using shared pool 192937984 192937984 0 96468992 idle> show parameter sh Ared_pool_size name type value =------------- ------------------------------ shared_pool_size big integer 92 M idle> select 92*1024*1024 from dual; 92*1024*1024 ------------ 96468992 however, some people suggest, as long as it is not used to play the database, we recommend that you use manual shared_pool management to keep the maximum length as long as possible with the application. When it reaches 50% of SGA, it will not be long, then due to fragmentation and reported ORA-04031 error stocking is easy to get out of control, captive is unhealthy, Oracle provides the automatic function will never be the optimal choice if SGA uses ASSM, for the transaction BUSY system, shared_pool will continue to grow, when the time to report ora-04031 Case 1: Once a carrier library, sga_target 15G, constantly reported ora-04031, now sharing pool to 7.5G +. After the change to manual management, 1g shared_pool did not report a fault for a few years Case 2: I have a system 10g on Linux, shared_pool is changed from 800 m to 1G, desperately report 4031. In order not to give up automatic SGA adjustment, I increase the size of db_cache_size by simply consuming the available space of shared_pool. Setting db_cache_size can solve the problem of excessive usage of shared_pool, you can also set these two parameters at www.2cto.com. You can also set shared_pool_reserved_size _ shared_pool_reserved_min_alloc. The minimum value is changed to 4100. The value cannot be any small value. ksppinm name, B. ksppstvl value,. ksppdesc description from x $ ksppi a, x $ ksppcv B where. indx = B. indx and. ksppinm like '% _ percent %'; name value description -------------------------------- hour _ percent 4400 minimum allocation size in bytes for reserved area of shared pool attached OCP related questions: