RE: change the LAST_ANALYZED date
- From: "Leng Kaing" <Leng.Kaing@xxxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 15 Dec 2006 18:09:12 +1100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
