Table WRH$_SYSMETRIC_SUMMARY

[Former Member]

I have upgraded Oracle from 9.2.0.8 to 10.2.0.2 . Unfortunately , because of an Oracle bug , floating point entries ihave been corrupting in table WRH$_SYSMETRIC_SUMMARY . How severe is the impact .

[lbreddemann]

It is not severe at all.

The data in there is just summary data and won't be processed any further. So there won't be any kind of data corruption when you find errors in this table.

This is also mentioned in SAP Note 1144178 Invalid float values.

Anyhow, if you find such errors in other tables, do open a support message to have it analyzed.

Regards,

Lars

[stefan_koehler]

Hello Graeme,

only for my information: can you please tell us the bug number for that?

> How severe is the impact

The object WRH$_SYSMETRIC_SUMMARY is used in the AWR.

The view DBA_HIST_SYSMETRIC_SUMMARY is s built upon WRH$_SYSMETRIC_SUMMARY:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3191.htm#I1023452

So if the entries are corrupted your AWR data maybe not completed or corrupted.

Regards

Stefan

[Former Member]

The Oracle bug number is 5347312 and is caused by running gather_fixed_object_stats .

This gives an error ORA3113 in sqlplus and ORA 7445 in alert log. Th3e metalink entry suggests running this before gather_dictionary_stats . That is being wise before the event . I'm not in that position and it always fails .

I am unsure about AWR and how it is used , Prior to the upgrade we were not using dbms_stats .

Is it used by CBO or just analyzing problems

[Former Member]

Hi Graeme,

AWR is for proactive and reactive tuning:

if you know the statspack tunig package: AWR is the "next generation" tuning component , much more detailed and build on the dbms_workload_repository package. 10g automates capture of vital performance and workload metrics at regular intervals and stores them inside the DB (well, that's what databases for). One example where AWR is superior: if you have a RAC it automatically investigates and collects the metrics allover the nodes inside the cluster.

AWR answers questions like: Where are my performance bottlenecks ? Which advise can you give me to tune SQL Statement xyz? etc...

CBO is using the table or index statistics generated by the dbms_stats package and calculates the costs of database access choosing the best execution plan.

Different components for different purposes in 10g but in the next Oracle versions you will see that these will be tighter integrated (i.e. automatic SQL tuning or SQL plan stabilization based on AWR information in 11g).

bye

yk