Chapter 01-Overview of Oracle 9i Database Perfomrmance Tuning

1、What Does the DBA Tune?

Performance tuning areas

• Application:(Shared with developers)
  • SQL statement performance
  • Change management
• Instance tuning:
  • Mermory
  • Database structure
  • Instance configuration
• Operating system interactions:(Shared with SA)
  • I/O
  • Swap
  • Parameters

2、Tuning Questions

• Who tunes?
  • Application designers
  • Application developers
  • Database administroators
  • System adminstrators
• What to tune?
• How much tuning is required?
• Response Time
• • inreaseing throughput 吞吐量高（尽可能减少Oracle对磁盘的操作，尽可能在内存中读写数据）
  • 提高缓存的命中率

3、Tuning Phases

SLA（Servie Level Agreement）

调优的时候，定义个量化的东西；按照这个量化的东西来展开调优；

Tuning can be divided into different phases:

• Application design and programming
• Database configuration
• Adding a new application to an existing database
• Troubleshooting and tuning

4、Common Performance Problems

Bad session management:

　　Limits saclability to point that cannot be exceeded

　　Makes the system one or two orders of magnitude slower than it should be　　

Bad cursor management:　　

Bad relational design:

　　Unnecessary table joins performed

　　Usually a result of trying to build an object interface to relational storage

5、Tuning Steps In Development

　　Tune the design

　　Tune the apppliation

　　Tune the memory

　　Tune I/O

　　Tune connection

　　Tune the operating system

6、Baseline of Statistics

A baseline set of statistics is used to

Provide a set of statistics that are collected when the system was operating within the bounds set 

Create a hypothesis about what has changed on the system

7、Steps for a Production DB

Define the problem

• Examine the host system and Oracle statistics
• Consider some common performance errors
• Build a conceptual mode
• Implement and measure the change
• Check that the bottleneck has been resolved

8、Tuning Methodology

• Check alert log and trace files for errors
• Chekc the parameter file for any diagnostic or inappropriate parameter setting.
• Check memory,I/O,and CPU usage.Indentify processes with resource usage anomalies.
• Identify and tune SQL statements that are heavy consumers of CPU or I/O.
• Tuning response time:
  • Analyze system performance in terms of work done(CPU or service time)versus time spent waiting for work(wait time)
  • Determine which component consumes the greatest amount of time.
  • Drill down to tune taht component ,if appropriate.

9、Trade-Offs of Performance & Safty

Factors that affect performances

• Multiple control files
• Multiple redo log members in a group
• Frequnet checkpointing
• Backing up data files
• Performing archiving
• Block check numbers
• Number of concurrent users and transactions

10、Summary

In this lesson,you should have learned how to:

• Create a good inital design
• Define a tuning methodology
• Perform production tuning
• Establish quantifiable goals
• List tuning problems
• Decide between performance and safety