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

這個應用的作用是什麼？

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

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

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

你想該應用做什麼？

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

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

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

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

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

啟動一個session並且執行以下的命令：alter session set create_stored_outlines = demo;

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

declare m_value varchar2(10); begin get_value(1, 1, m_value); end; /

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

alter session set create_stored_outlines = false;

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

select name, category, used, sql_text from user_outines where category = DEMO; NAME CATEGORY USED SQL_TEXT SYS_OUTLINE_020503165427311 DEMO UNUSED SELECT V1 FROM SO_DEMO WHERE N1 = :b1 AND N2 = :b2 select name, stage, hint from user_outline_hints where name = SYS_OUTLINE_020503165427311; NAME STAGE HINT SYS_OUTLINE_020503165427311 3 NO_EXPAND SYS_OUTLINE_020503165427311 3 ORDERED SYS_OUTLINE_020503165427311 3 NO_FACT(SO_DEMO) SYS_OUTLINE_020503165427311 3 FULL(SO_DEMO) SYS_OUTLINE_020503165427311 2 NOREWRITE SYS_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_fix for category demo on select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1 from so_demo where n1 = 1 and n2 = 2;

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

NAME CATEGORY USED SQL_TEXT SO_FIX DEMO UNUSED select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1 from so_demo where n1 = 1 and n2 = 2 NAME STAGE HINT SO_FIX 3 NO_EXPAND SO_FIX 3 ORDERED SO_FIX 3 NO_FACT(SO_DEMO) SO_FIX 3 AND_EQUAL(SO_DEMO SD_I1 SD_I2) SO_FIX 2 NOREWRITE SO_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$hints set 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$ ol1 set hintcount = ( select hintcount from ol$ ol2 where ol2.ol_name in (SYS_OUTLINE_020503165427311, SO_FIX) and ol2.ol_name != ol1.ol_name ) where ol1.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/outln

tables=(ol$, ol$hints, ol$nodes) # ol$nodes exists in v9 only

file=so.dmp

consistent=y # very important

rows=yes

query=where ol_name = AND_EQUAL_SAMPLE[@more@]