-
Hi,
How frequentlly should one analyse the tables and indexes of a database in which where all tables are cleared (all rows deleted ) and populated again.
The following steps are taken
- Indexes Dropped
-All records from all tables deleted.
-Indexes recreated.
Since the indexes are being recreated do they need to be analysed so that CBO takes the best path.
Please Help.
Thanks
Anurag
-
Yes you can analyze the table after deleting, and then build the indices and then analyze the indices.
Though it is not necessary for you to analyze the newly created indices, it would give some statistics for the optimizer.
Sam
-
That depends upon How much DML goin on your database. If more the DML, more often you need to analyze for optimizer to choose the best access path. Have a crontab script which runs weekly or monthly... Or at whatever frequency you wanted.
-
The database is used mainly for querying purpose.
Is dbms_utility.analyze_database a good option or should I analyse schema by schema.
Please suggest.
Thanks
Anurag
-
make sure that yo do not analyze any of the system tablespace objects, i.e objects that belong to sys/system.
Sam
-
This means that analyze database should not be used because it will analyze the sys/system table spaces also.
Anyways why should we not analyze the sys/system tablespaces.
Also how can I see the statistics which have been computed by the analyze command. I know that oracle stores it somewhere.
Thanks a lot for the help.
Anurag
-
No, DBMS_UTILITY.ANALYZE_DATABASE does skip the objects owned by SYS, so you should not have problems with it. However there were some bugs reported (8.0.5) that ANALYZING_DATABASES actually analyzes SYS's objects (particualry dangerous to anylyze are fet$ and uet$) - check for example the bug# 969814 on Metalink.