RE: change the LAST_ANALYZED date

Oooh, I do like the idea of calling SET_INDEX_STATS to get the
last_analyzed date to change. I will have to test it and let you know!!

------------------------------------------------------------------
Leng Kaing
Senior Oracle DBA
Hansen Technologies; 2 Frederick St; Doncaster 3108
Ph: +61-3-9840-3832

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx] 
Sent: Friday, 15 December 2006 5:48 PM
To: Leng Kaing
Cc: oracle-l@xxxxxxxxxxxxx
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


Other related posts: