If we enable full oracle scheduled maintenance job, it will also have auto stats gathering job enabled. But lot of times we observed that when our application using transient tables(GTT's are not an option due to unavoidable reasons) those get populated and then truncated as part of process, auto stats reset stats to 0 for such tables and it leads to performance issues. So in such scenarios we have to disabled Automatic stats gathering job and schedule custom job as per application requirement.
But generally DBA's disable stats gathering jobs completely and along with this system and dictionary stats also get's disabled. So even if there is hardware change these stats will not be gathered and it may lead to poor performance of database.
To avoid such situations, we can just gather stats on Oracle resources by setting appropriate preferences.
If we are satisfied that our SQL workload is already optimal, we can set autostats_target to "oracle" to turn off automatic statistics collection in 10g and beyond.
exec dbms_stats.set_param('autostats_target','oracle');
This is a good move if you already have optimal CBO statistics and don't want to risk changing your SQL execution plans.
To check what is current target;
select dbms_stats.get_param ('AUTOSTATS_TARGET') from dual;
No comments:
Post a Comment