Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

What is the better way of generating Oracle database STATS

Posted on 2015-02-17
6
Medium Priority
?
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 49

Assisted Solution

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

Assisted Solution

by:David
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.
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 23

Assisted Solution

by:David
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?
0
 
LVL 35

Accepted Solution

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

Author Closing Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

609 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