Q
Problem solve Get help with specific problems with your technologies, process and projects.

How does DBMS_STATS work?

How does DBMS_STATS work? I want to know what really happens when collecting statistics using DBMS_STATS. How does Oracle get the number of rows from a table?

How does DBMS_STATS work? I want to know what really happens when collecting statistics using DBMS_STATS. How does Oracle get the number of rows from a table?
Basically, to get the number of rows and other information that DBMS_STATS calculates, Oracle has to read that table. So if your table is 1 million rows, then DBMS_STATS will read all 1 million rows of data. This can be quite time-consuming for larger objects. For this, you should ESTIMATE rather than COMPUTE statistics on the object. With ESTIMATE, Oracle does not read the entire table, but a sample of the table. You can define how much by telling Oracle to use 10% or 20%, or whatever value, of the table. Or, you can let Oracle decide how much of the table to use by specifying the AUTO_SAMPLE_SIZE option as follows:
EXEC DBMS_STATS.GATHER_TABLE_STATS( 
ownname=>'SCOTT', tabname=>'EMP', 
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
This was last published in May 2006

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close