Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

Audience

Documentation Accessibility

Related Documents

Conventions

Part I Getting Started

1 Introduction

About This Guide

Common Oracle DBA Tasks

Tools for Tuning the Database

2 Oracle Database Performance Method

Gathering Database Statistics Using the Automatic Workload Repository

Time Model Statistics

Wait Event Statistics

Session and System Statistics

Active Session History Statistics

High-Load SQL Statistics

Using the Oracle Performance Method

Preparing the Database for Tuning

Tuning the Database Proactively

Tuning the Database Reactively

Tuning SQL Statements

Common Performance Problems Found in Oracle Databases

Part II Proactive Database Tuning

3 Automatic Database Performance Monitoring

Overview of Automatic Database Diagnostic Monitor

ADDM Analysis

ADDM Recommendations

ADDM for Oracle Real Application Clusters

Configuring Automatic Database Diagnostic Monitor

Setting Initialization Parameters to Enable ADDM

Setting the DBIO_EXPECTED Parameter

Managing AWR Snapshots

Creating Snapshots

Modifying Snapshot Settings

Reviewing the Automatic Database Diagnostic Monitor Analysis

Interpretation of Automatic Database Diagnostic Monitor Findings

Implementing Automatic Database Diagnostic Monitor Recommendations

Viewing Snapshot Statistics

4 Monitoring Real-Time Database Performance

Monitoring User Activity

Monitoring Top SQL

Monitoring Top Sessions

Monitoring Top Services

Monitoring Top Modules

Monitoring Top Actions

Monitoring Top Clients

Monitoring Top PL/SQL

Monitoring Top Files

Monitoring Top Objects

Monitoring Instance Activity

Monitoring Throughput

Monitoring I/O

Monitoring I/O by Function

Monitoring I/O by Type

Monitoring I/O by Consumer Group

Monitoring Parallel Execution

Monitoring Services

Monitoring Host Activity

Monitoring CPU Utilization

Monitoring Memory Utilization

Monitoring Disk I/O Utilization

Determining the Cause of Spikes in Database Activity

Customizing the Database Performance Page

5 Monitoring Performance Alerts

Setting Metric Thresholds for Performance Alerts

Responding to Alerts

Clearing Alerts

Part III Reactive Database Tuning

6 Manual Database Performance Monitoring

Manually Running ADDM to Analyze Current Database Performance

Manually Running ADDM to Analyze Historical Database Performance

Accessing Previous ADDM Results

7 Resolving Transient Performance Problems

Overview of Active Session History

Running Active Session History Reports

Active Session History Reports

Top Events

Top User Events

Top Background Events

Load Profile

Top SQL

Top Sessions

Top DB Objects

Top DB Files

Activity Over Time

Diagnosing Malfunctioning Systems in an Emergency

8 Resolving Performance Degradation Over Time

Managing Baselines

Creating a Baseline

Creating a Single Baseline

Creating a Repeating Baseline

Deleting a Baseline

Computing Threshold Statistics for Baselines

Setting Metric Thresholds for Baselines

Setting Metric Thresholds for the Default Moving Baseline

Setting Metric Thresholds for Selected Baselines

Running the AWR Compare Periods Reports

Comparing a Baseline to Another Baseline or Pair of Snapshots

Comparing Current System Performance to a Baseline Period

Comparing Two Pairs of Snapshots

Using the AWR Compare Periods Reports

Summary of the AWR Compare Periods Report

Snapshot Sets

Load Profile

Top Timed Events

Host Configuration Comparison

System Configuration Comparison

Details of the AWR Compare Periods Report

Supplemental Information in the AWR Compare Periods Report

Part IV SQL Tuning

9 Identifying High-Load SQL Statements

Identification of High-Load SQL Statements Using ADDM Findings

Identifying High-Load SQL Statements Using Top SQL

Viewing SQL Statements by Wait Class

Viewing Details of SQL Statements

Viewing SQL Statistics

Viewing Session Activity

Viewing the SQL Execution Plan

Viewing the Plan Control

Viewing the Tuning History

10 Tuning SQL Statements

Tuning SQL Statements Using SQL Tuning Advisor

Tuning SQL Manually Using SQL Tuning Advisor

Viewing Automatic SQL Tuning Results

Managing SQL Tuning Sets

Creating a SQL Tuning Set

Creating a SQL Tuning Set: Options

Creating a SQL Tuning Set: Load Method

Creating a SQL Tuning Set: Filter Options

Creating a SQL Tuning Set: Schedule

Dropping a SQL Tuning Set

Transporting SQL Tuning Sets

Exporting a SQL Tuning Set

Importing a SQL Tuning Set

Managing SQL Profiles

Managing SQL Execution Plans

11 Optimizing Data Access Paths

Running SQL Access Advisor

Running SQL Access Advisor: Initial Options

Running SQL Access Advisor: Workload Source

Using SQL Statements from the Cache

Using an Existing SQL Tuning Set

Using a Hypothetical Workload

Running SQL Access Advisor: Filter Options

Defining Filters for Resource Consumption

Defining Filters for Users

Defining Filters for Tables

Defining Filters for SQL Text

Defining Filters for Modules

Defining Filters for Actions

Running SQL Access Advisor: Recommendation Options

Running SQL Access Advisor: Schedule

Reviewing the SQL Access Advisor Recommendations

Reviewing the SQL Access Advisor Recommendations: Summary

Reviewing the SQL Access Advisor Recommendations: Recommendations

Reviewing the SQL Access Advisor Recommendations: SQL Statements

Reviewing the SQL Access Advisor Recommendations: Details

Implementing the SQL Access Advisor Recommendations

Index

List of Figures

2-1 DB Time in Overall User Response Time

2-2 DB Time in User Transaction

2-3 Active Session History

4-1 Performance Page

4-2 Monitoring User Activity

4-3 Active Sessions Working page

4-4 Monitoring Top SQL

4-5 Monitoring Top Sessions

4-6 Monitoring Top Services

4-7 Monitoring Top Modules

4-8 Monitoring Top Actions

4-9 Monitoring Top Clients

4-10 Monitoring Top PL/SQL

4-11 Monitoring Top Files

4-12 Monitoring Top Objects

4-13 Monitoring Throughput

4-14 Monitoring I/O

4-15 Monitoring Parallel Execution

4-16 Monitoring Services

4-17 Monitoring Host Activity

4-18 Performance Summary

6-1 Analyzing Historical Database Performance

7-1 Top User Events

7-2 Top Background Events

7-3 Top Service/Module

7-4 Top SQL with Top Events

7-5 Top Sessions

7-6 Top DB Objects

7-7 Top DB Files

7-8 Activity Over Time

8-1 AWR Compare Periods Report

9-1 Top Activity Page

9-2 Viewing SQL Statements by Wait Class

11-1 Scheduling a SQL Access Advisor Task

List of Tables

2-1 Active Session History

7-1 Activity Over Time

Oracle® Database

2 Day + Performance Tuning Guide

11g Release 2 (11.2)

E10822-04

November 2011

Oracle Database 2 Day + Performance Tuning Guide, 11g Release 2 (11.2)

E10822-04

Copyright © 2007, 2011, Oracle and/or its affiliates. All rights reserved.

Primary Authors: David McDermid, Lance Ashdown, Immanuel Chan

Contributing Author: Sushil Kumar

Contributors: Pete Belknap, Supiti Buranawatanachoke, Nancy Chen, Kakali Das, Karl Dias, Mike Feng, Yong Feng, Cecilia Grant, Connie Green, William Hodak, Andrew Holdsworth, Kevin Jernigan, Caroline Johnston, Sue K. Lee, Herve Lejeune, Colin McGregor, Mughees Minhas, Valarie Moore, Deborah Owens, Mark Ramacher, Uri Shaft, Susan Shepard, Janet Stern, Hsiao-Te Su, Minde Sun, Mark Townsend, Stephen Wexler, Graham Wood, Khaled Yagoub, Michael Zampiceni

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Preface

This preface contains the following topics:

Audience

Documentation Accessibility

Related Documents

Conventions

Audience

This guide is intended for Oracle database administrators (DBAs) who want to tune and optimize the performance of Oracle Database. Before using this document, you should complete Oracle Database 2 Day DBA.

In particular, this guide is targeted toward the following groups of users:

Oracle DBAs who want to acquire database performance tuning skills

DBAs who are new to Oracle Database

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Websites in Documentation

This documentation may contain links to websites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these websites.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/support/contact.html or visit http://www.oracle.com/accessibility/support.html if you are hearing impaired.

Related Documents

For more information about the topics covered in this document, see the following documents:

Oracle Database 2 Day DBA

Oracle Database Administrator's Guide

Oracle Database Concepts

Oracle Database Performance Tuning Guide

Conventions

The following conventions are used in this document:

Convention | Meaning
boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.
italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

Part I

Getting Started

Part I provides an introduction to this guide and explains the Oracle Database performance method. This part contains the following chapters:

Chapter 1, "Introduction"

Chapter 2, "Oracle Database Performance Method"

1 Introduction

As an Oracle database administrator (DBA), you are responsible for the performance of your Oracle database. Tuning a database to reach a desirable performance level may be a daunting task, especially for DBAs who are new to Oracle Database. Oracle Database 2 Day + Performance Tuning Guide is a quick start guide that teaches you how to perform day-to-day database performance tuning tasks using features provided by Oracle Diagnostics Pack, Oracle Tuning Pack, and Oracle Enterprise Manager (Enterprise Manager).

This chapter contains the following sections:

About This Guide

Common Oracle DBA Tasks

Tools for Tuning the Database

About This Guide

Before using this guide, you must do the following:

Read Oracle Database 2 Day DBA in its entirety.

Obtain the necessary products and tools described in "Tools for Tuning the Database".

Oracle Database 2 Day + Performance Tuning Guide is task-oriented. The objective is to describe why and when tuning tasks need to be performed.

This guide is not an exhaustive discussion of all Oracle Database concepts. For that type of information, see Oracle Database Concepts.

This guide does not describe basic Oracle Database administrative tasks. For that type of information, see Oracle Database 2 Day DBA. For a complete discussion of administrative tasks, see Oracle Database Administrator's Guide.

The primary interface used in this guide is the Enterprise Manager Database Control console. This guide is not an exhaustive discussion of all Oracle Database performance tuning features. It does not cover available application programming interfaces (APIs) that provide comparable tuning options to those presented in this guide. For this type of information, see Oracle Database Performance Tuning Guide.

Common Oracle DBA Tasks

As an Oracle DBA, you can expect to be involved in the following tasks:

Installing Oracle software

Creating an Oracle database

Upgrading the database and software to new releases

Starting up and shutting down the database

Managing the storage structures of the database

Managing user accounts and security

Managing schema objects, such as tables, indexes, and views

Making database backups and performing database recovery, when necessary

Proactively monitoring the condition of the database and taking preventive or corrective actions, as required

Monitoring and tuning database performance

Oracle Database 2 Day + Performance Tuning Guide describes how to accomplish the last two tasks in the preceding list.

Tools for Tuning the Database

The intent of this guide is to allow you to quickly and efficiently tune and optimize the performance of Oracle Database.

To achieve the goals of this guide, you must acquire the following products, tools, features, and utilities:

Oracle Database 11g Enterprise Edition

Oracle Database 11g Enterprise Edition offers enterprise-class performance, scalability and reliability on clustered and single-server configurations. It includes many performance features that are used in this guide.

Oracle Enterprise Manager

The primary tool to manage the database is Enterprise Manager, a Web-based interface. After you install the Oracle software, create or upgrade a database, and configure the network, you can use Enterprise Manager to manage the database. In addition, Enterprise Manager provides an interface for performance advisors and for database utilities, such as SQL*Loader and Recovery Manager (RMAN).

Oracle Diagnostics Pack

Oracle Diagnostics Pack offers a complete, cost-effective, and easy-to-use solution to manage the performance of Oracle Database environments by providing unique features, such as automatic identification of performance bottlenecks, guided problem resolution, and comprehensive system monitoring. Key features of Oracle Diagnostics Pack used in this guide include Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), and Active Session History (ASH).

Oracle Database Tuning Pack

Oracle Database Tuning Pack automates the database application tuning process, thereby significantly lowering database management costs while enhancing performance and reliability. Key features of Oracle Database Tuning Pack that are used in this guide include the following:

SQL Tuning Advisor

This feature enables you to submit one or more SQL statements as input and receive output in the form of specific advice or recommendations for how to tune statements, along with a rationale for each recommendation and its expected benefit. A recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statements, or creation of SQL profiles.

SQL Access Advisor

This feature enables you to optimize data access paths of SQL queries by recommending the proper set of materialized views and view logs, indexes, and partitions for a given SQL workload.

Oracle Real Application Testing

Oracle Real Application Testing consists of the following key features:

Database Replay

This feature enables you to capture the database workload on a production system, and replay it on a test system with the exact same timing and concurrency as the production system on the same or newer release of Oracle Database.

SQL Performance Analyzer

This feature enables you to assess the effect of system changes on SQL performance by identifying SQL statements that have regressed, improved, or remained unchanged.

See Oracle Database Real Application Testing User's Guide to learn how to use these features.

Note:

Some of the products and tools in the preceding list, including Oracle Diagnostics Pack and Oracle Database Tuning Pack, require separate licenses. For more information, see Oracle Database Licensing Information.

2 Oracle Database Performance Method

Performance improvement is an iterative process. Removing the first bottleneck (a point where resource contention is highest) may not lead to performance improvement immediately because another bottleneck might be revealed that has an even greater performance impact on the system. For this reason, the Oracle performance method is iterative. Accurately diagnosing the performance problem is the first step toward ensuring that your changes improve performance.

Typically, performance problems result from a lack of throughput (the amount of work that can be completed in a specified time), unacceptable user or job response time (the time to complete a specified workload), or both. The problem might be localized to specific application modules or span the system.

Before looking at database or operating system statistics, it is crucial to get feedback from the system users and the people paying for the application. This feedback makes it easier to set performance goals. Improved performance can be measured in terms of business goals rather than system statistics.

The Oracle performance method can be applied until performance goals are met or deemed impractical. Because this process is iterative, some investigations may have little impact on system performance. It takes time and experience to accurately pinpoint critical bottlenecks quickly. Automatic Database Diagnostic Monitor (ADDM) implements the Oracle performance method and analyzes statistics to provide automatic diagnosis of major performance problems. Because ADDM can significantly shorten the time required to improve the performance of a system, it is the method used in this guide.

This chapter discusses the Oracle Database performance method and contains the following sections:

Gathering Database Statistics Using the Automatic Workload Repository

Using the Oracle Performance Method

Common Performance Problems Found in Oracle Databases

Gathering Database Statistics Using the Automatic Workload Repository

Database statistics provide information about the type of load on the database and the internal and external resources used by the database. To accurately diagnose performance problems with the database using ADDM, statistics must be available.

A cumulative statistic is a count such as the number of block reads. Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle Database also tracks cumulative statistics about segments and services. Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning purposes.

By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons. The delta values captured by the snapshot represent the changes for each statistic over the time period. Statistics gathered by AWR are queried from memory. The gathered data can be displayed in both reports and views.

The following initialization parameters are relevant for AWR:

STATISTICS_LEVEL

Set this parameter to TYPICAL (default) or ALL to enable statistics gathering by AWR. Setting STATISTICS_LEVEL to BASIC disables many database features, including AWR, and is not recommended. To learn more about this initialization parameter, see Oracle Database Reference.

CONTROL_MANAGEMENT_PACK_ACCESS

Set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables many database features, including ADDM, and is strongly discouraged. To learn more about this initialization parameter, see Oracle Database Reference.

The database statistics collected and processed by AWR include:

Time Model Statistics

Wait Event Statistics

Session and System Statistics

Active Session History Statistics

High-Load SQL Statistics

Time Model Statistics

Time model statistics measure the time spent in the database by operation type. The most important time model statistic is database time (DB time). Database time represents the total time spent in database calls by foreground sessions, and is an indicator of the total instance workload. As shown in Figure 2-1, database time makes up a portion of an application's overall user response time.

Figure 2-1 DB Time in Overall User Response Time

Description of Figure 2-1 follows
Description of "Figure 2-1 DB Time in Overall User Response Time"

A session is a logical entity in the database instance memory that represents the state of a current user login to a database. Database time is calculated by aggregating the CPU time and wait time of all active sessions (sessions that are not idle). For any database request, the CPU time is the sum of the time spent working on the request, while the wait time is the sum of all the waits for various database instance resources. DB time includes only time spent on client processes and does not include time spent on background processes such as PMON.

For example, a user session may involve an online transaction made at an online bookseller consisting of the actions shown in Figure 2-2.

Figure 2-2 DB Time in User Transaction

Description of Figure 2-2 follows
Description of "Figure 2-2 DB Time in User Transaction"

Query for novels by author

The user performs a search for novels by a particular author. This action causes the application to perform a database query for novels by the author.

Browse results of query

The user browses the returned list of novels by the author and accesses additional details, such as user reviews and inventory status. This action causes the application to perform additional database queries.

Add item to cart

After browsing details about the novels, the user decides to add one novel to the shopping cart. This action causes the application to make a database call to update the shopping cart.

Checkout

The user completes the transaction by checking out, using the address and payment information previously saved at the bookseller's website from a previous purchase. This action causes the application to perform various database operations to retrieve the user's information, add a new order, update the inventory, and generate an e-mail confirmation.

For each of the preceding actions, the user makes a request to the database, as represented by the down arrow in Figure 2-2. The CPU time spent by the database processing the request and the wait time spent waiting for the database are considered DB time, as represented by the shaded areas. After the request is completed, the results are returned to the user, as represented by the up arrow. The space between the up and down arrows represents the total user response time for processing the request, which contains other components besides DB time, as illustrated in Figure 2-1.

Note:

DB time is measured cumulatively from when the instance started. Because DB time combines times from all non-idle user sessions, DB time can exceed the time elapsed since the instance started. For example, an instance that has run 5 minutes could have four active sessions whose cumulative DB time is 20 minutes.

The objective of database tuning is to reduce database time. In this way, you can improve the overall response time of user transactions in the application.

Wait Event Statistics

Wait events are incremented by a session to indicate that the session had to wait for an event to complete before being able to continue processing. When a session has to wait while processing a user request, the database records the wait by using one of a set of predefined wait events. The events are then grouped into wait classes, such as User I/O and Network. Wait event data reveals symptoms of problems that might be affecting performance, such as latch, buffer, or I/O contention.

See Also:

Oracle Database Performance Tuning Guide

Oracle Database Reference

Session and System Statistics

A large number of cumulative database statistics are available on a system and session level. Some of these statistics are collected by AWR.

Active Session History Statistics

The Active Session History (ASH) statistics are samples of session activity in the database. The database samples active sessions every second and stores them in a circular buffer in the System Global Area (SGA). Any session that is connected to the database and using CPU, or is waiting for an event that does not belong to the idle wait class, is considered an active session. By capturing only active sessions, a manageable set of data is represented. The size of the data is directly related to the work being performed, rather than the number of sessions allowed on the database.

Using the DB time example described in "Time Model Statistics", samples of session activity are collected from the online transaction made at the bookseller's website, represented as vertical lines below the horizontal arrow in Figure 2-3.

Figure 2-3 Active Session History

Description of Figure 2-3 follows
Description of "Figure 2-3 Active Session History"

The light vertical lines represent samples of inactive session activity that are not captured in the ASH statistics. The bold vertical lines represent samples of active sessions that are captured at:

7:38, while novels by the author are being queried

7:42, while the user is browsing the query results

7:50, when one novel is added to the shopping cart

7:52, during the checkout process

Table 2-1 lists ASH statistics collected for the active sessions, along with examples of the session ID (SID), module, SQL ID, session state, and wait events that are sampled.

Table 2-1 Active Session History

Time
SID
Module
SQL ID
State
Event

7:38

213

Book by author

qa324jffritcf

Waiting

db file sequential read

7:42

213

Get review ID

aferv5desfzs5

CPU

n/a

7:50

213

Add item to cart

hk32pekfcbdfr

Waiting

buffer busy wait

7:52

213

Checkout

abngldf95f4de

Waiting

log file sync

High-Load SQL Statistics

SQL statements that are consuming the most resources produce the highest load on the system, based on criteria such as elapsed time and CPU time.

Using the Oracle Performance Method

Performance tuning using the Oracle performance method is driven by identifying and eliminating bottlenecks in the database, and by developing efficient SQL statements. Database tuning is performed in two phases: proactively and reactively.

In the proactive tuning phase, you must perform tuning tasks as part of your daily database maintenance routine, such as reviewing ADDM analysis and findings, monitoring the real-time performance of the database, and responding to alerts.

In the reactive tuning phase, you must respond to issues reported by users, such as performance problems that may occur for only a short duration of time, or performance degradation to the database over a period of time.

SQL tuning is an iterative process to identify, tune, and improve the efficiency of high-load SQL statements.

Applying the Oracle performance method involves the following:

Performing pre-tuning preparations, as described in "Preparing the Database for Tuning"

Tuning the database proactively on a regular basis, as described in "Tuning the Database Proactively"

Tuning the database reactively when performance problems are reported by the users, as described in "Tuning the Database Reactively"

Identifying, tuning, and optimizing high-load SQL statements, as described in "Tuning SQL Statements"

To improve database performance, you must apply these principles iteratively.

Preparing the Database for Tuning

This section lists and describes the steps that must be performed before the database can be properly tuned.

To prepare the database for tuning: 

Get feedback from users.

Determine the scope of the performance project and subsequent performance goals, and determine performance goals for the future. This process is key for future capacity planning.

Check the operating systems of all systems involved with user performance.

Check for hardware or operating system resources that are fully utilized. List any overused resources for possible later analysis. In addition, ensure that all hardware is functioning properly.

Ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL (default) or ALL to enable the automatic performance tuning features of Oracle Database, including AWR and ADDM.

Ensure that the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter is set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable ADDM.

See Also:

"Gathering Database Statistics Using the Automatic Workload Repository" for information about configuring AWR

"Configuring Automatic Database Diagnostic Monitor"

Tuning the Database Proactively

This section lists and describes the proactive steps required to keep the database properly tuned on a regular basis. Perform these steps as part of your daily maintenance of Oracle Database. Repeat the tuning process until your performance goals are met or become impossible to achieve because of other constraints.

To tune the database proactively: 

Review the ADDM findings, as described in Chapter 3, "Automatic Database Performance Monitoring".

ADDM automatically detects and reports on performance problems with the database, including most of the "Common Performance Problems Found in Oracle Databases". The results are displayed as ADDM findings on the Database Home page in Oracle Enterprise Manager (Enterprise Manager). Reviewing these findings enables you to quickly identify the performance problems that require your attention.

Implement the ADDM recommendations, as described in Chapter 3, "Automatic Database Performance Monitoring".

With each ADDM finding, ADDM automatically provides a list of recommendations for reducing the impact of the performance problem. Implementing a recommendation applies the suggested changes to improve the database performance.

Monitor performance problems with the database in real time, as described in Chapter 4, "Monitoring Real-Time Database Performance".

The Performance page in Enterprise Manager enables you to identify and respond to real-time performance problems. By drilling down to the appropriate pages, you can identify and resolve performance problems with the database in real time, without having to wait until the next ADDM analysis.

Respond to performance-related alerts, as described in Chapter 5, "Monitoring Performance Alerts".

The Database Home page in Enterprise Manager displays performance-related alerts generated by the database. Typically, resolving the problems indicated by these alerts improves database performance.

Validate that any changes made have produced the desired effect, and verify that the users experience performance improvements.

Tuning the Database Reactively

This section lists and describes the steps required to tune the database based on user feedback. This tuning procedure is considered reactive. Perform this procedure periodically when performance problems are reported by the users.

To tune the database reactively: 

Run ADDM manually to diagnose current and historical database performance when performance problems are reported by the users, as described in Chapter 6, "Manual Database Performance Monitoring".

In this way you can analyze current database performance before the next ADDM analysis, or analyze historical database performance when you were not proactively monitoring the system.

Resolve transient performance problems, as described in Chapter 7, "Resolving Transient Performance Problems".

The Active Session History (ASH) reports enable you to analyze transient performance problems with the database that are short-lived and do not appear in the ADDM analysis.

Resolve performance degradation over time, as described in Chapter 8, "Resolving Performance Degradation Over Time".

The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time, and resolve performance degradation that may happen from one time period to another.

Validate that the changes made have produced the desired effect, and verify that the users experience performance improvements.

Repeat these steps until your performance goals are met or become impossible to achieve due to other constraints.

Tuning SQL Statements

This section lists and describes the steps required to identify, tune, and optimize high-load SQL statements.

To tune SQL statements: 

Identify high-load SQL statements, as described in Chapter 9, "Identifying High-Load SQL Statements".

Use the ADDM findings and the Top SQL section to identify high-load SQL statements that are causing the greatest contention.

Tune high-load SQL statements, as described in Chapter 10, "Tuning SQL Statements".

You can improve the efficiency of high-load SQL statements by tuning them using SQL Tuning Advisor.

Optimize data access paths, as described in Chapter 11, "Optimizing Data Access Paths".

You can optimize the performance of data access paths by creating the proper set of materialized views, materialized view logs, and indexes for a given workload by using SQL Access Advisor.

Analyze the SQL performance impact of SQL tuning and other system changes by using SQL Performance Analyzer.

To learn how to use SQL Performance Analyzer, see Oracle Database Real Application Testing User's Guide.

Repeat these steps until all high-load SQL statements are tuned for greatest efficiency.

Common Performance Problems Found in Oracle Databases

This section lists and describes common performance problems found in Oracle databases. By following the Oracle performance method, you should be able to avoid these problems. If you experience these problems, then repeat the steps in the Oracle performance method, as described in "Using the Oracle Performance Method", or consult the appropriate section that addresses these problems:

CPU bottlenecks

Is the application performing poorly because the system is CPU-bound? Performance problems caused by CPU bottlenecks are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify CPU bottlenecks by using the Performance page in Enterprise Manager, as described in "Monitoring CPU Utilization".

Undersized memory structures

Are the Oracle memory structures such as the System Global Area (SGA), Program Global Area (PGA), and buffer cache adequately sized? Performance problems caused by undersized memory structures are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify memory usage issues by using the Performance page in Enterprise Manager, as described in "Monitoring Memory Utilization".

I/O capacity issues

Is the I/O subsystem performing as expected? Performance problems caused by I/O capacity issues are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify disk I/O issues by using the Performance page in Oracle Enterprise Manager, as described in "Monitoring Disk I/O Utilization".

Suboptimal use of Oracle Database by the application

Is the application making suboptimal use of Oracle Database? Problems such as establishing new database connections repeatedly, excessive SQL parsing, and high levels of contention for a small amount of data (also known as application-level block contention) can degrade the application performance significantly. Performance problems caused by suboptimal use of Oracle Database by the application are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also monitor top activity in various dimensions—including SQL, session, services, modules, and actions—by using the Performance page in Enterprise Manager, as described in "Monitoring User Activity".

Concurrency issues

Is the database performing suboptimally due to a high degree of concurrent activities in the database? A high degree of concurrent activities might result in contention for shared resources that can manifest in the forms of locks or waits for buffer cache. Performance problems caused by concurrency issues are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify concurrency issues by using Top Sessions in Enterprise Manager, as described in "Monitoring Top Sessions".

Database configuration issues

Is the database configured optimally to provide desired performance levels? For example, is there evidence of incorrect sizing of log files, archiving issues, too many checkpoints, or suboptimal parameter settings? Performance problems caused by database configuration issues are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring".

Short-lived performance problems

Are users complaining about short-lived or intermittent performance problems? Depending on the interval between snapshots taken by AWR, performance problems that have a short duration may not be captured by ADDM. You can identify short-lived performance problems by using the Active Session History report, as described in Chapter 7, "Resolving Transient Performance Problems".

Degradation of database performance over time

Is there evidence that the database performance has degraded over time? For example, are you or your users noticing that the database is not performing as well as it was 6 months ago? You can generate an AWR Compare Periods report to compare the period when the performance was poor to a period when the performance is stable to identify configuration settings, workload profile, and statistics that are different between these two time periods. This technique helps you identify the cause of the performance degradation, as described in Chapter 8, "Resolving Performance Degradation Over Time".

Inefficient or high-load SQL statements

Are any SQL statements using excessive system resources that impact the system? Performance problems caused by high-load SQL statements are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring" and "Identification of High-Load SQL Statements Using ADDM Findings". You can also identify high-load SQL statements by using Top SQL in Enterprise Manager, as described in "Identifying High-Load SQL Statements Using Top SQL". After they have been identified, you can tune the high-load SQL statements using SQL Tuning Advisor, as described in Chapter 10, "Tuning SQL Statements".

Object contention

Are any database objects the source of bottlenecks because they are continuously accessed? Performance problems caused by object contention are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also optimize the data access path to these objects using SQL Access Advisor, as described in Chapter 11, "Optimizing Data Access Paths".

Unexpected performance regression after tuning SQL statements

Is the performance of SQL statements degrading after they have been tuned? Tuning SQL statements may cause changes to their execution plans, resulting in a significant impact on SQL performance. In some cases, the changes may result in the improvement of SQL performance. In other cases, the changes may cause SQL statements to regress, resulting in a degradation of SQL performance.

Before making changes on a production system, you can analyze the impact of SQL tuning on a test system by using SQL Performance Analyzer. This feature enables you to forecast the impact of system changes on a SQL workload by:

Measuring the performance before and after the change

Generating a report that describes the change in performance

Identifying the SQL statements that regressed or improved

Providing tuning recommendations for each SQL statement that regressed

Enabling you to implement the tuning recommendations when appropriate

To learn how to use SQL Performance Analyzer, see Oracle Database Real Application Testing User's Guide.

Part II

Proactive Database Tuning

Part II describes how to tune Oracle Database proactively on a regular basis and contains the following chapters:

Chapter 3, "Automatic Database Performance Monitoring"

Chapter 4, "Monitoring Real-Time Database Performance"

Chapter 5, "Monitoring Performance Alerts"

3 Automatic Database Performance Monitoring

Automatic Database Diagnostic Monitor (ADDM) automatically detects and reports performance problems with the database. The results are displayed as ADDM findings on the Database Home page in Oracle Enterprise Manager (Enterprise Manager). Reviewing the ADDM findings enables you to quickly identify the performance problems that require your attention.

Each ADDM finding provides a list of recommendations for reducing the impact of the performance problem. You should review ADDM findings and implement the recommendations every day as part of regular database maintenance. Even when the database is operating at an optimal performance level, you should continue to use ADDM to monitor database performance on an ongoing basis.

This chapter contains the following sections:

Overview of Automatic Database Diagnostic Monitor

Configuring Automatic Database Diagnostic Monitor

Reviewing the Automatic Database Diagnostic Monitor Analysis

Interpretation of Automatic Database Diagnostic Monitor Findings

Implementing Automatic Database Diagnostic Monitor Recommendations

Viewing Snapshot Statistics

See Also:

Oracle Database Performance Tuning Guide for information about using the DBMS_ADVISOR package to diagnose and tune the database with the Automatic Database Diagnostic Monitor

Overview of Automatic Database Diagnostic Monitor

ADDM is self-diagnostic software built into Oracle Database. ADDM examines and analyzes data captured in Automatic Workload Repository (AWR) to determine possible database performance problems. ADDM then locates the root causes of the performance problems, provides recommendations for correcting them, and quantifies the expected benefits. ADDM also identifies areas where no action is necessary.

This section contains the following topics:

ADDM Analysis

ADDM Recommendations

ADDM for Oracle Real Application Clusters

ADDM Analysis

An ADDM analysis is performed after each AWR snapshot (every hour by default), and the results are saved in the database. You can then view the results using Enterprise Manager. Before using another performance tuning method described in this guide, review the results of the ADDM analysis first.

The ADDM analysis is performed from the top down, first identifying symptoms and then refining the analysis to reach the root causes of performance problems. ADDM uses the DB time statistic to identify performance problems. DB time is the cumulative time spent by the database in processing user requests, including both the wait time and CPU time of all user sessions that are not idle.

The goal of database performance tuning is to reduce the DB time of the system for a given workload. By reducing DB time, the database can support more user requests by using the same or fewer resources. ADDM reports system resources that are using a significant portion of DB time as problem areas and sorts them in descending order by the amount of related DB time spent. For more information about the DB time statistic, see "Time Model Statistics".

ADDM Recommendations

In addition to diagnosing performance problems, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions from which you can choose. ADDM recommendations include the following:

Hardware changes

Adding CPUs or changing the I/O subsystem configuration

Database configuration

Changing initialization parameter settings

Schema changes

Hash partitioning a table or index, or using automatic segment space management (ASSM)

Application changes

Using the cache option for sequences or using bind variables

Using other advisors

Running SQL Tuning Advisor on high-load SQL statements or running the Segment Advisor on hot objects

ADDM benefits apply beyond production systems. Even on development and test systems, ADDM can provide an early warning of potential performance problems.

Performance tuning is an iterative process. Fixing one problem can cause a bottleneck to shift to another part of the system. Even with the benefit of the ADDM analysis, it can take multiple tuning cycles to reach a desirable level of performance.

See Also:

Oracle Database 2 Day DBA for information the Segment Advisor

ADDM for Oracle Real Application Clusters

In an Oracle Real Application Clusters (Oracle RAC) environment, you can use ADDM to analyze the throughput performance of a database cluster. ADDM for Oracle RAC considers DB time as the sum of database times for all database instances and reports findings that are significant at the cluster level. For example, the DB time of each cluster node may be insignificant when considered individually, but the aggregate DB time may be a significant problem for the cluster as a whole.

See Also:

Oracle Database 2 Day + Real Application Clusters Guide for information about using ADDM for Oracle RAC

Configuring Automatic Database Diagnostic Monitor

This section contains the following topics:

Setting Initialization Parameters to Enable ADDM

Setting the DBIO_EXPECTED Parameter

Managing AWR Snapshots

Setting Initialization Parameters to Enable ADDM

Automatic database diagnostic monitoring is enabled by default and is controlled by the CONTROL_MANAGEMENT_PACK_ACCESS and the STATISTICS_LEVEL initialization parameters.

Set CONTROL_MANAGEMENT_PACK_ACCESS to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables many Oracle Database features, including ADDM, and is strongly discouraged.

Set STATISTICS_LEVEL to TYPICAL (default) or ALL to enable automatic database diagnostic monitoring. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including ADDM, and is strongly discouraged.

To determine whether ADDM is enabled: 

From the Database Home page, click Server.

The Server subpage appears.

In the Database Configuration section, click Initialization Parameters.

The Initialization Parameters page appears.

In the Name field, enter statistics_level and then click Go.

The table shows the setting of this initialization parameter.

Description of statistics_level.gif follows
Description of the illustration statistics_level.gif

Do one of the following:

If the Value list shows ALL or TYPICAL, then do nothing.

If the Value list shows BASIC, then select ALL or TYPICAL, and then click Apply.

In the Name field, enter control_management_pack_access, and then click Go.

The table shows the setting of this initialization parameter.

Do one of the following:

If the Value column shows DIAGNOSTIC or DIAGNOSTIC+TUNING, then do nothing.

If the Value column shows NONE, then select DIAGNOSTIC or DIAGNOSTIC+TUNING and click Apply.

See Also:

Oracle Database Reference for information about the STATISTICS_LEVEL initialization parameter

Oracle Database Reference for information about the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter

Setting the DBIO_EXPECTED Parameter

ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block, in microseconds. Oracle Database uses the default value of 10 milliseconds, which is an appropriate value for most hard drives. You can choose a different value based on the characteristics of your hardware.

To determine the correct setting for the DBIO_EXPECTED initialization parameter: 

Measure the average read time of a single database block for your hardware.

This measurement must be taken for random I/O, which includes seek time if you use standard hard drives. Typical values for hard drives are between 5000 and 20000 microseconds. See Oracle Database Performance Tuning Guide to learn how to assess the I/O capability of the storage subsystem.

Set the value one time for all subsequent ADDM executions.

For example, if the measured value is 8000 microseconds, then execute the following PL/SQL code as the SYS user:

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
                     'ADDM', 'DBIO_EXPECTED', 8000);

Managing AWR Snapshots

By default, the Automatic Workload Repository (AWR) generates snapshots of performance data once every hour, and retains the statistics in the workload repository for 8 days. You can change the default values for both the snapshot interval and the retention period.

Oracle recommends that you adjust the AWR retention period to at least one month. You can also extend the period to one business cycle so you can compare data across time frames such as the close of the fiscal quarter. You can also create AWR baselines to retain snapshots indefinitely for important time periods.

The data in the snapshot interval is analyzed by ADDM. ADDM compares the differences between snapshots to determine which SQL statements to capture, based on the effect on the system load. The ADDM analysis shows the number of SQL statements that need to be captured over time.

This section contains the following topics:

Creating Snapshots

Modifying Snapshot Settings

Creating Snapshots

Manually creating snapshots is usually not necessary because AWR generates snapshots of the performance data once every hour by default. In some cases, however, it may be necessary to manually create snapshots to capture different durations of activity, such as when you want to compare performance data over a shorter period than the snapshot interval.

To create snapshots: 

From the Database Home page, click Performance.

The Performance page appears.

Under Additional Monitoring Links, click Snapshots.

The Snapshots page appears with a list of the most recent snapshots.

Click Create.

The Confirmation page appears.

Click Yes.

The Processing: Create Snapshot page is displayed while the snapshot is being taken.

After the snapshot is taken, the Snapshots page reappears with a Confirmation message.

In the following example, the ID of the snapshot that was created is 96.

Description of snapshots.gif follows
Description of the illustration snapshots.gif

Modifying Snapshot Settings

By default, AWR generates snapshots of performance data once every hour. You can modify the default values of both the interval between snapshots and their retention period.

To modify the snapshot settings: 

From the Database Home page, click Server.

The Server subpage appears.

In the Statistics Management section, click Automatic Workload Repository.

The Automatic Workload Repository page appears.

Description of snapshot_settings.gif follows
Description of the illustration snapshot_settings.gif

In this example, snapshot retention is set to 8 days and snapshot interval is set to 60 minutes.

Click Edit.

The Edit Settings page appears.

Description of snap_edit_set.gif follows
Description of the illustration snap_edit_set.gif

For Snapshot Retention, do one of the following:

Select Use Time-Based Retention Period (Days), and in the associated field enter the number of days to retain the snapshots.

