cursor_sharing 没有效果？

Yong Huang

原帖由 Toms_zhang 于 2008-8-7 02:16 发表
  1) 使用dbms_stats分析test表，再测试，SQL能够共享
  2) 在表test没有分析的前提下，关闭动态分析，设置optimizer_dynamic_sampling=1
   3) 在表test没有分析的前提下，修改optimier_mode为choose

   以上三种方法均可以让这个案例的中的SQL实现共享。

Did you flush shared_pool before each test? You still used stuff990's PL/SQL block to test. Correct? Let's all focus on testing in a regular user's account, not SYS. My test in 10.2.0.4 always shows 100 distinct SQLs with version_count being 1 for each. I always test after flushing shared pool.

SQL> alter system set cursor_sharing=similar;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> declare
  2  i int :=0;
  3  bb INT :=0;
  4  sqltext varchar2(100);
  5  begin
  6  for i in 1 .. 100
  7  loop
  8          sqltext :='select count(*) from TMP_A where a=' || i ;
  9    execute immediate sqltext INTO bb;
10  end loop;
11  commit;
12  end;
13  /

PL/SQL procedure successfully completed.

SQL> select sql_id, version_count, sql_text from v$sqlarea where lower(sql_text) like 'select count(*)% tmp_a %';

SQL_ID        VERSION_COUNT SQL_TEXT
------------- ------------- --------------------------------------------------------------------------------
8na80y7mpw05d             1 select count(*) from TMP_A where a=95
83758av6p40uv             1 select count(*) from TMP_A where a=9
...

Yong Huang