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 chapter describes how to create and work with tuning sessions after you have started Oracle Expert.
The topics in this chapter include:
When you launch Oracle Expert, the Oracle Expert main window displays, as shown in Figure 4-1.
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.
The Database container is followed by the Parallel Servers container. This container displays parallel databases. 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.
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.
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:
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".
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:
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.
| Tuning Types | Tuning Categories |
|---|---|
|
Instance tuning: |
SGA |
|
|
I/O |
|
|
Sort |
|
|
Parallel Query |
|
|
Parallel Server |
|
|
OS-Specific |
|
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.
| 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 |
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 |
|
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".
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:
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.
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.
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.
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.
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.
These parameters are specific to the instance parameters of the operating system. These parameters can have a dramatic impact on performance. Examples of these parameters include async_write and db_writers parameters.
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:
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.
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". For more information about managing workload data, see "Database Workloads".
With structure tuning, Oracle Expert can generate recommendations about the sizing and placement of database segments. The categories of structure tuning are:
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.
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.
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.
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.
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).
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.
