浅析Oracle共享SQL区、私有SQL区与游标

问题引出：
OCP 11g 052 第15题
15、Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)

A.Shared SQL Area will be allocated in the shared pool.

B.Shared SQL Area will be allocated when a session starts.

C.Shared SQL Area will be allocated in the large pool always.

D.Private SQL Area will be allocated in the Program Global Area (PGA) always.

E.Shared SQL Area and Private SQL Area will be allocated in the PGA or large pool.

F.The number of Private SQL Area allocations is dependent on the OPEN_CURSORS parameter.

Answer: AF  

首先我们来看下这两个区域的概念：

一、共享SQL区
共享SQL区，就是Library cace中的游标对象的句柄和子堆这些数据的另一种叫法。
二、私有SQL区的概念
私有SQL区，在专用服务器模式下，存贮在PGA中。
（复习一下PGA概念：
PGA在服务器端分配，记录各种不同的连接至Oracle服务器的进程的信息，如登录时的用户名、密码等。
每一个登录至Oracle的会话，都会在服务器端占用一块PGA。
当会话刚登录时，每个用户的PGA大概占个几百KB左右的内存，并可在以后根据会话中的操作伸、缩。）
私有 SQL 区被分为两个区域，这两部分的生命周期有所不同：
· 持续数据区（persistent area） 游标被关闭时被释放。
· 运行时区（run-time area），当游标执行结束就会被释放。
1). 持续数据区（persistent area）
当打开一个游标时，持续区内存被创建。包含绑定信息（bind information）、SQL声明本身等之类的数据。
此区只在游标关闭时才会被释放。
2). 运行时区 （run-time area）
当游标在执行时被创建。当语句执行完毕或cancel之后被释放。
运行区或会话特定区是你的会话维护的相关游标的状态信息，
例如：你的查询开始时的SCN、当前结果集的位置或其他特定于你的会话的东西。
另外，工作区（用于排序、哈希链接、Bitmap merge和Bitmap create等的内存区），也是运行时区的一部分。   
三、 私有SQL区与共享SQL的关系
总结上面的内容可知，持续数据区中的信息，是在游标打开后，到游标被执行前，这一段期间所使用的内存。
凡在此阶段中需被存进内存的信息，都是使用持续数据区的内存。
因此，除了上面所提到的绑定信息、SQL声明本身之外，还有一类重要的信息，就是用来和共享SQL区某一SQL关联的地址信息。
为什么私有SQL区要和共享SQL区关联，因为执行计划在共享SQL中存储着。
下面一段话来自文档：
    Oracle为执行SQL语句的会话分配一个私有SQL区。每个提交了SQL语句的会话都有一个私有SQL区。
每个提交了相同SQL语句的用户都有自己的私有SQL区，但它们使用同一个共享 SQL 区（shared SQL area）。
即多个私有SQL区可以和同一个共享SQL区相联系。
    这一段官样文章似乎清楚的描述了共享SQL区与私有SQL区的关系，但看过之后，
估计还会让许多如我一样资质愚钝的人有雾里看花之感。看一看TOM大叔更清晰的比喻：
共享SQL就像是DLL，一个共享的库。
一条SQL就像是一个运行中的应用程序，它指向且使用共享库DLL。
但每一个应用程序并不拷贝自己的私有二进制复本，它们只是共享它。
应用程序就是SQL，DLL共享库就是共享SQL 根据TOM的说法，执行计划这些相关SQL的信息，
在共享池中只留一份，各个会话的UGA中的还是指针。 
也就是说，在应用程序（SQL）中，应该存有相应DLL库（共享SQL）的指针，凭这个指针，将私有SQL区与共享SQL区联系起来。
这个指向共享SQL区的指针，和与之对应的SQL声明文本，是持续区最重要的内容。
有关多个私有SQL区共享一个共享SQL区的方式，在这一点应该就是借签了操作系统的动态链接库。
四、用户进程如何取得查询结果
Select * from table;
我们发出如上声明后，查询结果如何返回给用户呢？
1. 先在Buffer cache定位块，如果Table的块Buffer cache中不存在，先从磁盘上读一部分块到Buffer cache。
这就是物理读。这一过程，是需要PGA的协助。
当从磁盘上读块时，Oracle在每一次读时，尽量多读一些块到内存。
但每次读的块数，受硬件、OS、Oracle的多块读参数和区大小等因素限制。
如果Table的块在Buffer cache中可以找到，就不再从磁盘读取了，这叫逻辑读。
2. 从Buffer cache中取出一条记录，立即交给用户。Oracle并不额外拷贝这条记录到任何位置。
3. 重复第2步，如果Buffer cache中当前块中符合用户条件的记录被读完，回到第1步。
在上述这三步中，下一条要读取的记录的相关信息，就记录在运行时区，我们也可以称它为“结果集指针”。
下面结合一个静态游标的例子来具体说明：
declare
cursor aa1 is select id from t1 where myid<=10;
mx number;
begin
open aa1; ------>静态游标在打开时就已经执行了查询
dbms_output.put_line('查询行数1:'||aa1%rowcount); ---->但此时还没有开始读取，所以此Oracle并不知道
----查询将返回的行数，因此此处将显示0 。
for i in 1..10 loop
fetch aa1 into mx;
----用户程序要求读取记录，运行时区中指示要读取第一条记录。服务器进程负责在Buffer ----cache中查找相应块，
如果相应块不在Buffer cache中，就从磁盘中读取相应的块到----Buffer cache中。然后从buffer cache取出第一行记录，
立即将值传给用户进程。用户-------进程将得到的值传到指定变量中，此处即mx 。第一条记录读取完毕，
此时，运行----时区中指示要读取的记录已经变为了第二条
dbms_output.put_line('查询结果:'||mx);
end loop;
dbms_output.put_line('查询行数2:'||aa1%rowcount);
----上面的循环一共抓取了10条记录，因此rowcount的值为10
close aa1;
end;
/
不管你的查询将返回多少行，在查询执行后、抓取记录时，抓取到哪条记录，才会去读相应的块。
Oracle并不预先读取所有的块，构造一个结果集，然后从结果集中返回所查询的记录。
这从两点可以得到证明，一是在游标打开后，Oracle并不能返回游标所查询的行数。
如上例中的rowcount ，只有你抓取了N行，Oracle才知道，原来结果集中包括N行。你一行都不抓取，rowcount的值就是0 。
另外我们可以用一个例子来证明：
1. 发布如下声明，测试T4_1块的占用情况（下面很多视图将在Buffer cache一节中详述）：
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现有多少块在Buffer cache中
sys@MYTWO> select count(*) from x$bh where obj=7487;
COUNT(*)
----------
1
如果T4_1中有很多块在Buffer cache中，设法将它们释放。
（向某一表中大量插入或大量删除即可以达到目的，如：delete big_table where rownum<=240000;
或按索引选择一个大表：select /*+index(表名)*/* from 表名 where 索引列 is not null;）
3. 在执行如下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('查询结果:'||mx); 
end loop; 