Select Retain Forever to retain snapshots indefinitely.

It is recommended that you increase the snapshot retention period to the maximum allowed by the available disk space.

In this example, the snapshot retention period is changed to 30 days.

Description of snapshot_retent.gif follows
Description of the illustration snapshot_retent.gif

For Snapshot Collection, do one of the following:

Select System Snapshot Interval, and in the Interval list, select the desired interval to change the interval between snapshots.

Select Turn off Snapshot Collection to disable snapshot collection.

In this example, the snapshot collection interval is changed to 30 minutes.

Description of snapshot_collect.gif follows
Description of the illustration snapshot_collect.gif

Click the link next to Collection Level.

The Initialization Parameter page appears.

To change the statistics level, select the desired value in the Value list for the statistics_level parameter. Click Save to File to set the value in the server parameter file.

In this example, the default value of Typical is used.

Description of snapshot_stat_lvl.gif follows
Description of the illustration snapshot_stat_lvl.gif

Click OK to apply the changes.

The Automatic Workload Repository page appears and displays the new settings.

Description of snapshot_sett_mod.gif follows
Description of the illustration snapshot_sett_mod.gif

Reviewing the Automatic Database Diagnostic Monitor Analysis

By default, ADDM runs every hour to analyze snapshots taken by AWR during that period. If the database finds performance problems, then it displays the results of the analysis under Diagnostic Summary on the Database Home page.

Description of diagnostic_sum.gif follows
Description of the illustration diagnostic_sum.gif

The ADDM Findings link shows how many ADDM findings were found in the most recent ADDM analysis.

To view ADDM findings: 

On the Database Home page, under Diagnostic Summary, click the link next to ADDM Findings.

The Automatic Database Diagnostic Monitor (ADDM) page appears. The results of the ADDM run are displayed.

Description of addm_page.gif follows
Description of the illustration addm_page.gif

On the Automatic Database Diagnostic Monitor (ADDM) page, the Database Activity chart shows the database activity during the ADDM analysis period. Database activity types are defined in the legend based on their corresponding colors in the chart. Each icon below the chart represents a different ADDM task, which in turn corresponds to a pair of snapshots saved in AWR.

In this example, the two largest blocks of activity were 2:30 p.m. to 5:30 p.m. on January 20 and 9 a.m. to 11 a.m. the next day. The thick CPU and thin Wait bars in the hour after 4:30 p.m. indicate that CPU may have been a bottleneck during this period. In other areas of the chart, the Wait bar is thicker than the CPU bar, indicating that wait events had a greater performance impact than CPU.

In the ADDM Performance Analysis section, ADDM findings are listed in descending order, from highest to least impact. The Informational Findings section lists areas that have no performance impact and are for information only.

Description of information_findings.gif follows
Description of the illustration information_findings.gif

Optionally, click the Zoom icons to shorten or lengthen the analysis period displayed on the chart.

To view the ADDM findings in a report, click View Report.

The View Report page appears.

You can click Save to File to save the report for later access.

Interpretation of Automatic Database Diagnostic Monitor Findings

The ADDM analysis results are represented as a set of findings. Each ADDM finding belongs to one of three types:

Problem

Findings that describe the root cause of a database performance issue

Symptom

Findings that contain information that often leads to one or more problem findings

Information

Findings that are used to report areas of the system that do not have a performance impact

Each problem finding is quantified with an estimate of the portion of DB time that resulted from the performance problem.

When a specific problem has multiple causes, ADDM may report multiple findings. In this case, the impacts of these multiple findings can contain the same portion of DB time. Because performance problems can overlap, summing the impacts of the reported findings can yield a number higher than 100% of DB time. For example, if a system performs many read I/O operations, ADDM may report a SQL statement responsible for 50% of DB time due to I/O activity as one finding, and an undersized buffer cache responsible for 75% of DB time as another finding.

A problem finding can be associated with a list of recommendations for reducing the impact of a performance problem. Each recommendation has a benefit that is an estimate of the portion of DB time that can be saved if the recommendation is implemented. When multiple recommendations are associated with an ADDM finding, the recommendations may contain alternatives for solving the same problem. In this case, the sum of the benefits may be higher than the impact of the finding. You do not need to apply all the recommendations to solve the same problem.

Recommendations are composed of actions and rationales. You must apply all the actions of a recommendation to gain its estimated benefit. The rationales explain why the set of actions was recommended, and provide additional information for implementing them. An ADDM action may present multiple solutions. If this is the case, then choose the easiest solution to implement.

Implementing Automatic Database Diagnostic Monitor Recommendations

This section describes how to implement ADDM recommendations. ADDM findings are displayed in the Automatic Database Diagnostic Monitor (ADDM) page under ADDM Performance Analysis.

Description of perf_analysis.gif follows
Description of the illustration perf_analysis.gif

To implement ADDM recommendations: 

On the Database Home page, under Diagnostic Summary, click the link next to ADDM Findings.

The Automatic Database Diagnostic Monitor (ADDM) page appears.

In the Database Activity section, click the icon for the ADDM to investigate.

The data in the ADDM Performance Analysis section changes based on the ADDM run that you selected.

In the ADDM Performance Analysis section, click the ADDM finding that has the greatest impact.

In this example, the finding with the greatest impact is Top SQL Statements.

The Performance Finding Details page appears.

In the following example, three recommendations are shown. The first is estimated to have a maximum benefit of up to 39.5% of DB time in the analysis period. The second recommendation is estimated to have a maximum benefit of up to 25.6% of DB time, while the third has a maximum of 18.6%.

Description of perf_finding_details.gif follows
Description of the illustration perf_finding_details.gif

Under Recommendations, click Show to review the recommendations and required actions for each recommendation.

The Category column displays the category of the recommendation. The Benefit (%) column displays the estimated benefit of implementing the recommendation.

Description of addm_rec.gif follows
Description of the illustration addm_rec.gif

If additional information is available about why the set of actions was recommended, then click Additional Information, or review the content displayed under Additional Information.

For example, the Undersized Buffer Cache finding contains additional information to indicate the recommended value of the DB_CACHE_SIZE initialization parameter.

Description of addm_addit_info.gif follows
Description of the illustration addm_addit_info.gif

To view the history of a finding, click Finding History.

The Finding History page appears.

Description of finding_history.gif follows
Description of the illustration finding_history.gif

The Finding History page shows how often a particular finding has occurred in a selected 3-hour interval. You can use this information to determine whether the finding was a transient or a persistent problem in the system. Based on this information, you can determine whether the actions associated with the finding should be implemented.

The Active Sessions chart shows the impact of the finding and of the other loads on the system. You can change the display as follows:

To move the 3-hour interval, click and drag the shaded box in the Active Sessions chart.

To change dates, enter the desired date in the View field, and then click Go.

To view details about a finding, under Detail for Selected 3 Hour Interval, click the link in the Finding Details column to display the Performance Finding Details page for the corresponding ADDM finding.

Optionally, create a filter to suppress known findings that have been tuned or cannot be tuned further. To create filters for a selected ADDM finding:

Click Filters.

The Filters for Finding page appears.

Click Create.

The Create Filter for Finding page appears.

In the Name field, enter a name for the ADDM filter.

In the Active Sessions field, specify the filter criteria in terms of the number of active sessions.

The database filters the ADDM finding for future ADDM runs if the number of active sessions for the finding is less than the specified filter criteria.

In the % Active Sessions field, specify the filter criteria in terms of percentage of active sessions.

The database filters the ADDM finding for future ADDM runs if the number of active sessions for the finding is less than the specified filter criteria.

Click OK.

Perform the required action of a chosen recommendation.

Depending on the type of action you choose to perform, various options may be available, such as Implement or Run Advisor Now. These options enable you to implement the recommendation immediately with a single mouse click.

In the example shown in Step 4, the simplest solution is to click Run Advisor Now to immediately run a SQL Tuning Advisor task on the SQL statement.

See Also:

Chapter 10, "Tuning SQL Statements"

Viewing Snapshot Statistics

You can view the data contained in snapshots taken by AWR using Enterprise Manager. Typically, it is not necessary to review snapshot data because it primarily contains raw statistics. Instead, rely on ADDM, which analyzes statistics to identify performance problems. Snapshot statistics are intended primarily for advanced users, DBAs accustomed to using Statspack for performance analysis.

To view snapshot statistics: 

From the Database Home page, click Performance.

The Performance page appears.

Under Additional Monitoring Links, click Snapshots.

The Snapshots page appears with a list of the most recent snapshots.

To view the statistics gathered in a snapshot, click the ID link of the snapshot you want to view.

The Snapshot Details appears, showing the Details subpage.

Description of snapshot_det.gif follows
Description of the illustration snapshot_det.gif

In this example, statistics gathered from the previous snapshot (snapshot 100) to the selected snapshot (snapshot 101) are displayed.

To view a Workload Repository report of the statistics, click Report.

The Workload Repository report appears.

Optionally, click Save to File to save the report for later access.

See Also:

Chapter 8, "Resolving Performance Degradation Over Time"

4 Monitoring Real-Time Database Performance

The Performance page in Oracle Enterprise Manager (Enterprise Manager) displays information in three sections that you can use to assess the overall performance of the database in real time.

Figure 4-1 shows the Performance page.

Figure 4-1 Performance Page

Description of Figure 4-1 follows
Description of "Figure 4-1 Performance Page"

Typically, you should use the automatic diagnostic feature of Automatic Database Diagnostic Monitor (ADDM) to identify performance problems with the database, as described in Chapter 3, "Automatic Database Performance Monitoring". In some cases, you may want to monitor the database performance in real time to identify performance problems as they occur. For example, ADDM performs its analysis after each Automatic Workload Repository (AWR) snapshot, which by default is once every hour. However, if you notice a sudden spike in database activity on the Performance page, then you may want to investigate the incident before the next ADDM analysis.

By drilling down to other pages from the Performance page, you can identify database performance problems in real time. If you find a problem, then you can run ADDM manually to analyze it immediately without having to wait until the next ADDM analysis. To learn how to run ADDM manually, see "Manually Running ADDM to Analyze Current Database Performance".

This chapter contains the following sections:

Monitoring User Activity

Monitoring Instance Activity

Monitoring Host Activity

Determining the Cause of Spikes in Database Activity

Customizing the Database Performance Page

Monitoring User Activity

The Average Active Sessions chart of the Performance page shows the average load on the database. The average active sessions for a time period equals the total DB time of all sessions during this period divided by the elapsed time (wall clock time) for this period. The chart shows which active sessions are running on the CPU or waiting on an event.

Figure 4-2 Monitoring User Activity

Description of Figure 4-2 follows
Description of "Figure 4-2 Monitoring User Activity"

By following the performance method explained in Chapter 2, "Oracle Database Performance Method", you can drill down from the charts to identify the causes of instance-related performance issues and resolve them.

To monitor user activity: 

From the Database Home page, click Performance.

The Performance page appears.

Locate any sudden increases in the Average Active Sessions chart.

Each component shows the average number of active sessions in the specified state for the specified time. For example, if only one session were active, then the value .8 for CPU would mean that the session consumed CPU in 4 of 5 sampled seconds around the target time. The Maximum CPU equals the number of CPUs on the system. When the CPU Used value reaches the Maximum CPU line, the database instance is consuming 100 percent of CPU time on the host system.

The wait classes show how much database activity is consumed by waiting for a resource such as disk I/O. Values that use a larger block of active sessions represent bottlenecks caused by a particular wait class, as indicated by the corresponding color in the legend.

In the chart shown in Figure 4-2, the largest amount of activity after 3:35 p.m. appears in dark green and corresponds to the CPU Used wait class.

To identify each wait class, move your cursor over the block in the Average Active Sessions chart corresponding to the class.

The corresponding wait class is highlighted in the chart legend.

Click the largest block of color on the chart or its corresponding wait class in the legend to drill down to the wait class with the most active sessions.

If you click CPU Used, then the Active Sessions Working page for the wait class appears. If you click a different wait class, such as User I/O, then the Active Sessions Waiting page appears.

Figure 4-3 Active Sessions Working page

Description of Figure 4-3 follows
Description of "Figure 4-3 Active Sessions Working page"

The Active Sessions Working page shows a 1-hour timeline. Details for each wait class are shown in 5-minute intervals under Detail for Selected 5 Minute Interval.

You can view the details of wait classes in different dimensions by proceeding to one of the following sections:

"Monitoring Top SQL"

"Monitoring Top Sessions"

"Monitoring Top Services"

"Monitoring Top Modules"

"Monitoring Top Actions"

"Monitoring Top Clients"

"Monitoring Top PL/SQL"

"Monitoring Top Files"

"Monitoring Top Objects"

To change the selected time interval, move the slider below the chart to a different interval.

The information contained in the Detail for Selected 5 Minute Interval section is automatically updated to display the selected time period.

In the example shown in Figure 4-3, the 5 -minute interval from 5:03 to 5:08 is selected for the CPU Used wait class.

If you discover a performance problem, then you can attempt to resolve it in real time. On the Performance page, do one of the following:

Below the chart, click the snapshot corresponding to the time when the performance problem occurred to run ADDM for this time period.

For information about ADDM analysis, see "Reviewing the Automatic Database Diagnostic Monitor Analysis".

Click Run ADDM Now to create a snapshot manually.

For information about creating snapshots manually, see "Creating Snapshots". For information about running ADDM manually, see "Manually Running ADDM to Analyze Current Database Performance".

Click Run ASH Report to create an Active Session History (ASH) report to analyze transient, short-lived performance problems.

For information about ASH reports, see "Active Session History Reports".

Monitoring Top SQL

On the Active Sessions Working page, the Top Working SQL table shows the database activity for actively running SQL statements that are consuming CPU resources. The Activity (%) column shows the percentage of this activity consumed by each SQL statement. If one or several SQL statements are consuming most of the activity, then you should investigate them.

Figure 4-4 Monitoring Top SQL

Description of Figure 4-4 follows
Description of "Figure 4-4 Monitoring Top SQL"

In the example shown in Figure 4-4, a single SELECT statement is consuming over 47% of database activity, while four modification DML statements are consuming about 35%. These statements should be investigated.

To monitor the top working SQL statements: 

On the Performance page, in the Average Active Sessions chart, click the CPU block on the chart or its corresponding wait class in the legend.

The Active Sessions Working page appears.

In the Top Working SQL table, click the SQL ID link of the most active SQL statement.

The SQL Details page appears.

For SQL statements that are using the majority of the wait time, use SQL Tuning Advisor or create a SQL tuning set to tune the problematic SQL statements.

See Also:

"Viewing Details of SQL Statements"

"Tuning SQL Statements Using SQL Tuning Advisor"

Monitoring Top Sessions

On the Active Sessions Working page, the Top Working Sessions table displays the top sessions waiting for the corresponding wait class during the selected time period. A session is a logical entity in the database instance memory that represents the state of a current user login to the database.

Figure 4-5 Monitoring Top Sessions

Description of Figure 4-5 follows
Description of "Figure 4-5 Monitoring Top Sessions"

A session lasts from the time a user logs in to the database until the user disconnects. For example, when a user starts SQL*Plus, the user must provide a valid database user name and password to establish a session. If a single session is consuming the majority of database activity, then you should investigate it.

To monitor the top working sessions: 

On the Performance page, in the Average Active Sessions chart, click the CPU Used block on the chart or its corresponding wait class in the legend.

The Active Sessions Working page appears.

Under Detail for Selected 5 Minute Interval, select Top Sessions from the View list.

The Top Working Sessions table appears.

In the Top Working Sessions table, click the Session ID link of the session consuming the most database activity.

The Session Details page appears.

This page contains information such as session activity, session statistics, open cursors, blocking sessions, wait events, and parallel SQL for the selected session.

If a session is consuming too much database activity, then consider clicking Kill Session, and then tuning the session's SQL statement.

See Also:

Chapter 10, "Tuning SQL Statements"

Monitoring Top Services

The Top Services table displays the top services waiting for the corresponding wait event during the selected time period.

A service is a group of of applications with common attributes, service-level thresholds, and priorities. For example, the SYS$USERS service is the default service name used when a user session is established without explicitly identifying its service name. The SYS$BACKGROUND service consists of all database background processes. If a service is using the majority of the wait time, then you should investigate it.

To monitor a service: 

On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

The Active Sessions Working page appears.

Under Detail for Selected 5 Minute Interval, select Top Services from the View list.

The Top Services table appears.

Figure 4-6 Monitoring Top Services

Description of Figure 4-6 follows
Description of "Figure 4-6 Monitoring Top Services"

In the example shown in Figure 4-6, the SYS$USERS service is consuming 86.47% of database activity. This service corresponds to the database sessions for users hr and sh shown in Figure 4-5.

Click the Service link of the most active service.

The Service page appears.

This page contains information about the modules, activity, and statistics for the selected service.

Monitoring Top Modules

The Top Modules table displays the top modules waiting for the corresponding wait event during the selected time period.

Modules represent the applications that set the service name as part of the workload definition. For example, the DBMS_SCHEDULER module may assign jobs that run within the SYS$BACKGROUND service. If a single module is using the majority of the wait time, then it should be investigated.

To monitor a module: 

On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

The Active Sessions Working page appears.

Under Detail for Selected 5 Minute Interval, select Top Modules from the View list.

The Top Modules table appears.

Figure 4-7 Monitoring Top Modules

Description of Figure 4-7 follows
Description of "Figure 4-7 Monitoring Top Modules"

Click the Module link of the module that is showing the highest percentage of activity.

The Module page appears.

This page contains information about the actions, activity, and statistics for the selected module.

In the example shown in Figure 4-7, the SQL*Plus module is consuming over 84% of database activity and should be investigated. As shown in Figure 4-5, the SQL*Plus session for users sh and hr are consuming a huge percentage of database activity.

Monitoring Top Actions

The Top Actions table displays the top actions waiting for the corresponding wait event during the selected time period.

Actions represent the jobs that are performed by a module. For example, the DBMS_SCHEDULER module can run the GATHER_STATS_JOB action to gather statistics on all database objects. If a single action is using the majority of the wait time, then you should investigate it.

To monitor an action: 

On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

The Active Sessions Working page appears.

Under Detail for Selected 5 Minute Interval, select Top Actions from the View list.

The Top Actions table appears.

Figure 4-8 Monitoring Top Actions

Description of Figure 4-8 follows
Description of "Figure 4-8 Monitoring Top Actions"

Click the Action link of the most active action.

The Action page appears.

This page contains statistics for the selected action.

In the example shown in Figure 4-8, the SALES_INFO action associated with the SQL*Plus module is consuming 40.3% of the database activity, while EMP_DML is consuming 39.55% and EMP_QUERY is consuming 4.48%. This information is consistent with Figure 4-5, which shows that the two database sessions for users HR and SH are consuming over 84% of database activity.

Monitoring Top Clients

The Top Clients table displays the top clients waiting for the corresponding wait event during the selected time period. A client can be a Web browser or any client process that initiates requests for an operation to be performed by the database. If a single client is using the majority of the wait time, then you should investigate it.

To monitor a client: 

On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

The Active Sessions Working page appears.

Under Detail for Selected 5 Minute Interval, select Top Clients from the View list.

The Top Clients table appears.

Figure 4-9 Monitoring Top Clients

Description of Figure 4-9 follows
Description of "Figure 4-9 Monitoring Top Clients"

Click the Client ID link of the most active client.

The Clients page appears.

This page contains statistics for the selected client process.

Monitoring Top PL/SQL

The Top PL/SQL table displays the top PL/SQL subprograms waiting for the corresponding wait event during the selected time period. If a single PL/SQL subprogram is using the majority of the wait time, then you should investigate it.

To monitor a PL/SQL subprogram: 

On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

The Active Sessions Working page appears.

Under Detail for Selected 5 Minute Interval, select Top PL/SQL from the View list.

The Top PL/SQL table appears.

Figure 4-10 Monitoring Top PL/SQL

Description of Figure 4-10 follows
Description of "Figure 4-10 Monitoring Top PL/SQL"

Click the PL/SQL Subprogram link of the most active subprogram.

The PL/SQL Subprogram page appears.

This page contains statistics for the selected subprogram.

In Figure 4-10, the SYS.DBMS_AQ.LISTEN#2 subprogram is consuming 100% of database activity.

Monitoring Top Files

The Top Files table displays the average wait time for specific files during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.

To monitor a file: 

On the Performance page, in the Average Active Sessions chart, click the User I/O block on the chart or its corresponding wait class in the legend.

The Active Sessions Waiting: User I/O page appears.

Under Detail for Selected 5 Minute Interval, select Top Files from the View list.

The Top Files table appears.

Figure 4-11 Monitoring Top Files

Description of Figure 4-11 follows
Description of "Figure 4-11 Monitoring Top Files"

Click the Tablespace link of the file with the highest average wait time.

The View Tablespace page appears.

In the example shown in Figure 4-11, 75% of the wait times are associated with I/O to the files in the SYSTEM and SYSAUX tablespaces.

Monitoring Top Objects

The Top Objects table displays the top database objects waiting for the corresponding wait event during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.

To monitor an object: 

On the Performance page, in the Average Active Sessions chart, click the User I/O block on the chart or its corresponding wait class in the legend.

The Active Sessions Waiting: User I/O page appears.

Under Detail for Selected 5 Minute Interval, select Top Objects from the View list.

The Top Objects table appears.

Figure 4-12 Monitoring Top Objects

Description of Figure 4-12 follows
Description of "Figure 4-12 Monitoring Top Objects"

Click the Object Name link of the object with the highest average wait time.

The View page for the object appears.

This example in Figure 4-12 shows that over 84% of the waits are for an object whose name is unavailable. Based on the information in Figure 4-4 and Figure 4-5, you can conclude that the performance problem is caused by the query and modification DML statements.

Monitoring Instance Activity

In the Average Active Sessions section of the Performance page, you can use the instance charts to monitor database instance activity. As explained in "Customizing the Database Performance Page", you can also customize the Performance page so that the most useful charts are displayed by default.

You can use the instance activity charts to perform the following tasks:

Monitoring Throughput

Monitoring I/O

Monitoring Parallel Execution

Monitoring Services

Monitoring Throughput

Database throughput measures the amount of work the database performs in a unit of time. The Throughput charts show any contention that appears in the Average Active Sessions chart. The Throughput charts on the Performance page display:

Number of logons, transactions, physical reads, and redo size per second

Number of physical reads and redo size per transaction

Figure 4-13 Monitoring Throughput

Description of Figure 4-13 follows
Description of "Figure 4-13 Monitoring Throughput"

Compare the peaks on the Throughput charts with the peaks on the Average Active Sessions chart. If the Average Active Sessions chart displays a large number of sessions waiting, indicating internal contention, but throughput is high, then the situation may be acceptable. The database is probably also performing efficiently if internal contention is low but throughput is high. However, if internal contention is high but throughput is low, then consider tuning the database.

To monitor throughput: 

From the Database Home page, click Performance.

The Performance page appears.

In the instance activity chart, click Throughput.

The Throughput charts are shown with Instance Throughput Rate set to the default value of Per Second. You can select Per Transaction to show the throughput rate per transaction.

In the example in shown in Figure 4-13, the number of transactions and physical reads per second went up around 12:30 p.m. and remained up until around 5 p.m.

Monitoring I/O

The I/O charts show I/O statistics collected from all database clients. The I/O wait time for a database process represents the amount of time that the process could have been doing useful work if a pending I/O had completed. Oracle Database captures the I/O wait times for all important I/O components in a uniform fashion so that every I/O wait by any Oracle process can be derived from the I/O statistics.

Figure 4-14 Monitoring I/O

Description of Figure 4-14 follows
Description of "Figure 4-14 Monitoring I/O"

The Latency for Synchronous Single Block Reads chart shows the total perceived I/O latency for a block read, which is the time difference between when an I/O request is submitted and when the first byte of the transfer arrives. Most systems are performing satisfactorily if latency is fewer than 10 milliseconds. This type of I/O request is the best indicator of I/O performance for the following reasons:

Write operations may exhibit good performance because of write caches in storage.

Because multiblock I/O requests have varying sizes, they can take different amounts of time.

The latency of asynchronous I/O requests does not represent the full I/O wait time.

The other charts shown depend on your selection for I/O Breakdown, as described in the following sections:

Monitoring I/O by Function

Monitoring I/O by Type

Monitoring I/O by Consumer Group

Monitoring I/O by Function

The I/O Function charts determine I/O usage level by application or job. The component-level statistics give a detailed view of the I/O bandwidth usage, which you can then use in scheduling jobs and I/O provisioning. The component-level statistics fall in the following categories:

Background type

This category includes ARCH, LGWR, and DBWR.

Activity

This category includes XML DB, Streams AQ, Data Pump, Recovery, and RMAN.

I/O type

The category includes the following:

Direct Write

This write is made by a foreground process and is not from the buffer cache.

Direct Read

This read is physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.

Buffer Cache Read

Others

This category includes I/Os such as control file I/Os.

To monitor I/O by function: 

From the Database Home page, click Performance.

The Performance page appears.

In the instance activity chart, click I/O.

The I/O Megabytes per Second and I/O Requests per Second charts appear.

For I/O Breakdown, select I/O Function.

The I/O Megabytes per Second by I/O Function and I/O Requests per Second by I/O Function charts appear.

The example in Figure 4-14 shows that a significant amount of I/O is being performed by the log writer. The log writer activity peaked at approximately 500 I/O requests per second.

Click the largest block on the chart or its corresponding function in the legend to drill down to the function with the highest I/O rate.

The I/O Details page appears.

You can view real-time or historical data for details on I/O megabytes or I/O requests.

See Also:

Oracle Database Concepts to learn about database background processes such as ARCH, LGWR, and DBWR

Monitoring I/O by Type

The I/O Type charts enable you to monitor I/O by the types of read and write operations. Small I/Os are requests smaller than 128 KB and are typically single database block I/O operations. Large I/Os are requests greater than or equal to 128 KB. Large I/Os are generated by database operations such as table/index scans, direct data loads, backups, restores, and archiving.

Description of perf_io_type.gif follows
Description of the illustration perf_io_type.gif

When optimizing for short transaction times, such as in an OLTP environment, monitor latency for small I/Os. High latencies typically indicate that the storage system is a bottleneck.

When optimizing for large queries, such as in a data warehouse, performance depends on the maximum throughput the storage system can achieve rather than the latency of the I/O requests. In this case, monitor the I/O megabytes per second rather than the synchronous single-block I/O latencies.

To monitor I/O by type: 

From the Database Home page, click Performance.

The Performance page appears.

In the instance activity chart, click I/O.

The I/O Megabytes per Second and I/O Requests per Second charts appear.

For I/O Breakdown, select I/O Type.

The I/O Megabytes per Second by I/O Type and I/O Requests per Second by I/O Type charts appear.

In this example, the number of small writes per second increased to more than 600. These writes correspond to the log writer I/O requests shown in Figure 4-14.

Click the largest block on the chart or its corresponding function in the legend to drill down to the function with the highest I/O rate.

The I/O Details page appears.

You can view real-time or historical data for details on I/O megabytes or I/O requests.

Monitoring I/O by Consumer Group

When Oracle Database Resource Manager is enabled, the database collects I/O statistics for all consumer groups that are part of the currently enabled resource plan. The Consumer Group charts enable you to monitor I/O by consumer group.

A resource plan specifies how the resources are to be distributed among various users (resource consumer groups). Resource consumer groups enable you to organize user sessions by resource requirements. Note that the _ORACLE_BACKGROUND_GROUP_ consumer group contains I/O requests issued by background processes.

Description of perf_io_consum.gif follows
Description of the illustration perf_io_consum.gif

To monitor I/O requests by consumer group: 

From the Database Home page, click Performance.

The Performance page appears.

In the instance activity chart, click I/O.

The I/O Megabytes per Second and I/O Requests per Second charts appear.

For I/O Breakdown, select Consumer Group.

The I/O Megabytes per Second by Consumer Group and I/O Requests per Second by Consumer Group charts appear.

Monitoring Parallel Execution

The Parallel Execution charts show system metrics related to parallel queries. Metrics are statistical counts per unit. The unit could be a time measure, such as seconds, or per transaction, or session.

A parallel query divides the work of executing a SQL statement across multiple processes. The charts show parallel queries that were waiting for a particular wait event that accounted for the highest percentages of sampled session activity.

Figure 4-15 Monitoring Parallel Execution

Description of Figure 4-15 follows
Description of "Figure 4-15 Monitoring Parallel Execution"

To monitor parallel execution: 

From the Database Home page, click Performance.

The Performance page appears.

In the instance activity chart, click Parallel Execution.

The Parallel Execution charts appear.

Two pairs of charts are shown. The first pair shows the number of sessions on the y-axis, whereas the second pair shows the per second rate on the y-axis.

In the example shown in Figure 4-15, query parallelization was active between 12:30 p.m. to 4 p.m.

Monitoring Services

The Services charts show services waiting for the corresponding wait event during the time period shown. Services represent groups of applications with common attributes, service-level thresholds, and priorities. For example, the SYS$USERS service is the default service name used when a user session is established without explicitly identifying its service name. Only active services are shown.

Figure 4-16 Monitoring Services

Description of Figure 4-16 follows
Description of "Figure 4-16 Monitoring Services"

To monitor services: 

From the Database Home page, click Performance.

The Performance page appears.

In the instance activity chart, click Services.

The Services chart appears.

In Figure 4-16, the SYS$USERS service has the greatest number of active sessions.

Click the largest block of color on the chart or its corresponding service in the legend to drill down to the service with the highest number of active sessions.

The Service page appears, showing the Activity subpage.

You can view real-time data showing the session load for all wait classes associated with the service.

Monitoring Host Activity

The Host chart on the Performance page displays utilization information about the system hosting the database.

Figure 4-17 Monitoring Host Activity

Description of Figure 4-17 follows
Description of "Figure 4-17 Monitoring Host Activity"

To determine if the host system has enough resources available to run the database, establish appropriate expectations for the amount of CPU, memory, and disk resources that your system should be using. You can then verify that the database is not consuming too many of these resources.

To view details about CPU, memory, and disk utilization: 

From the Database Home page, under Host CPU, click the Load link.

Description of host_cpu.gif follows
Description of the illustration host_cpu.gif

The Host page appears, showing the Performance subpage.

Figure 4-18 Performance Summary

Description of Figure 4-18 follows
Description of "Figure 4-18 Performance Summary"

The Performance Summary view is shown by default. The Performance Summary view displays metric values for CPU utilization, memory utilization, disk I/O utilization, and the top 10 processes ordered by both CPU and memory utilization.

Determine whether sufficient resources are available and whether your system is using too many resources.

For example, determine the amount of CPU, memory, and disk resources the database uses in the following scenarios:

When your system is idle, or when little database and nondatabase activity exists

At average workloads

At peak workloads

Workload is an important factor when evaluating the level of resource utilization for your system. During peak workload hours, 90 percent utilization of a resource, such as a CPU with 10 percent idle and waiting time, can be acceptable. However, if your system shows high utilization at normal workload, then there is no room for additional workload.

Perform the following tasks to monitor the host activity for your database:

Monitoring CPU Utilization

Monitoring Memory Utilization

Monitoring Disk I/O Utilization

Set the appropriate threshold values for the performance metrics so the system can automatically generate alerts when these thresholds are exceeded.

For information about setting metric thresholds, see "Setting Metric Thresholds for Performance Alerts".

Monitoring CPU Utilization

To address CPU problems, first establish appropriate expectations for the amount of CPU resources your system should be using. You can then determine whether sufficient CPU resources are available and recognize when your system is consuming too many resources. This section describes how to monitor CPU utilization.

To monitor CPU utilization: 

From the Database Home page, under Host CPU, click the Load link.

The Host page appears, showing the Performance subpage.

Select CPU Details from the View list.

The CPU Details view appears.

This view contains statistics about CPU utilization, I/O wait times, and load gathered over the last hour. The top 10 processes are listed based on CPU utilization.

Verify the current CPU utilization using the CPU Utilization chart.

The CPU Utilization chart shows CPU utilization over the last hour. The current value is displayed below the chart. During standard workload hours, the value should not exceed the critical threshold.

Click CPU Utilization.

The CPU Utilization page appears.

This page contains CPU utilization statistics and related alerts generated over the last 24 hours.

In the following example, the CPU utilization has suddenly spiked from approximately 6% to 99.86%, which is above the warning threshold of 80%.

Description of cpu_util.gif follows
Description of the illustration cpu_util.gif

If you notice an unexpected spike in this value that is sustained through normal workload hours, then the CPU performance problem should be investigated.

Verify the current CPU I/O wait time using the CPU I/O Wait chart.

The CPU I/O Wait chart shows CPU I/O wait time over the last hour. The current value is displayed below the chart. During normal workload hours, the value of CPU I/O wait should not exceed the warning threshold.

CPU I/O wait represents the average number of jobs waiting for I/O during an interval.

Click CPU I/O Wait.

The CPU in I/O Wait page appears.

This page contains CPU I/O wait statistics and related alerts generated over the last 24 hours.

If you notice an unexpected increase in this value that is sustained through standard workload hours, then a CPU performance problem may exist.

Verify the current CPU load using the CPU Load chart.

The CPU Load chart shows the CPU load over the last hour. The current value is displayed below the chart. During standard workload hours, the value of CPU load should not exceed the warning threshold.

CPU load represents the average number of processes waiting to be scheduled for CPU resources in the previous minute, or the level of CPU contention time over time.

Click CPU Load.

The Run Queue Length page appears.

This page contains CPU load statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a CPU performance problem might exist.

Return to the CPU Details view of the Host Performance subpage and review the Top 10 Processes table.

If a process is consuming too much of the CPU utilization percentage, then this process should be investigated.

In the following example, two database processes are consuming 87.6% of CPU utilization. Therefore, the database is the likely source of a potential CPU performance problem and should be investigated.

Description of top_10_proc.gif follows
Description of the illustration top_10_proc.gif

If a CPU performance problem is identified, then you can try to resolve the issue by doing the following:

Use Oracle Database Resource Manager to reduce the impact of peak-load-use patterns by prioritizing CPU resource allocation

Avoid running too many processes that use a large amount of CPU

Increase hardware capacity, including changing the system architecture

See Also:

Oracle Database Performance Tuning Guide for information about resolving CPU issues

Oracle Database Administrator's Guide for information about Oracle Database Resource Manager

Monitoring Memory Utilization

Operating system performance issues commonly involve process management, memory management, and scheduling. This section describes how to monitor memory utilization and identify problems such as paging and swapping.

To monitor memory utilization: 

From the Database Home page, under Host CPU, click the Load link.

The Host page appears, showing the Performance subpage.

Select Memory Details from the View list.

The Memory Details view of the Performance subpage appears.

This view contains statistics about memory utilization, page scan rates, and swap utilization gathered over the last hour. The top 10 processes are also listed ordered by memory utilization.

Description of mem_details.gif follows
Description of the illustration mem_details.gif

Verify the current memory page scan rate using the Memory Page Scan Rate chart.

The current value of the memory page scan rate is displayed below the chart. On UNIX and Linux, this value represents the number of pages scanned per second. On Microsoft Windows, this value represents the rate at which pages are read from or written to disk to resolve hard page faults. This value is a primary indicator of the kinds of faults that may be causing systemwide delays.

Click Memory Scan Rate.

The Memory Page Scan Rate page appears.

This page contains memory page scan rate statistics and related alerts over the last 24 hours.

If you notice an unexpected increase in this value that is sustained through standard workload hours, then a memory performance problem might exist.

Verify the current memory utilization using the Memory Utilization chart.

The Memory Utilization chart shows how much memory is being used. The current value of memory utilization is displayed below the chart. During standard workload hours, the value should not exceed the warning threshold (shown in yellow).

Click Memory Utilization.

The Memory Utilization page appears.

This page contains memory utilization statistics and related alerts generated over the last 24 hours.

Description of mem_util.gif follows
Description of the illustration mem_util.gif

In this example, memory utilization never exceeded 60%, so a warning was not generated.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a memory performance problem might exist.

Verify current swap utilization using the Swap Utilization chart.

The Swap Utilization chart shows how much swap space is being used. The current value of swap utilization is displayed below the chart. During normal workload hours, the value should not exceed the warning threshold.

Click Swap Utilization.

The Swap Utilization page appears.

This page contains swap utilization statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a memory performance problem might exist.

Return to the Memory Details view of the Host Performance subpage and review the top processes in the Top 10 Processes (ordered by Memory) table.

If a process is taking up too much memory, then this process should be investigated.

If a memory performance problem is identified, you can attempt to resolve the issue by doing the following:

Use Automatic Memory Management to automatically manage and distribute memory between the System Global Area (SGA) and the aggregate program global area (PGA aggregate).

Use the Memory Advisor to set SGA and PGA memory target values.

Use Automatic PGA Management to manage SQL memory execution.

Avoid running too many processes that consume large amounts of memory.

Reduce paging or swapping.

Reduce the number of open cursors and hard parsing with cursor sharing.

See Also:

Oracle Database Administrator's Guide for information about using Automatic Memory Management

Oracle Database 2 Day DBA for information about using the Memory Advisor

Oracle Database Performance Tuning Guide for information about resolving memory issues

Monitoring Disk I/O Utilization

Because the database resides on a set of disks, the performance of the I/O subsystem is very important to database performance. Important disk statistics include the disk I/Os per second and the length of the service times. These statistics show if the disk is performing optimally or if the storage system is being overworked. This section describes how to monitor disk I/O utilization.

To monitor disk I/O utilization: 

From the Database Home page, under Host CPU, click the Load link.

The Host page appears, showing the Performance subpage.

Select Disk Details from the View list.

The Disk Details view appears.

This view contains disk I/O utilization and service time statistics gathered over the last hour, and the top disk devices ordered by the percentage of time that they were in use.

Description of disk_details.gif follows
Description of the illustration disk_details.gif

Verify the current disk I/O utilization using the Disk I/O Utilization chart.

The Disk I/O Utilization chart shows how many disk I/Os are being performed per second. The current value for total I/Os per second is displayed below the chart.

Click Total I/Os per Second.

The Total Disk I/O Per Second page appears.

This page contains disk utilization statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through standard workload hours, then a disk I/O performance problem might exist and should be investigated.

Verify the current I/O service time using the Longest I/O Service Time chart.

The Longest I/O Service Time chart shows the longest service time for disk I/Os in milliseconds. The current value for longest I/O service time is displayed below the chart.

Click Longest I/O Service Time.

