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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close