Corruption SYS user

[Former Member]

Hello experts I am reading SAP Database Admin with Oracle by Michael Hoding, Andre Faustmann, Gunnar Kelein, Ronny Zimmermann. In chapter 9 System Operation and Monitoring section 9.2.1.1 Complete Analysis it states:

Check at least the objects of the SYS user with this procedure, because when a corruption of an object of SYS remains undetected, an considerably time-consuming full database export and import is required in the worst case. You can use

brconnect -u / -c -f stats -t oradict_tab -v for a dedicated analysis of the objects of the SYS user.

I run:

brconnect -u / -c -f stats -t ALL on all my oracle servers. How many of you run stats against the SYS user....??? I thought it was a bad practice to do this.

Thanks

[Former Member]

Hello Billy,

the oradict_tab option was developed for Oracle 10g. Before this release, it was indeed not advisable to compute statistics on Oracle DDIC objects. The only exception to this was for some BW systems. I think it is due to the fact that Oracle uses some rule-based code for DDIC SQL access. In Oracle 10g though, everything uses cost-based optimizer. Computing stats on SYS objects is thus good practice and should be done once a quarter if you follow SAP's recommendation.

[stefan_koehler]

Hello Sébastien,

i think your are mixing two things: verify database objects and gathering statistics.

The option "oradict_tab" is used to verify the oracle ddic objects. If you wan to gather statistics on DDIC objects, you have to call it with the option "oradict_stats".

Check sapnote #23345 for the option "oradict_tab" and the alternatives.

http://help.sap.com/saphelp_nwpi71/helpdata/en/46/9f5515bb8e0488e10000000a1553f6/content.htm

How many of you run stats against the SYS user

To be honest, i have never done a verify against the DDIC objects. I only collect statistics with "oradict_stats" on my oracle 10g databases.

Regards

Stefan

[Former Member]

hi Stefan,

indeed I mixed up the two options ... that's what happen when you answer early in the morning héhé

Thanks for pointing the mismatch.

Seb