共计 22506 个字符，预计需要花费 57 分钟才能阅读完成。
这篇文章主要介绍了 Oracle 如何收集优化统计数据，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
介绍
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-18 22
SH SALES SALES_1995 0 0 0 0 2013-12-18 22
SH SALES SALES_1996 0 0 0 0 2013-12-18 22
SH SALES SALES_H1_1997 0 0 0 0 2013-12-18 22
SH SALES SALES_H2_1997 0 0 0 0 2013-12-18 22
SH SALES SALES_Q1_1998 43687 90 29 43687 2013-12-18 22
SH SALES SALES_Q1_1999 64186 121 29 64186 2013-12-18 22
SH SALES SALES_Q1_2000 62197 119 29 62197 2013-12-18 22
SH SALES SALES_Q1_2001 60608 119 30 60608 2013-12-18 22
SH SALES SALES_Q1_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q1_2003 0 0 0 0 2013-12-18 22
SH SALES SALES_Q2_1998 35758 76 29 35758 2013-12-18 22
SH SALES SALES_Q2_1999 54233 103 29 54233 2013-12-18 22
SH SALES SALES_Q2_2000 55515 109 30 55515 2013-12-18 22
SH SALES SALES_Q2_2001 63292 119 30 63292 2013-12-18 22
SH SALES SALES_Q2_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q2_2003 0 0 0 0 2013-12-18 22
SH SALES SALES_Q3_1998 50515 95 29 50515 2013-12-18 22
SH SALES SALES_Q3_1999 67138 120 29 67138 2013-12-18 22
SH SALES SALES_Q3_2000 58950 110 30 58950 2013-12-18 22
SH SALES SALES_Q3_2001 65769 124 29 65769 2013-12-18 22
SH SALES SALES_Q3_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q3_2003 0 0 0 0 2013-12-18 22
SH SALES SALES_Q4_1998 48874 108 29 48874 2013-12-18 22
SH SALES SALES_Q4_1999 62388 114 29 62388 2013-12-18 22
SH SALES SALES_Q4_2000 55984 106 30 55984 2013-12-18 22
SH SALES SALES_Q4_2001 69749 136 29 69749 2013-12-18 22
SH SALES SALES_Q4_2002 0 0 0 0 2013-12-18 22
SH SALES SALES_Q4_2003 0 0 0 0 2013-12-18 22
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 性能, 来在执行计划中对每一个步骤获得更精确的成本值. 系统统计信息缺省情况下是启用的, 它使用缺省值自动初始化, 这些值对于大多数系统来说是有代表性的.
感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“Oracle 如何收集优化统计数据”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!