The Longest Service Time page appears.

This page contains I/O service time statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a disk I/O performance problem might exist and should be investigated.

On the Disk Details page, verify the disk devices in the Top Disk Devices table.

If a particular disk is busy a high percentage of the time, then this disk should be investigated.

In this example, the drives that host Oracle Database (xvda and xvda1) are only busy about 1.41 percent of the time, so no disk performance problem appears to exist.

Description of top_disk_devices.gif follows
Description of the illustration top_disk_devices.gif

If a disk I/O performance problem is identified, you can attempt to resolve the problem by doing the following:

Use Oracle Automatic Storage Management (Oracle ASM) to manage database storage.

Stripe everything across every disk to distribute I/O.

Move files such as archived redo logs and online redo logs to separate disks.

Store required data in memory to reduce the number of physical I/Os.

See Also:

Oracle Database Performance Tuning Guide for information about resolving disk I/O issues

Determining the Cause of Spikes in Database Activity

If you see a spike in database activity in the Performance page, you can access the ASH Analytics page to find out which sessions are consuming the most database time. This page provides a stacked chart to help you visualize the active session activity for several dimensions, such as Event, Activity Class, Module/Action, Session, Instance ID, and PL/SQL function. You can drill down into specific members of a dimension (vertical zooming), and zoom in and out of any time period (horizontal zooming).

From the Performance menu in the database instance, select ASH Analytics.

View a macro perspective of top activity during a selected time period by dragging the shaded area in the top chart to the desired time period.

Tip:

When you move your mouse over the shaded block area, two user controls on both ends of the slider appear that enable you to select the desired time period.

You can select a time period within the default setting of one hour, or you can use the selector buttons to display time periods of one day, one week, or one month. You can also use the Calendar and Custom buttons to display a time period other than one of the preset choices.

View a micro perspective of your selected time period by viewing the Activity chart on the page. By default, the chart shows workload activity breakdown values for wait classes.

Investigate the impact by viewing detailed statistics for the top activity sessions that are adversely affecting the system.

Click the largest spike in the chart or the corresponding wait class in the legend beside the chart. The viewer now filters out everything in the chart except for the wait class of interest.

For instance, if the chart shows that the Concurrency wait class has the biggest spike, click the chart area of the legend for Concurrency. The viewer refreshes the chart and now only shows the Concurrency value and displays a Wait Class: Concurrency icon in the Filters bar.

Tip:

You can create an unlimited number of filters.

Expand the Activity icon, then select Top Dimensions from the menu, which provides a list of common processes of interest. The chart refreshes in response to your selection, displaying values for the particular category you selected.

For instance, if you create a filter for Concurrency as described above, select Top Dimensions from the menu, then User Session, the chart shows user sessions only for Concurrency.

Optional: Use the Load Map for a graphical view of system activity.

The Load Map is useful for viewing activity in a single- or multi-dimensional layout when you are not interested in seeing how activity has changed over time within the selected period.

Optional: Click Save to save the current page view as an HTML file for offline reference. When you click Save, a pop-up window appears, and you can specify where you want to save the report. This action creates an Enterprise Manager Active Report covering all data currently gathered as part of the analysis. You can use this later to conduct a more thorough postmortem analysis, for instance. You can view the report without Enterprise Manager or database connectivity.

You can also click Mail and specify an email address to send the page as an attachment.

Customizing the Database Performance Page

You can customize the Performance page so that it specifically addresses your requirements. As explained in "Monitoring Instance Activity", you can specify which charts you want to appear by default in the Performance page, and how you want them to appear. You can also decide whether to include baseline values in the Throughput and Services charts.

Enterprise Manager stores persistent customization information for each user in the repository. Enterprise Manager retrieves the customization data when you access the Performance page and caches it for the remainder of the browser session until you change the settings.

To customize the Performance page: 

From the Database Home page, click Performance.

The Performance page appears.

On the Performance page, click Settings.

The Performance Page Settings page appears.

Description of perf_page_sett.gif follows
Description of the illustration perf_page_sett.gif

In the Detailed Chart Settings section, choose the defaults for display of the instance activity charts. Complete the following steps:

In Default View, select the instance activity chart to appear by default in the Average Active Session section.

See "Monitoring Instance Activity" for a description of the Throughput, I/O, Parallel Execution, and Services charts.

In Throughput Chart Settings, select Per Second or Per Transaction as the default instance throughput rate to be displayed in the Throughput chart.

See "Monitoring Throughput" to learn how to use the Throughput charts.

In I/O Chart Settings, select the default I/O breakdown to be displayed in the I/O chart.

See "Monitoring I/O" to learn how to use the I/O charts.

In the Baseline Display section, choose how AWR baselines will be displayed in the performance charts. Do one of the following:

Select Do not show the baseline values to prevent baselines from appearing.

Select Show the 99th percentile line using the system moving window baseline to specify a percentile to display for the Throughput and Services charts.

Select Show the 99th percentile line using a static baseline with computed statistics and then select a baseline name from the Baseline Name list.

You can select only baselines that have undergone schedule statistics computation, as described in "Computing Threshold Statistics for Baselines".

Click OK.

The Performance page appears.

The charts are now displayed according to your customized settings.

5 Monitoring Performance Alerts

Oracle Database includes a built-in alerts infrastructure to notify you of impending problems with the database. By default, Oracle Database enables the following alerts:

Tablespace Usage

Snapshot Too Old

Recovery Area Low on Free Space

Resumable Session Suspended

For information about alerts and how to manage them, see Oracle Database 2 Day DBA.

In addition to these default alerts, you can use performance alerts to detect any unusual changes in database performance.

This chapter contains the following sections:

Setting Metric Thresholds for Performance Alerts

Responding to Alerts

Clearing Alerts

Setting Metric Thresholds for Performance Alerts

A metric is the rate of change in a cumulative statistic. This rate can be measured against a variety of units, including time, transactions, or database calls. For example, the number of database calls per second is a metric. You can set thresholds on a metric so that an alert is generated when the threshold is passed.

Performance alerts are based on metrics that are performance-related. These alerts are either environment-dependent or application-dependent.

Environment-dependent performance alerts may not be relevant on all systems. For example, the AVERAGE_FILE_READ_TIME metric generates an alert when the average time to read a file exceeds the metric threshold. This alert may be useful on a system with only one disk. On a system with multiple disks, however, the alert may not be relevant because I/O processing is spread across the entire subsystem.

Application-dependent performance alerts are typically relevant on all systems. For example, the BLOCKED_USERS metric generates a performance alert when the number of users blocked by a particular session exceeds the metric threshold. This alert is relevant regardless of how the environment is configured.

To obtain the most relevant information from performance alerts, set the threshold values of performance metrics to values that represent desirable boundaries for your system. You can then fine-tune these values over time until your system meets or exceeds your performance goals.

To set thresholds for performance metrics: 

On the Database Home page, under Related Links, click Metric and Policy Settings.

The Metric and Policy Settings page appears, showing the Metric Thresholds subpage.

For each performance metric relevant for your system, click the Edit icon.

The Edit Advanced Settings page appears.

Follow the steps of the wizard to set the threshold value.

See Also:

"Setting Metric Thresholds for Baselines"

Oracle Database 2 Day DBA to learn how to set metric thresholds

Responding to Alerts

When an alert is generated by Oracle Database, it appears under Alerts on the Database Home page.

Description of alerts.gif follows
Description of the illustration alerts.gif

Oracle Enterprise Manager (Enterprise Manager) enables you to configure alerts to be sent by e-mail, pager, or cellular phone text messaging.

To respond to an alert: 

On the Database Home page, under Alerts, locate the alert that you want to investigate and click the Message link.

A page that contains further information about the alert appears.

Do one of the following:

Follow the recommendations.

Run Automatic Database Diagnostic Monitor (ADDM) or another advisor to get more detailed diagnostics of the system or object behavior.

See Also:

Oracle Database 2 Day DBA for information about how to configure the alert notification method

Clearing Alerts

Most alerts, such as the CPU Utilization alert, are cleared automatically when the cause of the problem disappears. However, other alerts, such as the Generic Alert Log Error or Generic Incident alert, must be acknowledged.

Description of gen_inc.gif follows
Description of the illustration gen_inc.gif

After taking the necessary corrective measures, you can acknowledge an alert by clearing or purging it. Clearing an alert sends the alert to the Alert History, which can be viewed from the Database Home page under Related Links. Purging an alert removes it from the Alert History.

To clear alerts: 

On the Database Home page, under Diagnostic Summary, click the Alert Log link.

The Alert Log Errors page appears.

Description of alert_log.gif follows
Description of the illustration alert_log.gif

Do one of the following:

Select the alerts that you want to clear and click Clear.

To clear all open alerts, click Clear Every Open Alert.

Do one of the following:

Select the alerts that you want to purge and click Purge.

To purge all alerts, click Purge Every Alert.

See Also:

Oracle Database 2 Day DBA to learn how to manage alerts

Part III

Reactive Database Tuning

Part III describes how to tune Oracle Database in response to a reported problem, such as when the user reports a performance problem with the database that must be tuned immediately.

This part contains the following chapters:

Chapter 6, "Manual Database Performance Monitoring"

Chapter 7, "Resolving Transient Performance Problems"

Chapter 8, "Resolving Performance Degradation Over Time"

6 Manual Database Performance Monitoring

You can run the Automatic Database Diagnostic Monitor (ADDM) manually to monitor current and historical database performance. Typically, you use the automatic diagnostic feature of ADDM to identify performance problems with the database. As described in Chapter 3, "Automatic Database Performance Monitoring", ADDM runs once every hour by default. It is possible to configure ADDM to run more or less frequently. However, in some cases you may want to run ADDM manually.

You can run ADDM manually to analyze a time period that is longer than one ADDM analysis period. For example, you may want to analyze database performance in a workday by analyzing 8 consecutive hours. You could analyze each of the individual ADDM periods within the workday, but this approach may become complicated if performance problems appear in only some ADDM periods. Alternatively, you can run ADDM manually with a pair of Automatic Workload Repository (AWR) snapshots that encompass the 8-hour period. In this case, ADDM identifies the most critical performance problems in the entire time period.

This chapter contains the following sections:

Manually Running ADDM to Analyze Current Database Performance

Manually Running ADDM to Analyze Historical Database Performance

Accessing Previous ADDM Results

Manually Running ADDM to Analyze Current Database Performance

By default, ADDM runs every hour to analyze snapshots taken by AWR during this period. In some cases you may notice performance degradation that did not exist in the previous ADDM analysis period, or a sudden spike in database activity on the Performance page, as described in Chapter 4, "Monitoring Real-Time Database Performance". If the next ADDM analysis is not scheduled to run for 30 minutes, then you can run ADDM manually to identify and resolve the performance problem.

When you run ADDM manually, a manual AWR snapshot is created automatically. This manual run may affect the ADDM run cycle. For example, if you scheduled ADDM to run hourly at the start of each hour and the last ADDM run was at 8:00 p.m., running ADDM manually at 8:30 p.m. causes the next scheduled run to start at 9:30 p.m., not 9:00 p.m. Subsequent ADDM runs will continue on the new run cycle, occurring hourly at the half-hour instead of the start of each hour.

To analyze current database performance by manually running ADDM: 

On the Database Home page, under Related Links, click Advisor Central.

The Advisor Central page appears.

Under Advisors, click ADDM.

The Run ADDM page appears.

Description of run_addm_now.gif follows
Description of the illustration run_addm_now.gif

In this example, the average active sessions with wait events rose at 10:00 a.m., peaking at 10:50 a.m. The number dipped at 11:00 a.m. and then started to rise again at 11:10 a.m.

Select Run ADDM to analyze current instance performance and click OK.

The Confirmation page appears.

Click Yes.

The Processing: Run ADDM Now page appears while the database takes a new AWR snapshot.

An ADDM run occurs for the time period between the new and the previous snapshot. After ADDM completes the analysis, the Automatic Database Diagnostic Monitor (ADDM) page appears with the results.

Description of run_addm_now_results.gif follows
Description of the illustration run_addm_now_results.gif

Click View Report.

The View Report page appears.

Optionally, click Save to File to save the results of the ADDM task in a report for later access.

See Also:

"Reviewing the Automatic Database Diagnostic Monitor Analysis"

Manually Running ADDM to Analyze Historical Database Performance

You can run ADDM manually to analyze historical database performance by selecting a pair or range of AWR snapshots as the analysis period. This technique is useful when you have identified a previous time period when database performance was poor.

In the Performance page, you can monitor historical performance by selecting Historical from the View Data list. In the Historical view, you can monitor database performance in the past, up to the duration defined by the AWR retention period. If you notice performance degradation, then you can drill down from the Performance page to identify historical performance problems with the database, as described in Chapter 4, "Monitoring Real-Time Database Performance". If you identify a problem, then you can run ADDM manually to analyze a particular time period.

To analyze historical database performance by manually running ADDM: 

On the Database Home page, under Related Links, click Advisor Central.

The Advisor Central page appears.

Under Advisors, click ADDM.

The Run ADDM page appears.

Select Run ADDM to analyze past instance performance.

Specify a time period for analysis by selecting a pair of AWR snapshots. Complete the following steps:

Select Period Start Time.

Below the chart for the starting snapshot, click the snapshot you want to use for the start time.

A play icon (displayed as an arrow) appears over the snapshot icon.

In this example, database activity peaked from 10 a.m. to 11 a.m., so the snapshot taken at 10 a.m. is selected for the start time.

Select Period End Time.

Below the chart for the ending snapshot, click the snapshot you want to use for the end time.

A stop icon (displayed as a square) appears over the snapshot icon.

In this example, the ending snapshot is at 11:00 a.m.

Description of run_addm_past.gif follows
Description of the illustration run_addm_past.gif

Click OK.

After ADDM completes the analysis, the Automatic Database Diagnostic Monitor (ADDM) page appears with the results of the ADDM run.

Figure 6-1 Analyzing Historical Database Performance

Description of Figure 6-1 follows
Description of "Figure 6-1 Analyzing Historical Database Performance"

Click View Report.

The View Report page appears.

Optionally, click Save to File.

See Also:

"Reviewing the Automatic Database Diagnostic Monitor Analysis"

Accessing Previous ADDM Results

If you ran ADDM manually to analyze current or historical database performance, the results are displayed on the Automatic Database Diagnostic Monitor (ADDM) page after the ADDM run has completed.

You can access the ADDM results at a later time, or access the ADDM results from previous run cycles.

To access the ADDM results: 

On the Database Home page, under Related Links, click Advisor Central.

The Advisor Central page appears.

Complete the following steps:

Under Advisor Tasks, select ADDM from the Advisory Type list.

Select the appropriate search criteria.

For example, you can select All in the Advisor Runs list to view all ADDM tasks.

Click Go.

Description of advisor_tasks_addm.gif follows
Description of the illustration advisor_tasks_addm.gif

The ADDM tasks are displayed under Results.

To view an ADDM result, select the desired ADDM task and click View Result.

The results from the selected ADDM task are shown in the Automatic Database Diagnostic Monitor (ADDM) page.

See Also:

"Reviewing the Automatic Database Diagnostic Monitor Analysis"

7 Resolving Transient Performance Problems

Transient performance problems are short-lived and typically do not appear in the Automatic Database Diagnostic Monitor (ADDM) analysis. ADDM tries to report the most significant performance problems during an analysis period in terms of their effect on DB time. If a problem lasts for a brief time, then its severity might be averaged out or minimized by other performance problems in the entire analysis period. Therefore, the problem may not appear in the ADDM findings. Whether or not a performance problem is captured by ADDM depends on its duration compared to the interval between the Automatic Workload Repository (AWR) snapshots.

If a performance problem lasts for a significant portion of the time between snapshots, then it will be captured by ADDM. For example, if the snapshot interval is one hour, then a performance problem that lasts 30 minutes should not be considered a transient performance problem because its duration represents a significant portion of the snapshot interval and will likely be captured by ADDM.

On the other hand, a performance problem that lasts 2 minutes could be transient because its duration is a small portion of the snapshot interval and will probably not appear in the ADDM findings. For example, if the system was slow between 10:00 p.m. and 10:10 p.m., and if the ADDM analysis for the time period between 10:00 p.m. and 11:00 p.m. does not show a problem, then a transient problem may have occurred for only a few minutes of the 10-minute interval.

This chapter contains the following sections:

Overview of Active Session History

Running Active Session History Reports

Active Session History Reports

Diagnosing Malfunctioning Systems in an Emergency

Overview of Active Session History

To capture a detailed history of database activity, Oracle Database samples active sessions each second with the Active Session History (ASH) sampler. AWR snapshot processing collects the sampled data into memory and writes it to persistent storage. ASH is an integral part of the Oracle Database self-management framework and is extremely useful for diagnosing performance problems.

ASH gathers sampled data at the session level rather than at the instance level. By capturing statistics for only active sessions, ASH collects a manageable set of data. The size of this data is directly related to the work being performed, rather than to the size of the entire database instance.

Sampled data captured by ASH can be aggregated based on the dimensions in the data, including the following:

SQL identifier of a SQL statement

Object number, file number, and block number

Wait event identifier and parameters

Session identifier and session serial number

Module and action name

Client identifier of the session

Service hash identifier

You can run ASH reports to analyze transient performance problems with the database that only occur during specific times. This technique is especially useful when you are trying to do either of the following:

Resolve transient performance problems that may last for only a short period of time, such as why a particular job or session is not responding when the rest of the instance is performing as usual

Perform scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL identifier

See Also:

"Active Session History Statistics"

Running Active Session History Reports

This section describes how to generate ASH reports using Oracle Enterprise Manager (Enterprise Manager).

To run ASH reports: 

On the Performance page, under Average Active Sessions, click Run ASH Report.

The Run ASH Report page appears.

Enter the date and time for the start and end of the time period when the transient performance problem occurred.

In this example, database activity increased between 2:30 p.m. and 2:35 p.m., so an ASH report should be created for that time period.

Description of run_ash.gif follows
Description of the illustration run_ash.gif

Click Generate Report.

The Processing: View Report page appears while the report is being generated.

After the report is generated, the ASH report appears under Report Results on the Run ASH Report page.

Description of ash_report.gif follows
Description of the illustration ash_report.gif

Optionally, click Save to File to save the report in HTML format for future analysis.

Active Session History Reports

You can use an ASH report to identify the source of transient performance problems. The report is divided into titled sections. The following sections of the ASH report are useful places to begin the investigation:

Top Events

Load Profile

Top SQL

Top Sessions

Top DB Objects

Top DB Files

Activity Over Time

See Also:

Oracle Database Performance Tuning Guide for more detailed information about the ASH report

Top Events

The Top Events section of the report describes the top wait events of the sampled session activity categorized by user, background, and priority. Use this information to identify the wait events that may be the cause of the transient performance problem.

The Top Events section of the report contains the following subsections:

Top User Events

Top Background Events

Top User Events

The Top User Events subsection of the report lists the top wait events from client processes that accounted for the highest percentages of sampled session activity.

Figure 7-1 shows that most database activity is consumed by the CPU + Wait for CPU event. The Wait for CPU is the time the process spent in the operating system run queue. The %Event column shows the percentage of DB time consumed by this event. In this example, over 30 percent of DB time was spent either on the CPU or waiting to get on it. The Load Profile section should be examined next to determine the type of activity that is causing this CPU consumption.

Figure 7-1 Top User Events

Description of Figure 7-1 follows
Description of "Figure 7-1 Top User Events"

Top Background Events

The Top Background Events subsection lists the top wait events from the background events that accounted for the highest percentages of sampled session activity.

The example in Figure 7-2 shows that 22.81 percent of sampled session activity is consumed by the CPU + Wait for CPU event.

Figure 7-2 Top Background Events

Description of Figure 7-2 follows
Description of "Figure 7-2 Top Background Events"

Load Profile

The Load Profile section of the report describes the load analyzed in the sampled session activity. Use the information in this section to identify the service, client, or SQL command type that may be the cause of the transient performance problem.

The Top Service/Module subsection lists the services and modules that accounted for the highest percentages of sampled session activity. A service is a group of related database tasks that share common functionality, quality expectations, and priority. Services are a convenient way to monitor multiple applications. The SYS$USERS and SYS$BACKGROUND services are always defined.

Figure 7-3 shows that over half of the database activity is consumed by the SYS$USERS service running the SQL*Plus module. In this example, it appears that the user is running high-load SQL that is causing the performance problem indicated in Figure 7-1. The Top SQL section of the report should be analyzed next to determine whether a particular type of SQL statement makes up the load.

Figure 7-3 Top Service/Module

Description of Figure 7-3 follows
Description of "Figure 7-3 Top Service/Module"

See Also:

"Monitoring Top Services"

"Monitoring Top Modules"

Top SQL

The Top SQL section of the report describes the top SQL statements of the sampled session activity. Use this information to identify high-load SQL statements that may be the cause of the transient performance problem. The Top SQL with Top Events subsection lists the SQL statements that accounted for the highest percentages of sampled session activity. The Sampled # of Executions column shows how many distinct executions of a particular SQL statement were sampled. To view the text of the SQL statements, click the SQL ID link.

Figure 7-4 shows that over half of DB time is consumed by three DML statements. These statements were run in the SQL*Plus module shown in Figure 7-3. The Top Sessions section should be analyzed to identify the sessions running these statements.

Figure 7-4 Top SQL with Top Events

Description of Figure 7-4 follows
Description of "Figure 7-4 Top SQL with Top Events"

See Also:

"Monitoring Top SQL"

Top Sessions

The Top Sessions section lists the sessions that were waiting for the wait event that accounted for the highest percentages of sampled session activity. Use this information to identify the sessions that may be the cause of the performance problem.

The # Samples Active column shows the number of ASH samples in which the session was found waiting for that particular event. The percentage is calculated based on wall-clock time.

In Figure 7-5, the # Samples Active column shows that of the 300 times that ASH sampled database activity, the HR session (SID 123) performed a sequential read 243 times and a flashback operation 36 times. So, HR was active at least 93% of the time. The session consumed 27% of the total activity (much less than 93%) because other sessions, including the SH session, were also active.

It appears that the HR and SH sessions were running the high-load SQL statement in Figure 7-4. You should investigate this session to determine whether it is performing a legitimate operation and tune the SQL statement if possible. If tuning the SQL is not possible, and if a session is causing an unacceptable performance impact on the system, then consider terminating the session.

Figure 7-5 Top Sessions

Description of Figure 7-5 follows
Description of "Figure 7-5 Top Sessions"

See Also:

"Monitoring Top Sessions"

Chapter 10, "Tuning SQL Statements"

Top DB Objects

The Top DB Objects subsection lists the database objects (such as tables and indexes) that accounted for the highest percentages of sampled session activity.

The example in Figure 7-6 shows that the hr.departments and hr.employees tables account for a high percentage of activity. Enqueue waits are waits for locks. In this example, the wait is for the TM (table) lock. Sometimes these waits indicate unindexed foreign key constraints. The buffer busy waits event records waits for a buffer to become available. These waits indicate that multiple processes are attempting to concurrently access the same buffers in the buffer cache.

Figure 7-6 Top DB Objects

Description of Figure 7-6 follows
Description of "Figure 7-6 Top DB Objects"

Top DB Files

The Top DB Files subsection lists the database files that accounted for the highest percentages of sampled session activity. Only cluster and I/O events are considered. The % Event column breaks down the activity by event, so if multiple rows exist in this table, then the sampled activity is divided among multiple events.

Figure 7-7 shows that about 11 percent of DB time involves waits for the UNDOTBS tablespace. This information is consistent with Figure 7-4, which shows significant DML activity from multiple sessions.

Figure 7-7 Top DB Files

Description of Figure 7-7 follows
Description of "Figure 7-7 Top DB Files"

Activity Over Time

The Activity Over Time section of the ASH report is particularly useful for longer time periods because it provides in-depth details about activities and workload profiles during the analysis period. The Activity Over Time section is divided into time slots. The ASH report time span is divided into 10 time slots unless the time period is short or the data is sparse.

Figure 7-8 shows an activity report for the period between 2:10 p.m. and 2:40 p.m. The report indicates that the number of sampled sessions rose sharply in the fifth inner slot (2:24 p.m.) and stayed up. During this period CPU activity and lock enqueue waits increased dramatically.

Figure 7-8 Activity Over Time

Description of Figure 7-8 follows
Description of "Figure 7-8 Activity Over Time"

Each time slot contains session and wait event activity, as described in Table 7-1.

Table 7-1 Activity Over Time

Column
Description

Slot Time (Duration)

Duration of the slot

Slot Count

Number of sampled sessions in the slot

Event

Top three wait events in the slot

Event Count

Number of ASH samples waiting for the wait event

% Event

Percentage of ASH samples waiting for wait events in the entire analysis period

All inner slots are the same number of minutes each for easy comparison. The first and last slots, called outer slots, are odd-sized because they do not have a fixed slot time.

In Figure 7-8, the first outer slot has a duration of 1.2 minutes, whereas the last outer slot has a duration of 1.8 minutes. The duration of each inner slot is 3.0 minutes.

When comparing the inner slots, perform a skew analysis by identifying spikes. A spike in the Slot Count column indicates an increase in active sessions and a relative increase in database workload. A spike in the Event Count column indicates an increase in the number of sampled sessions waiting for an event. Typically, when the number of active session samples and the number of sessions associated with a wait event increase, the slot may be the cause of the transient performance problem.

Diagnosing Malfunctioning Systems in an Emergency

The following procedure is useful for diagnosing serious performance problems at the moment they are occurring. This may assist you in finding a remedy to resolve the problem other than rebooting the system.

From the Performance Home page, select the Performance menu, then select Emergency Monitoring.

The Emergency Performance page appears and displays collected ASH data. The page also shows top blocking sessions in the Hang Analysis table.

If the information displayed on this page does not help you resolve the problem, continue to the next step.

Select the Performance menu, then select Real-Time ADDM.

Click Start from the Real-Time ADDM page.

The system responds by collecting performance data from all target database instances, analyzing data for problem diagnosis and resolutions, and showing you results of the analysis.

Click the Findings tab for a clear and interactive summary of all of the findings the analysis has detected, and to view actionable recommendations.

Optional: Click Save to save the current page view as an HTML file for offline reference. When you click Save, a pop-up window appears, and you can specify where you want to save the report. This action creates an Enterprise Manager Active Report covering all data currently gathered as part of the analysis. You can use this later to conduct a more thorough postmortem analysis, for instance. You can view the report without Enterprise Manager or database connectivity.

You can also click Mail and specify an email address to send the report as an attachment.

Example

Your database is currently having severe performance problems, so you click the Emergency Performance page link.

Neither ASH nor Hang Analysis indicate the root cause or offer quick solutions, so you then click Emergency ADDM.

You view the active report generated by Emergency ADDM, which shows that the system is paging due to excessive PGA consumption by session S1, possibly due to a leak. The report recommends that you terminate S1 immediately.

You terminate session S1, then return to the Emergency Performance page to see if normal system behavior is restored.

You note that the system is making progress according to the activity charts on the page.

You return to the regular Performance Home page, and observe that database performance has improved.

8 Resolving Performance Degradation Over Time

Performance degradation of the database occurs when your database was performing optimally in the past, such as 6 months ago, but has gradually degraded to a point where it becomes noticeable to the users. The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time.

While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports, which totals four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods. The two time periods selected for the AWR Compare Periods report can be of different durations. The report normalizes the statistics by the amount of time spent on the database for each time period and presents statistical data ordered by the largest difference between the periods.

For example, a batch workload that historically completed in the maintenance window between 10:00 p.m. and midnight is currently showing poor performance and completing at 2 a.m. You can generate an AWR Compare Periods report from 10:00 p.m. to midnight on a day when performance was good and from 10:00 a.m. to 2 a.m. on a day when performance was poor. The comparison of these reports should identify configuration settings, workload profile, and statistics that were different in these two time periods. Based on the differences identified, you can more easily diagnose the cause of the performance degradation.

This chapter contains the following sections:

Managing Baselines

Running the AWR Compare Periods Reports

Using the AWR Compare Periods Reports

See Also:

"Gathering Database Statistics Using the Automatic Workload Repository"

Managing Baselines

Baselines are an effective way to diagnose performance problems. AWR supports the capture of baseline data by enabling you to specify and preserve a pair or a range of snapshots as a baseline. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

A moving window baseline corresponds to all AWR data that exists within the AWR retention period. Oracle Database automatically maintains a system-defined moving window baseline. The default size of the window is the current AWR retention period, which by default is 8 days.

This section contains the following topics:

Creating a Baseline

Deleting a Baseline

Computing Threshold Statistics for Baselines

Setting Metric Thresholds for Baselines

Creating a Baseline

Before creating a baseline, carefully consider the time period you choose as a baseline because it should represent the database operating at an optimal level. In the future, you can compare these baselines with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.

You can create the following types of baseline:

Creating a Single Baseline

Creating a Repeating Baseline

Creating a Single Baseline

A single baseline is captured at a single, fixed time interval. For example, a single baseline may be captured on February 5, 2009 from 5:00 p.m. to 8:00 p.m.

You can choose future start and end times to create a baseline that captures future database activity. If both the start time and the end time are in the future, then a baseline template with the same name as the baseline will also be created. A baseline template is a specification that enables Oracle Database to automatically generate a baseline for a future time period.

To create a single baseline: 

From the Database Home page, click Server.

The Server page appears.

Under Statistics Management, click AWR Baselines.

The AWR Baselines page appears with a list of existing baselines displayed.

Description of awr_baselines.gif follows
Description of the illustration awr_baselines.gif

Click Create.

The Create Baseline: Baseline Interval Type page appears.

Select Single.

Description of baseline_interval_single.gif follows
Description of the illustration baseline_interval_single.gif

Click Continue.

The Create Baseline: Single Baseline page appears.

Description of create_baseline_single.gif follows
Description of the illustration create_baseline_single.gif

In the Baseline Name field, enter a name for the baseline.

Under Baseline Interval, select whether to use a snapshot range or a time range for the baseline. Do one of the following:

To use a range, select Snapshot Range. Complete the following steps:

Under Select Time Period, select a start time for the baseline by selecting Period Start Time and the snapshot icon below the Active Sessions chart that corresponds to the desired start time.

Select an end time for the baseline by selecting Period End Time and the snapshot icon below the Active Sessions chart that corresponds to the desired end time.

Optionally, to view older snapshots that are not displayed below the Active Sessions chart, expand Change Chart Time Period. Enter the desired start date in the Chart Start Date field and the desired end date in the Chart End Date field, and click Go.

In this example, a snapshot range on February 5, 2009 from 3:10 p.m. to 3:30 p.m. is selected.

Description of baseline_snapshot_range.gif follows
Description of the illustration baseline_snapshot_range.gif

To use a time range, select Time Range. Complete the following steps:

In the Start Time fields, select a start time for the baseline.

In the End Time fields, select an end time for the baseline.

In the following example, a time range from 3:10 p.m. to 3:30 p.m. on February 6, 2009 is selected.

Description of create_baseline_time_range.gif follows
Description of the illustration create_baseline_time_range.gif

Click Finish.

The AWR Baselines page reappears with the newly created baseline displayed.

Creating a Repeating Baseline

A repeating baseline is a baseline that repeats during a time interval over a specific period. For example, a repeating baseline may repeat every Monday from 8:00 a.m. to 10:00 a.m. from February 6, 2009 to February 6, 2010.

To create a repeating baseline: 

From the Database Home page, click Server.

The Server page appears.

Under Statistics Management, click AWR Baselines.

The AWR Baselines page appears with a list of existing baselines displayed.

Click Create.

The Create Baseline: Baseline Interval Type page appears.

Select Repeating and then click Continue.

The Create Baseline: Repeating Baseline Template page appears.

In the Baseline Name Prefix field, enter a name prefix for the baseline.

Under Baseline Time Period, specify the time of the day that you want the baseline to begin collecting AWR data and the duration of the baseline collection.

Under Frequency, do one of the following:

Select Daily if you want the baseline to repeat on a daily basis.

Select Weekly if you want the baseline to repeat on a weekly basis, and then select the day of the week on which the baseline will repeat.

Under Interval of Baseline Creation, complete the following steps:

In the Start Time fields, select a date and time in the future when the data collection should begin.

In the End Time fields, select a date and time in the future when the data collection should end.

Under Purge Policy, enter the number of days to retain captured baselines.

Click Finish.

A baseline template with the same name as the baseline name prefix will be created. A baseline template is a specification that enables Oracle Database to automatically generate a baseline for a future time period.

This example creates a baseline that repeats weekly on Mondays from 8:00 a.m. to 10:00 a.m. from February 6, 2009 to February 6, 2010. Every captured baseline expires after 30 days.

Description of create_baseline_repeating.gif follows
Description of the illustration create_baseline_repeating.gif

Deleting a Baseline

To conserve storage space, you may want to periodically delete unused baselines stored in the database.

To delete a baseline: 

From the Database Home page, click Server.

The Server page appears.

Under Statistics Management, click AWR Baselines.

The AWR Baselines page appears with a list of existing baselines displayed.

Select a baseline and click Delete.

The Confirmation page appears.

Select whether to purge the underlying data associated with the baseline.

The underlying data includes the individual snapshots preserved in the baseline and any statistics that are computed for the baseline. Do one of the following:

To delete the underlying data, select Purge the underlying data associated with the baseline.

To preserve the underlying data, select Do not purge the underlying data associated with the baseline.

Click Yes.

The AWR Baselines page reappears. A message informs you that the baseline was deleted successfully.

Computing Threshold Statistics for Baselines

Computing threshold statistics for baselines enables you to graphically display the computed statistics in the charts on the Performance page.

To compute threshold statistics for baselines: 

From the Database Home page, click Server.

The Server page appears.

Under Statistics Management, click AWR Baselines.

The AWR Baselines page appears with a list of existing baselines displayed.

Select the baseline for which you want to compute statistics.

Select a baseline that does not already have computed statistics. These baselines are identified by No in the Statistics Computed column.

From the Actions list, select Schedule Statistics Computation, and then click Go.

The Compute Threshold Statistics page appears.

This example computes statistics for the baseline BASELINE_THU_1530.

Description of compute_threshold_stats.gif follows
Description of the illustration compute_threshold_stats.gif

In the Name field, enter a name for the task.

Alternatively, you can choose to use the system-generated name.

In the Description field, enter a description for the task.

Alternatively, you can choose to use the system-generated description.

Under Start, do one of the following:

Select Immediately to run the task immediately after it has been submitted.

Select Later to run the task at a later time as specified using the Date and Time fields.

This computation is resource-intensive, so you may want to schedule it to run during off-peak hours.

Click Submit.

The AWR Baselines page appears. A message informs you that statistics computation has been scheduled for the selected baseline.

See Also:

"Customizing the Database Performance Page" for information about displaying computed statistics on the Performance page

Oracle Database 2 Day DBA for information about thresholds and how to manage them

Setting Metric Thresholds for Baselines

As explained in "Setting Metric Thresholds for Performance Alerts", a metric is the rate of change in a cumulative statistic. Alerts notify you when particular metric thresholds are crossed. When the metric thresholds are crossed, the system is in an undesirable state. You can edit the threshold settings for baseline metrics.

You can create the following types of baseline:

Setting Metric Thresholds for the Default Moving Baseline

Setting Metric Thresholds for Selected Baselines

Setting Metric Thresholds for the Default Moving Baseline

This section describes the easiest technique for setting the metric thresholds for the default moving baseline. You can choose a group of basic metric threshold settings based on common database workload profiles such as OLTP, data warehousing, and OLTP with nighttime batch jobs. After choosing a workload profile, you can expand or change the threshold values as needed.

To set metric thresholds for the default moving baseline: 

On the Database Home page, under Related Links, click Baseline Metric Thresholds.

The Threshold Configuration tab of the Baseline Metric Thresholds page appears.

Click Quick Configuration.

The Quick Configuration: Baseline Metric Thresholds page appears.

In Workload Profile, select one of the following options, depending on how you are using the database:

Primarily OLTP (pure transaction processing 24 hours a day)

Primarily Data Warehousing (query and load intensive)

Alternating (OLTP during the daytime and batch during the nighttime)

In this example, select Primarily OLTP.

Click Continue.

The Quick Configuration: Review OLTP Threshold Settings page appears.

Description of threshold_quick.gif follows
Description of the illustration threshold_quick.gif

Review the metric threshold settings and then click Finish.

You are returned to the Baseline Metric Thresholds page, with the Threshold Configuration tab selected. The metric threshold settings are displayed.

Setting Metric Thresholds for Selected Baselines

This section explains how to select a baseline and edit its thresholds. You can configure the type of threshold, for example, whether it is based on significance levels, percentage of maximum values, or fixed values. You can also configure the threshold levels that determine when the database generates critical alerts and warnings.

You can edit thresholds for the default moving baseline or a baseline that you created in the AWR Baselines page. You can select a baseline in the Edit Thresholds page after you have scheduled statistics computation from the AWR Baselines page and the statistics have finished computing on the static baseline.

To set a metric threshold for a selected moving baseline: 

On the Database Home page, under Related Links, click Baseline Metric Thresholds.

The Threshold Configuration tab of the Baseline Metric Thresholds page appears.

In the View list, select Basic Metrics.

The Baseline Metric Thresholds page appears.

In the Category/Name column, click the link for the metric whose threshold you want to set or change.

For example, click Number of Transactions (per second).

The Edit Thresholds: Number of Transactions (per second) appears.

Description of edit_thresholds.gif follows
Description of the illustration edit_thresholds.gif

The charts on this page provide simple and detailed views of metric activity for a 24-hour period. In the top simple chart, click a day to view the value of the metric plotted against a 24-hour period.

Under AWR Baseline, in the Name list, select either the default SYSTEM_MOVING_WINDOW or the name of a baseline created in the AWR Baselines page.

A baseline appears in the AWR Baseline list after you have scheduled statistics computation from the AWR Baselines page and the statistics have finished computing on the static baseline.

In this example, BASELINE_THU_1530 is selected.

The page refreshes to show the charts for the baseline that you selected.

In the Threshold Settings section, complete the following steps to change the settings:

In Threshold Type, leave Significance Level selected.

In Critical, select Extreme.

In Warning, select Very High.

In Occurrences, leave the current value.

Click Apply Thresholds.

You are returned to the Baseline Metric Thresholds page. This page shows the altered metric threshold settings.

Running the AWR Compare Periods Reports

