我非常喜欢oracle的联机文档，写的非常的简单明了，又非常的深入，深远！

也许有人不同意这个观点，那我们上高中大学时学过类似于反向证明，那请不同意的朋友看看其他的DB2,MYSQL之类的联机文档，然后再回来看oracle的文档吧！

前段时间一直处于考试的状态，没有时间做其他事情。发现不少人对英文是非常非常的反感，所以想做一点点翻译，供参考。同时也让自己再学一遍！如果有翻译错误，是有道词典的问题！

目前没想到有什么好的方法来翻译，就将就着写在联机文档原文的后面吧。

相信有人可能刚开始学习，不知道怎么下载联机文档，我把放到下面：下载那个e11882-01.zip文档，解压开以后，把里面的除html，pdf以外都删除掉，剩余大约1G，当然把windows也删除掉，永远不要提在windows下使用oracle。

http://www.oracle.com/technetwork/database/enterprise-edition/documentation/index.html

注：本人语文水平很差，写作能力与小学生无太大区别，发现问题请指明。不过如果能认真的看上3篇英文，基本上不用再看翻译的狗屁中文了，专业英文都很简单的。

1 Performance Tuning Overview

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

本节将从以下两个方面提供一些性能调优方面的知识：介绍调优；调优特点，调优工具

注：oracle的所有文档都是这种方式：前面是总是概括本文要讲什么东西，是一个大的概念，随后根据下面的几个小题不断的展开，所以许多时候看开关这几个小题就可以大概的了解到本文是否是自己想要了解和查找的

Introduction to Performance Tuning
Introduction to Performance Tuning Features and Tools

1.1 Introduction to Performance Tuning

This guide provides information about tuning Oracle Database for performance. Topics discussed in this guide include:

本文提供了一个关于oracle性能调优的参考手册。主要包括如下三方面：性能调优的计划；实例调优；SQL语句调优

Performance Planning
Instance Tuning
SQL Tuning

1.1.1 Performance Planning

You should complete Part II, "Performance Planning" before proceeding to other parts of this guide. Based on years of designing and performance experience, Oracle has designed a performance methodology. This part describes activities that can dramatically improve system performance and contains the following topics:

在你看其他章节之前，先要看一下第二部分的性能计划的内容（oracle真把自己当老师一样循序渐进的教我们了，不过也真是的，很多文章都是像TOM大师那样的高手写出来的，很有槟榔般的嚼劲）。基于多年的开发及性能调优的经验，oracle设计出了一套关于性能的方法论(这个词相信IBM员工很熟悉)。这部分包含了一些可以神奇般的提升性能的精彩内容，不容错过，有以下内容：

Understanding Investment Options
Understanding Scalability（可扩展性）
System Architecture（系统架构）
Application Design Principles（应用设计原则）
Workload Testing, Modeling, and Implementation（压力测试）
Deploying New Applications（应用部署）

1.1.2 Instance Tuning

Part III, "Optimizing Instance Performance" discusses the factors involved in the tuning and optimizing of an Oracle database instance.

When considering instance tuning, take care in the initial design of the database to avoid bottlenecks that could lead to performance problems. In addition, you must consider:

第三部分讨论了涉及到调试与优化oracle数据库实例的因素。如：内存分配；数据库各部分对IO的要求；如何将操作系统调整到数据库要求的最佳状态

一旦你安装和配置完数据库之后，就应该去检查性能相关的一些问题。（当然没有是最好的了）

Allocating memory to database structures
Determining I/O requirements of different parts of the database
Tuning the operating system for optimal performance of the database

After the database instance has been installed and configured, you must monitor the database as it is running to check for performance-related problems.

1.1.2.1 Performance Principles

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

Tuning is driven by identifying the most significant bottleneck and making the appropriate changes to reduce or eliminate the effect of that bottleneck. Usually, tuning is performed reactively, either while the system is in preproduction or after it is live.

