11G的内存设置参数有memory_target、memory_max_target、sga_target、pga_aggregate_target等。
一个特别繁忙的数据库，前期内存设置较低，物理内存256G，memory_target设置了85G，而sga_max_size设置了20多G，前天晚上将sga_target设置了50G，这样，pga使用了30G。运行一天后，数据库性能有改善，第三天下午检查数据库的内存情况，如下。
memory总体情况如下：
SQL> select * from v$memory_dynamic_components; COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE ------------------------- ------------ ------------ ------------ ------------------- ------------ --------------- --------------- ------------------ ------------ shared pool 14227079168 13690208256 14495514624 4294967296 14 GROW IMMEDIATE 01-DEC-16 268435456 large pool 268435456 268435456 2415919104 0 18 SHRINK DEFERRED 01-DEC-16 268435456 java pool 1879048192 268435456 1879048192 0 13 SHRINK DEFERRED 01-DEC-16 268435456 streams pool 268435456 268435456 268435456 0 0 STATIC 268435456 SGA Target 53687091200 53687091200 53687091200 53687091200 0 STATIC 268435456 DEFAULT buffer cache 36507222016 34359738368 38386270208 0 45 SHRINK IMMEDIATE 01-DEC-16 268435456 KEEP buffer cache 0 0 0 0 0 STATIC 268435456 RECYCLE buffer cache 0 0 0 0 0 STATIC 268435456 DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 268435456 DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 268435456 DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 268435456 DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 268435456 DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 268435456 Shared IO Pool 0 0 0 0 0 STATIC 268435456 PGA Target 32212254720 32212254720 32212254720 0 0 STATIC 268435456 ASM Buffer Cache 0 0 0 0 0 STATIC 268435456 16 rows selected.
SGA建议如下：
SQL> select * from v$sga_target_advice; SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ------------ --------------- ------------ ------------------- ------------------- 19200 .375 7931996 4.031 2814487593 25600 .5 3486458 1.7718 910395653 32000 .625 2498844 1.2699 486148388 38400 .75 2176921 1.1063 347993756 44800 .875 2037605 1.0355 288157726 51200 1 1967749 1 258136456 57600 1.125 1922493 .977 238672967 64000 1.25 1889239 .9601 224423835 70400 1.375 1860118 .9453 211878403 76800 1.5 1839260 .9347 202946882 83200 1.625 1828044 .929 198222985 89600 1.75 1827849 .9289 198222985 96000 1.875 1827853 .9289 198222985 102400 2 1827854 .9289 198222985 14 rows selected.
根据如上建议，增大SGA至80G，预计的物理读可降到198222985，比258136456减少20%，值得增大。
PGA建议如下：
SQL> select * from v$pga_target_advice; PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADVICE_ST BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ----------------------- ----------------- --------- --------------- ------------ ------------------- ----------------------------- -------------------- 4026531840 .125 ON 3.529844E+12 552384320 588491400192 86 2882 8053063680 .25 ON 3.529844E+12 479063879 41846286336 99 0 16106127360 .5 ON 3.529844E+12 479063879 41846286336 99 0 24159191040 .75 ON 3.529844E+12 479063879 41846286336 99 0 32212254720 1 ON 3.529844E+12 478251898 35792513024 99 0 38654705664 1.2 ON 3.529844E+12 473602153 1126048768 100 0 45097156608 1.4 ON 3.529844E+12 473602153 1126048768 100 0 51539607552 1.6 ON 3.529844E+12 473602153 1126048768 100 0 57982058496 1.8 ON 3.529844E+12 473602153 1126048768 100 0 64424509440 2 ON 3.529844E+12 473602153 1126048768 100 0 96636764160 3 ON 3.529844E+12 473602153 1126048768 100 0 128849018880 4 ON 3.529844E+12 473602153 1126048768 100 0 193273528320 6 ON 3.529844E+12 473602153 1126048768 100 0 257698037760 8 ON 3.529844E+12 473602153 1126048768 100 0 14 rows selected.
解读如上建议，目前的pga设置30G，其cachehitpercentage已经达到99%，满足需要，即使降到16G甚至8G，也没有太大关系。
同时观察shared_pool_advice，如下：
SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE spsfe, 2 SHARED_POOL_SIZE_FACTOR spsf, 3 ESTD_LC_SIZE, 4 ESTD_LC_MEMORY_OBJECTS elmo, 5 ESTD_LC_TIME_SAVED elts, 6 ESTD_LC_TIME_SAVED_FACTOR eltsf, 7 ESTD_LC_MEMORY_OBJECT_HITS emoh 8 from v$shared_pool_advice; SPSFE SPSF ESTD_LC_SIZE ELMO ELTS ELTSF EMOH ------------ ------------ ------------ ------------ ------------ ------------ ------------ 12032 .8868 362 23808 13983348 .7228 458756144 12288 .9057 617 29861 19274992 .9964 599814975 12544 .9245 873 39752 19293848 .9973 600564212 12800 .9434 1129 50405 19306814 .998 601130086 13056 .9623 1384 58293 19319633 .9987 601688324 13312 .9811 1640 66256 19332401 .9993 602246380 13568 1 1895 76384 19345399 1 602815513 13824 1.0189 2151 85179 19345548 1 602823927 14080 1.0377 2407 94010 19345590 1 602826230 14336 1.0566 2663 102820 19345612 1 602827300 14592 1.0755 2919 111775 19345638 1 602828430 14848 1.0943 3175 121716 19345666 1 602829545 15104 1.1132 3431 134979 19345693 1 602830817 15360 1.1321 3687 144453 19345706 1 602831742 15616 1.1509 3943 153503 19345743 1 602832763 15872 1.1698 4199 166421 19345762 1 602833660 16640 1.2264 4967 194728 19345805 1 602837143 18176 1.3396 6503 252133 19346012 1 602848917 19712 1.4528 8039 305260 19346039 1 602849770 21248 1.566 8463 320450 19346039 1 602849832 22784 1.6792 8463 320450 19346039 1 602849832 24320 1.7925 8463 320450 19346039 1 602849832 25856 1.9057 8463 320450 19346039 1 602849832 27392 2.0189 8463 320450 19346039 1 602849832 24 rows selected.
可见oracle基于目前SGA大小自动设置的13G是可以的，但是随着SGA大小的变化，应该会有更加的大小设置，待设置更大SGA以后验证。
综上，此数据库需如下设置：【增大memory_target，增大sga_target，pga保持不变】
ALTER SYSTEM SET memory_max_target= 110g SCOPE=spfile SID='*';
ALTER SYSTEM SET memory_target= 110g SCOPE=spfile SID='*';
ALTER SYSTEM SET sga_target = 80g SCOPE=spfile SID='*';
如果操作系统shm不支持110G大小，则降低pga设置，不改变memory_target，增大sga至65G。