Home » RDBMS Server » Server Administration » ANALYZE_SCHEMA questions
ANALYZE_SCHEMA questions [message #180161] Fri, 30 June 2006 07:53 Go to next message
aidi-h
Messages: 45
Registered: November 2005
Member
My database is currently going slow at particular times and someone suggested that i analyze the tables.

I was going to run the following.

execute dbms_stats.analyze_schema('<schema name>', 'ESTIMATE');

questions are
1) Is this command correct.
2) Can this be run if the datase and schema are being used.
3) for an average size system, how long does this usually take.
4) if a system is going slow, will this command increase the efficiency/speed of the system.
5) Are there any considerations to bear in mind when I run this.

Report message to a moderator

Re: ANALYZE_SCHEMA questions [message #180178 is a reply to message #180161] Fri, 30 June 2006 08:45 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
use GATHER_SCHEMA_STATS. Use cascade option to collect stats on indexes.
Use a a proper bucket size.
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1003995
http://www.orafaq.com/forum/t/51267/0/

>>3) for an average size system, how long does this usually take.
Define average.
>>4) if a system is going slow, will this command increase the efficiency/speed of the system.
Please read the documentation and understand what the analyze does.
This procedure update the Statistics on tables/indexes so that CBO can do its job better.
CBO chooses an execution plan based on available statistics.
Read about CBO.
Search the forum/google.

Report message to a moderator

Previous Topic: Can users still stay on the system if I am adding redo log files?
Next Topic: Redo log size
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 10:24:53 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.