对视图v$sql_shared_cursor的说明

v$sql_shared_cursor学习

小结：

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

每次执行一条SQL语句时，如果其对应的当前已经存在于library cache里的一个父游标下的各个子游标都不能被该SQL语句重新使用（即共享），则会产生一个新的子游标，此时就会在V$SQL_SHARED_CURSOR里新增一行，分别描述该SQL语句不能使用当前已经存在的各个子游标的原因。当然一个父游标下的第一个子游标产生时也会在该视图上新增一行，只是各个该视图上的描述原因的列的值都为N。

v$sql_shared_cursor

ORACLE使用子游标去区分一个不能被共享的SQL，因为虽然SQL相同，但是SQL所指向的对象使不同的。也就是说，这些SQL的父游标都是一样的，HASH_VALUE值都相同。例如，数据库有三个表T，有这样一个语句，select *from T,由于每个T都被不同的对象使用或是用户使用，而在数据库级别，这些语句都是一样的，HASH_VALUE都相同，但是他们的子游标就不同了，这就会产生High Version Counts，由于HASH_VALUE相同，持有LATCH会不放，所以当PARSE的时候就会产生LATCH FREE。这是产生High Version的一个方面。另外正如大家前面所说的，数据库的BUG也会引发这个问题。至于第一个问题，可以通过查询V$SQL_SHARED_CURSOR得到详细关于子游标不能共享的原因。

例子

SQL> create table t(name varchar2(2000));
Table created.
SQL>  declare
     a varchar2(1) := 'x';
     b varchar2(100) := rpad('x',100,'x');
     c varchar2(500) := rpad('x',500,'x');
     d varchar2(1000) := rpad('x',1000,'x');
    begin
     insert into t values(a);
     insert into t values(b);
     insert into t values(c);
    insert into t values(d);
  end;
  2    3    4    5    6    7    8    9   10   11   12  /
PL/SQL procedure successfully completed.
查看父游标和子游标的hash值

SQL> select PARSING_USER_ID puid,parsing_schema_id psid,sql_text,sql_id,child_address from v$sql where sql_text like 'INSERT INTO T VALUES%';
      PUID       PSID SQL_TEXT                                           SQL_ID                     CHILD_AD
---------- ---------- -------------------------------------------------- -------------------------- --------
        55         55 INSERT INTO T VALUES(:B1 )                         9bay73nakuyw9              2D261260
        55         55 INSERT INTO T VALUES(:B1 )                         9bay73nakuyw9              2D26117C
        55         55 INSERT INTO T VALUES(:B1 )                         9bay73nakuyw9              2D261098
查看v$sql_shared_cursor中游标不能共享的原因
SQL> select * from v$sql_shared_cursor where sql_id='9bay73nakuyw9';
SQL_ID                     ADDRESS  CHILD_AD CHILD_NUMBER UNB SQL OPT OUT STA LIT SEC EXP BUF PDM INS SLA TYP AUT BIN DES LAN TRA ROW INS INS REM LOG
-------------------------- -------- -------- ------------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
INC OVE SQL MV_ USE TYP NO_ FLA ANY INC TOP DIF LOG DIF BIN PLS CUR STB ROW PQ_ TOP MUL BIN MV_ ROL OPT PX_ MV_ FLA LIT
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
9bay73nakuyw9              2D261344 2D261260            0 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N

9bay73nakuyw9              2D261344 2D26117C            1 N   N   N   N   N   N   N   N   N   N   N   N   N   N   Y   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N

9bay73nakuyw9              2D261344 2D261098            2 N   N   N   N   N   N   N   N   N   N   N   N   N   N   Y   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
可以看到由于bind_mismatch游标没有被共享，oracle认为绑定变量之间的值不一致。

由于v$sql_shared_cursor视图的字段较多，查看起来较为不便，有如下存储过程可以解决这个问题
$ cat shared_cursor.sql

SET  serveroutput on size 100000;
DECLARE
   c           NUMBER;
   col_cnt     NUMBER;
   col_rec     DBMS_SQL.desc_tab;
   col_value   VARCHAR2 (4000);
   ret_val     NUMBER;
BEGIN
   c := DBMS_SQL.open_cursor;
   DBMS_SQL.parse
      (c,
       'select q.sql_text, s.*
      from v$sql_shared_cursor s, v$sql q
      where s.sql_id = q.sql_id
          and s.child_number = q.child_number
          and q.sql_id like ''&1''',
       DBMS_SQL.native
      );
   DBMS_SQL.describe_columns (c, col_cnt, col_rec);

   FOR idx IN 1 .. col_cnt
   LOOP
      DBMS_SQL.define_column (c, idx, col_value, 4000);
   END LOOP;
   ret_val := DBMS_SQL.EXECUTE (c);
   WHILE (DBMS_SQL.fetch_rows (c) > 0)
   LOOP
      FOR idx IN 1 .. col_cnt
      LOOP
         DBMS_SQL.COLUMN_VALUE (c, idx, col_value);

         IF col_rec (idx).col_name IN ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER', 'SQL_TEXT')
         THEN
            DBMS_OUTPUT.put_line (RPAD (col_rec (idx).col_name, 30) || ' = ' || col_value);
         ELSIF col_value = 'Y'
         THEN
            DBMS_OUTPUT.put_line (RPAD (col_rec (idx).col_name, 30) || ' = ' || col_value);
         END IF;
      END LOOP;
      DBMS_OUTPUT.put_line ('--------------------------------------------------');
   END LOOP;

   DBMS_SQL.close_cursor (c);
END;
/
SET serveroutput off;

SQL> @/home/oracle/shared_cursor.sql
Enter value for 1: 9bay73nakuyw9
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''9bay73nakuyw9''',
SQL_TEXT                       = INSERT INTO T VALUES(:B1 )
SQL_ID                         = 9bay73nakuyw9
ADDRESS                        = 2D261344
CHILD_ADDRESS                  = 2D261260
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = INSERT INTO T VALUES(:B1 )
SQL_ID                         = 9bay73nakuyw9
ADDRESS                        = 2D261344
CHILD_ADDRESS                  = 2D26117C
CHILD_NUMBER                   = 1
BIND_MISMATCH                  = Y
--------------------------------------------------
SQL_TEXT                       = INSERT INTO T VALUES(:B1 )
SQL_ID                         = 9bay73nakuyw9
ADDRESS                        = 2D261344
CHILD_ADDRESS                  = 2D261098
CHILD_NUMBER                   = 2
BIND_MISMATCH                  = Y
--------------------------------------------------
PL/SQL procedure successfully completed.
输出简洁明了

参考至：《让Oracle跑得更快》谭怀远著

               http://space.itpub.net/267265/viewspace-713219
               http://ruanrong.itpub.net/post/4817/275709