oracle实验记录（cursor_sharing(2)SIMILAR）

最新推荐文章于 2023-02-21 14:35:45 发布

cuiye1492

最新推荐文章于 2023-02-21 14:35:45 发布

阅读量93

点赞数

文章标签：数据库数据结构与算法开发工具

SIMILAR 情况
设置为
SQL> alter system set cursor_sharing=SIMILAR;

系统已更改。
SQL> alter system flush shared_pool;
SQL> desc dba_tab_histograms;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
TABLE_NAME                                         VARCHAR2(30)
COLUMN_NAME                                        VARCHAR2(4000)
ENDPOINT_NUMBER                                    NUMBER
ENDPOINT_VALUE                                     NUMBER
ENDPOINT_ACTUAL_VALUE                              VARCHAR2(1000)
SQL> select count(*) from dba_tab_histograms where table_name='T3';

COUNT(*)
----------
2

系统已更改。
SQL> execute dbms_stats.delete_table_stats('XH','T3');

PL/SQL 过程已成功完成。

SQL> select count(*) from dba_tab_histograms where table_name='T3';~~~不存在histogram

COUNT(*)
----------
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                                                   15
session cursor cache hits                                               552
session cursor cache count                                               29
parse count (total)                                                     324
parse count (hard)                                                       69

SQL> select * from t3 where a=12;

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                               552
session cursor cache count                                               29
parse count (total)                                                     329
parse count (hard)                                                       70~~~~~~~~~~

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=13;

A
----------
13

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                               552
session cursor cache count                                               29
parse count (total)                                                     330~~~~~~~~
parse count (hard)                                                       70

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 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 2 2
_B_0"

~~~~~~~~~~~~~~~没histogram时跟force 一样

SQL> alter system flush shared_pool;

系统已更改。

已写入 file afiedt.buf~~~~~~~~~~~~~~~~~~~收集histogram信息

1      begin
2         dbms_stats.gather_table_stats(
3              'XH',
4                 't3',
5               cascade => true,
6               estimate_percent => null,
7          method_opt => 'for all columns size 120');
8* end;
9 /

PL/SQL 过程已成功完成。

SQL> select count(*) from dba_tab_histograms where table_name='T3';

COUNT(*)
----------
100

SQL> select * from t3 where a=1;

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                               465
session cursor cache count                                               29
parse count (total)                                                     163
parse count (hard)                                                       64

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=2;

A
----------
2

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                               465
session cursor cache count                                               29
parse count (total)                                                     164
parse count (hard)                                                       65~~~~~~~~~~~~~~~

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"

select * from t3 where a=:"SYS 1 1 3 1
_B_0"

SQL> select * from t3 where a=3;

A
----------
3

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                               465
session cursor cache count                                               29
parse count (total)                                                     165
parse count (hard)                                                       66~~~~~~~~~~~~~~~~

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"

select * from t3 where a=:"SYS 1 1 3 1
_B_0"

select * from t3 where a=:"SYS 1 1 3 2
_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 3 3 3
_B_0"

SQL> select * from t3 where a=1;

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    0
session cursor cache hits                                               465
session cursor cache count                                               29
parse count (total)                                                     166~~~~
parse count (hard)                                                       66

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"

select * from t3 where a=:"SYS 1 1 3 1
_B_0"

select * from t3 where a=:"SYS 1 1 3 2
_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 3 4 4
_B_0"

分析收集histogram后sql未使用BIND变量，当SQL使用HISTOGRAM的列信息时，oracle认为SQL传递的每个常量都是不可靠的（a=2,a=1,a=3）出现了unsafe bind，会为每个SQL生成

一个子游标
（到最后是不共享执行计划的，只是存同一个BUCKET 挂在同一个 LIBRARY CACHE HANDLE上）造成大量的VERSION_COUNT 会产生cursor:pin s wait on x等待，而且若是

version_count太高 N多子指针都位于同一个BUCKET ，那么搜索bucket 由library cache handle串起来的双向链表时间将会长，长时间持有libarary cache latch，影响性能
，所以存在histogram时跟exact差不多每次都是hard parse，只是共享了SQL语句的存储 BUCKET =>LIBRARY CACHE HANDLE ，没共享执行计划

