介绍
oracle优化器对一个sql语句检测所有可能的执行计划并选择一个成本值最小的,这里的成本代表了一个特定执行计划的资源使用情况.为了让优化器能准确的判断一个执行计划的成本它必须要关于sql语句要访问的所有对象(表或索引)的信息同时还要有运行sql语句的系统信息.
这些必要的信息通常称为优化器统计信息.理解和管理优化器统计信息是优化sql执行的关键.知道何时以及如何收集统计信息对于维护可以接受的性能来说至关重要.
这里将介绍在常见的oracle数据库场景中何时以及如何来收集统计信息.它包含以下内容:
怎样收集统计信息
何时收集统计信息
提高收集统计信息的效率
何时不收集统计信息
收集处理的统计信息
怎样收集统计信息
在oracle中收集统计信息的首选方法是使用提供了自动统计信息收集job.
自动统计信息收集job
对运行oracle autotask任务的一个预定义维护窗口期间对哪些丢失统计信息或统计信息过期的所有数据库对象收集统计信息,oracle内部很重视数据库对象的统计信息因此这此对象在进行处理前需要更新统计信息.自动统计信息收集job是使用dbms_stats.gather_database_stats_job_proc过程来实现的,它与dbms_stats.gather_*_stats过程使用相同的缺省参数.这个缺省值在大多数情况下是有效的.然而偶尔也需要改变这些统计信息收集参数的缺省值,可以通过dbms_stats.set_*_pref过程来进行修改.例如设置一个表中有5%的数据发生了改变而不是缺省值10%时就认会它的统计信息失效了.如果想要改变这个统计信息失效的阈值,可以使用dbms_stats.set_table_prefs过程来修改stale_percent参数.
sys@JINGYONG> begin
2 dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','5');
3 end;
4 /
PL/SQL 过程已成功完成。
使用dbms_stats.set_table_prefs过程将表统计信息失效的阈stale_percent改变了5%.
手动统计信息收集
如果已经有一个完善的统计信息收集过程或者因为某些原因想要对特定用户方案禁用自动统计信息收集而只保留收集数据字典的统计信息.可以使用dbms_stats.set_global_prefs过程来改变autostats_target参数为oracle来替代auto.
sys@JINGYONG> begin
2 dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
3 end;
4 /
PL/SQL 过程已成功完成。
用上面的代码改变自动统计信息收集job只自动收集数据字典统计信息.
为了手动收集统计信息你应该使用dbms_stats包,用它来替找过时的analyze命令.dbms_stats包提供多个
dbms_stats.gather_*_stats过程来收集用户方案对象,数据字典和固定对象的统计信息.理想情况下应该让这些过程除了用户方案名和对象名参数之外其它的参数值都使用缺省值.缺省参数值和自适应参数设置在大多数情况下已经足够了.
sys@JINGYONG> begin
2 dbms_stats.gather_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 过程已成功完成。
两个修改最频繁的参数是ESTIMATE_PERCENT和METHOD_OPT
ESTIMATE_PERCENT
在收集统计信息的过程最常见的问题是’使用什么样的抽样大小’与这个问题相关的设置是dbms_stats.gather_*_stats过程中的ESTIMATE_PERCENT参数.这个ESTIMATE_PERCENT参数判断用来计算统计信息所使用的行数百份比.当表中的所有行被处理时收集的统计信息是最准确的(比如100%抽样).然而抽样的样本越大收集操作的时间越长.因此使用怎样的抽样大小来提供及时准确的统计信息.
oracle11G之前的ESTIMATE_PERCENT
在oracle10g中,ESTIMATE_PERCENT的缺省值从100%变成了AUTO_SAMPLE_SIZE.这个AUTO_SAMPLE_SIZE的目的是让oracle在每次收集统计信息时来判断每一个表的合适的抽样大小.这将允许oracle自动地对每一个表改变其抽样大小但仍然能确保及时有效的收集统计信息.这种方法对于大多数表来说是一种可取的方法但是对于数据存在倾斜的表来说存在问题.当表中数据出现倾斜AUTO_SAMPLE_SIZE算法通常选择的抽样大小太小,在这种情况下最好的方法还是手动指定ESTIMATE_PERCENT参数的大小.
oracle11g中的ESTIMATE_PERCENT
oracle11g中引入一种新的hash-based抽样算法来提供精确的统计数据解决了精确和速度两个关键问题.它的精确度接近100%抽样大小的水平但是成本与10%抽样大小相当.这种新的算法只有当任何dbms_stats.gather_*_stats过程中的ESTIMATE_PERCENT参数设置为AUTO_SAMPLE_SIZE时才会使用.
下面的表格显示了一个早前使用1%抽样,100%抽样和AUTO_SAMPLE_SIZE抽样收集统计信息的结查.第一行比较运行的时间,后继的行将显示每次运行计算出来的L_ORDERKDY和L_COMMENT两个列不同值的数量(NDV)
-----------------------------------------------------------------------------------------------
1% sample auto_sample_size 100% sample
-----------------------------------------------------------------------------------------------
Elapse time (sec) 797 1908 18772
NDV for L_ORDERKEY Column 225000000 450000000 450000000
NDV for L_COMMENT Column 7244885 177499684 181122127
-----------------------------------------------------------------------------------------------
在这种情况下新的auto_sample_size算法比100%抽样执行的时间要快9倍且只比1%抽样执行的时间慢2.4倍,而提供的统计信息质量几乎接近100%抽样的水平(不足以改变执行计划).
从oracle11g开始强烈建议你使用estimate_percent参数的缺省值.如果你手动设置estimate_percent参数尽管你将它设置为100%它还是会使用旧的收集算法.
method_opt
在dbms_stats.gather_*_stats过程中到目前为止最有争议的参数就是method_opt.这个method_opt参数控制着在收集统计信息过程是否创建直方图.直方图是一种特殊类型的列统计信息提供关于表中列数据分布的详细信息.所以这就是为什么直方图存在争议的问题
直方图的创建会增加统计收集的时间和系统资源的使用但最大的问题是直方图与bind peeking功能的相互作用以及对near popular values基数评估的影响.
直方图与bind peeking
bind peeking对直方图的不利影响已经在oracle11g中通过引入自适应游标共享被淡化了但是直到今天仍然感受到它的影响.为了说明自适应游标共享是怎样解决这个问题的先来检查一个这个问题的原因.
oracle11g之前的直方图与bind peeking
在oracle11g之前当优化一个在where子句中包含绑定变量的语句时优化在第一次执行这个语句时会窥视这些绑定变量的值(在硬解析阶段).优化器然后会基于这个初始化的绑定变量值来判断执行计划.对于后续执行这个查询不会执行绑定变量窥视(没有硬解析发生),所以对于后面的这个语句的所有执行即使绑定变量发生了改变也会决定使用第一次设置的绑定变量值所产生的执行计划对于在表达式中使用绑定变量的列存在直方图这将有助于判断初始化绑定变量值的最优执行计划.因此对于相同的语句依赖于初始化硬解析时绑定变量的值执行计划可能会有所不同.
有两种方法可以避免这个问题:删除直方图并在将业停止收集直方图或禁用bind peeking绑定变量窥视.根据所有的sql是否都在使用绑定变量你可以判断哪一种方法更适合你的数据库.
禁止直方图的创建
如果你的环境中所有sql语句使用了绑定变量那么最好是删除存在的直方图并在将来的收集统计信息过程中禁止创建直方图.禁上直方图的创建能确保依赖于绑定变量的值的执行计划不会发生改变也会减少收集统计信息的时间.没有直方图优化器会假设列中不相同的值是均匀分布在所有行中的并当窥视sql语句中初始化绑定变量值时使用NDV(number distinct values)来判断基数的评估.
可以使用dbms_stats.delete_table_stats过程来删除统计信息中存在的直方图信息.
sys@JINGYONG> begin
2 dbms_stats.delete_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 过程已成功完成。
接下来可以通过使用dbms_stats.set_param过程来改变method_opt参数的缺省值来阻止将来生成直方图.这能确保
dbms_stats.gather_*_stats过程和自动统计信息收集job在将来都不会收集直方图信息.
sys@JINGYONG> begin 2 dbms_stats.set_param(pname=>'METHOD_OPT',pval=>'FOR ALL COLUMNS SIZE 1'); 3 end; 4 / PL/SQL 过程已成功完成。
最后可以使用dbms_stats.gather_table_stats过程来对受影响的对象重新收集统计信息.
注意在oracle11g中通过使用dbms_stats.delete_column_stats和对直方图设置col_stat_type可以删除不想要的直方图而不用删除所有的直方图信息.也可以对单个表或者使用dbms_stats.set_table_prefs过程来对列禁止直方图的创建.你知道直方图也用于某些连接谓词而且删除直方图对连接谓词的基数评估会有影响.在这种情况下更安全的方法是禁用绑定变量窥视.
禁用绑定变量窥视
如果你的环境中有一些sql语句是使用绑定变量而有一些sql语句使用了literal values那么你应该禁用绑定变量窥视.通过禁用绑定变量窥视它将阻止优化器窥初始绑定变量值且将不使用直方图来进行基数评估.相反优化器将列中的不相同值是均匀分布在行中并使用NDV(number distinct values)来进行基数评估操作.这将对于使用绑定变量的语句使用一致的执行计划.但是如果sql语句使用literal values那么仍然能利用直方图来得到最优的执行计划.可以通过设置隐含参数_optim_peek_user_binds为false来禁用绑定变量窥视.
oracle11g中的直方图与绑定变量窥视
在oracle11g中优化器已经增强了允许多个版本的执行计划用于使用绑定变量的单个sql语句.这个功能就叫作自适应游标共享且依赖于对执行统计的监控来确保每一个绑定变量值使用正确的执行路径.
在第一次执行时优化器将窥视绑定变量值且基于绑定变量值的选择性来判断其执行计划,与oracle11g之前的版本一样.如果优化器认为最佳的执行计划可能依赖于绑定变量的值(例如,列上的直方图或者一个范围谓词,or,< ,>)这个游标将会被标记为bind sensitive.当一个游标被标记为bind sensitive.oracle将监控游标使用不同绑定值的行为来确定是否要使用一个不同的执行计划.
如果一个不同的绑定变量值在后继的执行中使用,优化器将使用相同的执行计划因为oracle一开始会假设游标能被共享.然而新的
绑定变量的执行统计会被记录并与之前绑定变量值的执行统计进行比较.如果oracle判断新的绑定变量值造成了操作的数据量明显不同那么对于新的绑定变量值在下一次执行时会进行硬解析且这个游标会被标记为bind-aware.每一个bind_aware游标与绑定变量的选择性范围有关因此游标只有在这个语句的绑定变量值在一个被认为可共享的范围之内才能被共享.
当另一个新的绑定变量值被使用时,优化器将会基于绑定变量值的选择性的相似度来找到一个它认为最好的一个游标.如果它不能找到一个游标,它将创建一个新的.如果执行计划的一个新的游标与一个已经存在的游标一样,那么两个游标将会在共享池中合并从而节省空间.游标的选择性范围为了包含新绑定变量值的选择性将会有所增加.
通过允许对单个sql语句存在多个执行计划,在oracle11g中直方图对于使用绑定变量的语句不再有负面影响.
直方图和near popular values
当优化器遇到一个where子句中谓词列上有直方图,它将基于literal value的出现频率来进行基数评估.例如假设在sh用户下的customers表中的cust_city_di列上有一个高度平衡的直方图且有一个使用cust_city_id=51806的查询.优化器首先会检查这个直方图有51806作为它的end point有多少个桶.在这种情况下,endpint是51806的桶有136,137,138和139(可以查看user_histograms).因为endpoint的值有两个或多个桶要被考虑为出现频繁的优化器将使用下面的公式来进行基数评估:
(Number of bucket endpoints / total number of buckets) * number of rows in the table
在这种情况下:4/254*55500=874
sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=51806;
执行计划
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 874 | 4370 | 382 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_CITY_ID"=51806)
然而如果谓词是cust_city_id=52500,它对于任何桶来说都不是一个endpoint那么优化器会使用一个同的公式来进行基数评估.对于endpoint值只在一个桶出现或者任何桶中都没有这个endpoint时优化器会使用下面的计算公式:
density * number of rows in the table,
density的值可以在user_tab_col_statistics中看到,它的值从oracle10.2.0.4以后优化器将不再使用.记录这个值是为了向后兼容,在oracle9i和oracle10g前期的版本中会使用这个值.此外如果参数optimizer_features_enable设置的版本小于10.2.0.4,那么视图中的density仍然会被使用.
sys@JINGYONG> select column_name,density from dba_tab_col_statistics where owner
='SH' and table_name='CUSTOMERS';
COLUMN_NAME DENSITY
------------------------------ ----------
CUST_ID .000018018
CUST_FIRST_NAME .000769231
CUST_LAST_NAME .001101322
CUST_GENDER .5
CUST_YEAR_OF_BIRTH .013333333
CUST_MARITAL_STATUS .090909091
CUST_STREET_ADDRESS .000019629
CUST_POSTAL_CODE .001605136
CUST_CITY .001612903
CUST_CITY_ID .002179391
CUST_STATE_PROVINCE .006896552
CUST_STATE_PROVINCE_ID .000009009
COUNTRY_ID .000009009
CUST_MAIN_PHONE_NUMBER .000019608
CUST_INCOME_LEVEL .083333333
CUST_CREDIT_LIMIT .125
CUST_EMAIL .000588582
CUST_TOTAL 1
CUST_TOTAL_ID .000009009
CUST_SRC_ID 0
CUST_EFF_FROM 1
CUST_EFF_TO 0
CUST_VALID .5
已选择23行。
sys@JINGYONG> select column_name,num_buckets,histogram from dba_tab_col_statisti
cs where owner='SH' and table_name='CUSTOMERS' and column_name='CUST_CITY_ID';
COLUMN_NAME NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
CUST_CITY_ID 254 HEIGHT BALANCED
sys@JINGYONG> show parameter optimzer_features_enable
sys@JINGYONG> show parameter optimizer_features_enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 11.2.0.1
sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52500;
执行计划
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 66 | 330 | 382 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_CITY_ID"=52500)
现在将optimizer_features_enable设置为10.2.0.3
sys@JINGYONG> alter session set optimizer_features_enable='10.2.0.3';
会话已更改。
sys@JINGYONG> set autotrace traceonly
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52500;
执行计划
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 121 | 605 | 382 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_CITY_ID"=52500)
现在的基数是121=55500*.002179391,CUST_CITY_ID的density为.002179391
这些nearly popular值被归类为non-popular values使用与non-popular values相同的计算公式.例如,如果谓词是
cust_city_id=52114,那么它的评估基数将是66行.与non-popular值52500的基数一样,但是cust_city_id=52114实际上有227行记录.
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52114;
执行计划
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 66 | 330 | 382 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_CITY_ID"=52114)
sys@JINGYONG> select count(*) from sh.customers where cust_city_id=52114;
COUNT(*)
----------
227
唯一能让优化器意识到这些near popular values的方法是使用动态抽样.动态抽样在优化一个sql语句时会收集额外的statement-specific对象统计信息.在这个例子中,动态抽样提示加入到了查询中且优化器会得到一个更准确的基数评估值.
sys@JINGYONG> select /*+ dynamic_sampling(a 2) */ count(a.cust_id) from sh.custo
mers a where a.cust_city_id=52114;
执行计划
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 382 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 246 | 410 | 382 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."CUST_CITY_ID"=52114)
Note
-----
- dynamic sampling used for this statement (level=2)
使用动态抽样可以提高高度平衡直方图中non-popular value的基数评估
在上面已经讨论了在oracle10g中使用直方图可能存的问题和可能的解决方法.建议从oracle11g开始收集统计信息时使用参数METHOD_OPT的缺省值且利用自适应游标.
如果想手动设置method_opt参数值不使用缺省值要确保只对需要直方图的列进行设置.将method_opt设置为for all columns size 254将会使oracle对每一个列都收集直方图信息.这对于收集统计信息来说是不必要的会增加运行时间和浪费系统资源,也会增加存储这些统计信息的空间.
还要避免将method_opt设置为for all index columns size 254它使oracle对存过索引的每一个列收集直方图信息,也会浪费系统资源.这个设置还有一个副作用就是会阻止oracle对哪些不存在索引的列收集基本的列统计信息.
pending statistics
当决定改变dbms_stats_gather_*_stats过程的参数缺省值时强烈建议在生产系统中修改之前先验证这些改变.如果没有一个完整的测试环境应该使用pending statistics.使用pending statistics代替常用的数据字典表,存储在pending表中的统计信息在它们被发和被系统使用之前可以以一种受控的方式来启用和测试.为了激活pending统计信息的收集需要对希望创建pending统计信息的对象使用dbms_stats.set_*_prefs过程将参数publish从缺省值true改变false.下面的例子中对sh用户下的sales表启用pending统计信息并对sales表收集统计信息.
sys@JINGYONG> begin
2 dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');
3 end;
4 /
PL/SQL 过程已成功完成。
通过将publish设置为false来启用pending统计信息
正常的收集对象统计信息
sys@JINGYONG> begin
2 dbms_stats.gather_table_stats('SH','SALES');
3 end;
4 /
PL/SQL 过程已成功完成。
对于这些对象收集的统计信息可以查询*_tab_pending_stats视图来显示:
sys@JINGYONG>select * from dba_tab_pending_stats where owner='SH'; OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------- ----------- ------------- SH SALES 918843 1769 29 918843 2013-12-19 9: SH SALES SALES_1995 0 0 0 0 2013-12-19 9: SH SALES SALES_1996 0 0 0 0 2013-12-19 9: SH SALES SALES_H1_1997 0 0 0 0 2013-12-19 9: SH SALES SALES_H2_1997 0 0 0 0 2013-12-19 9: SH SALES SALES_Q1_1998 43687 90 29 43687 2013-12-19 9: SH SALES SALES_Q1_1999 64186 121 29 64186 2013-12-19 9: SH SALES SALES_Q1_2000 62197 119 29 62197 2013-12-19 9: SH SALES SALES_Q1_2001 60608 119 30 60608 2013-12-19 9: SH SALES SALES_Q1_2002 0 0 0 0 2013-12-19 9: SH SALES SALES_Q1_2003 0 0 0 0 2013-12-19 9: SH SALES SALES_Q2_1998 35758 76 29 35758 2013-12-19 9: SH SALES SALES_Q2_1999 54233 103 29 54233 2013-12-19 9: SH SALES SALES_Q2_2000 55515 109 30 55515 2013-12-19 9: SH SALES SALES_Q2_2001 63292 119 30 63292 2013-12-19 9: SH SALES SALES_Q2_2002 0 0 0 0 2013-12-19 9: SH SALES SALES_Q2_2003 0 0 0 0 2013-12-19 9: SH SALES SALES_Q3_1998 50515 95 29 50515 2013-12-19 9: SH SALES SALES_Q3_1999 67138 120 29 67138 2013-12-19 9: SH SALES SALES_Q3_2000 58950 110 30 58950 2013-12-19 9: SH SALES SALES_Q3_2001 65769 124 29 65769 2013-12-19 9: SH SALES SALES_Q3_2002 0 0 0 0 2013-12-19 9: SH SALES SALES_Q3_2003 0 0 0 0 2013-12-19 9: SH SALES SALES_Q4_1998 48874 108 29 48874 2013-12-19 9: SH SALES SALES_Q4_1999 62388 114 29 62388 2013-12-19 9: SH SALES SALES_Q4_2000 55984 106 30 55984 2013-12-19 9: SH SALES SALES_Q4_2001 69749 136 29 69749 2013-12-19 9: SH SALES SALES_Q4_2002 0 0 0 0 2013-12-19 9: SH SALES SALES_Q4_2003 0 0 0 0 2013-12-19 9: 29 rows selected
可以通过一个alter session命令来设置初始化参数optimizer_use_pending_stats为true来使用pending统计信息.在启用pending统计信息之后任何在该会话运行的sql将使用这些新的没有发布的统计信息.对于在工作负载下的所访问的表没有pending统计信息时优化器将使用标准数据字典表中的当前统计信息.当你验证这些pending统计信息后可以使用dbms_stats.publish_pending_stats过程来发布.
何时收集统计信息
为了选择一个最佳的执行计划优化器必须要有有代表性的统计信息,有代表性的统计信息并不是最新的统计信息但是这组统计信息能帮助优化器判断在执行计划中每一个操作步骤所期待的正确的行记录数.
自动统计信息收集job
在一个预定义的维护窗口中oracle会自动对哪些丢失统计信息或者统计信息失效的所有对象收集统计信息(每个工作日的晚上10点到零晨2点和每个周末的6点到零晨2点).
可以使用企业管理器或使用dbms_scheduler和dbms_auto_task_admin包来改变这个维护窗口.
如果已经有一个完善的统计信息收集过程或者如果因为某些原因想要禁用自动统计信息收集可以禁用收集任务:
sys@JINGYONG> begin 2 dbms_auto_task_admin.disable( 3 client_name=>'auto optimizer stats collection', 4 operation=>null, 5 window_name=>null); 6 end; 7 / PL/SQL 过程已成功完成。
手动统计信息收集
如果计划手动维护优化器统计信息将需要判断何时进行收集.
基于失效统计,自动收集job或者系统中加载新数据的时间你能判断何时来收集统计信息.如果基本数据没有发生明显的改变不建议不断的重新收集统计信息这样只会浪费系统资源.
如果数据在一个预定义的ETL或ELT job只加载到系统中那么统计信息收集操作应该作为这个过程的一部分被调度.注意如果使用分区交换加载并希望利用增量统计信息将需要在交换过程完成后收集统计信息.
然而如果系统中有大量的联机事务只插入少量的数据但是这些操作每天都会发生,你将需要判断何时你的统计信息将会失效然后触发统计信息收集job.如果你计划依赖user_tab_statistics中的stale_stats列来判断统计信息是否失效你应该能意识到这些信息每天及时更新.如果需要更多更及时的信息比如你的表什么时候执行过DML操作你将需要查看user_tab_modifications视图,它会显示每一个表上执行的insert,update,delete操作,表是否执行过truncated并计算自己是否已经失效.需要注意这些信息是否定时的从内存中自动更新.如果需要最新的信息需要使用dbms_stats.flush_database_monitoring_info函数来手动刷新.
阻止超出范围的条件
不管你是使用自动统计信息收集job还是手动收集统计信息,如果终端用户在统计信息收集之前开始查询新插入的数据,即使只有不到10%的数据发生了变化也可能由于失效的统计信息得到一个次优的执行计划.发生这种问题最常见的原因是where子句中谓词提供的值超出了最小/最大列统计信息所能表示的范围.这通常称为超出范围的错误.
这种情况在分区表中很常见.一个新分区刚添加到一个存在的范围分区表中且记录刚被插入到分区中.在对这个新分区收集统计信息之前终端用户就开始查询这些新的数据.对于分区表,可以使用dbms_stats.copy_table_stats过程(从oracle10.2.0.4开始可以使用)来阻止超出范围的条件表达式.这个过程将复制原分区数据的统计信息为新创建分区的统计信息.它能复制依赖对象的统计信息:列,本地(分区)索引等等.直到对分区收集统计信息之前复制的统计信息只能作为临时的解决方法来使用.复制的统计信息不能代替真实收集的统计信息.
注意通常dbms_stats.copy_table_stats只能调整分区统计信息不能调整全局或表级别的统计信息.如果想在复制统计信息时对分区列进行全局级别的更新需要将dbms_stats.copy_table_stats中的flags参数设置为8.
对于非分区表你能通过dbms_stats.set_column_stats过程来手动设置列的最大值.通常这种方法不建议它并不能代替真实的收集的统计信息.
提高收集统计信息的效率
随着数据量的增长和维护窗口的缩短能及时的收集统计信息是很重要的.oracle提供了多种方法来提高统计信息收集的速度.使用并行用于收集统计信息的几种并行方法
内部对象并行
外部对象并行
内部对象并行与外部对象并行的组合
内部对象并行
内部对象并行是由dbms_stats.gather_*_stats过程的degree参数来控制的.degree参数控制着用于收集统计信息的并行服务器进程的数量.
通常oracle使用数据字典表中并行属性的值作为指定并行服务器进程的参数值.在oracle数据库中所有的表都有一个degree属性缺省值为1.对要收集统计信息的大表设显示地设置这个参数能提高统计信息收集的速度.
你也可以设置degree为auto_degree.oracle将基于一个对象的大小自动判断一个合适的并行服务进程个数来收集统计信息.这个值的范围在1-小对象(串行操作)到大对象的default_degree((PARALLEL_THREADS_PER_CPU X CPU_COUNT)之间.
你将会注意到对一个分区表设置degree这意味着对每一个分区使用多个并行服务器进程来收集统计信息但是不能同时对不同的分区收集统计信息.统计信息只能在一个分区收集完之后才能收集下一个分区.
外部对象并行
在oracle11.2.0.2中,外部对象并行被引入且由global statistics gathering preference concurrent来控制.当concurrent设置为true时,oracle将使用oracle job作业调度和高级队列组来创建和管理多个统计信息收集job并发执行.通过oracle来完全利用多个cpu来对多个表和(子)分区并发的收集统计信息来减小整个统计信息收集的时间.
活动并行统计信息收集job的最大个数是由job_queue_processes参数来控制的.job_queue_processes缺省值设置为1000.这通常对于并行统计信息收集操作来说太高了尤其是在并行执行也在使用时更是如此.一个最有效的值应该是总cpu核数的2倍(在rac中这是每一个节点的参数值).你需要确在系统级别设置这个参数(alter system命令或init.ora文件)而不是在会话级别(alter session).
内部和外部并行的组合
在一个并行统计收集操作中的每一个统计信息收集job都能以并行的方式来执行.将并行统计收集和并行执行组合起来能大大减小收集统计信息的时间.
当使用并行执行作为一个并行统计信息收集操作的一部分时你应该禁用parallel_adaptive_multi_user初始化参数来阻止并行job被降级为串行操作.它应该在系统级别来禁用而不是在会话级别禁用这个参数:
sys@JINGYONG> alter system set parallel_adaptive_multi_user=false; 系统已更改。
增量统计信息
分区表的统计信息收集是由表级别(global statistics)和(子)分区级别的统计信息收集操作组成的.如果一个分区表的incremental preference设置为true,dbms_stats.gather_*_stats中参数granularity的值包含global和estimate_percent设置为auto_sample_size,oracle将会通过扫描这些已经被添加或被修改的分区来获得全局级别的统计信息而不是整个表的.
增量全局统计信息是由表中每个分区存储的概要计算出来的.一个概要是这个分区和分区中列的统计信息的元数据.聚合分区级的统计信息和每个分区的概要信息将能精确的生成全局级别的统计信息因此消除了需要扫描整个表的操作.当一个新的分区添加到表中,你仅仅需要对这个新的分区进行统计信息收集而已.表级别的统计信息将会使用新分区的概要信息和已经存的分区的概要信息来自动和精确的计算出来.
注意当增量统计信息被启用时分区统计信息不从子分区统计信息中进行聚合操作.
何时不收集统计信息
尽管oracle需要精确的统计信息来选择一个最优的执行计划,有些情况下收集统计信息是很困难的,很昂贵的或者是不能及时完成的所以要有一和睦替代的策略.
volatile表
一个volatile表是随着时间的变化数据量会发生很大改变的表.例如,一个订单队列表,这个表在一天开始的时候是空的,随着时间的推移订单将会填满这个表.当被处理的一个订单从表中删除时所以这一天结束时这个表会被再次清空.
如果你依赖自动统计信息收集job来维护象这样的表的统计信息那么这些表显示的统计信息总是空的因为收集job是在晚上.然而在当天工作期间这个表可能有成千上万行记录.
对于这样的表最好是在白天收集一组有代表性的统计信息并锁定这些信息.锁定这些统计信息将阻止自动统计信息收集job来覆盖它们.优化器在优化sql语句之前在编译sql语句时会使用动态抽样对表收集基本的统计信息.尽管通过动态抽样产生的统计信息质量不高或者不象使用dbms_stats包收集的统计信息那样完整但在大多数情况下已经够用了.
全局临时表
全局临时表在应用程序上下文中经常用来存储中间结果.一个全局临时表对于有合理权限的所有用户共享它的定义,但是数据只在各自的会话中可见.直到数据被插入表中之前是不分配物理存储的.一个全局临时表可能是transaction specific(提交时删除行(或session-specific(提交时保存行).对一个transaction specific的表收集统计信息将导致对这个表进行truncate操作.相反,可以对全局临时表收集统计信息.然而统计信息的收集将仅仅基于session-private数据内容进行收集但是这些统计信息将能被访问这个表的所有会话使用.
如果有一个全局临时表持续有行数据且每一个会话将有相同的数据量和相同的数值那么应该在一个会话中收集一组有代表性的统计信息并锁定它们防止其它会话将其覆盖.注意自动统计信息收集job是不会收集全局临时表的统计信息.
中间工作表
中间工作表是典型的一个ELT过程或者一个复杂事务的一部分.这些表只会写一次,读一次然后truncate或者delete.在这种情况下收集统计信息成本超过了它所带来的好处,因为统计信息只能用一次.相反动态抽样在这种情况下更有用.建议锁定中间工作表的统计信息来持久的阻止自动统计信息收集job来对它们收集统计信息.
收集其它类型的统计信息
因为现在只支持基于成本的优化器,数据库中所有的表需要有统计信息,包含所有的数据字典表(sys,system用户所拥有的表和内置在system和sysaux表空间中的表)和通过动态v$性能视图使用的x$表.
数据字典统计信息
数据字典表的统计信息是由自动统计信息收集job在晚维护窗口进行收集的.强烈建议你允许oracle自动统计信息收集job来维护数据字典统计信息即使在你关闭对主应用程序方案关闭自动统计信息收集的情况下.可以使用dbms_stats.set_global_prefs过程
将autostats_target从auto改成oracle
sys@JINGYONG> begin
2 dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE');
3 end;
4 /
PL/SQL 过程已成功完成。
固定对象统计信息
自动统计信息收集job不会收集固定对象的统计统计信息.当优化统计信息丢失时不象其它的数据库表对于sql语句中调用X$表是不能自动使用动态抽样的.如果它们的统计信息丢失优化器会使用预先定义的缺省统计信息.这些缺省的统计信息可能没有代表性且可能导致选择次优的执行计划,在系统中可能会导致严重的性能问题.如果是这个原因造成性能问题强烈建议你手动收集固定对象的统计信息.
可以使用dbms_stats.gather_fixed_objects_stats过程来收集固定对象的统计信息.因为在系统如果存在一个有代表性的工作负载收集x$这些固定对象的统计信息是很重要的.在大型系统中由于收集固定对象统计信息需要额外的资源所以对固定对象收集统计信息不总是可行.如果不能在负载高峰期间收集固定对象的统计信息那么应该在系统负载降低之后对三种关键类型的固定对象表收集统计信息:
structural data–比如controlfile contents
Session based data – 比如 v$session, v$access
Workload data -比如 v$sql, v$sql_plan
建议当主数据库或应用程序升级后,实现新的模块或者改变数据库的配置后重新收集固定对象统计信息.例如,如果增加SGA的大小包含缓冲区缓存和共享池信息的x$表会显著的发生改变,比如v$buffer_pool或v$shared_pool_advice视图使用的x$表.
系统统计信息
系统统计信息能让优化器通过使用执行这个语句相关的实际系统硬件信息,比如,cpu速度和IO性能,来在执行计划中对每一个步骤获得更精确的成本值.系统统计信息缺省情况下是启用的,它使用缺省值自动初始化,这些值对于大多数系统来说是有代表性的.
小结
为了让优化器准确的判断执行计划中的成本它必须有这个语句所访问的所对象的精确的统计信息和运行sql语句所在系统的系统统计信息.这里介绍了为什么必须要有统计信息,怎样使用统计信息和各种收集统计信息的方法.
通过使用自动统计信息收集job和其它收集技术相结合能让DBA维护一组精确的统计信息来确保优化器总是有必要的信息来选择最优的执行计划.一旦一个统计信息收集策略已经在使用时,要改变这个策略应该以一种受控的方式来利用一些关键特性比如pending统计信息来确保不会对程序性能造成影响.