1 Introduction

About This Guide

Before using this guide, you need to 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 this type of information, see Oracle Database Concepts.

This guide does not describe basic Oracle Database administrative tasks. For this 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 and 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

In a small-to-midsize database environment, you might be the sole person performing these tasks. In large, enterprise environments, the job is often divided among several DBAs—each with his or her own specialty—such as database security or database tuning. Oracle Database 2 Day + Performance Tuning Guide describes how to accomplish the last two tasks in this 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 will need to 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 your 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 Oracle Enterprise Manager to manage your 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 that are used in this guide include Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository (AWR).

• 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 two key features: Database Replay and SQL Performance Analyzer. Database Replay 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 enables you to assess the effect of system changes on SQL performance by identifying SQL statements that have regressed, improved, or remained unchanged.