柱状图与cursor_sharing设置为similar研究

当cursor_sharing参数为similar来减少硬解析.在这种设置下,如果一个等值查询sql的条件列上存在柱状图,Oracle将认为每次由不同的字面变量产生的cursor都是unsafe的,这样就会产生硬解析且增加version count.

SYS@huiches>select * from v$version;
BANNER
--------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

已用时间:  00: 00: 00.09
SYS@huiches>desc test;
 名称                                                                                                                                               是否为空? 类型
 -------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------------
 ID                                                                                                                                                          NUMBER
 NAME                                                                                                                                                        VARCHAR2(20)

SYS@huiches>select count(*) from test;
  COUNT(*)
----------
  19999900

已用时间:  00: 00: 10.79
SYS@huiches>select sql_text,hash_value from v$sql where sql_text='select count(*) from test';
SQL_TEXT                                                     HASH_VALUE
------------------------------------------------------------ ----------
select count(*) from test                                     297253644

已用时间:  00: 00: 00.03
SYS@huiches>select operation,options,object_name,optimizer from v$sql_plan where hashvalue='297253644';
OPERATION                      OPTIONS                        OBJECT_NAME                    OPTIMIZER
------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT STATEMENT                                                                             ALL_ROWS
SORT                           AGGREGATE
PX COORDINATOR
PX SEND                        QC (RANDOM)                    :TQ10000
SORT                           AGGREGATE
PX BLOCK                       ITERATOR
TABLE ACCESS                   FULL                           TEST

已选择7行。

已用时间:  00: 00: 02.61
SYS@huiches>show parameter cursor_sharing
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
cursor_sharing                       string                 EXACT
SYS@huiches>alter system set cursor_sharing='SIMILAR';

系统已更改。

已用时间:  00: 00: 00.07
SYS@huiches>show parameter cursor_sharing
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
cursor_sharing                       string                 SIMILAR

1.建立一个测试表histestnew,A B 两列都是unique的,区别在于A上有柱状图,B上没有柱状图;
SYS@huiches>create table histestnew as select rownum a, rownum b from dba_objects;

表已创建。

已用时间:  00: 00: 00.25
SYS@huiches>select count(*) from histestnew;
  COUNT(*)
----------
     50526

已用时间:  00: 00: 00.06
SYS@huiches>alter table histestnew add constraint UN1_histestnew unique (A);

表已更改。

已用时间:  00: 00: 00.23
SYS@huiches>alter table histestnew add constraint UN2_histestnew unique (B);

表已更改。

已用时间:  00: 00: 00.07

10g数据库统计怎么有问题呢?
SYS@huiches>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'HISTESTNEW',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.15
SYS@huiches>select column_name,num_distinct,num_buckets from user_tab_col_statistics  where  table_name='HISTESTNEW';
COLUMN_NAME                                                  NUM_DISTINCT NUM_BUCKETS
------------------------------------------------------------ ------------ -----------
A                                                                   50526          75
B                                                                   51362           1

已用时间:  00: 00: 00.04
SYS@huiches>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'HISTESTNEW',cascade=>true,method_opt=>'for columns B size 1');

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.12
SYS@huiches>select column_name,num_distinct,num_buckets from user_tab_col_statistics where table_name = 'HISTESTNEW';
COLUMN_NAME                                                  NUM_DISTINCT NUM_BUCKETS
------------------------------------------------------------ ------------ -----------
A                                                                   50526          75
B                                                                   50903           1

已用时间:  00: 00: 00.01

2.测试cursor_sharing=similar时,A列上不同字面量的等值查询将被oracle转换为相同hash value的sql语句;但是因为柱状图的存在,无法共享child cursor而发生硬解析:
SYS@huiches>show parameter cursor_sharing;
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
cursor_sharing                       string                 SIMILAR
SYS@huiches>alter system flush shared_pool;

系统已更改。

已用时间:  00: 00: 00.23
SYS@huiches>select * from histestnew where a=1;
         A          B
---------- ----------
         1          1

已用时间:  00: 00: 00.03
SYS@huiches>select sql_text,version_count,optimizer_mode,hash_value from v$sqlarea where sql_text like '%select * from histestnew where a=%';
SQL_TEXT                                                     VERSION_COUNT OPTIMIZER_MODE       HASH_VALUE
------------------------------------------------------------ ------------- -------------------- ----------
select * from histestnew where a=:"SYS_B_0"                              1 ALL_ROWS             2855577372

