SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? CURRENT_SIZE
---------------------------------------------------------------- ------------
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 2K buffer cache? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
DEFAULT 4K buffer cache? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
DEFAULT 8K buffer cache? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
DEFAULT 16K buffer cache? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
DEFAULT 32K buffer cache? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
ASM Buffer Cache? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0
13 rows selected.
sga_max_size：SGA允许的最大值，修改必须重启；
sga_target：必须小于sga_max_size, 表示当前SGA的最大值；
alter system set shared_pool_size=xxM scope=both;
3. 使用V$SHARED_POOL_ADVICE来设置shared pool的大小
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 would 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
可以使用下面的SQL语句来预估shared pool的大小：
select 'Shared Pool' component,shared_pool_size_for_estimate estd_sp_size,estd_lc_time_saved_factor parse_time_factor,case when current_parse_time_elapsed_s + adjustment_s<0then 0 else current_parse_time_elapsed_s + adjustment_s end response_timefrom (select shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_factor,a.estd_lc_time_saved,e.value/100current_parse_time_elapsed_s,c.estd_lc_time_saved - a.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$shared_pool_advice where shared_pool_size_factor=1) c);COMPONENT? ESTD_SP_SIZE? PARSE_TIME_FACTOR? RESPONSE_TIME--------------? ? -----------------? -------------------------? -------------Shared Pool? ? ? ? ? 64? ? ? ? ? ? .9989? ? ? ? ? ? ? ? ? ? ? ? ? 294.37Shared Pool? ? ? ? ? 72? ? ? ? ? ? ? ? 1? ? ? ? ? ? ? ? ? ? ? ? ? ? 257.37Shared Pool? ? ? ? ? 80? ? ? ? ? ? 1.0009? ? ? ? ? ? ? ? ? ? ? ? 226.37Shared Pool? ? ? ? ? 88? ? ? ? ? ? 1.0016? ? ? ? ? ? ? ? ? ? ? ? 201.37Shared Pool? ? ? ? ? 96? ? ? ? ? ? 1.0022? ? ? ? ? ? ? ? ? ? ? ? 181.37Shared Pool? ? ? ? ? 104? ? ? ? ? ? 1.0027? ? ? ? ? ? ? ? ? ? ? ? 166.37Shared Pool? ? ? ? ? 112? ? ? ? ? ? 1.0029? ? ? ? ? ? ? ? ? ? ? ? 156.37Shared Pool? ? ? ? ? 120? ? ? ? ? ? 1.0032? ? ? ? ? ? ? ? ? ? ? ? 149.37Shared Pool? ? ? ? ? 128? ? ? ? ? ? 1.0033? ? ? ? ? ? ? ? ? ? ? ? 144.37Shared Pool? ? ? ? ? 136? ? ? ? ? ? 1.0034? ? ? ? ? ? ? ? ? ? ? ? 141.37Shared Pool? ? ? ? ? 144? ? ? ? ? ? 1.0034? ? ? ? ? ? ? ? ? ? ? ? 139.37
11 rows selected.