Wednesday, September 10, 2014

FIXED_DATE and LAST_ANALYZED

I tested this on Oracle 11.2.0.1. You need to be careful when looking at the LAST_ANALYZED column in USER_TABLES. I created a table and removed the FIXED_DATE parameter from the database:
 
SQL> create table t1 (c1 number)
  2  /
 
Table created.
 
SQL> alter system set fixed_date = none
  2  /
 
System altered.
 
SQL>
 
…then I used DBMS_STATS.GATHER_TABLE_STATS and the old ANALYZE command to create statistics for the table. Both of them set the LAST_ANALYZED column in USER_TABLES to the current date:
 
SQL> exec dbms_stats.gather_table_stats -
> (ownname=>'ORACLE',tabname=>'T1');
 
PL/SQL procedure successfully completed.
 
SQL> select last_analyzed
  2  from user_tables
  3  where table_name = 'T1'
  4  /
 
LAST_ANALYZED
-------------
10-SEP-14
 
SQL> analyze table t1 compute statistics
  2  /
 
Table analyzed.
 
SQL> select last_analyzed
  2  from user_tables
  3  where table_name = 'T1'
  4  /
 
LAST_ANALYZED
-------------
10-SEP-14
 
SQL>
 
I set FIXED_DATE to 25th December 2014 and tried again:
 
SQL> alter system set fixed_date = '25-DEC-2014'
  2  /
 
System altered.
 
SQL>
 
DBMS_STATS.GATHER_TABLE_STATS set the LAST_ANALYZED column to the new FIXED_DATE value. I believe this is not what Oracle intended and may be a result of bug 8892343, which states:
 
The value of the LAST_ANALYZED column in USER_TABLES views is affected by the setting of the FIXED_DATE parameter when it should be independent of that value.
 
SQL> exec dbms_stats.gather_table_stats -
> (ownname=>'ORACLE',tabname=>'T1');
 
PL/SQL procedure successfully completed.
 
SQL> select last_analyzed
  2  from user_tables
  3  where table_name = 'T1'
  4  /
 
LAST_ANALYZED
-------------
25-DEC-14
 
SQL>
 
…whereas the old ANALYZE command set the LAST_ANALYZED column to the underlying system date:
 
SQL> analyze table t1 compute statistics
  2  /
 
Table analyzed.
 
SQL> select last_analyzed
  2  from user_tables
  3  where table_name = 'T1'
  4  /
 
LAST_ANALYZED
-------------
10-SEP-14
 
SQL>

No comments: