CODEWITHSUNDEEP
oracle-database
squid
squid

Reputation: 11

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?

Upvotes: 1

Views: 156

Answers (3)

Peter G.
Peter G.

Reputation: 15144

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

Upvotes: 2

Michael Broughton
Michael Broughton

Reputation: 4055

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.

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132680

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.

Upvotes: 5

Related Questions