Tuning PGA_AGGREGATE_TARGET in a Oracle box

[Ganimede-Dignan]

Hi,

we have a SAP ECC 6.0 ASCII with Oracle 10.2.0.4 on a Solaris 10 SPARC box.

We wolud like to tune PGA_AGGREGATE_TARGET parameter for best performance. We have just read 619876 SAP note about it.

So our istance is live from 6 days (stop on sunday for an off-line weekly backup).

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

ESTD_OVERALLOC_COUNT

FROM v$pga_target_advice; 2 3 4

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

-

---

-

---

-

---

64 56 68

128 68 25

256 97 0

384 97 0

512 100 0

614 100 0

717 100 0

819 100 0

922 100 0

1024 100 0

1536 100 0

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

-

---

-

---

-

---

2048 100 0

3072 100 0

4096 100 0

14 rows selected.

So... is really 512 MB a right value ? ... now we have exctly 512 MB.

Regards,

Gnaimede Dignan.

[stefan_koehler · Accepted solution]

Hello Gnaimede,

this topic is very complex and if you want to know how it works i always suggest this paper by Joze Senegacnik:

http://tonguc.yilmaz.googlepages.com/JozeSenegacnik-PGAMemoryManagementvO.zip

If you have understand how it works you can interpret the given values and can "tune" it or understand why it works like this.

Regards

Stefan