Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with dbms_stats.gather_schema_stats execution.

Posted on 2015-01-22
11
Medium Priority
?
383 Views
Last Modified: 2015-01-23
Hi,

  I have a job running for more than 2 weeks and it does not finish.   It is the first time i run it in our production database.  

  It was created with the sys user and the database size is 2TB.

  When it runs in the QA and Dev databases it takes like 4 days.   QA and DEV are 1.6 TB, they do not have updated production data.

  A) is there something wrong with my commands ?
  B) is there a way split it to specific tables or to make it run faster ?

begin
dbms_stats.gather_schema_stats(OWNNAME=>'APP_USER_SCHEMA',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE,degree=>DBMS_STATS.AUTO_DEGREE);
end;
0
Comment
Question by:joe_echavarria
[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
  • 5
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40564547
>>,estimate_percent=>100

Very large databases is why the estimate percent is in there.  for databases that are to large for COMPLETE statistic gathering.

>>way split it to specific tables or to make it run faster ?

Move to gather_table_stats and do them individually?
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 40564557
@slightwv

  I do not understand your suggestions.

  I should not use

   >>,estimate_percent=>100

  or change it to something ?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40564567
The estimate is there to keep from doing complete stats collection.

We cannot say what you should set it to because we do not know your database.

You need to know what percentage of rows would return a good representation of ALL the rows in the table.

For some tables, this may be 100.  For others it may be 10.


From the docs:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68577


estimate_percent
 
Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics.


Take a look at this blog:
https://blogs.oracle.com/optimizer/entry/improvement_of_auto_sampling_statistics_gathering_feature_in_oracle_database_11g

Excerpt from the blog:
It is not always easy for users to pick the appropriate sampling percentage. If the specified sampling percentage is too high, it can take longer to gather statistics. On the contray, if the data is skewed and the specified sampling percentage is too low, the resulting statistics can be inaccurate. For this reason, Oracle introduced the AUTO value for the estimate_percent parameter.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:joe_echavarria
ID: 40564732
Can this work on 10g ?
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 40564773
I just confirmed that parameter is also in 10g.

I will change it and test it again.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 40566270
Well, it worked fine.  It took 8.5 hours to finish.  
 
For checking if all the tables and indexes have been analyzed can i use the query below ?

SELECT distinct last_analyzed FROM ALL_indexes/ALL_TABLES  where owner='xxxxxx' order by last_analyzed desc
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40566289
Looks good to me but I'm not sure what the distinct is supposed to do.  Object names are distinct for each owner...
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40566294
>>Well, it worked fine.  It took 8.5 hours to finish.  

Remember:
Just because a table/index was analyzed doesn't mean it actually helps the system.  

It could really hurt a system.

If your estimate was too small and it doesn't represent what is actually in the table, the optimizer could make some bad choices and performance can suffer.

Up to date statistics are a good thing but they need to be accurate statistics.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 40566312
Well, the estimate i used  with the clause DBMS_STATS.AUTO_SAMPLE_SIZE and Oracle made the choice.  

Does it makes the right choice, ?, i hope so.

Setting up accurate statistics , isn't about using the parameter DBMS_STATS.AUTO_SAMPLE_SIZE ?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40566324
>>Does it makes the right choice, ?, i hope so.

It is probably going to be fine.  You just need to keep an eye on possible bad performing queries and may need to adjust things for specific tables.

>>Setting up accurate statistics , isn't about using the parameter  DBMS_STATS.AUTO_SAMPLE_SIZE ?

It's about knowing your database and how the applications use it.  'Automatic' is good but in the end it is still a educated guess.  It can guess wrong from time to time.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 40566328
Thanks for you help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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

705 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