在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条件列中，也就不会被收集直方图。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-9-2 15:18:43 |
赞(
操作
