Right use analyze_schema this is less time consuming for you in one command it will analyze all the objects in the schema where if you analyze using analyze_table you will have to do it manually one by one or write a script put all the analyze_table statements in it and then execute the file.I feel both of them will consume the same CPU resources
However, analyze_schema is a serial process. If you write your own, you can launch several jobs simultanwously that can each analyze a different set of tables. This is, AFAIK, the most efficient way to analyze the entire schema.
Whats the difference between ESTIMATE and COMPUTE...
We always do compute here. When will you do Estimate ?
When do you do validate structure ?
Also if you do analyze table it does analyze indexes too right, I saw the new date in user_indexes tables..just wanted to confirm this.
If say I analyze all of my tables today i.e May 21st... and I do lots of DML on that database. For how long will they be using cost based optimizer ? In other words when do I need to analyze all my tables again ?