Oracle Enterprise Manager Oracle Expert User's Guide

Creating and Working with Tuning Sessions

Oracle Expert Main Window
Identifying the Database to Tune
Creating a New Tuning Session
Setting the Scope of a Tuning Session
Selecting Values for Control Parameters
Opening an Existing Tuning Session
Modifying a Tuning Session
Deleting a Tuning Session

This chapter describes how to create and work with tuning sessions after you have started Oracle Expert.

The topics in this chapter include:

Oracle Expert Main Window

When you launch Oracle Expert, the Oracle Expert main window displays, as shown in Figure 4-1.

Figure 4-1: Oracle Expert Main Window

The window consists of menus, panes, a toolbar, and a status bar. Use the menus and toolbar to access Oracle Expert functionality. The status bar provides information relating to the task you are performing.

Left Pane

The left pane of the Oracle Expert main window provides a tree structure representing the hierarchy of the Oracle Expert objects. The top object in the tree structure is the Database container. Expanding this container displays all databases that have been discovered by Oracle Enterprise Manager. Expanding each database displays a list of tuning sessions. Each database may contain one or more tuning sessions. Click on the plus sign (+) to expand the tree and the minus sign (-) to collapse the tree.

If the Database container is empty, this means the Oracle Enterprise Manager discovery has never been performed. For more information on discovering databases and network services, see the Oracle Enterprise Manager Installation Guide.

Right Pane

When you first invoke Oracle Expert, the splash screen displays. Once a tuning session is opened, the right pane of the window displays information relevant to the tuning session highlighted in the left pane. In general, these are the pages associated with the Oracle Expert tuning session window. The information is based on data stored in Oracle Expert tables within the Oracle Enterprise Manager repository. The repository is a set of tables stored in an Oracle database. For a more detailed description of the repository, refer to the Oracle Enterprise Manager Concepts Guide.

Identifying the Database to Tune

Before you can create a tuning session, you must identify the database you want Oracle Expert to tune by clicking on the name of the database in the tree list. This database (service) name is the name Oracle Expert will use for the Database object and the Instance object on the View/Edit page of the tuning session window. Oracle Expert connects to the database by referring to the information in the Preferred Credentials defined in the Oracle Enterprise Manager console. If those credentials are not defined, Oracle Expert uses the credentials used to connect to the repository. If the repository credentials are not defined, you are prompted for the username and password.

Note that Oracle Expert requires the SELECT ANY TABLE privilege to be able to access and collect data from the database being tuned.

If you are using Oracle Expert to help you with initial configuration of a new database, click the database on which you want to base the new database. Create a new tuning session. Refer to Chapter 12, Initial Configuration, for more information.

To perform initial configuration tuning with Oracle Expert, you must set the value of the "Initial configuration tuning" control parameter to Enabled. You can view and change control parameter values on the Control Parameters section of the Scope page.

Creating a New Tuning Session

To create a new tuning session, in the tree list click on the '+' to the left of the name of the database you want to tune, expand the tree list to display the Tuning Session folder, and perform one of the following:

choose File=>Create
click the Create Tuning Session button in the Oracle Expert toolbar
click the right mouse button and choose Create in the pop-up menu

Oracle Expert assigns a unique name to the new tuning session. You can either accept this name or provide a new name. Edit the tuning session name by clicking on the name and typing the new name. The new name must be 40 characters or fewer in length. Oracle Expert preserves the case (lowercase and uppercase) of alphabetic characters in the name.

If you receive a "table not found" error message, you may not have adequate privileges. Oracle Expert requires SELECT ANY TABLE privilege to collect the required information from the database to be tuned. For more information about identifying the database to be tuned, see Selecting Values for Control Parameters on page 4-8.

Setting the Scope of a Tuning Session

You must specify the scope of the tuning effort for each Oracle Expert tuning session. You do this by selecting the tuning categories you want Oracle Expert to address for the session. The tuning categories you select determine the kinds of tuning recommendations Oracle Expert can make for the tuning session.

You can perform three different types of tuning with Oracle Expert:

instance tuning
application tuning
structure tuning

You can select any combination of tuning types. They are not mutually exclusive. Each tuning type has two or more tuning categories associated with it, as shown in Table 4-1. You can choose one or more categories for one or more of the tuning types.

Table 4-1: Tuning Categories for Tuning Types
Tuning Types	Tuning Categories
Instance tuning:	SGA
	I/O
	Sort
	Parallel Query
Application tuning:	SQL Tuning
	Access Methods
Structure tuning:	Sizing
	Placement

If you select all the categories for all the tuning types, this is called comprehensive tuning. During a comprehensive tuning session, Oracle Expert generates every tuning recommendation it is capable of making for a database.

If you do not select all the tuning categories for all the tuning types, this is called focused tuning. During a focused tuning session, Oracle Expert generates tuning recommendations for the selected tuning categories.

Select the Scope tab to display the Scope page of the Oracle Expert tuning session window, then select one or more tuning categories for the tuning session.

Table 4-2 uses a set of tuning considerations to compare the three types of tuning. Use the table to determine if the type of tuning you want to do is practical (based on the time and resources you have to devote to the tuning session). Note that Oracle Expert allows you to perform as many or as few categories of tuning as you want at a given time. If you do not have the time or resources to tune all the categories you are interested in at once, you can tune those you have time for first, then tune the other categories when you have more time or resources.

Table 4-2: Comparison of Tuning Types

Tuning Considerations

Instance Tuning

Application Tuning

Structure Tuning

Amount of data to collect

Small

Small->Large

Medium->Large

Time required to collect data

Short *

Short->Long

Medium->Long

Impact of collection effort on database

