解析Oracle 8i/9i的計劃穩定性(轉)

解析Oracle 8i/9i的計劃穩定性(轉)[@more@]

　　由Oralce8.1開始，Oracle增加了一個新的特性就是Stored Outlines，或者稱為Plan Stability（計劃穩定性）。這個特性帶來三個好處。首先，你可以最佳化開銷很大的語句的處理。第二，如果有一些語句Oracle需要花費長時間來最佳化（而不是執行），你可以節省時間並且減少最佳化階段的競爭。最後，它可以讓你選擇使用新的cursor_sharing引數而無需要擔心因此而不採用最佳化的執行路徑。

　　要知道如何使用儲存概要才是最優的，我們首先執行一些極度沒有效率的SQL的儲存過程開始，要注意的是，我們不能修改原始碼（理論上）。

　　我們將看一下如何跟蹤SQL語句，並且檢視它當前在資料庫中的執行計劃，找出一些提示來改進SQL語句的效能，然後再重新執行該SQL語句時，讓Oracle使用我們的提示。

　　在這個示例中，我們將建立一個使用者，在該使用者的模式中建一個表格，並且建立一個儲存過程訪問該表格，我們將在這個儲存過程上使用wrap工具，這樣我們就不能透過反向方式得到原始碼。然後我們將透過該儲存過程來除錯SQL的執行。

　　例子中我們將假定儲存慨要已經在資料庫建立的時候被自動安裝。

　　準備工作

　　建立一個使用者，他的許可權有：create session, create table, create procedure, create any outline, and alter session。以該使用者連線並且執行以下的指令碼來建立一個表格：

　　create table so_demo (n1 number,n2 number,v1 varchar2(10));insert into so_demo values (1,1,'One');create index sd_i1 on so_demo(n1);create index sd_i2 on so_demo(n2);analyze table so_demo compute statistics;

　　接著需要編碼來建立一個儲存過程訪問該表格。建立一個稱為c_proc.sql的指令碼，如下：

　　create or replace procedure get_value (i_n1 in number,i_n2 in number,io_v1 out varchar2)asbeginselect v1into io_v1from so_demowhere n1 = i_n1and n2 = i_n2;end;/

　　當然，也可以直接執行這個指令碼來建立該過程--不過，為了更有效果，轉到作業系統的命令列並且執行以下命令：

　　wrap iname=c_proc.sql

　　響應是：

　　Processing c_proc.sql to c_proc.plb

　　這裡不是透過執行c_proc.sql指令碼來產生該過程，而是執行看不到原始碼的c_proc.plb指令碼，你將會發現在user_source的檢視中找不到我們的SQL語句。

　　這個應用的作用是什麼？

　　現在我們已經產生了一個模擬的應用，我們就可以執行它，開啟sql_trace，看看有什麼事情發生。我們將會發現這個SQL執行一個全表搜尋來得到請求的資料。

　　在這個測試中，全表檢索或許是最有效的方式--不過讓我們假定已經證明使用一個單列的索引和and-equal選項才是最佳的執行路徑時，我們可以怎樣修改呢（無需在程式碼中加入提示）？

　　透過儲存概要，答案是簡單的。要達到我下面所做的事情實際上有好幾種方法，因此不要認為這是唯一的做法。Oracle一直改進它的特性以方便使用，這裡所講的技術或許在未來的一個版本中就會消失。

　　你想該應用做什麼？

　　要令Oracle如我們所想的那樣運作，有三個階段：

　　. 啟動一個新的session（連線），然後重新執行該過程，首先告訴Oracle我們要跟蹤將要執行的SQL語句和該SQL使用的路徑。這裡說的"路徑"就是我們儲存概要的第一個例子。

　　. 為有問題的SQL語句建立更好的儲存概要，然後用好的代替有問題的。

　　. 啟動一個新的session，並且告訴Oracle在看到匹配的SQL時，開始使用新的儲存概要，而不是使用通常的最佳化方法來執行；然後重新執行該過程。

