SGA ,PGA调整SQL语句

1:SGA预估为大于shared_pool 和buffer cache之和

2:PGA直接采用pga_ADVICE

share_pool建议值，ELTTS=1
select t.SHARED_POOL_SIZE_FOR_ESTIMATE "SP",
t.ESTD_LC_SIZE "EL",
t.ESTD_LC_MEMORY_OBJECTS "ELM",
t.ESTD_LC_TIME_SAVED "ELT",
t.ESTD_LC_TIME_SAVED_FACTOR "ELTS %",
t.ESTD_LC_MEMORY_OBJECT_HITS "ELMO"
from v$shared_pool_advice t
;

查看buffer cache, physical_read % 物理都比较小的时候

SELECT size_for_estimate "target M",
buffers_for_estimate,
estd_physical_read_factor "physical_read %",
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

PGA大小 alloc=0时候，最好est rw m=0时候。
select round(t.PGA_TARGET_FOR_ESTIMATE/1024/1024) "target M",
t.ESTD_PGA_CACHE_HIT_PERCENTAGE "Est Cache Hit %",
round(t.ESTD_EXTRA_BYTES_RW/1024/1024) "Est RW M",
t.ESTD_OVERALLOC_COUNT "alloc"
from v$pga_target_advice t;

QL> select t.SHARED_POOL_SIZE_FOR_ESTIMATE "SP",
2 t.ESTD_LC_SIZE "EL",
3 t.ESTD_LC_MEMORY_OBJECTS "ELM",
4 t.ESTD_LC_TIME_SAVED "ELT",
5 t.ESTD_LC_TIME_SAVED_FACTOR "ELTS %",
6 t.ESTD_LC_MEMORY_OBJECT_HITS "ELMO"
7 from v$shared_pool_advice t
8 ;

SP EL ELM ELT ELTS % ELMO
---------- ---------- ---------- ---------- ---------- ----------
256 256 26323 2374028 1 135367704
320 319 37229 2374064 1 135412280
384 383 47812 2374087 1 135459760
448 447 58563 2374106 1 135500576
512 511 69247 2374122 1 135539277
576 575 80105 2374134 1 135571078
640 639 91016 2374145 1 135595778
704 703 101621 2374159 1 135618413
768 767 112281 2374178 1 135632979
832 830 121898 2374200 1 135651449
896 893 130271 2374206 1 135658643
960 955 137711 2374210 1 135667251
1024 1017 144157 2374215 1 135676387

13 rows selected

上面可以看出shared_pool 为256M就可以了

SQL> SELECT size_for_estimate "target M",
2 buffers_for_estimate, estd_physical_read_factor "physical_read %",
3 estd_physical_reads
4 FROM V$DB_CACHE_ADVICE
5 WHERE name = 'DEFAULT'
6 AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
7 AND advice_status = 'ON';

target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS
---------- -------------------- --------------- -------------------
32 2016 3.429 7638104538
64 4032 2.9331 6533520605
96 6048 2.6578 5920281917
128 8064 2.4278 5407866686
160 10080 2.1395 4765688713
192 12096 1.8686 4162334128
224 14112 1.6706 3721318079
256 16128 1.4986 3338220666
288 18144 1.3533 3014462340
320 20160 1.2384 2758632779
352 22176 1.1394 2537902228
384 24192 1.0448 2327204753
400 25200 1 2227492898
416 26208 0.9588 2135661357
448 28224 0.8791 1958109900
480 30240 0.8076 1798865580
512 32256 0.7483 1666928361
544 34272 0.6847 1525133709
576 36288 0.6164 1372938777
608 38304 0.5551 1236473620

target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS
---------- -------------------- --------------- -------------------
640 40320 0.4939 1100249358

21 rows selected

上面看出buffer cache 为640M就可以了 逻辑读降低为（1-0.4939）

SQL> select round(t.PGA_TARGET_FOR_ESTIMATE/1024/1024) "target M", t.ESTD_PGA_CACHE_HIT_PERCENTAGE "Est Cache Hit %", round(t.ESTD_EXTRA_BYTES_RW/1024/1024) "Est RW M", t.ESTD_OVERALLOC_COUNT "alloc" from v$pga_target_advice t;

target M Est Cache Hit % Est RW M alloc
---------- --------------- ---------- ----------
13 55 4501302 181258
25 55 4428124 138660
50 68 2575119 6960
75 71 2213322 0
100 75 1855125 0
120 76 1755118 0
140 82 1156848 0
160 84 1030524 0
180 85 962708 0
200 85 962126 0
300 85 941166 0
400 88 749678 0
600 88 734498 0
800 93 384655 0

14 rows selected

上面可以看出PGA 目前只要设置为75M,

所以预估为：75+256+640=700M左右

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22934571/viewspace-1047311/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1047311/