Low

Medium

Low->Medium

Manual effort required to collect or edit data

Low

Low->High

Low->High

Potential complexity of implementing tuning recommendations

Low

Medium

High

Potential gain from implementing tuning recommendations

Medium

Medium->High

Medium

*
For a single instance statistics sample

You can change the scope of a tuning session. For more information about changing the scope, see Modifying a Tuning Session on page 4-9.

Instance Tuning

With instance tuning, you can tune instance parameters, which control the behavior of the database and certain configuration options, such as how the database will use memory resources on the system. Oracle Expert can tune any or all of the following categories of instance parameters, assuming that you collect the data Oracle Expert expects:

SGA

: These parameters affect the total size of the instance's System Global Area (SGA). The appropriate setting of these parameters results in efficient utilization of memory and prevents reparsing SQL statements except when necessary. Examples of these parameters include the db_block_buffers and shared_pool_reserved_size parameters.

I/O

: These parameters affect the throughput or distribution of I/O for the instance. Examples of these parameters include the checkpoint_process and db_file_multiblock_read_count parameters.

Sort

: These parameters influence how the Oracle Server performs sort operations on behalf of the user. Examples of these parameters include the sort_direct_write and sort_area_retained_size parameters.

Parallel Query

: These parameters are specific to the parallel query behavior for the instance. Examples of these parameters include the parallel_min_servers and parallel_max_servers parameters.

Application Tuning

With application tuning, Oracle Expert examines the applications, transactions, and SQL statements that run against a database. Depending on the application tuning areas you select, Oracle Expert can recommend that you make changes to applications, indexes, or both to improve database performance, assuming that you collect the data Oracle Expert expects. The two categories of Oracle Expert application tuning are:

SQL Tuning

: When SQL Tuning is selected, Oracle Expert performs SQL statement matching tuning when it analyzes the collected data.
: With SQL statement matching tuning, Oracle Expert compares statements in the workload to determine if they can be rewritten to take advantage of Oracle library cache behavior. The Oracle Server maintains only one copy of a distinct SQL statement within the cache to maximize memory and minimize redundant parsing and validating. The Oracle Server does not consider statements to be identical unless they use identical spacing, punctuation, and case, and they match character by character. If Oracle Expert finds one or more statements that can be rewritten to increase cache efficiency, it makes these recommendations.

Access Methods

: An access method is a strategy used to retrieve data from a database in the most optimal way. The Oracle Expert access method rules address the Oracle cost-based optimizer, not the rules-based optimizer. One method that databases use to decrease data retrieval time is indexes. When Access Methods is selected, Oracle Expert uses base index and workload analysis tuning to determine whether new indexes should be created or current indexes modified to enhance performance.
: With base index tuning, Oracle Expert scans schemas selected for tuning for evidence of implicit search operations, such as constraints and views. If a constraint or view is found, Oracle Expert determines whether an index is necessary to improve performance when the constraint or view is executed.
: With workload analysis, Oracle Expert scans SQL statements in the workload. After scanning the workload, Oracle Expert can identify poor existing index structures and recommend index structure changes to improve performance and use of those indexes. Oracle Expert can also recommend that some data be accessed in sorted order (avoiding costly data sorts). Finally, Oracle Expert can recommend new index structures. For more information about collecting workload data, see Collecting the Workload Class on page 5-25. For more information about managing workload data, see Database Workloads on page A-1.

Structure Tuning

With structure tuning, Oracle Expert can generate recommendations about the sizing and placement of database segments. The categories of structure tuning are:

Sizing

: Oracle Expert makes recommendations for some of the default storage parameters of a tablespace. These parameters provide default initial allocation and growth rate of segments created within that tablespace.

Placement

: Oracle Expert makes recommendations to ensure that separate tablespaces exist for different types of segments and that database users have been assigned appropriate default and temporary tablespaces.

Selecting Values for Control Parameters

Oracle Expert control parameters are displayed in the Control Parameters section of the Scope page.

Control parameters provide useful information for tuning the database environment. Control parameters provide information about your database that cannot be collected from your database. For each control parameter, select the most appropriate value for your database environment. To change the value of a control parameter, double-click in its Value column, then select the appropriate value. Oracle Expert uses the control parameter values you select to optimize its tuning recommendations for your database's specific environment.

You can get more information about a control parameter by selecting it, then clicking the Description button. Oracle Expert displays a description of the control parameter. To see the values that can be selected for a control parameter, click in the Value column for the control parameter.

Opening an Existing Tuning Session

You can open an existing tuning session by clicking on the existing tuning session name in the tree list, and then choosing File=>Open, clicking the Open icon on the Oracle Expert toolbar, or double-clicking the session name to make the session the active session.

Modifying a Tuning Session

To change an active session's scope and control parameters values, use the Scope page of the tuning session window. Enter data in the same manner as you did when you created the tuning session.

You can change the tuning scope to perform a different type of analysis. For example, you might initially collect the Database and Instance collection classes as part of an instance tuning session. After Oracle Expert has analyzed the data collected for the instance tuning session, you might decide to select tuning categories for application tuning or structure tuning. By changing the scope of the existing tuning session instead of creating a new tuning session, you do not need to re-collect data for the Database class (which Oracle Expert uses for instance, application, and structure tuning).

Deleting a Tuning Session

You can delete a tuning session by clicking on the tuning session name in the tree list and choosing File=>Delete. This displays a dialog box that asks you to confirm the tuning session deletion. If you confirm the deletion, Oracle Expert deletes the tuning session and all the data in the repository that is associated with the tuning session.

Copyright © 1996 Oracle Corporation.
All Rights Reserved.