This section describes how to run the AWR Compare Periods reports using Enterprise Manager.

You can use AWR Compare Periods reports to compare the database performance between two time periods by:

Comparing a Baseline to Another Baseline or Pair of Snapshots

Comparing Two Pairs of Snapshots

Comparing a Baseline to Another Baseline or Pair of Snapshots

When performance degradation occurs over time, you can run the AWR Compare Periods report to compare the degraded performance, captured as a new baseline or a pair of snapshots, to an existing baseline. You must have a baseline that represents the system operating at an optimal level. If an existing baseline is unavailable, then compare database performance between two periods of time using two arbitrary pairs of snapshots, as described in "Comparing Two Pairs of Snapshots".

To compare a baseline to another baseline: 

From the Database Home page, click Server.

The Server subpage appears.

Under Statistics Management, click Automatic Workload Repository.

The Automatic Workload Repository page appears.

Description of awr1.gif follows
Description of the illustration awr1.gif

Under Manage Snapshots and Baselines, click the link next to Baselines.

The AWR Baselines page appears.

Complete the following steps:

Select the baseline to use for the report.

At least one existing baseline must be available.

From the Actions list, select Compare Periods and click Go.

The Compare Periods: Second Period Start page appears. Under First Period, the selected baseline is displayed.

In this example, the baseline named BASELINE_THU_1530 is selected.

Description of baseline_first_period.gif follows
Description of the illustration baseline_first_period.gif

Compare the baseline selected in the first period to another baseline or a pair of snapshots. Do one of the following:

To compare to another baseline, select Select a Baseline and the baseline you want to use in the second period, and then click Next.

The Compare Periods: Review page appears. Go to Step 7.

To compare to a pair of snapshots, select Select Beginning Snapshot and the beginning snapshot to use in the second period, and then click Next.

This example selects snapshot 18, taken on February 5, 2009 at 5:00 p.m.

Description of awr_snapshot2_begin2.gif follows
Description of the illustration awr_snapshot2_begin2.gif

The Compare Periods: Second Period End appears. Proceed to the next step.

Select the ending snapshot for the snapshot period that will be included in the report and then click Next.

In this example, snapshot 24, taken on Feb 5, 2009 at 6:00 p.m., is selected.

Description of awr_snapshot2_end2.gif follows
Description of the illustration awr_snapshot2_end2.gif

The Compare Periods: Review page appears.

Description of awr2_review.gif follows
Description of the illustration awr2_review.gif

Review the periods to be included in the report and then click Finish.

The Compare Periods: Results page appears.

Data from the selected periods appears under the General subpage. You can view data per second or per transaction by selecting an option from the View Data list.

Note:

If the time periods have different lengths, then the data is normalized over database time before calculating the difference so that periods of different lengths can be compared.

In this example, almost every metric shows that more resources were consumed in the first period. The bar graphs indicate the proportions of the values in the two periods. The absence of bars indicates equivalent values. The report for this example shows significantly more database block changes per second and parse time in the first period than in the second.

Description of awr2_results_general.gif follows
Description of the illustration awr2_results_general.gif

Click Report to view the report.

The Processing: View Report page appears while the report is being generated. After it completes, the report will appear. To change periods, click Change Periods. To save the report as an HTML file, click Save to File.

Optionally, do the following:

To change periods, click Change Periods.

To save the report as an HTML file, click Save to File.

See Also:

"Creating a Baseline"

"Using the AWR Compare Periods Reports"

Comparing Current System Performance to a Baseline Period

You may have noticed a performance change on a production system and would like to know why, or you may have implemented a change to a production system and want to know the effect of the change, such as increased concurrency waits.

The Compare Period ADDM compares the performance of the database server in two time periods, and returns a report describing the performance changes and the root origin of the changes. The Advisor can analyze any Oracle RDBMS version 10.2.0.4 or greater monitored by Cloud Control. The following procedure explains how to initiate a report from the Compare Period ADDM.

From the Performance menu, select AWR, then Compare Period ADDM.

From the Compare Period Report page, specify the desired comparison and base periods:

Comparison Period — Generally represents an improperly functioning time period. However, you could also use the advisor to understand why performance has improved now when compared with an earlier time period.

Base Period — Represents a known (baseline or reference) period in which the database is functioning properly. You should select a base period in which the performance was acceptable, and the workload was as similar or identical as possible.

Click Run to display the Database Compare Period Report.

Examine the sections of the report to understand the performance change between the two periods and the cause of the change:

Overview

This portion of the report shows SQL commonality, which is the comparability between the base and comparison periods based on the average resource consumption of the SQL statements common to both periods.

A commonality value of 100% means that the workload "signature" in both time periods is identical. A commonality of 100% is expected for this use case, because the workload being replayed is the same (assuming that you are not using replay filters). A value of 0% means that the two time periods have no items in common for the specific workload dimension.

Commonality is based on the type of input (that is, which SQL is executing) as well as the load of the executing SQL statements. Consequently, SQL statements running in only one time period, but not consuming significant time, do not affect commonality. Therefore, two workloads could have a commonality of 100% even if some SQL statements are running only in one of the two periods, provided that these SQL statements do not consume significant resources.

Configuration

The information displayed shows base period and comparison period values for various parameters categorized by instance, host, and database.

Findings

The findings can show performance improvements and identify the major performance differences caused by system changes. For negative outcomes, if you understand and remove the cause, the negative outcome can be eliminated.

The values shown for the Base Period and Comparison Period represent performance with regard to database time.

The Change Impact value represents a measurement of the scale of a change in performance from one time period to another. It is applicable to issues or items measured by the total database time they consumed in each time period. The absolute values are sorted in descending order.

If the value is positive, an improvement has occurred, and if the value is negative, a regression has occurred. For instance, a change impact of -200% means that period 2 is three times as slow as period 1.

You can run performance tuning tools, such as ADDM and the SQL Tuning Advisor, to fix issues in the comparison period to improve general system performance.

Resources

The information shown here provides a summary of the division of database time for both time periods, and shows the resource usage for CPU, memory, I/O, and interconnect (Oracle RAC only).

Based on your observations, decide how to proceed to resolve performance regressions, then implement your action plan.

Comparing Two Pairs of Snapshots

If an existing baseline is unavailable, then you can compare database performance by using two arbitrary pairs of snapshots. Use one pair taken when the database is performing optimally, and another pair when the database is performing poorly. At least four existing snapshots must be available.

To compare performance using two pairs of snapshots: 

From the Database Home page, click Server.

The Server page appears.

Under Statistics Management, click Automatic Workload Repository.

The Automatic Workload Repository page appears.

Description of awr1.gif follows
Description of the illustration awr1.gif

Under Manage Snapshots and Baselines, click the link next to Snapshots.

The Snapshots page appears.

From the Go To Time list, select the time for the starting snapshot and then click Go.

This action filters the snapshots and displays only the snapshot taken at the start of the comparison period. The time in this example is at 2 p.m. on February 5.

Description of awr_gototime_1.gif follows
Description of the illustration awr_gototime_1.gif

Under Select Beginning Snapshot, select the starting point for the first snapshot period to be included in the report.

In this example, snapshot 1 is selected.

Description of awr_snapshot1_begin.gif follows
Description of the illustration awr_snapshot1_begin.gif

From the Actions list, select Compare Periods and click Go.

The Compare Periods: First Period End page appears.

Select the ending point for the first snapshot period to be included in the report and click Next.

In this example, snapshot 8, taken on February 5 at 3:20:10 p.m., is selected.

Description of awr_snapshot1_end.gif follows
Description of the illustration awr_snapshot1_end.gif

The Compare Periods: Second Period Start page appears.

Select the starting point for the second snapshot period to be included in the report and click Next. This snapshot is the third of four.

In this example, snapshot 20, taken on February 5 at 5:20 p.m., is selected.

The Compare Periods: Second Period End page appears.

Select the end point for the second period that will be included in the report and click Next.

In this example, snapshot 26, taken on February 5 at 6:20 p.m., is selected.

The Compare Periods: Review page appears.

Description of awr_review.gif follows
Description of the illustration awr_review.gif

Review the selected periods to be included in the report and then click Finish.

The Compare Periods: Results page appears.

Data from the selected periods appears under the General subpage. You can view data per second or per transaction by selecting an option from the View Data list.

In the following example, the first period shows more database activity, especially in parse time and physical reads, than the second period.

Description of awr_results_general.gif follows
Description of the illustration awr_results_general.gif

To view the report, click the Report tab.

The Processing: View Report page appears while the report is being generated. After it completes, the report will appear.

Optionally, do the following:

To change periods, click Change Periods.

To save the report as an HTML file, click Save to File.

Using the AWR Compare Periods Reports

After an AWR Compare Periods report is generated for the time periods you want to compare, you can use it to analyze performance degradation. To learn how to create the report, see "Running the AWR Compare Periods Reports".

Figure 8-1 shows a portion of an AWR Compare Periods report.

Figure 8-1 AWR Compare Periods Report

Description of Figure 8-1 follows
Description of "Figure 8-1 AWR Compare Periods Report"

The AWR Compare Periods report is divided into the following sections:

Summary of the AWR Compare Periods Report

Details of the AWR Compare Periods Report

Supplemental Information in the AWR Compare Periods Report

Summary of the AWR Compare Periods Report

The report summary is at the beginning of the AWR Compare Periods report, and summarizes information about the snapshot sets and loads used in the report. The report summary contains the following sections:

Snapshot Sets

Load Profile

Top Timed Events

Host Configuration Comparison

System Configuration Comparison

Snapshot Sets

The Snapshot Sets section displays information about the snapshot sets used for this report, such as instance, host, and snapshot information.

In the example shown in Figure 8-1, the first snapshot period corresponds to the time when performance was stable on February 5 from 5:00 p.m. to 6:00 p.m. The second snapshot period corresponds to the time when performance degradation occurred on the same day from 6:00 p.m. to 7:00 p.m.

Load Profile

The Load Profile section compares the loads used in the two snapshot sets. Differences in the loads are quantified as percentages in the %Diff column.

Description of awr_load_profile.gif follows
Description of the illustration awr_load_profile.gif

In this example, the DB time per second was 36% higher in the second period. CPU time per second was 100% higher.

Top Timed Events

The Top 5 Timed Events section is one of the most useful sections in the report. This section displays the five timed events or operations that consumed the highest percentage of total DB time in each of the snapshot sets.

Description of awr_top5_timed_events.gif follows
Description of the illustration awr_top5_timed_events.gif

In this example, CPU time is about twice as much in the second period than in the first. The number of waits for TM locks in the second period is about eight times the number in the first.

Host Configuration Comparison

The Host Configuration Comparison section compares the host configurations used in the two snapshot sets. For example, the report compares physical memory and number of CPUs. Differences in the configurations are quantified as percentages in the %Diff column.

System Configuration Comparison

The System Configuration Comparison section compares the database configurations used in the two snapshot sets. For example, the report compares the SGA and log buffer size. Differences in the configurations are quantified as percentages in the %Diff column.

Details of the AWR Compare Periods Report

The details section follows the summary of the AWR Compare Periods report, and provides statistics about the snapshot sets and loads used in the report. For example, the section includes statistics for database time, wait events, SQL execution time, and instance activity.

Supplemental Information in the AWR Compare Periods Report

The supplemental information is at the end of the AWR Compare Periods report, and provides additional information about initialization parameters and SQL statements. The init.ora Parameters section lists all the initialization parameter values for the first snapshot set. The Complete List of SQL Text section lists each statement by SQL ID and shows the text of the SQL statement.

Part IV

SQL Tuning

Part IV describes how to effectively tune SQL statements and contains the following chapters:

Chapter 9, "Identifying High-Load SQL Statements"

Chapter 10, "Tuning SQL Statements"

Chapter 11, "Optimizing Data Access Paths"

9 Identifying High-Load SQL Statements

High-load SQL statements may consume a disproportionate amount of system resources. These SQL statements often greatly affect database performance and must be tuned to optimize their performance and resource consumption. Even when a database is properly tuned, inefficient SQL can significantly degrade performance.

Identifying high-load SQL statements is an important SQL tuning activity that you must perform regularly. Automatic Database Diagnostic Monitor (ADDM) automates this task by proactively identifying potential high-load SQL statements. Additionally, you can use Oracle Enterprise Manager (Enterprise Manager) to identify high-load SQL statements that require further investigation. After you have identified the high-load SQL statements, you can tune them with SQL Tuning Advisor and SQL Access Advisor.

This chapter describes how to identify high-load SQL statements and contains the following sections:

Identification of High-Load SQL Statements Using ADDM Findings

Identifying High-Load SQL Statements Using Top SQL

Identification of High-Load SQL Statements Using ADDM Findings

By default, ADDM runs proactively once every hour. It analyzes key statistics gathered by the Automatic Workload Repository (AWR) over the last hour to identify any performance problems, including high-load SQL statements. When the system finds performance problems, it displays them as ADDM findings in the Automatic Database Diagnostic Monitor (ADDM) page.

ADDM provides recommendations with each ADDM finding. When a high-load SQL statement is identified, ADDM gives recommendations, such as running SQL Tuning Advisor on the SQL statement. You can begin tuning SQL statements as described in Chapter 10, "Tuning SQL Statements".

See Also:

"Overview of Automatic Database Diagnostic Monitor"

"Interpretation of Automatic Database Diagnostic Monitor Findings"

"Implementing Automatic Database Diagnostic Monitor Recommendations"

Identifying High-Load SQL Statements Using Top SQL

ADDM automatically identifies high-load SQL statements that may be causing systemwide performance degradation. Under normal circumstances, manual identification of high-load SQL statements is not necessary. In some cases, however, you may want to monitor SQL statements at a more granular level. The Top SQL section of the Top Activity page in Enterprise Manager enables you to identify high-load SQL statements for any 5-minute interval.

Figure 9-1 shows an example of the Top Activity page.

Figure 9-1 Top Activity Page

Description of Figure 9-1 follows
Description of "Figure 9-1 Top Activity Page"

To access the Top Activity page: 

From the Database Home page, click Performance.

The Performance page appears.

Under Additional Monitoring Links, click Top Activity.

The Top Activity page appears.

This page shows a 1-hour time line of the top activity running on the database. SQL statements that are using the highest percentage of database activity are listed under the Top SQL section, and are displayed in 5-minute intervals.

To move the 5-minute interval, drag the shaded box to the desired time.

The information contained in the Top SQL section will be automatically updated to reflect the selected time period. Use this page to identify high-load SQL statements that may be causing performance problems.

To monitor SQL statements for a longer duration than one hour, select Historical from the View Data list.

In Historical view, you can view the top SQL statements for the duration defined by the AWR retention period.

This section contains the following topics:

Viewing SQL Statements by Wait Class

Viewing Details of SQL Statements

Viewing SQL Statements by Wait Class

The SQL statements that appear in the Top SQL section of the Top Activity page are categorized into various wait classes, based on their corresponding class as described in the legend on the Top Activity chart.

To view the SQL statements for a particular wait class, click the block of color on the chart for the wait class, or its corresponding wait class in the legend. The Active Sessions Working page for the selected wait class appears, and the Top SQL section will be automatically updated to show only the SQL statements for that wait class.

The example in Figure 9-2 shows the Active Sessions Working page for the CPU Used wait class. Only SQL statements that are consuming the most CPU time are displayed in the Top Working SQL section.

Figure 9-2 Viewing SQL Statements by Wait Class

Description of Figure 9-2 follows
Description of "Figure 9-2 Viewing SQL Statements by Wait Class"

See Also:

"Monitoring User Activity" for information about using the Active Sessions Working page

Viewing Details of SQL Statements

The Top SQL section of the Top Activity page displays the SQL statements executed within the selected 5-minute interval in descending order based on their resource consumption. The SQL statement at the top of this table represents the most resource-intensive SQL statement during that time period, followed by the second most resource-intensive SQL statement, and so on.

In the example shown in Figure 9-2, "Viewing SQL Statements by Wait Class", the SELECT statement with the SQL ID axabnfyfp4r3p is consuming 12.14% of database activity and should be investigated.

To view details of SQL statements: 

From the Database Home page, click Performance.

The Performance page appears.

Under Additional Monitoring Links, click Top Activity.

The Top Activity page appears.

In the Top SQL section, click the SQL ID link of the SQL statement.

The SQL Details page for the selected SQL statement appears.

To view SQL details for a longer period, select Historical from the View Data list.

Description of sql_details_historical.gif follows
Description of the illustration sql_details_historical.gif

You can now view SQL details in the past, up to the duration defined by the AWR retention period.

In the Text section, review the SQL text for the SQL statement.

The Text section contains the SQL text for the selected SQL statement. Note that if only part of the SQL statement is displayed, then a plus sign (+) icon appears next to the Text heading. To view the SQL text for the entire SQL statement, click the plus sign (+) icon.

In this example, the text of SQL statement batd1pgpg49zf is as follows:

SELECT E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME
FROM   HR.EMPLOYEES E, HR.DEPARTMENTS D, HR.JOBS J
WHERE  E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND    E.JOB_ID = J.JOB_ID
AND    E.LAST_NAME LIKE 'A%'

In the Plan Hash Values list in the Details section, do one of the following:

If the SQL statement has multiple plans, then select All to show SQL details for all plans.

Select a particular plan to display SQL details for this plan only.

View the subpages available on the SQL Details page to display additional information about the SQL statement, as described in the following sections:

Viewing SQL Statistics

Viewing Session Activity

Viewing the SQL Execution Plan

Viewing the Plan Control

Viewing the Tuning History

If the SQL statement is a high-load SQL statement, then tune it as described in Chapter 10, "Tuning SQL Statements".

Viewing SQL Statistics

The Statistics subpage of the SQL Details page displays statistical information about the SQL statement.

To view statistics for the SQL statement: 

On the SQL Details page, under Details, click Statistics.

The SQL Details page appears, showing the Statistics subpage.

Description of sql_details_statistics.gif follows
Description of the illustration sql_details_statistics.gif

View the statistics for the SQL statement, as described in the following sections:

SQL Statistics Summary

General SQL Statistics

Activity by Wait Statistics and Activity by Time Statistics

Elapsed Time Breakdown Statistics

Shared Cursors Statistics and Execution Statistics

Other SQL Statistics

SQL Statistics Summary

The Summary section displays SQL statistics and activity on a chart.

In the Real Time view, the Active Sessions chart shows the average number of active sessions executing the SQL statement in the last hour. If the SQL statement has multiple plans and All is selected in the Plan Hash Value list, then the chart will display each plan in different colors, enabling you to easily spot if the plan changed and whether this may be the cause of the performance degradation. Alternatively, you can select a particular plan to display that plan only.

In the Historical view, the chart shows execution statistics in different dimensions. To view execution statistics, select the desired dimension from the View list:

Elapsed time per execution

Executions per hour

Disk reads per execution

Buffer gets per execution

This technique enables you to track the response time of the SQL statement using different dimensions. You can determine whether the performance of the SQL statement has degraded based on the dimension selected.

To view statistics of the SQL statement for a particular time interval, click the snapshot icon below the chart. You can also use the arrows to scroll the chart to locate a desired snapshot.

General SQL Statistics

The General section enables you to identify the origin of the SQL statement by listing the following information:

Module, if specified using the DBMS_APPLICATION_INFO package

Action, if specified using the DBMS_APPLICATION_INFO package

Parsing schema, or the database account used to execute the SQL statement

PL/SQL source, or the code line if the SQL statement is part of a PL/SQL program

Activity by Wait Statistics and Activity by Time Statistics

The Activity by Wait and Activity by Time sections enable you to identify how the SQL statement spent most of its time. The Activity by Wait section contains a graphical representation of how much elapsed time is consumed by CPU and by remaining waits. The Activity by Time section breaks out the total elapsed time into CPU time and wait time by seconds.

Elapsed Time Breakdown Statistics

The Elapsed Time Breakdown section enables you to identify if the SQL statement itself is consuming a lot of time, or if the total elapsed time is inflated due to the amount of time the originating program or application spent with the PL/SQL or Java engine. If the PL/SQL time or Java time makes up a significant portion of the elapsed time, then there may be minimal benefit gained by tuning the SQL statement. Instead, you should examine the application to determine how the PL/SQL time or Java time can be reduced.

Shared Cursors Statistics and Execution Statistics

The Shared Cursors Statistics and Execution Statistics sections provide information about the efficiency of various stages of the SQL execution process.

Other SQL Statistics

The Other Statistics section provides additional information about the SQL statement, such as average persistent and run-time memory.

Viewing Session Activity

The Activity subpage contains a graphical representation of the session activity.

To view session activity for the SQL statement: 

On the SQL Details page, under Details, click Activity.

The SQL Details page appears, showing the Activity subpage.

Description of sql_details_activity.gif follows
Description of the illustration sql_details_activity.gif

The Activity subpage displays details of various sessions executing the SQL statement. The Active Sessions chart profiles the average number of active sessions over time.

Optionally, drag the shaded box to select a 5-minute interval.

The Detail for Selected 5 Minute Interval section lists the sessions that executed the SQL statement during the selected 5-minute interval. The multicolored bar in the Activity % column depicts how the database time is divided for each session while executing the SQL statement.

Optionally, click the link in the SID column of the session you want to view to display the Session Details page.

See Also:

"Monitoring Top Sessions" for information about monitoring session activity and details

Viewing the SQL Execution Plan

The execution plan for a SQL statement is the sequence of operations Oracle Database performs to run the statement. The Plan subpage displays the execution plan for the SQL statement in a graph view and a table view.

To view the execution plan for the SQL statement: 

On the SQL Details page, under Details, click Plan.

The SQL Details page appears, showing the Plan subpage.

Click Graph to view the SQL execution in a graph view.

Description of sql_details_plan_graph.gif follows
Description of the illustration sql_details_plan_graph.gif

Optionally, select an operation in the graph to display details about the operations shown in the execution plan.

The Selection Details section refreshes to show details about the selected operations.

If the selected operation is on a particular database object (such as a table), then click the Object link to view further details about the database object.

To view the SQL execution in a table view, click Table.

The Plan subpage refreshes to show the explain plan in a table.

Description of sql_details_plan_table.gif follows
Description of the illustration sql_details_plan_table.gif

Query rewrite is an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes materialized views. The database compares the cost for the query, with and without query rewrite, and selects the least costly option. If a rewrite is necessary, then query rewrite and its cost benefit are shown in the Explain Rewrite section.

See Also:

Chapter 10, "Tuning SQL Statements" for information about execution plan and the query optimizer

Viewing the Plan Control

The Plan Control subpage contains information about the following items:

SQL profiles

A SQL profile contains additional statistics for the SQL statement. The optimizer uses these statistics to generate a better execution plan for the statement.

SQL patches

A SQL patch is automatically generated to work around an error or performance problem for a single SQL statement.

SQL plan baselines

A SQL plan baseline is an execution plan proven to have acceptable performance for a given SQL statement.

To view plan control information: 

On the SQL Details page, under Details, click Plan Control.

The SQL Details page appears, showing the Plan Control subpage.

Review the plan-related information.

In the following example, the optimizer used a SQL plan baseline named STMT01 for the SQL statement.

Description of sql_details_plan_control.gif follows
Description of the illustration sql_details_plan_control.gif

See Also:

Chapter 10, "Tuning SQL Statements" for information about SQL Tuning Advisor and SQL profiles

"Managing SQL Profiles"

Chapter 11, "Optimizing Data Access Paths" for information about SQL Access Advisor

Viewing the Tuning History

The SQL Tuning History section displays a history of SQL Tuning Advisor and SQL Access Advisor tasks.

To view the SQL tuning history: 

On the SQL Details page, under Details, click Tuning History.

The SQL Details page appears, showing the Tuning History subpage.

Review the information about the tuning history.

The ADDM Findings for this SQL During Historic Period section displays the number of occurrences of ADDM findings that are associated with the SQL statement.

The following example shows that SQL tuning task was performed by user DBA1 on February 9, 2009.

Description of sql_details_tuning.gif follows
Description of the illustration sql_details_tuning.gif

The SQL Tuning History section displays a history of SQL Tuning Advisor or SQL Access Advisor tasks.

The ADDM Findings for this SQL During Historic Period section displays the number of occurrences of ADDM findings that are associated with the SQL statement.

Description of sql_details_tuning.gif follows
Description of the illustration sql_details_tuning.gif

See Also:

Chapter 10, "Tuning SQL Statements" for information about SQL Tuning Advisor and SQL profiles

"Managing SQL Profiles"

Chapter 11, "Optimizing Data Access Paths" for information about SQL Access Advisor

10 Tuning SQL Statements

A SQL statement expresses the data you want Oracle Database to retrieve. For example, a SQL statement can retrieve the names of employees in a department. When Oracle Database executes the SQL statement, the query optimizer (also called the optimizer) first determines the best and most efficient way to retrieve the results.

The optimizer determines whether it is more efficient to read all data in the table, called a full table scan, or use an index. It compares the cost of all possible approaches and chooses the approach with the least cost. The access method for physically executing a SQL statement is called an execution plan, which the optimizer is responsible for generating. The determination of an execution plan is an important step in the processing of any SQL statement, and can greatly affect execution time.

The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can run the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics. Additionally, Oracle Enterprise Manager (Enterprise Manager) enables you to accept and implement many of these recommendations easily.

SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements. If significant performance improvements can be gained by creating a new index, then SQL Tuning Advisor may recommend it. However, such recommendations should be verified by running SQL Access Advisor using a SQL workload that contains a set of representative SQL statements.

This chapter describes how to tune SQL statements using SQL Tuning Advisor and contains the following sections:

Tuning SQL Statements Using SQL Tuning Advisor

Managing SQL Tuning Sets

Managing SQL Profiles

Managing SQL Execution Plans

See Also:

Chapter 9, "Identifying High-Load SQL Statements"

Chapter 11, "Optimizing Data Access Paths" for information about SQL Access Advisor

Tuning SQL Statements Using SQL Tuning Advisor

You can use SQL Tuning Advisor to tune one or more SQL statements. When tuning multiple statements, SQL Tuning Advisor does not recognize interdependencies between the SQL statements. Instead, SQL Tuning Advisor provides a convenient way to obtain tuning advice for a large number of SQL statements.

Oracle Database can generate SQL tuning reports automatically. Automatic SQL Tuning runs during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of high-load SQL statements. A maintenance window is a contiguous time interval during which automated maintenance tasks are run.

Tuning SQL Manually Using SQL Tuning Advisor

As described in Chapter 9, "Identifying High-Load SQL Statements", Automatic Database Diagnostic Monitor (ADDM) automatically identifies high-load SQL statements. If ADDM identifies such statements, then click Schedule/Run SQL Tuning Advisor on the Recommendation Detail page to run SQL Tuning Advisor.

To tune SQL statements manually using SQL Tuning Advisor: 

On the Database Home page, under Related Links, click Advisor Central.

The Advisor Central page appears.

Under Advisors, click SQL Advisors.

The SQL Advisors page appears.

Under SQL Tuning Advisor, click SQL Tuning Advisor.

The Schedule SQL Tuning Advisor page appears.

Description of sql_tuning_advisor_empty.gif follows
Description of the illustration sql_tuning_advisor_empty.gif

In the Name field, enter a name for the SQL tuning task.

If unspecified, then SQL Tuning Advisor uses a system-generated name.

Do one of the following:

To run a SQL tuning task for one or more high-load SQL statements, under SQL Tuning Advisor Data Source Links, click Top Activity.

The Top Activity page appears.

Under Top SQL, select the SQL statement you want to tune and click Schedule SQL Tuning Advisor. See "Identifying High-Load SQL Statements Using Top SQL" to learn how to identify high-load SQL statements using the Top Activity page.

To run a SQL tuning task for historical SQL statements from the Automatic Workload Repository (AWR), under SQL Tuning Advisor Data Source Links, click Historical SQL (AWR).

The Historical SQL (AWR) page appears.

Under Historical SQL (AWR), click the band below the chart, and select the 24-hour interval for which you want to view SQL statements that ran on the database. Under Detail for Selected 24 Hour Interval, select the SQL statement you want to tune, and click Schedule SQL Tuning Advisor.

To run a SQL tuning task for a SQL tuning set, click SQL Tuning Sets.

The SQL Tuning Sets page appears.

Select the SQL tuning set that contains the SQL statements you want to tune and then click Schedule SQL Tuning Advisor. See "Creating a SQL Tuning Set" to learn how to create SQL tuning sets.

The Schedule SQL Tuning Advisor page reappears.

To display the SQL text of the selected statement, expand SQL Statements.

Description of sql_tuning_sql_statements.gif follows
Description of the illustration sql_tuning_sql_statements.gif

Under Scope, select the scope of tuning to perform. Do one of the following:

Select Limited.

A limited scope takes approximately 1 second to tune each SQL statement but does not recommend a SQL profile.

Select Comprehensive, and then set a time limit (in minutes) for each SQL statement in the Time Limit per Statement field, and a total time limit (in minutes) in the Total Time Limit field. Note that setting the time limit too small may affect the quality of the recommendations.

Comprehensive mode may take several minutes to tune a single SQL statement. This mode is both time and resource intensive because each query must be hard-parsed. Thus, you should only use comprehensive scope for high-load SQL statements that have a significant impact on the entire system.

See "Managing SQL Profiles" to learn more about SQL profiles.

Under Schedule, do one of the following:

Select Immediately and then click Submit to run the SQL tuning task immediately.

The Processing: SQL Tuning Advisor Task page appears.

Select Later to schedule a specific time in the future, and then click OK.

On the Database Home page, under Related Links, click Advisor Central.

The Advisor Central page appears.

Under Advisor Tasks, the Results sections lists the result of advisors.

Description of sta_advisor_results.gif follows
Description of the illustration sta_advisor_results.gif

Select a result from the table and then click View Result.

The Recommendations for SQL ID page appears.

Description of rec_for_sql_id.gif follows
Description of the illustration rec_for_sql_id.gif

If you used a SQL tuning set, then multiple recommendations may be shown. To help you decide whether to implement a recommendation, an estimated benefit of implementing the recommendation is displayed in the Benefit (%) column. The Rationale column displays an explanation of why the recommendation is made.

To implement the recommendation, do one of the following:

If an automated solution is recommended, then click Implement.

A confirmation page appears. Click Yes to confirm the change.

If a manual solution is recommended, then consider implementing the recommendation.

Viewing Automatic SQL Tuning Results

By analyzing data in the Automatic Workload Repository (AWR), the database can identify routine maintenance tasks. The automated maintenance tasks infrastructure (known as AutoTask) schedules these tasks to run in maintenance windows.

Maintenance windows are Oracle Scheduler time intervals that belong to the window group named MAINTENANCE_WINDOW_GROUP. By default, one window is scheduled for each day of the week. You can customize attributes of these maintenance windows, including start and end times, frequency, and days of the week.

By default, AutoTask runs the following automated maintenance tasks in all maintenance windows:

Optimizer Statistics Collection

Segment Advisor

SQL Tuning Advisor

You can view the results of automated execution of SQL Tuning Advisor on observed high-load SQL statements.

To view automatic SQL tuning results: 

On the Database Home page, under Related Links, click Advisor Central.

The Advisor Central page appears.

Under Advisors, click SQL Advisors.

The SQL Advisors page appears.

Under SQL Tuning Advisor, click Automatic SQL Tuning Results.

The Automatic SQL Tuning Result Summary page appears.

The top half of the page includes sections for the status and activity summary of the SQL Tuning task.

Description of sql_tuning_auto_result.gif follows
Description of the illustration sql_tuning_auto_result.gif

In the Time Period list, select All and then click Go.

The Overall Task Statistics and Profile Effect Statistics sections are refreshed.

Description of sql_tuning_auto_stats.gif follows
Description of the illustration sql_tuning_auto_stats.gif

Optionally, in the Task Status section, click Configure to change the attributes of the Automatic SQL Tuning task.

The Automated Maintenance Tasks Configuration page appears.

In this page, you can enable or disable the Automatic SQL Tuning task and specify which days it should run. Click Apply or Revert to return to the previous page.

In the Task Activity Summary section, leave All selected for the Time Period and then click View Report.

The Automatic SQL Tuning Result Details page appears.

The page lists SQL statements that have been automatically selected by the database as candidates for SQL tuning.

Description of sql_tuning_result_details.gif follows
Description of the illustration sql_tuning_result_details.gif

Under Recommendations, select a SQL statement and then click View Recommendations.

The Recommendations for SQL ID page appears.

Description of sql_tuning_recommend.gif follows
Description of the illustration sql_tuning_recommend.gif

This page can include recommendations for SQL profiles and indexes. See "Tuning SQL Manually Using SQL Tuning Advisor" to learn how to implement recommendations made by SQL Tuning Advisor.

Managing SQL Tuning Sets

A SQL tuning set is a database object that includes one or more SQL statements and their execution statistics and context. You can use the set as an input for advisors such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the cursor cache, or high-load SQL statements that you identify.

A SQL tuning set includes the following:

A set of SQL statements

Associated execution context such as:

User schema

Application module name and action

List of bind values

Cursor compilation environment

Associated basic execution statistics such as:

Elapsed time and CPU time

Buffer gets

Disk reads

Rows processed

Cursor fetches

Number of executions and number of complete executions

Optimizer cost

Command type

Associated execution plans and row source statistics for each SQL statement (optional)

SQL statements can be filtered using the application module name and action, or any of the execution statistics. In addition, SQL statements can be ranked based on any combination of execution statistics.

SQL tuning sets are transportable, enabling SQL workloads to be transferred between databases for remote performance diagnostics and tuning. When high-load SQL statements are identified on a production system, it may not be desirable to perform investigation and tuning activities directly on this system. This feature enables you to transport the high-load SQL statements to a test system, where they can be safely analyzed and tuned. For information about transporting SQL tuning sets, see Oracle Database Performance Tuning Guide.

Using Enterprise Manager, you can manage SQL tuning sets by doing the following:

Creating a SQL Tuning Set

Dropping a SQL Tuning Set

Transporting SQL Tuning Sets

Creating a SQL Tuning Set

This section describes how to create a SQL tuning set with Enterprise Manager.

To create a SQL tuning set: 

Specify the initial options for the SQL tuning set, as described in "Creating a SQL Tuning Set: Options".

Select the load method to use for collecting and loading SQL statements into the SQL tuning set, as described in "Creating a SQL Tuning Set: Load Method".

Specify the filter options for the SQL tuning set, as described in "Creating a SQL Tuning Set: Filter Options".

Schedule and submit a job to collect the SQL statements and load them into the SQL tuning set, as described in "Creating a SQL Tuning Set: Schedule".

Creating a SQL Tuning Set: Options

The first step in creating a SQL tuning set is to specify options for the set such as name, owner, and description.

To specify options for creating a SQL tuning set: 

On the Database Performance page, under Additional Monitoring Links, click SQL Tuning Sets.

The SQL Tuning Sets page appears.

Click Create.

The Create SQL Tuning Set: Options page appears.

Enter the following details:

In SQL Tuning Set Name, enter a name for the SQL tuning set.

In Owner, enter the owner of the SQL tuning set.

In Description, enter a description of the SQL tuning set.

Optionally, to create an empty SQL tuning set and add SQL statements to it at a later time, complete the following steps:

Select Create an empty SQL tuning set.

Click Next.

The Create SQL Tuning Set: Review page appears.

Review your SQL tuning set options and click Submit.

The empty SQL tuning set is created. You can add SQL statements to it later.

Click Next.

The Create SQL Tuning Set: Load Methods page appears.

Description of sts_load_methods.gif follows
Description of the illustration sts_load_methods.gif

Proceed to the next step, as described in "Creating a SQL Tuning Set: Load Method".

Creating a SQL Tuning Set: Load Method

After options are specified for the SQL tuning set, select the load method to use for collecting and loading SQL statements into the SQL tuning set, as described in the following sections:

Loading Active SQL Statements Incrementally from the Cursor Cache

Loading SQL Statements from the Cursor Cache

Loading SQL Statements from AWR Snapshots

Loading SQL Statements from AWR Baselines

Loading SQL Statements from a User-Defined Workload

Tip:

Before selecting the load method for the SQL tuning set, create a SQL tuning set and specify the initial options, as described in "Creating a SQL Tuning Set: Options"

Loading Active SQL Statements Incrementally from the Cursor Cache

You can load active SQL statements from the cursor cache into the SQL tuning set incrementally over a specified period of time. This technique enables you to not only collect current and recent SQL statements stored in the SQL cache, but also SQL statements that will run during a specified time period in the future.

To load active SQL statements incrementally from the cursor cache: 

On the Create SQL Tuning Set: Load Methods page, select Incrementally capture active SQL statements over a period of time from the cursor cache.

In the Duration field, specify how long active SQL statements will be captured.

In the Frequency field, specify how often active SQL statements will be captured during the specified duration.

Click Next.

The Create SQL Tuning Set: Filter Options page appears.

Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".

Loading SQL Statements from the Cursor Cache

You can load SQL statements from the cursor cache into the SQL tuning set. However, because only current and recent SQL statements are in the SQL cache, collecting these statements only once may result in a SQL tuning set this is not representative of the entire database workload.

To load SQL statements from the cursor cache: 

On the Create SQL Tuning Set: Load Methods page, select Load SQL statements one time only.

From the Data Source list, select Cursor Cache.

Click Next.

The Create SQL Tuning Set: Filter Options page is shown.

Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".

Loading SQL Statements from AWR Snapshots

You can load SQL statements captured in AWR snapshots. This is useful when you want to collect SQL statements for specific snapshot periods of interest that can be used for later comparison or tuning purposes.

To load SQL statements from AWR snapshots: 

On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.

In the Data Source list, select AWR Snapshots.

In the AWR Snapshots field, select the snapshots to include. Do one of the following:

Select either ALL or a time period such as Last 24 hours and then go to Step 5.

Only snapshots that are captured and stored in AWR in the specified time will be included.

Select Customize and then go to Step 4.

Only snapshots that are captured and stored in AWR during a customized time period that you specify will be included.

To select a customized time period of snapshots, complete the following steps:

Select Customize and then click Go.

The Select Time Period window opens.

For the starting snapshot, select Period Start Time and then click the snapshot icon below the Active Session graph that corresponds to the desired start time.

For the ending snapshot, select Period End Time and then click the snapshot icon below the Active Session graph that corresponds to the desired end time.

Click Select.

In this example, the snapshot taken on February 17, 2009 at 12:10 p.m. is selected as the start time, and the snapshot taken on February 17, 2009 at 1:30 p.m. is selected as the end time.

