Should you or should you not analyze sys schema in 9i? 2

[dbtoo2001]

I am interested in your real world experiences in the analyzing of sys schema in 9i. Some documents state it should be done. What have you found to be the real world case?
Also, I was wondering what your real world experience is for analyzing other schemas? Do you do it on a scheduled basis, monitored basis (gather stale), or other? (Analyzing invalidates the shared pool for sql that is acting on the newly
Thanks.

 

[djbjr]

I dont have any comments on analyzing the sys schema but I think you should famiarize yourself with the new DBMS_STATS package where you can gather statistics. We have migrated our data warehouse completely off of analyze statements and have been using DBMS stats package, we analyze every table after 10% of the data has changed.

 

[sem]

It's a strict no-no as all queries in sys schema are already tuned for RBO. You should drop that statistics.

Regards, Dima

http://seminihin.narod.ru

 

[dbtoo2001]

Dima, do you have an oracle reference, or strictly experience? (I know, I was asking for experience, not reference.) Do you have any scenarios where dropping the stats gave better execution?

 

[Turkbear]

Hi,
If I may step in:

Thomas Kyte in his "Expert One-on-One Oracle" clearly states the reason ( Appendix A, page 1176) to paraphrase)

For SYS schema especially, the recursive SQL generated by Oracle over the years was optimized for execution using the rule-based optimizer.Having Ststistics on SYS ( and SYSTEM) objects will cause your database to operate slower than it should.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[karluk]

The only thing I know about analyzing the Oracle catalog tables is that the last couple of 9.2 patch sets have recommended running dbms_stats.drop_schema_stats on the sys schema before running the upgrade script, and then running dbms_stats.gather_schema_stats afterwards. The installation instructions say that the upgrade script will run faster with no statistics.

So you can take that advice for what it's worth. On the surface, it appears that there are certain situations where things will run faster without catalog statistics. On the other hand, the fact that the installation instructions say to run gather_schema_stats at the end of the upgrade script strongly implies that Oracle thinks it's just fine in general to generate statistics on the catalog tables.

 

[sem]

Here's some reference

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5792247321358

, but may be as Karluk stated, this is a bit out of date now.

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Ok, I found that it's essential to use dbms_stats.gather_schema_stats and not other ways of gathering statistics on SYS schema, e.g. in Note 245051.1

Regards, Dima

http://seminihin.narod.ru

 

[dbtoo2001]

Thank you, Sem, and others for you valuable inputs.

 

[sem]

I must redirect this star to Karluk as he was the first who pointed the right direction.

Regards, Dima

http://seminihin.narod.ru