v$sql_shared_cursor

精选转载

daizy 2013-09-12 17:07:00

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
--------------------------------------------------