oracle11g自带优化吗,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"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25542870/viewspace-2134459/，如需转载，请注明出处，否则将追究法律责任。