来自based cost oracle

Two workarounds appeared in 9i to deal with the traps introduced by cursor_sharing=force.
The easy workaround is the hint /*+ cursor_sharing_exact */, which can be added to a statement
to tell Oracle that the statement should not have its literal constants replaced by bind
variables.
The more subtle and dangerous workaround is to use the option cursor_sharing=similar.
With this value for cursor_sharing, Oracle will first replace literal constants with bind variables,
and then decide to peek at the bind variables so that it can optimize for the incoming values on
every single parse call for the statement if it seems to be a good idea.
The comments about this feature in the 9.2 manuals say that Oracle will reoptimize if the
values of the variables would make a difference to the execution plan. It seems that two things
will trigger this reoptimization: first, if any of the predicates involves a range scan, and second,
even on a simple equality, if there is histogram on a column that appears in a predicate, the
query will be reoptimized. (See script. similar.sql in the online code suite for an example
showing this.)
When this happens, the resources needed for optimization increase, as does the contention,
because Oracle rewrites the query with bind variables, decides it should not be sharable,
and inserts it into the library cache as a new child cursor in v$sql (where lots of copies of the
same, substituted text will presumably be accumulating under the same latch).
The moral of this story is that if you really think you have to set cursor_sharing=similar,
make sure you don’t create more histograms than you absolutely need to, or you may introduce
more performance problems than you solve. (In fact, you should always avoid creating histograms
that you don’t really need—it’s just that this setting for cursor_sharing really exacerbates
the problem.)

上面基本意思就是oracle将字面值(a=1)替换为bind变量,然后PEEK 该binds,这样时如果有必要可以对该SQL语句在每次调用时对输入的值进行优化.

上面示例执行计划都是一样的都是FTS且都是字面=,下面看看执行计划不一样的且是范围的,一个FTS,一个INDEX RANGE SCAN
,看下SIMILAR的特点
SQL> alter system flush shared_pool;

系统已更改。

SQL> conn xh/a123
已连接。
SQL> show user;
USER 为 "XH"
SQL> select distinct sid from v$mystat;

SID
----------
140

SQL>

SQL> show parameter cursor_sharing;

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing string
SIMILAR

SQL> select count(*) from dba_tab_histograms where table_name='T4';

COUNT(*)
----------
242

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 33CB80AC
_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                                              2331
session cursor cache count                                               29
parse count (total)                                                    1384
parse count (hard)                                                      261~~~~~~~~

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 1 1 3 0
_B_0"

select * from t4 where a>:"SYS 1 1 3 1
_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 2 1 1 33CB80AC
_B_0"

select * from t4 where a>:"SYS 2 1 1 33CB80AC
_B_0"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2331
session cursor cache count                                               29
parse count (total)                                                    1385
parse count (hard)                                                      262~~~~~~~~~~~~~

SQL> col options format a10
SQL> select operation,options,child_number from v$sql_plan where hash_value='314176
702';

OPERATION            OPTIONS    CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT                           1
TABLE ACCESS         BY INDEX R            1
                     OWID

INDEX                RANGE SCAN            1
SELECT STATEMENT                           0
TABLE ACCESS         FULL                  0
SQL>

SQL> select * from t4 where a>9998;

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

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"

select * from t4 where a>:"SYS 1 1 3 1
_B_0"

select * from t4 where a>:"SYS 1 1 3 2~~~
_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 3 1 1 33CB80AC
_B_0"

select * from t4 where a>:"SYS 3 2 2 33CB80AC
_B_0"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2331
session cursor cache count                                               29
parse count (total)                                                    1386
parse count (hard)                                                      263~~

SQL> select operation,options,child_number from v$sql_plan where hash_value='314176
702';

OPERATION            OPTIONS    CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT                           2
TABLE ACCESS         BY INDEX R            2
                     OWID

INDEX                RANGE SCAN            2
SELECT STATEMENT                           1
TABLE ACCESS         BY INDEX R            1
                     OWID

