首页 » ORACLE 9i-23ai » QUERIES NOT SHARED WHEN CURSOR_SHARING=FORCE (并未共享游标)

QUERIES NOT SHARED WHEN CURSOR_SHARING=FORCE (并未共享游标)

  • 2012/02/23
  • ORACLE 9i-23ai
  • 714 views
  • QUERIES NOT SHARED WHEN CURSOR_SHARING=FORCE (并未共享游标)已关闭评论

前天有个朋友问我了个问题发现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' ;
打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：