We help IT Professionals succeed at work.

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

sakthikumar
sakthikumar asked
on
740 Views
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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I would start at the docs homepage:
http://www.oracle.com/pls/db112/homepage

There is a link for 2 Day + Performance Tuning Guide
http://download.oracle.com/docs/cd/E11882_01/server.112/e10822/toc.htm

Then the booklist:  Oracle® Database Performance Tuning Guide
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/toc.htm
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.