Description of sts_load_time_period.gif follows
Description of the illustration sts_load_time_period.gif

Click Next.

The Create SQL Tuning Set: Filter Options page is shown.

Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".

Loading SQL Statements from AWR Baselines

You can load SQL statements captured in AWR baselines. This technique is useful when you want to collect SQL statements that are representative of a time period during known performance levels that can be used for later comparison or tuning purposes.

To load SQL statements from AWR baselines: 

On the Create SQL Tuning Set: Load Methods page, select Load SQL statements one time only.

In the Data Source field, select AWR Baseline.

In the AWR Baseline field, select the baseline to include.

Description of sts_load_baseline.gif follows
Description of the illustration sts_load_baseline.gif

Click Next.

The Create SQL Tuning Set: Filter Options page is shown.

Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".

Loading SQL Statements from a User-Defined Workload

You can load SQL statements by importing from a table or view. This technique is useful if the workload you want to analyze is not currently running on the database or captured in an existing AWR snapshot or AWR baseline.

There are no restrictions on which schema the workload resides in, the name of the table, or the number of tables that you can define. The only requirement is that the format of the table must match format of the USER_WORKLOAD table.

To load SQL statements from a user-defined workload: 

On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.

In the Data Source field, select User-Defined Workload.

In the User-Defined Workload field, select the table or view to include.

Description of sts_load_workload.gif follows
Description of the illustration sts_load_workload.gif

Click Next.

The Create SQL Tuning Set: Filter Options page is shown.

Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".

Creating a SQL Tuning Set: Filter Options

After the load method is selected, you can apply filters to reduce the scope of the SQL statements found in the SQL tuning set. While using filters is optional, it can be very beneficial due to the following:

Using filters directs the various advisors that use the SQL tuning set as a workload source, such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer, to make recommendations based on a specific subset of SQL statements, which may lead to better recommendations.

Using filters removes extraneous SQL statements from the SQL tuning set, which may greatly reduce processing time when it is used as a workload source for the various advisors.

Tip:

Before you can specify the filter options for the SQL tuning set, do the following:

Create a SQL tuning set and specify the initial options, as described in "Creating a SQL Tuning Set: Options"

Select the load method, as described in "Creating a SQL Tuning Set: Load Method"

To specify filter options for a SQL tuning set: 

On the Create SQL Tuning Set: Filter Options page, specify the values of filter conditions that you want use in the search in the Value column, and an operator or a condition in the Operator column.

Only the SQL statements that meet all of the specified filter conditions will be added to the SQL tuning set. Unspecified filter values will not be included as filter conditions in the search.

By default, the following filter conditions are displayed:

Parsing Schema Name

SQL Text

SQL ID

Elapsed Time (sec)

To add filter conditions, under Filter Conditions, select the filter condition you want to add and click Add a Filter or Column.

After the desired filter conditions have been added, specify their values in the Value column, and an operator or a condition in the Operator column.

To remove any unused filter conditions, click the icon in the Remove column for the corresponding filter condition you want to remove.

Click Next.

The Create SQL Tuning Set: Schedule page appears.

Proceed to the next step, as described in "Creating a SQL Tuning Set: Schedule".

Creating a SQL Tuning Set: Schedule

After the filter options are specified for the SQL tuning set, you can schedule and submit a job to collect the SQL statements and load them into the SQL tuning set.

Tip:

Before you can schedule a job to create the SQL tuning set, do the following:

Create a SQL Tuning Set and specify the initial options, as described in "Creating a SQL Tuning Set: Options".

Select the load method, as described in "Creating a SQL Tuning Set: Load Method".

Specify the filter options, as described in "Creating a SQL Tuning Set: Filter Options".

To schedule and submit a job to create a SQL tuning set: 

On the Create SQL Tuning Set: Schedule page, under Job Parameters, enter a name in the Job Name field if you do not want to use the system-generated job name.

In the Description field, enter a description of the job.

Under Schedule, do one of the following:

Immediately to run the job immediately after it has been submitted

Later to run the job at a later time as specified using the Time Zone, Date, and Time fields

Description of sts_schedule.gif follows
Description of the illustration sts_schedule.gif

Click Next.

The Create SQL Tuning Set: Review page appears.

Description of sts_review.gif follows
Description of the illustration sts_review.gif

Review the SQL Tuning Set options that you have selected.

To view the SQL statements used by the job, expand Show SQL.

Click Submit.

The SQL Tuning Sets page appears.

If the job was scheduled to run immediately, then a message is displayed to inform you that the job and the SQL tuning set were created successfully. If the job was scheduled to run at a later time, a message is displayed to inform you that the job was created successfully.

To view details about the job, such as operation status, click View Job Details.

The View Job page appears to display details about the job.

Dropping a SQL Tuning Set

This section describes how to drop a SQL tuning set. To conserve storage space, you may want to periodically drop unused SQL tuning sets stored in the database.

To drop a SQL tuning set: 

On the Database Performance page, under Additional Monitoring Links, click SQL Tuning Sets.

The SQL Tuning Sets page appears.

Existing SQL tuning sets are displayed on this page.

Select the SQL tuning set you want to drop and then click Drop.

The Confirmation page appears to verify that you want to drop the selected SQL tuning set.

Click Yes.

The SQL Tuning Sets page appears.

A confirmation message is displayed to indicate that the SQL tuning set was successfully dropped.

Transporting SQL Tuning Sets

You can transport SQL tuning sets from one system to another by first exporting a SQL tuning set from one database, and then importing it into another database.

This section contains the following topics:

Exporting a SQL Tuning Set

Importing a SQL Tuning Set

Exporting a SQL Tuning Set

This section describes how to export a SQL tuning set, enabling it to be transported to another system.

To export a SQL tuning set: 

On the Database Performance page, under Additional Monitoring Links, click SQL Tuning Sets.

The SQL Tuning Sets page appears.

Existing SQL Tuning Sets are displayed on this page.

Select the SQL tuning set you want to export and then click Export.

The Export SQL Tuning Set page appears.

Description of sts_export.gif follows
Description of the illustration sts_export.gif

In the Directory Object field, select a directory where the export file will be created.

For example, to use the Oracle Data Pump directory, select DATA_PUMP_DIR. The Directory Name field refreshes automatically to indicate the selected directory.

In the Export File field, enter a name for the file to be database.

Alternatively, you can accept the name generated by the database.

In the Log File field, enter a name for the log file for the export operation.

Alternatively, you can accept the name generated by the database.

Select a tablespace to temporarily store the data for the export operation.

By default, SYSAUX is used.

Under Job Parameters, in the Job Name field, enter a name for the job.

Alternatively, you can accept the name generated by the database.

Under Schedule, do one of the following:

Select Immediately to run the job immediately after it has been submitted.

Select Later to run the job at a later time as specified by selecting or entering values in the Time Zone, Date, and Time fields.

Click OK.

The SQL Tuning Sets page appears.

A confirmation message indicates that the job was created successfully.

Transport the export file to another system using the mechanism of choice, such as Oracle Data Pump or a database link.

Importing a SQL Tuning Set

Before a SQL tuning set can be imported, you must first export a SQL tuning set from another system and transport it to your current system. For more information, see "Exporting a SQL Tuning Set".

To import a SQL tuning set: 

On the Database Performance page, under Additional Monitoring Links, click SQL Tuning Sets.

The SQL Tuning Sets page appears.

Click Import.

The Import SQL Tuning Set page appears.

In Directory Object, select the directory containing the file to be imported.

The directory should contain the export file that was transported to your current system. For example, if the file resides in the Data Pump directory, then select DATA_PUMP_DIR. The Directory Name field refreshes automatically to indicate the selected directory.

In the Import File field, enter the name of the dump file that will be imported.

In the Log File field, enter a name for the log file for the import operation.

To replace an existing SQL tuning set with the one that you are importing, select Replace the existing SQL tuning set if one exists.

Select a tablespace to temporarily store the data for the import operation.

By default, SYSAUX is used.

Under Job Parameters, in the Job Name field, enter a name for the job.

Alternatively, you can accept the name generated by the system.

Under Schedule, do one of the following:

Select Immediately to run the job immediately after it has been submitted.

Select Later to run the job at a later time as specified by selecting or entering values in the Time Zone, Date, and Time fields.

Click OK.

The SQL Tuning Sets page appears.

A confirmation message is displayed to indicate that the job was successfully created. If the job is scheduled to run immediately, then the imported SQL tuning set will be shown on this page. You may need to refresh to see the SQL tuning set.

Managing SQL Profiles

A SQL profile is a set of auxiliary information that is built during automatic tuning of a SQL statement. A SQL profile is to a SQL statement what statistics are to a table.

When running a SQL Tuning Advisor task with a limited scope, the optimizer makes estimates about cardinality, selectivity, and cost that are sometimes significantly off, resulting in poor execution plans. To address this problem, consider running a SQL Tuning Advisor task with a comprehensive scope to collect additional information using sampling and partial execution techniques into a SQL profile. The database can use the profile to verify and, if necessary, adjust optimizer estimates.

During SQL profiling, the optimizer uses the execution history of the SQL statement to create appropriate settings for optimizer parameters. After SQL profiling completes, the optimizer uses the information in the SQL profile and regular database statistics to generate execution plans. The additional information enables the database to produce well-tuned plans for corresponding SQL statements.

After running a SQL Tuning Advisor task with a comprehensive scope, a SQL profile may be recommended. If you accept the recommendation, then the database creates the SQL profile and enables it for the SQL statement.

In some cases, you may want to disable a SQL profile. For example, you may want to test the performance of a SQL statement without using a SQL profile to determine if the SQL profile is actually beneficial. If the SQL statement is performing poorly after the SQL profile is disabled, then you should enable it again to avoid performance degradation. If the SQL statement is performing optimally after you have disabled the SQL profile, then you may want to remove the SQL profile from your database.

To enable, disable, or delete a SQL profile: 

On the Performance page, click Top Activity.

The Top Activity page appears.

Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.

The SQL Details page appears.

Click the Plan Control tab.

A list of SQL profiles is displayed under SQL Profiles and Outlines.

Select the SQL profile you want to manage. Do one of the following:

To enable a SQL profile that is disabled, click Disable/Enable.

To disable a SQL profile that is enabled, click Disable/Enable.

To remove a SQL profile, click Delete.

A confirmation page appears.

Click Yes to continue, or No to cancel the action.

See Also:

Oracle Database Performance Tuning Guide to learn how to manage SQL profiles using an API

Managing SQL Execution Plans

SQL plan management is a preventative mechanism that records and evaluates execution plans of SQL statements over time. The database builds SQL plan baselines consisting of a set of efficient plans. If the same SQL statement runs repeatedly, and if the optimizer generates a new plan differing from the baseline, then the database compares the plan with the baseline and chooses the best one.

SQL plan management avoids SQL performance regression. Events such as new optimizer statistics, changes to initialization parameters, database upgrades, and so on can cause changes to execution plans. These changes can cause SQL performance regressions that are difficult and time-consuming to fix manually. SQL plan baselines preserve performance of SQL statements, regardless of changes in the database.

You can have the database capture SQL plan baselines automatically or you can load SQL execution plans manually.

To capture SQL plan baselines automatically: 

Access the Database Home page.

See Oracle Database 2 Day DBA for more information.

Select Performance, then SQL, and then SQL Plan Control.

If the Database Login page appears, then log in as a user with administrator privileges. The SQL Profile subpage of the SQL Plan Control page appears.

Click SQL Plan Baseline.

The SQL Plan Baseline subpage appears.

Description of sql_plan_control.gif follows
Description of the illustration sql_plan_control.gif

Under Settings, click the link next to Capture SQL Plan Baselines.

The Initialization Parameters page appears.

In the Value column of the table, select TRUE and then click OK.

You are returned to the SQL Plan Baseline subpage, which now shows Capture SQL Baselines set to TRUE.

Because you configured baselines to be captured, the database automatically keeps a history of execution plans for all SQL statements executed more than once.

To load SQL execution plans manually, the Capture SQL Baselines setting must be FALSE.

To manually load SQL execution plans: 

Access the Database Home page.

See Oracle Database 2 Day DBA for more information.

Select Performance, then SQL, and then SQL Plan Control.

Click SQL Plan Baseline.

The SQL Plan Baseline subpage appears.

Click Load.

The SQL Plan Control page appears.

Description of sql_plan_load.gif follows
Description of the illustration sql_plan_load.gif

Select the SQL plan baselines to be loaded by completing the following steps:

Under Load SQL Plan Baselines, select Load plans from SQL Tuning Set (STS).

In this example, load plans from the SQL tuning set that you created in "Creating a SQL Tuning Set".

In Job Name, enter a name for the job. For example, enter SPM_LOAD_TEST.

Under Schedule, select Immediately.

Click OK.

The SQL Profile subpage of the SQL Plan Control page appears.

The table displays a list of SQL plans that are stored as SQL plan baselines.

Description of sql_baselines.gif follows
Description of the illustration sql_baselines.gif

Optionally, fix the execution plan of a baseline to prevent the database from using an alternative SQL plan baseline. Complete the following steps:

Select a SQL plan baseline that is not fixed.

Select Fixed - Yes from the list preceding the baseline table.

Click Go.

The table is refreshed to show the SQL execution plan with the value YES in the Fixed column of the table.

See Also:

Oracle Database Performance Tuning Guide to learn how to use SQL plan management

11 Optimizing Data Access Paths

To achieve optimal performance for data-intensive queries, materialized views and indexes are essential for SQL statements. However, implementing these objects does not come without cost. Creation and maintenance of these objects can be time-consuming. Space requirements can be significant. SQL Access Advisor enables you to optimize query access paths by recommending materialized views and view logs, indexes, SQL profiles, and partitions for a specific workload.

A materialized view provides access to table data by storing query results in a separate schema object. Unlike an ordinary view, which does not take up storage space or contain data, a materialized view contains the rows from a query of one or more base tables or views. A materialized view log is a schema object that records changes to a master table's data, so that a materialized view defined on the master table can be refreshed incrementally. SQL Access Advisor recommends how to optimize materialized views so that they can be rapidly refreshed and make use of the query rewrite feature. To learn more about materialized views, see Oracle Database Concepts.

SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A bitmap index reduces response time for many types of ad hoc queries and can also reduce storage space compared to other indexes. A function-based index derives the indexed value from the table data. For example, to find character data in mixed cases, a function-based index search for values as if they were all in uppercase. B-tree indexes are commonly used to index unique or near-unique keys.

Using SQL Access Advisor involves the following tasks:

Running SQL Access Advisor

Reviewing the SQL Access Advisor Recommendations

Implementing the SQL Access Advisor Recommendations

See Also:

Chapter 9, "Identifying High-Load SQL Statements"

Chapter 10, "Tuning SQL Statements" for information about SQL Tuning Advisor

Oracle Database Concepts to learn about indexes

Running SQL Access Advisor

This section describes how to run SQL Access Advisor to make recommendations for a SQL workload.

To run SQL Access Advisor: 

Select the initial options, as described in "Running SQL Access Advisor: Initial Options".

Select the workload source you want to use for the analysis, as described in "Running SQL Access Advisor: Workload Source".

Define the filters options, as described in "Running SQL Access Advisor: Filter Options".

Choose the types of recommendations, as described in "Running SQL Access Advisor: Recommendation Options".

Schedule the SQL Access Advisor task, as described in "Running SQL Access Advisor: Schedule".

Running SQL Access Advisor: Initial Options

The first step in running SQL Access Advisor is to select the initial options on the SQL Access Advisor: Initial Options page.

To select initial options: 

On the Database Home page, under Related Links, click Advisor Central.

The Advisor Central page appears.

Under Advisors, click SQL Advisors.

The SQL Advisors page appears.

Click SQL Access Advisor.

The SQL Access Advisor: Initial Options page appears.

Do one of the following:

Select Verify use of access structures (indexes, materialized views, partitioning, and so on) only to verify existing structures.

Select Recommend new access structures to use the recommended options defined in the Oracle Enterprise Manager default template.

If you select this option, then you can optionally complete the following steps:

Select Inherit Options from a previously saved Task or Template to use the options defined in an existing SQL Access Advisor task or another template.

In Tasks and Templates, select the task or template that you want to use.

In this example, Recommend new access structures is selected.

Description of sql_access_initial_options.gif follows
Description of the illustration sql_access_initial_options.gif

Click Continue.

The SQL Access Advisor: Workload Source page appears.

Proceed to the next step, as described in "Running SQL Access Advisor: Workload Source".

Running SQL Access Advisor: Workload Source

After initial options are specified for SQL Access Advisor, select the workload source that you want to use for the analysis, as described in the following sections:

Using SQL Statements from the Cache

Using an Existing SQL Tuning Set

Using a Hypothetical Workload

Tip:

Before you can select the workload source for SQL Access Advisor, select the initial options, as described in "Running SQL Access Advisor: Initial Options".

Using SQL Statements from the Cache

You can use SQL statements from the cache as the workload source. However, only current and recent SQL statements are stored in the SQL cache, so this workload source may not be representative of the entire workload on your database.

To use SQL statements from the cache as the workload source: 

On the SQL Access Advisor: Workload Source page, select Current and Recent SQL Activity.

Description of sql_access_source_cache.gif follows
Description of the illustration sql_access_source_cache.gif

Proceed to the next step, as described in "Running SQL Access Advisor: Filter Options".

Using an Existing SQL Tuning Set

You can use an existing SQL tuning set as the workload source. This option is useful because SQL tuning sets can be used repeatedly as the workload source for SQL Access Advisor and SQL Tuning Advisor.

To use a SQL tuning set as the workload source: 

On the SQL Access Advisor: Workload Source page, select Use an existing SQL Tuning Set.

Click the SQL Tuning Set search icon to use an existing SQL tuning set.

The Search and Select: SQL Tuning Set dialog box appears.

In the Schema field, enter the name of the schema containing the SQL tuning set you want to use and then click Go.

A list of SQL tuning sets contained in the selected schema appears.

Select the SQL tuning set to be used for the workload source and click Select.

The Search and Select: SQL Tuning Set dialog box closes and the selected SQL Tuning Set now appears in the SQL Tuning Set field.

Proceed to the next step, as described in "Running SQL Access Advisor: Filter Options".

See Also:

"Managing SQL Tuning Sets"

Using a Hypothetical Workload

A dimension table stores all or part of the values for a logical dimension in a star or snowflake schema. You can create a hypothetical workload from dimension tables containing primary or foreign key constraints. This option is useful if the workload to be analyzed does not exist. In this case, SQL Access Advisor examines the current logical schema design, and provides recommendations based on the defined relationships between tables.

To use a hypothetical workload as the workload source: 

On the SQL Access Advisor: Workload Source page, select Create a Hypothetical Workload from the Following Schemas and Tables.

Leave Schemas and Tables empty and then click Add to search for tables.

The Workload Source: Search and Select Schemas and Tables page appears.

In the Tables section, enter a schema name in the Schema field and then click Search.

A list of tables in the selected schema is displayed.

Select the tables to be used in creating the hypothetical workload and then click Add Tables.

The selected tables now appear in the Schemas and Tables field.

Click OK.

The SQL Access Advisor: Workload Source page appears with the selected tables now added.

Proceed to the next step, as described in "Running SQL Access Advisor: Filter Options".

See Also:

Oracle Database Concepts for an overview of materialized views

Running SQL Access Advisor: Filter Options

After the workload source is selected, you can optionally apply filters to reduce the scope of the SQL statements found in the workload. Filters are beneficial for the following reasons:

Using filters directs SQL Access Advisor to make recommendations based on a specific subset of SQL statements from the workload, which may lead to better recommendations.

Using filters removes extraneous SQL statements from the workload, which may greatly reduce processing time.

Tip:

Before you can select the filter options for the workload, do the following:

Select initial options, as described in "Running SQL Access Advisor: Initial Options".

Select the workload source, as described in "Running SQL Access Advisor: Workload Source".

To apply filters to the workload source: 

On the SQL Access Advisor: Workload Source page, click Filter Options.

The Filter Options section expands.

Select Filter Workload Based on these Options.

The Filter Options section is enabled.

Define the filters you want to apply, as described in the following sections:

Defining Filters for Resource Consumption

Defining Filters for Users

Defining Filters for Tables

Defining Filters for SQL Text

Defining Filters for Modules

Defining Filters for Actions

Click Next.

The Recommendation Options page appears.

Proceed to the next step, as described in "Running SQL Access Advisor: Recommendation Options".

Defining Filters for Resource Consumption

The resource consumption filter restricts the workload to include only the number of high-load SQL statements that you specify.

To define a filter for resource consumption: 

On the SQL Access Advisor: Workload Source page, under User Resource Consumption, enter the number of high-load SQL statements in the Number of Statements field.

From the Order by list, select one of the methods by which the SQL statements are to be ordered.

Defining Filters for Users

The users filter restricts the workload to include or exclude SQL statements executed by users that you specify.

To define a filter for users: 

On the SQL Access Advisor: Workload Source page, under Users, select Include only SQL statements executed by these users or Exclude all SQL statements executed by these users.

To search for available users, click the Users search icon.

The Search and Select: Users dialog box appears.

Select the users whose SQL statements you want to include or exclude and then click Select.

The Search and Select: Users dialog box closes and the selected tables now appear in the Users field.

In this example, a filter is defined to include only SQL statements executed by the user SH.

Description of sql_access_filter_users.gif follows
Description of the illustration sql_access_filter_users.gif

Defining Filters for Tables

The tables filter restricts the workload to include or exclude SQL statements that access a list of tables that you specify. Table filters are not permitted if you selected the Create a Hypothetical Workload from the Following Schemas and Tables option, as described in "Using a Hypothetical Workload".

To define a filter for tables: 

To include only SQL statements that access a specific list of tables, enter the table names in the Include only SQL statements that access any of these tables field.

To exclude all SQL statements that access a specific list of tables, enter the table names in the Exclude all SQL statements that access any of these tables field.

To search for available tables, click the Tables search icon.

The Search and Select: Schema and Table dialog box appears.

Select the tables for which you want to include or exclude SQL statements and click Select.

The Search and Select: Schema and Table dialog box closes and the selected tables now appear in the corresponding Tables field.

Defining Filters for SQL Text

The SQL text filter restricts the workload to include or exclude SQL statements that contains SQL text substrings that you specify.

To define a filter for SQL text: 

To include only SQL statements that contains specific SQL text, enter the SQL text to be included in the Include only SQL statements containing these SQL text substrings field.

To exclude all SQL statements that contain specific SQL text, enter the SQL text to be excluded in the Exclude all SQL statements containing these SQL text substrings field.

Defining Filters for Modules

The module filter restricts the workload to include or exclude SQL statements that are associated with modules that you specify.

To define a filter for module ID: 

Do one of the following:

To include only SQL statements associated with a specific module ID in the workload, select Include only SQL statements associated with these modules.

To exclude all SQL statements associated to a specific module ID from the workload, select Exclude all SQL statements associated with these modules.

In the Modules field, enter the names of the modules for which associated SQL statements will be included or excluded.

Defining Filters for Actions

The actions filter restricts the workload to include or exclude SQL statements that are associated with actions that you specify.

To define a filter for actions: 

Do one of the following:

To include only SQL statements associated with a specific action in the workload, select Include only SQL statements associated with these actions.

To exclude all SQL statements associated with a specific action from the workload, select Exclude all SQL statements associated with these actions.

In the Actions field, enter the actions for which associated SQL statements will be included or excluded.

Running SQL Access Advisor: Recommendation Options

To improve the underlying data access methods chosen by the optimizer for the workload, SQL Access Advisor provides recommendations for indexes, materialized views, and partitioning. Using these access structures can significantly improve the performance of the workload by reducing the time required to read data from the database. However, you must balance the benefits of using these access structures against the cost to maintain them.

Tip:

Before you can select the recommendation options for SQL Access Advisor, do the following:

Select initial options, as described in "Running SQL Access Advisor: Initial Options".

Select the workload source, as described in "Running SQL Access Advisor: Workload Source".

Define the filter options, as described in "Running SQL Access Advisor: Filter Options".

To specify recommendation options: 

On the SQL Access Advisor: Recommendation Options page, under Access Structures to Recommend, select the type of access structures to be recommended by SQL Access Advisor:

Indexes

Materialized Views

Partitioning

In this example, all of the preceding access types are selected.

Description of sql_access_recommend_types.gif follows
Description of the illustration sql_access_recommend_types.gif

Under Scope, select the mode in which SQL Access Advisor will run. Do one of the following:

Select Limited.

In limited mode, SQL Access Advisor focuses on SQL statements with the highest cost in the workload. The analysis is quicker, but the recommendations may be limited.

Select Comprehensive.

In comprehensive mode, SQL Access Advisor analyzes all SQL statements in the workload. The analysis can take much longer, but the recommendations will be exhaustive.

In this example, Limited Mode is selected.

Description of sql_access_advisor_mode.gif follows
Description of the illustration sql_access_advisor_mode.gif

Optionally, click Advanced Options.

The Advanced Options section expands. This section contains the following subsections:

Workload Categorization

In this section, you can specify the type of workload for which you want a recommendation. The following categories are available:

Workload Volatility

Select Consider only queries if the workload primarily contains read-only operations, as in data warehouses. Volatility data is useful for online transaction processing (OLTP) systems, where the performance of INSERT, UPDATE, and DELETE operations is critical.

Workload Scope

Select Recommend dropping unused access structures if the workload represents all access structure use cases.

Space Restrictions

Indexes and materialized views increase performance at the cost of space. Do one of the following:

Select No, show me all recommendations (unlimited space) to specify no space limits. When SQL Access Advisor is invoked with no space limits, it makes the best possible performance recommendations.

Select Yes, limit additional space to and then enter the space limit in megabytes, gigabytes, or terabytes. When SQL Access Advisor is invoked with a space limit, it produces only recommendations with space requirements that do not exceed the specified limit.

Tuning Prioritization

This section enables you to specify how SQL statements will be tuned. Complete the following steps:

From the Prioritize tuning of SQL statements by list, select a method by which SQL statements are to be tuned and then click Add.

Optionally, select Allow Advisor to consider creation costs when forming recommendations to weigh the cost of creating access structures against the frequency and potential improvement of SQL statement execution time. Otherwise, creation cost will be ignored. You should select this option if you want specific recommendations generated for SQL statements that are executed frequently.

Default Storage Locations

Use this section to override the defaults defined for schema and tablespace locations. By default, indexes are in the schema and tablespace of the table they reference. Materialized views are in the schema and tablespace of the first table referenced in the query. Materialized view logs are in the default tablespace of the schema of the table that they reference.

Click Next.

The SQL Access Advisor: Schedule page appears.

Proceed to the next step, as described in "Running SQL Access Advisor: Schedule".

Running SQL Access Advisor: Schedule

Use the SQL Access Advisor Schedule page to set or modify the schedule parameters for the SQL Access Advisor task.

Figure 11-1 Scheduling a SQL Access Advisor Task

Description of Figure 11-1 follows
Description of "Figure 11-1 Scheduling a SQL Access Advisor Task"

Tip:

Before you can schedule a SQL Access Advisor task, do the following:

Select initial options, as described in "Running SQL Access Advisor: Initial Options".

Select the workload source, as described in "Running SQL Access Advisor: Workload Source".

Define the filter options, as described in "Running SQL Access Advisor: Filter Options".

Specify the recommendation options, as described in "Running SQL Access Advisor: Recommendation Options".

To schedule a SQL Access Advisor task: 

On the SQL Access Advisor: Schedule page, under Advisor Task Information, enter a name in the Task Name field if you do not want to use the system-generated task name.

In the example shown in Figure 11-1, SQLACCESS9084523 is entered.

In the Task Description field, enter a description of the task.

In the example shown in Figure 11-1, SQL Access Advisor is entered.

From the Journaling Level list, select the level of journaling for the task.

Journaling level controls the amount of information that is logged to the SQL Access Advisor journal during task execution. This information appears on the Details subpage when viewing task results.

In the example shown in Figure 11-1, Basic is selected.

In the Task Expiration (Days) field, enter the number of days the task will be retained in the database before it is purged.

In the example shown in Figure 11-1, 30 is entered.

In the Total Time Limit (minutes) field, enter the maximum number of minutes that the job is permitted to run.

You must enter a time in this field rather than use the default.

Under Scheduling Options, in the Schedule Type list, select a schedule type for the task and a maintenance window in which the task should run. Do one of the following:

Click Standard.

This schedule type enables you to select a repeating interval and start time for the task. Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

In the Repeat list, select Do Not Repeat to perform the task only once, or select a unit of time and enter the number of units in the Interval field.

Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

Click Use predefined schedule.

This schedule type enables you to select an existing schedule. Do one of the following:

In the Schedule field, enter the name of the schedule to be used for the task.

To search for a schedule, click the search icon.

The Search and Select: Schedule dialog box appears.

Select the desired schedule and click Select. The selected schedule now appears in the Schedule field.

Click Standard using PL/SQL for repeated interval.

This schedule type enables you to select a repeating interval and an execution time period (window) for the task. Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

Under Available to Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

In the Repeat list, select Do Not Repeat to perform the task only once, or select a unit of time and enter the number of units in the Interval field.

In the Repeated Interval field, enter a PL/SQL schedule expression, such as SYSDATE+1.

Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.

Click Use predefined window.

This schedule type enables you to select an existing window. Select Stop on Window Close to stop the job when the window closes. Do one of the following:

In the Window field, enter the name of the window to be used for the task.

To search for a window, click the search icon.

The Search and Select: Window and Window Groups dialog box appears.

Select the desired window and click Select. The selected window now appears in the Schedule field.

Click Event.

Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

Under Event Parameters, enter values in the Queue Name and Condition fields.

Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.

Click Calendar.

Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

Under Calendar Expression, enter a calendar expression.

Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.

In the example shown in Figure 11-1, Standard is selected for schedule type. The task will not repeat and is scheduled to start immediately.

Click Next.

The SQL Access Advisor: Review page appears.

Description of sql_access_review.gif follows
Description of the illustration sql_access_review.gif

Under Options, a list of modified options for the SQL Access Advisor task is shown. To display both modified and unmodified options, click Show All Options. To view the SQL text for the task, click Show SQL.

Click Submit.

The Advisor Central page appears. A message informs you that the task was created successfully.

Reviewing the SQL Access Advisor Recommendations

SQL Access Advisor graphically displays the recommendations and provides hyperlinks so that you can quickly see which SQL statements benefit from a recommendation. Each recommendation produced by the SQL Access Advisor is linked to the SQL statement it benefits.

Tip:

Before reviewing the SQL Access Advisor recommendations, run SQL Access Advisor to make the recommendations, as described in "Running SQL Access Advisor".

To review the SQL Access Advisor recommendations: 

On the Advisor Central page, select the SQL Access Advisor task for review and click View Result.

Description of sql_access_adv_results.gif follows
Description of the illustration sql_access_adv_results.gif

If the task is not displayed, then you may need to refresh the screen. The Results for Task page appears.

Review the Summary subpage, which provides an overview of the SQL Access Advisor analysis, as described in "Reviewing the SQL Access Advisor Recommendations: Summary".

Review the Recommendations subpage, which enables you to view the recommendations ranked by cost improvement, as described in "Reviewing the SQL Access Advisor Recommendations: Recommendations".

Review the SQL statements analyzed in the workload, as described in "Reviewing the SQL Access Advisor Recommendations: SQL Statements".

Review the details of the workload, task options, and the SQL Access Advisor task, as described in "Reviewing the SQL Access Advisor Recommendations: Details".

Reviewing the SQL Access Advisor Recommendations: Summary

The Summary subpage displays an overview of the SQL Access Advisor analysis.

To review the recommendations summary: 

On the Results for Tasks page, click Summary.

The Summary subpage of the Results for Tasks page appears.

In this example, Limited Mode is selected so that SQL Access Advisor analyzes the highest cost statements rather than all statements.

Description of sql_access_results_summary.gif follows
Description of the illustration sql_access_results_summary.gif

Under Overall Workload Performance, assess the potential for improvement in implementing the recommendations.

Use the Workload I/O Cost chart to compare the original workload I/O cost with the new cost.

In this example, the workload I/O cost will decrease from 107.1 million to 43.1 million by implementing the recommendations.

Use the Query Execution Time Improvement chart to compare the improvement in query execution time.

This chart shows the percentage of SQL statements in the workload whose execution time will improve by accepting the recommendations. The SQL statements are grouped by the projected improvement factor along the horizontal axis on the chart (1x to >10x). The percentage of SQL statements that will improve by the projected improvement factor are along the vertical axis (0% to 100%).

In this example, approximately 62 percent of SQL statements in the workload will not improve execution time, but about 25 percent will have the potential for improvement of over 4x or more.

Under Recommendations, click Show Recommendation Action Counts.

In the following example, creating 2 indexes, 4 materialized views, and 4 materialized view logs is recommended.

Description of sql_access_results_reccount.gif follows
Description of the illustration sql_access_results_reccount.gif

Under SQL Statements, click Show Statement Counts to display the type of SQL statement.

In the following example, 25 SELECT statements are analyzed.

Description of sql_access_results_sqlcount.gif follows
Description of the illustration sql_access_results_sqlcount.gif

Reviewing the SQL Access Advisor Recommendations: Recommendations

The Recommendations subpage ranks the SQL Access Advisor recommendations by cost improvement. You can also view details about each recommendation.

To review recommendation details: 

On the Results for Tasks page, click Recommendations.

The Recommendations subpage appears.

Description of sql_access_results_rec.gif follows
Description of the illustration sql_access_results_rec.gif

Use the Recommendations by Cost Improvement chart to view recommendations ordered by the cost improvement.

Under Select Recommendations for Implementation, each recommendation is listed with its implementation status, recommendation ID, cost improvement, space consumption, and the number of affected SQL statements for each recommendation. Implementing the top recommendation will have the biggest benefit to the total performance of the workload.

To view details for a particular recommendation, select the recommendation and click Recommendation Details.

The Recommendation Details page appears.

Description of sql_access_rec_details.gif follows
Description of the illustration sql_access_rec_details.gif

The Recommendation Details page displays all actions for the specified recommendation.

Under Actions, you can choose to modify the schema name, tablespace name, and storage clause for each action. To view the SQL text of an action, click the link in the Action column for the specified action.

Under SQL Affected by Recommendation, the SQL text of the SQL statement and cost improvement information are displayed.

Click OK.

The Recommendations subpage appears.

To view the SQL text of a recommendation, select the recommendation and click Show SQL.

The Show SQL page for the selected recommendation appears.

Description of sql_access_show_sql.gif follows
Description of the illustration sql_access_show_sql.gif

Reviewing the SQL Access Advisor Recommendations: SQL Statements

The SQL Statements subpage ranks SQL statements in the workload by cost improvement. You can use this page to view details about the SQL statements analyzed in the workload.

To review SQL statements: 

On the Results for Tasks page, click SQL Statements.

The SQL Statements subpage appears.

Description of sql_access_results_sql.gif follows
Description of the illustration sql_access_results_sql.gif

Use the SQL Statements by Cost Improvement chart to view SQL statements in the workload ordered by the cost improvement.

Under Select SQL Statements to be Improved, each SQL statement is listed with its statement ID, SQL text, associated recommendation, cost improvement, and execution count.

Implementing the recommendation associated with the top SQL statement will have the biggest benefit to the total performance of the workload. In this example, implementing the recommendation with ID 4 will produce the biggest benefit, a cost improvement of 73.68%, for the SQL statement with ID 1052.

To view the SQL text of a recommendation, select the recommendation and click Show SQL.

The Show SQL page for the selected recommendation appears.

Reviewing the SQL Access Advisor Recommendations: Details

The Details subpage displays a list of all the workload and task options used in the analysis. You can also use this page to view a list of journal entries for the task, based on the journaling level used when the task was created.

To review workload and task details: 

On the Results for Tasks page, click Details.

The Details subpage appears.

Description of sql_access_results_details.gif follows
Description of the illustration sql_access_results_details.gif

Under Workload and Task Options, a list of options that were selected when the advisor task was created is displayed.

Under Journal Entries, a list of messages that were logged to the SQL Access Advisor journal while the task was executing is displayed.

Implementing the SQL Access Advisor Recommendations

A SQL Access Advisor recommendation can range from a simple suggestion to a complex solution that requires partitioning a set of existing base tables and implementing a set of database objects such as indexes, materialized views, and materialized view logs. You can select the recommendations for implementation and schedule when the job should be executed.

Tip:

Before implementing the SQL Access Advisor recommendations, review them for cost benefits to determine which ones, if any, should be implemented. For more information, see "Reviewing the SQL Access Advisor Recommendations".

To implement the SQL Access Advisor recommendations: 

On the Results for Tasks page, click Recommendations.

The Recommendations subpage appears.

Under Select Recommendations for Implementation, select the recommendation you want to implement and then click Schedule Implementation.

In the following example, the recommendation with ID value 4 is selected.

Description of sql_access_schedule_imp.gif follows
Description of the illustration sql_access_schedule_imp.gif

The Schedule Implementation page appears.

In the Job Name field, enter a name for the job if you do not want to use the system-generated job name.

Determine whether the implementation job should stop if an error is encountered. Do one of the following:

To stop processing if an error occurs, select Stop on Error.

To continue processing even if an error occurs, deselect Stop on Error.

Under Scheduling Options, in the Schedule Type list, select a schedule type for the task and a maintenance window in which the task should run. Do one of the following:

Click Standard.

This schedule type enables you to select a repeating interval and start time for the task. Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

In the Repeat list, select Do Not Repeat to perform the task only once, or select a unit of time and enter the number of units in the Interval field.

Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

Click Use predefined schedule.

This schedule type enables you to select an existing schedule. Do one of the following:

In the Schedule field, enter the name of the schedule to be used for the task.

To search for a schedule, click the search icon.

The Search and Select: Schedule dialog box appears.

Select the desired schedule and click Select. The selected schedule now appears in the Schedule field.

Click Standard using PL/SQL for repeated interval.

This schedule type enables you to select a repeating interval and an execution window for the task. Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

Under Available to Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

In the Repeat list, select Do Not Repeat to perform the task only once, or select a unit of time and enter the number of units in the Interval field.

In the Repeated Interval field, enter a PL/SQL schedule expression, such as SYSDATE+1.

Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.

Click Use predefined window.

This schedule type enables you to select an existing window. Select Stop on Window Close to stop the job when the window closes. Do one of the following:

In the Window field, enter the name of the window to be used for the task.

