RE: change the LAST_ANALYZED date

Thanks for this, Wolfgang.

I've just tested the SET_INDEX_STATS and the last_analyzed is certainly changed to today. Good to know that I have a work around if I Oracle Support doesn't come to the party.

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Friday, 15 December 2006 5:48 PM
To: Leng Kaing
Cc: oracle-l_at_freelists.org
Subject: RE: change the LAST_ANALYZED date

At 11:26 PM 12/14/2006, Leng Kaing wrote:
>
>"How did it get to be set to a future date?" by changing the o/s
>system time and then running a gather_index_stats
>
>Ooh, hadn't thought of that. Will keep that in mind, thanks, Wolfgang.
>
>However, I wanted a quick pl/sql call rather than having to muck
>around with the export/import. I guess what we're doing is changing
>system time for some test requirements. We put the time forward to
>trigger an event such as billing to happen. Then put the time back
>to today and carry on with testing. Data is loaded and we generate
>stats then continue on with testing. GATHER_INDEX_STATS is not
>working unless we either delete stats or do what you are suggesting.

export/import index_stats are simple enough pl/sql calls, but if that's too complicated, any set_index_stats call wil also update the last_analyzed date:

9.2.0.8> exec print_table('select * from user_indexes where

index_name = ''Z1''');
INDEX_NAME                    : Z1
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : SCOTT
TABLE_NAME                    : Z1
TABLE_TYPE                    : TABLE
UNIQUENESS                    : NONUNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS4K
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 65536
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  : 0
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     : 1
FREELIST_GROUPS               : 1
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 1
LEAF_BLOCKS                   : 41
DISTINCT_KEYS                 : 100
AVG_LEAF_BLOCKS_PER_KEY       : 1
AVG_DATA_BLOCKS_PER_KEY       : 3
CLUSTERING_FACTOR             : 337
STATUS                        : VALID
NUM_ROWS                      : 10000
SAMPLE_SIZE                   : 10000
LAST_ANALYZED                 : 2007-01-01 00:00:00
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
-----------------

PL/SQL procedure successfully completed.

9.2.0.8> exec dbms_stats.set_index_stats(user,'z1',numdist=>100);

PL/SQL procedure successfully completed.

9.2.0.8> exec print_table('select * from user_indexes where

index_name = ''Z1''');
INDEX_NAME                    : Z1
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : SCOTT
TABLE_NAME                    : Z1
TABLE_TYPE                    : TABLE
UNIQUENESS                    : NONUNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS4K
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 65536
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  : 0
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     : 1
FREELIST_GROUPS               : 1
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 1
LEAF_BLOCKS                   : 41
DISTINCT_KEYS                 : 100
AVG_LEAF_BLOCKS_PER_KEY       : 1
AVG_DATA_BLOCKS_PER_KEY       : 3
CLUSTERING_FACTOR             : 337
STATUS                        : VALID
NUM_ROWS                      : 10000
SAMPLE_SIZE                   : 10000
LAST_ANALYZED                 : 2006-12-14 23:29:21
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
USER_STATS                    : YES
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
-----------------

PL/SQL procedure successfully completed.

9.2.0.8>

But you'll have to retrieve a value to use in the set call first. It's 6 or 1/2 dozen either way.

>
>I'm also a bit concerned about daylight savings as the clock is set
>back one hour and we don't have the luxury to do the delete or
>import of stats for this.
>

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l