| Auto stats gathering - 11g - possible to exclude schema? [message #514625] |
Tue, 05 July 2011 10:53  |
 |
red5
Messages: 2
Registered: July 2011
|
Junior Member |
|
|
I need to exclude a single schema from the autostats gathering feature in 11g. The tables in this schema are analyzed at the appropriate time via the application code. The autostats gathering job sometimes kicks in at a time in which the tables are getting updated or loaded which can skew explain plans during the updates/inserts. I've searched through the oracle documentation and cannot find a way to simply "exclude" the schema without locking it. I see it is possible to disable the autostats at the entire db level but not at the schema level.
Does anyone know of way to exclude stats gathering at the schema level without locking the schema?
Thanks!
|
|
|
|
|
|
| Re: Auto stats gathering - 11g - possible to exclude schema? [message #514627 is a reply to message #514625] |
Tue, 05 July 2011 10:56  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You could disable at DB level then create a scheduler job to gather the stats on the schemas you want at the time you want.
I doubt there is a better way since your case is rather on unusual.
It is possible to move the maintenance window (the period of time in which auto stats can run) if that helps.
|
|
|
|
|
|
|
|
| Re: Auto stats gathering - 11g - possible to exclude schema? [message #514656 is a reply to message #514655] |
Tue, 05 July 2011 13:16 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
That's what lock stats does. It prevents DBMS_STATS from gathering stats on objects whit locked stats. So what should happen (again I never used dbms_stats locking) is autostats will skip schema since schema stats is locked (assuming autostats is not using force=true - you need to do some testing). Your code that gathers stats for the schema will use force=true to gather schema new stats when needed.
SY.
P.S. Locking stats prevents dbms_stats from gathering stats, it doesn't prevent using stats by otimizer
[Updated on: Tue, 05 July 2011 13:25] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
