We have an Oracle 9i database and were having performance problems with tables having 1.5 million rows. Using explain plan, we analyzed our queries and were surprised to see that even though indexes were defined, the optimizer refused to use them until we ran analyze table and indexes. Is this how it should be, or have we missed something when installing the product? Do we have to analyze each table where we want to use our indexes? Do we have to run analyze table from time to time to recalculate statistics, or if once initiated it will continuously update the relevant statistics? In which documentation can I find further information on the above?
The reason why the optimizer didn't recognize your indexes properly until after you analyzed is because the cost-based optimizer relies on current statistics in order to make valid decisions. By not having current statistics, the optimizer may have been using old statistics that did not know there were 1.5 million rows... or if you had not ever calculated statistics, then the optimizer had nothing at all to go on. For example, if the optimizer thought you had 100 rows, it would likely not use an index because it would think that 100 rows occupying only 1 or 2 blocks could be retrieved more quickly by just doing a full table scan. So, if you really had 1.5 million rows and it decided to do a full table scan based on the invalid statistics, your performance would suffer.
Keeping current statistics is highly important for the cost-based optimizer to be able to make good decisions. There isn't any "automatic" statistics gathering per se... although you can write a procedure that can be scheduled to run routinely which uses the DBMS_STATS package to gather statistics without intervention.
To get more information on the optimizer and how it works see the Oracle9i Database Performance Tuning Guide and Reference (http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96533/toc.htm) and for more info on how to gather statistics using the DBMS_STATS package see the Oracle9i Supplied PL/SQL Packages and Types Reference (http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96612/toc.htm).
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.