Oracle参数之cursor_sharing
一、Cursor_sharing简介:
这个参数是用来告诉Oracle在什么情况下可以共享游标,即SQL重用。
Cursor_sharing参数有3个值可以设置:
1)、EXACT:通常来说,exact值是Oracle推荐的,也是默认的,它要求SQL语句在完全相同时才会重用,否则会被重新执行硬解析操作。
2)、SIMILAR:similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时,才会被重新分析,否则将重用SQL。
3)、FORCE:force是在任何情况下,无条件重用SQL。
4)、在Oracle12c版本以后,不建议设置成SIMILAR。手册已经把该参数废弃。
备注:上面所说的SQL重用,仅仅是指谓词条件不同的SQL语句,实际上这样的SQL基本上都在执行同样的业务操作。
二、在Cursor_sharing参数值不同的时对SQL的影响:
2.1 创建实验环境:
11G:创建基表,录入数据。
SYS@orcl> create table test (id number,name varchar2(10));
SYS@orcl> insert into test values (1,'aa');
SYS@orcl> insert into test values (2,'bb');
SYS@orcl> insert into test values (3,'cc');
SYS@orcl> commit;
10年积累的成都网站设计、成都网站建设经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先网站制作后付款的网站建设流程,更有永济免费网站建设让你可以放心的选择与我们合作。
创建三张实验用表:
SYS@orcl> create table test_exact as select from test;
SYS@orcl> create table test_similar as select from test;
SYS@orcl> create table test_force as select * from test;
设置跟踪trace
SYS@orcl> oradebug setmypid
SYS@orcl> oradebug tracefile_name
测试exact:
SYS@orcl> alter session set cursor_sharing=exact;
SYS@orcl> alter session set sql_trace=true;
SYS@orcl> select * from test_exact where id=1;
ID NAME
1 aa
SYS@orcl> select * from test_exact where id=2;
ID NAME
2 bb
SYS@orcl> select * from test_exact where id=3;
ID NAME
3 cc
SYS@orcl> alter session set sql_trace=false;
观察trace文件:
SYS@orcl> select sql_text from v$sql where sql_text like 'select * from test_ex%';
SQL_TEXT
select from test_exact where id=2
select from test_exact where id=3
select * from test_exact where id=1
[oracle@orcl ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2849.trc out.txt aggregate=no
SQL ID: 22cdhbrvt2nmw
Plan Hash: 3210958934
select *
from
test_exact where id=1
call count cpu elapsed disk query current rows
Parse 1 0.00 0.01 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
total 4 0.00 0.01 2 6 0 1
Misses in library cache during parse: 1 --硬解析1次
Optimizer mode: ALL_ROWS
Parsing user id: SYS
SQL ID: f9kq2n9utcww7
Plan Hash: 3210958934
select *
from
test_exact where id=2
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
total 4 0.00 0.00 0 5 0 1
Misses in library cache during parse: 1 --硬解析1次
Optimizer mode: ALL_ROWS
Parsing user id: SYS
SQL ID: 22cdhbrvt2nmw
Plan Hash: 3210958934
select *
from
test_exact where id=1
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 0 --软解析
Optimizer mode: ALL_ROWS
Parsing user id: SYS
总结:当cursor_sharing=exact时,只有当SQL语句是完全一样的情况下才能被重用。
测试SIMILAR:
SYS@orcl> oradebug setmypid
SYS@orcl> oradebug tracefile_name
SYS@orcl> alter session set cursor_sharing=similar;
SYS@orcl> alter session set sql_trace=true;
SYS@orcl> select * from test_similar where id=1;
ID NAME
1 aa
SYS@orcl> select * from test_similar where id=2;
ID NAME
2 bb
SYS@orcl> select from test_similar where id=10;
no rows selected
SYS@orcl> select sql_text from v$sql where sql_text like 'select from test_similar where%';
SQL_TEXT
select from test_similar where id=:"SYS_B_0"
select from test_similar where id=:"SYS_B_0"
select * from test_similar where id=:"SYS_B_0"
SYS@orcl> alter session set sql_trace=false;
分析trace文件:
SQL ID: 6wvc0ymwz50uq
Plan Hash: 3269012161
select *
from
test_similar where id=:"SYS_B_0"
call count cpu elapsed disk query current rows
Parse 3 0.00 0.00 1 1 0 0
Execute 3 0.00 0.00 1 3 0 0
Fetch 5 0.00 0.00 0 11 0 2
total 11 0.00 0.00 2 15 0 2
Misses in library cache during parse: 3 --执行三次硬解析
Optimizer mode: ALL_ROWS
Parsing user id: SYS
对于SIMILAR的情况,如果CBO发现被绑定变量的谓词还有其他的执行计划可以选择时,如果谓词条件的值有变化,就将会产生一个新的子游标,而不是重用之前的SQL;如果谓词没有其他的执行计划可选择,则忽略谓词的值,重用之前的SQL。
进一步测试:
SYS@orcl> alter system flush shared_pool;
SYS@orcl> alter system flush buffer_cache;
SYS@orcl> insert into test_similar values (1,'abc');
SYS@orcl> commit;
SYS@orcl> create index ind_test_similar on test_similar(id);
SYS@orcl> exec dbms_stats.gather_table_stats(user,'test_similar',cascade=>true);
SYS@orcl> alter session set cursor_sharing=similar;
SYS@orcl> alter session set sql_trace=true;
SYS@orcl> select * from test_similar where id=1 and name='aa';
ID NAME
1 aa
SYS@orcl> select * from test_similar where id=1 and name='abc';
ID NAME
1 abc
SYS@orcl> alter session set sql_trace=false;
SYS@orcl> select sql_text from v$sql where sql_text like 'select * from test_similar where%';
SQL_TEXT
select * from test_similar where id=:"SYS_B_0" and name=:"SYS_B_1
查看trace:
SQL ID: 23gux1agm4fnt
Plan Hash: 3269012161
select *
from
test_similar where id=:"SYS_B_0" and name=:"SYS_B_1"
call count cpu elapsed disk query current rows
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 9 0 2
total 8 0.00 0.00 0 9 0 2
Misses in library cache during parse: 1 --硬解析1次
Optimizer mode: ALL_ROWS
Parsing user id: SYS
测试FORCE:
SYS@orcl> oradebug setmypid
SYS@orcl> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4104.trc
SYS@orcl> alter session set cursor_sharing=force;
SYS@orcl> alter session set sql_trace=true;
SYS@orcl> select * from test_force where id=1;
ID NAME
1 aa
SYS@orcl> select * from test_force where id=2;
ID NAME
2 bb
SYS@orcl> select * from test_force where id=1;
ID NAME
1 aa
SYS@orcl> alter session set sql_trace=false;
SYS@orcl> select sql_text from v$sql where sql_text like 'select * from test_force where%';
SQL_TEXT
select * from test_force where id=:"SYS_B_0"
查看trace:
SQL ID: 5my70999m011j
Plan Hash: 1419416768
select *
from
test_force where id=:"SYS_B_0"
call count cpu elapsed disk query current rows
Parse 3 0.00 0.00 1 1 0 0
Execute 3 0.00 0.00 1 1 0 0
Fetch 6 0.00 0.00 0 12 0 3
total 12 0.00 0.00 2 14 0 3
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
总结:force是在任何情况下,无条件重用SQL。
当前题目:Oracle参数之cursor_sharing
标题链接:http://myzitong.com/article/jdgdss.html