　　我們必須停止和啟動新的session來確保pl/sql緩衝中的遊標（cursors）並不是保持開啟的。儲存概要只在一個遊標被分析的時候產生和（或）應用，因此我們必須要確認以前存在的類似遊標是關閉的。

　　啟動一個session並且執行以下的命令：

　　alter session set create_stored_outlines = demo;

　　然後執行一小段匿名的程式碼塊來執行該過程，例如：

　　declarem_value varchar2(10);beginget_value(1, 1, m_value);end;/

　　然後停止收集執行的路徑（否則以下你執行的一些SQL也會放到儲存概要的表格中，令接下來的處理有點困難）。

　　alter session set create_stored_outlines = false;

　　要看到這樣做的結果，我們可以查詢以下檢視來看清Oracle為我們建立和儲存的概要細節。。

　　select name, category, used, sql_textfrom user_outineswhere category = 'DEMO';NAME CATEGORY USED------------------------------ ------------------------------ -------SQL_TEXT------------------------------------------------------------------------------SYS_OUTLINE_020503165427311 DEMO UNUSEDSELECT V1 FROM SO_DEMO WHERE N1 = :b1 AND N2 = :b2select name, stage, hintfrom user_outline_hintswhere name = ' SYS_OUTLINE_020503165427311';NAME STAGE HINT------------------------------ ---------- ------------------------------SYS_OUTLINE_020503165427311 3 NO_EXPANDSYS_OUTLINE_020503165427311 3 ORDEREDSYS_OUTLINE_020503165427311 3 NO_FACT(SO_DEMO)SYS_OUTLINE_020503165427311 3 FULL(SO_DEMO)SYS_OUTLINE_020503165427311 2 NOREWRITESYS_OUTLINE_020503165427311 1 NOREWRITE

　　我們可以看到在demo的分類中只有一個儲存概要，檢視概要中的sql_text我們可以看到與我們原來PL/SQL程式碼類似的、但又有點不同的語句。這是很重要的一點，因為Oracle僅在儲存的sql_text和將要執行的SQL非常相似的時候才會使用儲存概要。實際上，在Oracle8i中，兩個SQL語句要完全一樣才可以，這也是儲存概要的一個大問題。

　　你可以由列表中看到儲存概要中是一套hints用來描述Oracle如何執行（或者將要執行）該SQL。這個計劃使用一個全表搜尋--即使是一個全表搜尋這樣的操作，Oracle使用大量的hints來確保執行的計劃。

　　要注意到儲存概要通常都是屬於一個分類的；在這裡是demo分類，我們是透過alter session命令來指定的。如果在上面的命令中，我們使用true來代替demo，我們將在一個名字為default的分類中找到該儲存概要。

　　儲存概要都有一個名字，該名字在整個資料庫中都必須是唯一的。沒有兩個概要的名字是相同的，即使是它們是由不同的使用者產生。實際上，概要並不是由誰擁有的，它們僅有建立者。如果你建立的一個儲存概要和我以後執行的一個SQL語句匹配，Oracle將會應用你的hints列表到我的語句--即使這些hints在我的模式中是無意義的。（這樣我們就有完全不同的選項來欺騙儲存概要，不過這是另一篇文章的事情了）。你還可能注意到，當Oracle自動產生儲存概要時，它的名字中包含有一個接近毫秒的時間戳。

　　繼續處理我們那個有問題的SQL，我們判定如果使用一個/*+ and_equal(so_demo, sd_i1, sd_i2) */ 的hint，那麼Oracle將會使用我們想要的執行路徑，所以我們現在透過以下的方法顯式建立一個儲存概要：

　　create or replace outline so_fixfor category demo onselect /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1from so_demowhere n1 = 1and n2 = 2;

　　這樣就顯式地在我們的demo分類中建立了一個名字為so_fix的儲存概要。我們可以透過name='SO_FIX'這個條件來重新查詢user_outlines和user_outline_hints，檢視一下儲存概要是怎樣的。

