What objects are nessasary to analyze

[sree_sri]

Hi Folks,

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

[marist89]

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.
http://technet.oracle.com/docs/produ...tats.htm#25656

[anuragmin]

You should analyze both your tables and indexes.

Also make sure that you dont analyze any of the system objects.


Anurag

[sree_sri]

Hi Marist89,

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

[marist89]

Yes, correct.

[sree_sri]

ANALYZE ALL TABLES IN SCOTT SCHEMA WITH COMPUTE STATISTICS.

EXEC DBMS_UTILITY.ANALYZE_SCHEMA
('SCOTT', 'COMPUTE')

is same.

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.