This content has been marked as final. Show 15 replies
select table_name,last_analyzed1 person found this helpful
where table_name = '' and owner = ''
Check the last_analyzed time.
and i have this job sys.gather_stats_job activated ! , i have seeen this problem untill now with this table only ? any clues ???
select num_rows ,last_analyzed,stale_stats from dba_statistics where table_name ='Search_words'; num_rows :352946 , last_analyzed :11.12.08 ,stale_stats :NO. select count*) from search_words ; 892574
column operation format a301 person found this helpful
column target format a10 trunc
column start_time format a18 trunc
column end_time format a18 trunc
select operation,target,to_char(start_time,'DD/MM/YYYY HH24:Mi') start_time,
to_char(end_time,'DD/MM/YYYY HH24:Mi') end_time
from dba_optstat_operations order by start_time asc;
Will show if the jobs are running, please review. Hope this is of help.
1 person found this helpful
typo? Never heard of this view. Did you mean dba_tab_statistics?
select num_rows ,last_analyzed,stale_stats from *dba_statistics* where table_name ='Search_words';
On a side note, for your original query, check the value of stattype_locked in dba_tab_statistics.
What happens if you manually run gather stats against this table?
The job is running ,because it is gathering statiscs on other tables in the same schema,also i mean Dba_tab_statistcis and i have checked the statype_locked it is null.
i have checked this view :dba_tab_modifications and found for this table :
also i have checked dba_tables i found monitoring = YES
What does this means ???
Edited by: welo on Oct 13, 2009 4:47 AM
Perhaps as this is 10G you may wish to try setting monitoring off.1 person found this helpful
I presume it was enabled in 9i and left enabled when you upgraded?
From the SQL manual for 10G:-
MONITORING | NOMONITORING
In earlier releases, you could use these clauses to start or stop the collection of modification statistics on this table. These clauses have been deprecated.
If you formerly collected modification statistics on tables by using the DBMS_STATS package in GATHER AUTO or GATHER STALE mode, then you no longer have to do anything. Oracle Database now collects these statistics automatically, and the MONITORING and NOMONITORING keywords in existing code will not cause an error.
If, for performance reasons, you do not want to collect modification statistics on any tables, then you should set the STATISTICS_LEVEL initialization parameter to BASIC. Be aware, however, that doing so disables a number of manageability features
This is an upgraded db froma 9i , i have checked another table it is analyzed only with table iam facing the problem,i do not know what special it can be ! , what if the same problem exists with other tables ? i have more than 1000 table with more than 30 customers !.
If the old statistics indicate 352.000 rows and the dba_tab_modifications view shows 10.740 changes, then the changes ratio is:
+% changes ratio = (352.000 / 10.740)*100 = *3.05%*+
So, the Gather Auto option only chooses thoses tables that acumulate 10% of changes. Your tables isn`t candidate to gather auto.
Your solution can be to delete the statistics of that table and to pass the statistis with gather auto again.
Perhaps your best option is to log a call with Oracle support it may be a simple upgrade problem that they know about. metalink.oracle.com I hope this helps.
I know why it is not collecting statistics ,but iam concernced why it is still stal while the size of the table has been doubled ? in particular this is an active table so how can the deletes and updates equal to zero ? i can gather statistcs for this but i what if the problem is by man customers or table how i would Know ? i discovereed this by chance when i was tuning on query.
iam checking also meta link perhaps....
One option that comes into mind is that it could be possible that the stats gathering for the table is locked and thus the automatic job would not gather the stats for the table. Can you check back any such setting for the table. AFAIK if the job is running and is gathering stats on all other tables, there isno such thing that it would behave in a different way for another single table.
i have checked the statype_locked column in dba_tab_statistics it is null, the same as the other tables that are being analzed , i think the problem is why the operations in dba_tab_modifications are not updeted since lst year?! ,perhaps it is a bug... .
Perhaps the dml activyty over this table is under the 10% of the total rows. Dba_tab_modifications only changes with transactions over 10% of rows.
See Metalink 762738.1
If this is the case dba_tab_modifications could not be assigning this table as candidate.