[20130313]cursor_sharing=force的问题.txt
前一阵子,我把生产系统的cursor_sharing设置为force,
大概按照链接:
http://space.itpub.net/267265/viewspace-754003
我发现一个奇怪的问题,自己在测试环境重复测试看看.
1.建立测试环境:
--执行计划发生了变化,但是依旧没有转化为select * from t where id=:"SYS_B_0"方式.
--这样即使我修改了cursor_sharing=force,原来执行的sql语句依旧不转化,如果我重启数
--据库,这个现象才消失.如果解决,什么原因呢?
前一阵子,我把生产系统的cursor_sharing设置为force,
大概按照链接:
http://space.itpub.net/267265/viewspace-754003
我发现一个奇怪的问题,自己在测试环境重复测试看看.
1.建立测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> create table t as select rownum id,'test' name from dual connect by level
Table created.
SQL> select * from t where id=10;
ID NAME
---------- -----
10 test
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------
SQL_ID 6wxj4tkdajgbx, child number 0
-------------------------------------
select * from t where id=10
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 3 (0)|
--------------------------------------------------------
--sql_id=6wxj4tkdajgbx.
SQL> select sql_text,EXECUTIONS from v$sql where sql_id='6wxj4tkdajgbx';
SQL_TEXT EXECUTIONS
-------------------------------------------------------- ----------
select * from t where id=10 1
2.现在修改回话参数cursor_sharing=force.
SQL> alter session set cursor_sharing=force;
Session altered.
SQL> select * from t where id=10;
ID NAME
---------- -----
10 test
SQL> select sql_text,EXECUTIONS,CHILD_NUMBER from v$sql where sql_id='6wxj4tkdajgbx';
SQL_TEXT EXECUTIONS CHILD_NUMBER
-------------------------------------------------------- ---------- ------------
select * from t where id=10 2 0
--可以发现执行测试变成了2次.难道在使用cursor_sharing=force,sql语句不是变成
--select * from t where id= ":SYS_B_0";吗?
--退出sqlplus,设置参数alter session set cursor_sharing=force;问题依旧!
--换一个参数看看,发现可以使用的.
SQL> select * from t where id=11;
ID NAME
---------- -----
11 test
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1ab9rw9y1r9yt, child number 0
-------------------------------------
select * from t where id=:"SYS_B_0"
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 3 (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :SYS_B_0 (NUMBER): 11
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:SYS_B_0)
3.清空共享池看看.
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_text,EXECUTIONS,CHILD_NUMBER from v$sql where sql_id='6wxj4tkdajgbx';
no rows selected
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- --------
cursor_sharing string FORCE
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
SQL> select * from t where id=10;
ID NAME
---------- -----
10 test
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID 6wxj4tkdajgbx, child number 0
-------------------------------------
select * from t where id=10
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 3 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=10)
SQL> select sql_text,EXECUTIONS,CHILD_NUMBER from v$sql where sql_id='6wxj4tkdajgbx';
SQL_TEXT EXECUTIONS CHILD_NUMBER
-------------------------------------------------------- ---------- ------------
select * from t where id=10 1 0
--可以发现依旧不转化为select * from t where id=:"SYS_B_0"方式执行.为什么呢?
--难道一些东西依旧记忆在某些地方.
SQL> column KGLNAOBJ format a30
SQL> SELECT addr, kglnaobj, indx FROM x$kglob WHERE kglobt03 = '6wxj4tkdajgbx';
ADDR KGLNAOBJ INDX
---------------- ------------------------------ ----------
0000002A972E5298 select * from t where id=10 0
0000002A972FD698 select * from t where id=10 1
SQL> alter system flush shared_pool;
System altered.
SQL> SELECT addr, kglnaobj, indx FROM x$kglob WHERE kglobt03 = '6wxj4tkdajgbx';
no rows selected
--奇怪!无论是分析以及加注解,以及授权都无法改变这种情况.
SQL> exec dbms_stats.gather_table_stats(USER,'T',METHOD_OPT => 'FOR ALL COLUMNS SIZE 1 ',No_Invalidate => FALSE);
PL/SQL procedure successfully completed.
SQL> COMMENT ON TABLE T IS '';
3.建立索引看看:
SQL> create index i_t on t(id);
Index created.
SQL> select * from t where id=10;
ID NAME
---------- -----
10 test
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6wxj4tkdajgbx, child number 0
-------------------------------------
select * from t where id=10
Plan hash value: 2928007915
------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T | 1 | 1 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
--执行计划发生了变化,但是依旧没有转化为select * from t where id=:"SYS_B_0"方式.
--这样即使我修改了cursor_sharing=force,原来执行的sql语句依旧不转化,如果我重启数
--据库,这个现象才消失.如果解决,什么原因呢?
