【正文】 OTLP的主要目標(biāo)是可用性、速度、并發(fā)性和可恢復(fù)性。 – 當(dāng)設(shè)計(jì)這類系統(tǒng)時，必須確保大量的并發(fā)用戶不能干擾系統(tǒng)的性能。還需要避免使用過量的索引與 cluster 表，因?yàn)檫@些結(jié)構(gòu)會使插入和更新操作變慢。 有效的應(yīng)用設(shè)計(jì)? 將最常用的應(yīng)用分為 2種類型： 聯(lián)機(jī)事務(wù)處理類型 (OLTP)，決策支持系統(tǒng) (DSS)。? 決策支持 (DSS) – 該類型的應(yīng)用將大量信息進(jìn)行提取形成報(bào)告，協(xié)助決策者作出正確的判斷。典型情況是：決策支持系統(tǒng)將 OLTP應(yīng)用收集的大量數(shù)據(jù)進(jìn)行查詢。典型的應(yīng)用為客戶行為分析系統(tǒng) (超市，保險(xiǎn)等 )。 – 決策支持的關(guān)鍵目標(biāo)是速度、精確性和可用性。 – 該種類型的設(shè)計(jì)往往與 OLTP設(shè)計(jì)的理念背道而馳，一般建議使用數(shù)據(jù)冗余、大量索引、 cluster table、并行查詢等。 – 近年來，該類型的應(yīng)用逐漸與 OLAP、數(shù)據(jù)倉庫緊密的聯(lián)系在一起，形成的一個新的應(yīng)用方向。 目錄目錄1. 優(yōu)化基礎(chǔ)知識2. 性能調(diào)整綜述3.有效的應(yīng)用設(shè)計(jì)4. SQL語句的處理過程5. Oracle的優(yōu)化器6. Oracle的執(zhí)行計(jì)劃7. 注意事項(xiàng)SQL語句的處理過程1查詢語句處理 2DML語句處理 (insert,update,delete)3DDL語句處理 (create..,drop..,alter..,)4事務(wù)控制 (mit,rollback)SQL語句執(zhí)行過程如圖： 列出了處理和運(yùn)行一個sql語句的需要各個重要階段。在某些情況下， Oracle運(yùn)行 sql的過程可能與下面列出的各個階段的順序有所不同。如 DEFINE階段可能在 FETCH階段之前，這主要依賴你如何書寫代碼DML語句的處理? 假設(shè)你使用 Pro*C程序來為指定部門的所有職員增加工資。程序已經(jīng)連到正確的用戶，你可以在你的程序中嵌入如下的 SQL語句： EXECSQLUPDATEemployeesSETsalary=*salaryWHEREdepartment_id=:var_department_id。var_department_id是程序變量，里面包含部門號，我們要修改該部門的職員的工資。當(dāng)這個 SQL語句執(zhí)行時，使用該變量的值。 DML語句的處理? 每種類型的語句都需要如下階段： – ?第 1步 :CreateaCursor創(chuàng)建游標(biāo) – ?第 2步 :ParsetheStatement分析語句 – ?第 5步 :BindAnyVariables綁定變量 – ?第 7步 :RuntheStatement運(yùn)行語句 – ?第 9步 :ClosetheCursor關(guān)閉游標(biāo) ? 如果使用了并行功能，還會包含下面這個階段： – ?第 6步 :ParallelizetheStatement并行執(zhí)行語句 ? 如果是查詢語句，則需要幾個額外的步驟，如圖所示： – ?第 3步 :DescribeResultsofaQuery描述查詢的結(jié)果集 – ?第 4步 :DefineOutputofaQuery定義查詢的輸出數(shù)據(jù) – ?第 8步 :FetchRowsofaQuery取查詢出來的行 以上語句處理步驟解釋? 第 1步 :創(chuàng)建游標(biāo) (CreateaCursor)– 由程序接口調(diào)用創(chuàng)建一個游標(biāo)（ cursor）。任何 SQL語句都會創(chuàng)建它，特別在運(yùn)行 DML語句時，都是自動創(chuàng)建游標(biāo)的，不需要開發(fā)人員干預(yù)。多數(shù)應(yīng)用中，游標(biāo)的創(chuàng)建是自動的。然而，在預(yù)編譯程序 (pro*c)中游標(biāo)的創(chuàng)建，可能是隱含的，也可能顯式的創(chuàng)建。在存儲過程中也是這樣的。 ? 第 2步 :分析語句 (ParsetheStatement)– 在語法分析期間， SQL語句從用戶進(jìn)程傳送到 Oracle，SQL語句經(jīng)語法分析后， SQL語句本身與分析的信息都被裝入到共享 SQL區(qū)。在該階段中，可以解決許多類型的錯誤。 以上語句處理步驟解釋語法分析分別執(zhí)行下列操作： 252。翻譯 SQL語句，驗(yàn)證它是合法的語句，即書寫正確 252。實(shí)現(xiàn)數(shù)據(jù)字典的查找，以驗(yàn)證是否符合表和列的定義 252。在所要求的對象上獲取語法分析鎖，使得在語句的語法分析過程中不改變這些對象的定義 252。驗(yàn)證為存取所涉及的模式對象所需的權(quán)限是否滿足 252。決定此語句最佳的執(zhí)行計(jì)劃 252。將它裝入共享 SQL區(qū) 252。對分布的語句來說，把語句的全部或部分路由到包含所涉及數(shù)據(jù)的遠(yuǎn)程節(jié)點(diǎn)* 以上任何一步出錯誤，都將導(dǎo)致語句報(bào)錯，中止執(zhí)行。以上語句處理步驟解釋? 只有在共享池中不存在等價(jià) SQL語句的情況下，才對 SQL語句作語法分析。在這種情況下，數(shù)據(jù)庫內(nèi)核重新為該語句分配新的共享 SQL區(qū)，并對語句進(jìn)行語法分析。進(jìn)行語法分析需要耗費(fèi)較多的資源，所以要盡量避免進(jìn)行語法分析，這是優(yōu)化的技巧之一。 ? 語法分析階段包含了不管此語句將執(zhí)行多少次，而只需分析一次的處理要求。 Oracle只對每個 SQL語句翻譯一次，在以后再次執(zhí)行該語句時，只要該語句還在共享 SQL區(qū)中，就可以避免對該語句重新進(jìn)行語法分析，也就是此時可以直接使用其對應(yīng)的執(zhí)行計(jì)劃對數(shù)據(jù)進(jìn)行存取。這主要是通過綁定變量 (bindvariable)實(shí)現(xiàn)的，也就是我們常說的共享 SQL，后面會給出共享 SQL的概念。 ? 雖然語法分析驗(yàn)證了 SQL語句的正確性，但語法分析只能識別在 SQL語句執(zhí)行之前所能發(fā)現(xiàn)的錯誤 (如書寫錯誤、權(quán)限不足等 )。因此，有些錯誤通過語法分析是抓不到的。例如，在數(shù)據(jù)轉(zhuǎn)換中的錯誤或在數(shù)據(jù)中的錯（如企圖在主鍵中插入重復(fù)的值）以及死鎖等均是只有在語句執(zhí)行階段期間才能遇到和報(bào)告的錯誤或情況。 查詢 語句的處理查詢與其它類型的 SQL語句不同，因?yàn)樵诔晒?zhí)行后作為結(jié)果將返回?cái)?shù)據(jù)。第 3步 : 描述查詢結(jié)果 (Describe Results of a Query) 描述階段只有在查詢結(jié)果的各個列是未知時才需要；例如，當(dāng)查詢由用戶交互地輸入需要輸出的列名。在這種情況要用描述階段來決定查詢結(jié)果的特征（數(shù)據(jù)類型，長度和名字）。 第 4步 : 定義查詢的輸出數(shù)據(jù) (Define Output of a Query) 在查詢的定義階段，你指定與查詢出的列值對應(yīng)的接收變量的位置、大小和數(shù)據(jù)類型，這樣我們通過接收變量就可以得到查詢結(jié)果。如果必要的話， Oracle會自動實(shí)現(xiàn)數(shù)據(jù)類型的轉(zhuǎn)換。這是將接收變量的類型與對應(yīng)的列類型相比較決定的。 查詢 語句的處理? 第 5步 :綁定變量 (BindAnyVariables)– Oracle知道了 SQL語句的意思，但仍沒有足夠的信息用于執(zhí)行該語句。 Oracle需要得到在語句中列出的所有變量的值。在該例中， Oracle需要得到對 department_id列進(jìn)行限定的值。得到這個值的過程就叫綁定變量 (bindingvariables)– 此過程稱之為將變量值捆綁進(jìn)來。程序必須指出可以找到該數(shù)值的變量名（該變量被稱為捆綁變量，變量名實(shí)質(zhì)上是一個內(nèi)存地址，相當(dāng)于指針）。應(yīng)用的最終用戶可能并沒有發(fā)覺他們正在指定捆綁變量，因?yàn)?Oracle的程序可能只是簡單地指示他們輸入新的值，其實(shí)這一切都在程序中自動做了。– 因?yàn)槟阒付俗兞棵谀阍俅螆?zhí)行之前無須重新捆綁變量。你可以改變綁定變量的值，而 Oracle在每次執(zhí)行時，僅僅使用內(nèi)存地址來查找此值。– 如果 Oracle需要實(shí)現(xiàn)自動數(shù)據(jù)類型轉(zhuǎn)換的話（除非它們是隱含的或缺省的），你還必須對每個值指定數(shù)據(jù)類型和長度。關(guān)于這些信息可以參考 oracle的相關(guān)文檔，如 OracleCallInterfaceProgrammer39。sGuide查詢 語句的處理? 第 6步 :并行執(zhí)行語句 (ParallelizetheStatement)– ORACLE可以在 SELECTs,INSERTs,UPDATEs,MERGEs,DELETEs語句中執(zhí)行相應(yīng)并行查詢操作，對某些 DDL操作，如創(chuàng)建索引、用子查詢創(chuàng)建表、在分區(qū)表上的操作，可以執(zhí)行并行操作。并行化可導(dǎo)致多個服務(wù)器進(jìn)程 (oracleserverprocesses)為同一個 SQL語句工作，使該 SQL語句可以快速完成，但是會耗費(fèi)更多的資源，所以除非很有必要，否則不要使用并行查詢。? 第 7步 : 執(zhí)行語句 (Run the Statement)– 此時， Oracle擁有所有需要的信息與資源，可以真正運(yùn)行 SQL語句了。如果該語句為 SELECT查詢或 INSERT語句，則不需要鎖定任何行，因沒有數(shù)據(jù)需要被改變。如果語句為 UPDATE或 DELETE語句，則該語句影響的所有行都被鎖定，防止該用戶提交或回滾之前，別的用戶對這些數(shù)據(jù)進(jìn)行修改。這保證了數(shù)據(jù)的一致性。– 對于某些語句，你可以指定執(zhí)行的次數(shù)，這稱為批處理 (array processing)。指定執(zhí)行 N次，則綁定變量與定義變量被定義為大小為 N的數(shù)組的開始位置，這種方法可以減少網(wǎng)絡(luò)開銷，也是優(yōu)化的技巧之一。查詢 語句的處理第 8步 : 取出查詢的行 (Fetch Rows of a Query)? 在 fetch階段，行數(shù)據(jù)被取出來，每個后續(xù)的存取操作檢索結(jié)果集中的下一行數(shù)據(jù)，直到最后一行被取出來。上面提到過，批量的 fetch是優(yōu)化的技巧之一。第 9步 : 關(guān)閉游標(biāo) (Close the Cursor)– SQL語句處理的最后一個階段就是關(guān)閉游標(biāo) 。DDL語句的處理? DDL語句的執(zhí)行不同與 DML語句和查詢語句的執(zhí)行，這是因?yàn)?DDL語句執(zhí)行成功后需要對數(shù)據(jù)字典數(shù)據(jù)進(jìn)行修改。對于 DDL語句，語句的分析階段包括 ： 分析、查找數(shù)據(jù)字典信息和執(zhí)行 。 ? 事務(wù)管理語句、會話管理語句、系統(tǒng)管理語句只有分析與執(zhí)行階段，為了重新執(zhí)行該語句，會重新分析與執(zhí)行該語句。 事務(wù)控制? 必須定義事務(wù)，這樣在一個邏輯單元中的所有工作可以同時被提交或回滾，保證了數(shù)據(jù)的一致性。一個事務(wù)應(yīng)該由邏輯單元中的所有必須部分組成，不應(yīng)該多一個，也不應(yīng)該少一個。 – 在事務(wù)開始和結(jié)束的這段時間內(nèi)，所有被引用表中的數(shù)據(jù)都應(yīng)該在一致的狀態(tài) (或可以被回溯到一致的狀態(tài)) – 事務(wù)應(yīng)該只包含可以對數(shù)據(jù)進(jìn)行一致更改 (one consistent change to the data)的 SQL語句 ? Eg:在兩個帳號之間的轉(zhuǎn)帳 (這是一個事務(wù)或邏輯工作單元)，應(yīng)該包含從一個帳號中借錢 (由一個 SQL完成 )，然后將借的錢存入另一個帳號 (由另一個 SQL完成 )。這 2個操作作為一個邏輯單元，應(yīng)該同時成功或同時失敗。其它不相關(guān)的操作，如向一個帳戶中存錢，不應(yīng)該包含在這個轉(zhuǎn)帳事務(wù)中。 ? 在設(shè)計(jì)應(yīng)用時，除需要決定哪種類型的操作組成一個事務(wù)外，還需要決定使用 BEGIN_DISCRETE_TRANSACTIO存儲過程是否對提高小的、非分布式的事務(wù)的性能有作用。 目錄目錄1. 優(yōu)化基礎(chǔ)知識2. 性能調(diào)整綜述3.有效的應(yīng)用設(shè)計(jì)4. SQL語句的處理過程5. Oracle的優(yōu)化器6. Oracle的執(zhí)行計(jì)劃7. 注意事項(xiàng)Oracle優(yōu)化器? 2 種類型的優(yōu)化器：–基于規(guī)則的優(yōu)化器–基于代價(jià)的優(yōu)化器。不同之處：取得代價(jià)的方法與衡量代價(jià)的大小不同?；谝?guī)則的優(yōu)化器 RuleBased(Heuristic)Optimization(簡稱RBO)? 基于規(guī)則的優(yōu)化器中采用啟發(fā)式的方法 (HeuristicApproach)或規(guī)則(Rules)來生成執(zhí)行計(jì)劃。例如，如果一個查詢的 where條件 (whereclause)包含一個謂詞 (predicate，其實(shí)就是一個判斷條件，如 ”=”,“”,””等 )，而且該謂詞上引用的列上有有效索引，那么優(yōu)化器將使用索引訪問這個表，而不考慮其它因素，如表中數(shù)據(jù)的多少、表中數(shù)據(jù)的易變性、索引的可選擇性等。此時數(shù)據(jù)庫中沒有關(guān)于表與索引數(shù)據(jù)的統(tǒng)計(jì)性描述，如表中有多上行，每行的可選擇性等。優(yōu)化器也不考慮實(shí)例參數(shù)，如 multiblocki/o、可用排序內(nèi)存的大小等，所以優(yōu)化器有時就選擇了次優(yōu)化的計(jì)劃作為真正的執(zhí)行計(jì)劃，導(dǎo)致系統(tǒng)性能不高。? 如，對于 select*fromempwheredeptno=10。如果是使用基于規(guī)則的優(yōu)化器，而且 deptno列上有有效的索引，則會通過 deptno列上的索引來訪問 emp表。在絕大多數(shù)情況下，這是比較高效的，但是在一些特殊情況下，使用索引訪問也有比較低效的時候，現(xiàn)舉例說明： 基于規(guī)則的優(yōu)化器 RuleBased(Heuristic)Optimization(簡稱RBO)? 1)emp表比較小，該表的數(shù)據(jù)只存放在幾個數(shù)據(jù)塊中。此時使用全表掃描比使用索引訪問 emp表反而要好。因?yàn)楸肀容^小，極有可能數(shù)據(jù)全在內(nèi)存中，所以此時做全表掃描是最快的。而如果使用索引掃描，需要先從索引中找到符合條件記錄的 rowid，然后再一一根據(jù)這些 rowid從 emp中將數(shù)據(jù)取出來，在這種條件下，效率就會比全表掃描的效率要差一些。 ? 2)emp表比較大時，而且 deptno=10條件能查詢出表中大部分的數(shù)據(jù)如 (50%)。如該表共有 4000萬行數(shù)據(jù)，共放在有 500000個數(shù)據(jù)塊中，每個數(shù)據(jù)塊為 8k，則該表共有約 4G，則這么多的數(shù)據(jù)不可能全放在內(nèi)存中，絕大多數(shù)需要放在硬盤上。此時如果該查詢通過索引查詢，則是你夢魘的開始。db_file_multiblock_read_count參數(shù)的值 200。如果采用全表掃描，則需要500000/db_file_multiblock_read_count=500000/200=2500次 I/O。但是如果采用索引掃描，假設(shè) deptno列上的索引都已經(jīng) cache到內(nèi)存中，所以可以將訪問索引的開銷忽略不計(jì)。因?yàn)橐x出 4000萬 x50%=2023萬數(shù)據(jù)，假設(shè)在讀這 2023萬數(shù)據(jù)時，有 %的命中率，則還是需要 20230次 I/O,比上面的全表掃描需要的 2500次多多了，所以在這種情況下，用索引掃描反而性能會差很多。在這樣的情況下，用全表掃描的時間是固定的，但是用索引掃描的時間會隨著選出數(shù)據(jù)的增多使查詢時間相應(yīng)的延長。 基于代價(jià)的優(yōu)化器 CostBasedOptimization(簡稱 CBO)? Oracle把一個代價(jià)引擎 (Cost Engine)集成到數(shù)據(jù)庫內(nèi)核中，用來估計(jì)每個執(zhí)行計(jì)