I am on 11g r2 and there is a auto gather stats program which run's during its maintenance window's.
I want to disable this job for only partitioned tables and want to run it normally for the non-partitioned ones.
So in short can we modify this auto stats gather program to exclude partitioned tables. If yes could I know how to do this.
besides the already mentioned possibility to lock the table statistics - so they are not analyzed by the auto task during the night - you can also (11g New Feature) set different preferences for tables resp. schemas. Different means different from the global defaults that are being used by the auto task. Look here for that option:
Took a look at your link and there doesn't seem to be a way to exclude partitioned tables from the auto gather stats job. We can set table preferences for particular tables but no way to exclude it for tables we don't require.
Essentially we have already created another job to gather stats on partitioned tables so my original question still remains.
Hi Use this ;
Run the above for all your partioned tables, specifying in the table name area.
Automatic stats gathering will not touch the above table
The question would be: Why do you want to exclude some tables?
You have "good statistics" and want to keep them -> lock table stats
You want to gather statistics different from the way, autotask does it -> change table prefs
Your reason that I can't imagine :-)
I dont think i was very clear....
- Auto stats job in 11gr2 will gather stats on partitioned / non-parititioned tables.
- I need auto stats job to run for only non partitioned tables.
- I have a sepearte job to gather stats for partitioned tables.
I dont want to lock stats on any of the tables, i still need to gather them but with the above approach.
Hope I was clear this time.
Thanks Again for all inputs..
Frankly, it does not matter much what kind of tables you want to treat differently. My point is:
Instead of developing a separate job that gathers statistics the way you like, you may instead change the statistic preferences for exactly those tables and keep only one (autotask) job that gathers stats.
It would have been necessary to know about that option before developing a separate job, of course.
In other words: There was already a solution for your problem (Treat some tables differently), but you invented your own solution (separate job) and now you search for a feature supporting this homegrown solution.
But as far as I know, there is no such feature (excluding - but not stats locking - some tables from autotask).
And I explained why we don't need such a feature when using best practices.
You could try to lock stats before autotask runs and unlock afterwards.
I have just covered the two methods with this posting: