Solved

What is the better way of generating Oracle database STATS

Posted on 2015-02-17
6
206 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 35

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 83
Oracle regular expression 6 47
Create table from select - oracle 6 22
How to free up undo space? 3 29
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

13 Experts available now in Live!

Get 1:1 Help Now