-
Auto optimizer stats not collecting when it should?
We have a Warehouse database, and I have noticed that the rows in some particular tables have increased significantly but the automated gather stats job has not analyzed stats for these tables. For example, one table has increased from 10 millions to 22 million rows, the last_analyzed is 19th April 2010. I have checked the optimizer job is running and it does every night, see below:
CLIENT_NAME JOB_STATUS
---------------------------------------------------------------- ------------------
JOB_START_TIME
---------------------------------------------------------------------------
JOB_DURATION
------------------------------
auto optimizer stats collection SUCCEEDED
28-FEB-11 22.00.02.231000 EUROPE/LISBON
+000 00:05:42
auto optimizer stats collection SUCCEEDED
01-MAR-11 22.00.02.229000 EUROPE/LISBON
+000 00:04:50
auto optimizer stats collection SUCCEEDED
02-MAR-11 22.00.01.397000 EUROPE/LISBON
+000 00:04:43
The stale_percent is 10%.
SQL> select dbms_stats.get_param('stale_percent') from dual;
DBMS_STATS.GET_PARAM('STALE_PERCENT')
---------------------------------------------------------------
10
Does anyone know what it could be?
Its Oracle version 11.1.0.7.0
Thanks.
Regards Sheryl Smith
-
According to http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm
monitoring should be activated on the tables to be included for gather auto/stale
-
Found out that the stats on these tables had been previously locked by the DBA before.
The 'auto optimizer stats collection job' will gather stats on all objects. You don't need to specifically set monitoring on any tables.
Thanks.
Regards, Sheryl
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|