Oracle Enterprise Manager Oracle Expert User's Guide

Library

Product

Contents

Index

Managing Workloads

• Database Workloads
• Overwriting Workload Data

This appendix provides guidelines for collecting and managing Oracle Expert workloads. Topics in the appendix include:

• an overview of database workloads

• determining a representative workload for a tuning session

• collecting workload information with Oracle Trace

• collecting workload information from a SQL cache

• reducing the size of a collected workload

• understanding the workload hierarchy

• specifying importance values for workload elements

• overwriting workload data

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:

• a representative set of the SQL statements that execute during a period for which you would like Oracle Expert to optimize database performance

• a ranking of the SQL statements according to their relative business importance

• Database performance is consistently poor during certain time periods on most business days, and you want better performance during these periods.

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.

• Database performance deteriorates whenever a particular application runs, and you want better performance for this application.

In this situation, a representative workload would contain data about this application.

• Database performance deteriorates at different times from day to day with no obvious cause, and you would like to determine the cause of the problem.

In this situation, a representative workload would contain data about the applications running when performance is poor.

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.

Collecting Workload Information with Oracle Trace

• collect data about all of the SQL statements executing against a database

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.

• collect data for only the SQL statements executed by a particular application

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.

• choose the time period during which Oracle Trace will collect the SQL statement data

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.

See the Oracle Enterprise Manager Oracle Trace User's Guide for more information about collecting data using Oracle Trace.

Providing Oracle Trace Workload Information to Oracle Expert

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.

Collecting Workload Information from the SQL Cache

Providing SQL Cache Workload Information to 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.

Reducing the Size of a Collected Workload

Understanding the Workload Hierarchy

• Application

• Business Unit

• Transaction

• Request

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 comprise 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 comprise the Deposit Payment transaction in the Transaction category.

Table A-1 shows an example of 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.

Table A-1: A Sample Workload Hierarchy

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.

Specifying Importance Values for Workload Elements

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 consider all the elements in your workload to be 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 frequency 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.

Overwriting Workload Data

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index