性能优化需要一个尽管相似但不同的方法去配置一个系统。配置一个功能丰富可用的系统需要按一定的方法顺序去分配资源。性能调优的源动力在于：确定性能的瓶颈和减少或消除这些瓶颈。通常性能调优是在测试环境中或者是活生生的生产环境中展开的（也就是说不出问题不做优化，将就这么理解吧）

1.1.2.2 Baselines

The most effective way to tune is to have an established performance baseline that you can use for comparison if a performance issue arises. Most database administrators (DBAs) know their system well and can easily identify peak usage periods. For example, the peak periods could be between 10.00am and 12.00pm and also between 1.30pm and 3.00pm. This could include a batch window of 12.00am midnight to 6am.

It is important to identify these peak periods at the site and install a monitoring tool that gathers performance data for those high-load times. Optimally, data gathering should be configured from when 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 should include the following:

Application statistics (transaction volumes, response time) 事务的量，响应的时间
Database statistics 数据库统计数据
Operating system statistics 操作系统统计数据
Disk I/O statistics IO统计数据
Network statistics 网络统计数据

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

最有效的调优方法是建立一个可以进行参考及比较的标准。多数DBA都非常的清楚自己维护的系统高峰期是在什么时候的。例如在10点到12点是一个高峰期，13:30到15:00是另一个。可能还包含了在00:00点到6:00的一个批量处理时间。安装一个监控高峰时期性能的工具去监测问题是非常非常重要的。最好是在应用程序测试阶段就由QA去收集配置一下。否则就要到上线之后再去做了。理想情况下，这些基本的数据收集要包含以上的几点。在AWR当中，这些标准是由一系列的快照组成的，用于未来出问题时进行比较。

注：个人经验在银行的系统里面，通常情况下系统要上线的话，银行会先提一个要求，比如说ATM机前置系统要求：TPS达到100笔，意思就是一秒钟要至少处理100笔交易，同时要求一笔交易响应时间不能超过10秒钟等等（大家取钱时有些机器上会显示60秒倒计时），我这是打个比方的，不同系统的标准不一样的，不同银行标准也不一样的。银行的系统通常高峰期在8:30~11:00，11:30~13:30，14:30~16:30，17:30~19:00， 21:00左右；以上是时间段时OLTP处理的高峰时期 ------这个可以理解的，因为早晨银行8:30一开门，大爷们就开始去办理业务了，下班后小青年去ATM取钱喝啤酒了，晚上9点左右美女们开始淘宝购物了。通常情况下晚上11:00开始要跑批量程序，比如处理ATM机对账；处理银联的清算文件，信用卡系统入账；生成会计分录；各类报表产生等等，这些通常是在晚上11：00到第二天8点之间处理的，有些系统处理的快，可能几十分钟左右就处理完了，有些系统业务量大或者程序设计的烂的话要处理10几个小时。像新加坡星展银行由于采用印度人的银行核心系统，从上线那天开始几乎没有一天准时将银联清算文件生成出来的，基本上都是第二天进行人工处理（将文件手工发送给银联），印度系统不是一般的差，可能和使用JAVA语言有关。我在几十家银行中只见过这么一个使用JAVA做核心系统的，就是那德性，其他的基本上是C语言来做，个别败家子使用COBOL来做（日本国内有些是用汇编做的，维护几十年前的老系统，主要是证券上使用）。后来听说他们银行想换成C做的，我也不清楚他们现在怎么样了，总之印度系统靠谱的。

1.1.2.3 The Symptoms and the Problems 症状和问题

A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important 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 by poorly-configured disks. However, it could also be caused by a significant amount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.通常是硬盘配置有问题。但是也有可能是SQL写的太差造成不必要的IO开销（这是最主要的，特别是忘记加索引）
Latch contention

Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes.

很少是因为配置出错造成latch的竞争问题。通常是应用程序出了问题造成的。（比如乱加for update）

Excessive CPU usage

Excessive CPU usage usually means that there is little idle CPU on the system. This could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs.系统的CPU不够了。通常是CPU不够或者SQL太差，或者是应用设计的太烂。

