Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how to identify non-system and sys tables that have out of date statistics.
"select owner,table_name,last_analyzed from dba_tables
where owner not in ('SYS','SYSTEM') and last_analyzed<(SYSDATE-1) and
rownum<50
order by owner, table_name;"
This statements will give you wrong results. First, ROWNUM is a "pre" selection done before ORDER BY so the database will get the first 50 rows (in any order) and then sort. Second, checking "last_analyzed" is not good because a table can change a litle in terms of data so saying that it was analyzed one day ago doesn't mean we need to analyze it again. Just check for changes using MONITORING as mentioned above. Received on Thu Feb 23 2006 - 11:58:32 CST