4
Creating and Working with Tuning Sessions

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

The topics in this chapter include:

• Creating a 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

Creating a Tuning Session

A tuning session is the framework within which Oracle Expert performs its tuning activities. Oracle Expert provides the following ways to create a tuning session: manually or using the Tuning Session wizard.

Creating a Tuning Session Using the Tuning Session Wizard

Oracle Expert offers an easy and quick way to create a new tuning session using the Tuning Session wizard. To activate the Tuning Session wizard, choose Tools=>Tuning Session Wizard.

The Tuning Session wizard automatically displays when you start Oracle Expert, unless you have disabled the automatic display.

Welcome

On the first page of the Tuning Session wizard you have the option of loading the sample tuning session, creating a new tuning session, or opening an existing tuning session as shown in Figure 4-1.

Figure 4-1 Tuning Session Wizard - Welcome Screen

On this screen, you also have the option of deciding whether you want the Tuning Session wizard to display at the start-up of Oracle Expert.

Note: When you change the 'Show this wizard at start-up' option, the change takes affect immediately. Clicking the Finish or Cancel button has no affect on this option.

Opening a Tuning Session

On this page, you select the database you want to tune and an existing tuning session (see Figure 4-2). A database will be listed in the list only if it has at least one tuning session associated with it.

Figure 4-2 Tuning Session Wizard - Opening a Tuning Session

Creating a Tuning Session

On this page, you select the database you want to tune and provide a name for the new tuning session (see Figure 4-3). The tuning session name defaults to New Session #, where # is a unique identifier for the tuning session. Tuning session names must be unique for a database.

A database will be listed if it has been discovered through Oracle Enterprise Manager.

Figure 4-3 Tuning Session Wizard - Creating a Tuning Session

Creating a Tuning Session Manually

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.

Setting the Scope of a Tuning Session

You must specify the scope of the tuning effort for each Oracle Expert tuning session (see Figure 4-4). 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 will make for the tuning session.

Figure 4-4 Scope Page

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
	Parallel Server
	OS-Specific
Application tuning:	SQL Reuse
	Access Methods (Optimal Index Use and Index Rebuild Detection)
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	Short1	Short->Long	Medium->Long
Impact of collection effort on database	Negligible	Low	Low
Manual effort required to collect or edit data	Low	Low->High	Low
Potential complexity of implementing tuning recommendations	Low	Low	Low
Potential gain from implementing tuning recommendations	Medium	Medium->High	Medium

1 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".

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.

• Oracle Parallel Server

  These parameters are specific to the Oracle Parallel Server environment. These parameters are the gc_db_locks, gc_files_to_locks, and gc_releasable_locks parameters.

• Operating System-Specific (OS-Specific)

  These parameters are specific to the instance parameters of the operating system and their availability varies from platform to platform. These parameters can have a significant impact on performance. Examples of these parameters include async_write and db_writers 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 Reuse

  When SQL Reuse is selected, Oracle Expert performs SQL statement matching when it analyzes the collected data.

  With SQL statement matching, Oracle Expert compares statements in the workload to determine if similar statements can be rewritten to eliminate redundancy. 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 tuning rules address the Oracle cost-based optimizer, not the rules-based optimizer. One method that databases use to decrease data retrieval time is indexes. Oracle Expert offers two types of access method tuning: optimal index use and index rebuild.

  • When you select Optimal Index Use, 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". For more information about managing workload data, see "Database Workloads".
  • When you select Index Rebuild Detection, Oracle Expert analyzes indexes to identify those which suffer from index stagnation and should be rebuilt to enhance performance.

    When an indexed value is updated in the table, the old value is deleted from the index and the new value is inserted into a separate part of the index. The space released by the old value can never be used again. As indexed values are updated or deleted, the amount of unusable space within the index increases, a condition called index stagnation. Because a stagnated index contains a mixture of data and empty areas, scans of the index will be less efficient. To resolve this inefficiency, the index should be rebuilt.

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 sizing various segments in order to improve space usage and performance.

• 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, click the arrow next to the current value, then select the new value from the list. Oracle Expert uses the control parameter values you select to optimize its tuning recommendations for your database's specific environment.

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 on the existing tuning session name in the tree list and clicking the Open icon on the Oracle Expert toolbar

• double-clicking the session name to make the session the active session

• clicking the Tuning Session wizard button in the toolbar and choosing Open

Modifying a Tuning Session

To change an active session's scope and control parameter 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 have selected and performed focused instance tuning. 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.

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.

---