oracle中的cursor_sharing trace

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。