12
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 Tuning Session Characteristics
• Opening an Existing Tuning Session
• Modifying a Tuning Session
• Deleting a Tuning Session
• Saving Tuning Session Data to a Log File

Creating a Tuning Session

A tuning session is the framework within which Oracle Expert performs its tuning activities. Tuning sessions allow you to organize tuning activity and separate sessions within the same database. 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.

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=>New or File=>Create from File...
• click the New button in the Oracle Expert toolbar
• click the right mouse button and choose "New" or "Create from File" 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.

When logging in to the target database, Oracle Expert uses the preferred credentials provided by the console. If you manually created a service, the username and password supplied are used. The account specified must have the SELECT ANY TABLE privilege.

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 scope or scopes you want Oracle Expert to address for the session. The tuning scopes you select determine the kinds of data that will be collected and the types of tuning recommendations Oracle Expert will make for the tuning session.

You can select one or more of the following tuning scopes:

• Check for Instance Optimizations
• Check for SQL Reuse Opportunities
• Check for Appropriate Space Management
• Check for Optimal Data Access

You can select any combination of tuning scopes. They are not mutually exclusive. If you select all of the tuning scopes, this is called comprehensive tuning. During a comprehensive tuning session, Oracle Expert generates every tuning recommendation it is capable of making for a database. Comprehensive tuning is resource intensive and it generally takes a considerable amount of time to get results.

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

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

Table 12-1 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 12-1 Comparison of Tuning Scopes

Tuning Considerations	Instance Optimization	Space Management	SQL Reuse	Data Access by Table	Data Access by Workload	Data Access Index Rebuild
Amount of data to collect	small	med to large	small to med	small to large	small to large	small
Time Required to collect data	short	med to long	short to med	short to long	short to long	short
Impact of collection effort on database	negligible	low	low	low	low	low
Manual effort required to collect of edit data	low	low	low	low to high	low	low
Potential complexity of implementing tuning recommendations	low	low	low	low	low	low
Potential gain from implementing tuning recommendations	med	med	med	med to high	med to high	med

You can change the scope of a tuning session. For more information about changing the scope, see "Modifying a Tuning Session".

Instance Optimizations

When Check for Instance Optimizations is selected, 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 or how the parameters will handle various contention problems. Oracle Expert tunes the following categories, assuming that you collect the data Oracle Expert expects:

• SGA parameters

  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 parameters

  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 parameters

  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 parameters

  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 parameters

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

• Operating System-Specific (OS-Specific) parameters

  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 disk_async_io and dbwr_io_slaves parameters.

• Contention Problems

  During an instance tuning session, Oracle Expert also checks the init.ora file to make sure that there is enough rollback space available.

  Note: For more information on the instance optimization parameters, see Taking Advantage of Rules.

  
  
  

SQL Reuse Opportunities

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

During SQL reuse, Oracle Expert identifies similar statements that prevent SQL statements from being reused in the shared pool because of differences in case and/or spacing.

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.

Space Management

When Check for Appropriate Space Management is selected, Oracle Expert evaluates the types and structures of tablespaces, the sizing and placing of schema objects, and the tablespace assignments of database users. If Oracle Expert determines that various guidelines are not being followed, then appropriate recommendations will be made.

Optimal Data Access

When Check for Optimal Data Access is selected, Oracle Expert ensures there is efficient access to data.

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 rule-based optimizer. One method that databases use to decrease data retrieval time is indexes. Oracle Expert offers three types of access method tuning:

• When performing comprehensive index evaluation on tables referenced by worst performing SQL statements, Oracle Expert will automatically focus the Data Access tuning on the tables referenced within the worst performing SQL statements identified in your tuning session workload. The tuning session's SQL statements will be ranked based on each statement's ratio of physical reads per executions. Oracle Expert will also automatically check existing indexes on the target tables for index fragmentation.
• When performing comprehensive index evaluation on tables you specify, Oracle Expert uses base index and workload analysis to determine whether new indexes should be created or existing indexes should be modified to enhance performance. Oracle Expert will also automatically check existing indexes on the target tables for index fragmentation.
  • 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 performing index fragmentation evaluation on tables you specify, Oracle Expert will identify indexes that suffer from index stagnation and should be rebuilt to enhance performance.

Selecting Values for Tuning Session Characteristics

Oracle Expert Tuning Session Characteristics are displayed in the Tuning Session Characteristics section of the Scope page.

Tuning Session Characteristics provide useful information for tuning the database environment. Tuning Session Characteristics provide information about your database that cannot be collected from your database. For each Tuning Session Characteristic, select the most appropriate value for your database environment. To change the value of a Tuning Session Characteristic, click the down-arrow next to the current value, then select the new value from the list. Oracle Expert uses the Tuning Session Characteristic values you select to optimize its tuning recommendations for your database's specific environment.

Oracle Expert allows you to set the following Tuning Session Characteristics:

• Application Type

The Application Type tuning session characteristic gives Oracle Expert an indication about the type of workload used in the database environment. This allows Oracle Expert to optimize the database for the type of workload. The possible values are:

• Downtime Tolerance

The Downtime Tolerance tuning session characteristic allows you to influence whether the system will bias its recommendations for recovery or performance. If the tolerance is large, Oracle Expert will optimize for performance. If the tolerance is small, Oracle Expert will optimize for recovery time. The possible values are:

• Peak Logical Write Rate

The Peak Logical Write Rate tuning session characteristic give Oracle Expert an indication of maximum write transaction volume when actual transaction statistics have not been collected. This information is used to evaluate whether the server is configured to support the expected write transaction rate. The possible values are:

• Forms Applications Used

The Forms Application Used tuning session characteristic tells Oracle Expert whether forms applications are used within the database environment or not. Oracle Expert contains rules which are specific to forms applications, such as setting the minimum number of open cursors for the instance. Possible values are: Yes and No.

• Comprehensive Analysis

The Comprehensive Analysis tuning session characteristic tells Oracle Expert that there is a complete workload in the current database. A comprehensive analysis provides the most efficient tuning recommendations. If you chose not to perform a comprehensive analysis, the analysis will focus on a subset of SQL statements that are currently being accessed by the database repository.

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 the tuning session characteristic 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 a different tuning category.

You can also view and edit the rules and attributes associated with the tuning session by using the Edit pull-down menu.

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.

Saving Tuning Session Data to a Log File

You can manually save current tuning session data to an Oracle Expert log file by choosing Help=>Save Current Session. (Note that tuning session information is automatically saved to a log file when unexpected errors are encountered.) This information can be sent to Oracle Customer Support for a comprehensive diagnosis of your session. Log files are saved in the <Oracle Home>\sysman\temp directory.