Additional Tuning Tools


In this final section, I ll mention some other tools you may use for tuning, but whose use is beyond the scope of this book. Again, you can read the Oracle10 g Database Performance Tuning Guide for full details.

Statspack Package

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow you to collect and view performance data, such as high-resource SQL statements and database load. The most current instructions and information on installing and using the Statspack package are contained in the file spdoc.txt installed with the database. You ll find this file in the ORACLE_HOME\rdbms \doc directory.

Oracle Enterprise Manager Diagnostics Pack

The Oracle Enterprise Manager Diagnostics Pack captures operating-system, middle- tier , and application-performance data, as well as database-performance data. The Diagnostics Pack analyzes this performance data and displays the results graphically. You can also configure the Diagnostics Pack to alert you immediately to performance problems via e-mail or page. Oracle Enterprise Manager also includes software guides to help you resolve performance problems.

Automatic Database Diagnostic Monitoring

Automatic database diagnostic monitoring allows you to monitor the database for performance problems by analyzing system performance over a long period of time. You can view the performance information in Oracle Enterprise Manager. When you find performance problems, you can use the Oracle Advisors feature to further analyze and correct the problems. You ll learn about the SQL Tuning Advisor and SQLAccess Advisor next .

SQL Tuning Advisor

The SQL Tuning Advisor allows you to tune a SQL statement using the following items:

  • The text of the statement

  • The SQL identifier of the statement (obtained from the V$SQL_PLAN view, which is one of the views available to the DBA)

  • The range of snapshot identifiers

  • The SQL Tuning Set name

A SQL Tuning Set is a set of SQL statements with their associated execution plan and execution statistics. SQL Tuning Sets are analyzed to generate SQL Profiles that help the optimizer to choose the optimal execution plan. SQL Profiles contain collections of information that enable optimization of the execution plan.

SQLAccess Advisor

The SQLAccess Advisor provides you with performance advice on materialized views, indexes, and materialized view logs. The SQLAccess Advisor examines space usage and query performance and recommends the most cost-effective configuration of new and existing materialized views and indexes.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net