Skip navigation elements to page contents
 
 
ENGLISH      CHINESE
OperationSystem   DB Server
Forms Server Reports Server   Discoverer Service OtherServices
 
Oracle E-Business Suites Performance Tuning

Database Performance Tuning General Standards and Policies

Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.
  1. Database Design (if it's not too late):
    Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.
  2. Application Tuning:
    Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
  3. Memory Tuning:
    Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.
  4. Disk I/O Tuning:
    Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.
  5. Eliminate Database Contention:
    Study database locks, latches and wait events carefully and eliminate where possible.
  6. Tune the Operating System:
    Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system .

For more information about Oracle database,please see the following lists:

 

Oracle9i Server Architecture Review

  • Responsibilities of a DBA
  • The Evolving Complexity of Systems
  • Increasing Size of Databases
  • Internet and eBusiness Systems
  • Data Availability
  • Increase in Data Complexity
  • Oracle9i Environments
  • Oracle8i Architecture (Review)
  • Oracle9i Architecture
  • The Oracle System Global Area
  • Oracle Background Processes
  • Storage Management
  • Getting Familiar With Your Server
  • Important Subdirectories in ORACLE_HOME
  • Shared Server Architecture
  • The Shared Server Architecture
  • Deprecated Parameters
  • Shared Server Parameters
  • The Oracle9i Java Environment
  • PL/SQL versus Java
  • Oracle's Object-Relational Database
  • Oracle Architectures
  • DBA Management Pack

    • Performance Tuning Info.

  • What Needs Tuning?
  • Who is Responsible for Tuning?
  • Monitoring a System
  • Understanding Different Types of Applications
  • Developing a Tuning Methodology
  • Areas of Focus
  • Oracle Performance Tuning Tools
  • Primary V$ Views used in Tuning
  • Oracle Diagnostics and Tuning Packs
  • Putting Together a Tuning Game Plan
  • Where to Look First
  • Setting BACKGROUND_DUMP_DEST for the Alert log
  • Managing User Trace Files
  • Important Initialization Parameters

    • Designing Efficient Data Storage

  • Database Storage
  • Database Block Size
  • Looking Inside an Oracle Data Block
  • Sizing the Database Block
  • Managing Storage
  • Traditional Storage Management with Freelists
  • Setting Physical Storage Characteristics
  • Setting PCTFREE and PCTUSED
  • How PCTUSED and PCTFREE are Used
  • Guidelines for PCTFREE and PCTUSED
  • Managing Storage
  • Generating Table Statistics
  • Generating Index Statistics
  • The High-Water Mark
  • Chaining and Migration
  • Determining the Number of Chained Rows
  • Finding Chained and Migrated Rows
  • Table Storage Statistics
  • Environmental Impacts on Design
  • The Physical Design
  • Optimal Flexible Architecture

    • Managing Large Tables

  • Oracle Size Ceilings (Review)
  • Managing Large Tables with Oracle
  • Oracle Data Storage
  • What is Partitioning?
  • Range-Partitioning
  • Partition Features
  • Advantages of Partitioning
  • Creating a Range-Partitioned Table
  • The Partition Key
  • Partition Syntax
  • Accessing a Partitioned Table
  • Data Dictionary Views
  • Using MAXVALUE
  • Partitioning Rules to Follow
  • Partition Extended Table Name Syntax
  • Restrictions on Partition Extended Name
  • Multi-Column Partition Keys
  • Inserting into a Multi-Column Partition Key
  • Modifying Partition Key Values
  • Setting Physical Attributes for Partitions
  • Hash Partitioning
  • Defining Tablespaces for Hash Partitions
  • Composite Partitioning
  • Creating a Composite-Partitioned Table
  • Subpartitioning
  • Getting Information about Composite Objects
  • List Partitioning in Oracle9i
  • Oracle9iR2 Enhancements to Partitioning
  • List Partitioning Default Partition
  • Composite Range-list Partitioning
  • Composite Partition Templates
  • Fast Split Partitioning
  • Parallel Updates and Deletes on Non-partitioned Tables
  • Additional Oracle9i Partitioning Features
  • Reduced Global Index Maintenance in 9i
  • Index-Organized Tables
  • Restrictions on Index-Organized Tables
  • Temporary Tables

    • Managing Large Indexes

  • Different Types of Indexes
  • GLOBAL Prefixed Indexes
  • Create a Global Prefixed Index
  • Local Prefixed Indexes
  • Create a Local Prefixed Index
  • Local Non-Prefixed Indexes
  • More on Local Indexes
  • Composite-Partitioned Indexes
  • Reverse Key Indexes
  • Improvements to Bitmap Indexes
  • Function-Based Indexes
  • Index Rebuilds ONLINE
  • Rebuilding Indexes Online
  • Additional Index Features
  • Bitmap Join Indexes
  • Multiple Bitmap Join Indexes - How They Work

    • Efficient Segment Management

  • Automatic Segment-Space Management
  • Creating Tablespaces Using Automatic Segment-Space Management
  • Space Management
  • Bitmapped Managed Blocks
  • Creating Objects in Automatic Segment-Space Managed Tablespaces
  • Space Deallocation With Automatic Segment-Space Management
  • The DBMS_SPACE Package
  • The SPACE_USAGE Parameters
  • Example using SPACE_USAGE
  • Space Deallocation Statements
  • Viewing Space Usage Information
  • Data Segment Compression
  • Data Segment Compression with Row

    • Movement

    Tuning The Standard Buffer Cache

  • The Database Buffer Cache
  • The LRU List and Checkpoint Queue
  • Server Processes and the Buffer Cache
  • DBWR and the Buffer Cache
  • DBWR Events
  • Buffer Cache Hit Ratios
  • Determining the Cache Hit Ratio
  • Calculating the Data Buffer Cache Hit Ratio
  • LRU Latches (Oracle8)
  • Multiple DB Cache Buffer Pools (Oracle8)
  • Defining the DB Buffer Pools (Oracle8)
  • Buffer Pool Initialization Parameters (Oracle8)
  • The Buffer Pool Parameter
  • Defining Storage for Buffer Pools
  • Calculating the Hit Ratio for Buffer Pools
  • Calculating Cache Usage of Schema Objects
  • Calculating Buffer Pool Sizes
  • Buffer Pool Considerations
  • Caching Tables

    • Tuning the SGA, PGA and DB Caches

  • Dynamic Memory Management
  • Tuning the SQL Work Areas
  • Setting the PGA Target Memory Size
  • Defining Automatic PGA Memory Management
  • Setting up SQL Work Areas
  • Displaying Workarea Information
  • Using a PGA Workarea
  • PGA Aggregate Target Advisory
  • Dynamic SGA
  • Granules
  • Multiple Database Buffer Caches
  • Database Buffer Cache Parameters
  • V$BUFFER_POOL
  • Changing the Database Buffer Cache
  • Deprecated Parameters
  • Advisory Parameter
  • V$DB_CACHE_ADVICE

    • Tuning the Shared and Large Pool Areas

  • Memory Pool Areas
  • The Shared Pool Area
  • Tuning the Dictionary Cache
  • Configuring The Program Global Area
  • PGA Memory
  • Evaluating the Size of the UGA
  • Library Cache
  • Sizing the Shared Pool Area
  • Evaluating Library Cache Utilization
  • Changing the Shared Pool Size
  • Shared Pool Advisory Statistics
  • Pinning Large Objects
  • Monitoring Library Cache Reloads
  • Evaluating Statements in the Library Cache
  • Checking for SQL Statement Contention
  • Evaluating Sharable Memory
  • Sharing Cursors
  • Determining Cursor Memory
  • Shared Pool Advisory Statistics
  • Performance Tuning EM Enhancements
  • The Large Pool Area
  • Setting the Large Pool
  • Tuning Parallel Query
  • Parallel DML
  • Parallel DML Restrictions

    • Tuning the Redo Log Cache

  • The Redo Log Buffer
  • Redo log Cache I/O
  • Evaluating the Size of the Redo Log Buffer
  • Tuning the Redo Log Buffer Cache
  • Optimizing the Redo Log Buffer Cache
  • Tuning with V$SESSION_WAIT
  • Evaluating Waits using V$SYSSTAT
  • Redo Log Waits due to Log Switches
  • Batch Operations and the Redo Log Buffer

    • Tuning SQL Statements

  • SQL Tuning
  • The Rule-Based Optimizer
  • The Death of the Rule-Based Optimizer
  • The Cost-Based Optimizer
  • Generating Statistics
  • Using DBMS_STATS
  • DBMS_STATS Routines
  • The ANALYZE Command
  • Rule-Based versus Cost-Based Optimization
  • Setting the Optimizer Mode
  • EXPLAIN PLAN
  • Monitoring Execution Plans
  • The V$SQL View
  • V$SQL_PLAN
  • The PLAN_TABLE Columns
  • AUTOTRACE
  • SQL Trace and TKPROF
  • Setting up SQL Trace
  • Running TKPROF
  • TKPROF Data

    • Optimizer Features

  • Monitoring Tables
  • Index Monitoring
  • The V$OBJECT_USAGE View
  • Skip
  • Cursors (Review)
  • Cursor Sharing (Review)
  • The CURSOR_SHARING Parameter
  • Setting CURSOR_SHARING To SIMILAR
  • FIRST_ROWS Optimization
  • Gathering Statistic Estimates with DBMS_STATS
  • Query Rewrite Enhancements
  • Dynamic Sampling of Optimizer Statistics
  • Statistics Collection Level
  • Runtime Row Source Statistics

    • Tuning Undo and Rollback Segments

  • Automatic Undo Management
  • Tuning Undo Segments
  • Tuning Undo versus Rollback Segments
  • Automatic Undo Initialization Parameters
  • V$UNDOSTAT
  • Undo Tuning Data Dictionary Views
  • Rollback Segments
  • Typical Layout for Rollback Segments
  • Rollbacks and Long Transactions
  • Sizing Rollback Segments
  • CREATE and ALTER Rollback Segments
  • Creating Rollback Segments
  • Using the SET TRANSACTION Command
  • Data Dictionary Views for Rollback Segments
  • Rollback Segment Rules and Guidelines
  • Rollback Segment Usage
  • Sizing Rollback Segments for Transactions

    • Tuning Sort Operations

  • Managing Sort Operations
  • Sort Operations
  • Tuning Sorts
  • The SORT_AREA_SIZE Parameter
  • Allocating Sort Memory Space
  • The SORT_AREA_RETAINED_SIZE
  • Memory versus Disk Sorts
  • Temporary Tablespaces
  • CREATE TEMPORARY TABLESPACE Command
  • Evaluating Current Sort Storage

    • Configuring Database I/O

  • Physical Design Guidelines
  • I/O Tuning Goals
  • Tuning Full Table Scans
  • Determining Disk I/O Distribution
  • Reducing Fragmentation
  • MTTR and Checkpoint intervals
  • Methods for Controlling Check pointing
  • V$INSTANCE_RECOVERY View
  • Minimal I/O Recovery
  • Managing Checkpoint Intervals
  • Database Writer I/Os

    • Additional Tuning Considerations

  • Query Rewrite Enhancements
  • Tuning the High Impact Init.ora Parameters
  • Initialization Parameter Guidelines
  • Data Dictionary Views
  • The V$ Views
  • Undocumented Initialization Parameters
  • Tuning Users and Applications
  • Tuning Bottlenecks
  • Tools for Tuning Bottlenecks
  • Tuning for Specific Time Frames
  • Running UTLBSTAT and UTLESTAT
  • UTLBSTAT and UTLESTAT Statistics
  • Report.txt
  • Guidelines to Follow
  • One Step at a Time
  • Additional Features
  • The Oracle Event Management System
  • Common Tuning Issues
  • Tuning for Waits and Contention
  • System Event 10046
  • Wait Event Views
  • Ratio Tuning versus Wait Tuning
  • Unix Tuning Tools
  • Windows Tuning Tools

    • Statspack

  • What is Statspack?
  • Statspack Scripts
  • Installing Statspack
  • Setting
  • Database Space Requirements
  • Installing Statspack
  • Statspack Parameter Settings
  • Using Snapshots
  • Generating Statspack Snapshots
  • Exporting Statspack Data
  • UTLBSTAT / UTLESTAT and STATSPACK
  • Statistics Gathering
  • Scheduling Statspack Jobs
  • Removing Statspack
  • The Report
  • Looking at a Statspack Report

    •   Top↑
    Home Contact Us If you have any questions ,please feel free and contact us! Tel: 0086-755-81078317 E-mail: support@dba-oracle.org
    Copyright 2003 2004 Oracle Support Experts All rights reserved IE 1024*768