oracle sql 子游标_Oracle 父子游标

游标

从Oracle数据库管理员的角度上说，游标是对存储在库缓存中的可执行对象的统称。SQL语句是存储在库缓存中的，它是游标。除了它之外，还有Oracle的存储过程也是存储在库缓存中的可执行对象，从Oracle DBA的角度上说，它也是游标。Oracle也把它算为游标，在某些和游标相关的视图中，也会显示存储过程的一些信息的。但从开发者的角度说，只有SQL语句才是游标。

子游标与父游标。

如果两个游标的文本一模一样，但由于环境不同，比如，游标所操作的表是不同用户下的同名表，这两个游标是不能共享执行计划的。它们都有各自的执行计划存在库缓存中。这两个游标就是子游标，Oracle还会建立一个父游标，父游标中没有执行计划，它只是文本相同但执行计划不同的所有游标的代表。

其实在库缓存中，即使没有文本相同的子游标，Oracle也会为每个游标都创建父游标。因为父游标是文本相同的子游标的代表吗，所有文本相同的游标共享同一个父游标。

也就是说，只要你执行SQL语句，Oracle都会在库缓存中保存一父一子两个游标。如果你执行了文本相同但环境不同因而不能共享执行计划的SQL语句，那么一个父游标可能就对应多个子游标。

父游标没有执行计划，它只有一些信息管理性数据，Oracle添加它的目的就是为了管理文本相同的游标。有一个视图是专门针对父游标的，就是V$sqlarea。

V$SQL常用字段

SHARABLE_MEM：游标所占共享内存

SORTS：游标完成的排序次数

FETCHES：抓取的次数

PX_SERVERS_EXECUTIONS：以并行方式执行的总次数

EXECUTIONS：执行次数

LOADS：游标被加载或重新加载到库缓存中的次数。游标只所以被重新加载有可能是游标无效或库缓存内存不足。

INVALIDATIONS：游标的无效次数

PARSE_CALLS：游标的解析次数，包括硬解析与软解析

DIRECT_WRITES：游标直接写的次数

DISK_READS：游标执行了多少次物理读

USER_IO_WAIT_TIME：用户I/O等待时间

CPU_TIME：游标解析、执行、抓取时所用的CPU时间。单位是微秒。

ELAPSED_TIME：游标解析、执行、抓取时所用的总时间。单位是微秒。

V$sql_text视图

这个视图的目的是显示过长的SQL语句文本。对于这些文本过长的SQL语句，在V$sql_text中将分多行显示完整的SQL语句，每行显示64字节。效果如下：

步1：我发布一个文本很长的SQL语句，这个语句没什么意义，只是想让它文本长一点：

SQL> select case id when 1 then 'one' when 2 then 'two' end case, name from (select * from ui1 where id<=1) where id=1 or id=2;

CAS NAME

--- -----

one 1

步2：我需要知道此语句的HASH值，然后以此HASH值为条件在V$SQL_TEXT中查询：

SQL> select sql_text, hash_value from v$sqlarea where sql_text like 'select case id when 1 then%';

SQL_TEXT HASH_VALUE

---------------------------------------------------------------------- ----------

select case id when 1 then 'one' when 2 then 'two' end case, name from 4270697726

(select * from ui1 where id<=1) where id=1 or id=2

此语句的HASH值是4270697726。按照此HASH值在V$sql_text中查询：

SQL> select rownum, sql_text from v$sqltext where hash_value=4270697726;

ROWNUM SQL_TEXT

---------- ----------------------------------------------------------------------

1 e from (select * from ui1 where id<=1) where id=1 or id=2

2 select case id when 1 then 'one' when 2 then 'two' end case, nam

V$open_cursor与Open_cursor参数

这个视图和参数涉及游标的打开。什么是游标的打开，就是在库缓存中，用户在软、硬解析游标时，会在游标对象的句柄上加一个锁，也就是Library cache lock。在解析并执行完游标后，这个锁并不会马上去掉，而是会一直保留着，直到用户发出了Close命令关闭游标时为止。我们在SQL*Plus命令窗口中发出的命令，在抓取完所有行后，SQL*Plus将自动为我们发出Close命令来关闭游标。

SQL> show parameter cursor_sharing

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing string EXACT

