|
在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、 统计量收集 这里单独谈谈统计量收集的问题。从上面实验数据的情况看，数据表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 --------------- ------------ ----------- --------------- ID1 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 --------------- ------------ ----------- --------------- ID1 4 4FREQUENCY ID2 4 1 NONE ID3 4 1 NONE 当我们使用过一次id1条件之后，再次手机统计量，使用默认的auto参数，就生成id1列的频度直方图。 这里也就揭示了Oracle在收集统计量直方图auto选项的含义。当我们指定auto之后，Oracle会自动判断是否对数据列生成直方图、生成直方图bullet的个数。如果这个列从来就没有出现在SQL条件列中，也就不会被收集直方图。 3、EXACT——不进行条件列替换 EXACT是cursor_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 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("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_sharing为EXACT的情况下，只要SQL字面值存在差异，就不会进行任何SQL shared cursor。 本篇中我们主要介绍了cursor_sharing环境和EXACT取值含义，下篇我们集中介绍cursor_sharing参数的另两个取值：FORCE和SIMILAR含义。 (责任编辑：admin) |