[转载]Oracle游标共享，父游标和子游标的概念

Oracle设计shared pool内存的一个主要目的就是语句共享，通过语句共享，节省语句的解析时间，从而得到性能的提升。如果解释shared pool内存，工作量太大，这里只是做一个非常简单的演示。

查看语句共享可以借助两个数据字典：V$SQLAREA和V$SQL
V$SQLAREA：保留SQL语句的父游标信息，可以通过SQL_ID标识，其中的VERSION_COUNT列表示子游标的数量
V$SQL    ：保留SQL语句的子游标信息，可以通过SQL_ID和CHILD_NUMBER标识

V$SQL_SHARED_CURSOR:语句产生子游标的原因


首先确认参数cursor_sharing,默认值是EXACT，也就是说只有在不使用绑定变量的情况下，语句要完全一样才可以共享，包括大小写、空格回车等所有都要一样。
SQL> conn / as sysdba
Connected.
SQL> show parameter cursor_sharing
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

清空shared_pool内存，这个命令可以在实验的时候使用，在生产系统库要谨慎

SQL> alter system flush shared_pool;
System altered.


在SCOTT用户下和TJ用户下有一模一样的表叫做demo，这是我准备的实验场景，以下操作，浅色表示第一个窗口或者session，深色表示在另外一个窗口或session查询动态性能视图信息验证


在第一个窗口：
SQL> conn scott/tiger
Connected.
SQL> select * from demo;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1200
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975

SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH

由于上边这条语句是清空share pool后第一次执行，所以Oracle要做硬解析，生成游标，确切来讲是一个父游标和一个子游标，分别可以通过V$SQLAREA和V$SQL查到相关信息



在第二个窗口：
[oracle@asm11g workshop]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 16 21:20:24 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> get qs.sql
  1  col sql_text for a50
  2  set linesize 120
  3  col exec for 999
  4  col invalid for 99
  5  col loads for 999
  6  select sql_text,
  7         sql_id,
  8         hash_value,
  9         executions exec,
10         loads,
11         invalidations invalid
12  from v$sqlarea
13* where sql_text like '&text%'
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    1     1       0



SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          1     1

通过查询我们可以看到，V$SQLAREA数据字典中的是父游标的信息，语句解析（LOADS）了一次，执行（EXEC）了一次，当然在V$SQL中也可以看到类似的信息。



到第一个窗口：
SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH

再一次执行上一条语句，由于语句在share pool内存中已经有游标存在，所以语句会共享。



到第二个窗口：
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    2     1       0



SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          2     1

通过上面的查询我们可以看到，语句解析（LOADS）了一次，执行（EXEC）了两次，在子游标也可以看到相同的信息。



到第一个窗口：
SQL> select empno,ename from demo where empno=7499;

     EMPNO ENAME
---------- ----------
      7499 ALLEN

这个语句和刚刚的语句的区别在于我把条件改成了7499，这样这就是一个全新的语句，Oracle要做硬解析，并在内存中申请新的父子游标。



第二个窗口：

SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7499      0m3wzw5mrdg8z  1735834911    1     1       0
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    2     1       0



到第一个窗口：
切换用户到tj，tj用户下也有相同的表demo，执行select empno,ename from demo where empno=7369;
SQL> conn tj/tj
Connected.
SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH



到第二个窗口：
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7499      0m3wzw5mrdg8z  1735834911    1     1       0
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    3     2       0

SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          2     1
dhdkpzyv9b1w7            1          1     1



虽然tj用户下的语句和scott用户下写的语句一模一样，但是语意显然不一样，两个demo表属于不同的用户，所以我们看到，Oracle的父游标解析(LOADS)加1，执行次数也加1，从父游标的角度来讲，语句是共享的，而在子游标中，有了区别，新生成了一个子游标CHILD_NUMBER 为1，解析(LOADS)了一次，执行了一次。



到第一个窗口：

再次执行语句
SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH



到第二个窗口：
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7499      0m3wzw5mrdg8z  1735834911    1     1       0
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    4     2       0

SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          2     1
dhdkpzyv9b1w7            1          2     1

我们看到语句的父游标解析次数没有增加，执行次数加1，而对于子游标来说，CHILD_NUMBER 为1的子游标执行次数加1。



产生子游标的原因很多，比如我上边的用户方案（SCHEMA）改变的例子，当然还有很多其他原因也可以导致子游标的产生，比如优化器模式的改变，或者绑定变量的窥视等，如果你想确定是由那种原因造成的，需要查看v$sql_shared_cursor
对于刚才的例子，属于验证/事物检查不匹配

SQL> select sql_id,CHILD_NUMBER,AUTH_CHECK_MISMATCH from v$sql_shared_cursor where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER A
------------- ------------ -
dhdkpzyv9b1w7            0 N
dhdkpzyv9b1w7            1 Y