-
I am going to rebuild all Production Database indexes with a batch job. I want to know that do I have to run one of these
commands after rebuild finish :
ANALYZE INDEX COMPUTE STATISTICS;
ANALYZE TABLE COMPUTE STATISTICS FOR ALL INDEXED COLUMNS
Also I want to know because couple of tables are really big is it any problem can happen? For example I think I need a big Temp Tablespace !! Please advice what safety cution I have to take into effect ?
An ounce of prevention is worth a pound of cure
-
You don't need to run analyze command separately.
In the "CREATE INDEX ...." script add compute statistics ;
This is faster than running the analyze command.
-
I don't use create index , I want to user "ALTER" INDEX ....REBUILD . Also what I have to choose for next and first extent ( Rule of thunmb ? )
An ounce of prevention is worth a pound of cure
-
Ensure you have created a big temp tablespace for sorting, if you are using CREATE INDEX statement.
Example for ALTER INDEX:
ALTER INDEX SALES_IDX REBUILD
TABLESPACE TS_IDX
STORAGE ( INITIAL 20M NEXT 20M PCTINCREASE 0)
PARALLEL NOLOGGING COMPUTE STATISTICS;
ALTER INDEX SALES_IDX LOGGING;
The INITIAL and NEXT value depend upon the table size.
-
Guidelines for rebuilding indexes
Guidelines for rebuilding Index.
Find out indexes that are fragmented
1) select TABLESPACE_NAME , SEGMENT_NAME , COUNT(*)
from DBA_EXTENTS
where SEGMENT_TYPE = 'INDEX'
group by TABLESPACE_NAME , SEGMENT_NAME HAVING COUNT(*) > 5
ORDER BY 3 DESC
this SQL gives you indexs that are fragmented. Change the
group predicate condition if you wish
2) analyze index validate structure
will analyze the index structure
3) select * from index_stats
will display the computed statistics. Some significant fields are
HEIGHT, BLOCK ,
computed value for DEL_LF_ROWS /decode( LF_ROWS,0,1,LF_ROWS ))*100
( According to Oracle for any index, if HEIGHT > 4 or the computed ratio is greater than 25 then the index is a candidate for rebuild.)
4 )select name , value from v$parameter
where name = 'db_block_size' . This will give the value of
db_block_size.
5 ) Rebuild the index
alter index rebuild tablespace
strorage (initial comp_val1 , next comp_val2 , pctincrease 0 )
comp_val1 = BLOCK ( generated in step 3 ) * value of db_block_size
comp_val2 = comp_vale1/2
6 ) If needed create a PL SQL script to autmatically perform all these
steps and run it when the work load on the server is low.
Oracle DBA
National Bank Of Kuwait
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|