RE: silly dbms_stats question

From: Mercadante, Thomas F (LABOR) <"Mercadante,>
Date: Fri, 24 Apr 2009 12:37:09 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF023CA634_at_EXCNYSM0A1AJ.nysemail.nyenet>



Dbms_stats always updates the date column on my databases also. Aix & Sun v10.2.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Crisler, Jon Sent: Friday, April 24, 2009 12:29 PM
To: Jared Still
Cc: oracle-l-freelists
Subject: RE: silly dbms_stats question  

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-l
Received on Fri Apr 24 2009 - 11:37:09 CDT

Original text of this message