在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条件列中,也就不会被收集直方图。