This content has been marked as final. Show 7 replies
you can lock the statistics with LOCK_SCHEMA_STATS or LOCK_TABLE_STATS:
Then calculate the statistics with your own procedure using GATHER_SCHEMA_STATS or GATHER_TABLE_STATS togehter with the parameter FORCE=TRUE.
FORCE will overwrite the lock.
HiFirst of all I think it's important that you understand why Oracle collects statistics on these tables: Because it considers the statistics of the object to be missing or stale. So if you just disable the statistics gathering on these tables then you won't have statistics at all or outdated statistics.
I am using Oracle 10g and we have few tables which
are frequently truncated and news rows added to it.
Oracle automatically analyzes the table by some means
which collects statistics of the table but at the
wrong time(when the table is empty). This makes my
query to do a full table scan rather using indexes
since the statistics was collected when the table was
empty.Could any one please let me know how to disable
the automatic statistics collection feature of
So as said by the previous posts you should gather the statistics manually yourself anyway. If you do so right after loading the data into the truncated table, you don't need to disable the automatic statistics gathering as it only processes objects that are stale or don't have statistics at all.
If you still think that you need to disable it there are several ways to accomplish it:
As already mentioned, for particular objects you can lock the statistics using DBMS_STATS.LOCK_TABLE_STATS, or for a complete schema using DBMS_STATS.LOCK_SCHEMA_STATS. Then these statistics won't be touched by the automatic gathering job. You still can gather statistics using the FORCE=>true option of the GATHER__STATS procedures.
If you want to change the automatic gathering job that it only gathers statistics on objects owned by Oracle (data dictionary, AWR etc.), then you can do so by calling DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET', 'ORACLE'). This is the recommended method.
If you disable the schedule job as mentioned in the documentation by calling DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB') then no statistics at all will be gathered automatically, causing your data dictionary statistics to be become stale over time, which could lead to suboptimal performance of queries on the data dictionary.
All this applies to Oracle 10.2, some of the features mentioned might not be available in Oracle 10.1 (as you haven't mentioned your version of 10g).
Oracle related stuff blog:
SQLTools++ for Oracle:
I would truly appreciate some help...This is my first post on OTN. This is an issue on an Oralce 10.2.0.4 data warehouse on Linux 64 bit.
I have locked a few volatile tables with a representative number of rows (10-50 million) using dbms_stats.lock_table_stats. I verified that they were locked by querying the user_tab_statistics table. I run a process that inserts thousands of records into these tables and found that the user_tab_statistics.stale_stats column is set to 'YES' for the tables whose stats were supposedly locked. Oracle SQL Advisor also recognized these tables as having stale statistics and the execution plan uses a full table scan instead of the proper index it has used before.
Thanks a bunch,