老李分享： Oracle Performance Tuning Overview 翻譯

標籤：

   poptest是國內唯一一家培養測試開發工程師的培訓機構，以學員能勝任自動化測試，效能測試，測試載入器開發等工作為目標。如果對課程感興趣，請大家諮詢qq：908821478，諮詢電話010-84505200。1 效能最佳化概述

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

本章節給出了效能最佳化的簡介，它包含了一下幾個部分。

• Introduction to Performance Tuning
• 效能最佳化的介紹
• Introduction to Performance Tuning Features and Tools
• 效能最佳化工具和特點的介紹

1.1 效能最佳化簡介

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

這個小節提供了對一個oracle資料庫系統進行效能最佳化的知識。包括一下幾個主題

• Performance Planning
• 效能規劃
• Instance Tuning
• 執行個體最佳化
• SQL Tuning
• Sql最佳化

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已經設計了一套關於效能方面的方法體系。本節描述了能夠顯著提高系統效能的清晰和簡單的方案。該方案涉及到一下主題:

• Understanding Investment Options
• 理解能夠在調優方面起作用的所有因素
• Understanding Scalability
• 理解可擴充性
• System Architecture
• 系統架構
• Application Design Principles
• 應用設計準則
• Workload Testing, Modeling, and Implementation
• 負載測試 建立模型，實現
• Deploying New Applications
• 部署新的應用

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:

兩種不同方式的調優

• Proactive Monitoring
• 主動監測
• Bottleneck Elimination
• 消除瓶頸

1.1.2.4.1主動監測

Proactive monitoring usually occurs on aregularly scheduled interval, where a number of performance statistics areexamined to identify whether the system behavior and resource usage haschanged. Proactive monitoring can also be considered as proactive tuning.

主動監測通常發生在一個週期性計劃間隔，那裡，一定數量的效能統計資料會被檢測來確定系統的運行和資源使用率是否已經被改變。主動監測也可以被看作是主動調優。

Usually, monitoring does not result inconfiguration changes to the system, unless the monitoring exposes a seriousproblem that is developing. In some situations, experienced performanceengineers can identify potential problems through statistics alone, althoughaccompanying performance degradation is usual.

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

Monitoring is usually part of a largercapacity planning exercise, where resource consumption is examined to seechanges in the way the application is being used, and the way the applicationis using the database and host resources.

通常，監控並不改變系統的配置，除非他暴露出了正在發生的一些嚴重的問題。在一些情況下，有經驗的效能工程師能夠只通過統計資訊解決潛在的問題，儘管伴隨著效能的下降。當沒有顯著的效能下降時，主動調整和實驗系統是危險的，會造成系統不必要的效能下降。調整系統需要考慮反應調諧，而且反應調優的步驟要被跟蹤。

監測通常是更大規模的規劃工作中的一部分,那部分裡面，資源消耗被檢測來觀測應用被使用的方式和 資料庫及 主機資源的改變。

1.1.2.4.2瓶頸消除

Tuning usually implies fixing a performanceproblem. However, tuning should be part of the life cycle of an application—through the analysis, design, coding,production, and maintenance stages. Oftentimes, the tuning phase is left untilthe system is in production. At this time, tuning becomes a reactivefire-fighting exercise, where the most important bottleneck is identified andfixed.

Usually, the purpose for tuning is to reduceresource consumption or to reduce the elapsed time for an operation tocomplete. Either way, the goal is to improve the effective use of a particularresource. In general, performance problems are caused by the over-use of aparticular resource. That resource is the bottleneck in the system. There are anumber of distinct phases in identifying the bottleneck and the potentialfixes. These are discussed in the sections that follow.

調優通常意味著修複效能問題。然而調優應該是應用程式整個生命週期的一部分，包括分析，設計，編碼，生產，維護階段。經常的情況是調優階段會一直保留到到系統生產運行才離開。那個時候調優已經變成響應的工作，大多數重要的瓶頸已經被識別和修複。

通常情況下，調優的目的是為了減少資源消耗或者減少某項操作完成的耗時。無論哪種方式，目的都是為了提高某個特殊資源的有效利用。一般來說，效能問題都是由某個特殊資源過度使用造成的，那個資源正好是系統的瓶頸。辨識出瓶頸和潛在的問題有許多不同的階段。這些東西將在以下部分被討論：

Remember that the different forms ofcontention are symptoms that can be fixed by making changes in the followingplaces:

請記住競爭的不同形式是一些癥狀，這些癥狀能夠被在以下地方做些改變來修複、

• Changes in the application, or the way the application is used
• 應用程式的改變，或者應用程式被使用方式的改變
• Changes in Oracle
• oracle的改變
• Changes in the host hardware configuration
• 主機硬體設定的改變。

Often, the most effective way of resolving abottleneck is to change the application.

經常情況下，解決瓶頸最有效地方式是改變一個應用程式。

1.1.3 SQL 最佳化

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

本節的IV部分 討論到了調優的過程 和sql的最佳化。

Many client/server application programmersconsider SQL a messaging language, because queries are issued and data isreturned. However, client tools often generate inefficient SQL statements.Therefore, a good understanding of the database SQL processing engine isnecessary for writing optimal SQL. This is especially true for high transactionprocessing systems.

許多用戶端和服務端的應用程式開發人員把sql當做是一種訊息語言，因為查詢發起後就會返回結果資料。然而用戶端工具常常會產出一些低效率的sql。因此，對資料庫sql處理工作機制有個很好的理解對寫出最優的sql是必要的。這對高事物處理系統尤其管用。

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

通常情況下，由聯機交易處理系統發起的sql語句在同一時間會操作相對少的行。如果一個索引能夠定位它所需要的準確行，那麼oracle能夠通過可能的最短的路徑來建立一個準確的計劃來取到那些行。在決策支撐系統內容當中，查詢是次要的，因為他們總是要擷取表中的大部分行。。在這個情況下全表掃描是普遍的，索引甚至可能都沒被用到。這裡主要的關注點都在聯機交易處理系統。若要獲得決策支撐系統和混合實境世界更詳細的資訊，請看 oracle資料庫資料倉儲指南。

1.1.3.1查詢最佳化工具和執行計畫

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

當一個sql語句在oracle資料庫上執行的時候，在考慮過涉及到的若干對象的許多相關因素和查詢時的一些具體情況後oracle查詢最佳化工具會決定哪個是最高效的執行計畫。這個決定是任何sql語句執行過程的重要一步，而且對執行時間影響很大。

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

在評估期間，查詢最佳化工具會檢查系統收集的統計資訊來決定最好的取資料途徑和其他考慮。你可以在sql語句中插入hint提示來覆蓋查詢最佳化工具的執行計畫。

老李分享： Oracle Performance Tuning Overview 翻譯