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

转载 2013年11月28日 15:03:27

声明:本篇实验思路感谢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含义。

cursor_sharing的妙用

cursor_sharing的妙用Oracle2009-12-28 10:17:16阅读100评论0  字号:大中小 订阅OLTP系统中,我们总是希望使用绑定变量将sql语句共享在library ca...

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

上篇我们介绍了Oracle SQL共享游标的机制,以及cursor_sharing参数EXACT取值作用。本篇我们继续介绍cursor_sharing参数的其他两个取值:FORCE和SIMILAR的实...

游标共享之cursor_sharing=EXACT

cursor sharing用来告诉oracle什么情况下可以共享游标,即SQL重用。oracle默认cursor_sharing 是exact  指的是SQL语句必须绝对一样的情况下才能共享游标,否...

OCI接口学习--申请句柄(三)

句柄层次: 1、常用句柄类型: OCI环境句柄:       OCI_HTYPE_ENV—它定义所有OCI函数的环境调用,是其他句柄的父句柄。(由OCIEnvInit或OCIEnvCre...

如何诊断高水位争用(enq: HW - contention)

上文讲到了高水位推进的情况,当并发会话同时进行insert时,极易引起高水位争用enq: HW - contention,那么发生此类争用时,该如何诊断呢? 查看v$session_wait,应该会...

Use cursor_sharing_exact refused to SQL variable binding

In the use cursor_sharing parameters, may cause some SQL children cursor too high, cause competition...

Oracle 11g 针对SQL性能的新特性(一)- Adaptive Cursor Sharing

Oracle 11g对SQL执行计划的生成过程做了很多改变,我们经常看到有客户抱怨,数据库在升级到11g以后,执行计划变得很不稳定,甚至难以预测。实际上,Oracle在最新版本中致力于让优化器变得更加...

oracle11g中SQL优化(SQL TUNING)新特性之Adaptive Cursor Sharing (ACS)

oracle11g中最重要的特点之一——Adaptive Cursor Sharing (ACS),几乎完美的解决了之前版本不绑定不行,绑定也不行的尬尴问题,今天有机会对此特点再次进行研究和查阅资料,...
  • LHDZ_BJ
  • LHDZ_BJ
  • 2015年06月30日 14:10
  • 1021

Oracle 11g 新特性 -- 自适应游标共享(Adaptive Cursor Sharing: ACS) 说明

一.自适应游标共享(Adaptive Cursor Sharing) 说明 1.1 ACS概述绑定变量使Oracle DB 可以为多条SQL 语句共享单个游标,以减少分析SQL 语句所使用的共享内存量...

【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能

本文主要介绍Oracle优化器的自适应游标共享(Adaptive Cursor Sharing)功能和处理流程。...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:浅谈cursor_sharing取值对SQL共享的影响(上)
举报原因:
原因补充:

(最多只允许输入30个字)