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_statsOoh, 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 Corporationhttp://www.centrexcc.com
-- //www.freelists.org/webpage/oracle-l