老李分享： Oracle Performance Tuning Overview 翻译

 

1 性能优化概述

This chapter provides an introduction toperformance tuning and contains the following sections:

本章节给出了性能优化的简介，它包含了一下几个部分。

1.1 性能优化简介

This guide provides information on tuning anOracle Database system for performance. Topics discussed in this guide include:

这个小节提供了对一个oracle数据库系统进行性能优化的知识。包括一下几个主题

1.1.1性能规划

Before starting on the instance or SQL tuningsections of this guide, make sure you have read Part II, "Performance Planning".

在学习本小节的实例和sql优化部分之前，请先阅读II部分：性能规划

Based on years of designing and performanceexperience, Oracle has designed a performance methodology. This brief sectionexplains clear and simple activities that can dramatically improve systemperformance. It discusses the following topics:

基于多年的设计和性能方面的经验，oracle已经设计了一套关于性能方面的方法体系。本节描述了能够显著提高系统性能的清晰和简单的方案。该方案涉及到一下主题:

1.1.2实例优化

Part III, "Optimizing Instance Performance" ofthis guide discusses the factors involved in the tuning and optimizing of anOracle database instance.

本节的III部分，“优化实例性能”讨论涉及到oracle数据库实例调优和优化的各种因素。

When considering instance tuning, care mustbe taken in the initial design of the database system to avoid bottlenecks thatcould lead to performance problems. In addition, you need to consider:

当考虑实例优化的时候，数据库系统初始化设计必须要注意防止一些瓶颈，这些瓶颈可能会带来性能方面的问题。此外你需要考虑一下几点：

  • Allocating memory to database structures

  • 给数据库分配内存

  • Determining I/O requirements of different parts of the database

  • 确定数据库的不同部分的I/O需求

  • Tuning the operating system for optimal performance of the database

  • 调优操作系统使数据库系能达到最佳

After the database instance has beeninstalled and configured, you need to monitor the database as it is running tocheck for performance-related problems.

在安装和配置数据库实例之后，你需要监控数据库，因为它时刻运行着来检查跟性能相关的一些问题。

1.1.2.1性能准则

Performance tuning requires a different,although related, method to the initial configuration of a system. Configuringa system involves allocating resources in an ordered manner so that the initialsystem configuration is functional.

对于系统的初始化配置来说 性能调优需要一种与之不同的尽管相关的方法。要以有序的方式分配资源来配置一个系统，这样系统的初始化配置才能起作用。

Tuning is driven by identifying the mostsignificant bottleneck and making the appropriate changes to reduce oreliminate the effect of that bottleneck. Usually, tuning is performedreactively, either while the system is preproduction or after it is live.

通过找到最重要的瓶颈且做一些合适的变更来减少或消除瓶颈的影响来驱动调优的进行。通常来说，调优是需要有反应来验证的，这个系统要么是在试运行中，要么是已经在生产运行了。

1.1.2.2基线

The most effective way to tune is to have anestablished performance baseline that can be used for comparison if aperformance issue arises. Most database administrators (DBAs) know their systemwell and can easily identify peak usage periods. For example, the peak periodscould be between 10.00am and 12.00pm and also between 1.30pm and 3.00pm. Thiscould include a batch window of 12.00am midnight to 6am.

最有效的调优方式是建立一个性能的基准线，它能够被用来在性能问题出现的时候做比较。大部分的数据库管理员对他们的系统熟知而且能轻易的判断出系统使用的高峰时段。例如：高峰时段可能是上午10：00到下午12:00，也可能是

下午1.30到3.00.这可能包括凌晨12:00到6:00的批处理窗口。

It is important to identify these peakperiods at the site and install a monitoring tool that gathers performance datafor those high-load times. Optimally, data gathering should be configured fromwhen the application is in its initial trial phase during the QA cycle.Otherwise, this should be configured when the system is first in production.

找到这些这些高峰时段和安装一个能够收集高负载时段性能数据的监控工具都是非常重要的。理想情况下，数据采集应该是从应用的质量保证生命周期中的初始化试验阶段开始配置。否则，应该在系统首次应用于生产时配置。

Ideally, baseline data gathered shouldinclude the following:

理想情况下，基准线数据应该包括以下内容：

  • Application statistics (transaction volumes, response time)

  • 应用程序统计信息（交易量，响应时间）

  • Database statistics

  • 数据库统计信息

  • Operating system statistics

  • 操作系统统计信息

  • Disk I/O statistics

  • 磁盘 输入输出 统计信息

  • Network statistics

  • 网络统计信息

In the Automatic Workload Repository,baselines are identified by a range of snapshots that are preserved for futurecomparisons. See "Overview of the Automatic Workload Repository".

在自动工作量知识库里面，基线由一系列的快照形成，这些快照会被保存以便日后比较。祥见“自动工作负载库概述”。

1.1.2.3症状和问题

A common pitfall in performance tuning is tomistake the symptoms of a problem for the actual problem itself. It isimportant to recognize that many performance statistics indicate the symptoms,and that identifying the symptom is not sufficient data to implement a remedy.For example:

性能调优的一个常见陷阱是错误的把问题的症状（表现）当成是问题的实质了。认识到大量性能统计信息预示着问题的症状是很重要的。当然，同时认识到症状并不能给问题补救提供充分的数据也非常重要。

  • Slow physical I/O

  • 缓慢的物理输入输出

Generally, this is caused bypoorly-configured disks. However, it could also be caused by a significantamount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.

通常情况下，这是由较次配置的磁盘引起的。然而他也可能由大量的不必要的磁盘物理输入输出引起，而这些输入输出是由垃圾sql导致。

  • Latch contention

  • 闩锁争用

Rarely is latch contention tunable byreconfiguring the instance. Rather, latch contention usually is resolvedthrough application changes.

通过重新配置实例来实现闩锁争用问题可调是很少的，相反闩锁争用通常通过改变应用程序来解决。

  • Excessive CPU usage

  • CPU使用率过高

Excessive CPU usage usually means that thereis little idle CPU on the system. This could be caused by an inadequately-sizedsystem, by untuned SQL statements, or by inefficient application programs.

过高的cpu使用率通常意味着系统会有很少空闲的cpu。这可能由系统规模不适当 或未经优化的sql 或者 低效率的应用程序导致。

1.1.2.4何时调优

There are two distinct types of tuning:

两种不同方式的调优