Introduction Performance improvement is an iterative process. Removing the first bottleneck might not lead to performance improvement immediately, because another bottleneck might be revealed that has an even greater performance impact on the system. The Oracle performance method can be applied until performance goals are met or deemed impractical.
Scope of Oracle Tuning
Oracle tuning involves the following steps, with each step getting more specific and targeted:
Server & network tuning—This is always the first step, as not amount of tuning will help a poor server environment.
Instance tuning—Tuning the Oracle SGA is the next step, and all of the Oracle initialization parameters must be reviewed to ensure that the database has been properly configured for it’s workload. In some cases, a database may have a bi-modal workload (online vs. batch) and the instance parms are adjusted as-needed during this step.
Object tuning—This step of performance tuning looks at the setting for Oracle tables and indexes. Table and index settings such as PCTFREE, PCTUSED, and FREELISTS can have a dramatic impact on Oracle performance.
SQL tuning—This is last step in tuning, and the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. If you have carefully optimized the workload as a whole from step 2, there you will only need to tune “outlier” SQL statements. Within this step, there are sub-steps:
Remove unnecessary large-table full-table scans—In this tuning step you evaluate the SQL based on the number of rows returned by the query. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans.
Cache small-table full-table scans—In this step we ensure that a dedicated data buffer is available for the rows. Verify optimal index usage—This step is critical because you may have “missing” indexes in your database, causing excessive I/O. Materialize your aggregations and summaries for static tables – One features of the Oracle SQLAccess advisor is recommendations for new indexes and suggestions for materialized views.
Methods of Tuning
Performance tuning is driven by identifying and eliminating bottlenecks in the database, and by developing efficient SQL statements. Database tuning is performed in two phases:
Proactive Tuning
Reactive Tuning
In the proactive tuning phase, you need to perform tuning tasks as part of your daily database maintenance routine, such as reviewing ADDM analysis and findings, monitoring the real-time performance of the database, and responding to alerts.
In the reactive tuning phase, you need to respond to issues reported by the users, such as performance problems that may occur for only a short duration of time, or performance degradation to the database over time.
SQL tuning is an iterative process to identify, tune, and improve the efficiency of high-load SQL statements.
Performance Tuning Steps
Performing pre-tuning preparations
Tuning the database proactively on a regular basis
Tuning the database reactively when performance problems are reported by the users
Identifying, tuning, and optimizing high-load SQL statements To improve the performance of your database, you will need to apply these principles iteratively.
Pre-Tuning Preparations
Get feedback from users.
Determine the scope of the performance project and subsequent performance goals, and determine performance goals for the future. This process is key for future capacity planning.
Sanity-check the operating systems of all systems involved with user performance.
Check for hardware or operating system resources that are fully utilized. List any overused resources as possible symptoms for later analysis. In addition, ensure that all hardware is functioning properly.
Ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL to enable the automatic performance tuning features of Oracle Database, including the AWR and ADDM.
The default setting for this parameter is TYPICAL.
Tuning Proactively
Review the ADDM findings (Automatic Database Diagnostics Monitor)
ADDM automatically detects and reports on performance problems with the database, including most of the “Common Performance Problems Found in Oracle Databases”. The results are displayed as ADDM findings on the Database Home page in Enterprise Manager. Reviewing these findings enables you to quickly identify the performance problems that require your attention.
Implement the ADDM recommendations
ADDM automatically provides a list of recommendations for reducing the impact of the performance problem with each ADDM finding. Implementing a recommendation applies the suggested changes to improve the database performance.
Analyze the database schema objects to
1) Collect or delete statistics about an index or index partition, table or table partition, index- organized
table, cluster, or scalar object attribute.
2) Validate the structure of an index or index partition, table or table partition, index-organized table, cluster,
or object reference (REF).
3) Identify migrated and chained rows of a table or cluster.
Recompile all the invalid objects in the database.
Monitor performance problems with the database in real time
The Database Performance page in Oracle Enterprise Manager enables you to identify and respond to real-time performance problems. By drilling down to the appropriate pages, you can identify and resolve performance problems with the database in real time, without having to wait until the next ADDM analysis.
Respond to performance-related alerts
The Database Home page in Oracle Enterprise Manager enables you to view performance-related alerts generated by the system. Typically, these alerts reveal performance problems that, once resolved, will improve the performance of your database.
Validate that the changes made have produced the desired effect, and verify if the perception of performance to the users has improved.
Repeat these steps until your performance goals are met or become impossible to achieve due to other constraints.
Tuning the Database Reactively
Run ADDM manually to diagnose current and historical database performance when performance problems are reported by the users.
This is useful if you want to run ADDM before the next ADDM analysis to analyze current database performance, or to analyze historical database performance when you were not proactively monitoring the system.
Resolve transient performance problems
The Active Session History (ASH) reports enable you to analyze transient performance problems with the database that are short-lived and do not appear in the ADDM analysis.
Resolve performance degradation over time
The Automatic Workload Repository (AWR) Compare Periods reports enable you to compare database performance between two periods of time, and resolve performance degradation that may happen from one time period to another. Validate that the changes made have produced the desired effect, and verify if the perception of performance to the users has improved.
Repeat these steps until your performance goals are met or become impossible to achieve due to other constraints.
Tuning SQL Statements
Identify high-load SQL statements
Use the ADDM findings and the Top SQL to identify high-load SQL statements that are causing the greatest contention.
Tune high-load SQL statements
You can improve the efficiency of high-load SQL statements by tuning them using the SQL Tuning Advisor.
Optimize data access paths
You can optimize the performance of data access paths by creating the proper set of materialized views, materialized view logs, and indexes for a given workload by using the SQL Access Advisor.
Repeat these steps until all high-load SQL statements are tuned for greatest efficiency.
Tuning Tools
Oracle Database 10g Enterprise Edition Oracle Database 10g Enterprise Edition offers enterprise-class performance, scalability and reliability on clustered and single-server configurations. It includes many performance features that are used in this guide.
Oracle Enterprise Manager The primary tool to manage your database is Oracle Enterprise Manager, a Web-based interface. After you install the Oracle software, create or upgrade a database, and configure the network, you can use Oracle Enterprise Manager to manage your database. In addition, Oracle Enterprise Manager provides an interface for performance advisors and for Oracle utilities, such as SQL*Loader and Recovery Manager.
Oracle Diagnostics Pack Oracle Diagnostics Pack offers a complete, cost-effective, and easy-to-use solution to manage the performance of Oracle Database environments by providing unique features, such as automatic identification of performance bottlenecks, guided problem resolution, and comprehensive system monitoring. Key features of the Oracle Diagnostics Pack that are used in this guide include the Automatic Database Diagnostics Monitor (ADDM) and the Automatic Workload Repository (AWR).
Oracle Database Tuning Pack Oracle Database Tuning Pack automates the entire database application tuning process, thereby significantly lowering database management costs while enhancing performance and reliability. Key features of the Oracle Database Tuning Pack that are used in this guide include the SQL Tuning Advisor and the SQL Access Advisor.
Tuning Areas
Sizing the Shared Pool
Sizing the Buffer Cache
Sizing Other SGA Structures
Database Configuration and I/O Issues
Optimizing Sort Operations
Diagnosing Contention for Latches
Tuning Rollback Segments
Monitoring and Detecting Lock Contention
Tuning the Oracle Shared Server
Application Tuning Using Oracle Blocks Efficiently
SQL Statement Tuning
Tuning the Operating System and Using Resource Manager