【正文】 化器(Optimizer)來(lái)完成的 Oracle的優(yōu)化器共有兩種的優(yōu)化方式,即基于規(guī)則的優(yōu)化方 式(RuleBased Optimization,簡(jiǎn)稱(chēng)為RBO)和基于代價(jià)的優(yōu) 化方式(CostBased Optimization,簡(jiǎn)稱(chēng)為CBO)。優(yōu)化器在判斷是否用這種方式時(shí),主要參照的是表及索引的統(tǒng)計(jì)信息， 很多的時(shí)侯過(guò)期統(tǒng)計(jì)信息會(huì)令優(yōu)化器做出一個(gè)錯(cuò)誤的執(zhí)行計(jì)劃在 Oracle8及以后的版本,Oracle推薦用CBO的方式。 以SYSTEM用戶(hù)創(chuàng)建PLAN_TABLE表 Sql $ORACLE_HOME/rdbms/admin/utlxplan.sql Sql create public synonym plan_table for plan_table。ACCOUNTING39。SALES39。 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 39。 4 3 INDEX (UNIQUE SCAN) OF 39。 事實(shí)上的結(jié)果是：數(shù)據(jù)庫(kù)管理員對(duì)調(diào)整負(fù)有全部或主要的責(zé)任。,誰(shuí)來(lái)調(diào)整系統(tǒng),與系統(tǒng)涉及的每個(gè)人都在調(diào)整過(guò)程中起某些作用，當(dāng)上面提及的那些人員傳達(dá)了系統(tǒng)的特性并提供了它們的資料，調(diào)整就能相對(duì)的容易和更快一些。 調(diào)整并不是數(shù)據(jù)庫(kù)管理員的專(zhuān)利，相反大部分應(yīng)該是設(shè)計(jì)人員和開(kāi)發(fā)人員的工作，這就需要設(shè)計(jì)人員和開(kāi)發(fā)人員具體必要的數(shù)據(jù)庫(kù)知識(shí)，這樣才能組成一個(gè)高效的團(tuán)隊(duì)，然而事實(shí)上往往并非如此。 應(yīng)用設(shè)計(jì)人員需要在設(shè)計(jì)階段設(shè)置應(yīng)用的性能期望值。在設(shè)計(jì)期間的調(diào)整能以最低的代價(jià)給你最大的收益。在開(kāi)發(fā)期間，你應(yīng)測(cè)試應(yīng)用研究，確定在采 取應(yīng)用之前是否滿(mǎn)足設(shè)計(jì)的性能目標(biāo)。如果不可能買(mǎi)，你可能要限制系統(tǒng)的并發(fā)性，來(lái)獲取所需的性能。,SQL 調(diào)整的目標(biāo),去掉不必要的大型表的全表掃描。 以上目標(biāo)任務(wù)將占據(jù)SQL調(diào)整90%以上的工作。 “優(yōu)化器”討論了Oracle的查詢(xún)優(yōu)化器，以及如何寫(xiě)語(yǔ)句以獲取最快的結(jié)果。 除非你的應(yīng)用有特殊的需要，否則使用缺省的Oracle鎖。 分布你的數(shù)據(jù)，使得一個(gè)節(jié)點(diǎn)使用的數(shù)據(jù)本地存貯在該節(jié)點(diǎn)中。 當(dāng)設(shè)計(jì)這類(lèi)系統(tǒng)時(shí)，必須確保大量的并發(fā)用戶(hù)不能干擾系統(tǒng)的性能。典型情況是：決策支持系統(tǒng)將OLTP應(yīng)用收集的大量數(shù)據(jù)進(jìn)行查詢(xún)。 近年來(lái)，該類(lèi)型的應(yīng)用逐漸與OLAP、數(shù)據(jù)倉(cāng)庫(kù)緊密的聯(lián)系在一起，形成的一個(gè)新的應(yīng)用方向。程序已經(jīng)連到正確的用戶(hù)，你可以在你的程序中嵌入如下的SQL語(yǔ)句： EXEC SQL UPDATE employees SET salary = 1.10 * salary WHERE department_id = :var_department_id。任何SQL語(yǔ)句都會(huì)創(chuàng)建它，特別在運(yùn)行DML語(yǔ)句時(shí)，都是自動(dòng)創(chuàng)建游標(biāo)的，不需要開(kāi)發(fā)人員干預(yù)。 第2步:分析語(yǔ)句(Parse the Statement) 在語(yǔ)法分析期間，SQL語(yǔ)句從用戶(hù)進(jìn)程傳送到Oracle，SQL語(yǔ)句經(jīng)語(yǔ)法分析后，SQL語(yǔ)句本身與分析的信息都被裝入到共享SQL區(qū)。在這種情況下，數(shù)據(jù)庫(kù)內(nèi)核重新為該語(yǔ)句分配新的共享SQL區(qū)，并對(duì)語(yǔ)句進(jìn)行語(yǔ)法分析。這主要是通過(guò)綁定變量(bind variable)實(shí)現(xiàn)的，也就是我們常說(shuō)的共享SQL，后面會(huì)給出共享SQL的概念。,查詢(xún) 語(yǔ)句的處理,查詢(xún)與其它類(lèi)型的SQL語(yǔ)句不同，因?yàn)樵诔晒?zhí)行后作為結(jié) 果將返回?cái)?shù)據(jù)。如果必要的話(huà)，Oracle會(huì)自動(dòng)實(shí)現(xiàn)數(shù)據(jù)類(lèi)型的轉(zhuǎn)換。在該例中，Oracle需要得到對(duì)department_id列進(jìn)行限定的值。 因?yàn)槟阒付俗兞棵谀阍俅螆?zhí)行之前無(wú)須重新捆綁變量。s Guide,查詢(xún) 語(yǔ)句的處理,第6步: 并行執(zhí)行語(yǔ)句(Parallelize the Statement ) ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs語(yǔ)句中執(zhí)行相應(yīng)并行查詢(xún)操作，對(duì)某些DDL操作，如創(chuàng)建索引、用子查詢(xún)創(chuàng)建表、在分區(qū)表上的操作，可以執(zhí)行并行操作。如果語(yǔ)句為UPDATE或DELETE語(yǔ)句，則該語(yǔ)句影響的所有行都被鎖定，防止該用戶(hù)提交或回滾之前，別的用戶(hù)對(duì)這些數(shù)據(jù)進(jìn)行修改。,查詢(xún) 語(yǔ)句的處理,第8步: 取出查詢(xún)的行(Fetch Rows of a Query) 在fetch階段，行數(shù)據(jù)被取出來(lái)，每個(gè)后續(xù)的存取操作檢索結(jié)果集中的下一行數(shù)據(jù)，直到最后一行被取出來(lái)。對(duì)于DDL語(yǔ)句，語(yǔ)句的分析階段包括：分析、查找數(shù)據(jù)字典信息和執(zhí)行。 在事務(wù)開(kāi)始和結(jié)束的這段時(shí)間內(nèi)，所有被引用表中的數(shù)據(jù)都應(yīng)該在一致的狀態(tài)(或可以被回溯到一致的狀態(tài)) 事務(wù)應(yīng)該只包含可以對(duì)數(shù)據(jù)進(jìn)行一致更改(one consistent change to the data)的SQL語(yǔ)句 Eg:在兩個(gè)帳號(hào)之間的轉(zhuǎn)帳(這是一個(gè)事務(wù)或邏輯工作單元)，應(yīng)該包含從一個(gè)帳號(hào)中借錢(qián)(由一個(gè)SQL完成)，然后將借的錢(qián)存入另一個(gè)帳號(hào)(由另一個(gè)SQL完成)。,目錄,優(yōu)化基礎(chǔ)知識(shí) 性能調(diào)整綜述 有效的應(yīng)用設(shè)計(jì) SQL語(yǔ)句的處理過(guò)程 Oracle的優(yōu)化器 Oracle的執(zhí)行計(jì)劃 注意事項(xiàng),Oracle 優(yōu)化器,2 種類(lèi)型的優(yōu)化器： 基于規(guī)則的優(yōu)化器 基于代價(jià)的優(yōu)化器。此時(shí)數(shù)據(jù)庫(kù)中沒(méi)有關(guān)于表與索引數(shù)據(jù)的統(tǒng)計(jì)性描述，如表中有多上行，每行的可選擇性等。在絕大多數(shù)情況下，這是比較高效的，但是在一些特殊情況下，使用索引訪問(wèn)也有比較低效的時(shí)候，現(xiàn)舉例說(shuō)明：,基于規(guī)則的優(yōu)化器 Rule Based (Heuristic) Optimization(簡(jiǎn)稱(chēng)RBO),1) emp表比較小，該表的數(shù)據(jù)只存放在幾個(gè)數(shù)據(jù)塊中。 2) emp表比較大時(shí)，而且deptno = 10條件能查詢(xún)出表中大部分的數(shù)據(jù)如(50%)。如果采用全表掃描，則需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。,基于代價(jià)的優(yōu)化器 Cost Based Optimization(簡(jiǎn)稱(chēng)CBO),Oracle把一個(gè)代價(jià)引擎(Cost Engine)集成到數(shù)據(jù)庫(kù)內(nèi)核中，用來(lái)估計(jì)每個(gè)執(zhí)行計(jì)劃需要的代價(jià)，該代價(jià)將每個(gè)執(zhí)行計(jì)劃所耗費(fèi)的資源進(jìn)行量化，從而CBO可以根據(jù)這個(gè)代價(jià)選擇出最優(yōu)的執(zhí)行計(jì)劃。 CPU代價(jià)是處理在內(nèi)存中數(shù)據(jù)所需要的代價(jià)，如一旦數(shù)據(jù)被讀入內(nèi)存，則我們?cè)谧R(shí)別出我們需要的數(shù)據(jù)后，在這些數(shù)據(jù)上執(zhí)行排序(sort)或連接(join)操作，這需要耗費(fèi)CPU資源。該參數(shù)可能的取值為：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。 FIRST_ROWS為優(yōu)化器使用的第二種具體的優(yōu)化方法，是以數(shù)據(jù)的響應(yīng)時(shí)間為主要目標(biāo)，以便快速查詢(xún)出開(kāi)始的幾行數(shù)據(jù)。對(duì)Oracle來(lái)說(shuō)，執(zhí)行這樣的語(yǔ)句有許多不同的方法，譬如說(shuō)，將隨著以什么順序訪問(wèn)哪些表或索引的不同而不同。這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶(hù)共享。,概念分析,Rowid的概念： rowid是一個(gè)偽列，既然是偽列，那么這個(gè)列就不是用戶(hù)定義，而是系統(tǒng)自己給加上的。,概念分析,為什么使用Rowid： rowid對(duì)訪問(wèn)一個(gè)表中的給定的行提供了最快的訪問(wèn)方法，通過(guò)ROWID可以直接定位到相應(yīng)的數(shù)據(jù)塊上，然后將其讀到內(nèi)存。隨著oracle8中對(duì)象概念的擴(kuò)展，ROWID發(fā)生了變化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER構(gòu)成。當(dāng)需要的數(shù)據(jù)字典信息沒(méi)有在共享內(nèi)存中時(shí)，經(jīng)常會(huì)發(fā)生Recursive calls，這些Recursive calls會(huì)將數(shù)據(jù)字典信息從硬盤(pán)讀入內(nèi)存中。,概念分析,Row Source(行源) ： 用在查詢(xún)中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數(shù)據(jù)的集合；也可以是表的部分行數(shù)據(jù)的集合；也可以為對(duì)上2個(gè)row source進(jìn)行連接操作(如join連接)后得到的行數(shù)據(jù)集合。如果該row source返回較多的行數(shù)據(jù)，則對(duì)所有的后續(xù)操作有負(fù)面影響。在我們后面的描述中，一般將該表稱(chēng)為連接操作的row source 1。在我們后面的描述中，一般將該表稱(chēng)為連接操作的row source 2。所以限制條件中包含先導(dǎo)列時(shí)，該限制條件才會(huì)使用該組合索引。,內(nèi)容提綱,Oracle的執(zhí)行計(jì)劃 優(yōu)化定義 概念分析 執(zhí)行計(jì)劃分析 優(yōu)化器選擇 訪問(wèn)數(shù)據(jù)存取方法 表間連接 如何產(chǎn)生執(zhí)行計(jì)劃 如何分析執(zhí)行計(jì)劃 如何干預(yù)執(zhí)行計(jì)劃 綜合案例分析,執(zhí)行計(jì)劃分析,為了執(zhí)行語(yǔ)句，Oracle可能必須實(shí)現(xiàn)許多步驟。如果該列的”唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在組合索引中有一個(gè)重要的概念：引導(dǎo)列(leading column)，在上面的例子中，col1列為引導(dǎo)列。在我們從驅(qū)動(dòng)表中得到具體一行的數(shù)據(jù)后，在該表中尋找符合連接條件的行。一般說(shuō)來(lái)，是應(yīng)用查詢(xún)的限制條件后，返回較少行源的表作為驅(qū)動(dòng)表，所以如果一個(gè)大表在WHERE條件有限制條件(如等值限制)，則該大表作為驅(qū)動(dòng)表也是合適的，所以并不是只有較小的表可以作為驅(qū)動(dòng)表，正確說(shuō)法應(yīng)該為應(yīng)用查詢(xún)的限制條件后，返回較少行源的表作為驅(qū)動(dòng)表。,概念分析,Driving Table(驅(qū)動(dòng)表)： 該表又稱(chēng)為外層表(OUTER TABLE)。當(dāng)然DML語(yǔ)句與SELECT都可能引起recursive SQL。,概念分析,Recursive SQL概念 ： 為了執(zhí)行用戶(hù)發(fā)出的一個(gè)sql語(yǔ)句，Oracle必須執(zhí)行一些額外的語(yǔ)句，我們將這些額外的語(yǔ)句稱(chēng)之為‘recursive calls’或‘recursive SQL statements’。這也就是我們使用索引查詢(xún)時(shí)，速度比較快的原因。不過(guò)你可以像使用其它列那樣使用它，但是不能刪除該列，也不能對(duì)該列的值進(jìn)行修改、插入。ORACLE的這個(gè)功能大大地提高了SQL的執(zhí)行性能并大大節(jié)省了內(nèi)存的使用。Oracle中稱(chēng)之為優(yōu)化器（Optimizer）的組件用來(lái)選擇這種它認(rèn)為最有效的執(zhí)行計(jì)劃。該參數(shù)為ORACLE 9I新引入的。 CHOOSE則是根據(jù)實(shí)際情況，如果數(shù)據(jù)字典中包含被引用的表的統(tǒng)計(jì)數(shù)據(jù)，即引用的對(duì)象已經(jīng)被分析，則就使用CBO優(yōu)化器，否則為RBO優(yōu)化器。查詢(xún)遠(yuǎn)程表的查詢(xún)或執(zhí)行分布式連接的查詢(xún)會(huì)在network代價(jià)方面花費(fèi)比較大。 I/O代價(jià)是將數(shù)據(jù)從磁盤(pán)讀入內(nèi)存所需的代價(jià)。因?yàn)橐x出4000萬(wàn)x 50% = 2000萬(wàn)數(shù)據(jù)，假設(shè)在讀這2000萬(wàn)數(shù)據(jù)時(shí)，有99.9%的命中率，則還是需要20000次I/O,比上面的全表掃描需要的2500次多多了，所以在這種情況下，用索引掃描反而性能會(huì)差很多。此時(shí)如果該查詢(xún)通過(guò)索引查詢(xún)，則是你夢(mèng)魘的開(kāi)始。因?yàn)楸肀容^小，極有可能數(shù)據(jù)全在內(nèi)存中，所以此時(shí)做全表掃描是最快的。 如，對(duì)于 select * from emp where deptno = 10。,基于規(guī)則的優(yōu)化器 Rule Based (Heuristic) Optimization(簡(jiǎn)稱(chēng)RBO),基于規(guī)則的優(yōu)化器中采用啟發(fā)式的方法(Heuristic Approach)或規(guī)則(Rules)來(lái)生成執(zhí)行計(jì)劃。其它不相關(guān)的操作，如向一個(gè)帳戶(hù)中存錢(qián)，不應(yīng)該包含在這個(gè)轉(zhuǎn)帳事務(wù)中。,事務(wù)控制,必須定義事務(wù)，這樣在一個(gè)邏輯單元中的所有工作可以同時(shí)被提交或回滾，保證了數(shù)據(jù)的一致性。 第9步: 關(guān)閉游標(biāo)(Close the Cursor) SQL語(yǔ)句處理的最后一個(gè)階段就是關(guān)閉游標(biāo)。 對(duì)于某些語(yǔ)句，你可以指定執(zhí)行的次數(shù)，這稱(chēng)為批處理(array processing)。 第7步: 執(zhí)行語(yǔ)句(Run the Statement) 此時(shí)，Oracle擁有所有需要的信息與資源，可以真正運(yùn)行SQL語(yǔ)句了。 如果Oracle 需要實(shí)現(xiàn)自動(dòng)數(shù)據(jù)類(lèi)型轉(zhuǎn)換的話(huà)（除非它們是隱含的或缺省的），你還必須對(duì)每個(gè)值指定數(shù)據(jù)類(lèi)型和長(zhǎng)度。程序必須指出可以找到該數(shù)值的變量名（該變量被稱(chēng)為捆綁變量，變量名實(shí)質(zhì)上是一個(gè)內(nèi)存地址，相當(dāng)于指針）。,查詢(xún) 語(yǔ)句的處理,第5步: 綁定變量(Bind Any Variables) Oracle知道了SQL語(yǔ)句的意思，但仍沒(méi)有足夠的信息用于執(zhí)行該語(yǔ)句。在這種情況要用描述階段來(lái)決定查詢(xún)結(jié)果的特征（數(shù)據(jù)類(lèi)型，長(zhǎng)度和名字）。因此，有些錯(cuò)誤通過(guò)語(yǔ)法分析是抓不到的。 語(yǔ)法分析階段包含了不管此語(yǔ)句將執(zhí)行多少次，而只需分析一次的處理要求。,以上語(yǔ)句處理步驟解釋,語(yǔ)法分析分別執(zhí)行下列操作： 翻譯SQL語(yǔ)句，驗(yàn)證它是合法的語(yǔ)句，即書(shū)寫(xiě)正確 實(shí)現(xiàn)數(shù)據(jù)字典的查找，以驗(yàn)證是否符合表和列的定義 在所要求的對(duì)象上獲取語(yǔ)法分析鎖，使得在語(yǔ)句的語(yǔ)法分析過(guò)程中不改變這些對(duì)象的定義 驗(yàn)證為存取所涉及的模式對(duì)象所需的權(quán)限是否滿(mǎn)足 決定此語(yǔ)句最佳的執(zhí)行計(jì)劃 將它裝入共享SQL區(qū) 對(duì)分布的語(yǔ)句來(lái)說(shuō)，把語(yǔ)句的全部或部分路由到包含所涉及數(shù)據(jù)的遠(yuǎn)程節(jié)點(diǎn) * 以上任何一步出錯(cuò)誤，都將導(dǎo)致語(yǔ)句報(bào)錯(cuò)，中止執(zhí)行。然而，在預(yù)編譯程序(pro*c)中游標(biāo)的創(chuàng)建，可能是隱含的，也可能顯式的創(chuàng)建。當(dāng)這個(gè)SQL語(yǔ)句執(zhí)行時(shí)，使用該 變量的值。在某些情況下，Oracle運(yùn)行sql的過(guò)程可能與下面列出的各個(gè)階段的順序有所不同。 決策支持的關(guān)鍵目標(biāo)是速度、精確性和可用性。,有效的應(yīng)用設(shè)計(jì),將最常用的應(yīng)用分為2種類(lèi)型：聯(lián)機(jī)事務(wù)處理類(lèi)型(OLTP)，決策支持系統(tǒng)(DSS)。 聯(lián)機(jī)事務(wù)處理(OLTP) 該類(lèi)型的應(yīng)用是高吞吐量，插入、更新、刪除操作比較多的系統(tǒng)，這些系統(tǒng)以不斷增長(zhǎng)的大容量數(shù)據(jù)為特征，它們提供給成百用戶(hù)同時(shí)存取，典型的OLTP系統(tǒng)是訂票系統(tǒng)，銀行的業(yè)務(wù)系統(tǒng)，訂單系統(tǒng)。 選擇你的數(shù)據(jù)塊的最佳大小。使用存儲(chǔ)過(guò)程。 系統(tǒng)設(shè)計(jì)人員和應(yīng)用開(kāi)發(fā)人員必須了解Oracle的查詢(xún)處理機(jī)制以便寫(xiě)出高效的SQL語(yǔ)句。 校驗(yàn)優(yōu)化索引的使用。 隨著應(yīng)用的越來(lái)越龐大，硬件性能的提高，全面的調(diào)整應(yīng)用逐漸變成代價(jià)高昂的行為，在這樣情況下，要取得最大的投入/效率之比，較好的辦法是調(diào)整應(yīng)用的關(guān)鍵部分，使其達(dá)到比較高的性能，這樣從總體上來(lái)說(shuō)，整個(gè)系統(tǒng)的性能也是比較高的。一旦確定了瓶頸，可能要犧牲一些其它方面的指標(biāo)