已用时间:  00: 00: 00.03
SYS@huiches>select sql_text,loaded_versions,parse_calls,child_number from v$sql where sql_text like  '%select * from histestnew where a=%';
SQL_TEXT                                                     LOADED_VERSIONS PARSE_CALLS CHILD_NUMBER
------------------------------------------------------------ --------------- ----------- ------------
select * from histestnew where a=:"SYS_B_0"                                1           1            0

已用时间:  00: 00: 00.04
SYS@huiches>select * from v$mystat m,v$statname s where s.statistic#=m.statistic# and s.name='parse count (hard)';
       SID STATISTIC#      VALUE STATISTIC# NAME                                                              CLASS    STAT_ID
---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- ----------
       158        339        528        339 parse count (hard)                                                   64  143509059

已用时间:  00: 00: 00.01
SYS@huiches>select * from histestnew where a=2;     使用2,3,4这三个不同的字面量,做等值查询
         A          B
---------- ----------
         2          2

已用时间:  00: 00: 00.00
SYS@huiches>select * from histestnew where a=3;
         A          B
---------- ----------
         3          3

已用时间:  00: 00: 00.01
SYS@huiches>select * from histestnew where a=4;
         A          B
---------- ----------
         4          4

已用时间:  00: 00: 00.00
SYS@huiches>select sql_text,version_count,optimizer_mode,hash_value from v$sqlarea where sql_text like '%select * from histestnew where a=%';
SQL_TEXT                                                     VERSION_COUNT OPTIMIZER_MODE       HASH_VALUE
------------------------------------------------------------ ------------- -------------------- ----------
select * from histestnew where a=:"SYS_B_0"             4            ALL_ROWS             2855577372

已用时间:  00: 00: 00.03
SYS@huiches>select sql_text,loaded_versions,parse_calls,child_number from v$sql where sql_text like  '%select * from histestnew where a=%';
SQL_TEXT                                                     LOADED_VERSIONS PARSE_CALLS CHILD_NUMBER
------------------------------------------------------------ --------------- ----------- ------------
select * from histestnew where a=:"SYS_B_0"                                1           1            0
select * from histestnew where a=:"SYS_B_0"                                1           1            1
select * from histestnew where a=:"SYS_B_0"                                1           1            2
select * from histestnew where a=:"SYS_B_0"                                1           1            3

已用时间:  00: 00: 00.04

硬解析次数也相应的增加了3(531-528)次:
SYS@huiches>select * from v$mystat m,v$statname s where s.statistic#=m.statistic# and s.name='parse count (hard)';
       SID STATISTIC#      VALUE STATISTIC# NAME                                                              CLASS    STAT_ID
---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- ----------
       158        339        531        339 parse count (hard)                                                   64  143509059

已用时间:  00: 00: 00.01

3.测试使用相同值做等值查询:
SYS@huiches>select * from histestnew where a=2;
         A          B
---------- ----------
         2          2

已用时间:  00: 00: 00.01
SYS@huiches>select * from histestnew where a=2;
         A          B
---------- ----------
         2          2

已用时间:  00: 00: 00.01
SYS@huiches>select * from histestnew where a=2;
         A          B
---------- ----------
         2          2

已用时间:  00: 00: 00.00

此时可以看到硬解析次数未增加,version_count个数也未增加,sql可以共享:
SYS@huiches>select * from v$mystat m,v$statname s where s.statistic#=m.statistic# and s.name='parse count (hard)';
       SID STATISTIC#      VALUE STATISTIC# NAME                                                              CLASS    STAT_ID
---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- ----------
       158        339        531        339 parse count (hard)                                                   64  143509059

已用时间:  00: 00: 00.03
SYS@huiches>select sql_text,version_count,optimizer_mode,hash_value from v$sqlarea where sql_text like '%select * from histestnew where a=%';
SQL_TEXT                                                     VERSION_COUNT OPTIMIZER_MODE       HASH_VALUE
------------------------------------------------------------ ------------- -------------------- ----------
select * from histestnew where a=:"SYS_B_0"                4       ALL_ROWS             2855577372

