如何調(diào)優(yōu) Oracle SQL系列文章：SQL性能方法論

如何調(diào)優(yōu) Oracle SQL系列文章 第二篇： SQL性能方法論。

2 SQL性能方法論

2.1 應(yīng)用程序設(shè)計(jì)指南

獲得良好SQL性能的關(guān)鍵是在設(shè)計(jì)應(yīng)用程序時(shí)考慮性能。

2.1.1 數(shù)據(jù)建模指南

數(shù)據(jù)建模對(duì)于成功的應(yīng)用程序設(shè)計(jì)非常重要。

你必須以根據(jù)實(shí)際的業(yè)務(wù)需求進(jìn)行數(shù)據(jù)建模。在這個(gè)過程中，對(duì)于什么樣的模型是正確的數(shù)據(jù)模型可能會(huì)出現(xiàn)不同的爭(zhēng)議。重要的是將最大的建模工作應(yīng)用于受最頻繁的業(yè)務(wù)事務(wù)影響的實(shí)體。

在建模階段，很有可能花費(fèi)太多時(shí)間來(lái)建模非核心數(shù)據(jù)元素，這會(huì)導(dǎo)致開發(fā)前置時(shí)間的增加。使用建模工具可以快速生成模式定義，并且在需要快速原型時(shí)非常有用。

2.1.2 編寫有效的應(yīng)用指南

在系統(tǒng)開發(fā)的設(shè)計(jì)和架構(gòu)階段，確保應(yīng)用程序開發(fā)人員了解SQL執(zhí)行效率。
為實(shí)現(xiàn)此目標(biāo)，開發(fā)環(huán)境必須支持以下特征：

• 良好的數(shù)據(jù)庫(kù)連接管理
  連接到數(shù)據(jù)庫(kù)是一項(xiàng)昂貴的操作，并且無(wú)法擴(kuò)展。因此，最佳做法是最小化與數(shù)據(jù)庫(kù)的并發(fā)連接數(shù)。一個(gè)簡(jiǎn)單的系統(tǒng)，用戶在應(yīng)用程序初始化時(shí)連接，是比較理想的。但是，在基于Web或多層應(yīng)用程序中，這種方法可能很困難。使用這些類型的應(yīng)用程序，一般是使用數(shù)據(jù)庫(kù)連接池，而不是為每個(gè)用戶請(qǐng)求重新建立連接。

• 良好的游標(biāo)使用和管理
  維護(hù)用戶連接對(duì)于最小化系統(tǒng)上的解析活動(dòng)同樣重要。解析是解釋SQL語(yǔ)句并為其創(chuàng)建執(zhí)行計(jì)劃的過程。此過程有許多階段，包括語(yǔ)法檢查，安全檢查，執(zhí)行計(jì)劃生成以及將共享結(jié)構(gòu)加載到共享池中。有兩種類型的解析操作：

  首次提交SQL語(yǔ)句，并且在共享池中找不到匹配項(xiàng)。硬解析是資源最密集且不可擴(kuò)展的，因?yàn)樗鼈儓?zhí)行解析中涉及的所有操作。

  首次提交SQL語(yǔ)句，并在共享池中找到匹配項(xiàng)。匹配可以是其他用戶先前執(zhí)行的結(jié)果。共享SQL語(yǔ)句，這對(duì)性能來(lái)說是最佳的。但是，軟解析并不是最理想的，因?yàn)樗鼈內(nèi)匀恍枰Z(yǔ)法和安全檢查，這會(huì)消耗系統(tǒng)資源。

• 軟解析

• 硬解析

因?yàn)榻馕鰬?yīng)該盡可能地最小化，所以應(yīng)用程序開發(fā)人員應(yīng)該設(shè)計(jì)他們的應(yīng)用程序來(lái)解析一次SQL語(yǔ)句并多次執(zhí)行它們。這是通過游標(biāo)完成的。經(jīng)驗(yàn)豐富的SQL程序員應(yīng)該熟悉打開和重新執(zhí)行游標(biāo)的概念。

• 有效使用綁定變量

應(yīng)用程序開發(fā)人員還必須確保在共享池中所共享SQL語(yǔ)句。為了實(shí)現(xiàn)這一目標(biāo)，使用綁定變量來(lái)改造查詢。如果不這樣做，則SQL語(yǔ)句可能會(huì)被解析一次，并且永遠(yuǎn)不會(huì)被其他用戶重用。要確保共享SQL，不要將字符串文字與SQL語(yǔ)句一起使用。例如：

帶字符串文字的語(yǔ)句:

SELECT
 * 
FROM
 employees 
WHERE
 last_name 
LIKE 'KING';

綁定變量的語(yǔ)句：

SELECT
 * 
FROM
 employees 
WHERE
 last_name 
LIKE :1;

下面的例子展示了一個(gè)簡(jiǎn)單的OLTP應(yīng)用程序的一些測(cè)試結(jié)果:

這些測(cè)試是在4顆CPU計(jì)算機(jī)上進(jìn)行的。隨著系統(tǒng)上CPU數(shù)量的增加，差異也會(huì)增加。

2.2 部署應(yīng)用程序指南

要實(shí)現(xiàn)最佳性能，部署應(yīng)用程序時(shí)要像設(shè)計(jì)應(yīng)用程序時(shí)一樣精心。

2.2.1 在測(cè)試環(huán)境中部署指南

測(cè)試過程主要包括功能測(cè)試和穩(wěn)定性測(cè)試。在這個(gè)過程的某個(gè)時(shí)候，您必須執(zhí)行性能測(cè)試。

