RE: change the LAST_ANALYZED date
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: "Leng Kaing" <Leng.Kaing@xxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 23:48:24 -0700
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:
