Solved

Problem with dbms_stats.gather_schema_stats execution.

Posted on 2015-01-22
11
318 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
  • 6
  • 5
11 Comments
 
LVL 76

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 76

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
 
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
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 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 76

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 76

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 76

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

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

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…
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 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.
This video shows how to recover a database from a user managed backup

759 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

20 Experts available now in Live!

Get 1:1 Help Now