解析Oracle 8i/9i的計(jì)劃穩(wěn)定性(1)
2024-08-29 13:35:33
供稿：網(wǎng)友
由Oralce8.1開(kāi)始，Oracle增加了一個(gè)新的特性就是Stored Outlines，或者稱(chēng)為Plan Stability（計(jì)劃穩(wěn)定性）。這個(gè)特性帶來(lái)三個(gè)好處。首先，你可以優(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è)用戶，在該用戶的模式中建一個(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è)用戶，他的權(quán)限有：create session, create table, create PRocedure, create any outline, and alter session。以該用戶連接并且運(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è)階段：
1. 啟動(dòng)一個(gè)新的session（連接），然后重新運(yùn)行該過(guò)程，首先告訴Oracle我們要跟蹤將要運(yùn)行的SQL語(yǔ)句和該SQL使用的路徑。這里說(shuō)的"路徑"就是我們存儲(chǔ)概要的第一個(gè)例子。
2. 為有問(wèn)題的SQL語(yǔ)句創(chuàng)建更好的存儲(chǔ)概要，然后用好的代替有問(wèn)題的。
3. 啟動(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é)果，我們可以查詢以下視圖來(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è)概要的名字是相同的，即使是它們是由不同的用戶產(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)重新查詢user_outlines和user_outline_hints，查看一下存儲(chǔ)概要是怎樣的。NAME CATEGORY USED
SQL_TEXTSO_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模式擁有，我們必須直接修改這些表格；這意味著要使用outl
