ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (cursor_sharing(1)exact&force)

oracle实验记录 (cursor_sharing(1)exact&force)

原创 Linux操作系统 作者:fufuh2o 时间:2009-10-14 12:01:48 0 删除 编辑

实验看下cursor_sharing与histogram

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

 

FORCE

Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR

Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the

statement or the degree to which the plan is optimized.

EXACT

Only allows statements with identical text to share the same cursor.

有以上3个值
exact必须精确
force:server 端强制绑定变量
similar:oracle存在histogram时对于不同的变量值重新解析,相当于SIMILAR=EXACT
不存时与force一样

下面实验

首先exact情况


SQL> show user
USER 为 "XH"
SQL> create table t3(a int);

 

SQL> ed
已写入 file afiedt.buf

  1  declare
  2  begin
  3  for i in 1..100 loop
  4  insert into t3 values(i);
  5  end loop;
  6  commit;
  7* end;
SQL> /

PL/SQL 过程已成功完成。

SQL> execute dbms_stats.gather_table_stats('XH','T3');

PL/SQL 过程已成功完成。


SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT


SQL> select distinct sid from v$mystat;

       SID
----------
       136

SQL>

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                                68
session cursor cache count                                               30
parse count (total)                                                     103
parse count (hard)                                                       20

 

SQL> select * from t3 where a=1;

         A
----------
         1

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                                68
session cursor cache count                                               30
parse count (total)                                                     114
parse count (hard)                                                       21

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
     1879812956              0

SQL> col sql_text format a30
SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1879812956;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t3 where a=1                 1          1           1

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=1879812956;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=1               1          1           3            0

SQL>

SQL> select * from t3 where a=2;

         A
----------
         2
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                                68
session cursor cache count                                               30
parse count (total)                                                     115
parse count (hard)                                                       22


SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      640433521              0


SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=640433521;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=2               1          1           3            0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=640433521;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=2               1          1           3            0


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SQL> alter system flush shared_pool;

系统已更改。

SQL>
SQL> alter system set cursor_sharing=force;

 

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                                 0
session cursor cache count                                                5
parse count (total)                                                      19
parse count (hard)                                                        5

 

SQL> select * from t3 where a=10;

         A
----------
        10


SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      917182724              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS           1          1           3            0
_B_0"

 

SQL> select * from t3 where a=11;

         A
----------
        11


SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      917182724              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS           2          2           3            0
_B_0"


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                                 1
session cursor cache count                                               10
parse count (total)                                                      33
parse count (hard)                                                       10

SQL> select * from t3 where a=12;

         A
----------
        12

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=136  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                                 1
session cursor cache count                                               16
parse count (total)                                                      34~~~
parse count (hard)                                                       10

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=136;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      917182724              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=917182724;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t3 where a=:"SYS           3          3           3            0
_B_0"

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=917182724;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t3 where a=:"SYS             1          3           3
_B_0"

 

 

force  与peek
实验表xh.t4  10000 rows , distinct 10000,
当使用a>1时应该  走FTS,A>9999时应该走INDEX RANGE SACN

SQL> select * from t4 where a>1;

执行计划
----------------------------------------------------------
Plan hash value: 2560505625

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 70000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T4   | 10000 | 70000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A">1)

SQL> select * from t4 where a>9999;

执行计划
----------------------------------------------------------
Plan hash value: 4096627024

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        |     1 |     7 |     3   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T4     |     1 |     7 |     3   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T4 |     1 |       |     2   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A">9999)

 

 

 

 

 

SQL> alter system set cursor_sharing=FORCE;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2048
session cursor cache count                                               30
parse count (total)                                                    1181
parse count (hard)                                                      208

 

SQL> select * from t4 where a>9999;

         A          B
---------- ----------
     10000      10001


SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      314176702              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS           1          1           3            0
_B_0"


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS             1          1           1 25568278
_B_0"


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2106
session cursor cache count                                               29
parse count (total)                                                    1198
parse count (hard)                                                      225

SQL>

SQL> select * from t4 where a>9998;~~~还应该走INDEX

         A          B
---------- ----------
      9999      10000
     10000      10001

SQL>
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      314176702              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS           2          2           3            0
_B_0"


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS             1          2           2 25568278
_B_0"


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2106
session cursor cache count                                               29
parse count (total)                                                    1199~~~~~~~~~~~
parse count (hard)                                                      225

SQL> select * from t4 where a>1;

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      314176702              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS           3          3           3            0
_B_0"


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS             1          3           3 25568278~~~没产生子游标
_B_0"


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2106
session cursor cache count                                               30~~~~~~~cursor CACHE HINT
parse count (total)                                                    1200~~~~~~~~还是一次 SOFT PARSE
parse count (hard)                                                      225

 

另一个SESSION 看下执行计划
SQL> set autotrace trace exp
SQL> select * from xh.t4 where a>9999;

执行计划
----------------------------------------------------------
Plan hash value: 4096627024

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        |     1 |     7 |     3   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T4     |     1 |     7 |     3   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T4 |     1 |       |     2   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A">9999)

SQL> select * from xh.t4 where a>1;

执行计划
----------------------------------------------------------
Plan hash value: 2560505625

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 70000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T4   | 10000 | 70000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A">1)

可以看到 一个是  INDEX RANGE SACN,一个是FTS ,既然 A>1时候 计划不一样应该产生新的 子游标 并且HARD PARSE


SQL> select * from t4 where a>1;再执行一次

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      314176702              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS           4          4           3            0
_B_0"


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS             1          4           4 25568278
_B_0"


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2107~~~~~~~~~
session cursor cache count                                               30
parse count (total)                                                    1201
parse count (hard)                                                      225

还是没有 产生新子游标,新hard parse,而是出现一次fast soft parse,完全共享直接使用~~

SQL> set autotrace off
SQL> select operation from v$sql_plan where hash_value='314176702';

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
INDEX
用v$sql_plan看~~~~A>1实际 是用了 INDEX RANGE SCAN 方式,这是由于oracle peeking,oracle 会窥视第一次输入变量的实际值产生PLAN,然后一直用这个执行计划,set

autotrace显示的不对


再证实一下

SQL> alter system flush shared_pool;

系统已更改。

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2163
session cursor cache count                                               30
parse count (total)                                                    1268
parse count (hard)                                                      231

SQL>
SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;

未选定行


SQL> select * from t4 where a>1;


SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      314176702              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS           1          1           3            0
_B_0"


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS             1          1           1 25568278
_B_0"


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2181
session cursor cache count                                               29
parse count (total)                                                    1285
parse count (hard)                                                      232

SQL> select operation from v$sql_plan where hash_value='314176702';

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS
SQL> select * from t4 where a>9999;

         A          B
---------- ----------
     10000      10001


SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
      314176702              0

SQL> select sql_text,PARSE_CALLS,EXECUTIONS,CHILD_LATCH,CHILD_NUMBER from v$sql whe
re hash_value=314176702;

SQL_TEXT                       PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:"SYS           2          2           3            0
_B_0"


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2181
session cursor cache count                                               29
parse count (total)                                                    1286~~~~~~~~一次soft parse
parse count (hard)                                                      232

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS ,address from v$sqlarea
where hash_value=314176702;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:"SYS             1          2           2 25568278
_B_0"

SQL> select operation from v$sql_plan where hash_value='314176702';

OPERATION
------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS~~~~~~~~~~~~~~~~~~~~~~还是FTS,应该走INDEX RANGE SCAN,可以看出是由于oracle peek

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-616512/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426873