Oracle 性能優(yōu)化 之 游標及 SQL

我們要先說一下游標這個概念。

從 Oracle 數(shù)據(jù)庫管理員的角度上說，游標是對存儲在庫緩存中的可執(zhí)行對象的統(tǒng)稱。SQL 語句是存儲在庫緩存中的，它是游標。除了它之外，還有 Oracle 的存儲過程也是存儲在庫緩存中的可執(zhí)行對象，從 Oracle DBA 的角度上說，它也是游標。Oracle 也把它算為游標，在某些和游標相關的視圖中，也會顯示存儲過程的一些信息的。但從開發(fā)者的角度說，只有 SQL 語句才是游標。

你的應用程序或許是用 Java、Pro*C 等語言開發(fā)的，也可能有中件間，等等，對于 DBA 來說，我們不必過多的關心這些。以一個常見的三層應用為例， 如下圖：

類似的圖我們在很多地方都可以看到，假設這是一個三層 J2EE 應用?？蛻舳苏{(diào)用的 Java 應用程序存放在中間的應用服務器層，應用程序的執(zhí)行由應用服務器負責。

如上圖這段 Java 應用程序，它的執(zhí)行就是應用應用服務器的任務。但是，當執(zhí)行到 executeQuery ("select * from Test") 語句時，這條 Java 語句要求從數(shù)據(jù)庫服務器中查詢表 Test。發(fā)下圖：

這條語句的執(zhí)行，是由數(shù)據(jù)庫服務器負責的。數(shù)據(jù)庫服務器只負責以最快的速度將 “Select * from test” 執(zhí)行完畢。其他的它一概不負責。我們作為 DBA，只要保證 SQL 語句可以更快的執(zhí)行就行了，至于應用程序邏輯方面的問題，不由我們負責。也就是說，作為 DBA，我們不必負責具體代碼的問題，我們只負責 SQL 語句的執(zhí)行。每條送交 Oracle 執(zhí)行的 SQL 語句，無論這條語句是你手動在 SQL*Plus 命令窗口中敲入的，還是應用服務器傳送給 Oracle 要求執(zhí)行的，它們都以一樣的方式被傳遞到 Oracle 中，由服務器進程執(zhí)行。這些 SQL 語句的執(zhí)行情況、具體的執(zhí)行計劃等數(shù)據(jù)資料會在一些視圖中被記錄下來，以供 DBA 追蹤問題、調(diào)優(yōu) SQL 的執(zhí)行。

下面，我們就介紹一下這些相關 SQL 執(zhí)行情況的視圖。我們再強調(diào)一個名詞，對于從任何地方傳遞給 Oracle 數(shù)據(jù)庫服務器要求執(zhí)行的東西，我們都稱為游標。它主要包括 SQL 語句和 PL/SQL 程序段。

SHARABLE_MEM：游標所占共享內(nèi)存

SORTS：游標完成的排序次數(shù)

FETCHES：抓取的次數(shù)

EXECUTIONS：執(zhí)行次數(shù)

LOADS：游標被加載或重新加載到庫緩存中的次數(shù)。游標只所以被重新加載有可能是游標無效或庫緩存內(nèi)存不足。

INVALIDATIONS：游標的無效次數(shù)

PARSE_CALLS：游標的解析次數(shù)，包括硬解析與軟解析

DISK_READS：游標執(zhí)行了多少次物理讀

BUFFER_GETS：邏輯讀的次數(shù)

USER_IO_WAIT_TIME：用戶 I/O 等待時間

PARSING_SCHEMA_NAME：最先解析此游標的方案 ID

CPU_TIME：游標解析、執(zhí)行、抓取時所用的 CPU 時間。單位是微秒。

ELAPSED_TIME：游標解析、執(zhí)行、抓取時所用的總時間。單位是微秒。

這個視圖中 DISK_READS、BUFFER_GETS、CPU_TIME、ELAPSED_TIME 這四個列在調(diào)優(yōu) SQL 語句時最為重要。在數(shù)據(jù)庫系統(tǒng)的速度不是太另人滿意時，如果你已經(jīng)確定過了，不是其他方面的原因，而是 SQL 語句性能的問題，只是無法確定是那條、或那些條語句拖慢了整體的速度。那么此時選擇調(diào)優(yōu)物理讀、邏輯讀最多的，或最耗 CPU 時間的 SQL 語句進行調(diào)節(jié)，往往可以取得今人滿意的性能增長。

我們也可以以 EXECUTIONS（執(zhí)行次數(shù)）最多的 SQL 語句為調(diào)優(yōu)對象。另外，PARSE_CALLS 是解析次數(shù)，對于此列值最多的 SQL 語句，我們可以看看是否可以降低語句的解析次數(shù)。

關于 SQL 調(diào)優(yōu)，和程序的調(diào)優(yōu)是一樣的。如果我們從事過代碼優(yōu)化這樣的工作，就會知道，對于一個大型的應用程序來說調(diào)優(yōu)的方法也是要從執(zhí)行次數(shù)最多的那部分代碼、或從最消耗資源的代碼入手。

還有一個問題，就是文檔中關于這個視圖會經(jīng)常提到一個概念：子游標與父游標。如果兩個游標的文本一模一樣，但由于環(huán)境不同，比如，游標所操作的表是不同用戶下的同名表，這兩個游標是不能共享執(zhí)行計劃的。它們都有各自的執(zhí)行計劃存在庫緩存中。這兩個游標就是子游標，Oracle 還會建立一個父游標，父游標中沒有執(zhí)行計劃，它只是文本相同但執(zhí)行計劃不同的所有游標的代表。

其實在庫緩存中，即使沒有文本相同的子游標，Oracle 會為每個游標都創(chuàng)建父游標。因為父游標是文本相同的子游標的代表嗎，所有文本相同的游標共享同一個父游標。

也就是說，只要你執(zhí)行 SQL 語句，Oracle 都會在庫緩存中保存一父一子兩個游標。如果你執(zhí)行了文本相同但環(huán)境不同因而不能共享執(zhí)行計劃的 SQL 語句，那么一個父游標可能就對應多個子游標。

父游標沒有執(zhí)行計劃，它只有一信息管理性數(shù)據(jù)，Oracle 添加它的目的就是為了管理文本相同的游標。有一個視圖是專門針對父游標的，就是 V$sqlarea。下面我們說一下這個視圖。

V$SQLAREA 和 V$SQL 的列幾乎是一模一樣的。在 V$SQLAREA 中匯總了子游標的數(shù)據(jù)。如果有兩個語句：語句 A 和語句 B，它們文本一模一樣，但是由于環(huán)境不同沒有共享執(zhí)行計劃，而是有各自的執(zhí)行計劃。也就是語句 A 和語句 B 是同一父游標下的子游標。在 V$SQL 視圖中，因為它是顯示子游標的，所以語句 A 和語句 B 各占一行，假設語句 A 的 DISK_READS（物理讀）是 100，語句 B 的物理讀是 3000。V$SQLAREA 是顯示父游標信息的，語句 A 和語句 B 因為文本相同，它們兩個對應同一個父游標，在 V$SQLAREA 中占一行。在 V$SQLAREA 中，語句 A 和語句 B 父游標行中的 DISK_READS 就是 3100，也就是語句 A 和語句 B 的和。V$SQLAREA 中的其它列也是如此，都是 V$SQL 中相應子游標的合計。

有一個列是 V$SQL 中沒有的，就是：VERSION_COUNT，它是對應同一父游標的子游標的數(shù)量。如果這個數(shù)字太高，可能代表由于某些原因使本可以共享執(zhí)行計劃的游標沒有共享。

這個視圖和參數(shù)涉及游標的打開。什么是游標的打開，就是在庫緩存中，用戶在軟、硬解析游標時，會在游標對象的句柄上加一個鎖，也就是 Library cache lock。在解析并執(zhí)行完游標后，這個鎖并不會馬上去掉，而是會一直保留著，直到用戶發(fā)出了 Close 命令關閉游標時為止。我們在 SQL*Plus 命令窗口中發(fā)出的命令，在抓取完所有行后，SQL*Plus 將自動為我們發(fā)出 Close 命令來關閉游標。

當游標打開時，Library cache lock 將一直保持，這樣，即使庫緩存內(nèi)存緊張，需要老化對象，也不會老化這些還正在加鎖的對象。因此，如果用戶不停的要求數(shù)據(jù)庫服務器打開游標、執(zhí)行 SQL，但卻忘了關閉游標，這很容易耗盡共享池的內(nèi)存。為此，Oracle 準備了一個參數(shù)，就是 Open_cursor，它的默認值在 9i 下是 50，在 10g 中是 300，也就是說，在 10g 下，每個會話最多只能同時打開 300 個游標。有了這個限制，就不用害怕用戶不停的打開游標但又不關閉它，而耗盡共享池內(nèi)存了。

如果會話同時打開的游標數(shù)量超出了 Open_cursor 參數(shù)的限制，Oracle 將禁止會話打開新的游標。同時報出錯誤：ORA-01000: 超出打開游標的最大數(shù) 。

在用戶斷開會話的連接后，會話打開的這些游標將自動關閉。

V$open_cursor 視圖專用來查看當前會話打開的游標信息。它只能查看當前會話打開的游標。

如果應用程序中有很多類似下面這樣的 SQL 語句：

select * from 某表 where id=1;

select * from 某表 where id=2;

select * from 某表 where id=50;

等等，這些 SQL 語句嚴格來說是無法共享游標（也就是共享執(zhí)行計劃）的，但是這些語句所需要執(zhí)行計劃其實都是一樣的。無論你在表中查詢 ID 為 1 的行還是查詢 ID 為 100 的行，執(zhí)行方式應該是一樣的。如果你想讓這樣的語句共享游標，那么，你可以改變 Cursor_sharing 參數(shù)的值。

此參有三個值：

• ? EXACT：這個值是默認值。除非游標文本一模一樣，否則不會共享游標。

• ? SIMILAR：這個最智能，如果游標只有條件中的數(shù)據(jù)值部分不同，并且?guī)炀彺嬷性杏螛说膱?zhí)行計劃對于新執(zhí)行的 SQL 語句也是最優(yōu)的，將不再為 SQL 語句創(chuàng)建新的游標，而是讓它共享庫緩存中原有的游標。

• ? FORCE ：不比較執(zhí)行計劃是否最優(yōu)，只要游標中除了條件中的數(shù)據(jù)值部分不同外，其他部分都相同，就會共享游標。

此參數(shù)可以在會話級修改，也就是可以使用 Alter session 修改它的值，這將只影響某一個會話，而不會影響其他會話。