顯式游標范圍大小和復雜間隔的相關問題介紹
2024-07-21 02:39:54
供稿：網友
我們的技術專家回答關于游標、范圍（extent）和間隔的問題。
是不是從Oracle7第7.3版以后的版本，隱式游標得到了優化，不會兩次取數據？還有，為什么當表T在列X上有一個索引時下面的隱式游標比顯式游標運行得更快，而沒有索引時是顯式游標運行得較快呢？
Implicit Cursor:
Select x
into y
from T
where x = j;
EXPlicit Cursor:
cursor c(p number) is
select x from blah where x = p;
open c(j);
fetch c into y;
close c;
為了讓每個人都了解顯式游標和隱式游標是什么，我先簡單介紹一下它們的定義。
通常，隱式游標是指程序員并不"顯式"聲明、打開、從中取數據和關閉的那些游標；這些操作都是隱式的。因此，在上面的例子中，SELECT X INTO Y查詢就是一個隱式游標。對于它來說并沒有"cursor cursor_name is ..."這樣的定義語句。相反，第二個例子是典型的顯式關標。程序員顯式地聲明、打開、取數據和關閉它。
在PL/SQL中隱式游標比顯式游標運行得更快是一個事實，在Oracle7 7.3版之前的版本中就是這樣。事實上，我在Oracle7 7.1版中就測試過這樣的情況并得到了同樣的結論（這些測試請參見asktom.oracle.com/~tkyte/ivse.Html）。隱式游標運行得更快的原因（FOR LOOP隱式游標和SELECT INTO隱式游標）是PL/SQL引擎只需要解釋和執行很少的代碼。一般來說，PL/SQL引擎在后臺做的越多，程序就運行地越快。上面的隱式游標只使用了一行PL/SQL代碼；顯式游標至少使用了三行代碼，假如要"正確地"運行，實際上要使用6行代碼。你的顯式代碼并不像隱式游標那樣運行，它要確保你得到一條且只得到一條記錄。你的顯式代碼缺少了許多你要做的工作。為了精確地比較你的兩個游標例子，你的顯式代碼應該被擴展出以下幾行：
open c(j);
fetch c into y;
if ( c%notfound ) then raise NO_DATA_FOUND;
end if;
fetch c into y;
if ( c%found ) then raise TOO_MANY_ROWS;
end if;
close c;
假如這就是你的顯式游標，你會發現在所有情況下顯式游標都運行得比較慢，甚至于無論你的例子中有沒有索引都是這樣。
那么，你的問題的癥結所在是：為什么在你的例子中沒有索引時，隱式游標似乎運行地非常慢，然而當存在一個索引的時候，隱式游標卻運行得較快呢？答案在于全表掃描，事實上在得到一條記錄后，你的顯式測試就停止了。我將給出一個例子來向你展示它們之間的不同之處：
SQL> create table t ( x int )
2 pctfree 99 pctused 1;
Table created.
SQL> insert into t
2 select rownum
3 from all_objects;
29264 rows created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks, num_rows
2 from user_tables
3 where table_name = 'T';
BLOCKS EMPTY_BLOCKS NUM_ROWS
------------- ------------ -----------
4212 140 29264
我創建了一個有許多數據塊的表；值pctfree 99為隨后更新數據保留了99％的塊作為"空閑空間"。因此，即使表中的數據量很小，表本身也相當大。接著，我通過INSERT把值1，2，3，...一直到29,264嚴格按順序插入到表中。因此，X＝1在該表的"第一個"塊中而X＝29,000在表中相當接近表的最后一個塊。
接下來，我將運行一個小PL/SQL塊，它會顯示各種隱式和顯式游標對數據進行一致讀的次數。因為沒有索引，查詢將對整個表進行全面掃描。一旦我運行這個程序然后評審查詢結果，將很輕易對性能的差異進行量化。
SQL> declare
2 l_last_cgets number default 0;
3 l_x number;
4 cursor c( p_x in number ) is
5 select x
6 from t
7 where x = p_x;
8
9 PRocedure cgets( p_msg in varchar2
)
10 is
11 l_value number;
12 begin
13 select b.value into l_value
14 from v$statname a, v$mystat b
15 where a.statistic# = b.statistic#
16 and a.name = 'consistent gets';
17
18 dbms_output.put_line( p_msg );
19 dbms_output.put_line
20 ( 'Incremental cgets: '
21 to_char(l_value-l_last_cgets,
22 '999,999') );
23 l_last_cgets := l_value;
24 end;
25
26 begin
27 cgets('Starting');
28
29 open c(1);
30 fetch c into l_x;
31 close c;
32 cgets('Explicit to find X=1 '
33 'stop at first hit' );
34
35 open c(1);
36 fetch c into l_x;
37 fetch c into l_x;
38 close c;
39 cgets('Explicit to find X=1 '
40 'check for dups' );
41
42 select x into l_x
43 from t
44 where x = 1 AND rownum = 1;
45 cgets('Implicit to find X=1 '
46 'stop at first hit' );
47
48 select x into l_x
49 from t
50 where x = 1;
51 cgets('Implicit to find X=1 '
52 'check for dups' );
53
54 open c(29000);
55 fetch c into l_x;
56 close c;
57 cgets('Explicit to find X=29000');
58
59 select x into l_x
60 from t
61 where x = 29000;
62 cgets('Implicit to find X=29000');
63 end;
64 /
Starting
Incremental cgets: 514,690
Explicit to find X=1 stop at first hit
Incremental cgets: &nb
sp; 4
Explicit to find X=1 check for dups
Incremental cgets: 4,220
Implicit to find X=1 stop at first hit
Incremental cgets: 4
Implicit to find X=1 check for dups
Incremental cgets: 4,219
Explicit to find X=29000
Incremental cgets: 4,101
Implicit to find X=29000
Incremental cgets: 4,219
PL/SQL procedure sUCcessfully completed.
現在你就可以明白在你的例子中為什么顯式游標似乎比隱式游標運行得更快了。當我使用顯式游標進行測試的時候，只取一次數據X＝1，為了找到答案，查詢只需要掃描非常少的塊（很少的一致的讀次數）。然而，只要我使顯式游標來進行隱式游標的工作，檢查確保沒有其他記錄滿足同一條件，你就會看到顯式游標檢查表中的每一個塊?，F在，我接著說隱式游標，通過使用ROWNUM=1看看它是否也會在找到第一條符合條件的記錄時停下來，它和顯式游標做相同的工作量。當它檢查表中的第二行是否符合條件時，你會看到它同顯式游標一樣進行相同次數的一致讀；它也不得不對表進行全面掃描以核定只有一行X＝1。
最有趣的是當我查詢X＝29,000的時候。因為那行接近表的"結尾"，所以無論我采用什么方法，兩個查詢的工作量都差不多。為了找到滿足條件的第一行它們都必須掃描幾乎整個表。
現在，假如在X上有一個索引，兩個查詢都會使用索引范圍掃描，而且兩個查詢都不必對表進行全面掃描，便能快速地發現只有一行滿足條件。
這就解釋了你的游標行為：SELECT INTO檢查第二行，但顯式游標卻不這么做。假如你對應地進行比較：第二次顯式地取數據或者把"rownum = 1"添加到SELECT INTO語句中－－你就會發現兩個游標的工作量相同。
簡而言之，隱式游標更好。它們比使用顯式游標的相同代碼運行地更快，更輕易編碼（需要鍵入的代碼更少），而且我個人認為使用隱士游標的代碼更輕易讀也更輕易理解。
小、中和大
在我們的新應用程序中，我們設計了數據庫并創建了數據模型，甚至還估計了表的大小并為每個標指定了存儲參數。但現在我們的數據庫治理員告訴我們將給我們三個表空間：范圍大小統一為160K的TS_small表空間、范圍大小統一為5MB的TS_med表空間和范圍大小統一為160MB的TS_large表空間。他們告訴我們在TS_small中創建小于5MB的表，在TS_med中創建小于160MB的表，在TS_large中創建大于160MB的表。另外，他們不希望我們對表使用任何存儲參數。對索引也是這樣。這似乎并不合理，因為對于一個預計大小為120MB的表，我們應把它放在TS_med中，接下來假如我們在那個表空間中創建它，它會占24個范圍！數據庫治理員聲稱許多測試已經證實這種設計提供了最佳的性能并可以防止碎片。我的問題是，他們說的對嗎？我擔心對象會有太多的范圍。
看來他們已經讀過asktom Web站點（asktom.oracle.com）和互聯網討論組的相關內容，并發現了好的建議。從他們的數字看，我注重到他們答應一個表占用的最大空間是5GB，可以有32個或更少的范圍。假設上百個（或者上千個）范圍不會影響運行時數據操縱語言（DML）的性能，我會說他們做得非常好。
他們的前提都是正確的：將不會出現表空間碎片，性能也將得到優化。讓我們來看看每個主張。
不可能出現碎片應該很輕易明白。數據字典治理的表空間由于范圍的尺寸大小不同所以會出現碎片。數據字典治理的表空間可能包括上千個范圍嗎？每一個空閑范圍和已使用的范圍的大小都不同?，F在，你開始在這個表空間中刪除并創建對象，隨著時間的推移，表空間中就會出現許多大小不同的"空洞"（空閑空間）。接著，你查看一下數據字典治理的表空間并累計其中的空閑空間，你會發現有500MB的空閑空間。但接著你試著創建一個帶有40MB初始范圍的表，卻得到一個關于不能分配第一個范圍的錯誤消息。這是怎么回事？你有500MB的空閑空間，不是嗎？是這樣，但不幸的是那500MB的空間分布許多范圍中，那些范圍中的每一個空閑空間都小于40MB！因此，你有許多無法使用的空閑空間；你的表空間有許多碎片?，F在，讓我們考慮一下使用統一范圍的本地化治理的表空間。每個范圍都毫無例外地與其他每個范圍擁有相同的尺寸。假如你發現你有500MB的空閑空間，那么我可以保證你將能夠在這個表空間中分配一個新的范圍，因為每個被定義的空閑范圍都可以被你的對象使用。
至于最佳性能，你必須明白擁有幾十個，上百個甚至更多的范圍不會對運行時性能產生實質性的影響。你的DML操作（包括查詢）不會由于有許多范圍而受到不利的影響。我就不在這里證實它了，具體情況請通過以下兩個鏈接參見usenet討論組：asktom.oracle.com/~tkyte/extents.html 和asktom.oracle.com/~tkyte/extents2.html，每一個都包含有關這個主題的相當多的討論。對于你的對象來說三十二個范圍是非常合適的－根本不會對性能產生影響。事實上，因為本地化治理的表空間在空間分配方面比數據字典治理的表空間高效得多，使用它們將會提高性能，而不是降低性能。
不要擔心32個范圍等問題，你應該興奮你再也不必弄清什么是"最好的"INITIAL、NEXT、PCTINCREASE、MINEXTENTS和MAXEXTENTS。
設置一個復雜的間隔
我正在使用DBMS_JOB，我想在從周一到周五天天早6點到晚6點之間每15分鐘運行一次任務。我怎樣調度它呢？我無法計算出調度間隔。
對于計算DBMS_JOB的復雜間隔，我喜歡使用使用新的（Oracle8i第2版中）CASE語句。例如，下面的CASE語句將按你的要求返回正確的間隔：
SQL> alter session set nls_date_format =
2 'dy mon dd, yyyy hh24:mi';
Session altered.
SQL> select
2 sysdate,
3 case
; 4 when (to_char( sysdate, 'hh24' )
5 between 6 and 17
6 and to_char(sysdate,'dy') NOT IN
7 ('sat','sun') )
8 then trunc(sysdate)+
9 (trunc(to_char(sysdate,'sssss')/
10 900)+1)*15/24/60
11 when (to_char( sysdate, 'dy' )
12 not in ('fri','sat','sun') )
13 then trunc(sysdate)+1+6/24
14 else next_day( trunc(sysdate),
15 'Mon' )+6/24
16 end interval_date
17 from dual
18 /
SYSDATE
------------------------------
INTERVAL_DATE
------------------------------
sun sep 15, 2002 16:35
mon sep 16, 2002 06:00
CASE語句在產生諸如你需要的復雜值方面具有很大的靈活性。不幸的是，DBMS_JOB只答應你使用200字符或少于200字符的間隔，即使你"壓縮"了以上的CASE語句，你還是會發現它最少也有大約300個字符。因此你不能在對DBMS_JOB的調用中直接使用它。有以下兩種解決方法：一種是為那個select語法創建一個視圖NEXT_DATE，因此select * from next_date將返回該任務下次運行的時間；第二種方法是在一個返回日期值的PL/SQL函數中封裝以上的查詢。假如我使用視圖，我對DBMS_JOB的調用看起來可能像下面這樣：
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'(select * from next_date)'
);
end;
/
假如我使用PL/SQL函數方法并創建一個函數NEXT_DATE，它可能是這樣：
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'next_date()'
);
end;
/
實現信息只讀的最好方式
我們有幾個按時間（財政年度）劃分的表。你認為什么是使歷史數據只讀而當前數據可讀/寫的最好方式？
從根本上來說，我們希望能夠對當前財政年度的數據進行添加并鎖定以前財政年度的數據以便使它們不能被修改。我現在的想法是把歷史數據放在一個與當前數據隔離的表空間中。這個方法可行嗎？我在Microsoft windows 2000上使用Oracle9i 9.0.1版。
實際上，這很輕易實現。一個表空間可以是只讀的或是可讀寫的。假如你每個分區使用一個表空間（或者至少將歷史分區在與當前分區不同的表空間中），你可以簡單地使用ALTER TABLESPACE READ ONLY來使它只讀。最終用戶將不能修改那個表空間，而且，事實上，你可以節省相當可觀的備份時間，因為你只需備份那個表空間一次（除非你使它可讀寫并修改它--那么很顯然你將需要再次備份）。
事實上，你甚至可以把這個表空間放在某些只讀介質（例如CD），使它不可能被修改。
假如我使用Oracle9i數據庫第2版，我會更進一步。在使這個歷史分區只讀之前，我會使用新的表COMPRESS特性壓縮它。這樣我可以節省這個數據占用的大量磁盤空間。我會通過使用壓縮選項移動"現有的分區來完成它。在許多情況下，不要過分期望壓縮比會達到3:1、5:1甚至12:1，這依靠于數據的性質。
Autotrace的輸出意味著什么？
你能為我解釋這個結果中的recursive calls、db block gets等等是什么意思嗎？
Statistics
---------------------------------------------
0 recursive calls
202743 db block gets
84707 consistent gets
0 physical reads
0 redo size
2010 bytes sent via SQ
L*Net to client
430 bytes received via SQL*Net from ...
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk) 8 rows processed
這確實是一個最常見的問題，我將具體討論每個統計。我將使用Oracle9i數據庫性能調優向導和參考（Oracle9i Database Performance Tuning Guide and Reference）手冊中提供的定義來描述每個統計，在我認為定義可能不清楚時我會加一些注釋：
Recursive Calls. 在用戶級和系統級產生的遞歸調用的數目。
Oracle數據庫維護用于內部處理的表。當它需要改變那些表時，Oracle數據庫生成一個內部SQL語句，該語句反過來產生一個遞歸調用。
簡而言之，遞歸調用就是代表你的SQL執行的SQL語句。因此，假如你必須解析該查詢，例如，你可能必須運行一些其他的查詢來得到數據字典的信息。這就是遞歸調用?？臻g治理、安全性檢查、從SQL中調用PL/SQL--所有這些都會引起遞歸SQL調用。
DB Block Gets. 當前塊被請求的次數。
當存在時，當前模式塊將被立即檢索，而不會以一致讀的方式檢索。通常，查詢檢索的塊假如在查詢開始時存在，它們就被檢索。當前模式塊假如存在就立即被檢索，而不是從一個以前的時間點檢索。在一個SELECT期間，你可以看到當前模式檢索，因為對于需要進行全面掃描的表來說，需要讀數據字典來找到范圍信息（因為你需要"立即"信息，而不是一致讀）。在修改期間，為了向塊中寫入內容，你要以當前模式訪問塊。
Consistent Gets. 對于一個塊一致讀被請求的次數。
這是你以"一致讀"模式處理的塊數。為了回滾一個塊，這將包括從回滾段讀取的塊的數目。例如，這是你在SELECT語句中讀取塊的模式。當你進行一個指定的UPDATE/DELETE操作時，你也以一致讀模式讀取塊，然后以當前模式獲得塊以便實際進行修改。
Physical Reads. 從磁盤讀取的數據塊的總數。這個數等于"physical reads direct"（物理上直接讀取的塊數）的值加上讀入緩存區的所有塊數。
Redo Size. 所產生的以字節為單位的redo（重做日志）總數。
Bytes Sent via SQL*Net to Client. 從前臺進程發送到客戶端的字節總數。
一般來說，這是你的結果集的整體大小。
Bytes Received via SQL*Net from Client. 通過網絡從客戶端收到的字節總數。
一般來說，這是通過網絡傳輸的你的查詢的大小。
SQL*Net Round-trips to/from Client. 發送到客戶端和從客戶端接收的網絡消息總數。
一般來說，這是為了得到回答在你和服務器間發生的交互次數。當你在SQL*Plus中增加ARRAYSIZE設置值時，你將看到對于返回多條記錄的SELECT語句，這個數字會下降（更少的往返交互，因為每獲取N條記錄是一個往返）。當你減少你的ARRAYSIZE值時，你將看到這個數字增加。
Sorts (memory). 完全在內存中執行、且不需要任何磁盤寫的排序操作的數目。
沒有比在內存中排序更好的排序了，除非根本沒有排序。排序通常是由表連接SQL操作的選擇條件設定所引起的。
Sorts (disk). 至少需要一次磁盤寫的排序操作的次數。需要磁盤輸入/輸出的排序操作需要耗費大量資源。請試著增加初始化參數SORT_AREA_SIZE的大小。
Rows Processed. 這是由SELECT語句所返回的或是由INSERT、UPDATE或DELETE語句修改的總行數。
