我非常喜歡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.

Oracle Performance Tuning 11g2 (1)