已用时间:  00: 00: 00.03
SYS@huiches>select sql_text,loaded_versions,parse_calls,child_number from v$sql where sql_text like  '%select * from histestnew where a=%';
SQL_TEXT                                                     LOADED_VERSIONS PARSE_CALLS CHILD_NUMBER
------------------------------------------------------------ --------------- ----------- ------------
select * from histestnew where a=:"SYS_B_0"                                1           4          1
select * from histestnew where a=:"SYS_B_0"                                1           1            2
select * from histestnew where a=:"SYS_B_0"                                1           1            3

已用时间:  00: 00: 00.04

4.现在来看看B列,没有柱状图.

首先执行查询,生成一个共享游标(Cursor):
SYS@huiches>alter system flush shared_pool;

系统已更改。

已用时间:  00: 00: 00.17
SYS@huiches>select * from histestnew where b=1;
         A          B
---------- ----------
         1          1

已用时间:  00: 00: 00.01
  1* select sql_text,version_count,optimizer_mode,hash_value from v$sqlarea where sql_text like '%select * from histestnew where b=%'
SYS@huiches>/
SQL_TEXT                                                     VERSION_COUNT OPTIMIZER_MODE       HASH_VALUE
------------------------------------------------------------ ------------- -------------------- ----------
select * from histestnew where b=:"SYS_B_0"                              1 ALL_ROWS             2699912528

已用时间:  00: 00: 00.04
  1* select sql_text,loaded_versions,parse_calls,child_number from v$sql where sql_text like '%select * from histestnew where b=%'
SYS@huiches>/
SQL_TEXT                                                     LOADED_VERSIONS PARSE_CALLS CHILD_NUMBER
------------------------------------------------------------ --------------- ----------- ------------
select * from histestnew where b=:"SYS_B_0"                                1           1            0

已用时间:  00: 00: 00.03
SYS@huiches>select * from v$mystat m,v$statname s where s.statistic#=m.statistic# and s.name='parse count (hard)';
       SID STATISTIC#      VALUE STATISTIC# NAME                                                              CLASS    STAT_ID
---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- ----------
       158        339        616        339 parse count (hard)                                                   64  143509059

已用时间:  00: 00: 00.03
SYS@huiches>select * from histestnew where b=2;
         A          B
---------- ----------
         2          2

已用时间:  00: 00: 00.01
SYS@huiches>select * from histestnew where b=3;
         A          B
---------- ----------
         3          3

已用时间:  00: 00: 00.01
SYS@huiches>select * from histestnew where b=4;
         A          B
---------- ----------
         4          4

已用时间:  00: 00: 00.00
SYS@huiches>select sql_text,version_count,optimizer_mode,hash_value from v$sqlarea where sql_text like '%select * from histestnew where b=%';
SQL_TEXT                                                     VERSION_COUNT OPTIMIZER_MODE       HASH_VALUE
------------------------------------------------------------ ------------- -------------------- ----------
select * from histestnew where b=:"SYS_B_0"                              1 ALL_ROWS             2699912528

已用时间:  00: 00: 00.03

可以看到这个SQL只有一个版本,解析调用4次,实现了共享:
SYS@huiches>select sql_text,loaded_versions,parse_calls,child_number from v$sql where sql_text like '%select * from histestnew where b=%';
SQL_TEXT                                                     LOADED_VERSIONS PARSE_CALLS CHILD_NUMBER
------------------------------------------------------------ --------------- ----------- ------------
select * from histestnew where b=:"SYS_B_0"                                1           4            0

已用时间:  00: 00: 00.03

SYS@huiches>select * from v$mystat m,v$statname s where s.statistic#=m.statistic# and s.name='parse count (hard)';
       SID STATISTIC#      VALUE STATISTIC# NAME                                                              CLASS    STAT_ID
---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- ----------
       158        339        616        339 parse count (hard)                                                   64  143509059

可见在cursor_sharing=similar的环境中,如果条件列上存在柱状图,不同的字面量等值查询的sql,parent cursor可以共享,但是无法共享child cursor(VERSION_COUNT),因而造成硬解析.每一次sql被执行前都先要到library cache中根据hash value 查找parent cursor,然后查找是否有可以共享的child cursor,如果有就发生软解析,没有就要硬解析.在这个过程中,要使用library cache latch.如果发生硬解析,就要多次获得library cache latch,并且随着child cursor数量的增多,library cache latch 被持有的时间也会变长.由于latch是串行机制,多个进程同时申请获得一个latch就会产生竞争.等待获得latch的过程就是latch free,在等待latch free的时候,进程将持续占有CPU,这可能引起latch free导致CUP使用率过高.