Release 1.4.0
A53653_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Oracle Expert User's Guide Release 1.4.0 A53653_01 |
|
This appendix provides guidelines for collecting and managing Oracle Expert workloads. Topics in the appendix include:
All the SQL statements that execute against a database comprise the workload for that database. A complete database workload would include all the ad hoc SQL queries issued by interactive database users and all the SQL statements in database applications that execute against the database. It would be a very difficult task to determine the complete workload for many databases, especially large databases accessed by many users and applications.
Oracle Expert requires workload data for some of its tuning categories, for example, index and SQL tuning. Fortunately, you do not have to provide Oracle Expert with a complete database workload. Oracle Expert can make good tuning recommendations when provided with a representative workload for the database being tuned.
A representative workload includes:
You must determine what constitutes a representative workload for each tuning session that requires workload data. Begin by asking yourself what you want to accomplish during the tuning session. Your tuning goals may include one of the following:
If the same applications run at the same time every day against a database, poor performance may occur during the same time periods from day to day. In this situation, a representative workload would contain data about the applications running during the poor performance periods.
In this situation, a representative workload would contain data about this application.
In this situation, a representative workload would contain data about the applications running when performance is poor.
You should consider whether you need to tune your database during every period when the database is used. For example, suppose that several applications run each night at your company and produce reports that are not used until the next afternoon. Although database performance may be sluggish overnight, this may not matter if no other more important or time-critical applications run at night. If you plan on providing workload data to Oracle Expert to solve other performance problems during your tuning session, you may choose not to include workload data about the nightly applications. In this case, you would reduce the total amount of workload data for Oracle Expert to analyze, so Oracle Expert would generate its recommendations more quickly.
After you have determined the goals of your tuning session, you need to collect a representative workload for Oracle Expert. After analyzing the representative workload you provide, Oracle Expert can make application tuning recommendations.
To obtain workload data from an active database, you can use Oracle Trace to collect it, or Oracle Expert can collect it from the database's SQL cache.
You can use Oracle Trace to collect 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.
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 it collects data about, 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.
By default, Oracle Expert does not save invalid requests (SQL statements) when it imports the workload. A request is invalid if it refers to a table or schema that has not been collected. To save invalid requests when importing Oracle Trace data, enable the Save Invalid Requests option on the Workload Collect Options dialog box.
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. If you have collected multiple instances for a tuning session, the SQL statements from each instance's SQL cache are imported separately. To collect SQL statements from the SQL cache of one or more instances, choose the SQL Cache option on the Workload Collect Options dialog box.
You can provide Oracle Expert with workload data that has been collected from a SQL cache by importing it directly into Oracle Expert. Use the Workload Collect Options dialog box to import the workload data directly from the SQL cache into Oracle Expert.
By default, Oracle Expert does not save invalid requests (SQL statements) when it imports the workload. A request is invalid if it refers to a table or schema that has not been collected. To save invalid requests when importing SQL cache data, enable the Save Invalid Requests option on the Workload Collect Options dialog box.
In some situations, you can reduce the amount of workload data that you import into Oracle Expert. For example, if you are interested in tuning a subset of the schemas and tables in a database, you can collect Schema class data for just those schemas and tables. The workload data collected by Oracle Trace or from a SQL cache can include SQL statements that access any of the schemas and tables in the database. By default, the Save Invalid Requests option is disabled. This means when Oracle Expert imports the workload data, it removes from the workload all the SQL statements that do not access the schemas or tables for which you collected Schema class data. This results in a smaller workload, which Oracle Expert can analyze more quickly.
Data in an Oracle Expert workload is stored in one of four categories in the workload hierarchy. The categories, from top to bottom in the workload hierarchy, are:
Each piece of data in one of the bottom three categories is a child of a workload element in the category above it. For example, each individual SQL statement is stored in the Request category, and is a child of one of the transactions stored in the Transaction category. Similarly, each transaction in the Transaction category is a child of a business unit stored in the Business Unit category. Each business unit in the Business Unit category is a child of an Application at the top of the hierarchy. The workload hierarchy for a tuning session can be viewed on the View/Edit page of the tuning session window.
The top category in the hierarchy is the Application category (this category is called the Workload Application category on the View/Edit page, to show that the elements in this category are the applications that make up the tuning session's workload). A possible example of a workload element in this category would be an application called General Ledger.
The second category is the Business Unit category. A possible example of a workload element at this level would be a program module called Accounts Receivable, which would be a component of the General Ledger application in the Application category.
The third category is the Transaction category. A possible example of a Transaction category element would be a transaction called Deposit Payment that is part of the Accounts Receivable program module in the Business Unit category.
The bottom category is the Requests category. Possible examples of Requests category elements would be the SQL statements that make up the Deposit Payment transaction in the Transaction category.
The following example shows the elements that could appear in a workload hierarchy. This example shows the workload elements that might exist for a single application. If you collect workload data for several applications, there could be many more elements in each workload category.
Workload Category Category Element Application General Ledger Business Unit Accounts Receivable Transaction Deposit Payment Request SELECT FROM... INSERT INTO...
Oracle Expert assigns names to individual elements in the workload categories when it collects workload data.
As mentioned previously, Oracle Trace can collect workload data for several applications or users at the same time during a single Oracle Trace collection. When you import the workload data from an Oracle Trace database into Oracle Expert, it will not be categorized using your application names. Instead, the workload data for all the applications in the collection will appear together under a tree hierarchy on the View/Edit page of the tuning session window, where the root of the tree hierarchy is the Server Application in the Application category.
Similarly, when you import workload data from the SQL cache of an instance, it will not be categorized using your application names. Instead, the workload data for all the applications will appear together under a tree hierarchy on the View/Edit page, where the root of the tree hierarchy is an Application named for the instance in the Application category.
Regardless of whether you collect workload data using Oracle Trace or from the SQL cache, you will find it easier to identify which collected transactions and requests come from which applications if only one or a few applications are running when you collect the data. You can assign better importance values to workload elements when you can identify the applications that they are part of.
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. Oracle Expert uses the importance value for each workload element to determine the most important applications, then generates recommendations to optimize their performance.
Oracle Expert computes an element's relative importance value using the element's importance value and frequency value. When workload elements collected by Oracle Trace or from a SQL cache are imported, Oracle Expert automatically assigns these elements an importance value of 5. Unless you believe that all the elements in your workload are equally important, you may want to consider changing their importance values.
The frequency value for an element is collected when the element is collected by Oracle Trace or from a SQL cache. The frequency value for an element is the number of times the element was executed each time its parent in the workload hierarchy was executed. For an individual SQL statement (Request), for example, the frequency value is the number of times the statement executed during a single execution of its parent transaction.
Oracle Expert has two ways of computing the relative importance of individual workload elements. By default, Oracle Expert gives more weight to the frequency value of an element when computing its relative importance. However, you can change the default behavior so that Oracle Expert gives more weight to the importance value of an element when computing its relative importance. To change the default method of computing relative frequency, change the value of the "Workload analysis style" rule from 2 to 1. This rule can be modified at the Workload Application level on the View/Edit page. To change the value of the "Workload analysis style" 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 considers the elements in the highest category 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 workload element by taking into account the relative importance of the element's parents in higher categories 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 parents 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 in your workload 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 value 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 categories 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.
The Overwrite Existing option on the Workload Collect Options dialog box pertains to workload data at the Application level. When you select Overwrite Existing, Oracle Expert deletes all existing workload data and replaces it with the workload data being imported for that Application.
The Append to Existing option on the Workload Collect Options dialog box pertains to workload data at the Application level. When you select Append to Existing, Oracle Expert saves the existing workload data for a particular Application and also saves the workload data being imported for that Application. Oracle Expert assigns a new name to the newly imported workload data for the Application.
Consider how this works in practice. The first time you import workload data collected by Oracle Trace, Oracle Expert stores that data as an Application named Server Application. Then, if you later collect other workload data using Oracle Trace and import it into Oracle Expert with the Append to Existing option enabled, Oracle Expert preserves the existing data for the application named Server Application and stores the data you are importing as an Application named Server Application 1. The next time you import workload data collected by Oracle Trace and enable the Append to Existing option, that workload data will be named Server Application 2, and so on.
