2.1 创建实验环境：
复制代码
----首先创建一张weisi_t表----
SQL> create table weisi_t (id int,name varchar2(10));
Table created.
---产生一些数据----
SQL> insert into weisi_t values(1,'aa');
1 row created.
SQL> insert into weisi_t values(2,'bb');
1 row created.
SQL> insert into weisi_t values(3,'cc');
1 row created.
SQL> insert into weisi_t values(4,'dd');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from weisi_t;
ID NAME
---------- ----------
1 aa
2 bb
3 cc
4 dd
----创建下面实验将要用到的三张表----
SQL> create table weisi_t_exact as select * from weisi_t;
Table created.
SQL> create table weisi_t_similar as select * from weisi_t;
Table created.
SQL> create table weisi_t_force as select * from weisi_t;
Table created.
---查看该session的trace文件的路径----
SQL>select * from v$diag_info where name='Default Trace File';
/opt/oracle/diag/rdbms/weisidb/WEISIDB/trace/WEISIDB_ora_4952.trc
2.2 cursor_sharing=exact的情况：
复制代码
----将cursor_sharing设置为exact----
SQL> alter session set cursor_sharing=exact;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from weisi_t_exact where id=1;
ID NAME
---------- ----------
1 aa
SQL> select * from weisi_t_exact where id=3;
ID NAME
---------- ----------
3 cc
SQL> select * from weisi_t_exact where id=1;
ID NAME
---------- ----------
1 aa
SQL> alter session set sql_trace=false;
Session altered.
----从下面的查询可以看出执行了两次硬解析----
SQL> select sql_text from v$sql where sql_text like 'select * from weisi_t_exact where%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from weisi_t_exact where id=1
select * from weisi_t_exact where id=3
---查看trace文件，通过tkprof工具
[oracle@yft ~]$ tkprof /opt/oracle/diag/rdbms/weisidb/WEISIDB/trace/WEISIDB_ora_4952.trc out.txt aggregate=no sys=no----
SQL ID: fnggytkynxz04
Plan Hash: 4127630146
select *
from
weisi_t_exact where id=1
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 ---id=1，执行一次硬解析
Optimizer mode: ALL_ROWS
Parsing user id: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL weisi_t_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
********************************************************************************
SQL ID: 1n0paamkf7sup
Plan Hash: 4127630146
select *
from
weisi_t_exact where id=3
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 ----id=3，执行一次硬解析
Optimizer mode: ALL_ROWS
Parsing user id: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL weisi_t_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
********************************************************************************
SQL ID: fnggytkynxz04
Plan Hash: 4127630146
select *
from
weisi_t_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: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL weisi_t_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
********************************************************************************
总结：当cursor_sharing=exact时，只有当SQL语句是完全一样的情况下才能被重用。
2.3 cursor_sharing=similar的情况：
----将curor_sharing设置为similar----
SQL> alter session set cursor_sharing=similar;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from weisi_t_similar where id=1;
ID NAME
---------- ----------
1 aa
SQL> select * from weisi_t_similar where id=4;
ID NAME
---------- ----------
4 dd
SQL> select * from weisi_t_similar where id=8;
no rows selected
----下面查询中可以看到Oracle将SQL中的谓词条件用同一个名词的一个变量替代，尽管看起来是一样的，但是Oracle依然把它们作为两条SQL来处理----
SQL> select sql_text from v$sql where sql_text like 'select * from weisi_t_similar where%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from weisi_t_similar where id=:"SYS_B_0"
select * from weisi_t_similar where id=:"SYS_B_0"
select * from weisi_t_similar where id=:"SYS_B_0"
SQL> alter session set sql_trace=false;
Session altered.
[oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5329.trc out.txt sys=no
TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 22 10:18:16 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
---从trace文件中可以清楚看到上面的结论----
SQL ID: 80chtmbbwpx49
Plan Hash: 1559066762
select *
from
weisi_t_similar where id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 3 0 0
Fetch 5 0.00 0.00 0 10 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.01 0.01 0 13 0 2
Misses in library cache during parse: 3 ----进行三次查询，都各执行了一次硬解析。
Optimizer mode: ALL_ROWS
Parsing user id: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL weisi_t_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
********************************************************************************
对于SIMILAR的情况，如果CBO发现被绑定变量的谓词还有其他的执行计划可以选择时，如果谓词条件的值有变化，就将会产生一个新的子游标，
而不是重用之前的SQL；如果谓词没有其他的执行计划可选择，则忽略谓词的值，重用之前的SQL。
上面的例子还不能足以说明该情况，接着下面的模拟：
----清楚一下shared_pool中的内容，否则会影响后面的输出----
SQL> alter system flush shared_pool;
System altered.
SQL> select * from weisi_t_similar;
ID NAME
---------- ----------
1 aa
2 bb
3 cc
4 dd
SQL> insert into weisi_t_similar values(1,'gg');
1 row created.
SQL> commit;
Commit complete.
---创建索引，并进行分析----
SQL> create index weisi_t_similar_ind on weisi_t_similar(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'weisi_t_similar',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select * from weisi_t_similar;
ID NAME
---------- ----------
1 aa
2 bb
3 cc
4 dd
1 gg
SQL> alter session set cursor_sharing=similar;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from weisi_t_similar where id=1 and name='aa';
ID NAME
---------- ----------
1 aa
SQL> select * from weisi_t_similar where id=1 and name='gg';
ID NAME
---------- ----------
1 gg
SQL> alter session set sql_trace=false;
Session altered.
----在这里可以看到执行两次SQL查询，只进行了一个硬解析----
SQL> select sql_text from v$sql where sql_text like 'select * from weisi_t_similar where%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from weisi_t_similar where id=:"SYS_B_0" and name=:"SYS_B_1"
---在trace文件也验证了这一点。----
---虽然name的值发生了改变，但是id的值没有发生变，而id上有索引的，name上没有索引，CBO认为这样的情况不会改变SQL的执行计划。
SQL ID: 10ku2kuy1sqaj
Plan Hash: 2730352089
select *
from
weisi_t_similar where id=:"SYS_B_0" and name=:"SYS_B_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: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID weisi_t_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=6 card=1)
2 INDEX RANGE SCAN weisi_t_SIMILAR_IND (cr=2 pr=0 pw=0 time=17 us cost=1 size=0 card=2)(object id 75044)
********************************************************************************
SQL ID: 10ku2kuy1sqaj
Plan Hash: 2730352089
select *
from
weisi_t_similar where id=:"SYS_B_0" and name=:"SYS_B_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: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID weisi_t_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=6 card=1)
2 INDEX RANGE SCAN weisi_t_SIMILAR_IND (cr=2 pr=0 pw=0 time=3 us cost=1 size=0 card=2)(object id 75044)
********************************************************************************
2.4 cursor_sharing=force的情况
----设置cursor_sharing=force----
SQL> alter session set cursor_sharing=force;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from weisi_t_force where id=1;
ID NAME
---------- ----------
1 aa
SQL> select * from weisi_t_force where id=4;
ID NAME
---------- ----------
4 dd
SQL> select * from weisi_t_force where id=1;
ID NAME
---------- ----------
1 aa
SQL> alter session set sql_trace=false;
Session altered.
---从下面的查询中可以看出只进行了一次硬解析，而且使用了绑定变量----
SQL> select sql_text from v$sql where sql_text like 'select * from weisi_t_force where%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from weisi_t_force where id=:"SYS_B_0"
----查看trace文件内容----
[oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5551.trc aggregate=no sys=no
SQL ID: 38vy9d4quwdwk
Plan Hash: 1272021682
select *
from
weisi_t_force where id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 1 0 0
Execute 1 0.00 0.00 0 1 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 6 0 1
Misses in library cache during parse: 1 ----id=1的时候进行一次硬解析
Optimizer mode: ALL_ROWS
Parsing user id: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL weisi_t_FORCE (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
********************************************************************************
SQL ID: 38vy9d4quwdwk
Plan Hash: 1272021682
select *
from
weisi_t_force where id=:"SYS_B_0"
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 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0 ----id=4的时候进行0次硬解析，一次软解析
Optimizer mode: ALL_ROWS
Parsing user id: 105
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL weisi_t_FORCE (cr=3 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
********************************************************************************
SQL ID: 38vy9d4quwdwk
Plan Hash: 1272021682
select *
from
weisi_t_force where id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
1 Execute 1 0.00 0.00 0 0 0 0
1 Fetch 2 0.00 0.00 0 4 0 1
1 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
1 total 4 0.00 0.00 0 4 0 1
1
1 Misses in library cache during parse: 0 ----id=1时进行0次硬解析，一次软解析
1 Optimizer mode: ALL_ROWS
1 Parsing user id: 105
1
1 Rows Row Source Operation
1 ------- ---------------------------------------------------
1 1 TABLE ACCESS FULL weisi_t_FORCE (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1)
1
1 ********************************************************************************
总结：force是在任何情况下，无条件重用SQL。
网友评论