　　NAME CATEGORY USED------------------------------ ------------------------------ ---------SQL_TEXT---------------------------------------------------------------------------SO_FIX DEMO UNUSEDselect /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1from so_demowhere n1 = 1and n2 = 2NAME STAGE HINT------------------------------ ---------- --------------------------------SO_FIX 3 NO_EXPANDSO_FIX 3 ORDEREDSO_FIX 3 NO_FACT(SO_DEMO)SO_FIX 3 AND_EQUAL(SO_DEMO SD_I1 SD_I2)SO_FIX 2 NOREWRITESO_FIX 1 NOREWRITE

　　要注意到的是FULL(SO_DEMO)那一行已經被AND_EQUAL(SO_DEMO SD_I1 SD_I2)替換了，這是我們想要看到的。

　　現在我們必須將兩個儲存概要"替換"過來。我們想Oracle在看到以前的語句時使用新的hint列表；要做到這一點，我們必須做一些欺騙。user_outlines和user_outline_hints檢視是由兩個表格產生的(分別是ol$和ol$hints），它們由outln模式擁有，我們必須直接修改這些表格；這意味著要使用outln連線資料庫，並且使用一個有許可權的帳號來更新表格。

　　幸運的是，outln表格並沒有任何引用的完整性限制。便利的是，ol$ (outlines)和ol$hints (hints) 表格間的關係是由概要的名字定義的（儲存在ol_name列中）。因此，仔細檢查名字，我們就可以透過交換ol$hints表上的名字交換儲存概要的提示：

　　update outln.ol$hintsset ol_name =decode(ol_name,'SO_FIX','SYS_OUTLINE_020503165427311','SYS_OUTLINE_020503165427311','SO_FIX')where ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX');

　　對於這樣做，你可能感到有點不習慣，特別是根據指南上的建議--不過這個更新在Metalink（譯者注：這是Oracle的一個技術支援站點）上是允許的。不過，你還需要做第二次更新來確保和每個儲存概要相聯絡的hints數目保持一致。如果你忽略了這一步，你將會發現你的一些儲存概要被損壞，或者在一個匯出/匯入中的處理中被破壞。

　　update outln.ol$ ol1set hintcount = (select hintcountfrom ol$ ol2where ol2.ol_name in ('SYS_OUTLINE_020503165427311',' SO_FIX')and ol2.ol_name != ol1.ol_name)whereol1.ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX');

　　一旦完成上面的語句，你就可以發起一個新的連線，告訴它使用儲存概要，重新執行該過程然後退出；同樣地，你可以使用sql_trace來確認Oracle確實是這樣做的。要告訴Oracle使用修改後的儲存概要，你可以使用以下的命令：

　　alter session set use_stored_outline = demo;

　　檢查trace檔案，你將會發現該SQL現在使用and_equal的路徑（如果你使用tkprof來處理和解釋trace檔案，你將會發現輸出顯示了兩個矛盾的路徑。第一個將展示使用的and_equal路徑，第二個將可能是一個全表搜尋，這是因為在tkprof在跟蹤的SQL上執行explain plan時，該儲存概要可能沒有被呼叫）。

　　由開發到生成環境

　　現在我們已經產生了一個單一的概要，我們需要將它傳送到生產環境中。儲存概要有很多特性可以幫助我們做到這一點。例如，我們可以將儲存概要改名，由開發環境中匯出，然後將它匯入到生產系統中，首先在生產環境的一個測試分類中檢驗它，然後在將它轉移到生產分類中。有用的命令是：

　　alter outline SYS_OUTLINE_020503165427311 rename to AND_EQUAL_SAMPLE;

　　alter outline AND_EQUAL_SAMPLE change category to PROD_CAT;

　　要將概要由一個開發系統匯出到一個生產系統中，我們可以利用在一個匯出的引數檔案中加入一個where語句，因此我們的匯出引數檔案可能是：

　　userid=outln/outlntables=(ol$, ol$hints, ol$nodes) # ol$nodes exists in v9 onlyfile=so.dmpconsistent=y # very importantrows=yesquery='where ol_name = ''AND_EQUAL_SAMPLE'''

　　Oracle 9的加強

　　在使用儲存概要時，還有許多其它的細節需要考慮，在Oracle8中，對於它們能夠做什麼以及如何工作是有一些不便的限制的，不過其中許多的問題已經在Oracle 9中消除了。

　　儲存概要在Oracle8中使用的最大不足是它只可以在儲存的文字和將要執行的文字要完全一樣才可以使用。在Oracle 9中，有一個"標準化"的處理可以消除這個匹配的限制；在對比前，文字將會被轉換為大寫並且被除去空格。這樣就提升了不同的SQL可以使用同一個儲存概要的機會。

　　呼叫多個儲存塊的複雜執行計劃中還有一些問題。Oracle公司透過在Oracle 9中推出了一個在outln模式中的ol$nodes表來解決。這樣就可以幫助Oracle減少ol$hints中的hints列表，並且可以在即將執行SQL的子區中正確地交叉應用它們。不過，由儲存概要之間交換hints的策略有一個副作用，因為ol$hints表還需要不同細節的文字長度和偏移。升級到Oracle9時，將需要選用一些方法來管理儲存概要，例如帶有特別資料集合或者丟失索引的第二個模式，或者是帶有內建的hints的儲存檢視來替換文字中命名的表格。

　　Oracle9的另一個特色是在管理儲存概要時有更多的支援，包括初次推出了一個包來讓你直接編輯儲存概要。更重要的是，還有一個選項可讓你更安全地管理生產系統上的計劃。雖然沒人喜歡在生產環境上做實驗，不過在有些時候，只有生產系統才有正確的資料分佈和卷，以讓你決定某個SQL的最優執行路徑。在Oracle9中，你可以建立一個outln表的私人複製，並且將"public"的概要釋放進去以作"私人的"實驗，這樣你就不用冒你的私人儲存概要被終端使用者的程式碼看到的危險。我個人認為這是一個最後的手段，不過我可以想象到有時它是必須的。更安全的是，如果你有一個full-scale UAT或者開發系統，可以使用這個特性自由地測試。

　　告誡

　　這篇文章給你足夠的資訊作儲存概要的實驗；不過在應用該技術到一個生產系統上時，還有一些地方是你必須意識到的。

　　首先--在Oracle8i中，outln（這是擁有儲存概要的那些表格所在的模式）有一個預設的密碼，該帳號有一個非常危險的許可權。你必須修改這個帳號的密碼。在Oracle9i中，你將會發現這個帳號已經被鎖定。

　　第二--保持儲存概要的表格在system表空間中建立。在一個生產系統中，當你開始建立儲存概要時，你將會發現會使用system表空間中的很多空間。因此最好將這些表格移走，最好是放到它們自己的表空間中。不幸的是，其中的一個表格含有long列，因此你將可能需要使用exp/imp將這些表格移動到一個新的表空間中。

　　第三--雖然儲存概要對於解決嚴重的效能問題是很有用的，不過它也有一個開銷。如果啟用了儲存概要，那麼Oracle在分析每個新的語句時都會檢查是否存在一個相關的儲存概要。如果大量的語句都沒有儲存概要，那麼你就需要平衡一下這個開銷與你在很少擁有儲存概要語句上得到的效能提升，看是否值得這樣處理。不過，這個問題只會在一個有著更嚴重效能問題的系統上出現。

　　結論

　　儲存概要有著巨大的好處。當你不能修改原始碼或者索引策略時，儲存概要是令第三方的應用執行得更有效率的唯一方法。

　　更進一步，如果你還需要面對將一個系統由基於規則切換到開銷優先的問題，那麼儲存概要將是你最有效率和無風險的選擇。

　　如果你需要發揮儲存概要的最大好處，那麼Oracle9有一些加強可讓它覆蓋更多類的SQL，減少開銷，並可讓你更靈活地測試、管理和安裝儲存概要。