当游标打开时，Library cache lock将一直保持，这样，即使库缓存内存紧张，需要老化对象，也不会老化这些还正在加锁的对象。因此，如果用户不停的要求数据库服务器打开游标、执行SQL，但却忘了关闭游标，这很容易耗尽共享池的内存。为此，Oracle准备了一个参数，就是Open_cursor，它的默认值在9i下是50，在10g中是300，也就是说，在10g下，每个会话最多只能同时打开300个游标。有了这个限制，就不用害怕用户不停的打开游标但又不关闭它，而耗尽共享池内存了。

如果会话同时打开的游标数量超出了Open_cursor参数的限制，Oracle将禁止会话打开新的游标。同时报出错误：ORA-01000: 超出打开游标的最大数 。

在用户断开会话的连接后，会话打开的这些游标将自动关闭。

V$open_cursor视图专用来查看当前会话打开的游标信息。它只能查看当前会话打开的游标。

CURSOR_SHARING参数

如果应用程序中有很多类似下面这样的SQL语句：

select * from 某表 where id=1;

select * from 某表 where id=2;

select * from 某表 where id=50;

等等，这些SQL语句严格来说是无法共享游标(也就是共享执行计划)的，但是这些语句所需要的执行计划其实都是一样的。无论你在表中查询ID为1的行还是查询ID为100的行，执行方式应该是一样的。如果你想让这样的语句共享游标，那么，你可以改变Cursor_sharing参数的值。

此参有三个值：

EXACT：这个值是默认值。除非游标文本一模一样，否则不会共享游标。

SIMILAR：这个最智能，如果游标只有条件中的数据值部分不同，并且库缓存中原有游标的执行计划对于新执行的SQL语句也是最优的，将不再为SQL语句创建新的游标，而是让它共享库缓存中原有的游标。

FORCE ：不比较执行计划是否最优，只要游标中除了条件中的数据值部分不同外，其他部分都相同，就会共享游标。

Oracle可以将一部分执行次数比较多的游标的信息缓存在会话的私人内存PGA中，这样当被缓存游标再被执行时，很多数据不必再到库缓存中寻找，会话直接可以在自己的PGA中取出。这可以大大提高软解析的速度，这样的解析被称为更软的软解析(或快速软解析)。一般来说，会话在执行游标时，第一步会到自己的PGA中搜索游标，如果找到了，这就是更软的软解析。如果游标没有被缓存到PGA中，再到库缓存中查找，如果找到了这就是普通的软解析。如果库缓存中也没有，就进行硬解析，重新生成游标相关数据和执行计划。

如果会话在执行游标时，发现游标的总的执行次数已经超过了三次，就会将游标信息缓存在自己的PGA中。此参数的作用是设定一个会话共可以缓存多少个游标。

此参数的值如果比较大，将会耗用更多的PGA和共享池内存，但是，这对提高软解析速度是很有帮助的。如果你的数据库软解析耗用了过多的时间，可以尝试加大此参数的值。在10g中，此参数默认值是30。在大型OLTP应用中，此参数的值一般都设置为几百甚至上千。

version count

V$SQLAREA有一个列是V$SQL中没有的，就是：VERSION_COUNT，它是对应同一父游标的子游标的数量。如果这个数字太高，可能代表由于某些原因使本可以共享执行计划的游标没有共享

我们使用实验SQL，通过视图v$sqlarea和v$sql可以观察到library cache中的父子游标情况。v$sqlarea中保存父游标信息，而v$sql保存子游标信息。

SQL> select /*+version_count */count(*) from t where wner='SCOTT';

COUNT(*)

----------

14

SQL> select sql_id, version_count fromv$sqlareawhere sql_text like 'select /*+version_count */count(*)%';

SQL_ID VERSION_COUNT

------------- -------------

54fuganxkyky6 1

SQL> select sql_id, child_number fromv$sql where sql_id='54fuganxkyky6';

SQL_ID CHILD_NUMBER

------------- ------------

54fuganxkyky6 0

SQL语句(sql_id=54fuganxkyky6)对应一个父游标和一个子游标。

version count就表示当前父游标下对应子游标的个数。如果一个父游标对应的子游标version count过多，也就是对应了很多的子游标对象。这样，当server process检查可共享的游标时，就需要长时间的检索子游标列表。

最有名的version count过多问题是由于设置cursor_sharing参数为similar后，引发的version count错误。