Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to identify non-system and sys tables that have out of date statistics.

Re: how to identify non-system and sys tables that have out of date statistics.

From: <mpacheco_brazil_at_hotmail.com>
Date: 23 Feb 2006 09:58:32 -0800
Message-ID: <1140717512.231190.319470@z34g2000cwc.googlegroups.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US