• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

What is the better way of generating Oracle database STATS

What should be the better way of generating STATS in Oracle 11g rel 2 (11.2.0.3)
I mean using
1. dbms_stats.gather_database_stats
or
2. dbms_stats.gather_schema stats
0
Oranew
Asked:
Oranew
4 Solutions
 
PortletPaulCommented:
All I can offer toward that question is this Oracle white paper:

Understanding Optimizer Statistics in Oracle Database 11g from the "optimizer team blog"
0
 
DavidSenior Oracle Database AdministratorCommented:
Great question, but it's so broad it kind of deserves the "it depends" approach.  The Database stats imply the entire database, including overhead accounts.  A schema is just one account that owns tables and similar objects.  So when asked "how to cross a road" it's going to take some common sense about the road....

Generally speaking, once the database is correctly sized and tuned, there's not much call to redo the database stats.  For that matter, redoing the schema stats is dependent upon how much of the data is changing.  Routine inserts and deletes are one thing, massive truncates and nightly loads is another.

You are far more likely to see performance affected by one or more of the following:
1.  Hardware and network configuration; load
2.  O/S configuration
3.  Database SGA tuning, or auto-tuning of cache
4.  Poorly written SQL queries
5.  Actual database load
6.  Poor sizing of redo logs

Hope this helps a bit.
0
 
OranewAuthor Commented:
I asked this question as I am doing database stats daily and it finishes in less than 10 minutes.
There is a query which usually takes 2 minutes and uses only 4 tables but I ran
Stats on these tables only and the query finished in 4 seconds. I then changed the stats to run on schema and I am also getting the result in 4 to 6 seconds
This is one weeks result.
I like to check with experts what full database is missing in the concept
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
DavidSenior Oracle Database AdministratorCommented:
If I understand you, your concern is that both the database stats and schema stats result in approximately the same way for a particular query.

Until the size and composition of the table(s) changes dramatically, the (count of) table rows are now known to the optimizer.  IOW it remembers that the statement will process about the same number of rows each time, and how they are indexed.  Four seconds one day, four to six the next.  Since you mentioned just four tables, I might presume they're comparatively small -- thousands of rows rather than millions.

For any particular statement, such as a query,  Oracle can provide additional tools to explore how the statement can be parsed by the optimizer -- see explain plan.  For example, a statement driven by an indexed column will run faster than a full table scan, in general.

So, once the stats are "current", and the data is not rapidly changing, either gather stats technique will not significantly change things.

Many many sources for more info, such as http://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics.php.  

Am I getting your point?
0
 
Mark GeerlingsDatabase AdministratorCommented:
One area where we have seen stats on particular tables to be very helpful (after doing full database or full schema stats on a weekly basis) is when queries try to retrieve particular rows based on indexed values that are outside of the range of values that existed when the more-general stats were last calculated.  Even if the number of rows added to the table is a small percentage of the total rows in the table (that might not trigger automatic stats re-calculation) we have seen cases of individual queries becoming very slow until we gather stats (daily or more often) on the specific tables in the queries.
0
 
OranewAuthor Commented:
Thanks to everyone who tried to help and for your valuable time.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now