Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

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

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?
  • 4
3 Solutions
slightwv (䄆 Netminder) Commented:
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.
slightwv (䄆 Netminder) Commented:
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
slightwv (䄆 Netminder) Commented:
The stats guide then talks about: 13.2 Managing Automatic Optimizer Statistics Collection

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.
slightwv (䄆 Netminder) Commented:

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now