1.1.2.4 When to Tune

There are two distinct types of tuning:

Proactive Monitoring 主动性的监控或前瞻性的监控
Bottleneck Elimination 消除性能的瓶颈

1.1.2.4.1 Proactive Monitoring

Proactive monitoring usually occurs on a regularly scheduled interval, where several performance statistics are examined to identify whether the system behavior and resource usage has changed. Proactive monitoring can also be considered as proactive tuning.

Usually, monitoring does not result in configuration changes to the system, unless the monitoring exposes a serious problem that is developing. In some situations, experienced performance engineers can identify potential problems through statistics alone, although accompanying performance degradation is usual.

Experimenting with or tweaking a system when there is no apparent performance degradation as a proactive action can be a dangerous activity, resulting in unnecessary performance drops. Tweaking a system should be considered reactive tuning, and the steps for reactive tuning should be followed.

Monitoring is usually part of a larger capacity planning exercise, where resource consumption is examined to see changes in the way the application is being used, and the way the application is using the database and host resources.

前瞻性监控应该是定期的去做，这样才能判断系统行为或者资源使用是否在正常允许范围内。这种主动性监控算是一种前瞻性的优化工作。

通常情况下监控不会导致要去修改系统配置，除非一些严重的问题正在发生或者已经发生。在有些情况下，有经验的性能工程师在性能没有明显下降的时候，仅仅通过看这些统计数据就能发现那些----可能在未来才会发现的问题。（这是老中医啊。我听过一个保险公司的20岁女孩能在看财务报表几眼后知道有没有假账，后来去美国了，NB啊）

当作为一个前瞻性的方法，对在没有性能出现明显的下降情况下进行实验或者对系统进行微调是非常危险的行为，可能会导致不必要的性能下降。（没事别乱动！说是就是我啊！）

系统微调应该看作是一个reactive（被动的）调优，这个过程不在这个主动性范围内。

监控通常是占很大一部分地位的，它们对资源的使用进行监控以查看应用程序的状态，以及它们对数据库的使用情况。

（在银行监控是运维团队的事情，它们本应该定期将生产上的资源图发给我们开发人员，可是如果开发人员不给他们要的话，许多运维人员懒的做的。除非出了问题，比如CPU一直90%以上，或者硬盘满了，数据库ARCHIVED空间不够了，正常情况下1小时的批量跑了5个小时等等，这才发邮件过来通知的。通常情况下对于重要的系统，开发团队会有人在定期监控看的，不会等到监控人员监控到问题才展开行动，如果那样的话就晚了。如果在监控人员发现问题时还没有将问题解决，这时候项目经理或者主要负责人员通常已经找好话语去对付他们了。未来我就要干运维了，小子们等着吧！）

1.1.2.4.2 Bottleneck Elimination

Tuning usually implies fixing a performance problem. However, tuning should be part of the life cycle of an application—through the analysis, design, coding, production, and maintenance stages. Often, the tuning phase is left until the database is in production. At this time, tuning becomes a reactive process, where the most important bottleneck is identified and fixed.

Usually, the purpose for tuning is to reduce resource consumption or to reduce the elapsed time for an operation to complete. Either way, the goal is to improve the effective use of a particular resource. In general, performance problems are caused by the overuse of a particular resource. The overused resource is the bottleneck in the system. There are several distinct phases in identifying the bottleneck and the potential fixes. These are discussed in the sections that follow.

Remember that the different forms of contention are symptoms that can be fixed by making changes in the following places:

Changes in the application, or the way the application is used 修改应用程序，或者修改程序使用的方式（比如改代码，或者举例改用shared网络配置模式等等)
Changes in Oracle 在oracle上进行修改，这可能是一些参数有问题或者是oracle的BUG造成的.
Changes in the host hardware configuration 机器配置太差

Often, the most effective way of resolving a bottleneck is to change the application.通常情况下都是修改应用程序即可（说的好像oracle自己牛的不得了一样，不过人家的确是牛的有资本啊）

