前天有个朋友问我了个问题发现v$sqlarea 中文本相同的sql还有很多记录，也就是并未共享游标，也许很多人知道当cursor_sharing=force，可以尽可能的减小hard parse,记住是min,并不是完全防止硬解析，因为解析也很浪费资源
下面看我的实验
SQL> conn system/oracle
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> conn anbob/anbob
Connected.
SQL> declare
2 c int :=100;
3 begin
4 insert into testsharing values(100,200);
5 insert into testsharing values(300,400);
6 insert into testsharing values(c,500);
7 insert into testsharing values(c,600);
8 execute immediate 'insert into testsharing values(911,1)';
9 execute immediate 'insert into testsharing values(911,2)';
10 commit;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> conn system/oracle
Connected.
SQL> col sql_text for a70
SQL> l
1* select executions,loads,sql_text,hash_value,address,sql_id from v$sqlarea where lower(sql_text) like '%testsharing%' and sql_text not like '%$%'
SQL> /
EXECUTIONS LOADS SQL_TEXT HASH_VALUE ADDRESS SQL_ID
---------- ---------- ---------------------------------------------------------------------- ---------- -------- --------------------
1 2 declare c int :=100; begin insert into testsharing values(100,200) 4245713616 33ABEFB4 0mbv6jgyj0wqh
; insert into testsharing values(300,400); insert into testsharing v
alues(c,500); insert into testsharing values(c,600); execute immedia
te 'insert into testsharing values(911,1)'; execute immediate 'insert
into testsharing values(911,2)'; commit; end;
1 1 INSERT INTO TESTSHARING VALUES(:B1 ,600) 3173049334 33A26CEC 7cdbkm2yk1szq
2 1 insert into testsharing values(:"SYS_B_0",:"SYS_B_1") 1921846338 33AE1570 9tbyx65t8u322
1 1 INSERT INTO TESTSHARING VALUES(300,400) 596477512 33A693DC 5ju5zhwjsv1k8
1 1 INSERT INTO TESTSHARING VALUES(100,200) 4073443016 338E0714 dca5787tcrmq8
1 1 INSERT INTO TESTSHARING VALUES(:B1 ,500) 3038634095 338C13CC aukkkbqujvs3g
6 rows selected.
note:
当然如果plsql中的两条sql完全一样，包括字面常量是还是可以共享的
execute immediate 的也是可以共享
SQL> conn anbob/anbob
Connected.
SQL> variable c number;
SQL> variable d number;
SQL> begin
2 :c:=1000;
3 :d:=2000;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> insert into testsharing values(1,100);
1 row created.
SQL> insert into testsharing values(:c,200);
1 row created.
SQL> insert into testsharing values(:d,300);
1 row created.
SQL> insert into testsharing values(:d,:c);
1 row created.
SQL> insert into testsharing values(2,500);
1 row created.
SQL> commit;
Commit complete.
SQL> conn system/oracle
Connected.
SQL> select executions,loads,sql_text,hash_value,address,sql_id from v$sqlarea where lower(sql_text) like '%testsharing%' and sql_text not like '%$%';
EXECUTIONS LOADS SQL_TEXT HASH_VALUE ADDRESS SQL_ID
---------- ---------- -------------------------------------------------------------------------------- ---------- -------- --------------------
1 1 insert into testsharing values(:d,:c) 1444290358 338CAB68 bx2d4sxb1c7tq
1 1 insert into testsharing values(:d,:"SYS_B_0") 1404221571 339D2A10 6dg5dhj9v5f43
2 2 insert into testsharing values(:"SYS_B_0",:"SYS_B_1") 1921846338 33AE1570 9tbyx65t8u322
1 1 insert into testsharing values(:c,:"SYS_B_0") 335258613 339D2B68 awdyb549zr8zp
note:
如果指定了不同的绑定变量表，parent cursor 也是不可以共享的
除了上面的还有就是准备修改cursor_sharing 前有时要必须连续 flush shared_pool 两次，
接自 MOS
Interesting. You may need to flush the shared pool “harder”… Try your testcase again, but issue the ALTER SYSTEM FLUSH SHARED_POOL twice in a row before changing the cursor_sharing setting. It looks like that the statement doesn’t show up in V$SQLAREA any longer but still seems not to be flushed entirely and I can find some left-overs of it in the underlying X$KGL library cache fixed tables with the same hash value. After the second “flush shared pool” these are gone and then the cursor sharing works as expected.
还有就是在一些版本中的bug,Bug 7026158
如sql: xx where a=xx and id in(1);
This is happening because of the use of the IN operator.
The same queries are shared when using “=” instead.
Also when optimizer_dynamic_sampling is greater than 1, the above queries are
not shared.
When optimizer_dynamic_sampling =1 then they are shared.
如果列上有histogram也会影响share cursor,还有就是java 程序调用 sql时的各种bug,慢慢研究
SQL不能共享的原因:
select * from (select sql_id, nonshared_reason, count(*) from v$sql_shared_cursor unpivot (nonshared_value for nonshared_reason in ( UNBOUND_CURSOR as 'UNBOUND_CURSOR', SQL_TYPE_MISMATCH as 'SQL_TYPE_MISMATCH', OPTIMIZER_MISMATCH as 'OPTIMIZER_MISMATCH', OUTLINE_MISMATCH as 'OUTLINE_MISMATCH', STATS_ROW_MISMATCH as 'STATS_ROW_MISMATCH', LITERAL_MISMATCH as 'LITERAL_MISMATCH', FORCE_HARD_PARSE as 'FORCE_HARD_PARSE', EXPLAIN_PLAN_CURSOR as 'EXPLAIN_PLAN_CURSOR', BUFFERED_DML_MISMATCH as 'BUFFERED_DML_MISMATCH', PDML_ENV_MISMATCH as 'PDML_ENV_MISMATCH', INST_DRTLD_MISMATCH as 'INST_DRTLD_MISMATCH', SLAVE_QC_MISMATCH as 'SLAVE_QC_MISMATCH', TYPECHECK_MISMATCH as 'TYPECHECK_MISMATCH', AUTH_CHECK_MISMATCH as 'AUTH_CHECK_MISMATCH', BIND_MISMATCH as 'BIND_MISMATCH', DESCRIBE_MISMATCH as 'DESCRIBE_MISMATCH', LANGUAGE_MISMATCH as 'LANGUAGE_MISMATCH', TRANSLATION_MISMATCH as 'TRANSLATION_MISMATCH', BIND_EQUIV_FAILURE as 'BIND_EQUIV_FAILURE', INSUFF_PRIVS as 'INSUFF_PRIVS', INSUFF_PRIVS_REM as 'INSUFF_PRIVS_REM', REMOTE_TRANS_MISMATCH as 'REMOTE_TRANS_MISMATCH', LOGMINER_SESSION_MISMATCH as 'LOGMINER_SESSION_MISMATCH', INCOMP_LTRL_MISMATCH as 'INCOMP_LTRL_MISMATCH', OVERLAP_TIME_MISMATCH as 'OVERLAP_TIME_MISMATCH', EDITION_MISMATCH as 'EDITION_MISMATCH', MV_QUERY_GEN_MISMATCH as 'MV_QUERY_GEN_MISMATCH', USER_BIND_PEEK_MISMATCH as 'USER_BIND_PEEK_MISMATCH', TYPCHK_DEP_MISMATCH as 'TYPCHK_DEP_MISMATCH', NO_TRIGGER_MISMATCH as 'NO_TRIGGER_MISMATCH', FLASHBACK_CURSOR as 'FLASHBACK_CURSOR', ANYDATA_TRANSFORMATION as 'ANYDATA_TRANSFORMATION', PDDL_ENV_MISMATCH as 'PDDL_ENV_MISMATCH', TOP_LEVEL_RPI_CURSOR as 'TOP_LEVEL_RPI_CURSOR', DIFFERENT_LONG_LENGTH as 'DIFFERENT_LONG_LENGTH', LOGICAL_STANDBY_APPLY as 'LOGICAL_STANDBY_APPLY', DIFF_CALL_DURN as 'DIFF_CALL_DURN', BIND_UACS_DIFF as 'BIND_UACS_DIFF', PLSQL_CMP_SWITCHS_DIFF as 'PLSQL_CMP_SWITCHS_DIFF', CURSOR_PARTS_MISMATCH as 'CURSOR_PARTS_MISMATCH', STB_OBJECT_MISMATCH as 'STB_OBJECT_MISMATCH', CROSSEDITION_TRIGGER_MISMATCH as 'CROSSEDITION_TRIGGER_MISMATCH', PQ_SLAVE_MISMATCH as 'PQ_SLAVE_MISMATCH', TOP_LEVEL_DDL_MISMATCH as 'TOP_LEVEL_DDL_MISMATCH', MULTI_PX_MISMATCH as 'MULTI_PX_MISMATCH', BIND_PEEKED_PQ_MISMATCH as 'BIND_PEEKED_PQ_MISMATCH', MV_REWRITE_MISMATCH as 'MV_REWRITE_MISMATCH', ROLL_INVALID_MISMATCH as 'ROLL_INVALID_MISMATCH', OPTIMIZER_MODE_MISMATCH as 'OPTIMIZER_MODE_MISMATCH', PX_MISMATCH as 'PX_MISMATCH', MV_STALEOBJ_MISMATCH as 'MV_STALEOBJ_MISMATCH', FLASHBACK_TABLE_MISMATCH as 'FLASHBACK_TABLE_MISMATCH', LITREP_COMP_MISMATCH as 'LITREP_COMP_MISMATCH', PLSQL_DEBUG as 'PLSQL_DEBUG', LOAD_OPTIMIZER_STATS as 'LOAD_OPTIMIZER_STATS', ACL_MISMATCH as 'ACL_MISMATCH', FLASHBACK_ARCHIVE_MISMATCH as 'FLASHBACK_ARCHIVE_MISMATCH', LOCK_USER_SCHEMA_FAILED as 'LOCK_USER_SCHEMA_FAILED', REMOTE_MAPPING_MISMATCH as 'REMOTE_MAPPING_MISMATCH', LOAD_RUNTIME_HEAP_FAILED as 'LOAD_RUNTIME_HEAP_FAILED', HASH_MATCH_FAILED as 'HASH_MATCH_FAILED', PURGED_CURSOR as 'PURGED_CURSOR', BIND_LENGTH_UPGRADEABLE as 'BIND_LENGTH_UPGRADEABLE', USE_FEEDBACK_STATS as 'USE_FEEDBACK_STATS' )) where nonshared_value = 'Y' group by sql_id, nonshared_reason ) where sql_id = '&sqlid' ;