Database performances

What is the best to 开发者_开发知识库do among the following commands ?

1. exec dbms_stats.gather_table_stats

2. dbms_stats.gather_schema_stats

which one improves the database performances?

They both improve database performance (or rather, they both gather statistics that will be used by the oprtimiser and may improve performance). GATHER_SCHEMA_STATS gathers statistics for all objects in a schema. GATHER_TABLE_STATS does it for just one table. You can read all about them in the documentation.

both. neither. depends.

What do you mean by "the database performances"? Gathering stats on existing tables and indexes MAY help if the stats are out of date. But they aren't a magic bullet that will replace adding helpful indexes for use by problem queries.

updating stats on one table may be all that you need for your problem. Updating stats on ALL indexed columns might be in order. Or these may not be the issues that you are dealing with from a performance perspective. Good indexes won't help baddly written queries.

You need to figure out WHAT SPECIFICALLY is slow before you can figure out how to fix it. For sure there is not one accelerator pedal for the entire database - no matter how we all might wish there was.

Oracle does stats gathering anyway regularly since 10g. Have you noticed the standard gathering is too rare or not detailed enough in your case? The default of estimate_percent < 100 can lead to bad plans in 10g.

1. Have a look at your application, if performance is ok, you're done. If not ...
2. Focus on the tasks your application does to slow and break down the time spent into the major contributors. Try to optimize the major contributors. Stop when performance is acceptable.

The statistics gathering might one of the optimizations you will apply but we cannot know in advance. Before you augment Oracle's default statistics gathering you would look for sub-optimal query plans that would not be chosen with manually gathered statistics.

This excellent paper by Cary Millsap might help you: http://method-r.com/downloads/doc_details/44-thinking-clearly-about-performance