Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Release 9.0.1

Part Number A86647-01

Library

Product

Index

Go to next page

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Getting Started with the Oracle Tuning Pack

1 Introduction to the Oracle Tuning Pack

Overview of the Tuning Pack Applications
What's New in Tuning Pack for 9i
Oracle SQL Analyze
Oracle Expert
Oracle Index Tuning Wizard
Reorg Wizard
Tablespace Map
Outline Management
Outline Editor
Choosing the Right Application
Starting the Tuning Pack Products
Using Help

2 Configuring the Oracle Tuning Pack

Configuring SQL Analyze

Part II Getting Started with Oracle SQL Analyze

3 Introduction to Oracle SQL Analyze

Introduction
Benefits of Oracle SQL Analyze
What's New in Oracle SQL Analyze 9.0.1
SQL Tuning as Part of an Overall Tuning Process
SQL Tuning Issues
Methods of SQL Analysis and Tuning
Analyzing Explain Plans
Controlling Optimizer Modes
Adding Hints
Applying Rules-of-Thumb
Analyzing Object Properties
The SQL Tuning Process
Step One: Starting a Tuning Session
Step Two: Gathering Information
Step Three: Tuning the Statement
Step Four: Verifying Your Results

4 Starting a Tuning Session

Starting a Tuning Session
Assigning the SQLADMIN Role
Creating and Working with Tuning Sessions
Oracle SQL Analyze Repository
Oracle SQL Analyze Main Window
The Navigator Pane
SQL Text Pane
Details Pane
Selecting a Statement for Tuning
Selecting Statements with TopSQL
Entering a New Statement
Importing Statements From a SQL File
Opening a Previously Used Tuning Session
Printing
Saving

5 Gathering and Analyzing Information

Understanding Statistical Information
Analyzing the Database Environment
Instance Based Parameters
Session Based Parameters
Analyzing Logical Structure
Viewing Object Properties
Table Properties
Cluster Properties
Index Properties
Examining Views
Understanding the Oracle Optimizer
Cost-Based and Rule-Based Optimization
Understanding Performance Statistics
TopSQL Statistics
Using SQL History
Understanding Explain Plans
Viewing Execution Statistics
Comparing Execution Statistics
Comparing SQL Statements and Explain Plans

6 Tuning SQL Statements

Tuning SQL Statements
Editing Statements Manually
Understanding Index Tuning Recommendations
Understanding Hints
Understanding Rules-of-Thumb
Use TRUNC differently to enable indexes
Using the SQL Tuning Wizard
The SQL Tuning Wizard Process
Using the Hint Wizard

7 Verifying Performance

How to Verify SQL Performance Improvement

Part III Getting Started with Graphical Explain Plans

8 SQL Explain Plan

Stepping Through the SQL Explain Plan

Part IV Getting Started with Oracle Expert

9 Introduction to Oracle Expert

Advantages of Using Oracle Expert
What Is Database Tuning?
Database Tuning Issues
Resolving Tuning Issues
What Are the Types of Performance Tuning?
Ways to Use Oracle Expert
Sample Tuning Session

10 Oracle Expert Methodology

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

11 Getting Started with Oracle Expert

Starting Oracle Expert
Oracle Expert Main Window
Identifying the Database to Tune
Creating a SQL History

12 Creating and Working with Tuning Sessions

Creating a Tuning Session
Creating a Tuning Session Using the Tuning Session Wizard
Creating a Tuning Session Manually
Setting the Scope of a Tuning Session
Instance Optimizations
SQL Reuse Opportunities
Space Management
Optimal Data Access
Selecting Values for Tuning Session Characteristics
Opening an Existing Tuning Session
Modifying a Tuning Session
Deleting a Tuning Session
Saving Tuning Session Data to a Log File

13 Collecting the Data

