How statistics are collected in oracle? and how are they used?

Posted on 2011-04-27
Last Modified: 2012-06-21
I believe oracle 11g uses statistics for optimization of queries. And statistics are collected by
invoking the package dbms_stats.

My question is, If the statistics are not collected by dbms_stats, will oracle collect
any statistics by its own after a period of time?

What are the statistics data used by the optimizer?

What is the difference between table_stats and index_stats?
Question by:sakthikumar
    LVL 76

    Accepted Solution

    You should ask one question per question.

    In a nutshell the stats allow the cost based optimiser pick the most efficient execution path.

    I suggest you spend some time in the performance tuning docs.  It goes into all this.

    Oracle 'may' collect table stats in the standard maintenance window.  The docs talk about the window and what stats are collected and why.

    Table and index stats are just that; stats against the repsective objects.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    I would start at the docs homepage:

    There is a link for 2 Day + Performance Tuning Guide

    Then the booklist:  Oracle® Database Performance Tuning Guide
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    The stats guide then talks about: 13.2 Managing Automatic Optimizer Statistics Collection

    Expert Comment

    when the creation of database it will asks for the running jobs to collect stats at evryday midnight

    you may disable that option if you want to run the dbms.stats process by your own.
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)

    I believe I already covered the Automatic Maintenance Job in http:#35479707 and provided the doc link that talks about setting/disabling it in http:#a35479737

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

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

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to recover a database from a user managed backup

    737 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