what objects we have to analyze? I mean only analyze tables is enough, or other objects we have to analyze.
I am planing to analyze only tables. Is it create any new problems. Thanks
If you analyze tables with COMPUTE option, it will also analyze indexes.
In general, I use either dbms_utility.analyze_schema or dbms_utility.analyze_database to calculate my statistics. There is also a procedure called gather_database_stats in the dbms_stats package that you can use to gather your stats in parallel.
You should analyze both your tables and indexes.
Also make sure that you dont analyze any of the system objects.
I am analyzing the tables like this.
ANALYZE TABLE SCOTT.EMP COMPUTE STATISTICS;
ANALYZE TABLE SOCTT.DEPT COMPUTE STATISTICS;
I have emp_idx1, emp_idx2 is there on the EMP table.So the above anlyze command will analyze the SCOTT.EMP
indexes also. I am right. Just i am trying to make sure. Thanks
ANALYZE ALL TABLES IN SCOTT SCHEMA WITH COMPUTE STATISTICS.
Because i have two databases. On one i am running analyze command on all tables with compute statistics and another one i am exec the analyze_schema procedure.
The same sql code is accessing this two databases. Is there possible to go wrong. I mean is there chances to dicrease the performance. Thanks.
The size of the tables are almost same. The structure is same.
Click Here to Expand Forum to Full Width