调优通常意味着修复一个性能问题。然而，调优应该是在应用程序生命周期中的一部分工作，包含了需求分配，设计，编码，上线，维护几个阶段。通常情况下，很多人将调优被放置到上线以后去做，这样调优就成了一个被动的过程，需要后期被动地去确定和修复这么问题。（我见过几个银行的项目就是这样子的，我不提是哪个银行了，他们使用DB2数据库，在上线前1个多月的时候，出了一点性能问题，我去负责调应用程序。完了以后我告诉行方说数据库要尽可能派人优化一下，他说市场人大把的DBA在，这个好做的。结果是上线没一周就失败了，最后又开始修改，重新上线；二期上线前性能还是不行，我又被派去优化，再次查看SQL语句，真想吐啊。写成那样子别说是DB2，就是ORACLE也不行啊。不是说DB2不好，而是它确定不好！）

通常情况下调优的目的是：减少资源消耗或者减少响应时间。无论是哪种，目标都是尽可能的提升相应资源的使用效率。通常情况下，性能问题是由于对特定资源的过度使用造成的，或者说是不正确的使用造成的。这个过度使用的资源就是瓶颈所在。在确定瓶颈和修复时有几个不同的阶段，详见以上3点。

1.1.3 SQL Tuning

Part IV, "Optimizing SQL Statements" of this guide discusses the process of tuning and optimizing SQL statements.

Many application programmers consider SQL a messaging language, because queries are issued and data is returned. However, client tools often generate inefficient SQL statements. Therefore, a good understanding of the database SQL processing engine is necessary for writing optimal SQL. This is especially true for high transaction processing systems.

Typically, SQL statements issued by OLTP applications operate on relatively few rows at a time. If an index can point to the exact rows that are required, then Oracle Database can construct an accurate plan to access those rows efficiently through the shortest possible path. In decision support system (DSS) environments, selectivity is less important, because they often access most of a table's rows. In such situations, full table scans are common, and indexes are not even used. This book is primarily focussed on OLTP-type applications. For detailed information on DSS and mixed environments, see the Oracle Database Data Warehousing Guide.

在第四部分讲解了SQL如何进行调优

许多应用开发人员将SQL看成一个对讲机语言，它们发出查询请求，oracle将结果返回给他们。然而客户端的工具（可以理解成应用程序）通常使用低效的SQL语句。因此深入的理解SQL处理机制是写出漂亮SQL的必要条件。特别是在调优那些对处理效率要求很高的应用时。

基本上，在OLTP系统中，SQL语句同一时间仅仅取很少的数据。如果索引可以准确的定位到那些数据的话，oracle就可以精确的制订出最优的执行计划出来。但是在数据库仓库系统中，这种选择度就不是很重要了，因为他们通常访问表中大部分数据库。在这种情况下全表扫描就是最好的了，索引都可以不需要。本书集中在OLTP应用中。

1.1.3.1 Query Optimizer and Execution Plans

When a SQL statement is executed on an Oracle database, the query optimizer determines the most efficient execution plan after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

During the evaluation process, the query optimizer reviews statistics gathered on the system to determine the best data access path and other considerations. You can override the execution plan of the query optimizer with hints inserted in SQL statement.

oracle在执行查询语句时，查询优化器会考虑很多的因素，以及指定查询的条件，然后才能找到一个相对而言最好的执行计划。这个寻找执行计划的步骤对于SQL的执行效率是非常重要的。

在评估过程中，优化器会查看系统的统计数据及其他条件去计算一个最好的访问路径。当然我们可以通过加一些SQL的暗示语句以指导oracle做一些执行计划。（毕竟只有应用开发人员知道数据是如何组织的；不过99%的情况下用不着，oracle比我们做的更好）

1.2 Introduction to Performance Tuning Features and Tools

Effective data collection and analysis is essential for identifying and correcting performance problems. Oracle Database provides several tools that allow a performance engineer to gather information regarding database performance. In addition to gathering data, Oracle Database provides tools to monitor performance, diagnose problems, and tune applications.

