Environment:
idle> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production
Under assm
The size of this parameter is the minimum value of the shared pool. The allocated value cannot be lower than this value.
If this value is exceeded, memory advisor will automatically adjust the value within the sga_target range.
idle> select component, current_size,min_size,max_size,user_specified_size from v$sga_dynamic_components where component='shared pool';COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE------------------------- ------------ ---------- ---------- -------------------shared pool 192937984 192937984 0 96468992idle> show parameter shared_pool_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------shared_pool_size big integer 92Midle> select 92*1024*1024 from dual;92*1024*1024------------ 96468992
However, some people suggest using manual management as long as it is not a library for playing.
Shared_pool with the application, always try to grow, grow to 50% of the SGA, it is no longer long, and then due to fragments and reported ORA-04031 Error
Free stock is easy to get out of control, and captive storage is unhealthy. The automatic function provided by Oracle will never be the optimal choice.
If the SGA uses assm, shared_pool will continue to grow for a busy transaction system, and a ora-04031 will be reported at that time.
Case 1:
Once a carrier library, sga_target 15g, constantly reported ora-04031, now sharing pool to 7.5g +.
Changed to manual management, 1g shared_pool, no fault reported for several years
Case 2:
I have a system with 10 GB on Linux. shared_pool is adjusted from 800 mb to 1 GB, and 4031 is reported.
In order not to discard automatic SGA adjustment
I want to increase the size of db_cache_size, but I just want to eat the space that can be increased by shared_pool.
Indeed, setting db_cache_size solves the problem of excessive shared_pool occupation.
Alternatively, you can:
You can also set these two parameters.
Shared_pool_reserved_size
_ Shared_pool_reserved_min_alloc: the minimum value is changed to 4100.
idle> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description from x$ksppi a,x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_shared_pool_reserved_min_alloc%';NAME VALUE DESCRIPTION-------------------------------- ---------- --------------------------------------------------_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area of shared pool
Attached OCP questions: