【正文】 的存取操作檢索結果集中的下一行數(shù)據，直到最后一行被取出來。上面提到過，批量的fetch是優(yōu)化的技巧之一。 第9步: 關閉游標(Close the Cursor) SQL語句處理的最后一個階段就是關閉游標。,DDL 語句的處理,DDL語句的執(zhí)行不同與DML語句和查詢語句的執(zhí)行，這是因為DDL語句執(zhí)行成功后需要對數(shù)據字典數(shù)據進行修改。對于DDL語句，語句的分析階段包括：分析、查找數(shù)據字典信息和執(zhí)行。 事務管理語句、會話管理語句、系統(tǒng)管理語句只有分析與執(zhí)行階段，為了重新執(zhí)行該語句，會重新分析與執(zhí)行該語句。,事務控制,必須定義事務，這樣在一個邏輯單元中的所有工作可以同時被提交或回滾，保證了數(shù)據的一致性。一個事務應該由邏輯單元中的所有必須部分組成，不應該多一個，也不應該少一個。 在事務開始和結束的這段時間內，所有被引用表中的數(shù)據都應該在一致的狀態(tài)(或可以被回溯到一致的狀態(tài)) 事務應該只包含可以對數(shù)據進行一致更改(one consistent change to the data)的SQL語句 Eg:在兩個帳號之間的轉帳(這是一個事務或邏輯工作單元)，應該包含從一個帳號中借錢(由一個SQL完成)，然后將借的錢存入另一個帳號(由另一個SQL完成)。這2個操作作為一個邏輯單元，應該同時成功或同時失敗。其它不相關的操作，如向一個帳戶中存錢，不應該包含在這個轉帳事務中。 在設計應用時，除需要決定哪種類型的操作組成一個事務外，還需要決定使用BEGIN_DISCRETE_TRANSACTIO存儲過程是否對提高小的、非分布式的事務的性能有作用。,目錄,優(yōu)化基礎知識 性能調整綜述 有效的應用設計 SQL語句的處理過程 Oracle的優(yōu)化器 Oracle的執(zhí)行計劃 注意事項,Oracle 優(yōu)化器,2 種類型的優(yōu)化器： 基于規(guī)則的優(yōu)化器 基于代價的優(yōu)化器。 不同之處：取得代價的方法與衡量代價的大小不同。,基于規(guī)則的優(yōu)化器 Rule Based (Heuristic) Optimization(簡稱RBO),基于規(guī)則的優(yōu)化器中采用啟發(fā)式的方法(Heuristic Approach)或規(guī)則(Rules)來生成執(zhí)行計劃。例如，如果一個查詢的where條件(where clause)包含一個謂詞(predicate，其實就是一個判斷條件，如”=”, “”, ””等)，而且該謂詞上引用的列上有有效索引，那么優(yōu)化器將使用索引訪問這個表，而不考慮其它因素，如表中數(shù)據的多少、表中數(shù)據的易變性、索引的可選擇性等。此時數(shù)據庫中沒有關于表與索引數(shù)據的統(tǒng)計性描述，如表中有多上行，每行的可選擇性等。優(yōu)化器也不考慮實例參數(shù)，如multi block i/o、可用排序內存的大小等，所以優(yōu)化器有時就選擇了次優(yōu)化的計劃作為真正的執(zhí)行計劃，導致系統(tǒng)性能不高。 如，對于 select * from emp where deptno = 10。 如果是使用基于規(guī)則的優(yōu)化器，而且deptno列上有有效的索引，則會通過deptno列上的索引來訪問emp表。在絕大多數(shù)情況下，這是比較高效的，但是在一些特殊情況下，使用索引訪問也有比較低效的時候，現(xiàn)舉例說明：,基于規(guī)則的優(yōu)化器 Rule Based (Heuristic) Optimization(簡稱RBO),1) emp表比較小，該表的數(shù)據只存放在幾個數(shù)據塊中。此時使用全表掃描比使用索引訪問emp表反而要好。因為表比較小，極有可能數(shù)據全在內存中，所以此時做全表掃描是最快的。而如果使用索引掃描，需要先從索引中找到符合條件記錄的rowid，然后再一一根據這些rowid從emp中將數(shù)據取出來，在這種條件下，效率就會比全表掃描的效率要差一些。 2) emp表比較大時，而且deptno = 10條件能查詢出表中大部分的數(shù)據如(50%)。如該表共有4000萬行數(shù)據，共放在有500000個數(shù)據塊中，每個數(shù)據塊為8k，則該表共有約4G，則這么多的數(shù)據不可能全放在內存中，絕大多數(shù)需要放在硬盤上。此時如果該查詢通過索引查詢，則是你夢魘的開始。db_file_multiblock_read_count參數(shù)的值200。如果采用全表掃描，則需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引掃描，假設deptno列上的索引都已經cache到內存中，所以可以將訪問索引的開銷忽略不計。因為要讀出4000萬x 50% = 2000萬數(shù)據，假設在讀這2000萬數(shù)據時，有99.9%的命中率，則還是需要20000次I/O,比上面的全表掃描需要的2500次多多了，所以在這種情況下，用索引掃描反而性能會差很多。在這樣的情況下，用全表掃描的時間是固定的，但是用索引掃描的時間會隨著選出數(shù)據的增多使查詢時間相應的延長。,基于代價的優(yōu)化器 Cost Based Optimization(簡稱CBO),Oracle把一個代價引擎(Cost Engine)集成到數(shù)據庫內核中，用來估計每個執(zhí)行計劃需要的代價，該代價將每個執(zhí)行計劃所耗費的資源進行量化，從而CBO可以根據這個代價選擇出最優(yōu)的執(zhí)行計劃。 查詢耗費的資源可以被分成3個基本組成部分：I/O代價、CPU代價、network代價。 I/O代價是將數(shù)據從磁盤讀入內存所需的代價。訪問數(shù)據包括將數(shù)據文件中數(shù)據塊的內容讀入到SGA的數(shù)據高速緩存中，在一般情況下，該代價是處理一個查詢所需要的最主要代價，所以我們在優(yōu)化時，一個基本原則就是降低查詢所產生的I/O總次數(shù)。 CPU代價是處理在內存中數(shù)據所需要的代價，如一旦數(shù)據被讀入內存，則我們在識別出我們需要的數(shù)據后，在這些數(shù)據上執(zhí)行排序(sort)或連接(join)操作，這需要耗費CPU資源。 對于需要訪問跨節(jié)點(即通常說的服務器)數(shù)據庫上數(shù)據的查詢來說，存在network代價，用來量化傳輸操作耗費的資源。查詢遠程表的查詢或執(zhí)行分布式連接的查詢會在network代價方面花費比較大。,判斷當前數(shù)據庫使用何種優(yōu)化器,由optimizer_mode初始化參數(shù)決定的。該參數(shù)可能的取值為：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。 具體解釋如下： RULE為使用RBO優(yōu)化器。 CHOOSE則是根據實際情況，如果數(shù)據字典中包含被引用的表的統(tǒng)計數(shù)據，即引用的對象已經被分析，則就使用CBO優(yōu)化器，否則為RBO優(yōu)化器。 ALL_ROWS為CBO優(yōu)化器使用的第一種具體的優(yōu)化方法，是以數(shù)據的吞吐量為主要目標，以便可以使用最少的資源完成語句。 FIRST_ROWS為優(yōu)化器使用的第二種具體的優(yōu)化方法，是以數(shù)據的響應時間為主要目標，以便快速查詢出開始的幾行數(shù)據。 FIRST_ROWS_[1 | 10 | 100 | 1000] 為優(yōu)化器使用的第三種具體的優(yōu)化方法，讓優(yōu)化器選擇一個能夠把響應時間減到最小的查詢執(zhí)行計劃，以迅速產生查詢結果的前 n 行。該參數(shù)為ORACLE 9I新引入的。 查看命令：show parameter OPTIMIZER_MODE,目錄,優(yōu)化基礎知識 性能調整綜述 有效的應用設計 SQL語句的處理過程 Oracle的優(yōu)化器 Oracle的執(zhí)行計劃 注意事項,內容提綱,Oracle的執(zhí)行計劃 優(yōu)化定義 概念分析 執(zhí)行計劃分析 優(yōu)化器選擇 訪問數(shù)據存取方法 表間連接 如何產生執(zhí)行計劃 如何分析執(zhí)行計劃 如何干預執(zhí)行計劃 綜合案例分析,優(yōu)化定義,什么是優(yōu)化： 優(yōu)化是選擇最有效的執(zhí)行計劃來執(zhí)行SQL語句的過程，這是在處理任何數(shù)據的語句（SELECT,INSERT,UPDATE或DELETE）中的一個重要步驟。對Oracle來說，執(zhí)行這樣的語句有許多不同的方法，譬如說，將隨著以什么順序訪問哪些表或索引的不同而不同。所使用的執(zhí)行計劃可以決定語句能執(zhí)行得有多快。Oracle中稱之為優(yōu)化器（Optimizer）的組件用來選擇這種它認為最有效的執(zhí)行計劃。,內容提綱,Oracle的執(zhí)行計劃 優(yōu)化定義 概念分析 執(zhí)行計劃分析 優(yōu)化器選擇 訪問數(shù)據存取方法 表間連接 如何產生執(zhí)行計劃 如何分析執(zhí)行計劃 如何干預執(zhí)行計劃 綜合案例分析,概念分析,共享SQL語句： 為了不重復解析相同的SQL語句(因為解析操作比較費資源，會導致性能下降)，在第一次解析之后，ORACLE將SQL語句及解析后得到的執(zhí)行計劃存放在內存中。這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數(shù)據庫用戶共享。因此，當你執(zhí)行一個SQL語句(有時被稱為一個游標)時，如果該語句和之前的執(zhí)行過的某一語句完全相同，并且之前執(zhí)行的該語句與其執(zhí)行計劃仍然在內存中存在，則ORACLE就不需要再進行分析，直接得到該語句的執(zhí)行路徑。ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并大大節(jié)省了內存的使用。使用這個功能的關鍵是將執(zhí)行過的語句盡可能放到內存中，所以這要求有大的共享池(通過設置shared buffer pool參數(shù)值)和盡可能的使用綁定變量的方法執(zhí)行SQL語句。,概念分析,Rowid的概念： rowid是一個偽列，既然是偽列，那么這個列就不是用戶定義，而是系統(tǒng)自己給加上的。對每個表都有一個rowid的偽列，但是表中并不物理存儲ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除該列，也不能對該列的值進行修改、插入。一旦一行數(shù)據插入數(shù)據庫，則rowid在該行的生命周期內是唯一的，即即使該行產生行遷移，行的rowid也不會改變。,概念分析,為什么使用Rowid： rowid對訪問一個表中的給定的行提供了最快的訪問方法，通過ROWID可以直接定位到相應的數(shù)據塊上，然后將其讀到內存。我們創(chuàng)建一個索引時，該索引不但存儲索引列的值，而且也存儲索引值所對應的行的ROWID，這樣我們通過索引快速找到相應行的ROWID后，通過該ROWID，就可以迅速將數(shù)據查詢出來。這也就是我們使用索引查詢時，速度比較快的原因。 在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER構成。隨著oracle8中對象概念的擴展，ROWID發(fā)生了變化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER構成。利用DBMS_ROWID可以將rowid分解成上述的各部分，也可以將上述的各部分組成一個有效的rowid。,概念分析,Recursive SQL概念 ： 為了執(zhí)行用戶發(fā)出的一個sql語句，Oracle必須執(zhí)行一些額外的語句，我們將這些額外的語句稱之為‘recursive calls’或‘recursive SQL statements’。如當一個DDL語句發(fā)出后，ORACLE總是隱含的發(fā)出一些recursive SQL語句，來修改數(shù)據字典信息，以便用戶可以成功的執(zhí)行該DDL語句。當需要的數(shù)據字典信息沒有在共享內存中時，經常會發(fā)生Recursive calls，這些Recursive calls會將數(shù)據字典信息從硬盤讀入內存中。用戶不必關心這些recursive SQL語句的執(zhí)行情況，在需要的時候，ORACLE會自動的在內部執(zhí)行這些語句。當然DML語句與SELECT都可能引起recursive SQL。簡單的說，我們可以將觸發(fā)器視為recursive SQL。,概念分析,Row Source(行源) ： 用在查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數(shù)據的集合；也可以是表的部分行數(shù)據的集合；也可以為對上2個row source進行連接操作(如join連接)后得到的行數(shù)據集合。 Predicate(謂詞) ： 一個查詢中的WHERE限制條件。,概念分析,Driving Table(驅動表)： 該表又稱為外層表(OUTER TABLE)。這個概念用于嵌套與HASH連接中。如果該row source返回較多的行數(shù)據，則對所有的后續(xù)操作有負面影響。注意此處雖然翻譯為驅動表，但實際上翻譯為驅動行源(driving row source)更為確切。一般說來，是應用查詢的限制條件后，返回較少行源的表作為驅動表，所以如果一個大表在WHERE條件有限制條件(如等值限制)，則該大表作為驅動表也是合適的，所以并不是只有較小的表可以作為驅動表，正確說法應該為應用查詢的限制條件后，返回較少行源的表作為驅動表。在執(zhí)行計劃中，應該為靠上的那個row source，后面會給出具體說明。在我們后面的描述中，一般將該表稱為連接操作的row source 1。,概念分析,Probed Table(被探查表)： 該表又稱為內層表(INNER TABLE)。在我們從驅動表中得到具體一行的數(shù)據后，在該表中尋找符合連接條件的行。所以該表應當為大表(實際上應該為返回較大row source的表)且相應的列上應該有索引。在我們后面的描述中，一般將該表稱為連接操作的row source 2。,概念分析,組合索引(concatenated index)： 由多個列構成的索引，如create index idx_emp on emp(col1, col2, col3, ……)，則我們稱idx_emp索引為組合索引。在組合索引中有一個重要的概念：引導列(leading column)，在上面的例子中，col1列為引導列。當我們進行查詢時可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，這樣的限制條件都會使用索引，但是”where col2 = ? ”查詢就不會使用該索引。所以限制條件中包含先導列時，該限制條件才會使用該組合索引。,概念分析,可選擇性(selectivity)： 比較一下列中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。如果該列的”唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在可選擇性高的列上進行查詢時，返回的數(shù)據就較少，比較適合使用索引查詢。,內容提綱,Oracle的執(zhí)行計劃 優(yōu)化定義 概念分析 執(zhí)行計劃分析 優(yōu)化器選擇 訪問數(shù)據存取方法 表間連接 如何產生執(zhí)行計劃 如何分析執(zhí)行計劃 如何干預執(zhí)行計劃 綜合案例分析,執(zhí)行計劃分析,為了執(zhí)行語句，Oracle可能必須實現(xiàn)許多步驟。這些步驟中的每一步可能是從數(shù)據庫中物理檢索數(shù)據行，或者用某種方法準備數(shù)據行，