To search for a window, click the search icon.

The Search and Select: Window and Window Groups dialog box appears.

Select the desired window and click Select. The selected window now appears in the Schedule field.

Click Event.

Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

Under Event Parameters, enter values in the Queue Name and Condition fields.

Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.

Click Calendar.

Complete the following steps:

Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.

Under Calendar Expression, enter a calendar expression.

Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.

Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.

In this example, Standard is selected for schedule type. The job will not repeat and is scheduled to start immediately.

Description of sql_access_schedule_options.gif follows
Description of the illustration sql_access_schedule_options.gif

Optionally, click Show SQL to view the SQL text for the job.

To submit the job, click Submit.

Do one of the following, depending on whether the job is scheduled to start immediately or later:

If you submitted the job immediately, and if the Results for Task page is shown, then click the link in the Scheduler Job field to display the View Job page. Go to Step 10.

If the job is scheduled to start at a later time, then proceed to Step 9.

Complete the following steps:

On the Server page, under Oracle Scheduler, click Jobs.

The Scheduler Jobs page appears.

Select the implementation job and click View Job Definition.

The View Job page for the selected job appears.

On the View Job page, under Operation Detail, check the status of the operation.

Description of sql_access_operation_det.gif follows
Description of the illustration sql_access_operation_det.gif

Optionally, select the operation and click View.

The Operation Detail page appears.

This page contains information (such as start date and time, run duration, CPU time used, and session ID) that you can use when troubleshooting.

Optionally, from the Database Home page, click Schema.

The Schema subpage appears.On this page you can verify that the access structure recommended by SQL Access Advisor is created. Depending on the type of access structure that is created, you can display the access structure using the Indexes page, Materialized Views page, or the Materialized View Logs page.

Index

A  B  C  D  E  H  I  M  O  P  S  T  U  W 

A

actions

about, 4.1.5

Active Session History

about, 7.1

report

about, 7.1

activity over time, 7.3.7

load profile, 7.3.2

running, 7.2

top events, 7.3.1

Top SQL, 7.3.3

using, 7.3

sampled data, 7.1

statistics, 2.1.4

alerts

clearing, 5.3

default, 5

performance, 5.1

purging, 5.3

responding to, 5.2

Automatic Database Diagnostic Monitor

about, 3.1

accessing results, 6.3

analysis, 3.1.1

configuring, 3.2

enabling, 2.2.1

findings

about, 3.4

viewing, 3.3

for Oracle RAC, 3.1.3

identifying high-load SQL, 9.1

recommendations

actions, 3.4

implementing, 3.5

interpreting, 3.4

rationales, 3.4

types, 3.1.2

report, 3.3

reviewing results, 3.3

running manually

analyzing current database performance, 6.1

analyzing historical database performance, 6.2

Automatic SQL Tuning

modifying task attributes, 10.1.2

viewing recommendations, 10.1.2

viewing results, 10.1.2

Automatic Workload Repository

about, 2.1

baselines, 8.1

compare periods report

about, 8

details, 8.3.2

saving, 8.2.1, 8.2.1, 8.2.3

summary, 8.3.1

supplemental information, 8.3.3

using, 8.3

using another baseline, 8.2.1

using snapshot pairs, 8.2.3

enabling, 2.2.1

snapshots, 2.1

statistics collected, 2.1

using, 3.2.3

B

baselines

about, 8.1

baseline template

about, 8.1.1.1, 8.1.1.2

comparing, 8.2.1, 8.2.2

computing threshold statistics for, 8.1.3

creating

single, 8.1.1.1

deleting, 8.1.2

C

clients

about, 4.1.6

CONTROL_MANAGEMENT_PACK_ACCESS parameter and ADDM, 3.2.1

CPU

I/O wait, 4.3.1

load, 4.3.1

performance problems, 4.3.1

utilization

about, 4.3

monitoring, 4.3.1

customizing the Performance page, 4.5

D

data access paths, optimizing, 11

database

statistics, 2.1

time, 2.1.1, 3.4

database performance

alerts, 5.1

automatic monitoring, 3

comparing, 8

current analysis, 6.1

degradation over time, 8

historical analysis, 6.2

manual monitoring, 6

overview, 2

Database Resource Manager

using, 4.3.1

database tuning

determining causes of spikes in activity, 4.4

performance degradation over time, 8

preparing the database, 2.2.1

proactive tuning, 2.2.2

reactive tuning, 2.2.3

real-time performance problems, 4

SQL tuning, 2.2.4

tools, 1.3

transient performance problems, 7

using the Performance page, 4

DB time

about, 2.1.1

and ADDM finding, 3.4

DBIO_EXPECTED parameter

about, 3.2.2

setting, 3.2.1, 3.2.2

DBMS_ADVISOR package

configuring ADDM, 3.2.2

setting DBIO_EXPECTED, 3.2.2

disk

performance problems, 4.3.3

utilization

about, 4.3

monitoring, 4.3.3

E

execution plan

about, 10

viewing for a SQL statement, 9.2.2.3

H

high-load SQL

about, 9

identifying using ADDM, 9.1

identifying using Top SQL, 9.2

identifying using Top SQL by wait class, 9.2.1

statistics, 2.1.5

tuning, 10.1.1, 10.1.2

viewing details, 9.2.2

viewing execution plans, 9.2.2.3

viewing session activity, 9.2.2.2

viewing SQL text, 9.2.2

viewing statistics, 9.2.2.1

viewing tuning information, 9.2.2.4

host activity, monitoring, 4.3

I

index

about, 11

bitmap, 11

B-tree, 11

creating, 11.1

functional, 11

indexes

creating, 2.2.4

instance activity

monitoring, 4.2

monitoring I/O wait times, 4.2.2

monitoring parallel execution, 4.2.3

monitoring services, 4.2.4

monitoring throughput, 4.2.1

I/O wait times, monitoring, 4.2.2

M

materialized view logs

about, 11

creating, 2.2.4, 11.1

materialized views

creating, 2.2.4, 11.1

memory

performance problems, 4.3.2

swap utilization, 4.3.2

utilization

about, 4.3

monitoring, 4.3.2

metrics, 5.1, 8.1.4

modules, 4.1.4

O

Oracle performance method

about, 2

pretuning tasks, 2.2.1

proactive database tuning tasks, 2.2.2

reactive database tuning tasks, 2.2.3

SQL tuning tasks, 2.2.4

using, 2.2

P

parallel execution, monitoring, 4.2.3

parameters

DBIO_EXPECTED, 3.2.2

initialization, 8.3.3

STATISTICS_LEVEL, 2.2.1

Performance page customization, 4.5

performance problems

common, 2.3

CPU, 4.3.1

diagnosing, 3.1

diagnosing in an emergency, 7.4

disk, 4.3.3

memory, 4.3.2

real time, 7.4

real-time, 4

transient, 7

S

services

about, 4.2.4

monitoring, 4.1.3, 4.2.4

snapshots

about, 2.1

comparing, 8.2.3

creating, 3.2.3.1

default interval, 3.2.3

filtering, 8.2.3

modifying settings, 3.2.3.2

viewing statistics, 3.6

SQL Access Advisor

about, 10, 11

filters, 11.1.3

initial options, 11.1.1

recommendations

about, 11

details, 11.2.2

implementing, 11.3

options, 11.1.4

reviewing, 11.2

SQL, 11.2.3

summary, 11.2.1

running, 11.1

scheduling, 11.1.5

task options, 11.2.4

workload options, 11.2.4

workload source, 11.1.2

SQL Performance Analyzer

about, 2.3

SQL profiles

deleting, 10.3

disabling, 10.3

enabling, 10.3

SQL Tuning Advisor

about, 10

automated maintenance tasks, 10.1.2

implementing recommendations, 10.1.1, 10.1.1

limited scope, 10.1.1

using, 10.1.1, 10.1.2

SQL tuning sets

about, 10.2

creating, 10.2.1

load method, 10.2.1.2

statistics

Active Session History, 2.1.4

baselines, 8.1

databases, 2.1

DB time, 2.1.1, 3.4

default retention, 3.2.3

gathered by the Automatic Workload Repository, 2.1

gathering, 2.1

high-load SQL, 2.1.5, 9.2.2.1, 9.2.2.2

sampled data, 7.1

session, 2.1.3

system, 2.1.3

time model, 2.1.1

wait events, 2.1.2

STATISTICS_LEVEL parameter

and ADDM, 3.2.1

setting, 2.2.1

T

throughput, monitoring, 4.2.1

time model statistics

about, 2.1.1

Top Actions

user activity, 4.1.5

top activity

top SQL, 9.2, 9.2.2

Top Clients

user activity, 4.1.6

Top Files

user activity, 4.1.8

Top Modules

user activity, 4.1.4

Top Objects

user activity, 4.1.9

Top PL/SQL

user activity, 4.1.7

Top Services

user activity, 4.1.3

Top SQL

Active Session History report, 7.3.3

by wait class, 9.2.1

identifying high-load SQL, 9.2

user activity, 4.1.1

Top Working Sessions

user activity, 4.1.2

U

user activity

Top Actions, 4.1.5

Top Clients, 4.1.6

Top Files, 4.1.8

Top Modules, 4.1.4

Top Objects, 4.1.9

Top PL/SQL, 4.1.7

Top Services, 4.1.3

top services, 4.1.3

Top SQL, 4.1.1

Top Working Sessions, 4.1.2

W

wait class

viewing high-load SQL by, 9.2.1

wait events

statistics, 2.1.2

The Top Modules section is displayed.

The View list shows the value Top Modules.

The table has the following columns: Activity (%), Service, Module. The Activity column represents values as a horizontal bar chat with an associated number. The other columns represent values as links.

Row 1 has the following values: 95.19, SYS$USERS, SQL*Plus. Row 2 has the following values: 1.92, SYS$USERS, Realtime Connection. Row 3 has the following values: 1.28, SYS$USERS, null. Row 4 has the following values: 1.28, SYS$USERS, emagent.exe. Row 5 has the following values: .32, SYS$USERS, DBMS_SCHEDULER.

The total sample count is 312.

End of description.

This screenshot shows a section of the Edit Settings page.

One selection possibility is shown. For Snapshot Retention, select one of the following: Use Time-Based Retention (shown selected), or Retain Forever. The first option has a corresponding text field, with the value 30 shown.

End of description.

The Create SQL Tuning Set: Schedule page is shown.

The Create SQL Tuning Set: Schedule section is shown.The database is orcl111. The Finish, Cancel, Back, and Next buttons are shown. The text between the Back and Next buttons is "Step 4 of 5."The text says "A job will be created and scheduled to collect SQL statements and load them into the new SQL tuning set."The Job Parameters section is shown.The Job Name field shows CREATE_STS_1173267160578.The Description field is null.The Schedule section shows two options: Immediately (selected) and Later. The Time Zone list shows Pacific/Pago_Pago. The Date is Mar 7, 2007. The Time is 3:32 a.m.

End of description.

The Performance subpage of the Host page is shown.

On the top left is a View list with Performance Summary selected. On the top right is a View Data list with Real Time: Manual Refresh shown.

Three charts appear on the page. Under CPU Utilization, the chart shows that CPU usage peaked at 100%. Under Memory Utilization, the chart shows that memory usage is constant at approximately 90%. Under Disk I/O Utilization, the chart shows that disk usage has been fluctuating around 10.

Underneath the charts is the Processes section.

There are 69 process.

This section has a Top 10 Processes subsection.

The View By list shows CPU Utilization (%).

The table has the following columns: Process ID Command, CPU Utilization (%), Resident Size (KB), Virtual Size (KB) Owner.

There are 10 rows in the table. Row 1 has the values: 3896, iexplore, 1, 34128, 43936, N/A. Row 2 has the values: 2320, cmd, 0, 88, 1476, N/A. The remaining rows are similar to row 2 in terms of CPU and size.

End of description.

The Active Sessions Working: CPU Used page is shown.

The Active Sessions chart has a y-axis that ranges from 0.0 to 2. The x-axis ranges from 11:02 a.m. to 12:00 a.m.

The activity spikes to 2.0 at 11:40 and then drops off. The activity spikes to 1.8 at 11:52 and hovers around 1.8 for the remaining times shown.

The Detail for Selected 5 Minute Interval section appears underneath the chart. The Top Working SQL section appears below.

The Action list shows the Schedule SQL Tuning Advisor option. Next to it is a Go button.

The table has the following columns: Select, Activity (%), SQL ID, SQL Type.

Row 1 has the following values: (checkbox), 29.93, batd1pgpg49zf, PL/SQL EXECUTE.

Row 2 has the following values: (checkbox), 13.57, 94fzm3jx1c1yp, PL/SQL EXECUTE.

Row 3 has the following values: (checkbox), 12.14, axabnfyfp4r3p, SELECT.

Rows 4-10 have a variety of DML statements.

The Top Working Sessions section appears to the right of the Top Working SQL section.

The View list shows the Top Session value.

The table has the following columns: Activity (%), Session ID, User Name, Program.

Row 1 has the following values: (checkbox), 55.92, 113, HR, sqlplus.

Row 2 has the following values: (checkbox), 21.49, 125, SH, sqlplus.

Rows 3-8 show insignificant session activity.

End of description.

The title is WORKLOAD REPOSITORY COMPARE PERIOD REPORT.

The first table has the following columns: Snapshot Set, DB Name, DB Id, Instance, Inst num, Release, Cluster, Host, Std Block Size.

Row 1 has the following values: First (1st), EMDC, 1621828677, emdc, 1, 11.1.0.4.0, NO, stbcs09-1, 8192.

Row 2 has the following values: Second (2nd), EMDC, 1621828677, emdc, 1, 11.1.0.4.0, NO, stbcs09-1, 8192.

The second table has the following columns: Snapshot Set, Begin Snap Id, Begin Snap Time, End Snap Id, End Snap Time, Avg Active Users, Elapsed Time (min), DB time (min).

Row 1 has the following values: 1st, 50, 21-Mar-07 13:59:33 (Wed), 52, 21-Mar-07 16:00:51 (Wed), 0.04, 121.31, 5.05.

Row 2 has the following values: 2nd, 110, 22-Mar-07 21:00:07 (Thu), 114, 22-Mar-07 23:00:47 (Thu), 0.42, 120.66, 50.44.

Row 3 has the following values: %Diff, null, null, null, null, 950.00, -0.54, 898.07.

The Host Configuration Comparison section follows.

The section contains a table with the following columns: null, 1st, 2nd, Diff, %Diff.

Row 1 has the following values: Number of CPUs, 1, 1, 0, 0.00.

Row 2 has the following values: Physical Memory, 1773M, 1773M, 0M, 0.00.

Row 3 has the following values: Load at End Snapshot, 2.07, .52, -1.55, -74.88.

Row 4 has the following values: %User Time, 3.97, 7.72, 3.75, 94.46.

Row 5 has the following values: %System Time 9.15, 8.87, -.28, -3.06.

Row 6 has the following values: %Idle Time, 86.75, 83.25, -3.5, -4.03.

Row 7 has the following values: %IO Wait Time, 1.55, 2.06, .51, 32.90.

The System Configuration Comparison section follows.

The section contains a table with the following columns: null, 1st, 2nd, Diff, %Diff.

Row 1 has the following values: SGA Target, 0, 0, 0M, 0.00.

Row 2 has the following values: Buffer Cache, 96M, 96M, 0m, 0.00.

Row 3 has the following values: Shared Pool Size, 196M, 196M, 0M, 0.00.

Row 4 has the following values: Large Pool Size, 0M, 0M, 0M, 0.00.

Row 5 has the following values: Java Pool Size, 52M, 52M, 0M, 0.00.

Row 6 has the following values: Streams Pool Size, 0M, 0M, 0M, 0.00.

Row 7 has the following values: Log Buffer, 6120K, 6120K, 0K, 0.00.

End of description.

This screenshot shows the General section of the Automatic Workload Repository page.

In the top right corner of the screenshot are the following buttons: Edit.

Four label-value pairs appear. The value for the Snapshot Retention (days) label is: 30. The value for the Snapshot Interval (minutes) label is: 30. The value for the Collection Level label is: TYPICAL. The value for the Next Snapshot Capture Time interval is: Jan 20, 2009 1:55:08 PM.

End of description.

This graphic shows a horizontal arrow labeled User Response Time, with points at each end. Under the User Response Time arrow and parallel to it are 9 smaller two-pointed arrows. The left to right the arrows are labeled as follows: Browser, WAN, Apps Server, LAN, DB Time, LAN, Apps Server, WAN, and Browser. The DB Time arrow, which is in the middle of the graphic, is blue. The end points of the DB Time arrow map to a gray segment of the User Response Time arrow.

End of description.

On the Compare Periods: First Period End page shown, 8 rows are shown. Row 8 is the only one selected.

Row 8 has the following values: (selected), 8 (shown as a link), Feb 5, 2009 3:20:10 PM, TYPICAL, null.

End of description.

The Create Baseline: Baseline Interval Type section appears.

On the top right the Cancel and Continue buttons appear.

The following mutually exclusive options are shown: Single (selected) and Repeating.

End of description.

The Top Actions section is displayed.

The View list displays Top Actions.

The table contains the following columns: Activity (%), Service, Module, Action. The Activity data is presented as a horizontal bar chart with attached number. The data in the other columns is presented as links.

The table contains 7 rows.

Row 1 has the following values: 88.03, SYS$USERS, SQL*Plus, and SALES_INFO.

Row 2 has the following values: 1.08, SYS$USERS, null, and null.

Row 3 has the following values: .72, SYS$BACKGROUND, null, and null.

Row 4 has the following values: .72, emdc, OEM.SystemPool, and null.

Row 5 has the following values: .36, emdc, OEM.CacheModeWaitPool, and null.

Row 6 has the following values: .36, SYS$USERS, EM PING, null.

Row 7 has the following values: .36, SYS$USERS, emagent, and null.

End of description.

The screenshot shows the Details subpage of the Results for Tasks page. The other subtabs displayed are Summary, Recommendations, and SQL Statements. The Details subpage has two tables: "Workload and Task Options", and "Journal Entries".

The Workload and Task Options table has descriptive text below the title which reads "These are the options that were selected when the advisor task was created." Below this text, on the right-hand side of the page is a link for the previous page (disabled), and action list for selecting which options are displayed (the value "1-10 of 29" is selected), and a link for Next 10. The table has 3 columns, which are named, from left to right: Option, Value, and Description. There are 10 rows displayed in the table. They contain the following values, in column order from left to right and row order from top to bottom:

Advisor Mode, Comprehensive, Specifies the mode in which SQL Access Advisor will operate during an analysis, either limited (quicker results) or comprehensive (higher quality recommendations)

Analysis Scope, All Tuning Artifacts, The type of recommendations that are allowed.

Creation Cost, Consider creation cost, When specified, the SQL Access Advisor will weigh the cost of creation of access structures against the frequency of the queries and potential improvement in query execution time

Default Index Schema, None, Specifies the default owner for new index recommendations

Default Index Tablespace, None, Specifies the default tablespace for new index recommendations

Default MVLog Tablespace, None, Specifies the default tablespace for new materialized view log recommendations

Default MView Schema, None, Specifies the default owner for new materialized view recommendations

Default MView Tablespace, None, Specifies the default tablespace for new materialized view recommendations

Default Partitioning Tablespaces, None, Specifies the default tablespaces for new Partitioning recommendations

Excluded Actions, None, Contains a list of application actions that are NOT eligible for tuning

Below this table, on the right-hand side of the page is a link for the previous page (disabled), and action list for selecting which options are displayed (the value "1-10 of 29" is selected), and a link for Next 10.

The Journal Entries table has descriptive text below the title which reads "These are the messages that were logged to the advisor journal while the task was executing. The amount of information logged is controlled by the 'Journalling Level' option shown in the table above." Below this text, on the right-hand side of the page is a link for the previous page (disabled), and action list for selecting which options are displayed (the value "1-10 of 18" is selected), and a link for the Next 8. The table has 3 columns, which are named, from left to right: Severity, Entry, and Order. Only 7 of the first 10 rows are shown in the screenshot. The rows in the table are sorted by the descending value of the Order column. These rows contain the following values, in column order from left to right and row order from top to bottom:

Information icon, Preparing workload for analysis, 1

Information icon, Filter Summary: Valid username: Unused, 2

Information icon, Filter Summary: Invalid username: Unused, 3

Information icon, Filter Summary: Valid module: Unused, 4

Information icon, Filter Summary: Invalid module: Unused, 5

Information icon, Filter Summary: Valid action: Unused, 6

Information icon, Filter Summary: Invalid action: Unused, 7

End of description.

This screenshot shows the top portion of the Finding History: Top SQL by DB Time page.

In the top left corner of the screenshot are the following elements: View list, calendar icon, Go button, and Filters button. The value shown in the View list is Jan 19, 2009.

A line chart is shown. The units of the x-axis are hours. The x-coordinates shown range from 7:00 p.m. on January 19 to 7 p.m. on January 20. The units of the y-axis are: active sessions. The y-coordinates shown range from 0.0 to 2.0. The following types of information are plotted in the chart: Other Load on System, and Impact of Finding. The chart spiked between 2 p.m. and 5 p.m. on January 20. The time from 1 p.m. to 4 p.m. is included in a rectangle.

Underneath the chart is a slider.

This screenshot includes a Detail for Selected 3 Hour Interval section.

Underneath the section title are the following left-justified links: Show All Details, and Hide All Details.

Underneath the row of elements is a table. The table contains the following columns: Details, Finding Details, Impact (Active Sessions), and Start Date. Values in the Details column are displayed as a navigation tree. Values in the Finding Details column are displayed as links.

5 rows are shown in the table. The values of row 1 are: Show, ADDM:399937146_1_99, 0.5, and Jan 20, 2009 2:30:56 PM PST. Each subsequent row shows the same Finding Details value, except with the last two digits increased by one (100, 101, etc.). The Impact values ranged from .21 to 1.87.

End of description.

The screenshot shows the Operation Detail table on the View Job page. At the top of the table is a single button labeled View. The table has 5 columns, and only 1 row is shown. The columns and values are, listed from left to right:

Select: selected

Log ID: 1153 (hyperlinked)

Log Date: Mar 28, 2007 6:48:43 PM –07:00

Operation: RUN

Status: SUCCEEDED

End of description.

Overall Task Statistics section is shown.The following name-value pairs appear: Executions is 2, Candidate SQL is 55, Distinct SQL Examined is 46.SQL Examined Status shows a pie chart. 70% is SQL Examined Without Findings (32). 30% is SQL Examined with Findings. 0% is SQL Skipped Due to Errors.The Breakdown by Finding Type chart is shown. There are four bars, part blue (Not Implemented) and part green (Implemented). The y-axis is Number of SQL and ranges from 0 to 10. SQL Profile is 7 blue and up to 8 green. Index is 1 blue. Statistics is 8 blue. Restructure SQL is 2 blue.The Profile Effect Statistics section is shown. The title of the chart is Tuned SQL DB Time Benefit (seconds per week). Implemented (sec) is 7318. Potential (sec) is 4.The y-axis is DB Time (s) and ranges from 0 to 9000. The x-axis is Implemented Recommended. The legend is navy blue as Before and light blue as After. Before is at 7500 whereas After is just above 0.

End of description.

The Create SQL Tuning Set: Load Methods page is shown.

The database is orcl1111. The Finish, Cancel, Back, and Next buttons are shown. Between the Back and Next button the text "Step 2 of 5" is shown.

The text says "Pick one of the load methods to collect and load SQL statements into the SQL tuning set." Two options are possible. The option selected is "Incrementally capture active SQL statements over a period of time from the cursor cache." The text says "Specify the duration within which the SQL statements will be collected, and specify frequency over which the active SQL statements from the cursor cache will be collected repeatedly." The Duration box shows 24 hours. The Frequency box shows 5 minutes. The deselected option is "Load SQL statements one time only." The Data Source list shows Cursor Cache selected.

End of description.

The Top Background Events section is shown.

The table has the following columns: Event, Event Class, % Activity, Avg Active Sessions.

Row 1 has the following values: CPU + Wait for CPU, CPU, 22.81, and 0.82.

Row 2 has the following values: log file parallel write, System I/O, 8.22, 0.30.

Row 3 has the following values: control file parallel write, System I/O, 3.05, 0.11.

Row 4 has the following values: control file sequential read, System I/O, 1.11, 0.04.

Row 5 has the following values: db file sequential read, User I/O, 1.11, 0.04.

The Schedule SQL Tuning Advisor section is shown.The Cancel and OK button appear on the upper right.The Name field has SQL_TUNING_1173098703162.The Description field is empty.The SQL Tuning Set field is empty. A Go button is to the right of the field.The SQL Statements Count is 0.The Scope section permits you to select Limited or Comprehensive. Comprehensive is selected. The Time Limit per Statement is 5. The Total Time Limit is 30.The Schedule section has the Time Zone set to Pacific/Pago_Pago.The available options are IMmediately (selected) or Later.

End of description.

The Top Disk Devices (ordered by % Busy) section on the Disk Details page is shown.

The table contains the following columns: Status, Device, Busy (%), Average Outstanding Requests, Reads per second, Writes per second, Blocks Read (512 bytes per second), Blocks Written (512 bytes per second), Average Wait Time (ms), Average Service Time (ms).

Row 1 contains the following values: (check mark icon), 0 C:, 2.82, .06, 3.04, 5.14, 120.85, 8453, 0, 7.3.

Row 2 contains the following values: (check mark icon), 1 E:, 0, 0, 0, 0, 0, 0, 0, 0, 0.

End of description.

This graphic shows the SQL Profiles and SQL Patches section.

The text reads: A SQL Profile contains additional information (auxiliary statistics) that aids the optimizer to select the optimal execution plan of a particular SQL statement. A SQL Patch is automatically generated to workaround an error or performance problem for a single SQL statement.

The table has the following columns: Select, Name, Type, Category, Status, Created.

The next section is titled SQL Plan Baseline.

The text says: A SQL Plan Baseline is an execution plan deemed to have acceptable performance for a given SQL statement.

The table has the following columns: Select, Name, Fix, Accept, Auto Purge, Enabled, Created.

Row 1 values are STMT01, NO, YES, NO, YES, Apr 21, 2009, 12:57:53 PM.

The Top Objects section is shown.

The View list shows Top Objects.

The table contains the following columns: Activity (%), Object Name, and Object Type. Values in the Activity column are shown as bar charts with an associated number. Values in the Object Name column are shown as links.

The table contains 1 row. Row 1 has the following values: 100, SYS.I_SYSAUTH1, and INDEX.

The total sample count is 1.

End of description.

The Create SQL Tuning Set: Load Methods page is shown.

In the Data Source field, AWR Baseline is selected. In the AWR Baseline field, BASELINE_PEAK_LOAD is entered.

End of description.

This screenshot contains a table. The table contains the following columns: Impact (%), Finding, Occurrences (last 24 hours). The values in the Finding and Occurrences columns are links. Values in the Impact column are shown as a horizontal bar and labeled with a number.

In the ADDM Performance Analysis section, 4 ADDM Findings are listed.

The first ADDM Finding shows that SQL statements are consuming significant database time. The impact of this finding is 83.7%.

The second ADDM Finding shows that the "Scheduler" Wait Class was consuming significant database time. The impact of this finding is 23.4%.

The third ADDM Finding shows that Log File Switches were consuming significant database time. The impact of this finding is 3.5%.

The fourth ADDM Finding shows that an Undersize Buffer Cache was consuming significant database time. The impact of this finding was 2.1%.

End of description.

The SQL Plan Control page is shown.

The Cancel and OK buttons are on the top right.

The Load SQL Plan Baselines section is shown. The possible options are "Load plans from SQL Tuning Set (STS)" and "Load plans from cursor cache."

The Job Parameters section is shown. The Job Name is SPM_LOAD_20070627_105654. The Description field is null.

The Schedule section is shown. The possible options are Immediately (selected) or Later. Next to later the Time Zone list shows Universal Time. The Date field shows June 27, 2007. The Time lists show 10:56:00 a.m.

End of description.

The Parallel Execution tab is shown.

4 line charts are shown.

In the first chart, the y-axis units are sessions. The y-axis values range from 0 to 40. The x-axis units are hours of day, shown in 10-minute intervals. The values range from 11:00 a.m. to 12:20 p.m. The following types of information are tracked in the chart: Active Serial Sessions and Active Parallel Sessions. The chart shows that serial sections remain at around 45. Parallel session activity at level 10 begins at 11:30 a.m.

In the second chart, the y-axis units are sessions. The y-axis values range from 0 to 20. The x-axis units are hours of day, shown in 10-minute intervals. The values range from 11:00 a.m. to 12:20 p.m. The following types of information are tracked in the chart: Parallel QC and Parallel Slaves. The chart shows minor parallel slaves activity at level 5 beginning at 11:30 a.m. There is minor Parallel QA activity during the same time period.

In the third chart, the y-axis units are per second. The y-axis values range from 0 to 3. The x-axis units are hours of day, shown in 10-minute intervals. The values range from 11:20 a.m. to 12:20 p.m. The following types of information are tracked in the chart: DDL Statements Parallelize, DML Statements Parallelize, and Queries Parallelized. The parallel queries activity spikes to 3 per second at 11:30 a.m. and remains fairly constant.

In the fourth chart, the y-axis units are per second. The y-axis values range from 0 to 3. The x-axis units are hours of day, shown in 10-minute intervals. The values range from 11:20 a.m. to 12:20 p.m. The following types of information are tracked in the chart: Serialized, Downgraded 100%, Downgraded 75%, Downgraded 50%, Downgraded 25%, and Not Downgraded. The chart a large block of Downgraded 25% activity at 3 per second from 11:30 a.m. to 11:40 a.m. The Downgraded 100% activity spikes to 3 per second at 11:40 a.m. and remains fairly constant at this rate.

End of description.

Four tabs are shown: Statistics (selected), Activity, Plan, Plan Control, Tuning History, and SQL Monitoring (grayed out).

In the Summary section, the Active Sessions chart is shown. The y-axis values range from 0 to 1. The x-axis values range from 11:04 to 12:05. Session activity spikes to .5 at 11:35 and then hovers around .25.

The General section has the following name-value pairs: Module is SQL*Plus, Action is null, Parsing Schema is HR, PL/SQL Source is Not Applicable, SQL Profile is n/a, and SQL Plan Baseline is n/a. The Activity By Waits pie chart has a legend of Remaining Waits (61.2%), Concurrency Waits (17.7%), and CPU (21%).

The Activity By Time section has the following name-value pairs: Elapsed Time (sec) is 303.73, CPU Time (sec) is 63.86, and Wait Time (sec) is 239.87.The Elapsed Time Breakdown section has the following name-value pairs: SQLTime (sec) is 303.73, PL/SQL Time (sec) is 0, Java Time (sec) is 0.The Shared Cursors Statistics section has the following name-value pairs: Total Parses is 1, Hard Parses is 0, Child Cursors is 1, Child Cursors with Loaded Plans is 1, Invalidations is 0, Largest Cursor Size (KB) is 16.39, and All Cursor Size (KB) is 16.39.The Execution Statistics table has the following columns: null, Total, Per Execution, and Per Row. Row 1 has the following values: Executions, 1, 1, n/a. Row 2 has the following values: CPU Time (sec), 2362.10, 2362.10, and n/a. Row 3 has the following values: Buffer Gets, 214871708, 214871708, and n/a. Row 4 has the following values: Disk Reads, 7240, 7240, and n/a. Row 5 has the following values: Direct Writers, 0, 0, and n/a. Row 6 has the following values: Rows, 0, 0, and n/a. Row 7 has the following values: Fetches, 0, 0, and n/a.The Other Statistics section has the following name-value pairs: Executions that Fetched All Rows (%) is 0.

End of description.

The table contains the following columns: Slot Time (Duration), Slot Count, Event, Event Count, % Event.

Row 1 has the values: 14:10:50 (1.2 min), 5, control file sequential read, 4, 0.11.

Row 2 has the values: null, null, CPU + Wait for CPU, 1, 0.03.

Row 3 has the values: 14:12:00 (3.0 min), 9, CPU + Wait for CPU, 5, 0.14.

Row 4 has the values: null, null, control file parallel write, 2, 0.05.

Row 5 has the values: null, null, null event, 1, 0.03.

Row 6 has the values: 14:15:00 (3.0 min), 8, control file parallel write, 4, 0.11.

Row 7 has the values: null, null, control file sequential read, 4, 0.11.

Row 8 has the values: 14:18:00 (3.0 min), 10, control file sequential read, 6, 0.16.

Row 9 has the values: null, null, control file parallel write, 3, 0.08.

Row 10 has the values: null, null, SQL*Net break/reset to client, 1, 0.03.

Row 11 has the values: 14:21:00 (3.0 min), 14, CPU + Wait for CPU, 5, 0.14.

Row 12 has the values: null, null, control file parallel write, 5, 0.14.

Row 13 has the values: null, null, control file sequential read, 3, 0.08.

End of description.

The ASH Report for EMDC/emdc appears.The top of the report is a table with the following columns: DB Name, DB Id, Instance, Inst num, Release, RAC, and Host. Row 1 shows the following values: EMDC, 399937146, emtst, 1, 11.2.0.0.2, NO, and dadvmn0621.The next table has the following columns: CPUs, SGA Size, Buffer Cache, Shared Pool, and ASH Buffer Size. Row 1 shows the following values: 1, 355M(100%), 96M(27.0%), 196M(55.2%), and 2.0M(0.6%).The next table has the following columns: null, Sample Time, and Data Source.The table contains 7 rows. Row 1 contains the following values: Analysis Begin Time, 29-Jan-09 14:30:20, V$ACTIVE_SESSION_HISTORY.Row 2 contains the following values: Analysis End Time, 29-Jan-09 14:35:20, and V$ACTIVE_SESSION_HISTORY.Row 3 contains the following values: Elapsed Time, 15.0(mins), and null.Row 4 contains the following values: Sample Count, 1083, and null.Row 5 contains the following values: Average Active Sessions, 3.61, and null.Row 6 contains the following values: Avg. Active Session per CPU, 3.61, and null.Row 7 contains the following values: Report Target, None specified, and null.Below the table is the heading "ASH Report." The following links appear below the heading: Top Events, Load Profile, Top SQL, Top PL/SQL, Top Java, Top Sessions, Top Objects/Files/Latches, Activity Over Time.

End of description.

The First Period section is shown.

The section contains 6 name-value pairs. The Baseline ID label has the value 2. The Name label has the value BASELINE_THU_1530. The Beginning Snapshot ID has the value 9. The Ending Snapshot ID label has the value 15. The Capture Time label has the value Feb 5 2009 3:30:14 PM. The Capture Time label has the value Feb 5 2009 4:30:50 PM.

End of description.

The Top User Events subsection is shown.

The table has the following columns: Event, Event Class, % Event, and Avg Active Sessions.

Row 1 has the values: CPU + Wait for CPU, CPU, 30.56, and 1.10.

Row 2 has the values: db file sequential read, User I/O, 23.64, 0.85.

Row 3 has the values: flashback buf free by RVWR, Configuration, 3.32, 0.12.

Row 4 has the values: latch: shared pool, Concurrency, 1.02, 0.04.

End of description.

The Edit Thresholds: Number of Transactions (per second) page is shown.The Cancel, Clear Thresholds, and Apply Thresholds buttons are shown.Last Updated is February 5, 2009 5:29:34 PM PST.The AWR Baseline section includes the Name list. BASELINE_THU_1530 is selected.The Number of Transactions (per second) vs. Baseline section includes a chart. A thumbnail graph is shown underneath Thu.The y-axis of the chart goes from 0 to 750. The x-axis goes from 12 AM to 11 PM. The activity spikes at 3 p.m. to 750.The Threshold Setting sections includes: the Threshold Type list, with Significance Level selected; the Critical list with None selected; the Warning list with None selected; the Occurrences text box with 2 shown. The Preview button is shown.

End of description

The Plan subpage is shown.The following name-value pairs are shown Data Source is Cursor Cache, Capture Time is Feb 9 2009 12:07:07 PM, Parsing Schema HR, Optimizer Mode ALL_ROWS.In the View section, Graph is selected.The Section Details section shows the following name-value pairs:Operation is TABLE ACCESS BY INDEX ROWIDObject is EMPLOYEESObject Type is TABLEOrder is 2Rows is 3Size (KB) is 0.059Cost is 2Time is aCPU Cost is 15893I/O Cost is 2The graphic depicts a flow chart with EMP_NAME_IX leading to EMPLOYEES, leading to HASH JOIN. The JOBS object also leads to HASH JOIN. HASH JOIN then leads to HASH JOIN. DEPARTMENTS also leads to this HASH JOIN. HASH JOIN finally leads to SELECT STATEMENT.

End of description.

This screenshot shows the top portion of the Performance Finding Details: Top SQL Statements page.

In the top right corner of the screenshot are the following buttons: Finding History.

6 label-value pairs appear. The value for the Finding label is: SQL statements consuming significant database time were found. The value for the Impact (Active Sessions) label is: .22. The value for the Impact (%) label is: 83.7. The value is displayed as a horizontal bar. The value for the Period Start Time label is: Jan 20, 2009 4:00:40 PM PST. The value for the Period Duration (minutes) label is: 29.5. The value for the Filtered label is: No. The Filters button appears next to this value.

This screenshot shows the Recommendations section of the page.

Underneath the section title is the following left-justified button: Schedule SQL Tuning Advisor.

The following left-justified links are shown in a row: Select All, Select None, Show All Details, Hide All Details.

Underneath the row of links is a table. The table contains the following columns: Select, Details, Category, and Benefit (%). The value of the Details column is a collapsible navigation tree. The value of the Benefit column is a horizontal bar chart with an associated number.

3 rows are shown in the table. The values of row 1 are: null, Show, SQL Tuning, and 39.5. The values of row 2 are: null (a checked box is shown), Show, SQL Tuning, and 25.6. The values of row 3 are: null (a checked box is shown), Show, SQL Tuning, and 18.6.

The Recommendations section contains a Findings Path subsection. Underneath the section title are the links Expand All and Collapse All. Underneath the row of links is a table. The table contains the following columns: Findings, Impact (%), and Additional Information.

1 row is shown in the table. The values of row 1 are: SQL statements consuming significant database time were found, 100, and null.

End of description.

The Top Files section is shown.

The View list shows Top Files selected.

The table has the following columns: Activity (%), Name, Tablespace, Average Wait Time (ms).

