浅谈cursor_sharing取值对SQL共享的影响（上）

在Oracle中，用户应用输入的SQL语句要进行所谓的Parse解析过程，用于生成执行计划，这也就是Query Optimizer的主要工作。在Parse中，有两种具体类型，被称为“hard parse”（硬解析）和“Soft parse”（软解析）。　　“实现执行计划shared cur***共享，减少硬解析”是我们OLTP系统优化一个重要方向。但是，让Oracle真正实现SQL共享不是一件容易的事情，受到很多其他因素的影响。*常用的方式是使用绑定变量，让SQL字面值保持一致。如果应用端没有使用绑定变量，一种做法是设置系统参数cur***_sharing，将SQL语句中的条件进行绑定变量替换。本篇将从cur***_sharing可选值含义入手，讨论分析几种取值的确切含义和应用场景。以及为什么很多资料中都是对cur***_sharing设置望而却步。　　1、 环境准备　　我们在Oracle 10g下准备一个相对偏值的数据表。　　SQL> select * from v$version;　　BANNER　　----------------------------------------------------------------　　Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod　　PL/SQL Release 10.2.0.1.0 - Production　　CORE      10.2.0.1.0      Production　　TNS for 32-bit Windows: Version 10.2.0.1.0 - Production　　NLSRTL Version 10.2.0.1.0 - Production　　在Oracle 10g里，默认cur***_sharing取值为EXACT，表示不开启SQL字面取值绑定变量替换功能。　　SQL> show parameter cur***_sharing;　　NAME                                TYPE       VALUE　　------------------------------------ ----------- ------------------------------　　cur***_sharing                      string     EXACT　　SQL> select name, value from v$parameter where name='cur***_sharing';　　NAME                VALUE　　-------------------- --------------------------------------------------------------------------------　　cur***_sharing      EXACT　　使用脚本生成数据表数据。　　SQL> create table t (id1 varchar2(10), id2 varchar2(10), id3 varchar2(10));　　Table created　　SQL> create index idx_t_id1 on t(id1);　　Index created　　SQL> select object_id from dba_objects where wner='SYS' and object_name='T';　　OBJECT_ID　　----------　　54307　　SQL> select id1, count(*) from t group by id1;　　I**         COUNT(*)　　---------- ----------　　P               8000　　D              10000　　A                 10　　G                  5　　2、 统计量收集　　这里单*谈谈统计量收集的问题。从上面实验数据的情况看，数据表T的id1列是一个数据极度偏移的数据列。在Oracle统计量中，通常选择直方图histogram进行偏度描述。　　注意：在Oracle 9i中，直方图默认使用dbms_stats是不会收集的，需要手工的制定method_opts参数。在Oracle 10g之后，使用“column auto”作为method_opts参数的默认取值。　　SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');　　PL/SQL procedure successfully completed　　SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';　　COLUMN_NAME    NUM_DISTINCT NUM_BUCKETS HISTOGRAM　　--------------- ------------ ----------- ---------------　　I**                       4          1NONE　　ID2                       4          1 NONE　　ID3                       4          1 NONE　　注意，默认是没有生成直方图的。主要原因在于需要使用一次id1作为条件列。　　//使用一次条件列；　　SQL> select count(*) from t where id1='D';　　COUNT(*)　　----------　　10000　　//重新收集一下统计量；　　SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');　　PL/SQL procedure successfully completed　　//发现统计量收集　　SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';　　COLUMN_NAME    NUM_DISTINCT NUM_BUCKETS HISTOGRAM　　--------------- ------------ ----------- ---------------　　I**                       4          4FREQUENCY　　ID2                       4          1 NONE　　ID3                       4          1 NONE　　当我们使用过一次id1条件之后，再次手机统计量，使用默认的auto参数，就生成id1列的频度直方图。　　这里也就揭示了Oracle在收集统计量直方图auto选项的含义。当我们指定auto之后，Oracle会自动判断是否对数据列生成直方图、生成直方图bullet的个数。如果这个列从来就没有出现在SQL条件列中，也就不会被收集直方图。　　3、EXACT——不进行条件列替换　　EXACT是cur***_sharing参数的默认选项，表示含义是不进行SQL条件自动绑定变量替换。　　SQL> select name, value from v$parameter where name='cur***_sharing';　　NAME                VALUE　　-------------------- --------------------　　cur***_sharing      EXACT　　SQL> alter system flush shared_pool;　　System altered　　我们发出两句SQL，分别使用数据取值差异很大的id1值。　　SQL> select /*+ cur***_sharing_exact_demo */ count(*) from t where id1='P';　　COUNT(*)　　----------　　8000　　SQL> select /*+ cur***_sharing_exact_demo */ count(*) from t where id1='A';　　COUNT(*)　　----------　　10　　此时，父子游标library cache中情况如下：　　SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ cur***_sharing_exact_demo */%';　　SQL_TEXT                                                             SQL_ID       VERSION_COUNT EXECUTIONS　　---------------------------------------------------------------------- ------------- ------------- ----------　　select /*+ cur***_sharing_exact_demo */ count(*) from t where id1='P' 6trn7v99dngaj            1         1　　select /*+ cur***_sharing_exact_demo */ count(*) from t where id1='A' dpcnym3gs7psp            1         1　　在EXACT下，不会发生SQL字面值改写的情况。如果两个SQL的其他部分相同，只是where条件的取值有差异，Oracle是会将这两个语句作为两个单*SQL进行硬解析，分别生成执行计划。下面尝试将两个执行计划抽取出。　　SQL> select * from table(dbms_xplan.display_cur***('6trn7v99dngaj',0,'advanced'));　　PLAN_TABLE_OUTPUT　　--------------------------------------------------------------------------------　　SQL_ID 6trn7v99dngaj, child number 0　　-------------------------------------　　select /*+ cur***_sharing_exact_demo */ count(*) from twhere id1='P'　　Plan hash value: 2966233522　　---------------------------------------------------------------------------　　| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |　　---------------------------------------------------------------------------　　|  0 | select STATEMENT  |     |      |      |    9 (100)|         |　　|  1 | SORT AGGREGATE   |     |    1 |    2 |           |         |　　|* 2 |  TABLE ACCESS FULL| T   | 8000 | 16000 |    9 (12)| 00:00:01 |　　---------------------------------------------------------------------------　　Predicate Information (identified by operation id):　　---------------------------------------------------　　2 - filter("I**"='P')　　Column Projection Information (identified by operation id):　　-----------------------------------------------------------　　1 - (#keys=0) COUNT(*)[22]　　43 rows selected　　SQL> select * from table(dbms_xplan.display_cur***('dpcnym3gs7psp',0,'advanced'));　　PLAN_TABLE_OUTPUT　　--------------------------------------------------------------------------------　　SQL_ID dpcnym3gs7psp, child number 0　　-------------------------------------　　select /*+ cur***_sharing_exact_demo */ count(*) from t where id1='A'　　Plan hash value: 555228874　　-------------------------------------------------------------------------------　　| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time    |　　-------------------------------------------------------------------------------　　|  0 | select STATEMENT |          |      |      |    1 (100)|         |　　|  1 | SORT AGGREGATE  |          |    1 |    2 |           |         |　　|* 2 |  INDEX RANGE SCAN| IDX_T_I** |   10 |   20 |    1  (0)| 00:00:01 |　　-------------------------------------------------------------------------------　　Predicate Information (identified by operation id):　　---------------------------------------------------　　2 - access("I**"='A')　　PLAN_TABLE_OUTPUT　　--------------------------------------------------------------------------------　　1 - (#keys=0) COUNT(*)[22]　　43 rows selected　　由于数据偏移的原因，借助直方图，Oracle对两个SQL生成的执行计划还存在差异。换句话说，在cur***_sharing为EXACT的情况下，只要SQL字面值存在差异，就不会进行任何SQL shared cur***。　 浅谈cur***_sharing取值对SQL共享的影响（下）