Gather table stats sample size

Posted on 2011-10-10
Medium Priority
Last Modified: 2013-01-16
Hi Team,
               We need to come up with a sample size for running DBMS_Stats.Gather tablestats procedure. The tables on which stats need to be collected are very large (Some of the tables are more than 25 G with the largest table being 95 G and has 600 million rows). How to come to a reasonable sample size for estimate. Would you recommend Auto sample size. We use a sample size of 4 for large tables as a general rule (in this environment). Completion time of the stats gathering is a factor in this calculation.
Thanks and Regards,
Rajesh K.
Question by:rajkeshav

Author Comment

ID: 36947665
Hi Team,
            Any thoughts on the above question??

Thanks and Regards,
Rajesh K.
LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 36948162
We generally use auto unless it doesn't seem to do a good enough job, which generally only happens if there is a lot of skew.

Unfortunately, only you can tell us if that's the case, and if so, you may have to play around with it.

How many indexes are on the table?  The more indexes, the longer it will take to analyze.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 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