Solved

Problem with dbms_stats.gather_schema_stats execution.

Posted on 2015-01-22
11
350 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 500 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

756 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