Row 1 has the following values: 100.00, t_db1.f, SYSTEM, and 322.

End of description.

The Activity tab is selected.The Summary section contains a chart.The y-axis is labeled Active Sessions and ranges from 0 to 1.0. The x-axis ranges from 11:06 a.m. to 12 p.m. The legend shows latch free, asynch descriptor resize, buffer busy waits, latch: cache buffers chains, latch: row cache objects, CPU Used. The CPU Used activity spikes to .5 at 11:35 and then frequently drops and spikes to .3 thereafter.The Detail for Selected 30 Minute Interval section contains the Run AWR SQL Report button and Run ASH Report button. The Start Time is Feb 9, 2009 11:37:19 a.m.The table has the following columns: Activity (%), SID, User, Program, Service, Plan Hash Value. Row 1 has the following values: 100.00, 113 (shown as a link), HR (shown as a link), sqlplus, SYS$USERS (shown as a link), and 2004336139.

End of description.

The Automatic SQL Tuning Result Details section is shown.The Begin Date is Mar 20, 2007 10:00:38 PM. The End Date is Mar 22, 2007 11:38 AM.The Recommendations section is shown. The View Recommendations button is underneath the title.The table contains the following columns: Select, SQL Text, Parsing Schema, SQL ID, Statistics, SQL Profile, Index, Restructure SQL, Miscellaneous, Error, Date.Row 1 has the following values: (select), SELECT ..., SH, 5mxdwvuf9i3vp, null, 99.6%, (62%), null, null, null, 3/20/07.Row2 has the following values: (deselected), SELECT ..., SYSMAN, 0ms7aqju3b8s9, null, 98.4%, null, null, null, null, 3/20/07.Row 3 has the following values: (deselected), SELECT ..., SYSMAN, gim43un5cy843, (check), 84%, null, null, null, null, 3/20/07.Row 4 has the following values: (deselected), SELECT ..., SYSMAN, 4irfrtx4u6zcx, (check), 71.7%, null, null, null, null, 3/20/07.Row 5 has the following values: (deselected), SELECT ..., SYS, 3xua7cgdy0qhr, null, 50%, null, (check), null, null, 3/20/07.Row 6 has the following values: (deselected), SELECT ..., BMSNMP, 5qmzpdt3hbxur, (check), 44.7%, null, null, (check), null, 3/20/07.Row 7 has the following values: (deselected), SELECT ..., SYS, f72wu153yw45k, (check), 42.1%, null, null, null, null, 3/20/07.Row 8 has the following values: (deselected), select ..., SYS, bsa0wi5ftq3uw, null, 33.3%, null, null, null, null, 3/20/07.

End of description.

In the Select Beginning Snapshot section shown, 2 rows of a table are shown.

Row 1 has the following values: (selected), 1 (shown as a link), Feb 5, 2009 2:12:19 PM, TYPICAL.

Row 2 has the following values: (deselected), 2 (shown as a link), Feb 5, 2009 2:20:25 PM, TYPICAL.

End of description.

The I/O subpage is shown.

The top section is Latency for Synchronous Single Block Reads. On the right side of the section is the I/O Calibration button.

A line chart is displayed. The y-axis units are milliseconds. The values range from 0 to 10. The x-axis units are hours of the day, shown in 10-minute intervals. The x-axis values range from 9:40 a.m. to 10:50 a.m. The chart tracks Latency information. The chart does not show any activity.

The I/O breakdown options are: I/O Function (shown selected), I/O Type, and Consumer Group.

A line chart labeled I/O Megabytes per Second by I/O Function is shown. The y-axis units are I/O per second. The y-axis values range from 0 to 50. The following types of information are tracked in the chart: Others, XDB, Streams AQ, Data Pump, Recovery, RMAN, ARCH, LGWR, DBWR, Direct Writes, Direct Reads, Buffer Cache Reads. The chart shows a slight spike in LGWR activity at 10:45 a.m.

A line chart labeled I/O Requests per Second by I/O Function is shown. The y-axis units are MB per second. The y-axis values range from 0 to 600. The following types of information are tracked in the chart: Others, XDB, Streams AQ, Data Pump, Recovery, RMAN, ARCH, LGWR, DBWR, Direct Writes, Direct Reads, Buffer Cache Reads. The chart shows a huge spike in LGWR activity at 10:45 a.m.

End of description.

This screenshot shows the Recommendations section of the Performance Finding Details page.

Underneath the section title is the following left-justified button: Schedule SQL Tuning Advisor.

The following left-justified links are shown in a row: Select All, Select None, Show All Details, Hide All Details.

Underneath the row of links is a table. The table contains the following columns: Select, Details, Category, and Benefit (%). Values in the Details column are displayed as a collapsible navigation tree. Values in the Benefit column are displayed as a horizontal bar chart with an associated number.

3 rows are shown in the table. The values of row 1 are: null, Hide, SQL Tuning, and 39.5. Row 2 shows the Action to be performed for the recommendation in row 1. The action is: "Investigate the SELECT statement with SQL_ID '31h2wmu3q47u6' for possible performance improvements. You can supplement the information given here with an ASH report for this SQL_ID." Five rationales are shown under Action.

The values of row 3 are: null (a checked box is shown), Show, SQL Tuning, and 25.6. The values of row 4 are: null (a checked box is shown), Show, SQL Tuning, and 18.6.

End of description.

The column are Name, Help, Revisions, Value, Comments, Type, Basic, Modified, Dynamic, Category.

The row shows statistics_level, [Help], String, [Dynamic], Diagnostics and Statistics.

This screenshot shows the top portion of the Active Sessions Working: CPU Used page.

In the top right corner of the screenshot is the following list: View Data. The value shown in Real Time: 15 Second Refresh.

A line chart labeled Active Sessions is shown. The units of the x-axis are times of day in 5-minute intervals. The x-coordinates shown range from 4:12 to 5:10. The units of the y-axis are: number of active sessions. The y-coordinates shown range from 0.0 to 1.2. The following types of information are plotted in the chart: CPU Used. The chart indicates that CPU usage spiked to 1.0 from 5:03 to 5:09.

Underneath the chart is a slider. The selected time interval is 5:05 to 5:10.

Underneath the line chart is the Detailed for Selected 5 Minute Interval Section.

The Start Time is April 1, 2007 5:03:32 PM PDT.

The Detail section contains the following subsections: Top Working SQL, and Top Working Sessions.

The Top Working SQL section contains the Actions list, which shows Schedule SQL Tuning Advisor. Next to it is the Go button.

In a horizontal row across the top of the screenshots are the following links: Select All, and Select None.

The Top Working Sessions section contains the View list, which shows Top Sessions.

Underneath the list is a table. The table contains the following columns: Activity (%), Session ID, User Name, and Program. Values in the Activity column are displayed as a horizontal bar chart with an associated number. Values in the Session ID and User Name columns are links.

3 rows are shown in the table. The values of row 1 are: 97.37, 121, SH, and sqlplus@stbcs09-1. The values of row 2 are: .33, 140, DBSNMP, OMS. the values of row 3 are: .33, 142, SYSMAN, OMS.

End of description.

The screenshot shows the Load Profile section of the AWR report. Refer to the complete text description of the report.

End of description.

The Select Beginning Snapshot section is shown.

The Go to Time field on the Snapshots page shown shows 2/5/09. The list shows 2:00PM. A Go button is to the right of the list.

End of description.

This screenshot shows the Recommendations section of the Performance Finding Details page.

The following left-justified links are shown in a row: Show All Details, Hide All Details.

Underneath the row of links is a table. The table contains the following columns: Details, Category, and Benefit (%). Values in the Details column are displayed as a collapsible navigation tree. Values in the Benefit column are displayed as a horizontal bar chart with an associated number.

2 rows are shown in the table. The values of row 1 are: Hide, DB Configuration, and 6.3. Row 2 shows additional information about row 1. The values of row 2 are: Action, "Increase the buffer cache size by setting the value of parameter DB_CACHE_SIZE to 160M," and null. Next to the text in the Category column are the following buttons: Implement, and Filters.

In the Additional Information section, the following message is displayed: The value of parameter "db_cache_size" was "96 M" during the analysis period.

In the Findings Path section, the Expand All and Collapse All links appear in the upper left. The table has the following columns: Findings, Impact (%), and Additional Information. The first row has the values: "The buffer cache was undersized causing significant additional read I/O," 2.1 (shown as a bar chart), and an Additional Information link. The second row has the values: "Wait class 'User I/O' was consuming significant database time," 2.1 (shown as a bar chart), and null.

End of description.

This screenshot shows the SQL Statements section of the Summary subpage of the Results for Tasks page, but with the Show Statement Counts link in expanded form. Once expanded the link name changes to "Hide Statement Counts" and displays the following additional information:

Insert 0

Select 19

Update 0

Delete 0

Merge 0

Skipped (Parsing or Privilege Errors) 0

End of description.

The Run ADDM page is shown.

The Cancel and OK buttons are right-justified at the top of the page.

You can make the following selections: Run ADDM to analyze current performance, or Run ADDM to analyze past performance. If you make the latter suggestions, then you can set Period Start Time and Period End Time. In the screenshot, the Period Start Time is Jan 29 2009 10:00:08 AM PST. The Period End time is one hour later.

A line chart is shown. The y-axis units are average active sessions and range from 0 to 7.54. The x-axis units are times of day, shown in 15-minute intervals, and range from 9:00 a.m. to 11:30 a.m. Wait times (shown in red) peaked significantly between 10:00 and 11:00.

End of description.

The Compare Periods: Review page is shown.

Under First Period, snapshots 2 and 8 are displayed as the beginning and ending snapshot IDs. The Beginning Snapshot Capture Time is Mar 21, 2007 5:00:09 PM. The Ending Snapshot Capture Time is Mar 21, 2007 6:00:26 PM.

Under Second Period, snapshots 20 and 26 are displayed. The Beginning Snapshot Capture Time is Feb 5, 2009 5:20:10 PM. The Ending Snapshot Capture Time is Feb 5, 2009 6:20:45 PM.

End of description.

The Top PL/SQL section is shown.

The View list shows the value Top PL/SQL.

The table contains two columns: Activity (%) and PL/SQL Subprogram. The Activity column is shown as a horizontal bar chart with an associated number.

The table contains 1 row. The values of row are: 100, SYSMAN.MGMT_JOB_EXEC_UPDATE.

The total sample count is 1.

End of description.

This screenshot shows the Database Activity section of the Automatic Database Diagnostic Monitor (ADDM) page.

In the top right corner of the screenshot are the following buttons: Run ADDM, Finding History.

A line chart is shown. The units of the x-axis are hours. The x-coordinates shown range from 1 p.m. on Jan 20 2009 to 11 a.m. on Jan 21 2009. The units of the y-axis are: active sessions. The y-coordinates shown range from 0.0 to 1.12. The following types of information are plotted in the chart: Wait, User I/O, CPU. The chart indicates that all activity spiked between 2:30 p.m. and 5:30 p.m. on January 20 and from 9 a.m. to 11 a.m. on January 21.

This screenshot also shows the ADDM Performance Analysis section of the Automatic Database Diagnostic Monitor (ADDM) page.

In the top right of the section are the following buttons: Filters, View Snapshots, View Report.

6 label-value pairs appear. The value for the Task Name label is: ADDM:399937146_1_141. The value for the Time Range label is: null. The value for the Task Owner label is: SYS. The value for the Average Active Sessions label is: 0.3. The value for the Period Start Time label is: Jan 21, 2009 10:30:28 AM PST. The value for the Period Duration (minutes) label is: 30.4.

Underneath this section is a table. The table contains the following columns: (%), Finding, Occurrences (last 24 hours). The values in the Finding and Occurrences columns are links. The % value is shown as a horizontal bar.

4 rows are shown in the table. The values of row 1 are: 100, Top SQL by DB Time, 11 of 19. The values of row 2 are: 77.5, CPU Usage, 12 of 19. The values of row 3 are: 9.5, Hard Parse, 8 of 19. The values of row 4 are: 4.9, "Scheduler" Wait Class, 12 of 19. The values of row 5 are: 1.4, I/O Throughput, 11 of 19.

A collapsed navigation tree for Information Findings is shown.

End of description.

This graphic shows a table with the following columns: Severity, Category, Name, Impact, Message, Alert Triggered.

Row 1 has the following values: X, Incident, Generic Incident, [null], Incident detected, Jan 28, 2009 4:22:03 PM.

The Top Clients section is shown.

The View list shows the value Top Clients.

The table contains two columns: Activity (%) and Client ID. Values in the Activity column are presented as horizontal bar charts with an associated number.

The table contains 1 row. The values of row 1 are: 100, SYS@130.35.182.5@Mozilla/5.0 (Windows; U; Windows NT 5.1; en-us).

The total sample count is 302.

End of description.

The AWR Baselines page appears.

On the top right of the page are the Refresh and Create buttons.

The Search field is empty. The Go button appears next to the field.

Underneath are the following elements: Edit button, View button, Delete button, Actions list (set to Schedule Statistics Computation), and Go button.

The table has the following columns: Select, Name, Type, Valid, Statistics Computed, Last Time Computed, Start Time, End Time. The Select column values appear as selectable options. The Name values are links.

Row 1 has the following values: (option selected), SYSTEM MOVING WINDOW, MOVING_WINDOW (8 Days), Yes, Pending, Feb 8 2009 2:12:19 PM, Feb 5 2009 2:30:32 PM, Feb 5 2009 2:30:32 PM, 0.

End of description

The SQL Access Advisor: Schedule page is shown.The following name-value pairs are shown: Database is database, Logged In As is DBA1.To the right of the name-value pairs are the Cancel, Back, and Next button.The Advisor Task Information section is shown.The Task Name field is SQLACCESS9084523.The Task Description field is SQL Access Advisor.The Journaling Level list shows Basic selected.The Task Expiration (days) field shows 30.The Total Time Limit (minutes) field shows 10.The Scheduling Options section is shown.The Schedule Type list shows Standard.The Time Zone field shows PST8PDT.The Repeating section includes the Repeat list. Do Not Repeat is selected.The Start section shows Immediately (selected) and Later. The Date field shows Mar 26, 2007. The Time is 6:50:00 p.m.

End of description.

The screenshot shows the Show SQL page for a selected recommendation. The heading of the page is Show SQL. On the top, on the right-hand side of the page, is a button labeled Done.The SQL statement displayed is:

