解析Oracle 8i/9i的計(jì)劃穩(wěn)定性
2024-08-29 13:46:32
供稿：網(wǎng)友
由Oralce8.1開(kāi)始，Oracle增加了一個(gè)新的特性就是Stored Outlines，或者稱(chēng)為Plan Stability（計(jì)劃穩(wěn)定性）。這個(gè)特性帶來(lái)三個(gè)好處。首先，你可以?xún)?yōu)化開(kāi)銷(xiāo)很大的語(yǔ)句的處理。 第二，假如有一些語(yǔ)句Oracle需要花費(fèi)長(zhǎng)時(shí)間來(lái)優(yōu)化（而不是執(zhí)行），你可以節(jié)省時(shí)間并且減少優(yōu)化階段的競(jìng)爭(zhēng)。最后，它可以讓你選擇使用新的cursor_sharing參數(shù)而無(wú)需要擔(dān)心因此而不采用優(yōu)化的執(zhí)行路徑。
要知道如何使用存儲(chǔ)概要才是最優(yōu)的，我們首先運(yùn)行一些極度沒(méi)有效率的SQL的存儲(chǔ)過(guò)程開(kāi)始，要注重的是，我們不能修改源代碼（理論上）。
我們將看一下如何跟蹤SQL語(yǔ)句，并且查看它當(dāng)前在數(shù)據(jù)庫(kù)中的執(zhí)行計(jì)劃，找出一些提示來(lái)改進(jìn)SQL語(yǔ)句的性能，然后再重新執(zhí)行該SQL語(yǔ)句時(shí)，讓Oracle使用我們的提示。
在這個(gè)示例中，我們將創(chuàng)建一個(gè)用戶(hù)，在該用戶(hù)的模式中建一個(gè)表格，并且創(chuàng)建一個(gè)存儲(chǔ)過(guò)程訪問(wèn)該表格，我們將在這個(gè)存儲(chǔ)過(guò)程上使用wrap工具，這樣我們就不能通過(guò)反向方式得到源代碼。然后我們將通過(guò)該存儲(chǔ)過(guò)程來(lái)調(diào)試SQL的執(zhí)行。
例子中我們將假定存儲(chǔ)慨要已經(jīng)在數(shù)據(jù)庫(kù)創(chuàng)建的時(shí)候被自動(dòng)安裝。
預(yù)備工作
創(chuàng)建一個(gè)用戶(hù)，他的權(quán)限有：create session, create table, create PRocedure, create any outline, and alter session。以該用戶(hù)連接并且運(yùn)行以下的腳本來(lái)創(chuàng)建一個(gè)表格：
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;
接著需要編碼來(lái)創(chuàng)建一個(gè)存儲(chǔ)過(guò)程訪問(wèn)該表格。創(chuàng)建一個(gè)稱(chēng)為c_proc.sql的腳本，如下：
create or replace procedure get_value (
i_n1 in number,
i_n2 in number,
io_v1 out varchar2
)
as
begin
select v1
into io_v1
from so_demo
where n1 = i_n1
and n2 = i_n2
;
end;
/
當(dāng)然，也可以直接執(zhí)行這個(gè)腳本來(lái)建立該過(guò)程--不過(guò)，為了更有效果，轉(zhuǎn)到操作系統(tǒng)的命令行并且執(zhí)行以下命令：
wrap iname=c_proc.sql
響應(yīng)是：
Processing c_proc.sql to c_proc.plb
這里不是通過(guò)執(zhí)行c_proc.sql腳本來(lái)產(chǎn)生該過(guò)程，而是執(zhí)行看不到源碼的c_proc.plb腳本，你將會(huì)發(fā)現(xiàn)在user_source的視圖中找不到我們的SQL語(yǔ)句。 這個(gè)應(yīng)用的作用是什么？
現(xiàn)在我們已經(jīng)產(chǎn)生了一個(gè)模擬的應(yīng)用，我們就可以運(yùn)行它，打開(kāi)sql_trace，看看有什么事情發(fā)生。我們將會(huì)發(fā)現(xiàn)這個(gè)SQL執(zhí)行一個(gè)全表搜索來(lái)得到請(qǐng)求的數(shù)據(jù)。
在這個(gè)測(cè)試中，全表檢索或許是最有效的方式--不過(guò)讓我們假定已經(jīng)證實(shí)使用一個(gè)單列的索引和and-equal選項(xiàng)才是最佳的執(zhí)行路徑時(shí)，我們可以怎樣修改呢（無(wú)需在代碼中加入提示）？
通過(guò)存儲(chǔ)概要，答案是簡(jiǎn)單的。要達(dá)到我下面所做的事情實(shí)際上有好幾種方法，因此不要認(rèn)為這是唯一的做法。Oracle一直改進(jìn)它的特性以方便使用，這里所講的技術(shù)或許在未來(lái)的一個(gè)版本中就會(huì)消失。
你想該應(yīng)用做什么？
要令Oracle如我們所想的那樣運(yùn)作，有三個(gè)階段：
. 啟動(dòng)一個(gè)新的session（連接），然后重新運(yùn)行該過(guò)程，首先告訴Oracle我們要跟蹤將要運(yùn)行的SQL語(yǔ)句和該SQL使用的路徑。這里說(shuō)的"路徑"就是我們存儲(chǔ)概要的第一個(gè)例子。
. 為有問(wèn)題的SQL語(yǔ)句創(chuàng)建更好的存儲(chǔ)概要，然后用好的代替有問(wèn)題的。
. 啟動(dòng)一個(gè)新的session，并且告訴Oracle在看到匹配的SQL時(shí)，開(kāi)始使用新的存儲(chǔ)概要，而不是使用通常的優(yōu)化方法來(lái)執(zhí)行；然后重新運(yùn)行該過(guò)程。
我們必須停止和啟動(dòng)新的session來(lái)確保pl/sql緩沖中的游標(biāo)（cursors）并不是保持打開(kāi)的。存儲(chǔ)概要只在一個(gè)游標(biāo)被分析的時(shí)候產(chǎn)生和（或）應(yīng)用，因此我們必須要確認(rèn)以前存在的類(lèi)似游標(biāo)是關(guān)閉的。
啟動(dòng)一個(gè)session并且執(zhí)行以下的命令：
alter session set create_stored_outlines = demo;
然后運(yùn)行一小段匿名的代碼塊來(lái)執(zhí)行該過(guò)程，例如：
declare
m_value varchar2(10);
begin
get_value(1, 1, m_value);
end;
/
然后停止收集執(zhí)行的路徑（否則以下你執(zhí)行的一些SQL也會(huì)放到存儲(chǔ)概要的表格中，令接下來(lái)的處理有點(diǎn)困難）。
alter session set create_stored_outlines = false;
要看到這樣做的結(jié)果，我們可以查詢(xún)以下視圖來(lái)看清Oracle為我們創(chuàng)建和存儲(chǔ)的概要細(xì)節(jié)。。
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的分類(lèi)中只有一個(gè)存儲(chǔ)概要，查看概要中的sql_text我們可以看到與我們?cè)瓉?lái)PL/SQL代碼類(lèi)似的、但又有點(diǎn)不同的語(yǔ)句。這是很重要的一點(diǎn)，因?yàn)镺racle僅在存儲(chǔ)的sql_text和將要執(zhí)行的SQL非常相似的時(shí)候才會(huì)使用存儲(chǔ)概要。實(shí)際上，在Oracle8i中，兩個(gè)SQL語(yǔ)句要完全一樣才可以，這也是存儲(chǔ)概要的一個(gè)大問(wèn)題。
你可以由列表中看到存儲(chǔ)概要中是一套hints用來(lái)描述Oracle如何執(zhí)行（或者將要執(zhí)行）該SQL。這個(gè)計(jì)劃使用一個(gè)全表搜索--即使是一個(gè)全表搜索這樣的操作，Oracle使用大量的hints來(lái)確保執(zhí)行的計(jì)劃。
要注重到存儲(chǔ)概要通常都是屬于一個(gè)分類(lèi)的；在這里是demo分類(lèi)，我們是通過(guò)alter session命令來(lái)指定的。假如在上面的命令中，我們使用true來(lái)代替demo，我們將在一個(gè)名字為default的分類(lèi)中找到該存儲(chǔ)概要。
存儲(chǔ)概要都有一個(gè)名字，該名字在整個(gè)數(shù)據(jù)庫(kù)中都必須是唯一的。沒(méi)有兩個(gè)概要的名字是相同的，即使是它們是由不同的用戶(hù)產(chǎn)生。實(shí)際上，概要并不是由誰(shuí)擁有的，它們僅有創(chuàng)建者。假如你創(chuàng)建的一個(gè)存儲(chǔ)概要和我以后執(zhí)行的一個(gè)SQL語(yǔ)句匹配，Oracle將會(huì)應(yīng)用你的hints列表到我的語(yǔ)句--即使這些hints在我的模式中是無(wú)意義的。（這樣我們就有完全不同的選項(xiàng)來(lái)欺騙存儲(chǔ)概要，不過(guò)這是另一篇文章的事情了）。你還可能注重到，當(dāng)Oracle自動(dòng)產(chǎn)生存儲(chǔ)概要時(shí)，它的名字中包含有一個(gè)接近毫秒的時(shí)間戳。
繼續(xù)處理我們那個(gè)有問(wèn)題的SQL，我們判定假如使用一個(gè)/*+ and_equal(so_demo, sd_i1, sd_i2) */ 的hint，那么Oracle將會(huì)使用我們想要的執(zhí)行路徑，所以我們現(xiàn)在通過(guò)以下的方法顯式創(chuàng)建一個(gè)存儲(chǔ)概要：
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分類(lèi)中創(chuàng)建了一個(gè)名字為so_fix的存儲(chǔ)概要。我們可以通過(guò)name='SO_FIX'這個(gè)條件來(lái)重新查詢(xún)user_outlines和user_outline_hints，查看一下存儲(chǔ)概要是怎樣的。
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)那一行已經(jīng)被AND_EQUAL(SO_DEMO SD_I1 SD_I2)替換了，這是我們想要看到的。
現(xiàn)在我們必須將兩個(gè)存儲(chǔ)概要"替換"過(guò)來(lái)。我們想Oracle在看到以前的語(yǔ)句時(shí)使用新的hint列表；要做到這一點(diǎn)，我們必須做一些欺騙。user_outlines和user_outline_hints視圖是由兩個(gè)表格產(chǎn)生的(分別是ol$和ol$hints），它們由outln模式擁有，我們必須直接修改這些表格；這意味著要使用outln連接數(shù)據(jù)庫(kù)，并且使用一個(gè)有權(quán)限的帳號(hào)來(lái)更新表格。
幸運(yùn)的是，outln表格并沒(méi)有任何引用的完整性限制。便利的是，ol$ (outlines)和ol$hints (hints) 表格間的關(guān)系是由概要的名字定義的（存儲(chǔ)在ol_name列中）。因此，仔細(xì)檢查名字，我們就可以通過(guò)交換ol$hints表上的名字交換存儲(chǔ)概要的提示：
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')
;
對(duì)于這樣做，你可能感到有點(diǎn)不習(xí)慣，非凡是根據(jù)指南上的建議--不過(guò)這個(gè)更新在Metalink（譯者注：這是Oracle的一個(gè)技術(shù)支持站點(diǎn)）上是答應(yīng)的。不過(guò)，你還需要做第二次更新來(lái)確保和每個(gè)存儲(chǔ)概要相聯(lián)系的hints數(shù)目保持一致。假如你忽略了這一步，你將會(huì)發(fā)現(xiàn)你的一些存儲(chǔ)概要被損壞，或者在一個(gè)導(dǎo)出/導(dǎo)入中的處理中被破壞。
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')
;
一旦完成上面的語(yǔ)句，你就可以發(fā)起一個(gè)新的連接，告訴它使用存儲(chǔ)概要，重新運(yùn)行該過(guò)程然后退出；同樣地，你可以使用sql_trace來(lái)確認(rèn)Oracle確實(shí)是這樣做的。要告訴Oracle使用修改后的存儲(chǔ)概要，你可以使用以下的命令：
alter session set use_stored_outline = demo;
檢查trace文件，你將會(huì)發(fā)現(xiàn)該SQL現(xiàn)在使用and_equal的路徑（假如你使用tkprof來(lái)處理和解釋trace文件，你將會(huì)發(fā)現(xiàn)輸出顯示了兩個(gè)矛盾的路徑。第一個(gè)將展示使用的and_equal路徑，第二個(gè)將可能是一個(gè)全表搜索，這是因?yàn)樵趖kprof在跟蹤的SQL上執(zhí)行explain plan時(shí)，該存儲(chǔ)概要可能沒(méi)有被調(diào)用）。 由開(kāi)發(fā)到生成環(huán)境
現(xiàn)在我們已經(jīng)產(chǎn)生了一個(gè)單一的概要，我們需要將它傳送到生產(chǎn)環(huán)境中。存儲(chǔ)概要有很多特性可以幫助我們做到這一點(diǎn)。例如，我們可以將存儲(chǔ)概要改名，由開(kāi)發(fā)環(huán)境中導(dǎo)出，然后將它導(dǎo)入到生產(chǎn)系統(tǒng)中，首先在生產(chǎn)環(huán)境的一個(gè)測(cè)試分類(lèi)中檢驗(yàn)它，然后在將它轉(zhuǎn)移到生產(chǎn)分類(lèi)中。有用的命令是：
alter outline SYS_OUTLINE_020503165427311 rename to AND_EQUAL_SAMPLE;
alter outline AND_EQUAL_SAMPLE change category to PROD_CAT;
要將概要由一個(gè)開(kāi)發(fā)系統(tǒng)導(dǎo)出到一個(gè)生產(chǎn)系統(tǒng)中，我們可以利用在一個(gè)導(dǎo)出的參數(shù)文件中加入一個(gè)where語(yǔ)句，因此我們的導(dǎo)出參數(shù)文件可能是：
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'''
Oracle 9的加強(qiáng)
在使用存儲(chǔ)概要時(shí)，還有許多其它的細(xì)節(jié)需要考慮，在Oracle8中，對(duì)于它們能夠做什么以及如何工作是有一些不便的限制的，不過(guò)其中許多的問(wèn)題已經(jīng)在Oracle 9中消除了。
存儲(chǔ)概要在Oracle8中使用的最大不足是它只可以在存儲(chǔ)的文本和將要執(zhí)行的文本要完全一樣才可以使用。在Oracle 9中，有一個(gè)"標(biāo)準(zhǔn)化"的處理可以消除這個(gè)匹配的限制；在對(duì)比前，文本將會(huì)被轉(zhuǎn)換為大寫(xiě)并且被除去空格。這樣就提升了不同的SQL可以使用同一個(gè)存儲(chǔ)概要的機(jī)會(huì)。
調(diào)用多個(gè)存儲(chǔ)塊的復(fù)雜執(zhí)行計(jì)劃中還有一些問(wèn)題。Oracle公司通過(guò)在Oracle 9中推出了一個(gè)在outln模式中的ol$nodes表來(lái)解決。這樣就可以幫助Oracle減少ol$hints中的hints列表，并且可以在即將執(zhí)行SQL的子區(qū)中正確地交叉應(yīng)用它們。不過(guò)，由存儲(chǔ)概要之間交換hints的策略有一個(gè)副作用，因?yàn)閛l$hints表還需要不同細(xì)節(jié)的文本長(zhǎng)度和偏移。升級(jí)到Oracle9時(shí)，將需要選用一些方法來(lái)治理存儲(chǔ)概要，例如帶有非凡數(shù)據(jù)集合或者丟失索引的第二個(gè)模式，或者是帶有內(nèi)置的hints的存儲(chǔ)視圖來(lái)替換文本中命名的表格。
Oracle9的另一個(gè)特色是在治理存儲(chǔ)概要時(shí)有更多的支持，包括初次推出了一個(gè)包來(lái)讓你直接編輯存儲(chǔ)概要。更重要的是，還有一個(gè)選項(xiàng)可讓你更安全地治理生產(chǎn)系統(tǒng)上的計(jì)劃。雖然沒(méi)人喜歡在生產(chǎn)環(huán)境上做實(shí)驗(yàn)，不過(guò)在有些時(shí)候，只有生產(chǎn)系統(tǒng)才有正確的數(shù)據(jù)分布和卷，以讓你決定某個(gè)SQL的最優(yōu)執(zhí)行路徑。在Oracle9中，你可以創(chuàng)建一個(gè)outln表的私人拷貝，并且將"public"的概要釋放進(jìn)去以作"私人的"實(shí)驗(yàn)，這樣你就不用冒你的私人存儲(chǔ)概要被終端用戶(hù)的代碼看到的危險(xiǎn)。我個(gè)人認(rèn)為這是一個(gè)最后的手段，不過(guò)我可以想象到有時(shí)它是必須的。更安全的是，假如你有一個(gè)full-scale UAT或者開(kāi)發(fā)系統(tǒng)，可以使用這個(gè)特性自由地測(cè)試。
告誡
這篇文章給你足夠的信息作存儲(chǔ)概要的實(shí)驗(yàn)；不過(guò)在應(yīng)用該技術(shù)到一個(gè)生產(chǎn)系統(tǒng)上時(shí)，還有一些地方是你必須意識(shí)到的。
首先--在Oracle8i中，outln（這是擁有存儲(chǔ)概要的那些表格所在的模式）有一個(gè)默認(rèn)的密碼，該帳號(hào)有一個(gè)非常危險(xiǎn)的權(quán)限。你必須修改這個(gè)帳號(hào)的密碼。在Oracle9i中，你將會(huì)發(fā)現(xiàn)這個(gè)帳號(hào)已經(jīng)被鎖定。
第二--保持存儲(chǔ)概要的表格在system表空間中創(chuàng)建。在一個(gè)生產(chǎn)系統(tǒng)中，當(dāng)你開(kāi)始創(chuàng)建存儲(chǔ)概要時(shí)，你將會(huì)發(fā)現(xiàn)會(huì)使用system表空間中的很多空間。因此最好將這些表格移走，最好是放到它們自己的表空間中。不幸的是，其中的一個(gè)表格含有l(wèi)ong列，因此你將可能需要使用exp/imp將這些表格移動(dòng)到一個(gè)新的表空間中。
第三--雖然存儲(chǔ)概要對(duì)于解決嚴(yán)重的性能問(wèn)題是很有用的，不過(guò)它也有一個(gè)開(kāi)銷(xiāo)。假如激活了存儲(chǔ)概要，那么Oracle在分析每個(gè)新的語(yǔ)句時(shí)都會(huì)檢查是否存在一個(gè)相關(guān)的存儲(chǔ)概要。假如大量的語(yǔ)句都沒(méi)有存儲(chǔ)概要，那么你就需要平衡一下這個(gè)開(kāi)銷(xiāo)與你在很少擁有存儲(chǔ)概要語(yǔ)句上得到的性能提升，看是否值得這樣處理。不過(guò)，這個(gè)問(wèn)題只會(huì)在一個(gè)有著更嚴(yán)重性能問(wèn)題的系統(tǒng)上出現(xiàn)。
結(jié)論
存儲(chǔ)概要有著巨大的好處。當(dāng)你不能修改源代碼或者索引策略時(shí)，存儲(chǔ)概要是令第三方的應(yīng)用運(yùn)行得更有效率的唯一方法。
更進(jìn)一步，假如你還需要面對(duì)將一個(gè)系統(tǒng)由基于規(guī)則切換到開(kāi)銷(xiāo)優(yōu)先的問(wèn)題，那么存儲(chǔ)概要將是你最有效率和無(wú)風(fēng)險(xiǎn)的選擇。
假如你需要發(fā)揮存儲(chǔ)概要的最大好處，那么Oracle9有一些加強(qiáng)可讓它覆蓋更多類(lèi)的SQL，減少開(kāi)銷(xiāo)，并可讓你更靈活地測(cè)試、治理和安裝存儲(chǔ)概要。
