Release 9.0.1
Part Number A86647-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack Release 9.0.1 Part Number A86647-01 |
|
This chapter provides guidelines for collecting and managing Oracle Expert workloads. Topics in this chapter include:
Workload data consists of SQL statements and SQL statistics. Workload data for a Tuning Session is collected from a source workload. One of the following sources can be used to supply workload data for a Tuning Session:
When the source workload is collected by the Oracle Expert Tuning Session, it goes through a filtering process. This filtering process removes all SQL statements that are not required by the Tuning Session, leaving only those SQL statements that are related to the database tables to be tuned by the Tuning Session. The tables to be tuned are selected in one of two ways:
The set of SQL statements remaining after this filtering process is referred to as the Tuning Session Workload.
Obtaining a good set of SQL statements for the Tuning Session Workload is critical. If the source workload contains a representative set of the SQL statements that are typically executed against the database tables being tuned, then the Tuning Session Workload will provide Oracle Expert with a good basis for tuning. Therefore, it is very important that the Oracle Expert user ensure that the source workload is collected from the database when the relevant SQL is in use by the database application.
Any of the sources referred to earlier can supply a representative set of SQL statements for a Tuning Session depending on the tuning circumstances. For example:
The most comprehensive source workload is the SQL History. The SQL History allows the user to collect SQL workload over time, when the user knows that relevant SQL is available. The SQL History can be created, refreshed or replaced from any of the other source workloads: SQL cache, Oracle Trace collections or XDL files.
You can use Oracle Trace to collect SQL workload data. Oracle Trace collects data about SQL statements executing against a database in real time (while the statements are executing). Oracle Trace allows you to:
If you are unsure why performance deteriorates at different times from day to day, Oracle Trace may be able to help. You can use Oracle Trace to collect data about all the SQL statements executing against a database during periods of poor performance.
You can use Oracle Trace to collect workload data for a single application by running the application during a period when there is no other activity against the database. This may only be practical for batch applications that do not require any user input during execution.
You can run an interactive application requiring user input at a different time than usual to allow Oracle Trace to collect workload data for only that application. If you do, you need to consider whether the data collected by Oracle Trace could be appropriately included as part of a representative workload. A representative workload includes SQL statements that execute against a database during a period for which you want to improve performance. If you collect workload data while an application is running under artificial conditions, the workload may contain different data than a workload collected while the application is run under normal conditions.
You control the duration of an Oracle Trace collection. If you want to obtain workload data for a 15-minute period of poor performance, you can do this by stopping the collection immediately after the poor performance interval ends.
You can restrict an Oracle Trace collection to one or more database sessions.
Oracle Trace collects data about the sequence in which SQL statements executed. When a given SQL statement executes against a database, it does so within a transaction. For each SQL statement, Oracle Trace identifies the transaction within which the statement executed. The order in which statements execute can affect how quickly they execute. When provided with the sequence of statements within a transaction, Oracle Expert can use this information to generate more effective tuning recommendations.
See the Oracle Enterprise Manager Oracle Trace User's Guide for more information about collecting data using Oracle Trace.
After an Oracle Trace collection is completed, use the Oracle Trace format function to format the raw Oracle Trace data and store it in an Oracle database (hereafter referred to as an Oracle Trace database).
You can provide Oracle Expert with workload data that has been collected by Oracle Trace by collecting the Oracle Trace data directly into Oracle Expert from the Oracle Trace database. Use the Workload Collect Options dialog box to import the workload data directly from the Oracle Trace database into Oracle Expert.
An instance's SQL cache contains the SQL statements that are currently the most frequently executed against the instance. Therefore, if you want to collect the most frequently executed SQL statements from a particular application or group of applications, you can collect this data from the SQL cache while these applications are executing. To collect SQL statements from the SQL cache of one or more instances, choose the Current SQL Cache option on the Workload Collect Options dialog box.
You can provide Oracle Expert with workload data that has been collected for another tuning session by exporting that session's workload data to an .XDL file. You then import the .XDL file into your current tuning session. Use the Workload Collect Options dialog box to import the workload data directly from an .XDL file into Oracle Expert.
If a SQL History exists, you can use the SQL History as a source for the tuning session workload.
The SQL History is shared between SQL Analyze, Oracle Expert, and the Index Tuning wizard. The SQL History is intended to contain a complete set of SQL for the database environment. A SQL History will ensure that consistent index tuning recommendations are made across the three tools.
One of the major benefits of Oracle Expert is that you can use it to optimize the performance of your business's most important applications by tuning the indexes on high impact or frequently accessed tables. Oracle Expert uses the importance value for each element of the tuning session workload to determine the most important applications, then generates recommendations to optimize their performance. An element can be either an application or a SQL request. Importance values can be specified at either level.
Oracle Expert computes an element's relative importance value using the element's importance value and frequency value. When application and request data is collected by the workload source, Oracle Expert automatically assigns these elements an importance value of 5000. Unless you believe that all the elements in your workload are equally important, you may want to consider changing their importance values.
Oracle Expert computes the relative importance of individual workload elements based on the Primary workload emphasis value. The emphasis value is based on either a user-supplied importance, a calculated frequency of executions or the physical I/O count. By default, Oracle Expert gives more weight to I/O. This can be changed by modifying the Primary workload emphasis and Secondary workload emphasis user rules.
To change the default method of computing relative frequency, change the value of the Workload emphasis rule. This rule can be modified at the Workload Application level on the Review page. To change the value of the Workload emphasis rule for an Application and its children, select a workload Application, choose Edit=>Modify, select the Rules tab and the Workload tab of the Edit dialog box, and make the desired change.
For every element in every category of the workload hierarchy, you can provide an importance value between 1 and 9999, with 1 being the lowest importance value.
Oracle Expert ranks the elements in the highest level of the workload hierarchy (Applications) to be the most important, and elements in each of the lower categories to be proportionately less important. What this means in practice is that Oracle Expert largely determines the relative importance of a given application or request element by taking into account the relative importance of the element's parent in higher levels of the workload hierarchy.
The importance value for an element in one of the lower three categories does not entirely determine its relative importance. Instead, much of an element's relative importance is determined by the relative importance of its parent in the workload hierarchy. Any of the Requests that are part of the most important Application in a workload will have a higher relative importance than any of the Requests for less important Applications.
You need to know which SQL statements are part of which applications to get the best performance for your most important applications.
What if you change the default behavior so that Oracle Expert gives more weight to the importance of an element when computing its relative importance? Oracle Expert follows the same basic principles in computing the relative importance of elements. That is, the elements in the highest workload category (Applications) are still considered to be the most important and elements in each of the lower categories are considered to be proportionately less important. Therefore, the relative importance of a given workload element is still largely determined by the relative importance of the element's parents in higher levels of the workload hierarchy.
The difference is that when frequency is the dominant factor in determining relative importance, Oracle Expert deems the Application with the highest frequency value to be the most important element in your workload. Oracle Expert is also likely to give higher relative importance values to Requests that are part of that Application than to Requests that are part of Applications with lower frequency values.
To modify the importance value or frequency value for a workload element, select the element on the View/Edit page, choose Edit=>Modify, and enter a new value on the Attributes page.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|