共享SQL區(qū)、私有SQL區(qū)與游標(biāo) 一、共享SQL區(qū) 共享SQL區(qū)，就是Library cace中的游標(biāo)對(duì)象的句柄和子堆這些數(shù)據(jù)的另一種叫法。 二、私有SQL區(qū)的概念 私有SQL區(qū)，在專用服務(wù)器模式下，存貯在PGA中。 （復(fù)習(xí)一下PGA概 ...
共享SQL區(qū)、私有SQL區(qū)與游標(biāo)
一、共享SQL區(qū)
共享SQL區(qū)，就是Library cace中的游標(biāo)對(duì)象的句柄和子堆這些數(shù)據(jù)的另一種叫法。
二、私有SQL區(qū)的概念
私有SQL區(qū)，在專用服務(wù)器模式下，存貯在PGA中。
（復(fù)習(xí)一下PGA概念：
PGA在服務(wù)器端分配，記錄各種不同的連接至Oracle服務(wù)器的進(jìn)程的信息，如登錄時(shí)的用戶名、密碼等。每一個(gè)登錄至Oracle的會(huì)話，都會(huì)在服務(wù)器端占用一塊PGA。當(dāng)會(huì)話剛登錄時(shí)，每個(gè)用戶的PGA大概占個(gè)幾百KB左右的內(nèi)存，并可在以后根據(jù)會(huì)話中的操作伸、縮。）
私有 SQL 區(qū)被分為兩個(gè)區(qū)域，這兩部分的生命周期有所不同：
· 持續(xù)數(shù)據(jù)區(qū)（persistent area） 游標(biāo)被關(guān)閉時(shí)被釋放。
· 運(yùn)行時(shí)區(qū)（run-time area），當(dāng)游標(biāo)執(zhí)行結(jié)束就會(huì)被釋放。
1).
持續(xù)數(shù)據(jù)區(qū)（persistent area）
當(dāng)打開一個(gè)游標(biāo)時(shí)，持續(xù)區(qū)內(nèi)存被創(chuàng)建。包含綁定信息（bind information）、SQL聲明本身等之類的數(shù)據(jù)。此區(qū)只在游標(biāo)關(guān)閉時(shí)才會(huì)被釋放。
2).
運(yùn)行時(shí)區(qū)
當(dāng)游標(biāo)在執(zhí)行時(shí)被創(chuàng)建。當(dāng)語句執(zhí)行完畢或cancel之后被釋放。
運(yùn)行區(qū)或會(huì)話特定區(qū)是你的會(huì)話維護(hù)的相關(guān)游標(biāo)的狀態(tài)信息，例如：你的查詢開始時(shí)的SCN、當(dāng)前結(jié)果集的位置或其他特定于你的會(huì)話的東西。
另外，工作區(qū)（用于排序、哈希鏈接、Bitmap merge和Bitmap create等的內(nèi)存區(qū)），也是運(yùn)行時(shí)區(qū)的一部分。
如果我們?cè)趫?zhí)行select name from mytable order by name;這條語句，Oracle做的事情大致上包括：
三、 私有SQL區(qū)與共享SQL的關(guān)系
總結(jié)上面的內(nèi)容可知，持續(xù)數(shù)據(jù)區(qū)中的信息，是在游標(biāo)打開后，到游標(biāo)被執(zhí)行前，這一段期間所使用的內(nèi)存。凡在此階段中需被存進(jìn)內(nèi)存的信息，都是使用持續(xù)數(shù)據(jù)區(qū)的內(nèi)存。因此，除了上面所提到的綁定信息、SQL聲明本身之外，還有一類重要的信息，就是用來和共享SQL區(qū)某一SQL關(guān)聯(lián)的地址信息。
為什么私有SQL區(qū)要和共享SQL區(qū)關(guān)聯(lián)，因?yàn)閳?zhí)行計(jì)劃在共享SQL中存儲(chǔ)著。
下面一段話來自文檔：
Oracle 為執(zhí)行 SQL 語句的會(huì)話分配一個(gè)私有SQL 區(qū)。每個(gè)提交了 SQL 語句的會(huì)話都有一個(gè)私有 SQL 區(qū)。每個(gè)提交了相同 SQL 語句的用戶都有自己的私有 SQL 區(qū)，但它們使用同一個(gè)共享 SQL 區(qū)（shared SQL area）。即多個(gè)私有 SQL 區(qū)可以和同一個(gè)共享 SQL 區(qū)相聯(lián)系。
這一段官樣文章似乎清楚的描述了共享SQL區(qū)與私有SQL區(qū)的關(guān)系，但看過之后，估計(jì)還會(huì)讓許多如我一樣資質(zhì)愚鈍的人有霧里看花之感?？匆豢碩OM大叔更清晰的比喻：
共享SQL就像是DLL，一個(gè)共享的庫。
一條SQL就像是一個(gè)運(yùn)行中的應(yīng)用程序，它指向且使用共享庫DLL。但每一個(gè)應(yīng)用程序并不拷貝自己的私有二進(jìn)制復(fù)本，它們只是共享它。
應(yīng)用程序就是SQL，DLL共享庫就是共享SQL
根據(jù)TOM的說法，執(zhí)行計(jì)劃這些相關(guān)SQL的信息，在共享池中只留一份，各個(gè)會(huì)話的UGA中的還是指針．
也就是說，在應(yīng)用程序（SQL）中，應(yīng)該存有相應(yīng)DLL庫（共享SQL）的指針，憑這個(gè)指針，將私有SQL區(qū)與共享SQL區(qū)聯(lián)系起來。
這個(gè)指向共享SQL區(qū)的指針，和與之對(duì)應(yīng)的SQL聲明文本，是持續(xù)區(qū)最重要的內(nèi)容。
有關(guān)多個(gè)私有SQL區(qū)共享一個(gè)共享SQL區(qū)的方式，在這一點(diǎn)應(yīng)該就是借簽了操作系統(tǒng)的動(dòng)態(tài)鏈接庫。
四、用戶進(jìn)程如何取得查詢結(jié)果
Select * from table;
我們發(fā)出如上聲明后，查詢結(jié)果如何返回給用戶呢？
1.
先在Buffer cache定位塊，如果Table的塊Buffer cache中不存在，先從磁盤上讀一部分塊到Buffer cache。這就是物理讀。這一過程，是需要PGA的協(xié)助。
當(dāng)從磁盤上讀塊時(shí)，Oracle在每一次讀時(shí)，盡量多讀一些塊到內(nèi)存。但每次讀的塊數(shù)，受硬件、OS、Oracle的多塊讀參數(shù)和區(qū)大小等因素限制。
如果Table的塊在Buffer cache中可以找到，就不再?gòu)拇疟P讀取了，這叫邏輯讀。
2.
從Buffer cache中取出一條記錄，立即交給用戶。Oracle并不額外拷貝這條記錄到任何位置。
3.
重復(fù)第2步，如果Buffer cache中當(dāng)前塊中符合用戶條件的記錄被讀完，回到第1步。
在上述這三步中，下一條要讀取的記錄的相關(guān)信息，就記錄在運(yùn)行時(shí)區(qū)，我們也可以稱它為“結(jié)果集指針”。
下面結(jié)合一個(gè)靜態(tài)游標(biāo)的例子來具體說明：
declare
cursor aa1 is select id from t1 where myid<=10;
mx number;
begin
open aa1; ------>靜態(tài)游標(biāo)在打開時(shí)就已經(jīng)執(zhí)行了查詢
dbms_output.put_line('查詢行數(shù)1:'||aa1%rowcount); ---->但此時(shí)還沒有開始讀取，所以此Oracle并不知道
----查詢將返回的行數(shù)，因此此處將顯示0 。
for i in 1..10 loop
fetch aa1 into mx;
----用戶程序要求讀取記錄，運(yùn)行時(shí)區(qū)中指示要讀取第一條記錄。服務(wù)器進(jìn)程負(fù)責(zé)在Buffer ----cache中查找相應(yīng)塊，如果相應(yīng)塊不在Buffer cache中，就從磁盤中讀取相應(yīng)的塊到----Buffer cache中。然后從buffer cache取出第一行記錄，立即將值傳給用戶進(jìn)程。用戶-------進(jìn)程將得到的值傳到指定變量中，此處即mx 。第一條記錄讀取完畢，此時(shí)，運(yùn)行----時(shí)區(qū)中指示要讀取的記錄已經(jīng)變?yōu)榱说诙l
dbms_output.put_line('查詢結(jié)果:'||mx);
end loop;
dbms_output.put_line('查詢行數(shù)2:'||aa1%rowcount);
----上面的循環(huán)一共抓取了10條記錄，因此rowcount的
----值為10
close aa1;
end;
/
不管你的查詢將返回多少行，在查詢執(zhí)行后、抓取記錄時(shí)，抓取到哪條記錄，才會(huì)去讀相應(yīng)的塊。Oracle并不預(yù)先讀取所有的塊，構(gòu)造一個(gè)結(jié)果集，然后從結(jié)果集中返回所查詢的記錄。這從兩點(diǎn)可以得到證明，一是在游標(biāo)打開后，Oracle并不能返回游標(biāo)所查詢的行數(shù)。如上例中的rowcount ，只有你抓取了N行，Oracle才知道，原來結(jié)果集中包括N行。你一行都不抓取，rowcount的值就是0 。
另外我們可以用一個(gè)例子來證明：
1. 發(fā)布如下聲明，測(cè)試T4_1塊的占用情況（下面很多視圖將在Buffer cache一節(jié)中詳述）：
scott@MYTWO> select dbms_rowid.rowid_block_number(rowid) RID,min(rownum),max(rownum)
from t4_1 group by dbms_rowid.rowid_block_number(rowid);
RID MIN(ROWNUM) MAX(ROWNUM)
---------- ----------- -----------
31508
1
38
-----> 1至38條記錄占用塊31508
31509
39
75
-----> 39至75條記錄占用塊31509
31510
76
112
-----> 等等
31511
113
150
31512
151
187
31513
188
225
31514
226
262
………………
2.
查看T4_1現(xiàn)有多少塊在Buffer cache中
sys@MYTWO> select count(*) from x$bh where obj=7487;
COUNT(*)
----------
1
如果T4_1中有很多塊在Buffer cache中，設(shè)法將它們釋放。
（向某一表中大量插入或大量刪除即可以達(dá)到目的，如：delete big_table where rownum<=240000;
或按索引選擇一個(gè)大表：select /*+index(表名)*/* from 表名 where 索引列 is not null;）
3.
在執(zhí)行如下PL/SQL塊：
declare
cursor aa1 is select id from t4_1;
mx number;
begin
open aa1;
for i in 1..75 loop
fetch aa1 into mx;
dbms_output.put_line('查詢結(jié)果:'||mx);
end loop;
dbms_output.put_line('查詢行數(shù):'||aa1%rowcount);
close aa1;
end;
/
表T4_1共有2000行，64個(gè)塊，Aa1游標(biāo)將選擇它的全部行。但程序只抓取75行，從步驟1的查詢結(jié)果可知，也就是兩個(gè)塊。下面我們?cè)俅尾樵僗$BH，看看Oracle到底讀取了多少塊到內(nèi)存中：
4.
再次查詢X$BH：
sys@MYTWO> select count(*) from x$bh where obj=7487;
COUNT(*)
----------
14
可以看到，T4_1共有64個(gè)塊，但由于我們只抓取了一部分行，因此，只有一部分塊被送進(jìn)Buffer cache。這說明Oracle并不預(yù)先讀所有塊，而是“抓取到哪兒讀到哪兒”。
但我們只抓取前75行，應(yīng)該只讀兩個(gè)塊才對(duì)，為什么會(huì)一下讀14個(gè)塊呢？這當(dāng)然是由于多塊讀參數(shù)：db_file_multiblock_read_count，這就不屬Library cache的內(nèi)容了，本部分以Library cache為主，像多塊讀參數(shù)這些內(nèi)容，以后再討論。這里簡(jiǎn)單說一下。我這里設(shè)置此多塊讀參數(shù)為16，也就是說Oracle一次讀盤，如果有可能，會(huì)一下讀16個(gè)塊到Buffer cache。此參數(shù)以后再詳細(xì)討論，此處只所以沒有讀16個(gè)塊，而是讀14個(gè)塊，很可能是區(qū)大小的原因。運(yùn)行如下兩個(gè)查詢：
sys@MYTWO> select EXTENT_ID,FILE_ID,BLOCK_ID from dba_extents where segment_name='T4_1';
EXTENT_ID
FILE_ID
BLOCK_ID
---------- ---------- ----------
0
5
31505
1
5
31521
2
5
31537
3
5
31553
sys@MYTWO> select FILE#,dbablk,state from x$bh where obj=7487;
FILE#
DBABLK
STATE
---------- ---------- ----------
5
31507
1
5
31508
1
:
:
:
:
:
:
5
31520
1
已選擇14行。
可以看到T4_1的0號(hào)區(qū)塊編號(hào)從31505到31520 ，而Buffer cache中的塊從31507到31520，整好讀到一個(gè)區(qū)就停止了。
總結(jié)：Oracle并不將預(yù)先構(gòu)造結(jié)果集、也不緩存結(jié)果。記錄從buffer中直接讀出傳給用戶進(jìn)程。而游標(biāo)則在抓取行時(shí)，記錄下一個(gè)該抓取的行的信息。
Oracle并不緩存結(jié)果，我們可以從一個(gè)例看出，執(zhí)行下列聲明：
scott@MYTWO> set autotrace traceonly;
scott@MYTWO> select * from aa_1;
未選定行
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1
0
TABLE ACCESS (FULL) OF 'AA_1'
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
3
consistent gets
0
physical reads
0
redo size
215
bytes sent via SQL*Net to client
372
bytes received via SQL*Net from client
1
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
0
rows processed
你可以重復(fù)執(zhí)行select * from aa_1幾次，所得資料應(yīng)該是一樣的。
從上面的資料中可以看出，有3次邏輯讀。這說明Oracle在Buffer cache讀了三次。這三個(gè)邏輯讀，其實(shí)讀的都是段頭。這是Oracle重新開始一個(gè)查詢的標(biāo)志，說明Oracle正準(zhǔn)備重新開始抓取行，這說明Oracle并沒有緩存結(jié)果集。如果Oracle緩存有結(jié)果集的話，根本不必再到Buffer cache中讀取這些頭部信息塊，直接根據(jù)結(jié)果集返回結(jié)果即可。
五、游標(biāo)
是為游標(biāo)下一個(gè)準(zhǔn)確定義的時(shí)候了。
來源自文檔：A cursor is a handle or name for a private SQL area.
即：游標(biāo)是私有SQL區(qū)的句柄（或名字）。
游標(biāo)可以說是所有私有SQL區(qū)中各種信息的總稱，它并不實(shí)際存在，它只是由所有私有SQL區(qū)中的部件組成的。游標(biāo)可以說是私有SQL區(qū)的代名詞。
我們可以這樣說：用戶進(jìn)程通過游標(biāo)執(zhí)行自己的SQL聲明，仍然后將存儲(chǔ)在服務(wù)器中的信息取出呈現(xiàn)給用戶。
六、有關(guān)游標(biāo)的視圖
每一個(gè)游標(biāo)（即私有SQL區(qū)），在Library cache中都有一個(gè)對(duì)象與之對(duì)應(yīng)。并且，這個(gè)對(duì)象的句柄是加了模式為1的鎖的。
V$open_cursor 視圖是Oracle提供的用來查看當(dāng)前所有游標(biāo)的視圖。觀察它和X$KGLLK的對(duì)應(yīng)關(guān)系，可對(duì)游標(biāo)與Library cache的聯(lián)系，有一定的了解。
例10：觀察V$open_cursor與X$KGLLK的對(duì)應(yīng)關(guān)系：
v$fixed_view_definition視圖中有Oracle中所有動(dòng)態(tài)性能視圖（V$視圖）的定義，如下查詢，可知V$OPEN_CURSOR的由來。
select view_definition from v$fixed_view_definition where view_name='V$OPEN_CURSOR';
再進(jìn)一步查詢，即可看到V$OPEN_CURSOR與X$KGLLK的聯(lián)系。
通過V$OPEN_CURSOR與X$KGLLK的關(guān)系，可知對(duì)Oracle來說，一個(gè)打開的游標(biāo)，必將有一個(gè)加了LOCK為1的鎖的句柄與之對(duì)應(yīng)。
除了V$open_cursor，Oracle中并沒提供專門的顯示游標(biāo)信息的視圖。因?yàn)橛螛?biāo)中很多信息都存儲(chǔ)在PGA中。而Oracle中的V$系列動(dòng)態(tài)性能視圖，都是來自SGA，或控制文件。PGA中的信息，只有用戶會(huì)話的服務(wù)進(jìn)程才能訪問，因?yàn)樗皇枪蚕韮?nèi)存，因此，無法像X$視圖那樣由后臺(tái)進(jìn)程統(tǒng)一管理。
而V$open_cursor雖然是針對(duì)游標(biāo)的，但它是利用PGA中私有SQL區(qū)和SGA中共享SQL區(qū)的聯(lián)系，顯示的還是SGA中的信息。
