Home » RDBMS Server » Performance Tuning » Gather Statistics - SYS
Gather Statistics - SYS [message #234838] Thu, 03 May 2007 05:06 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I was reading this article.

Now, I have a doubt.
1. Do we need to gather statistics of SYS schema in Ora 9iR2?

2. Will regularly gathering statistics of SYS help in any way for CBO?

Brayan.
Re: Gather Statistics - SYS [message #235181 is a reply to message #234838] Fri, 04 May 2007 04:26 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Hi

Yes gathering sys staistics would be safe , provided we make it as a one time activity.Also we can gather the statistics for Fixed Objects.The size of these tables (x$ tables) depends on the init.ora settings, so unless we go for a larger infrastructure change , the stats collection for Dictionary Tables and the Fixed Objects would be a one time one and would be safe more likely.

Thanks
Re: Gather Statistics - SYS [message #235304 is a reply to message #234838] Fri, 04 May 2007 17:02 Go to previous message
dbaxchangedba
Messages: 26
Registered: November 2005
Junior Member
"Do we need to gather statistics of SYS schema in Ora 9iR2?"

Not really. Although collecting statistics on dictionary tables is supported from 8i, it is not recommended in 8i and 9i versions. Check metalink note 375944.1 for more details.

Will regularly gathering statistics of SYS help in any way for CBO?

Help with what? performance of recursive sql statements? Here's some of the things you would need to do before deciding as to whether recursive sql statements are a bottle neck:

- Check for recursive call and recursive cpu usage statistics in your statspack report and see if you are seeing high numbers.

- Check and see v$rowcache for gets and misses.

- Trace sessions that seem to experience high CPU usage and all trace files have a section at the end that give detailed totals on recursive statements.

- Utilize the features available that help you take some of the load away from the data dictionary. Features like locally managed tablespaces, tempfiles for temporary tablespaces and automatic segment space management help you achieve some of that.

- Check for object (procedures, packages, functions, triggers....) invalidations and recompiles. Fix the root causes if they seem to happen frequently.

- Check for DDL statements within developer's code and get rid of unnecessary DDL statements. DDL statements contribute to DML with the data dictionary.

They are many more things like these that can be done before actually deciding on collecting stats on the data dictionary tables.

Good luck........

http://www.dbaxchange.com
Previous Topic: Shared Pool Statistics - StatsPack Report
Next Topic: oracle sql query analyzer optimize tool
Goto Forum:
  


Current Time: Thu Dec 08 08:37:24 CST 2016

Total time taken to generate the page: 0.11553 seconds