浅谈cursor_sharing取值对SQL共享的影响(上)

声明:本篇实验思路感谢dbsnake老师指点。

 

Oracle中,用户应用输入的SQL语句要进行所谓的Parse解析过程,用于生成执行计划,这也就是Query Optimizer的主要工作。在Parse中,有两种具体类型,被称为“hard parse”(硬解析)和“Soft parse”(软解析)。

 

“实现执行计划shared cursor共享,减少硬解析”是我们OLTP系统优化一个重要方向。但是,让Oracle真正实现SQL共享不是一件容易的事情,受到很多其他因素的影响。最常用的方式是使用绑定变量,让SQL字面值保持一致。如果应用端没有使用绑定变量,一种做法是设置系统参数cursor_sharing,将SQL语句中的条件进行绑定变量替换。本篇将从cursor_sharing可选值含义入手,讨论分析几种取值的确切含义和应用场景。以及为什么很多资料中都是对cursor_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里,默认cursor_sharing取值为EXACT,表示不开启SQL字面取值绑定变量替换功能。

 

 

SQL> show parameter cursor_sharing;

NAME                                 TYPE        VALUE

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

cursor_sharing                       string      EXACT

 

SQL> select name, value from v$parameter where name='cursor_sharing';

NAME                 VALUE

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

cursor_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;

ID1          COUNT(*)

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

P                8000

D               10000

A                  10

G                   5

 

 

2、  统计量收集

 

这里单独谈谈统计量收集的问题。从上面实验数据的情况看,数据表Tid1列是一个数据极度偏移的数据列。在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

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

ID1                        4           1 NONE

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

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

ID1                        4           4 FREQUENCY

ID2                        4           1 NONE

ID3                        4           1 NONE

 

 

当我们使用过一次id1条件之后,再次手机统计量,使用默认的auto参数,就生成id1列的频度直方图。

 

这里也就揭示了Oracle在收集统计量直方图auto选项的含义。当我们指定auto之后,Oracle会自动判断是否对数据列生成直方图、生成直方图bullet的个数。如果这个列从来就没有出现在SQL条件列中,也就不会被收集直方图。

 

 

3EXACT——不进行条件列替换

 

 

EXACTcursor_sharing参数的默认选项,表示含义是不进行SQL条件自动绑定变量替换。

 

 

SQL> select name, value from v$parameter where name='cursor_sharing';

NAME                 VALUE

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

cursor_sharing       EXACT

 

SQL> alter system flush shared_pool;

System altered

 

 

我们发出两句SQL,分别使用数据取值差异很大的id1值。

 

 

SQL> select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='P';

 

  COUNT(*)

----------

      8000

 

SQL> select /*+ cursor_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 /*+ cursor_sharing_exact_demo */%';

 

SQL_TEXT                                                              SQL_ID        VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='P' 6trn7v99dngaj             1          1

select /*+ cursor_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_cursor('6trn7v99dngaj',0,'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  6trn7v99dngaj, child number 0

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

select /*+ cursor_sharing_exact_demo */ count(*) from t where 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("ID1"='P')

Column Projection Information (identified by operation id):

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

   1 - (#keys=0) COUNT(*)[22]

 

43 rows selected

 

SQL> select * from table(dbms_xplan.display_cursor('dpcnym3gs7psp',0,'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  dpcnym3gs7psp, child number 0

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

select /*+ cursor_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_ID1 |    10 |    20 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID1"='A')

 

PLAN_TABLE_OUTPUT

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

   1 - (#keys=0) COUNT(*)[22]

 

43 rows selected

 

 

由于数据偏移的原因,借助直方图,Oracle对两个SQL生成的执行计划还存在差异。换句话说,在cursor_sharingEXACT的情况下,只要SQL字面值存在差异,就不会进行任何SQL shared cursor

 

本篇中我们主要介绍了cursor_sharing环境和EXACT取值含义,下篇我们集中介绍cursor_sharing参数的另两个取值:FORCESIMILAR含义。

阅读更多
个人分类: ORACLE工作机制
上一篇浅谈optimizer_mode优化器模式
下一篇浅谈cursor_sharing取值对SQL共享的影响(下)
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