Gather table stats sample size

Posted on 2011-10-10
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

    Hi Team,
                Any thoughts on the above question??

    Thanks and Regards,
    Rajesh K.
    LVL 73

    Accepted Solution

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    730 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

    18 Experts available now in Live!

    Get 1:1 Help Now