Solved

What is the better way of generating Oracle database STATS

Posted on 2015-02-17
6
202 Views
Last Modified: 2015-03-10
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
Comment
Question by:Oranew
6 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 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"
0
 
LVL 23

Assisted Solution

by:David
David earned 200 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.
0
 

Author Comment

by:Oranew
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Assisted Solution

by:David
David earned 200 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?
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 200 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.
0
 

Author Closing Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now