INDEX RANGE SCAN 1
SELECT STATEMENT 0

OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
TABLE ACCESS FULL 0

已选择8行。

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"

select * from t4 where a>:"SYS 2 2 3 1
_B_0"

select * from t4 where a>:"SYS 1 1 3 2
_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 3 1 1 33CB80AC~~~~~~~走FTS PLAN 执行1次,解析1次,是一个子游标
_B_0"

select * from t4 where a>:"SYS 3 3 3 33CB80AC~~~有index scan range plan 执行3次,解析3次(一次soft parse),2个子游标
_B_0"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2331
session cursor cache count                                               29
parse count (total)                                                    1387~~~~~~~一次soft parse
parse count (hard)                                                      263~~~~~~

SQL> select operation,options,child_number from v$sql_plan where hash_value='314176
702';

INDEX                RANGE SCAN            2
SELECT STATEMENT                           1
TABLE ACCESS         BY INDEX R            1
                     OWID

INDEX RANGE SCAN 1
SELECT STATEMENT 0

OPERATION OPTIONS CHILD_NUMBER
-------------------- ---------- ------------
TABLE ACCESS FULL 0

已选择8行。

可以看到 v$sqlarea有点不同,每个执行计划对应了一个sql_text, 而version_count ,就是表示sql_text:select * from t4 where a>:"SYS _B_0" 这个hash value 对应的

bucket,library cache handle下有 3个子游标(是总和).可以看到这造成了大量 HARD PARSE 虽然 a>1与a>2 执行计划都一样都是FTS ,与exact情况一样~~
从上面可以看出oracle窥探每个bind实际值,产生合理的执行计划(A>1 FTS,A>9999时窥视变为 INDEX RANGE SCAN,从这点可以看出SIMILAR是每次执行调用时都窥视一次,而不是象

普通BINDS 只窥视第一次的后面都共享前面窥视后的执行计划),不过也是认为每一个值都是不可靠的unsafe bind,造成version_count过多,hard parse过多

看下正常使用bind时候的PEEK

SQL> alter system flush shared_pool;

系统已更改。
SQL> variable b number
SQL> exec :b:=1

PL/SQL 过程已成功完成。

SQL> select * from t4 where a>:b;

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

SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:b 1 1 1 0

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

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:b 1 1 1 33CB981C

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2376
session cursor cache count                                               29
parse count (total)                                                    1436
parse count (hard)                                                      270

SQL> select operation,options,child_number from v$sql_plan where hash_value=1705074
740;

OPERATION            OPTIONS    CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT                           0
TABLE ACCESS         FULL                  0

SQL> exec :b:=9999

PL/SQL 过程已成功完成。

SQL> select * from t4 where a>:b;

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

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

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1705074740 0

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

SQL_TEXT PARSE_CALLS EXECUTIONS CHILD_LATCH CHILD_NUMBER
------------------------------ ----------- ---------- ----------- ------------
select * from t4 where a>:b 2 2 1 0

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

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from t4 where a>:b 1 2 2 33CB981C

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                    8
session cursor cache hits                                              2376
session cursor cache count                                               29
parse count (total)                                                    1438
parse count (hard)                                                      271

SQL> select operation,options,child_number from v$sql_plan where hash_value=1705074
740;

OPERATION            OPTIONS    CHILD_NUMBER
-------------------- ---------- ------------
SELECT STATEMENT                           0
TABLE ACCESS         FULL                  0

可以看到还是共享了子游标 (只有一个version_count)
这就是peek,第一次使用时将窥视bind值,产生执行计划(例中为FTS计划),然后后面其他BIND值都将共享这个执行计划(例中为FTS计划),而没按实际情况去分析,比如a>9999 应该

走INDEX RANG SCAN

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

转载于:http://blog.itpub.net/12020513/viewspace-616541/

cuiye1492

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle实验记录（cursor_sharing(2)SIMILAR）

SIMILAR 情况设置为SQL> alter system set cursor_sharing=SIMILAR;系统已更改。SQL> alter system flush shared_pool;SQL&gt...
复制链接

扫一扫