Overview of Data Collection
Collecting the Collection Classes
How Oracle Expert Collects Class Data
Specifying the Class Data to Collect
Collecting Data Efficiently
Collecting the Database Class
Collecting Database Class Data from an Instance
Collecting Database Class Data from a File
Re-Collecting Database Class Data
Collecting the Instance Class
Collecting Instance Class Data from One or More Instances
Collecting Instance Class Data from a File
Getting Less Conservative Instance Tuning Recommendations
Re-Collecting Instance Class Data
Collecting the Schema Class
Collecting Schema Class Data from One or More Instances
Collecting Schema Class Data from a File
Re-Collecting Schema Class Data
Collecting the System Class
Entering System Class Data Manually
Collecting System Class Data from a File
Re-Collecting the System Class
Collecting the Workload Class
Workload Options
Collecting Workload Class Data from a Database's SQL Cache
Collecting Workload Class Data from a SQL History
Collecting Workload Class Data from a File
Collecting Workload Class Data from an Oracle Trace Database
Re-Collecting Workload Class Data
Collecting Workload Class Data Manually
Starting a Collection
Restrictions During a Collection
Canceling a Collection
Collecting Invalid Data

14 Viewing and Editing the Collected Data

Review Page
Databases
Environment
Workload Applications

15 Generating and Reviewing Recommendations

Generating Tuning Recommendations
Analyzing Data Efficiently
Tunable Rule
Deleting an Object
Restrictions During an Analysis
Canceling an Analysis
Invalid Objects Discovered During an Analysis
Invalidating an Analysis
Reviewing Tuning Recommendations

16 Implementing the Recommendations

Implementing Tuning Recommendations
How to Use the Implementation Files

17 Generating Reports

Generating an Analysis Report
Generating a Recommendation Summary Report
Generating a Session Data Report
Generating a Cross Reference Report

18 Using Oracle Expert Effectively

Defining the Appropriate Tuning Scope
Providing Complete and Accurate Data
Using Iterative Tuning for Improved Performance
Taking Advantage of Rules
Using Existing Analysis Statistics for Very Large Tables
Index Rebuild Detection Requires Analysis Statistics
Use SQL History to Avoid Extra SQL Collections

19 Initial Configuration

Advantages of Using Oracle Expert for Initial Configuration
Configuring a New Database
Performing Initial Configuration for a Database
Improving the Initial Configuration of the Database
User-Provided Information

20 Autotune

Starting Autotune
Stopping Autotune
Viewing Autotune Recommendations
Implementing Autotune Recommendations

21 Managing Workloads

Database Workloads
Collecting Workload Information with Oracle Trace
Collecting Workload Information from the SQL Cache
Collecting Workload Information from an .XDL File
Collecting Workload Information from the SQL History
Specifying Importance Values

Part V Getting Started with Oracle Index Tuning Wizard

22 Introduction to Oracle Index Tuning Wizard

When to Use the Index Tuning Wizard
Accessing the Index Tuning Wizard
Index Tuning Wizard Interface
Application Type
Schema Selection
Index Recommendations
Analysis Report and Script
Finish

Part VI Getting Started with the Reorg Wizard

23 Introduction to the Reorg Wizard

When to use the Reorg Wizard
Accessing the Reorg Wizard
Reorganizing an Entire Tablespace
Reorganizing a Specific Schema Object
Reorganization Process

Part VII Getting Started with the Tablespace Map

24 Introduction to the Tablespace Map

Accessing the Tablespace Map
Viewing the Tablespace Map
Analyzing Segments in a Tablespace
Generating a Tablespace Analysis Report
Viewing Tablespace Analysis Results
Launching Reorg Wizard to Correct Space Problems

Part VIII Getting Started with Stored Outlines

25 Introduction to Outline Management

Accessing the Outline Management Tool
Using the Outline Management Tool

26 Introduction to the Outline Editor

Accessing the Outline Editor
Creating an Outline
Using Categories
Using Bind Variables
Editing an Outline
Changing the Join Order
Changing the Join Method
Changing the Access Method

Glossary

Index


Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.

Library

Product

Index