ORACLE11G 效能調優學習

Oracle? Database 2 Day + Performance Tuning Guide
11g Release 2 (11.2)
E10822-04

================================================================================
重點需要掌握的工具
ADDM
ASH
ASSM


Part I
一：調優工具
1、Oracle Database 11g Enterprise Edition
2、Oracle Enterprise Manager
3、Oracle Diagnostics Pack
4、Oracle Diagnostics Pack
4.1、SQL Tuning Advisor
4.2、SQL Access Advisor
5、Oracle Real Application Testing
5.1、Database Replay
5.2、SQL Performance Analyzer

二：Oracle Database Performance Method

1、Gathering Database Statistics Using the Automatic Workload Repository


和AWR相關的兩個初始化引數:
?STATISTICS_LEVEL
?CONTROL_MANAGEMENT_PACK_ACCESS


The database statistics collected and processed by AWR include:

?Time Model Statistics
最佳化目標：減少database time，提高應用程式的響應時間
?Wait Event Statistics
?Session and System Statistics
?Active Session History Statistics
?High-Load SQL Statistics


2、Using the Oracle Performance Method

Database tuning is performed in two phases: proactively and reactively.

Applying the Oracle performance method involves the following:
?Performing pre-tuning preparations, as described in "Preparing the Database for Tuning"
?Tuning the database proactively on a regular basis, as described in "Tuning the Database Proactively"
?Tuning the database reactively when performance problems are reported by the users, as described in "Tuning the Database Reactively"
?Identifying, tuning, and optimizing high-load SQL statements, as described in "Tuning SQL Statements"
To improve database performance, you must apply these principles iteratively.

常用的工具
ADDM
ASH


3、Common Performance Problems Found in Oracle Databases

常見問題：
?CPU bottlenecks
?Undersized memory structures
?I/O capacity issues
?Suboptimal use of Oracle Database by the application
?Concurrency issues
?Database configuration issues
?Short-lived performance problems
?Degradation of database performance over time
?Inefficient or high-load SQL statements
?Object contention
?Unexpected performance regression after tuning SQL statements



==============================================================================================

Part II
Proactive Database TuningPart II describes how to tune Oracle Database proactively on a regular basis and contains the following chapters:
?Chapter 3, "Automatic Database Performance Monitoring"
?Chapter 4, "Monitoring Real-Time Database Performance"
?Chapter 5, "Monitoring Performance Alerts"

三 Automatic Database Performance Monitoring

Automatic Database Diagnostic Monitor (ADDM)
檢視ADDM報告應作為每天的日常工作

spfile引數：
CONTROL_MANAGEMENT_PACK_ACCESS建議設定為DIAGNOSTIC+TUNING (default) or DIAGNOSTIC；如設定為NONE則將關閉ADDM等特性；
STATISTICS_LEVEL設定為TYPICAL (default) or ALL；如設定為BASIC則將關閉ADDM等新特性；

DBIO_EXPECTED   隨機讀單獨一個資料塊的微妙時間
可透過自帶語句評測出該時間；

AWR

=======================================================================================================
Part III
Reactive Database TuningPart III describes how to tune Oracle Database in response to a reported problem, such as when the user reports a performance problem with the database that must be tuned immediately.

This part contains the following chapters:

?Chapter 6, "Manual Database Performance Monitoring"

?Chapter 7, "Resolving Transient Performance Problems"

?Chapter 8, "Resolving Performance Degradation Over Time"