10
Oracle Expert Methodology

Oracle Expert performs tuning by following a designed methodology.

The topics in this chapter include:

• Steps in Oracle Expert Methodology
• Setting the Scope of the Tuning Session
• Collecting Data
• Managing SQL History Data
• Viewing and Editing Collected Data
• Generating Recommendations
• Reviewing Recommendations
• Implementing Recommendations
• Inputs and Outputs

Steps in Oracle Expert Methodology

The Oracle Expert methodology includes the following steps (see Figure 10-1).

Figure 10-1 Implementation of Oracle Expert Methodology

Text description of the illustration nu3599a.gif

1. setting the scope of the tuning session
2. collecting the data
3. viewing and editing the collected data
4. analyzing the data and generating recommendations
5. reviewing the Oracle Expert recommendations
6. generating scripts for implementing the recommendations

Setting the Scope of the Tuning Session

When you set the scope of a new tuning session, you are telling Oracle Expert exactly what you want to tune. You can create a focused tuning session by initially selecting one or two categories. You can also create a comprehensive tuning session by selecting all of the scopes, but this may take a considerable amount of time.

Your choices can be one or more of the following:

• Check for Instance Optimizations

  Use this option to confirm that your instance parameter settings are appropriate and contention is not a problem.

• Check for SQL Reuse Opportunities

  Use this option to confirm SQL is being shared as appropriate.

• Check for Appropriate Space Management

  Use this option to evaluate database space management issues such as sizing and placement.

• Check for Optimal Data Access

  Use this option to optimize index usage on your database tables or to check for indexes that should be rebuilt.

Collecting Data

Oracle Expert collects the following classes of data as appropriate for the specified tuning scopes:

• database (database name and version, database users, tablespaces, public synonyms, and rollback and other segments)
• instances (parameters and statistics)
• schemas (tables, constraints, indexes, clusters, views, and synonyms)
• environment (system information)
• workload (can include data collected by Oracle Trace, a SQL History, an .XDL file or SQL cache)

Managing SQL History Data

Oracle Expert allows you to maintain a SQL History for your service (database). The SQL History can store SQL cache, Oracle Trace, or .XDL file data. The SQL History is shared among other Oracle Tuning Pack applications and tuning sessions. With the SQL History, the user can build up the complete set of SQL statements that are executed within the database environment and share the statements with multiple tuning sessions. By sharing the SQL history, you do not have to recollect statements for each tuning session. You can merge to the SQL history (building a complete set of SQL statements executing in the database environment) or you can replace an existing SQL History with a new one. Each service, however, can only have one SQL History at a time.

Viewing and Editing Collected Data

Once you have collected the various pieces of tuning data, you can view and edit that data. The data is organized as follows:

• database
  • instances (instance parameters)
  • schemas (tables, views, indexes, clusters, constraints, and synonyms)
  • tablespaces (datafiles and tablespace segments)
  • public synonyms
  • database users
• environment (system information)
• workload (application and request)

You have the option of editing both the attributes and the rules for this data. Attribute information is the actual data collected by the product. This data can be can be edited for "what if" tuning. You should change any dependent attributes for the evaluation to provide the best recommendations. The rules can also be changed. Rule adjustments allow you to influence Oracle Expert's evaluation process.

Generating Recommendations

Once you have collected and edited the data as needed, you can have Oracle Expert perform the analysis to generate tuning recommendations.

During the analysis, Oracle Expert evaluates the collected data in conjunction with all its rules and provides optimal performance tuning recommendations.

Reviewing Recommendations

Once Oracle Expert has analyzed the data, you can review the recommendations and decide which to accept.

For example, assume that Oracle Expert recommends that you increase the shared_pool_size parameter from 300,000 bytes to 500,000 bytes. If you choose not to use this recommendation, you can decline the recommendation and analyze the data again. Oracle Expert keeps track of the recommendations you have accepted and takes into account interdependencies among the collected data before generating new recommendations.

Implementing Recommendations

When you are ready to implement the Oracle Expert recommendations, you can use Oracle Expert to create parameter files and implementation scripts. These files and scripts allow you to implement the Oracle Expert recommendations at your convenience. These files and scripts minimize the risk of introducing any new problems during implementation, and they reduce the level of expertise required to implement the recommendations.

Inputs and Outputs

Oracle Expert takes collected input, processes this input through various rules and algorithms, and creates recommendations, tuning scripts, and reports.

Figure 10-2 Oracle Expert Inputs and Outputs

Text description of the illustration nu3600a.gif

Tuning Inputs

Oracle Expert uses the following input data to generate effective tuning recommendations:

• Database class

  The Database class data contains database-wide attributes of the database, such as the database name and version, users, tablespaces, and public synonyms.

• Instance class

  The Instance class data refers to the instance parameters and instance statistics information [collected by Oracle Expert not necessarily collected from V$ tables.]

• Schema class

  The Schema class data refers to the tables, indexes, clusters, views, and constraints information collected by Oracle Expert.

• Environment class

  The Environment class data refers to the physical hardware resources available to the database; for example, and system data (which includes memory and CPU information).

• Workload class

  Workload class data refers to the nature, frequency, and importance of SQL requests that access the database.

• Rules

  Rules are pieces of knowledge used by Oracle Expert to analyze collected data for a tuning session. By changing rule values, you influence the tuning recommendations made by Oracle Expert.

• Tuning Session characteristics

  Tuning Session characteristics provide Oracle Expert with a higher level of guidance about how the database is used. This is data that cannot be collected directly from the database. For example, the Oracle Server has specific features that are designed to optimize performance in a data warehouse environment. If you set Application Type to Data Warehousing, Oracle Expert knows that these features are applicable. Downtime Tolerance allows the user to influence whether the system will bias its recommendations for recovery or performance.

Generated Output

With inputs such as database, instance, schema, environment, workload, and rules, Oracle Expert generates the output needed for solving database performance problems. Oracle Expert generates the following:

• reports

  Oracle Expert generates the Analysis, Session Data, and Recommendation Summary reports and Workload Cross Reference.

  • Analysis report

    Describes the tuning recommendations made by Oracle Expert. This report provides a detailed explanation of what Oracle Expert evaluated, how Oracle Expert interpreted the collected data and why, and any risks involved in implementing the recommendations.

  • Session Data report

    Provides summary database information. It also provides detailed information about instances, database users, tablespaces, schemas, environment, workload, and rules.

  • Recommendation Summary

    Describes, in a concise form, the recommendations made by Oracle Expert. This report is a summary of the Analysis report.

  • Workload Cross Reference Report

    Provides information on tables and related requests. The report is organized by table name and by request name so that you can quickly find the information you need. This report can show you if your current Oracle Expert workload is complete.

• implementation files

  In addition to the reports, Oracle Expert generates files to help you implement its recommendations. These files include:

  • implementation script (.TXT file)

    Contains SQL for implementing schema objects, tablespaces and database users. Some recommendations, such as table relocation to a new tablespace, are described textually and performed by the DBA.

  • instance parameter file subsets (.ORA file)

    Contains instance parameter values that Oracle Expert recommends for improving the performance of a particular instance. Oracle Expert generates these subsets, which you can merge into the existing INIT.ORA file for the instance.