Performance Tuning Topics Index

General:

Oracle Instance Tuning

Tkprof & SQLTrace

SQL Hints

AWR report Interpretation

Full Table scan & Index scan

What's New in Oracle Performance?

Part 1: Performance Planning

Steps in The Oracle Performance Improvement Method

A Sample Decision Process for Performance Conceptual Modeling

Top Ten Mistakes Found in Oracle Systems

Part 2: Optimizing  Instance Performance

Configuring a Database for Performance

Important Initialization Parameters With Performance Impact

Configuring Undo Space

Sizing Redo Log Files

Creating Subsequent Tablespaces

Creating Permanent Tablespaces - Automatic Segment-Space Management

Creating Temporary Tablespaces

Creating and Maintaining Tables for Good Performance

Table Compression

Reclaiming Unused Space

Indexing Data

Specifying Memory for Sorting Data

Performance Considerations for Shared Servers

Identifying Contention Using the Dispatcher-Specific Views

Reducing Contention for Dispatcher Processes

Identifying Contention for Shared Servers

Automatic Performance Statistics

AWR

ASH Reports (active session history)

Automatic Performance Diagnostics

ADDM

Memory Configuration and Use

ASMM,

Buffer pool Hit ratios,

keep pool,

recycle pool,

cursor_space_for_time,

cursor_sharing,

sizing the shared pool,

using large pool

configuring automatic pga memory,

sizing the log buffer,

sizing the buffer cache

I/O Configuration and Design

OMF

choosing data block size

Understanding Operating System Resources

Finding System CPU Utilization

1 Checking Memory Management

2 Checking I/O Management

3 Checking Network Management

4 Checking Process Management

Instance Tuning Using Performance Views

Instance Tuning Steps

Interpreting Oracle Statistics

Wait Events Statistics  --- buffer busy waits ,db file sequential read ,library cache lock

Idle Wait Events

Part 3: Optimizing SQL Statements

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/part4.htm#g996847

Chapter 11, "SQL Tuning Overview"

Chapter 12, "Automatic SQL Tuning"

Chapter 13, "The Query Optimizer"

Chapter 14, "Managing Optimizer Statistics"

Chapter 15, "Using Indexes and Clusters"

Chapter 16, "Using Optimizer Hints"

Chapter 17, "SQL Access Advisor"

Chapter 18, "Using Plan Stability"

Chapter 19, "Using EXPLAIN PLAN"

Chapter 20, "Using Application Tracing Tools"

11

Identifying High-Load SQL

Automatic SQL Tuning Features

Developing Efficient SQL Statements

12

SQL Tuning Advisor

SQL Tuning Sets

SQL Profiles

13  The Query Optimizer

Optimizer Operations

Choosing an Optimizer Goal

Enabling and Controlling Query Optimizer Features

Understanding the Query Optimizer

Understanding Access Paths for the Query Optimizer

Understanding Joins

14 Managing Optimizer Statistics

Understanding Statistics

Automatic Statistics Gathering - GATHER_STATS_JOB

Manual Statistics Gathering -  DBMS_STATS Procedures

System Statistics

Managing Statistics

Viewing Statistics

15 Using Indexes and Clusters

15.2 Using Function-based Indexes for Performance

15.3 Using Partitioned Indexes for Performance

15.4 Using Index-Organized Tables for Performance

15.5 Using Bitmap Indexes for Performance

15.6 Using Bitmap Join Indexes for Performance

15.7 Using Domain Indexes for Performance

15.8 Using Clusters for Performance

15.9 Using Hash Clusters for Performance

16 Using Optimizer Hints

Understanding Optimizer Hints

Specifying Hints

Using Hints with Views

17 SQL Access Advisor

Overview of the SQL Access Advisor in the DBMS_ADVISOR Package

Steps for Using the SQL Access Advisor

Tuning Materialized Views for Fast Refresh and Query Rewrite -------DBMS_ADVISOR.TUNE_MVIEW Procedure

18 Using Plan Stability

 ----------Storing Outlines

-------Using Plan Stability with Query Optimizer Upgrades ----Moving from RBO to the Query Optimizer

19 Using EXPLAIN PLAN

 -------EXPLAIN PLAN Restrictions

------The PLAN_TABLE Output Table

20 Using Application Tracing Tools

--Using the trcsess Utility

--Using the SQL Trace Facility and TKPROF