SGA advisory missing in AWR report for 10.2.0.4

[Former Member]

Hello Experts,

I have recently applied Oracle 10.2.0.4 patchset on few DB servers which were on Oracle 10.2.0.2 along with all the applicable interim patches as per SAP note 1137346.

Now when I am generating the AWR reports, in the SGA Target Advisory section it says "No data exists for this section of the report". This was available before the patchset application. I have tested this in all the 3 DB servers which were upgraded.

Have I missed something during the upgrade? Is there any specific setting to be done to get those statistics? Please help.

Best Regards,

Anup Thatte

[Former Member · Accepted solution]

Hello Experts,

I think I found the answer to my question!!

While in my database, db_cache_advice is set to ON and SGA MAXSIZE is defined. However, SGA_TARGET is set to 0.

I feel if I give a certain value to sga_target (say equal to SGA_MAX_SIZE), it will populate the required field in the AWR report.

It would be great if anyone of you confirm this.. Thanks..

Best Regards,

Anup Thatte

[stefan_koehler · Accepted solution]

Hello Anup,

i am sorry but you are wrong .. the SGA advisory should also work with "manual sized SGAs".

Please let's crosscheck some things:

shell> sqlplus / as sysdba
SQL> show parameter db_cache_advice
SQL> show parameter db_cache_size
SQL> show parameter db_block_buffers

SQL> select advice_status,count(*) from v$db_cache_advice group by advice_status;

The cache advisory will not work with the old cache size parameter db_block_buffers for example.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thanks for your reply.. Please find the output of the commands which you have given:

SQL> show parameter db_cache_advice

NAME TYPE VALUE

-

---

-

---

-

---

db_cache_advice string ON

SQL> show parameter db_cache_size

NAME TYPE VALUE

-

---

-

---

-

---

db_cache_size big integer 120M

SQL> show parameter db_block_buffers

NAME TYPE VALUE

-

---

-

---

-

---

db_block_buffers integer 0

SQL> select advice_status,count(*) from v$db_cache_advice group by advice_status;

ADV COUNT(*)

--- -

---

ON 20

Using V$DB_CACHE_ADVICE I am able to gauge the db_cache_size sizing. However when I tried querying V_$SGA_TARGET_ADVICE view there are no records found. For this view to be populated, wouldn't I need SGA_TARGET to be defined so that automatic/dynamic SGA sizing is enabled??

I have tested this on my Dev system where SGA_TARGET is defined and V_$SGA_TARGET_ADVICE gives sizing values as per the AWR for this server.

Best Regards,

Anup Thatte

[stefan_koehler · Accepted solution]

Hello Anup,

ok now i see .. i think two things were mixed up under the hood of the "SGA advisory".

> Using V$DB_CACHE_ADVICE I am able to gauge the db_cache_size sizing

You are right .. this is working because of you have set the parameters (db_cache_advice / db_cache_size) correct.

This is "useful" for manual memory management.

This stuff is mentioned here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2218

> However when I tried querying V_$SGA_TARGET_ADVICE view there are no records found

Sure .. because of the SGA_TARGET does not only contain the db cache.

Values are populated to this view only, if you (want to) use the automatic shared memory management and in consequence you have to set the parameter SGA_TARGET.

This stuff is mentioned here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i58728

The question that you should answer is: "Do i want to use the ASMM feature or not?"

If you want to you can query the view V$SGA_TARGET_ADVICE to get more information .. if not you can query V$DB_CACHE_ADVICE to determine the "optimal size" for the single SGA component.

In my experience / opinion these advisories are not really "useful / reliable" .... mostly they are pointing to increase the amount of SGA memory (instead of pointing to some other root issues).

Regards

Stefan

[Former Member · Accepted solution]

Hello Stefan,

Thanks for the reply.. All my doubts are cleared!! I am closing the thread as answered..

Best Regards,

Anup Thatte