The Oracle Database gathering and monitoring features are mainly automatic, managed by Oracle background processes. To enable automatic statistics collection and automatic performance features, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL. You can administer and display the output of the gathering and tuning tools with Oracle Enterprise Manager, or with APIs and views. For ease of use and to take advantage of its numerous automated monitoring and diagnostic tools, Oracle Enterprise Manager Database Control is recommended.

有效的数据收集和分析对于确定问题所在是非常必要的。oracle提供了许多的工具让优化工程师去收集性能相关的数据。除了收集，它还提供监控，诊断，调优的三陪服务工具噢！

oracle收集和监控是主要由后台的一个程序自动完成的。为了做到这一点，STATISTICS_LEVEL 参数一定要设置成TYPICAL或者更高级的ALL才行。你可以使用OEM或者DBMS_之类的包去显示那些统计的结果。为了容易的显示这些报表，oracle建议你要用它提供的OEM去做。

See Also:下来是介绍我们可以去参考的书籍或目录，以后是边学边看；边看边忘；再看再忘；直接记住为止

Oracle Database 2 Day DBA to learn how to use Oracle Enterprise Manager to manage Oracle Database
Oracle Database 2 Day + Performance Tuning Guide to learn how to use Oracle Enterprise Manager to tune database performance
Oracle Database PL/SQL Packages and Types Reference for detailed information on the DBMS_ADVISOR, DBMS_SQLTUNE, DBMS_AUTO_SQLTUNE, and DBMS_WORKLOAD_REPOSITORY packages
Oracle Database Reference for information about the STATISTICS_LEVEL initialization parameter

1.2.1 Automatic Performance Tuning Features

The Oracle Database automatic performance tuning features include:

Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. See "Overview of the Automatic Workload Repository".
Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database. See "Overview of the Automatic Database Diagnostic Monitor".
SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements. See "Tuning Reactively with SQL Tuning Advisor".
SQLAccess Advisor provides advice on materialized views, indexes, and materialized view logs. See "Automatic SQL Tuning Features" and "Overview of SQL Access Advisor" for information about SQLAccess Advisor.
End-to-End Application tracing identifies excessive workloads on the system by specific user, service, or application component. See "End-to-End Application Tracing".
Server-generated alerts automatically provide notifications when impending problems are detected. See Oracle Database Administrator's Guide to learn how to monitor the operation of the database with server-generated alerts.
Additional advisors that can be launched from Oracle Enterprise Manager, such as memory advisors to optimize memory for an instance. The memory advisors are commonly used when automatic memory management is not set up for the database. Other advisors are used to optimize mean time to recovery (MTTR), shrinking of segments, and undo tablespace settings. To learn about the advisors available with Oracle Enterprise Manager, see Oracle Database 2 Day + Performance Tuning Guide.
The Database Performance page in Oracle Enterprise Manager displays host, instance service time, and throughput information for real time monitoring and diagnosis. The page can be set to refresh automatically in selected intervals or manually. To learn about the Database Performance page, see Oracle Database 2 Day + Performance Tuning Guide.

ORACLE提供了以下几个自动性能诊断的工具：AWR，ADDM, SQL Tuning Advisor, SQLAccess Advisor 等等其他的。这个不做翻译了

1.2.2 Additional Oracle Database Tools（oracle还提供了一些其他的工具用于侦测性能问题）

This section describes additional Oracle Database tools that you can use for determining performance problems.

1.2.2.1 V$ Performance Views

The V$ views are the performance information sources used by all Oracle Database performance tuning tools. The V$ views are based on memory structures initialized at instance startup. The memory structures, and the views that represent them, are automatically maintained by Oracle Database for the life of the instance. See Chapter 10, "Instance Tuning Using Performance Views" for information diagnosing tuning problems using the V$ performance views.

ITPub博客

Oracle Performance Tuning 11g2 (1)