以下列表描述了對(duì)應(yīng)用程序進(jìn)行性能測(cè)試的簡(jiǎn)單規(guī)則。如果記錄正確，則此列表在應(yīng)用程序上線后為生產(chǎn)應(yīng)用程序和容量規(guī)劃過程提供重要信息。

• 使用自動(dòng)數(shù)據(jù)庫(kù)診斷監(jiān)視器（ADDM）和SQL Tuning Advisor進(jìn)行設(shè)計(jì)驗(yàn)證。

• 使用實(shí)際數(shù)據(jù)量和分布進(jìn)行測(cè)試。

所有測(cè)試必須使用完全填充的表完成。測(cè)試數(shù)據(jù)庫(kù)應(yīng)包含代表生產(chǎn)系統(tǒng)的數(shù)據(jù)，包括表之間的數(shù)據(jù)量和基數(shù)。應(yīng)構(gòu)建所有生產(chǎn)索引，并正確填充模式統(tǒng)計(jì)信息。

• 使用正確的優(yōu)化程序模式。

使用您計(jì)劃在生產(chǎn)中使用的優(yōu)化程序模式執(zhí)行所有測(cè)試。

• 測(cè)試單個(gè)用戶性能。

在空閑或輕度使用的數(shù)據(jù)庫(kù)上測(cè)試單個(gè)用戶以獲得可接受的性能。如果單個(gè)用戶在理想條件下無(wú)法達(dá)到可接受的性能，則多個(gè)用戶在實(shí)際條件下無(wú)法實(shí)現(xiàn)可接受的性能。

• 獲取并記錄所有SQL語(yǔ)句的計(jì)劃。

獲取每個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃。使用此過程驗(yàn)證優(yōu)化器是否獲得了最佳執(zhí)行計(jì)劃，并且可以根據(jù)CPU時(shí)間和物理I/O來(lái)理解SQL語(yǔ)句的相對(duì)成本。此過程有助于識(shí)別將來(lái)最需要調(diào)優(yōu)和性能工作的大量事務(wù)。

• 嘗試多用戶測(cè)試。

此過程難以準(zhǔn)確執(zhí)行，因?yàn)橛脩艄ぷ髫?fù)載和配置文件可能無(wú)法完全量化。但是，應(yīng)測(cè)試執(zhí)行DML語(yǔ)句的事務(wù)以確保不存在鎖定沖突或序列化問題。

• 使用正確的硬件配置進(jìn)行測(cè)試。

使用盡可能靠近生產(chǎn)系統(tǒng)的配置進(jìn)行測(cè)試。使用真實(shí)的系統(tǒng)對(duì)于網(wǎng)絡(luò)延遲，I/O子系統(tǒng)帶寬以及處理器類型和速度尤為重要。如果不使用此方法，可能會(huì)導(dǎo)致對(duì)潛在性能問題的錯(cuò)誤分析。

• 測(cè)量穩(wěn)態(tài)性能。

在基準(zhǔn)測(cè)試時(shí)，對(duì)穩(wěn)態(tài)條件下的性能進(jìn)行測(cè)量是非常重要的。每個(gè)基準(zhǔn)測(cè)試運(yùn)行都應(yīng)該有一個(gè)上升階段，在這個(gè)階段，用戶連接到應(yīng)用程序，并逐漸開始對(duì)應(yīng)用程序執(zhí)行工作。這個(gè)過程允許將頻繁緩存的數(shù)據(jù)初始化到緩存中，并在穩(wěn)態(tài)條件之前完成單個(gè)執(zhí)行操作(例如解析)。同樣，在基準(zhǔn)測(cè)試運(yùn)行之后，有一段下降期也是有用的，這樣系統(tǒng)就可以釋放資源，用戶就可以停止工作并斷開連接。

2.2.2 應(yīng)用程序部署指南

當(dāng)新應(yīng)用程序推出時(shí)，通常采用兩種策略:Big Bang方法(即所有用戶同時(shí)遷移到新系統(tǒng))和Trickle方法(即用戶緩慢地從現(xiàn)有系統(tǒng)遷移到新系統(tǒng))。

這兩種方法都有優(yōu)點(diǎn)和缺點(diǎn)。 Big Bang方法依賴于以所需規(guī)模對(duì)應(yīng)用程序進(jìn)行可靠測(cè)試，但具有最小化數(shù)據(jù)轉(zhuǎn)換和與舊系統(tǒng)同步的優(yōu)勢(shì)，因?yàn)樗皇潜魂P(guān)閉。 Trickle方法允許在工作負(fù)載增加時(shí)調(diào)試可伸縮性問題，但可能意味著必須在轉(zhuǎn)換發(fā)生時(shí)將數(shù)據(jù)遷移到遺留系統(tǒng)和從遺留系統(tǒng)遷移。

很難推薦一種方法，而不選另一種方法，因?yàn)槊糠N技術(shù)都存在相關(guān)風(fēng)險(xiǎn)，可能會(huì)在轉(zhuǎn)換發(fā)生時(shí)導(dǎo)致系統(tǒng)中斷。當(dāng)然，Trickle方法允許在實(shí)際用戶被引入新應(yīng)用程序時(shí)對(duì)他們進(jìn)行分析，并且允許在只影響遷移用戶的情況下重新配置系統(tǒng)。這種方法會(huì)影響早期采用者，但限制了支持服務(wù)的負(fù)載。因此，計(jì)劃外停機(jī)只影響一小部分用戶。

關(guān)于如何推出新應(yīng)用程序的決定是針對(duì)每個(gè)業(yè)務(wù)的。任何采用的方法都有其獨(dú)特的壓力和應(yīng)力。您從測(cè)試過程中獲得的測(cè)試和知識(shí)越多，就越能認(rèn)識(shí)到什么時(shí)機(jī)最適合推出新應(yīng)用。