RE: silly dbms_stats question
Date: Fri, 24 Apr 2009 12:29:14 -0400
Message-ID: <56211FD5795F8346A0719FEBC0DB0675042C7B84_at_mds3aex08.USIEXCHANGE.COM>
My information was that dbms_stats does not update the last_analyzed column of dba_tables / user_tables, and this affected all versions of 10g, but was fixed in 11g. However your test seems to contradict my information, but I have tested this many times and found it to be true, even on 10.2.0.3. I wonder if there is a patch that changed this behavior ?
From: Jared Still [mailto:jkstill_at_gmail.com]
Sent: Friday, April 24, 2009 12:23 PM
To: Crisler, Jon
Cc: oracle-l-freelists
Subject: Re: silly dbms_stats question
On Fri, Apr 24, 2009 at 8:49 AM, Crisler, Jon <Jon.Crisler_at_usi.com> wrote:
I know that dba_tables column "last_analyzed" does not reflect dbms_stats. DBA_TAB_MODIFICATIONS tracks monitoring for dbms_stats, but it does not track when dbms_stats was run.
Could you be more specific on that?
Version, etc?
Just ran this test on a 10.2.0.3 database on RH EL4, and last_analyzed
is
being updated by dbms_stats as I would expect:
select table_name, last_analyzed
from user_tables
where table_name = 'AT1'
order by last_analyzed
/
begin
dbms_stats.gather_table_stats(user,'AT1');
end;
/
select table_name, last_analyzed
from user_tables
where table_name = 'AT1'
order by last_analyzed
/
09:20:01 ordevdb01.radisys.com - js001292_at_dv11 SQL> _at_s5
TABLE NAME LAST_ANALYZED ------------------------------ ------------------- AT1 08/22/2006 22:00:02
1 row selected.
PL/SQL procedure successfully completed.
TABLE NAME LAST_ANALYZED ------------------------------ ------------------- AT1 04/24/2009 09:20:03
1 row selected.
I've been using last_analyzed for several versions, and haven't seen the problem you mentioned.
Or if it was there, I just missed it. :)
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 24 2009 - 11:29:14 CDT