An Oracle Tune-Up: Tuning Your SQL for Maximum Performance
During this process, Oracle exhaustively looks at all of the data and recalculates all of the statistical values for that table. Although this is likely to yield a very accurate analysis of the table, this process can take a very long time if performed on large tables. The process usually must be performed during a maintenance window because Oracle must lock the table being analyzed, which would hold up any pending transactions. This problem is now somewhat addressed by the implementation of Oracle RAC, a system of clustering databases. Oracle RAC allows the same database to be clusterd on multiple nodes, so maintenance can be done on one node while another is still available to applications. If Oracle RAC is not an option, however, there is another way to compute statistics that takes less time:
Analyze table Employees estimate statistics sample 1000 rows;
Analyze table Employees estimate statistics sample 10 percent;
This command does recompute statistics for the table based on actual data, but only on a fraction of it. In the first example, Oracle will sample 1000 rows of data; in the second, it will sample 10% of the total data in the table. Thus, even for a large table, the time to complete the analysis can be far less than the full analysis performed by totally recomputing all statistics.
Later versions of Oracle have implemented special packages for the purpose of analyzing databases, schemas, and tables. Using these utility packages offers a bit more flexibility than the analyze commands above. To analyze a table and compute statistics, one can use the following command:
exec DBMS_STATS.gather_table_stats('MYSCHEMA', 'Employees');
To estimate statistics for a table, use the following:
Exec DBMS_STATS.gather_table_stats('MYSCHEMA', 'Employees', estimate_percent = 10);
It is also possible to set statistics manually. This might be necessary as a stop-gap measure to fix poorly running queries until a more thorough job of gathering statistics can be done. It might also be useful if a table often fluctuates between a small and large amount of data over a short period of time, such as a log table whose data is purged out every night.
It is especially important to be sure that all tables referenced in a query have statistics available to the optimizer. Having one table with statistics and another without them is a recipe for inconsistent query performance. Oracle may attempt to estimate the statistics on-the-fly when this occurs, resulting in a performance hit. Your database administrator should have measures in place to insure that statistics are recalculated regularly on all tables. However, if it is not possible to do this for all tables in a query, you can totally bypass the cost-based optimizer by specifying the /*+RULE*/ hint. This causes Oracle to revert to a much simpler approach that does not use statistics, and relies solely on the primary keys and indexes available on the table.
In this article, you have explored how to get an execution plan for a query, maximize query performance with indexes, use hints to tune queries, and generate statistics for Oracle's cost-based optimizer to use when creating query execution plans. Hopefully, these tools will get you well on your way to writing efficient SQL for your applications.
About the Author
David Thurmond is a Sun Certified Developer with over fifteen years of software development experience. He has worked in the agriculture, construction equipment, financial, home improvement and logistics industries.
Page 5 of 5