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

[Former Member]

Dear Gnaimede

Why do you want to set this paramter up by yourself. This parameter is more useful to set in case of BI systems. Also, having gone through the note, I noticed that Oracle itself calculates the pga_memory as of ORacle 9i. You can check the same from SQL prompt

SQL> show paramter workarea_size_policy scope=spfile;

If this is set to AUTO, then Oracle will calculate the same itself.

Lokesh