| Gather Statistics - SYS [message #234838] |
Thu, 03 May 2007 05:06  |
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  |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
