|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
Oracle Expert is a software tool for optimizing the performance of your database environment. Oracle Expert assists with the initial configuration of a database and with the collection and evaluation of performance characteristics of existing databases.
Oracle Expert automates the process of collecting and analyzing performance tuning data and provides expert database tuning recommendations. Additionally, Oracle Expert generates scripts that assist with the implementation of tuning recommendations.
The topics in this chapter include:
Oracle Expert provides many advantages. Oracle Expert:
Oracle Expert also serves as:
The database tuning process consists of tasks such as:
Even though you may realize that you have a poorly tuned database environment, you may not be able to resolve the problem, because:
To resolve tuning issues, there are at least two important requirements:
A significant amount of a database expert's time is spent collecting and sifting through vast quantities of information. Collecting information for a normal database tuning session requires knowledge of many tools.
Also, the effectiveness of the database tuning effort can vary a great deal depending on the expertise of the person doing the job. To further complicate the issue, with database tuning there often is no exact solution to a specific performance problem.
The tuning recommendations produced by Oracle Expert are both consistent and accurate. Oracle Expert can sift through volumes of tuning information without missing relevant symptoms. It automates many of the repetitive and time-consuming aspects of database tuning, thus reducing the time required to get meaningful performance improvements. Finally, Oracle Expert manages the history of the collected information over time.
Tuning an Oracle database can involve tuning the application, the instance, and the space usage in a database.
Whether you are writing new SQL statements or tuning problematic statements in an existing application, application tuning can improve CPU response time, reduce disk I/O, and reduce memory resources. The methodology for tuning SQL involves identifying the statements that consume the most resources and then tuning these statements to use fewer resources. In general, a small number of SQL statements are responsible for most of the activity that occurs in the database. Rather than trying to completely understand an application, focus your tuning efforts on those statements or tables where the benefit of tuning will exceed the cost.
Approaches to SQL statement tuning include: determining which indexes should exist on a table, and identifying existing indexes that should be rebuilt to improve performance. You should also ensure SQL is shared effectively. Ineffective SQL sharing can result in unnecessary reparsing which requires more CPU usage.
Instance tuning can be used to solve a variety of problems such as inefficient memory allocation and I/O problems. Instance tuning involves tuning areas such as the redo log buffer, the shared pool, the buffer cache, and the sort areas. Instance tuning also tunes the log writer (LGWR) and database writer (DBWR) background processes.
Effective space management will improve database availability and reduce performance problems resulting from poor space utilization. When an object such as a table is created, space in the database is allocated for the data. Therefore, correct placement and sizing of these objects is essential.
Through the available tuning scopes, Oracle Expert supports the above tuning areas by checking for the following:
|Type of Tuning||Tuning Scope|
Optimal Data Access
Instance Parameter Optimizations
Appropriate Space Management
Oracle Expert is as flexible as you are. Oracle Expert can help you achieve any and all of the following:
Deals with the optimization of all aspects of the database environment and maintains the performance of the database over time.
Delves into the resolution of a known performance problem. This resolution occurs as you choose the appropriate tuning categories and focus on the particular problem.
Oracle Expert can optimize a newly created database using additional information supplied about the workload, physical memory, and expected transaction volumes.
Oracle Expert provides you with a sample tuning session called "Personnel session." Personnel session is a tuning session against a non-existent Personnel database. It contains example data used by Oracle Expert, such as database, instance, schema, environment, and SQL workload information.
The sample tuning session does not use a real instance. Therefore performing a collection from the sample simply reloads the data from the sample file.
With this sample tuning session, you can experiment with the View/Edit page, analysis, reviewing recommendations, and generating script files.
To load this sample, choose Help=>Load Sample from the Oracle Expert menu bar. The Personnel tuning session displays in the tree list.
If you want to experiment with collecting data for this tuning session, you must use the XPPSO.XDL file in the $ORACLE_HOME\SYSMAN\EXPERT\SAMPLE directory.
When setting collect options for database, instance, schema, or workload, choose to collect from File, and select XPPSO.XDL file as the source. This file is located in the $ORACLE_HOME\SYSMAN\EXPERT\SAMPLE directory. The XPPSO.XDL file contains the information needed for all the collection classes.