What is the better way of generating Oracle database STATS

Posted on 2015-02-17
Medium Priority
Last Modified: 2015-03-10
What should be the better way of generating STATS in Oracle 11g rel 2 (
I mean using
1. dbms_stats.gather_database_stats
2. dbms_stats.gather_schema stats
Question by:Oranew
LVL 49

Assisted Solution

PortletPaul earned 400 total points
ID: 40615524
All I can offer toward that question is this Oracle white paper:

Understanding Optimizer Statistics in Oracle Database 11g from the "optimizer team blog"
LVL 23

Assisted Solution

David earned 800 total points
ID: 40615550
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.

Author Comment

ID: 40615593
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
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 23

Assisted Solution

David earned 800 total points
ID: 40615653
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?
LVL 36

Accepted Solution

Mark Geerlings earned 800 total points
ID: 40616750
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.

Author Closing Comment

ID: 40657820
Thanks to everyone who tried to help and for your valuable time.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question