Rem SQL Access Advisor: Version 11.1.0.4.0 – Production
Rem
Rem Username:  SYS
Rem Task: SQLACCESS6607720
Rem Execution date:
Rem
CREATE MATERIALIZED VIEW LOG ON
"SH"."CUSTOMERS"
WITH ROWID, SEQUENCE("CUST_ID", "CUST_LAST_NAME","CUST_STATE
_PROVINCE")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"SH"."SALES"
WITH ROWID, SEQUENCE("PROD_ID", "CUST_ID", "QUANTITY_SOLD",
 "AMOUNT_SOLD")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW "SYS"."MV$$_00650002"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.SALES.CUST_ID C1, SUM("SH"."SALES"."AMOUNT_SOLD") M1,
COUNT("SH"."SALES"."AMOUNT_SOLD")
M@, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID
GROUP BY SH.SALES.CUST_ID;
begin
dbms_stats.gather_table_stats('"SYS"'.'"MV$$_00650002"',NULL, dbms_stats.auto_sample_size);
end;
/

End of description.

In the top right corner of the page are the following buttons: Create.

In a horizontal row across the top of the screenshots are the following GUI elements: Delete button, Actions list (with Create SQL Tuning Set shown), Go button, page scrolling list (with 76-96 of 96 shown).

Underneath the row of GUI elements is a table. The table contains the following columns: Select, ID, Capture Time, Collection Level, Within A Baseline. The values in the ID column are links. The Select column shows a selectable option.

12 rows are shown. The values of row 1 are: null, 92, Jan 20 2009 10:00:03 AM, TYPICAL, null. Each row increments the ID by 1, so row 2 is 93, row 3 is 94, and so on. Each snapshot occurs an hour after the previous snapshot, except for the last one, which occurs at 1:25:08 PM.

End of description.

This graphic shows a horizontal arrow labeled User Response Time, with a point on the right end. In a horizontal row above the arrow are four boxes with the following contents: Query for Novels by Author, Browse Results of Query, Add Item to Cart, and Checkout. The first, third, and fourth boxes have a pair of bold arrows: one arrow points down from the box to the main arrow and the other points up. The second box in the series has two pairs of arrows. On the main arrow underneath each pair of up/down arrows, a thin shaded segment is shown. The shading indicates DB Time.

End of description.

The screenshot shows a portion of the Schedule Implementation page. At the top is a text entry field labeled Job Name (preceded by an asterisk). The value in the text entry field is SQLACCESSIMPL662656. Below the Job Name is the Stop on Error option, which is selected. This option has the following descriptive text: "If checked, this implementation job will stop processing if an error occurs. If not checked, this job will ignore errors and will continue processing all actions of selected recommendations."

The Scheduling Options section appears next in the screenshot. Under the section heading is the Schedule Type action list, which has the value Standard selected. Below this is the Time Zone action list, which has the value PST8PDT selected. To the right of the Time Zone action list is a Search icon (a flashlight symbol). Below the Time Zone action list if the Repeating section. In the Repeating section there is a single action list, Repeat, which has the value Do Not Repeat selected.

Next the Start section is shown. This section has two options, Immediately (selected) and Later. Following the Later option there is a Date text entry field, which has the value Mar 29, 2007. This text entry field has a Calendar icon to its right. Below the Date field are 3 action lists for selecting the time. The first action list has the value 12, the second action list has the value 05, and the third action list has the value 00. To the right of these Time action lists are 2 options: AM, and PM (selected).

End of description.

The Export SQL Tuning Set page is shown.

The Export SQL Tuning Set page is shown.The Cancel and OK buttons are on the right side of the page.The SQL Tuning Set Name is STS_BASELINE_PEAK_LOAD.Owner is IMMCHAN.Directory Object is a list showing DATA_PUMP_DIR.Directory Name is c:\oracle\admin\orcl111\dpdump.Export File is a text field showing EXPDAT_STS_BASELINE_PEAK_LOAD.Log File is a text field showing EXPDAT_STS_BASELINE_PEAK_LOAD.The following text appears: "Select a tablespace which will be used temporarily to store the data for the export operation. By default, SYSAUX will be used."The table has the following columns: Select, Tablespace Name, Available Space (MB).Row 1 has the following values: (selected option), SYSAUX, 30.625.Row 2 has the following values: (deselected option), EXAMPLE, 24.5625.Row 3 has the following values: (deselected option), SYSTEM, 2.5625.Row 4 has the following values: (deslected option), USERS, 15.3125.The Job Parameters section is below the table.The Job Name field shows EXPDAT_STS_BASELINE_PEAK_LOAD.The Description field is blank.The Schedule section is shown. The Immediately option is selected. The Later option is deselected. The Time Zone field shows Pacific/Pago_Pago.The Data is Mar 12, 2007.The Time is 2:21 a.m.

End of description.

A portion of the Create Baseline: Single Baseline page is shown.

Time Range is selected. The Start Time is Feb 6 2009 at 3:10 p.m. The End Time is Feb 6 2009 at 3:30 p.m.

End of description

This screenshot shows the Diagnostic Summary section of the Database Home page.Four label-value pairs appear. The value for the ADDM Findings label is: 4. This value is a link. The value for the Period Start Time label is: Jan 21, 2009 10:00:53 AM. The value for the Alert Log label is: No ORA- errors. This value is a link. The value for Active Incidents is 0.

End of description.

The Memory Details view is shown.

This view contains 4 sections: Memory Page Scan Rate (pages/sec), Memory Utilization (%), Swap Utilization (%), and Top 10 Processes (ordered by Memory).

The Memory Page Scan Rate chart has 0 to .0030 on the y-axis and 11:48 to 1:30 on the x-axis. The rate jumps to .0020 at around 12:00 and then falls off. The Current Memory Page Scan Rate is 0.

The Memory Utilization chart shows 0 o 100 on the y-axis and 11:48 to 1:30 on the x-axis. The chart shows a solid horizontal line at 76.9. The Current Memory Utilization (%) is 76.9.

The Swap Utilization chart has 0 to 100 on the y-axis and 12:33 to 1:30 on the x-axis. The chart shows a horizontal line at 7.78. Current Swap Utilization (%) is 7.78.

End of description.

Data from the two periods compared are shown under the General subpage in the Compare Periods: Results page.

The table has the following columns: Name, First Period Metric Ratio, Second Period Metric Ratio, First Period Value, Second Period Value, First Period Rate Per Second, Second Period Rate Per Second.

Some metrics show null, null, 0, 0, 0, 0. These rows are: DB cpu, global cache cr block receive time, global cache cr blocks received, global cache current block receive time, global cache current blocks received, global cache get time, global cache gets.

Row 2 has the following values: DB time (seconds), (small bar), null, 13263.84, 10360.69, 3.70, 2.85.

Row 3 has the following values: db block changes, (short bar), null, 64654.00, 55517.00, 17.88, 15.36.

Row 4 has the following values: execute count, (bar extending half through column), null, 1043192, 40113, 288.41, 11.10.

End of description.

The SQL Access Advisor: Workload Source page is shown.

The Cancel and Next buttons are shown. Between the buttons is the text "Step 1 of 4."

The following name-value pairs are shown: Database is database, Logged In As is DBA1.

The following text appears: "Select the source of the workload that you want to use for the analysis. The best workload is one that fully represents all the SQL statements that access the underlying tables."

The Current and Recent SQL Activity option is selected.

End of description.

This screenshot shows the Results table for the SQL Access Advisor task. Below the heading Results, there are 3 buttons and an action list. They are labeled, from left to right: View Result, Delete, Actions (list), and Go.

The table has 9 columns and two rows. The column headings are, from left to right: Select (option button), Advisory Type, Name, Description, User, Status, Start Time, Duration (seconds), and Expires in (days). The rows in the table have the following values, listed in order from left to right:

Selected, SQL Access Advisor, SQLACCESS2710801 (hyperlink), SQL Access Advisor, SYS, CREATED, no value, no value, 30

Not selected, SQL Access Advisor, SQLACCESS9817748, SQL Access Advisor, SYS, CREATE, no value, no value, 30

End of description.

The Run ADDM page is shown.

The Cancel and OK buttons are right-justified at the top of the page.

You can make the following selections: Run ADDM to analyze current performance, or Run ADDM to analyze past performance. If you make the latter suggestions, then you can set Period Start Time and Period End Time. In the screenshot, the Period Start Time is Jan 29, 2009 11:00:48 AM PST. The Period End time is one hour later.

A line chart is shown. The y-axis units are average active sessions and range from 0 to .754. The x-axis units are times of day, shown in 10-minute intervals, and range from 9 a.m. to 11:30 a.m. Waits peaked significantly between 10 and 11.

End of description.

The Top Services section is displayed.

The View list shows the value Top Services.

The table has the following columns: Activity (%), and Service. The Activity column is shown as a horizontal bar chart with an associated value. The Service values are shown as links.

The able has 3 rows. Row 1 has the following values: 86.47, SYS$USERS. Row 2 has the following values: 12.78, SYS$BACKGROUND. Row 3 has the following values: .75, emtst.

The total sample count is 298.

End of description.

The screenshot shows a portion of the SQL Statements subpage. There are two sections displayed: SQL Statements By Cost Improvement and Select SQL Statements to be Improved.

The SQL Statements By Cost Improvement section contains a bar chart. The vertical axis measures Cost Improvement with values from 1.5 to 4.5 incrementing by 0.5. The horizontal axis contains the Statement ID, with values, from left to right: 2421, 1905, 2192, and 2283. According to the legend, blue bars indicate Cost Improvement. There are 4 blue bars in the graph:

For statement ID 2421, the cost improvement is about 4.0

For statement ID 1905, the cost improvement is about 2.0

For statement ID 2192, the cost improvement is about 2.0

For statement ID 2283, the cost improvement is about 2.0

The Select SQL Statements to be Improved section contains a table. Just below the section heading is the option "Affected SQL Statements", which is selected. Below this option are two buttons: Schedule Implementation and Show SQL. Below the buttons and just above the table are 2 links: Select All and Select None. The table has 9 columns and 4 rows. The columns headings, from left to right, are: Select (an option box), Statement ID, Statement, Recommendation ID, Original Cost, New Cost, Cost Improvement, Cost Improvement (%), and Execution Count. The rows in the table have the following values, from left to right:

Not selected, 2421 (hyperlinked), "select e.employee_id, e.last_name, d.department_name from employees e, departments d where e.department_id = d.department_id", 1 (hyperlinked), 7, 3, 4, 57.14, 1

Not selected, 1905 (hyperlinked), "select sum(unit_cost) from costs group by prod_id", 4 (hyperlinked), 8, 6, 2, 25.00, 2

Not selected, 2192 (hyperlinked), "SELECT c.cust_last_name, sum(s.amount_sold) AS dollars, sum(s.quantity_sold) as quantity FROM sales s, customers c, products p WHERE c.cust_id = s.cust_id AND s.prod_id = p.prod_id AND c.cust_state_province IN ('Dublin','Galway') GROUP BY c.cust_last_name", 2 (hyperlinked), 8, 6, 2, 25.00, 2

Selected, 2283 (hyperlinked), "SELECT c.cust_id, SUM(amount_sold) AS dollar_sales FROM sales s, customers c HWERE s.cust_id = c.cust_id GROUP BY c.cust_id", 3 (hyperlinked), 8, 6, 2, 25.00, 2

End of description.

The screenshot shows the Actions and SQL Affected by Recommendations sections of the Recommendation Details page. At the top of the Actions section is the "Set Tablespace for All Actions" text entry field, which is blank. Next to the text entry field is a search icon (a flashlight symbol), and to the left of that is a button labeled Go. Below the text entry field is a table. There are 6 columns and 4 rows in the table. The column headings are, from left to right: Implementation Status, Recommendation Ids, Action, Object Name, Object Attributes, and Base Table. The rows in the table have the following values:

A blue square, 3, CREATE MATERIALIZED VIEW LOG (hyperlinked), no value, no value, SH.CUSTOMERS

A blue square, 3, CREATE MATERIALIZED VIEW LOG (hyperlinked), no value, no value, SH.SALES

A blue square, 3, CREATE MATERIALIZED VIEW (hyperlinked), MV$$_0065002 (modifiable), General Match, no value

A blue square, 3, GATHER TABLE STATISTICS (hyperlinked), MV$$_0065002, no value, no value

The SQL Affected by Recommendations section contains a table with 8 columns and 1 row. The column headers and row values are, from left to right:

Statement ID: 2283 (hyperlinked)

Statement: SELECT c.cust_id, SUM(amount_sold) AS dollar_sales FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id

Recommendation ID: 3

Original Cost: 8

New Cost: 6

Cost Improvement: 2

Cost Improvement (%): 25.00

Execution Count: 2

End of description.

The Memory Utilization (%): Last 24 hours section is shown.

The View Data list on the upper right shows Last 24 hours.

The Statistics section contains 8 name-value pairs.

Last Known Value: 88.6

Average Value: 78.99

High Value 95.98

Low Value: 59.89

Warning Threshold 99

Critical Threshold: Not Defined

Occurrences Before Alert: 6

Corrective Action: None

The Metric Value section contains a chart. The x-axis ranges from 49.010 to 98.020. The y-axis ranges from 10:01 p.m. to 10 a.m. The chart shows activity at around 85, which drops to 61 after midnight. The activity gradually rises to 85 at 5:00.

The Alert History section contains the Comment for Most Recent Alert text field. To the right is the Add Comment button.

The table contains the following columns: Severity, Timestamp, Message, Last Comment, and Details. The table contains 1 row. The row values are: (check mark icon), Mar 4 2007 6:52:21 AM, Memory Utilization is 91.3%, null, and null.

End of description.

The Compare Periods: Review section is shown.

On the top right are the following buttons: Cancel, Back, and Finish.

On the left side is a name-value pair. The name is Database and the value is database.

Underneath are two sections: First Period and Second Period.

First Period contains 6 name-value pairs.

The Baseline ID label has the value 2.

The Name label has the value BASELINE_THU_1530.

The Beginning Snapshot ID label has the value 9.

The Ending Snapshot ID label has the value 15.

The Capture Time label has the value Feb 5 2009 3:30:14 PM.

The Capture Time label has the value Feb 5 2009 4:30:50 PM.

The Second Period section has 4 name-value pairs.

The Beginning Snapshot ID label has the value 18.

The Ending Snapshot ID label has the value 19.

The Beginning Snapshot Capture Time label has the value Feb 5 2009 5:00:03 PM.

The Ending Snapshot Capture Time label has the value Feb 5 2009 5:10:05 PM.

End of description.

The Results section is shown. Underneath the title are the View Result and Delete buttons. The Action list shows Re-schedule selected. To the right of the list is the Go button.The table contains the following columns: Select, Advisory Type, Name, Description, User, Status, Start Time, Duration (seconds), Expires In (days).Row 1 has the following values: (selected), SQL Tuning Advisor, SQL_TUNING, null, SYS, SCHEDULED, Mar 5 2007 5:30:00 AM, null, 30.Row 2 has the following values: (deselected), SQL Tuning Advisor, SYS_AUTO_SQL_TUNING_TASK, Automatic SQL Tuning Task, SYS, COMPLETED, Mar 4 2007 4:42:33 PM, 955, UNLIMITED.

End of description.

This screenshot shows a section of the Snapshot Collection page.

One selection possibility is shown. For Snapshot Collection, select one of the following: System Snapshot Interval (shown selected), or Turn Off Snapshot Collection. The first option has a corresponding list, with the value 30 Minutes shown.

End of description.

The Recommendations for SQL ID:05b6pvb81dg8b section is shown.

The Return button is to the right of the page.

The SQL Text section shows the following text:

SELECT /*+ ORDERED USE NL(c) FULL(c) */ COUNT(*) FROM SH.SALES S, SH.CUSTOMERS C WHERE C.CUST_ID=S.CUST_ID AND CUST FIRST NAME='Dina' ORDER BY TIME

The Selection Recommendation section includes the Original Explain Plan (Annotated) button.

The Implement button is shown.

The table includes the following columns: Select, Type, Findings, Recommendations, Rationale, Benefit (%), New Explain Plan, Compare Explain Plans.

Row 1 includes the following values: (selected), SQL Profile, "A potentially better execution plan was found for this statement", "Consider accepting the recommended SQL Profile.", null, 99.63, (spectacles icon), (spectacles icon).

End of description.

The Consumer Group tab is shown.

2 line charts are shown.

In the first chart, the y-axis units are MB per second. The y-axis values range from 0 to 50. The x-axis units are hours of day, shown in 10-minute intervals. The values range from 11:10 a.m. to 12:10 p.m. The following consumer groups are shown: ORA$AUTOTASK_SQL_GROUP, SYS GROUP, ORA$DIAGNOSTICS, ORA$AUTOTASK_URGENT_GROUP, ORA$AUTOTASK_HEALTH_GROUP, _ORACLE_BACKGROUND_GROUP, OTHER_GROUPS, ORA$AUTOTASK_MEDIUM_GROUP, ORA$AUTOTASK_STATS_GROUP, ORA$AUTOTASK_SPACE_GROUP. The _ORACLE_BACKGROUND_GROUP ranges between 0 and 5 starting at 11:50 a.m. The ORA$AUTOTASK_URGENT_GROUP spikes to 25 at 12:05.

In the second chart, the y-axis units are I/O per second. The y-axis values range from 0 to 900. The x-axis units are hours of day, shown in 10-minute intervals. The values range from 11:10 a.m. to 12:10 p.m. The following types of information are tracked in the chart: Parallel QC and Parallel Slaves. The _ORACLE_BACKGROUND_GROUP ranges between 0 and 100 starting at 11:50 a.m. The ORA$AUTOTASK_URGENT_GROUP spikes to 900 at 12:05 p.m.

End of description.

This screenshot shows the Top Working SQL section of the Active Sessions Working page.

Underneath the section title is the Actions list. The selected action is Schedule SQL Tuning Advisor. Next to the list is a Go button.

Underneath the row of elements is a table. The table contains the following columns: Select, Activity (%), SQL ID, SQL Type. Values in the SQL ID column are displayed as a link. Values in the Activity column are displayed as a horizontal bar chart with an associated number.

7 rows are shown in the table. The values of row 1 are: 90.58, 05p6pxb81dg8b, SELECT. All SQL types are SELECT except for row 3, which is PL/SQL Execute. The Activity % of all rows except for row 1 is 1.81 or less.

End of description.

The SQL Plan Baseline tab of the SQL Plan Control page is shown.

The Refresh button is on the right.

The text is "A SQL Plan Baseline is an execution plan deemed to have acceptable performance for a given SQL statement."

The Settings section has the following name-value pairs: Capture SQL Plan Baselines is FALSE, Use SQL Plan Baselines is TRUE, Plan Retention (weeks) is 53. The Configure button is next to Plan Retention.

The Jobs for SQL Plan Baselines section is shown. The Load Jobs shows SPM_LOAD_20070626_154521.

The Search section is shown. The SQL Text field is null. The Go button is to the right of the field.

End of description.

The screenshot show the Throughput tab selected. The Instance Throughput Rate can be set to Per Second (shown selected) or Per Transaction.A line chart labeled is shown. The units of the y-axis are: total number per second. The units of the x-axis are times of the day, shown in 10-minute intervals. The x-coordinates shown range from 10:00 a.m. to 11:00 a.m. on April 3, 2007. The y-coordinates shown range from 0 to 2000. The following types of information are plotted in the chart: Logons, Transactions. The chart indicates that a spike in transactions occurred at 10:45 a.m.A second line chart is shown. The units of the y-axis are: total number per second. The y-coordinates shown range from 0 to 1000. The following types of information are plotted in the chart: Physical Reads (KB), and Redo Size (KB). The chart indicates that a spike in physical reads occurred at 10:45 a.m.

End of description.

The Create Baseline: Single Baseline section appears.

On the top right the Cancel, Back, Finish buttons appear.

The Baseline Name field is empty.

In the Baseline Interval section, Snapshot Range is selected.

The Change Chart Time Period navigation tree is collapsed.

In the Select Time Period section, the Period Start Time option shows Feb 5 2009 3:40:16 PM PST. It is not selected. The Period End Time option shows Mar 5 2009 3:50:18 PM PST. This option is selected.

A chart shows active sessions on the y-axis. The values range from 0 to 2.232. The x-axis shows hours of day, shown in 1-hour intervals, ranging from 2:20 p.m. to 3:50 p.m.

The Time Range option is not selected.

The Start Time is Feb 5 2009 at 3:40 p.m. The End Time is Feb 5 2009 at 3:50 p.m.

End of description.

The Tuning History subpage is shown.The title is SQL Tuning History.The text says: "The following SQL tuning tasks provide the recommendations to tune this SQL statement."The table has the following columns: Advisor Task Name, Advisor Task Owner, Task Completion. Row 1 has the following values: SQL_TUNING_1234210497448, DBA1, Feb 9 2009 12:15:28 PM.The following section is ADDM Findings for this SQL during historic period.The table has the Finding Name and Occurrences (last 24 hrs) columns. Row 1 has the following values: Top SQL Statements, 8 of 144.

End of description.

The table contains the following columns: Sid, Serial#, % Activity, Event, % Event, User, Program, # Samples Active, XIDs.

Row 1 contains the following values: "123,275", 27.33, db file sequential read, 22.44, HR, sqlplus@stbcs01-1, 243/300, 16.

Row 2 contains the following values: null, null, flashback buf free by RVWR, null, null, 36/300, 4.

Row 3 contains the following values: "126,28313", 27.33, CPU + Wait for CPU, 26.78, SH, sqlplus -L@dad...1 (TNS V1-V3), 290/300, 0.

Row 4 contains the following values: "154,5", 15.24, CPU + Wait for CPU, 11.54, SYS, oracle@dadvmn0621 (RVWR), 125/300, 0.

Row 5 contains the following values: null, null, control file parallel write, 2.12, null, null, 23/300, 0.

Row 6 contains the following values: "161,1", 8.31, log file parallel write, 8.22, SYS, oracle@dadvmn0621 (LGWR), 39/300, 0.

Row 7 contains the following values: "110,36790", 2.86, CPU + Wait for CPU, 1.11, SYS, oracle@dadvmn0621 (M000), 12/300, 0.

End of description.

The Performance Page Settings page is shown.

The Cancel and OK buttons are on the top right of the screenshot.

The Detailed Chart Settings section contains 3 settings.

Default View can be set to: Throughput (selected), I/O, Parallel Execution, or Services.

Throughput Chart Settings can be set to: Per Second (selected) or Per Transaction.

I/O Chart Settings can be set to: I/O Function (selected), I/O Type, Consumer Group.

The Baseline Display section contains 3 settings: Do not show the baseline values (selected), Show the 99th percentile line using the system moving window baseline, and Show the 99th percentile line using a static baseline with computed statistics. The last option is associated with the Baseline Name list.

End of description.

The View Data list on the right side of the page shows Real Time: 15 Second Refresh.

The Active Sessions chart is shown. The y-axis values start at 0.0 and go to 5. The x-axis values start at 11:21 and go to 12:21.

The chart shows System/IO spiking at 11:26 and 11:51.

The Detail for Selected 5 Minute Interval section shows a Start Time of Feb 9, 2009, 11:37:00 PM PST. The Run ASH Report button is on the right side of the page.

The Top SQL section shows the Actions list value of Schedule SQL Tuning Advisor. The Go button is to the right of the list.

Below the Actions list are the Select All and Select None links.

The table has the following columns: Select, Activity (%), SQL ID, and SQL Type.

Row 1 has the following values: (check box), 14.81, 05b6pvb81dg8b, PL/SQL EXECUTE.

Row 2 has the following values: (check box), 13.51, 8tck1adu5gyfc, DELETE.

Rows 3-8 have various DML statements.

The Top Sessions table is also displayed but is not relevant for this graph.

Below the table the Actions list shows Schedule SQL Tuning Advisor and the Go button.

End of description.

The Top Service/Module subsection is shown.

The table contains the following columns: Service, Module, % Activity, Action, and % Action.

Row 1 contains the following values: SYS$USERS, SQL*Plus, 54.66, EMP_DML, 27.33.

Row 2 contains the following values: null, null, null, SALES_INFO, and 27.33.

Row 2 contains the following values: SYS$BACKGROUND, MMON_SLAVE, 4.16, Auto-Flush Slave Action, 2.86.

Row 3 contains the following values: null, null, null, Auto ADDM Slave Action, and 2.86.

Row 4 contains the following values: null, UNNAMED, 4.90, UNNAMED, 2.31.

Row 5 contains the following values: SYS$USERS, Realtime Connection, 1.47, UNNAMED, and 1.47.

Row 6 contains the following values: emdc, OEM.SystemPool, 1.35, NotificationMgr, 1.10.

End of description.

The Host CPU section is shown.

A bar vertical bar graph has an x-axis with the following values: 0, 25, 50, 75, 100.

The graphic shows emtst at 90 and Other as the remaining 10.

The Load label shows 3.25. The Paging label shows 0.00.

This screenshot shows a section of the Initialization Parameter page.

In the bottom right corner of the screenshot are the following buttons: Save to File.

This screenshot shows a table. The table contains the following columns: Name, Help, Revisions, Value, Comments, Type, Basic, Modified, Dynamic, and Category. Values in the Value column are displayed as a list.

1 row is shown in the table. The values of row 1 are: statistics_level, (information icon), null, TYPICAL, null, String, null, null, (check mark), and Diagnostics and Statistics.

End of description.

The Recommendations for SQL ID:5mxdwvuf9j3vp page is shown.

The Return button is on the right side of the page.

The SQL Text is SELECT /* ...

The Select Recommendation section includes the Original Explain Plan (Annotated) button. Underneath it is the Implement button.

The table includes the following columns: Select, Type, Findings, Recommendations, Rationale, Benefit (%), New Explain Plan, Compare Explain Plans.

Row 1 has the following values: (selected), SQL Profile, "A potentially better execution plan was found for this statement", "Consider accepting the recommended SQL profile", null, 99.56, (spectacles icon), (spectacles icon).

Row 2 has the following values: (deselected), Index, "The execution plan of this statement can be improved by creating one or more indices", "Consider running the Access Advisor to improve the physical schema design or creating the recommended index", "Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run 'Access Advisor' using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption", 62.08, (spectacles icon), (spectacles icon).

End of description.

A portion of the Compare Periods: Second Period Start page is shown.

The Select a Baseline and Select Beginning Snapshot options (selected) are shown. Underneath Select Beginning Snapshot is the Go To Time field. The value is 3/22/07. A list shows the value 6:00 PM. A Go button is next to the list.

Underneath the Go To Time field is a table. The table has the following columns: Select (values are shown as selectable options), ID (values are shown as links), Capture Time, Collection Level, and Within A Baseline (values shown as check marks). The table has 10 rows, with row 8 selected. Row 8 has the values: (selected option), 18, Feb 5, 2009 5:00:03 PM, TYPICAL, and null.

End of description.

This screenshot shows the General section of the Automatic Workload Repository page.

In the top right corner of the screenshot are the following buttons: Edit.

4 label-value pairs appear. The value for the Snapshot Retention (days) label is: 8. The value for the Snapshot Interval (minutes) label is: 60. The value for the Collection Level label is: TYPICAL. The value for the Next Snapshot Capture Time label is: Jan 20, 2009 2:25:08 PM.

End of description.

The screenshot shows the Recommendations subpage of the Results for Tasks page. There are two sections shown in the screenshot: Recommendations by Cost Improvement and Select Recommendations for Implementation.

The Recommendations by Cost Improvement section contains a bar chart. The vertical axis measures the Cost Improvement, with values from 1.5 to 4.5 incrementing by 0.5. The horizontal axis shows the ID, with values, from left to right, of 1, 3, 4, and 2. The legend shows that the blue bars measure Cost Improvement. All the bars in the graph are blue.

For ID 1, the cost improvement value is 4.0.

For ID 3, the cost improvement value is 2.0.

For ID 4, the cost improvement value is 2.0.

For ID 2, the cost improvement value is 2.0.

The Select Recommendations for Implementation section contains a table. Just below the section heading is a selectable option labeled "Include Retain Actions", which is selected. 3 buttons are displayed below this option. The buttons are labeled, from left to right, Recommendation Details, Schedule Implementation, and Show SQL. Below the Recommendation Details button are 2 links, Select All and Select None.

The table has 9 columns and 4 rows in it. The columns headings, from left to right, are: Select (an option box), Implementation Status, ID, Actions, Action Types, Cost Improvement, Cost Improvement (%), Estimated Space Used (MB), and Affected SQL Statements. The rows in the table have the following values, from left to right:

Selected, a blue square, 1 (hyperlinked), 4, 3 squares (blue, dark blue, and beige), 4, 40.00, 0.008, and 1

Selected, a blue square, 3 (hyperlinked), 4, 3 squares (blue, dark blue, and beige), 2, 20.00, 0.008, and 1

Selected, a blue square, 4 (hyperlinked), 3, 3 squares (blue, dark blue, and beige), 2, 20.00, 0.008, and 1

Selected, a blue square, 2 (hyperlinked), 6, 4 squares (pale blue, blue, dark blue, and beige), 2, 20.00, 0.016, and 1

End of description.

The Run ASH Report page is shown.

The Generate Report button is on the upper right of the screen.

The Start Date field shows the value 1/29/09. A calendar icon is next to the field.

The End Date field shows the value 1/29/09. A calendar icon is next to the field.

The Start Time shown is 2:30 PM. The End Time shown in 2:35 PM.

The Filter list shows the value SID. Next to the list is an empty text field.

End of description.

A table in the SQL Profile subpage is shown.

The buttons at the top right are Load and Unpack.

The buttons at the top of the table are Enable, Disable, Drop, Evolve, and Pack.

The Access Structures to Recommend section on the SQL Access Advisor: Recommended Options page is displayed. The following check boxes are selected: Indexes, Materialized Views, and Partitioning.

End of description.

The CPU Utilization (%): Last 24 hours page is shown.

On the upper right is the View Data list, which shows Last 24 hours.

The Statistics section contains 8 name-value pairs. Last Known Value: 21.82. Average Value: 26.46. High Value: 100. Low Value: 8.41. Warning Threshold: 80. Critical Threshold: 95. Occurrences Before Alert: 6. Corrective Action: None.

The Metric Value chart measures 0 to 110 on the y-axis and 9:01 p.m. to 10 p.m. on the x-axis. The chart shows a spike to 55 at 1 a.m. and then a gradual rise to 55.0 at 5 p.m. The line falls and then spikes again at 9.

Underneath the chart is the Alert History section.

The Comment for Most Recent Alert text box is null. To the right is the Add Comment button.

Underneath is a table with the following columns: Severity, Timestamp, Message, Last Comment, and Details. The table contains three rows. The values are: (exclamation point icon), Mar 4 2007 10:41, CPU Utilization is 21.82%, null, and null. The other rows are similar.

End of description.

The Top Files section is shown.

The View list shows the value Top Files.

The table contains the following columns: Activity (%), Name, Tablespace, and Average Wait Time (ms). Values in the Activity column are shown as bar charts with an associated number. Values in the Name and Tablespace columns are shown as links.

The table contains 2 rows. The values of row 1 are: 100, t_db1.f, SYSTEM, and 322.

The total sample count is 3.

End of description.

The Compute Threshold Statistics: BASELINE_THU_1530 screen appears.

On the upper right of the page the Cancel and Submit buttons appear.

In the Task Information section, the Name field shows the value BSLNSTATS_1233881388. The Description field includes the following value: Compute metric statistics for baseline BASELINE_THU_1530.

The Schedule section includes 2 name-value pairs. The value for the Schedule Type label is Standard. The value for the Time Zone label is GMT-08:00.

In the Repeating section, the value for the Repeat label is Do Not Repeat.

The Start section supports two mutually exclusive options: Immediately and Later. For Late, the Date list shows 2/5/09. There is a calendar icon next to the Date field. The Time list shows 4:50 PM.

End of description.

The SQL Statements section in the Schedule SQL Tuning Advisor page is shown.

The table has columns SQL Text and Parsing Schema.

Row 1 has the following value for column 1: SELECT /*+ ORDERED USE_NL(c) FULL(c) FULL(s) */ COUNT (*) FROM SH.SALES S, SH.CUSTOMERS C WHERE C.CUST_ID=S.CUST_ID AND CUST_FIRST_NAME='Dina' ORDER BY TIME_ID. The value for column 2 is: ICHAN.

End of description.

The Run ADDM page is shown.

The Cancel and OK buttons are right-justified at the top of the page.

You can make the following selections: Run ADDM to analyze current performance, or Run ADDM to analyze past performance. If you make the latter suggestions, then you can set Period Start Time and Period End Time. In the screenshot, the Period Start Time is Mar 20, 2007 12:50 PM. The Period End time is Mar 20, 2007 1:00:53 PM.

A line chart is shown. The y-axis units are average active sessions and range from 0 to .763. The x-axis units are times of day, shown in 10-minute intervals, and range from 11 to 1:00. CPU usage (shown in green) has peaked significantly in the last 10 minutes.

The ADDM Performance Analysis section is shown.The Task Name is ADDM:1621828677_1_8. The Time Range is Mar 20, 2007 1:08:01 PM to Mar 20 2007 1:12:01 PM.The Filters, View Snapshots, and View Report buttons are shown.The Task Owner is SYS. The Average Active Sessions is 1. The Period Start Time is Mar 20, 2007 1:00:54 PM PDT. The Period Duration is 9.6.A table includes the following columns: Impact (%), Finding, Occurrences (last 24 hrs).Row 1 includes the following values: 100, CPU Usage, 4 of 5.Row 2 includes the following values: 100, Top SQL by DB Time, 4 of 5.The Information Findings navigation tree is shown collapsed.

End of description.

The I/O breakdown options are: I/O Function, I/O Type (selected), and Consumer Group.

A line chart labeled I/O Megabytes per Second by I/O Type is shown. The y-axis units are MB per second. The y-axis values range from 0 to 50. The following types of information are tracked in the chart: Large Writes, Small Writes, Large Reads, and Small Reads. The chart shows a slight spike in LGWR activity at 10:45 a.m.

A line chart labeled I/O Requests per Second by I/O Type is shown. The y-axis units are I/O per second. The y-axis values range from 0 to 600. The following types of information are tracked in the chart: Large Writes, Small Writes, Large Reads, and Small Reads. The chart shows a huge spike in Small Writes activity at 10:45 a.m.

End of description.

This screenshot shows the top portion of the Performance page.

In the top right corner of the screenshot are the following elements: Settings Button, View Data list. The list item displayed is Real Time: 15 Second Refresh.

A line chart labeled Host is shown. The units of the x-axis are hours. The x-coordinates shown range from 2:39 pm to 4:00 pm. The units of the y-axis are: Percent. The y-coordinates shown range from 0.0 to 100.0. The following types of information are plotted in the chart: CPU Utilization %. The chart indicates that CPU utilization was high until 3:10, then dropped until 3:29, then spiked again.

A line chart labeled Average Active Sessions is shown. The units of the x-axis are times of the day, shown in 10-minute intervals. The x-coordinates shown range from 8:30 pm to 9:20 pm. The units of the y-axis are: active sessions. The y-coordinates shown range from 0.0 to 2.8. The following types of information are plotted in the chart: Other, Queueing, Network, Administrative, Configuration, Commit, Application, Concurrency, System I/O, User I/O, Scheduler, CPU Used. The chart indicates that spikes occurred from 8:30 to 8:40, from 8:50 to 9:10, and at 9:25. The largest spike was at about 8:55.

Underneath the chart is a subsection with the Throughput tab selected. The Instance Throughput Rate can be set to Per Second (shown selected) or Per Transaction.

A line chart labeled is shown. The units of the y-axis are: total number. The units of the x-axis are times of the day, shown in 10-minute intervals. The x-coordinates shown range from 2:39 pm to 4:00 pm. The y-coordinates shown range from 1 to 3. The following types of information are plotted in the chart: Logons, Transactions. The chart indicates that a spike in transactions occurred at 8:55 pm.

A second line chart is shown. The units of the y-axis are: total number. The y-coordinates shown range from 0 to 2000. The following types of information are plotted in the chart: Physical Reads (KB), and Redo Size (KB). The chart indicates that a spike in physical reads occurred at 2:39 pm and again at 3:35 pm.

End of description.

The General section of the Automatic Workload Repository page is shown.

The Edit button is on the upper right.

4 name-value pairs appear. The value of Snapshot Retention (days) is 694. The value of Snapshot Interval (minutes) is 10. The Collection Level is TYPICAL. The Next Snapshot Capture Time is Feb 5, 2009 5:50:18 PM.

The Manage Snapshots and Baselines section is shown.

The Run AWR Report button is on the upper right.

4 name-value pairs appear. The value of Snapshots is 22. The value of Baselines is 2. The values of Latest Snapshot Time is Feb 5, 2009 5:40:18 PM. The value of Earliest Snapshot Time is Feb 5, 2009 2:12:19 PM.

End of description.

This screenshot shows the Top DB Objects section.

The table has the following columns: Object ID, % Activity, Event, % Event, Object Name (Type), and Tablespace.

Row 1 has the following values: 66590, 32.10, enq:TM - contention, 32.10, HR.DEPARTMENTS (TABLE), EXAMPLE.

Row 2 has the following values: 66595, 1.99, buffer busy waits, 1.99, HR.EMPLOYEES (TABLE), EXAMPLE.

End of description.

The Database Activity page is shown.

The ADDM Performance Analysis section is shown under the chart. The Task Name is TASK_96 (End Time:Mar 21, 2007 11:00:55 PM). The Time Range is: Mar 21, 2007 11:00:01 PM to Mar 21, 2007 11:06:01 PM.

The Filters, View Snapshots, and View Report buttons appear.

The Task Owner is SYS. The Average Active Sessions is 0.3. The Period Start Time is Mar 21 2007 10:00:45 PM PDT. The Period Duration (minutes) is 60.2.

The table includes the following columns: Impact (%), Finding, Occurrences (last 24 hrs). The table shows 7 rows.

Row 1 has the following values: 100, Undersized Shared Pool, and 2 of 49.

Row 2 has the following values: 37.2, Top SQL by DB Time, and 10 of 49.

Row 3 has the following values: 13.7, "Scheduler" Wait Class, and 2 of 49.

Row 4 has the following values: 11.4, Undersized Buffer Cache, 2 of 49.

Row 5 has the following values: 7.3, CPU Usage, and 10 of 49.

Row 6 has the following values: 7.2, Hard Parse Due to Parse Errors, 2 of 49.

Row 7 has the following values: 4.7, Top Segments by I/O, 2 of 49.

The Informational Findings navigation tree is collapsed.

End of description.

This screenshot shows the SQL Access Advisor: Review page. At the top of the page, on the right-hand side, are 4 buttons. These buttons are labeled, from left to right: Cancel, Show SQL, Back, and Submit. Between the Back and Submit buttons is the text "Step 4 of 4".

At the top of the page on the right-hand side, the following information is displayed:

Database: database

Logged In As: DBA1

Following this information is the text "Please review the SQL Access Advisor options and values you have selected." It then displays 3 values, and a table of options. The 3 values are:

Task Name: SQLACCESS9817748

Task Description: SQL Access Advisor

Scheduled Start Time: Run Immediately

The Options table has a button labeled Show All Options at the top, on the left-hand side. The table has 4 columns, titled, from left to right: Modified, Option, Value, and Description. There are 3 rows shown in the table in the screenshot. These rows have the values:

Check mark, Analysis Scope, All Tuning Artifacts, "The type of recommendations that are allowed"

Check mark, Total Time Limit (minutes), 30, "Specifies the total time limit in minutes for the current SQL Access Advisor"

Check mark, Workload SQL Limit, 25, "Specifies the number of SQL statements to be analyzed"

Below the table, on the right-hand side are 4 buttons. These buttons are labeled, from left to right: Cancel, Show SQL, Back, and Submit. Between the Back and Submit buttons is the text "Step 4 of 4".

End of description.

The Services tab is shown.

1 line chart is shown.

In the first chart, the y-axis units are active sessions. The y-axis values range from 0 to 5.5. The x-axis units are hours of day, shown in 10-minute intervals. The values range from 10:20 a.m. to 11:20 a.m. The following types of information are tracked in the chart: SYS$BACKGROUND, emdc, and SYS$USERS. The chart shows that the largest spikes are caused by SYS$USERS and occur between 10:20 and 10:30 a.m., and from 10:40 a.m. to 11:20 a.m.

End of description.

The table contains the following columns: SQL ID, Planhash, Sampled # of Executions, % Activity, Event, % Event, Top Row Source, % RwSrc, SQL Text. Values in the first column are shown as links.

Row 1 contains the following values: 31h2wmu3q47u6, 4090065844, 277, 26.22, CPU + Wait for CPU, 26.13, TABLE ACCESS - FULL, 19.94, SELECT /*+ ORDERED USE_NL(c).

Row 2 contains the following values: 3djkjtba139ct, 1426549735, 148, 13.76, db file sequential read, 12.00, ** Row Source Not Available **, 12.00, INSERT INTO EMPLOYEES VALUES.

Row 3 contains the following values: null, null, null, null, flashback buf free by RVWR, 1.39, ** Row Source Not Available **,1.39, null.

Row 4 contains the following values: null, null, null, null, flashback buf free by RVWR, 1.75, DELETE, 1.75, null.

End of description.

This screenshot shows a table in The Compare Periods: Results page.

The table has the following columns: Name, First Period Metric Ratio (shown as a bar chart), Second Period Metric Ratio (shown as a bar chart), First Period Value, Second Period Value, First Period Rate Per Second, Second Period Rate Per Second.

Row 1 has the values: DB cpu (seconds), null, null, 0, 0, 0, 0.

Row 2 has the values: Db time (seconds), null, null, 10345.23, 1715.34, 2.85, 2.85.

Row 3 has the values: db block changes, (short bar), null, 19944, 26344, 11.42, 14.56.

Row 4 has the values: execute count, (tiny bar), null, 20285, 20770, 11.61, 11.48.

Rows for the following metrics showed all 0s: global cache cr block receive time, global cache cr blocks received, global cache current block receive time, global cache current blocks received, global cache get time, global cache gets.

End of description.

This graphic shows the View Data list. The following options are shown: Real Time: Manual Refresh, Real Time: 15 Second Refresh, and Historical.

The Select Time Period page is shown.

The Change Chart Time Period link is shown.

The Select Time Period section includes the following text: "Choose the Period Start Time option, then click a snapshot icon in the chart to select the period start time. Repeat the process for the period end time."

Period Start Time is selected. The time is Mar 4, 2007 9:01:22 PM PST. Period End Time is deselected. The time is Mar 5, 2007 1:00:48 AM PST.

A bar chart is shown. The y-axis is labeled Active Sessions. The values range from 0 to 1.347. The x-axis ranges from 9:01 p.m. to 6 a.m. The legend shows Wait (red), User I/O (blue), and CPU (green). The most activity is in CPU. It ranges from .40 at 9 a.m. to .45 at 10 am, then dips to .40 at 11 and up to .5 at midnight where is stays until it begins to drop at 1 a.m.

End of description.

The Advisor Tasks page is shown.

The Change Default Parameters button is right-justified.

The Search section is shown.

The Advisory Type list has a value of ADDM. The Task Name list shows a null value. The Advisor Runs list shows a value of All. The status list shows the value of All. The Go button appears to the right of the lists.

The Results section includes a table.

Above the table are the View Result and Delete buttons. The Actions list shows the Re-schedule value. To the right of this list is the Go button.

The table has the following columns: Select, Advisory Type, Name, Description, User, Status, Start Time, Duration (seconds), and Expires In (days). The Name values appear as links. The Select values appear as selectable options.

Row 1 contains the following values: (option, shown selected), ADDM, TASK_447, ADDM auto run, DBA1, COMPLETED, Jan 29 2009, 0, and 30.

Row 2 contains the following values: (option), ADDM, ADDM:399937146_1_416, ADDM auto run, DBA1, COMPLETED, Jan 29 2009 11:29:12 AM, 1, 30.

Row 3 contains the following values: (option), ADDM, ADDM:399937146_1_415, ADDM auto run, DBA1, COMPLETED, Jan 29 2009 11:23:12 AM, 0, 30.

End of description.

The Create SQL Tuning Set: Review page is shown.

The Create SQL Tuning Set: Review section is shown.The Database is orcl111.The Cancel, Back, and Submit buttons are shown. The text "Step 5 of 5" is between Back and Submit.Below the buttons is the text "Review the SQL Tuning Set options you have selected."The following name-value pairs are shown:SQL Tuning Set Name is STS_BASELINE_PEAK_LOAD.Owner is IMMCHAN.Description is "Contains SQL from BASELINE_PEAK_LOAD".Created an empty SQL tuning set is No.Load Methods is "Load SQL statements one time only".Data Source is "AWR Baseline".Baseline Name is BASELINE_PEAK_LOAD.Top N is <ALL>.Job Name is CREATE_STS_1173267160578.Scheduled Start Time is Run Immediately.

End of description.

The Host section of the Performance page is shown.

A line chart is shown. The y-axis units are Runnable Processes and range from 1.5 to 6. The x-axis units are times of time, shown in 10-minute intervals. The x-axis values range from 10:06 to 11:05 a.m. The chart tracks host activity. The chart shows that activity ranges between 2 and 4.5.

End of description.

The Top 10 Processes (ordered by CPU) section on the Performance Summary page is shown.

The table contains the following columns: Command, CPU Utilization (%), Resident Size (KB), Virtual Size (KB), Owner, Process ID. The table contains 10 rows.

Row 1 has the following values: ORACLE, 100, 257060, 390152, N/A, 836.

Row 2 has the following values: emdctl, .97, 22340, 20836, N/A, 5208.

Row 3 contains the following values: SWAGENT, 0, 84, 1872, N/A, 240.

Row 4 contains the following values: cmd, 0, 88, 1476, N/A, 2320.

Row 5 contains the following values: cmd, 0, 108, 1464, N/A, 2236.

Row 6 contains the following values: SWSOC, 0, 116, 2148, N/A, 348.

Row 7 contains the following values: SWSTRTR, 0, 128, 428, N/A, 248.

End of description.

The Advisor Mode section on the SQL Access Advisor: Recommended Options page is displayed. The text says: "The advisor can run in one of two modes, Limited or Comprehensive. Limited Mode is meant to return quickly after processing the statements with the highest cost, potentially ignoring statements with a cost below a certain threshold. Comprehensive Mode will perform an exhaustive analysis."

The Limited Mode option is selected. The Comprehensive Mode option is deselected.

End of description.

The screenshot shows the Recommendations section of the previous screenshot, but with the Show Recommendation Action Counts link in expanded form. Once expanded the link name changes to "Hide Recommendation Action Counts" and displays the following additional information:

Indexes: Create 1 Drop 0 Retain 0

Materialized Views: Create 4 Drop 0 Retain 0

Materialized View Logs: Create 6 Retain 0 Alter 0

Partitions: Tables 0 Indexes 0 Materialized Views 0

End of description.

An expanded Informational Findings navigation tree is shown. The following wait classes were not consuming significant database time: Commit, Concurrency, CPU, Network, User I/O. Also, session connect and disconnect calls and hard parsing were not consuming significant database time.

End of description.

The Quick Configuration: Review OLTP Threshold Settings page is shown.

The Cancel, Back, and Finish buttons are shown.

The OLTP Threshold Settings section contains a table.

The column names are Metric Name, AWR Baseline, Threshold Type, Warning Level, and Critical Level.

Row 1 has the following values: Average Active Sessions, SYSTEM_MOVING_WINDOW, Significance Level, Very High (.99), Extreme (.9999).

Row 2 has the following values: Redo Generate (per second), SYSTEM_MOVING_WINDOW, Percentage of Maximum, 100%, 120%.

Row 4 has the following values: Session Logical Reads (per transaction), SYSTEM_MOVING_WINDOW, Significance Level, Very High (.99), None.

The Impact on Existing Thresholds section is shown.

The table contains the following columns: Metric Name, AWR Baseline, Threshold Type, Warning Level, Critical Level.

Row 1 has the following values: "Cumulative Logons (per second)", null, Fixed Values, 100, null.

Row 2 has the following values: "Current Open Cursors Count", null, Fixed Values, 1200, null.

End of description

The Top Timed Events section of the AWR Compare Periods report is shown. Refer to the complete text description of the report.

End of description.

The Alert Log Errors page is shown.

The Alert Log Errors page is shown.

On the top right the View Data list shows All Days. To the right of the list is the Refresh button.

The Alert Log Entries Containing ORA-Errors section is shown.

The contains the right-justified following buttons: Show Open Alerts, Clear Every Open Alert, and Purge Every Alert.

The left-justified buttons Clear and Purge are shown.

The left-justified links Select All and Select None are shown.

A table contains the following columns: Select, Severity, Category, Time, Alert Log Error Stack, Alert Triggered, and Line Number.

The table contains 5 rows.

The values in row 1 are: (unchecked check box), (alert icon), Generic Alert Log Error, Feb 26 2007 4:44:39 AM, ORA-00600 internal error code, Feb 26 2007 4:50:34 AM, and 26728.

The values in row 2 are: (unchecked check box), check mark, Generic Alert Log Error, Feb 25 2007 10:34:35 PM, ORA-12012 error on auto execute of job 63855, null, and 26694.

The values in row 3 are: (unchecked check box), check mark, Generic Alert Log Error, Feb 25 2007 10:26:04 PM, ORA-12012 error on auto execute of job 63854, null, and 26687.

End of description.

The screenshot shows the Select Recommendations for Implementation table of the Recommendations subpage. Under the table title is the option Include Retain actions, which is selected. Below this option there are 3 buttons that are labeled, from left to right: Recommendation Details, Schedule Implementation, and Show SQL. Below the buttons are 2 links: Select All and Select None.

The table has 9 columns and 4 rows. The columns headings are, from left to right: Select, Implementation status, ID, Actions, Action Types, Cost Improvement, Cost Improvement (%), Estimated Space Used (MB), and Affected SQL Statements. The rows, listed in order from top to bottom, contain the following values, listed in order from left to right:

Selected, a blue square, 1 (hyperlinked), 4, three squares (blue, dark blue, and beige), 4, 40.00, 0.008, and 1

Not selected, a blue square, 3 (hyperlinked), 4, three squares (blue, dark blue, and beige), 2, 20.00, 0.008, and 1

Not selected, a blue square, 4 (hyperlinked), 3, three squares (blue, dark blue, and beige), 2, 20.00, 0.008, and 1

Not selected, a blue square, 2 (hyperlinked), 6, four squares (pale blue, blue, dark blue, and beige), 2, 20.00, 0.016, and 1

End of description.

A line chart labeled Average Active Sessions is shown. The units of the x-axis are times of the day, shown in 10-minute intervals. The x-coordinates shown range from 2:45 pm to 4:00 pm. The units of the y-axis are: active sessions. The y-coordinates shown range from 0.0 to 2.5. The following types of information are plotted in the chart: Other, Queueing, Network, Administrative, Configuration, Commit, Application, Concurrency, System I/O, User I/O, Scheduler, CPU Used. The chart indicates that spikes occurred from 2:45 to 3:15, and at 3:30. The largest spike was at about 3:10. The spikes were mainly caused by CPU usage, user I/O, and system I/O.

End of description.

The screenshot shows the Summary subpage of the Results for Tasks page. The other subtabs shown, but not selected, are Recommendations, SQL Statements, and Details.

The Summary subpage has 5 sections. At the top is Overall Workload Performance. In the middle is Workload I/O Cost on the left and Query Execution Time Improvement on the right. At the bottom are Recommendations on the left and SQL Statements on the right.

The Overall Workload Performance section has an information icon (the letter i in a chat bubble) followed by the words "Potential for Improvement"

The Workload I/O Cost section has a bar chart that compares the workload I/O cost for the Original Cost (877), indicated by a red bar, with the New Cost (867), indicated by a smaller blue bar. The vertical axis measures the workload I/O cost, with values from 865.0 to 877.5 in increments of 2.5

The Query Execution Time Improvement section also has a bar chart. The vertical axis is the percentage of statements, and has values from 0 to 100 incrementing by 25. The horizontal axis measures the Query Improvement Factor with values of 1x, 2x, 4x, 6x, 8x, 10x, and >10x. The legend contains only 2 entries: No Performance Improvement, indicated by the gray bars and Potential Performance Improvement, indicated by the blue bars. The graph indicates that 75% of the queries would see no improvement, as indicated by the large gray bar, No Performance Improvement, at 1x. Approximately 20 percent of the statements have a potential query execution time improvement of 4x, and 5 percent of the statements have a potential query execution time improvement of 6x, as indicated by the blue bars at 4x and 6x. There are no other bars on the chart.

The Recommendations section displays the following information:

Recommendations: 4 (hyperlinked)

Space Requirements (MB): 0.039

User Specified Space Adjustment: Unlimited

Show Recommendation Action Counts (hyperlinked and unexpanded)

The SQL Statements section displays the following information:

SQL Statements: 19 (hyperlinked), followed by the descriptive text "Statements remaining after filters were applied:

Show Statement Counts (hyperlinked and unexpanded)

End of description.

The Snapshot Range option is selected.

The Change Chart Time Period navigation tree is expanded.

The Chart Start Date is 2/5/09. Underneath the field is the Go button. The Chart End Date is 2/5/09.

The Select Time Period section is shown.

The Period Start Time is February 5, 2009 at 3:10 p.m. The Period End Time is February 5, 2009 at 3:30 p.m. This option is selected.

A line chart is shown. The y-axis is labeled Active Session and ranges from 0 to 2.232. The x-axis shows times of day, shown in 10-minute intervals, and ranges from 2:20 to 3:50. The chart tracks Wait, User I/O, and CPU. The CPU activity spikes to 2.232 at 2:30 p.m. and falls off to 0 at 2:50. It then gradually rises to 1.116 at 3:10.

End of description

The Automatic SQL Tuning Result Summary is shown.The text underneath the title is: "The Automatic SQL Tuning runs during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of high-load SQL statements."Task Status section.Automatic SQL Tuning (SYS_AUTO_SQL_TUNING_TASK) is currently Enabled.Automatic Implementation of SQL Profiles is currently Enabled. The Configure button is shown.The Task Activity Summary section is shown.The text underneath the title is: "The activity summary graph shows the benefit of the task activities on the systems high-load SQL. Only profiles that significant improve SQL performance were implemented."The Time Period list has All selected. The Go and View Report buttons are shown.The Begin Date is Mar 20, 2007 10:00:38 PM (UTC-07:00). The End Date is Mar 22, 2007 11:38:09 AM (UTC-07:00).

End of description.

This screenshot shows a section of the Edit Settings page.

For Snapshot Retention, select one of the following: Use Time-Based Retention Retention Period (Days), or Retain Forever. The first option has a corresponding text field, with the value 8 shown. For Snapshot Collection, select one of the following: System Snapshot Interval, or Turn off Snapshot Collection. The first option has a corresponding list, with the value 1 Hour shown.

1 label-value pair appears. The value for the Collection Level label is: Typical. The value is a link.

End of description.

The Plan subpage is shown.The page shows the following name-value pairs: Data Source is Cursor Cache, Capture Time is Feb 9 2009 12:07:07 PM, Parsing Schema is HR, Optimizer Mode is ALL_ROWS.In the View section, Table is selected.The table has the following columns: Operation, Object, Order, Rows, Bytes, Cost, CPU (%), Time, Query Block Name/Object Alias, Predicate, Filter, and Projection.Row 1 has the following fields: SELECT STATEMENT, null, 7, null, null, 7, null, null, null, null, null.Row 2 has the following fields: HASH JOIN, null, 6, 3, 189, 7, 14, 0:0:1, SEL$1, "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID", null, (#keys=1) "E"."LAST_NAME".

End of description.

The Alerts section on the Database Home page is shown.

The Category list shows All. To the right of the list is the Go button. To the right is the word "Critical," followed by a red "X" and the number 1. To the right is the word "Warning," followed by an exclamation point icon and the number 1.

The table contains the following columns: Severity, Category, Name, Impact, Message, and Alert Triggered.

The table contains 2 rows. Row 1 contains the following values: (red x icon), Incident, Generic Internal Error, PROCESS FAILURE, Internal error (3050924502) detected, and Feb 26 2007 4:45 AM.

Row 2 contains the following values: (exclamation point icon), Alert log, Generic Alert Log Error, null, ORA-error stack logged, and Feb 26 2007 1:50 AM.

End of description.

This screenshot shows the Top Working Sessions section of the Active Sessions Working page.

The View list shows the value Top Sessions.

Underneath the row of elements is a table. The table contains the following columns: Activity (%), Session ID, User Name, and Program. Values in the Session ID and User Name columns are displayed as links. Values in the Activity column are displayed as a horizontal bar chart with an associated number.

3 rows are shown in the table. The values of row 1 are: 96.42, 124, SH, and sqlplus@stbc09-1. The values of row 2 are: .98, 121, DBNSMP, OMS. The values of row 3 are: .33, 135, SYSMAN, OMS.

End of description.

This screenshot shows the Details tab of the Snapshot Details page.

6 label-value pairs appear. The value of label Beginning Snapshot ID is: 100. The value of the Beginning Snapshot Capture label is: Jan 30, 2009 2:49:35 PM. The value of the Ending Snapshot ID label is: 162. The value of the Ending Snapshot Capture label is: Jan 20, 2009 3:30:30 PM.

Underneath the preceding section is a table. The table contains the following columns: Name, Value, Per Second, and Per Transaction.

27 rows are shown in the table. The values of row 1 are: DB cpu (seconds), 0.00, 0,00, and 0.00. The values of row 2 are: DB time (seconds), 12,477.31, 5.08, and 20.42.

End of description.

The SQL Access Advisor: Initial Options page is shown.

The Cancel and Continue buttons are shown.

The text "Select a set of initial options" is shown.

The following options are possible: Verify use of access structures (indexes, materialized views, partitioning, etc.) only and Recommend new access structures. The following box is checked: Inherit Options from a previously saved Task or Template.

The Task and Templates section is shown.

The View list shows Templates Only.

The View Options button is shown.

The table has the following columns: Select, Name, Description, Last Modified, and Type.

Row 1 has the following values: (selected option), SQLACESS_EMTASK, "Default Enterprise Manager task templates", Mar 26 2007 1:28:21 PM PDT, Default Template.

Row 2 has the following values: (deselected option), SQLACCESS_GENERAL, "General purpose database template", Mar 26 2007 1:28:19 PM PDT, Template.

Row 3 has the following values: SQLACCESS_OLTP, "OLTP database template", Mar 26 2007 1:28:20 PM PDT, Template.

Row 4 has the following values: SQLACCESS_WAREHOUSE, "Data Warehouse database template", Mar 26 2007 1:20:21 PM PDT, Template.

End of description.

This graphic shows a horizontal arrow labeled User Response Time, with a point on the right end. In a horizontal row above the arrow are four boxes with the following contents: Query for Novels by Author, Browse Results of Query, Add Item to Cart, and Checkout. The first, third, and fourth boxes have a pair of bold arrows: one arrow points down from the box to the main arrow and the other points up. The second box in the series has two pairs of arrows. On the main arrow underneath each pair of up/down arrows, a thin shaded segment is shown. The shading indicates DB Time. The first, third, fourth, and fifth gray segments are labeled with the following times: 7:38, 7:42, 7:50, and 7:52.

End of description.

The Second Period section is shown.

Underneath the words "Second Period" are 2 name-value pairs. The Beginning Snapshot ID label has the value 18. The Beginning Snapshot Capture Time label has the value Feb 5 2009 5:00:03 PM.

The Go To Time field has the value 2/5/09. The adjacent list has the value 6:00 PM. Next to the list is a Go button.

Underneath is a table with the following columns: Select (values shown as selectable options), ID (values shown as links), Capture Time, Collection Level, and Within a Baseline. The table has 6 rows. Row 6 has the following values: (selected option), 24, Feb 5 2009 6:00:26 PM, TYPICAL, null.

End of description.

The Create SQL Tuning Set: Load Methods page is shown.

In the Data Source field, User-Defined Workload is selected. In the User-Defined Workload field, IMMCHAN.BAD_SQL is entered.

End of description.

The Disk Details view is shown.

On the top right of the screen the View Data list shows Real Time: Manual Refresh.

This view contains 3 sections: Disk I/O Utilization, Longest I/O Service Time (ms), and Top Disk Devices (ordered by % Busy).

The Disk I/O Utilization chart ranges from 0 to 20 on the y-axis and 9:36 to 1 AM on the x-axis. The chart measure Total I/Os per second. The chart shows a jagged line ranging from 8 to 11. The Total I/Os per second is 9.25.

The Longest I/O service time (ms) chart ranges from 0 to 15 on the y-axis and 11 to 1 AM on the x-axis. The chart measures Longest I/O service time (ms). The chart shows a jagged line ranging from 3 to 10. The Longest Service Time (ms) is .9.

The Top Disk Devices (ordered by % Busy) section includes a table. The columns of the table are: Status, Device, Busy (%), Average Outstanding Requests, Reads per second, Writes per second, Blocks Read (512 bytes per second), Blocks Written (512 bytes per second, Average Wait Time (ms), Average Service Time (ms).

The table contains 2 rows. Row 1 values are: (check mark icon), 0 C:, 2.44, .03, 3.33, 5.36, 80.31, 104.94, 0, 3.8. Row 2 values are: (check mark icon), 1 E:, 0, 0, C, 0, 0, 0, 0, 0.

End of description.

The Create Baseline: Repeating Baseline Template is shown.

On the top right are the Cancel, Back, and Finish buttons.

The Baseline Name Prefix field shows BASELINE_2009_MON_8.

In the Baseline Time Period section, the Start Time is 8 a.m. The Duration (Hours) field shows 2.

In the Frequency section, the mutually exclusive options are Daily and Weekly (selected). A row of options from Monday (selected) through Sunday is shown.

In the Interval of Baseline Creation section, the Start Time is Feb 6, 2009 at 8:00 a.m. The End Time is Feb 6, 2010 at 10:00 a.m.

In the Purge Policy section, the Retention Time (Days) field shows 30.

End of description.

The User page is shown.

The following options are possible: Include only SQL statements executed by these users (selected), and Exclude all SQL statements executed by these users.

To the right of the options is a rectangular text box containing the text "SH."

End of description.

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2012, Oracle and/or its affiliates. All rights reserved.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Alpha and Beta Draft Documentation Notice

If this document is in prerelease status:

This documentation is in prerelease status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.