dbms_output.put_line('查询行数:'||aa1%rowcount);
close aa1;
end;
/
表T4_1共有2000行，64个块，Aa1游标将选择它的全部行。但程序只抓取75行，从步骤1的查询结果可知，也就是两个块。
下面我们再次查询X$BH，看看Oracle到底读取了多少块到内存中：
4. 再次查询X$BH：
sys@MYTWO> select count(*) from x$bh where obj=7487;
COUNT(*)
----------
14
可以看到，T4_1共有64个块，但由于我们只抓取了一部分行，因此，只有一部分块被送进Buffer cache。
这说明Oracle并不预先读所有块，而是“抓取到哪儿读到哪儿”。
但我们只抓取前75行，应该只读两个块才对，为什么会一下读14个块呢？
这当然是由于多块读参数：db_file_multiblock_read_count，
这就不属Library cache的内容了，本部分以Library cache为主，像多块读参数这些内容，以后再讨论。
这里简单说一下。我这里设置此多块读参数为16，也就是说Oracle一次读盘，如果有可能，会一下读16个块到Buffer cache。
此参数以后再详细讨论，此处只所以没有读16个块，而是读14个块，很可能是区大小的原因。运行如下两个查询：
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号区块编号从31505到31520 ，而Buffer cache中的块从31507到31520，整好读到一个区就停止了。
总结：Oracle并不将预先构造结果集、也不缓存结果。记录从buffer中直接读出传给用户进程。
而游标则在抓取行时，记录下一个该抓取的行的信息。
Oracle并不缓存结果，我们可以从一个例看出，执行下列声明：
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
你可以重复执行select * from aa_1几次，所得资料应该是一样的。
从上面的资料中可以看出，有3次逻辑读。这说明Oracle在Buffer cache读了三次。
这三个逻辑读，其实读的都是段头。这是Oracle重新开始一个查询的标志，
说明Oracle正准备重新开始抓取行，这说明Oracle并没有缓存结果集。如果Oracle缓存有结果集的话，
根本不必再到Buffer cache中读取这些头部信息块，直接根据结果集返回结果即可。
五、游标
是为游标下一个准确定义的时候了。
来源自文档：A cursor is a handle or name for a private SQL area.
即：游标是私有SQL区的句柄（或名字）。
游标可以说是所有私有SQL区中各种信息的总称，它并不实际存在，它只是由所有私有SQL区中的部件组成的。
游标可以说是私有SQL区的代名词。
我们可以这样说：用户进程通过游标执行自己的SQL声明，仍然后将存储在服务器中的信息取出呈现给用户。
六、有关游标的视图
每一个游标（即私有SQL区），在Library cache中都有一个对象与之对应。并且，这个对象的句柄是加了模式为1的锁的。  
V$open_cursor 视图是Oracle提供的用来查看当前所有游标的视图。
观察它和X$KGLLK的对应关系，可对游标与Library cache的联系，有一定的了解。
例10：观察V$open_cursor与X$KGLLK的对应关系：
v$fixed_view_definition视图中有Oracle中所有动态性能视图（V$视图）的定义，如下查询，可知V$OPEN_CURSOR的由来。
select view_definition from v$fixed_view_definition where view_name='V$OPEN_CURSOR';
再进一步查询，即可看到V$OPEN_CURSOR与X$KGLLK的联系。
通过V$OPEN_CURSOR与X$KGLLK的关系，可知对Oracle来说，一个打开的游标，必将有一个加了LOCK为1的锁的句柄与之对应。
除了V$open_cursor，Oracle中并没提供专门的显示游标信息的视图。因为游标中很多信息都存储在PGA中。
而Oracle中的V$系列动态性能视图，都是来自SGA，或控制文件。
PGA中的信息，只有用户会话的服务进程才能访问，因为它不是共享内存，
因此，无法像X$视图那样由后台进程统一管理。
而V$open_cursor虽然是针对游标的，但它是利用PGA中私有SQL区和SGA中共享SQL区的联系，显示的还是SGA中的信息。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064835/viewspace-1801478/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27064835/viewspace-1801478/