数据库版本:10.2.0.1.0
前一阵子同事主要负责维护的一个系统,从凌晨1点多开始CPU负载极高,基本上就是100%.同事认为和当天晚上收集了优化器统计信息有关,就重新收集了一下优化器统计信息,过了一会儿系统负载下来了,同事决定对这个表制定特定的收集策略,可使用这个重新收集了优化器统计信息之后,系统负载马上又上来了.弄得同事有些不知所措.同事和我说起这事儿的时候,我觉得自然这次收集之前,性能上没有问题,那么最简单的方式就是复原优化器统计信息到那时候,于是我使用DBMS_STATS.restore_table_stats复原优化器统计信息到这次收集统计信息之前,但这个操作完成之后,CPU使用并没有降低下来,应该和这些过程的参数no_invalidate的默认值有关,默认值dbms_stats.auto_invalidate使得相关游标在一段时间后逐渐失效,这样可以避免大量的相关游标集中的进行硬分析带来的性能问题.但显然这不是我们想要的,我们想要的就是相关的游标都立刻重新硬分析,所以其实我调用过程的时候,就应该使用no_invalidate=>false的,所以现在需要采取一些纠正动作,来让所有相关的游标都失效,使得再次执行的时候需要重新硬分析,要达到这样的一个目的,最简单的途径就是对这个对象进行权限的变更:
grant select on res_article_info to scott;
命令完成后,从vmstat看,cpu负载立刻就降低下来了.我又执行了一个revoke select on res_article_info from scott;回收不需要的权限,然后exec dbms_stats.lock_table_stats(user,'RES_ARTICLE_INFO');
把这个表的优化器统计信息给锁定了,避免因为gather_stats_job使用默认的选项来收集优化器统计信息导致的问题(这里最初出现问题就是因为自动收集机制导致的),然后根据业务需要,考虑这个表的具体的收集选项,特别是estimate_percent和method_opt,具体收集的时候,调用gather_table_stats时指定force => TRUE 就可以了.
从awr报表和ash报表来看,是两个sql语句在当时的执行计划存在问题,都选择了错误的索引导致的逻辑io偏高导致了cpu使用率偏大.将两个sql语句精简如下,依然能够反映出当时的问题来:
select count(a.image3)
from res_article_info a
where a.status = 5 and a.product_id = :1 and display_time > sysdate - 90;
res_article_info 这个表230W的数据量,应用display_time > sysdate - 90 过滤条件返回13W~15W的数据量,可这里却选择了display_time上的索引,却没有选择选择性更高的product_id上的索引.第一次出现性能问题时捕抓到了这条sql语句,第二次出现性能问题时却没有从awr,ash报表中看到这条sql语句,应该来说,到第二次出现性能问题的时候,这个sql语句可能已经选择了正确的执行计划了.
另一个sql语句是:
SELECT count(1)
FROM V_173_HANGQING
WHERE PRODUCT_CATALOG_ID = 75450
OR PRODUCT_BRAND_ID = 75450;
v_173_hangqing这个视图的定义如下:
create or replace view v_173_hangqing as
select a.id,
a.title,
a.url referurl,
a.category_name categoryname,
a.from_where fromwhere,
a.display_time timepublished,
a.category_id,
a.product_catalog_id,
a.product_brand_id,
a.product_id,
decode(category_name,'行情',1,'导购',2,'评测',3,'调研',4)catalogtype
from res_article_info a
where a.product_id>0 and a.status=5 and a.category_name in ('行情','导购','评测','调研')
and display_time > sysdate - 90
and a.site_id=22
order by a.display_time desc;
这里也是错误的选择了display_time上的索引,而没有选择PRODUCT_CATALOG_ID和PRODUCT_BRAND_ID这两个列上的索引(这两个列上都有一个单列索引的).
display_time列上的索引定义是这样的:index NK_RES_ARTICLE_INFO on res_article_info(DISPLAY_TIME DESC);
当然为什么会选择了错误的执行计划呢,这个问题有待确认,现在来看,它们的执行计划都没有问题,都选择了正确的索引,可当初为什么选择了那么一个选择性很低的索引呢?
我们知道,9i的时候,在每次收集优化器统计信息前,可以使用dbms_stats.export_table_stats过程将当前的优化器统计信息备份到指定表里去(也可以直接使用dbms_stats.gather_table_stats(...,stattab =>...,statid =>...),它是先将当前统计信息保存到指定表中去,然后收集新的统计信息到数据字典中去),以便于当性能因为统计信息的收集出现问题的时候,可以使用dbms_stats.import_table_stats复原原来的优化器统计信息,10g的时候,这个备份当前的统计信息的过程自动化了,每次收集优化器统计信息的时候,oracle都会将当前的统计信息备份出来,然后才收集新的统计信息的,以后可以使用DBMS_STATS.restore_table_stats来复原优化器统计信息.oracle提供了视图dba_tab_stats_history可以查看备份的历史,但这里的信息很简单,只是表示在某个时间点保存了某个对象的统计信息,当时的统计信息具体是什么,并没有相应的视图提供出来,这里可以查询底层表来查看的:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE owner='SYS' and OBJECT_NAME LIKE 'WRI$_OPTSTAT_%';
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_OPR
WRI$_OPTSTAT_TAB_HISTORY
就是这些表来保存历史统计信息.可以通过这些表来查看历史统计信息的.
当然,我不仅需要查看历史的统计信息,也需要做一下10053事件查看一下为什么选择了错误的执行计划,所以采取了下面的方法:
(这个数据库连个测试数据库也没有,就算有,也不见得能模拟出当时的情况来,不直接在生产库用这种方法的话,也许只能activate物理从库了,也许也可以把这几个底层表导出来,导入到测试数据库中,并修改相应的obj#来实现了.这里把所有的脚本都准备好了,可以快速完成,如果性能上出现问题的话,也可以快速的复原统计信息的,所以才在生产库上执行了这样的操作,不过最好还是不要在生产库上执行这样的操作)
create table zsj_tab_statistics as
select 100 id,a.* from user_tab_statistics a where table_name='RES_ARTICLE_INFO' and 1=0;
create table zsj_tab_cols as
select 100 id,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID,DEFAULT_LENGTH,
NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH,GLOBAL_STATS,
USER_STATS,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED,V80_FMT_IMAGE,DATA_UPGRADED,HIDDEN_COLUMN,VIRTUAL_COLUMN,SEGMENT_COLUMN_ID,INTERNAL_COLUMN_ID,
HISTOGRAM,QUALIFIED_COL_NAME
from user_tab_cols where table_name='RES_ARTICLE_INFO' and 1=0;
(这里没有包含data_default这个字段,因为含有long类型的表是不能ctas的,要报错:ORA-00997 illegal use of LONG datatype.
User_Tab_Cols 有隐藏列信息,而USER_TAB_COL_STATISTICS,User_Tab_Columns都没有隐藏列信息.因为上面索引中的desc,导致创建了一个函数索引,导致了一个虚拟列的出现,要查看这个虚拟列的统计信息,所以查询User_Tab_Cols这个视图)
create table zsj_tab_histograms as
select 100 id,a.* from user_tab_histograms a where table_name='RES_ARTICLE_INFO' and 1=0;
USER_TAB_HISTOGRAMS 这里面有隐藏列的柱状图统计信息的.
这里的id用来标识某次的优化器统计信息.
建立好了这样的表之后,就开始复原优化器统计信息,把复原出来的优化器统计信息插入这3个表中,在复原出来的优化器统计信息下做10053事件:
复原优化器统计信息到因为自动收集导致了问题时的状态:
EXEC DBMS_STATS.restore_table_stats(USER,'RES_ARTICLE_INFO',as_of_timestamp => TO_DATE('2010-12-02 23','YYYY-MM-DD HH24'),force=>true); --实际上是12月2日22点自动收集了这个表的统计信息
insert into table zsj_tab_statistics
select 100 id,a.* from user_tab_statistics a where table_name='RES_ARTICLE_INFO';
insert into table zsj_tab_cols
select 100 id,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID,DEFAULT_LENGTH,
NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH,GLOBAL_STATS,
USER_STATS,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED,V80_FMT_IMAGE,DATA_UPGRADED,HIDDEN_COLUMN,VIRTUAL_COLUMN,SEGMENT_COLUMN_ID,INTERNAL_COLUMN_ID,
HISTOGRAM,QUALIFIED_COL_NAME
from user_tab_cols where table_name='RES_ARTICLE_INFO';
insert into zsj_tab_histograms
select 100 id,a.* from user_tab_histograms a where table_name='RES_ARTICLE_INFO';
commit; --将复原出来的优化器统计信息插入这3给表中去.这里使用100这个标识id.
connect username/password
alter session set tracefile_identifier='zhao100';
alter session set events '10053 trace name context forever';
select /*+ zhao100*/count(a.image3)
from res_article_info a
where a.status = 5
and a.product_id = 672914
and a.display_time is not null
and display_time > sysdate - 90
and a.channel_id not in
(36460, 36178, 36177, 36176, 36175, 36174, 36173, 36171, 36166, 36165,
36164, 36163, 36162, 35120, 30702, 30701, 30700, 30699)
and a.site_id = 22;
SELECT /*+ zhao100*/count(1)
FROM "V_173_HANGQING" "A2"
WHERE "PRODUCT_CATALOG_ID" = 75450
OR "PRODUCT_BRAND_ID" = 75450;
--针对这2个sql语句进行10053事件诊断.
同理进行了其它3个操作,最终:100对应最初因为自动收集导致问题时的状态,200对应同事第二次收集又导致问题时的状态,300对应自动收集之前没有问题时的状态,400对应同事第一次收集之后没问题的状态,也就是现在的状态.后来我又复原出了一个更早的状态,执行计划都没有问题的,对应id 500.
当然,我需要迅速的复原到我这些操作之前的状态,并且通过授权语句保证相关的sql都重新硬分析,sql性能不存在问题.
对于100的状态来说,第一个sql语句:
Access Path: index (AllEqRange)
Index: IND_ARTINFO_PROD_ID --对应product_id上的索引
resc_io: 59.00 resc_cpu: 564770
ix_sel: 1.1335e-04 ix_sel_with_filters: 1.1335e-04
Cost: 29.53 Resp: 29.53 Degree: 1
Column (#62): SYS_NC00062$(RAW)
AvgLen: 10.00 NDV: 1885160 Nulls: 0 Density: 5.3046e-07
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO --对应display_time上的desc索引
resc_io: 4.00 resc_cpu: 30859
ix_sel: 2.4647e-06 ix_sel_with_filters: 2.4513e-08
Cost: 2.00 Resp: 2.00 Degree: 1
****** trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange Index: NK_RES_ARTICLE_INFO
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.40 Bytes: 0
对于product_id = 672914,这里的谓词选择性还是没有什么大的问题的,可对于display_time > sysdate - 90来说,ix_sel: 2.4647e-06 ix_sel_with_filters: 2.4513e-08明显偏低了,而且对于单列的索引来说,ix_sel和ix_sel_with_filters两个值居然是不等的,可以看到,优化器已经将这个sql重写为了:
SELECT COUNT("A"."IMAGE3") "COUNT(A.IMAGE3)"
FROM "RES_ARTICLE_INFO" "A"
WHERE SYS_OP_DESCEND("A"."DISPLAY_TIME")<SYS_OP_DESCEND(SYSDATE@!-90)
......
AND "A"."DISPLAY_TIME">SYSDATE@!-90 AND "A"."DISPLAY_TIME" IS NOT NULL;
-----------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | RES_ARTICLE_INFO | 1 | 25 | 2 | 00:00:01 |
| 3 | INDEX RANGE SCAN | NK_RES_ARTICLE_INFO| 1 | | 2 | 00:00:01 |
-----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter......
3 - access("A"."SYS_NC00062$"<SYS_OP_DESCEND(SYSDATE@!-90))
3 - filter((SYS_OP_UNDESCEND("A"."SYS_NC00062$")>SYSDATE@!-90 AND SYS_OP_UNDESCEND("A"."SYS_NC00062$") IS NOT NULL))
可以看到这里已经使用index(display_time desc)这个函数索引所对应的虚拟列SYS_NC00062$了,从UNPARSED QUERY看,是完全基于DISPLAY_TIME这个列的,而从执行计划的predicates部分来看,又是完全基于SYS_NC00062$这个虚拟列的,那计算选择性,具体的说是ix_sel和ix_sel_with_filters的时候,是使用的SYS_NC00062$列的统计信息,还是display_time列上的统计信息,还是两者都使用了呢?
那来看看这时的优化器统计信息,我们知道即使是对单列索引来说,在判断这个列的选择性的时候,使用的都是列上的统计信息,而不会使用索引上的统计信息(比如distinct_keys),所以这里只查看user_tab_cols了,为了将这里的low_value,high_value这些raw data为可读的形式,使用了下面的函数:
create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
cn number;
cv varchar2(32);
cd date;
cc char(32);
cnv nvarchar2(32);
cr rowid;
begin
if (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return cv;
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd,'syyyy-mm-dd hh24:mi:ss');
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return cc;
elsif (type = 'NVARCHAR2') then
dbms_stats.CONVERT_RAW_VALUE_NVARCHAR(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.CONVERT_RAW_VALUE_ROWID(rawval, cr);
return RowidToChar(cr);
else
return 'UNKNOWN DATATYPE';
end if;
end;
我们看一下列上的统计信息:
select column_name,data_type,num_distinct,density,num_buckets,histogram,
low_value,high_value,
display_raw(low_value,data_type) low_value_display,
display_raw(high_value,data_type) high_value_display
from zsj_tab_cols where id=100 and COLUMN_NAME IN('DISPLAY_TIME','PRODUCT_ID')
union all
select column_name,data_type,num_distinct,density,num_buckets,histogram,
low_value,high_value,
display_raw(SYS_OP_UNDESCEND(low_value),'DATE') low_value_display,
display_raw(SYS_OP_UNDESCEND(HIGH_value),'DATE') high_value_display
from zsj_tab_cols where id=100 and COLUMN_NAME='SYS_NC00062$';
COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM LOW_VALUE HIGH_VALUE LOW_VALUE_DISPLAY HIGH_VALUE_DISPLAY
DISPLAY_TIME DATE 1885159 5.3045923447306E-7 1 NONE 2C6D07070D0D0D 78BB0206010101 -5591-07-07 12:12:12 2087-02-06 00:00:00
PRODUCT_ID NUMBER 45038 0.000114090131203651 254 HEIGHT BALANCED 3E6466 C3441240 -1 671763
SYS_NC00062$ RAW 1885160 5.3045895308621E-7 1 NONE 8791F3FCFEF8FEFAFF 999BF8F8F2F2F2FF 2010-12-03 00:00:00 0200-07-07 12:12:12
这是gather_stats_job自动收集到的优化器统计信息,product_id上不需要收集柱状图统计信息的,这里却收集了,而display_time,SYS_NC00062$这两个列上需要收集柱状图统计信息的,这里却没有收集,你也可以看到数据本身也是有问题的,都有公元前的数据出现了(而且公元前5591这个数据都是有问题的,这个问题下面再讨论),而且SYS_NC00062$列就是对display_time列应用SYS_OP_DESCEND这个函数,所以这两个列的最大和最小应该是交错对应的,但这里好像不是这样对应的.
我们计算一下display_time>sysdate-90的选择性,我这里是2010-12-24做的10053事件,所以按这个sysdate来计算,按SYS_NC00062$计算选择性,我不知道raw数据该如何计算选择性,如果按它显示出来的我们可以理解的日期计算的话,好像应该是这样的:
select (to_date('2010-12-03','yyyy-mm-dd')-(to_date('2010-12-24','yyyy-mm-dd')-90))/
(to_date('2010-12-03','yyyy-mm-dd')-to_date('0200-07-07','yyyy-mm-dd'))
from dual;
返回结果:0.000104349719768071
如果按照display_time来计算的话(因为-5591-07-07 12:12:12本身就是一个非法的date值,所以这里更不知道应该如何计算,大致的按照年份来计算吧):
select (2087.1-2010.8)/(2087.1-(-5591.6)) from dual;
返回结果:0.00993657780613906
当然这些数据和cbo给出的数据差距还是挺大的,我们无法确知cbo是如何计算选择性的(10053文件只是给出了结果,具体的计算过程并没有给出来),但有一点是可以肯定的,就是这里的问题是因为这些极少量的离散的胡乱录入的display_time数据(1980年甚至公元前的数据都有,2011年之后甚至2100年之后的数据都有)和display_time和SYS_NC00062$没收集柱状图统计信息造成的(即使把那些胡乱录入的数据清除掉了,这里似乎还是有必要收集柱状图统计信息的,因为近一年基本上是每月3W-5W的数据,可时间越往早退每月的数据越少,到2004年之前都不足1W了,实际上把那些胡乱录入的时间去除掉,把时间框定到2000-01到2010-01的话,不收集柱状图统计信息,问题也不是太大,这样计算出的选择性是2.2%左右,而实际是6.5%左右,不过最好还是收集柱状图统计信息的好)
我们再来看看200的情况:
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM LOW_VALUE HIGH_VALUE LOW_VALUE_DISPLAY HIGH_VALUE_DISPLAY
DISPLAY_TIME 1.73807931055162E-5 254 HEIGHT BALANCED 2C6D07070D0D0D 796B02080D0101 -5591-07-07 12:12:12 2107-02-08 12:00:00
PRODUCT_CATALOG_ID 0.00346020761245675 1 NONE 3E6466 C30B1535 -1 102052
PRODUCT_BRAND_ID 0.000215377988369589 1 NONE 3E6466 C30B2010 -1 103115
PRODUCT_ID 1.85418675368983E-5 1 NONE 3E6466 C3441F30 -1 673047
SYS_NC00062$ NONE
同事似乎意识到了原来自动收集的优化器统计信息的问题,所以这里product_id上不再收集柱状图统计信息,PRODUCT_CATALOG_ID,PRODUCT_BRAND_ID上也不再收集柱状图统计信息了(100里这3个列上都收集了柱状图信息的),display_time上收集了size 254的柱状图统计信息,应该来说是采用了先dbms_stats.delete_table_stats,尔后收集统计信息时method_opt里指定具体的列名具体的size的情况来收集的,这里一定没有指定SYS_NC00062$这个虚拟列,所以这个列上连基本的统计信息都没有收集.
Access Path: index (AllEqRange)
Index: IND_ARTINFO_PROD_ID
resc_io: 13.00 resc_cpu: 116444
ix_sel: 1.8542e-05 ix_sel_with_filters: 1.8542e-05
Cost: 6.51 Resp: 6.51 Degree: 1
Column (#62): SYS_NC00062$(RAW) NO STATISTICS (using defaults)
AvgLen: 12.00 NDV: 71426 Nulls: 0 Density: 1.4001e-05
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO
resc_io: 833.00 resc_cpu: 11182141
ix_sel: 0.0090011 ix_sel_with_filters: 4.0696e-04
Cost: 417.75 Resp: 417.75 Degree: 1
****** trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange Index: IND_ARTINFO_PROD_ID
Cost: 6.51 Degree: 1 Resp: 6.51 Card: 0.30 Bytes: 0
这里使用默认值,不知道默认值是什么,更不知道这里的ix_sel和ix_sel_with_filters是如何得来的,这些信息似乎不是从display_time上得来的(因为这个列上已经收集柱状图统计信息了,会有偏差,但不会有这么大的偏差),ix_sel偏小使得需要扫描的索引叶块变少,特别是ix_sel_with_filters偏小使得需要回访的数据行变少,这些都使得计算出来的cost相对实际值偏小,当然,因为这里还不是像100那样太偏小,比product_id上的选择性值要高,所以第一个sql还是选择了正确的执行计划.可因为这里的选择性相对于实际值还是偏小,所以导致第二个sql选择了错误的执行计划:
Column (#62): SYS_NC00062$(RAW) NO STATISTICS (using defaults)
AvgLen: 12.00 NDV: 71426 Nulls: 0 Density: 1.4001e-05
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO
resc_io: 833.00 resc_cpu: 11183067
ix_sel: 0.0090011 ix_sel_with_filters: 4.0696e-04
Cost: 417.75 Resp: 417.75 Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (AllEqRange)
Index: IND_ARTINFO_PROD_CATAID
resc_io: 11.00 resc_cpu: 598736
ix_sel: 0.0034602 ix_sel_with_filters: 0.0034602
Cost: 5.53 Resp: 5.53 Degree: 0
Access Path: index (AllEqRange)
Index: IND_ARTINFO_PROD_BRANDID
resc_io: 3.00 resc_cpu: 53764
ix_sel: 2.1538e-04 ix_sel_with_filters: 2.1538e-04
Cost: 1.50 Resp: 1.50 Degree: 0
Access path: Bitmap index - rejected
Cost: 2147.09 Cost_io: 2143.04 Cost_cpu: 34729613 Sel: 0.001209
Not believed to be index-only
Best:: AccessPath: IndexRange Index: NK_RES_ARTICLE_INFO
Cost: 417.75 Degree: 1 Resp: 417.75 Card: 0.10 Bytes: 0
Access Path: TableScan
Cost: 38091.90 Resp: 38091.90 Degree: 0
Cost_io: 37586.00 Cost_cpu: 4334415545
Resp_io: 37586.00 Resp_cpu: 4334415545
Access Path: index (AllEqRange)
Index: IND_ARTINFO_PROD_CATAID
resc_io: 1456.00 resc_cpu: 14069406
ix_sel: 0.0034602 ix_sel_with_filters: 0.0034602
Cost: 728.82 Resp: 728.82 Degree: 1
****** trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange Index: IND_ARTINFO_PROD_CATAID
Cost: 728.82 Degree: 1 Resp: 728.82 Card: 0.03 Bytes: 0
这里因为上面计算出来的选择性还是偏低,所以第2个sql还是使用了NK_RES_ARTICLE_INFO这个索引,而不是catalog_id和brand_id两个单列索引的index_combine来实现.
这里要说的是:同样是使用索引IND_ARTINFO_PROD_CATAID,为什么上面计算出来的cost那么小,而下面却这么大,应该来说下面的cost包含了索引叶块扫描和回访表的cost,而上面的cost计算中,只是包括了索引叶块扫描和rowid转换为位图的计算的cost,所以这部分很小,而回访表的cost是在bitmap index这部分计算的,当然bitmap index这部分还包括位图的or计算和位图转换为rowid的计算的cost.
再看看300的情况:
Column (#62): SYS_NC00062$(RAW)
AvgLen: 10.00 NDV: 1879099 Nulls: 0 Density: 5.3217e-07 Min: 0 Max: 707896020573642960009920835414392832
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO
resc_io: 80042.00 resc_cpu: 1129884677
ix_sel: 0.99739 ix_sel_with_filters: 0.039048
Cost: 40152.98 Resp: 40152.98 Degree: 1
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM LOW_VALUE HIGH_VALUE LOW_VALUE_DISPLAY HIGH_VALUE_DISPLAY
DISPLAY_TIME 2.30547550432277E-5 254 HEIGHT BALANCED 77AA08120E0B01 796B02080D0101 1970-08-18 13:10:00 2107-02-08 12:00:00
SYS_NC00062$ 5.32169938890926E-7 1 NONE 00 8855F7EDF1F4FEFAFF 1970-08-18 13:10:00
这里ix_sel_with_filters这个标识需要回访表的百分比数已经有点儿靠谱了,当然我还是不知道这个值是如何计算出来的.我把这堆没用的数据记录下来,是因为希望有一天能看出些端倪来.
400的情况:
Column (#62): SYS_NC00062$(RAW)
AvgLen: 10.00 NDV: 1023242 Nulls: 0 Density: 9.7729e-07 Min: 0 Max: 1098555594583279757371786541156794368
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO
resc_io: 16992.00 resc_cpu: 431167144
ix_sel: 0.6685 ix_sel_with_filters: 0.0066485
Cost: 8563.84 Resp: 8563.84 Degree: 1
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM LOW_VALUE HIGH_VALUE LOW_VALUE_DISPLAY HIGH_VALUE_DISPLAY
DISPLAY_TIME 9.76926939541899E-7 1 NONE 2C6D07070D0D0D 78BB0206010101 -5591-07-07 12:12:12 2087-02-06 00:00:00
SYS_NC00062$ 9.77285920632656E-7 1 NONE 00 D392F8F8F2F2F2FF -5591-07-07 12:12:12
虽然这两个sql的执行计划没有问题,但这里的ix_sel_with_filters还是偏低了.这里真正让我感到困惑的是:这里和100的区别是什么?这两次中,DISPLAY_TIME,SYS_NC00062$上都是size 1,可为什么选择性值差别那么大?应该来说和400这次采样比例是20%,其余几次都是100%没有关系,难道和400这次SYS_NC00062$ low_value=00,而100那次是8791F3FCFEF8FEFAFF有关?这是这里真正让我不明白的地方.
500的情况:
Column (#62): SYS_NC00062$(RAW)
AvgLen: 10.00 NDV: 1908883 Nulls: 0 Density: 5.2387e-07 Min: 0 Max: 707896575175403018777250424504188928
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO
resc_io: 122449.00 resc_cpu: 1501736892
ix_sel: 1 ix_sel_with_filters: 0.062997
Cost: 61378.67 Resp: 61378.67 Degree: 1
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM LOW_VALUE HIGH_VALUE LOW_VALUE_DISPLAY HIGH_VALUE_DISPLAY
DISPLAY_TIME 3.59531171352556E-5 254 HEIGHT BALANCED 77AA01120A383C 78BB0206010101 1970-01-18 09:55:59 2087-02-06 00:00:00
SYS_NC00062$ 5.23866575374185E-7 1 NONE 00 8855FEEDF5C7C3FF 1970-01-18 09:55:59
这里的ix_sel_with_filters更靠谱了,300和500的情况有些相似.当然了,200和这两个也有相同的地方,display_time都是size 254,但200和它们不一样的地方在于,200的虚拟列上没有收集基本的列统计信息,而这两个上收集了虚拟列上的基本列统计信息,难道就是因为这个造成了200和这两个的巨大区别?这也是让我想不明白的另一个地方.
再说说这里的公元前的数据:
select id,to_char(display_time,'syyyy-mm-dd hh24:mi:ss') display_time
from res_article_info d
where display_time<to_date('1900','yyyy') order by d.display_time desc;
ID DISPLAY_TIME
------------ --------------------
9301956 0200-11-02 18:29:26
7636749 0200-10-10 00:00:00
8984138 0200-07-07 12:12:12
11188149 0025-03-25 10:26:27
9474337 0010-06-07 09:07:38
9415655 0010-04-26 10:07:35
9272292 0009-11-06 06:55:21
9238076 0009-10-19 11:19:38
9040070 0009-07-30 08:20:06
9011510 0009-07-20 10:38:19
9005653 0009-07-17 12:21:21
8717519 0009-02-17 16:08:36
8677151 0004-07-29 00:00:00
11485106 -2010-08-17 16:35:41
11483696 -2010-08-17 09:38:09
11481024 -2010-08-16 11:01:04
11453875 -2010-07-30 06:35:22
11448309 -2010-07-27 11:14:30
11447101 -2010-07-26 16:53:12
8987468 00000-00-00 00:00:00
8987371 00000-00-00 00:00:00
暂且不说这里的公元前的数据,注意最后的两行数据,按说这样的数据都是invalid的date数据呀!可这里create table as是可以拷贝走这些数据的,而且新表里依然是小于公元前3000年的数据,exp后imp也是这样的.
block dump文件中的显示:
id=8987371的数据行的dump结果
col 0: [ 5] c4 09 63 4a 48 id列
col 28: [ 7] 2c 6d 07 07 0d 0d 0d display_time列
SQL> select dump(id,16) id,dump(DISPLAY_TIME,16) display_time from RES_ARTICLE_INFO WHERE ID=8987371;
ID DISPLAY_TIME
------------------------------------ --------------------------------------------------------------------------------
Typ=2 Len=5: c4,9,63,4a,48 Typ=12 Len=7: 2c,6d,7,7,d,d,d
结果是一致的.
id=8987468的数据行的dump结果
col 0: [ 5] c4 09 63 4b 45 id列
col 28: [ 7] 2c 6d 07 07 0d 0d 0d display_time列
SQL> select dump(id,16) id,dump(DISPLAY_TIME,16) display_time from RES_ARTICLE_INFO WHERE ID=8987468;
ID DISPLAY_TIME
------------------------------------ --------------------------------------------------------------------------------
Typ=2 Len=5: c4,9,63,4b,45 Typ=12 Len=7: 2c,6d,7,7,d,d,d
结果是一致的.
而且前面raw date经过display_raw函数转换后显示的日期:-5591-07-07 12:12:12也是有问题的.
SQL> select to_date('-5591-07-07 ','syyyy-mm-dd') from dual;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
也就是说这个年份还有上面的0000年份都不在oracle的date数据类型范围内.
现在处理这批明显随意录入的时间数据.不多的这批数据,其实不是因为应用控制不严格和编辑随意输入造成的问题,而是这个系统软件升级的时候,把老数据迁移到新系统时带来的一些问题,所以简单的更新这批数据到一个还算合理的时间点就可以了:
update res_article_info
set display_time=to_date('2000-05-16','yyyy-mm-dd')
where display_time<to_date('199901','yyyymm') or display_time>to_date('201102','yyyymm');
总共更新了1000行的数据.
再收集一下优化器统计信息,SYS_NC00062$,DISPLAY_TIME两个列上都收集高等直方图,product_id上只收集基本的列统计信息,不再收集直方图信息了.
SQL> exec dbms_stats.gather_table_stats(user,'RES_ARTICLE_INFO',cascade => FALSE,estimate_percent =>100,method_opt => 'FOR COLUMNS SIZE 254 SYS_NC00062$,DISPLAY_TIME,PRODUCT_ID SIZE 1');
COLUMN_NAME NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
SYS_NC00062$ 254 HEIGHT BALANCED
DISPLAY_TIME 254 HEIGHT BALANCED
PRODUCT_ID 1 NONE
对下面两个sql语句做10053事件,你会发现其实还是存在隐患的:
SELECT /*+ zhao800_2*/count(1)
FROM "V_173_HANGQING" "A2"
WHERE "PRODUCT_CATALOG_ID" = 75450
OR "PRODUCT_BRAND_ID" = 75450;
SELECT /*+ index(a2) no_index(a2 IND_ARTINFO_PROD_CATAID) no_index(a2 IND_ARTINFO_PROD_BRANDID) zhao800_2*/count(1)
FROM V_173_HANGQING A2
WHERE PRODUCT_CATALOG_ID = 75450
OR PRODUCT_BRAND_ID = 75450;
Table Stats::
Table: RES_ARTICLE_INFO Alias: A
#Rows: 2339222 #Blks: 223162 AvgRowLen: 674.00
SINGLE TABLE ACCESS PATH
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO
resc_io: 8298.00 resc_cpu: 100793489
ix_sel: 0.0631 ix_sel_with_filters: 0.0040995
Cost: 4159.20 Resp: 4159.20 Degree: 1
强制使用提示时的执行计划:
-----------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4159 | |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | RES_ARTICLE_INFO | 3 | 84 | 4159 | 00:00:50 |
| 3 | INDEX RANGE SCAN | NK_RES_ARTICLE_INFO| 9588 | | 269 | 00:00:04 |
-----------------------------------------------------------+-----------------------------------+
强制使用提示时的执行统计信息如下:
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.65 | 119K|
|* 2 | TABLE ACCESS BY INDEX ROWID| RES_ARTICLE_INFO | 1 | 3 | 1 |00:00:00.65 | 119K|
|* 3 | INDEX RANGE SCAN | NK_RES_ARTICLE_INFO | 1 | 9588 | 149K|00:00:00.15 | 706 |
-------------------------------------------------------------------------------------------------------------
这里还是不明白为什么ix_sel和ix_sel_with_filters是不等的.按道理说,ix_sel表示扫描叶块的数量,ix_sel_with_filters表示回访表的数量,也就是这里id=3步骤估算返回的数据行数,所以估算返回的数据行数应该是2339222*0.0040995=9589.6,也就是执行计划里id=3估算返回的数据行数9588,这个没有问题,但实际返回的行数是149k(也就是说要回访表这么多次),这个数是多少呢?2339222*0.0631=147605=148K,也就是说最好ix_sel=ix_sel_with_filters=0.0631,这里的问题是:ix_sel是准确的,但ix_sel_with_filters值偏低了,想想300,500的情况,DISPLAY_TIME size 254,SYS_NC00062$ size 1的情况,ix_sel偏高,都几乎为1了,但ix_sel_with_filters可以说基本上表达了实际情况.现在我们消除了极少量的随意录入的时间数据.我们再看一下DISPLAY_TIME size 254,SYS_NC00062$ size 1的情况:
SQL> exec dbms_stats.gather_table_stats(user,'RES_ARTICLE_INFO',cascade => FALSE,estimate_percent =>100,method_opt => 'FOR COLUMNS SYS_NC00062$ SIZE 1,DISPLAY_TIME SIZE 254',force=>true);
COLUMN_NAME NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
SYS_NC00062$ 1 NONE
DISPLAY_TIME 254 HEIGHT BALANCED
对下面的sql语句做10053事件:
SELECT /*+ index(a2) no_index(a2 IND_ARTINFO_PROD_CATAID) no_index(a2 IND_ARTINFO_PROD_BRANDID) zhao800_3*/count(1)
FROM V_173_HANGQING A2
WHERE PRODUCT_CATALOG_ID = 75450
OR PRODUCT_BRAND_ID = 75450;
Table Stats::
Table: RES_ARTICLE_INFO Alias: A
#Rows: 2339266 #Blks: 223162 AvgRowLen: 674.00
Column (#29): DISPLAY_TIME(DATE)
AvgLen: 8.00 NDV: 1919737 Nulls: 296 Density: 2.7620e-05 Min: 2451236 Max: 2455593
SINGLE TABLE ACCESS PATH
Column (#62): SYS_NC00062$(RAW)
AvgLen: 10.00 NDV: 1919738 Nulls: 0 Density: 5.2090e-07 Min: 0 Max: 707308303603968026721481162179477504
Access Path: index (RangeScan)
Index: NK_RES_ARTICLE_INFO
resc_io: 130847.00 resc_cpu: 1589578482
ix_sel: 0.99536 ix_sel_with_filters: 0.064661
Cost: 65584.37 Resp: 65584.37 Degree: 1
2339266*0.064661=151K
-----------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 64K | |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | RES_ARTICLE_INFO | 3 | 84 | 64K | 00:13:08 |
| 3 | INDEX RANGE SCAN | NK_RES_ARTICLE_INFO| 148K | | 4225 | 00:00:51 |
-----------------------------------------------------------+-----------------------------------+
我们无法知道它这里为什么是148K,而不是我上面计算出来的151K,但这两个值都可以认为是比较准确了,和实际的149K相比.这里的问题,和300,500的情况还是一样的,ix_sel_with_filters比较准确了,但ix_sel还是偏大,接近1了,难道ix_sel=ix_sel_with_filters=0.064661的情况就出现不了吗?还是我对于这个索引的结构理解存在问题呀?当然了,ix_sel代表了访问叶块的数量,ix_sel_with_filters代表了回访表的数量,如果两者只能选择一个的话,相对来说,ix_sel_with_filters的准确性对于cost的计算进而对于执行计划的生成的影响要更大一些,所以如果只能选择一个的话,还是选择display_time size 254,SYS_NC00062$ size 1的好一些(从200的例子可以知道这里的这个虚拟列是必须要收集基本的列的统计信息的)
最终:
像上面那样针对size auto自动收集到的优化器统计信息做出了一些纠正动作后,锁定优化器统计信息,size repeat收集优化器统计信息就可以了.
SQL> EXEC DBMS_STATS.lock_table_stats(USER,'RES_ARTICLE_INFO');
SQL> SELECT S.STATTYPE_LOCKED FROM USER_TAB_STATISTICS S where table_name='RES_ARTICLE_INFO';
STATTYPE_LOCKED
---------------
ALL
CREATE OR REPLACE PROCEDURE PROC_GATHER_STATS
IS
BEGIN
DBMS_STATS.gather_table_stats(USER,'RES_ARTICLE_INFO',cascade => TRUE,estimate_percent => 100,method_opt => 'FOR ALL COLUMNS SIZE REPEAT',force => true);
END;
然后使用job调用这个过程来收集这个表的统计信息。这里对于新加的列(包括因为函数索引添加的虚拟列),下次收集的时候都是size 1,收集基本的列的统计过信息,但不收集柱状图统计信息的.
最后简单的说几句:
size auto(gather_stats_job使用的就是这个选项)最常见的问题是:因为极个别的sql语句的执行,使用这个选项容易导致不必要的柱状图统计信息的收集,且不说这种收集方式是消耗资源的,它导致的问题包括:因为绑定变量peeking,导致执行计划不稳定;假如peeking到的是一个非典型输入值的话,容易生成对于典型输入值的非最优的执行计划;和cursor_sharing=simiar一起来解决应用不使用绑定变量的问题时,因为收集了柱状图统计信息,容易导致一个sql语句存在多个子游标的情况,sql语句的version_count激增,分析(包括硬分析和软分析)时持有library cache latch时间增加,容易导致library cache latch的争用,导致耗费大量的cpu的情况的出现.我见过因为size auto选项id这样的主键列上收集了高等直方图的情况(id虽然是唯一的,可最大值最小值之间数据分别并不均匀,所以它也是数据倾斜的,如果有语句,包括个别的手工执行的sql语句中出现了where id>v_id这样的id列上的范围判断谓词的使用,size auto选项在下次收集优化器统计信息的时候就可能会收集这个id列上的高等直方图信息),name这样几乎不出现在where条件中,只是出现在select子句部分的列也收集了高等直方图的情况(应该是有极个别的sql语句where中使用了类似name like 'sun%'这样范围判断的谓词导致的)。当然,也偶尔有100这样的情况出现,应该收集直方图的列却只收集了基本的列统计信息,没有任何的直方图信息收集.毕竟优化器对于我们的应用并不如我们熟悉,所以一任的让优化器来做选择的行为是不负责任的,所以我一般来说,倾向于对于一般的表来说,使用gather_stats_job做优化器统计信息的收集,但对于一些业务核心表,我会在size auto的基础上,使用类似于method_opt => 'FOR COLUMNS SYS_NC00062$ SIZE 1,DISPLAY_TIME SIZE 254'这样的选项,对于一些不合理的列的收集行为作出纠正,然后锁定优化器统计信息的收集,这样gather_stats_job默认就不再收集这个对象上的统计信息了,尔后自己定制job来收集这些业务核心表上的优化器统计信息,收集时使用method_opt => 'FOR ALL COLUMNS SIZE REPEAT',force => true这样的选项.
这里一些1900年前的日期和2100年后的日期这样一些明显偏离实际的绝大部分数据的出现,也容易带来一些问题:如果没有这些极端数据,其它的绝大多数数据是均匀分布的话,那么列上只收集基本的列统计信息,而不收集直方图信息,优化器也可以找到最优的执行计划,但因为这些极端数据的出现,如果采样时收集到了这样的数据,因为没有直方图信息时,优化器认为数据是均匀分布的,而这里出现了极大的最大值和(或者)极小的最小值,优化器在判断谓词的选择性时极可能出现很大的偏差,导致非最优的执行计划的选择的.当然这时一种解决方案就是收集直方图信息,当然最好还是控制这些极端数据的录入和对于已经存在的极端数据的处理.毕竟直方图信息也会带来一些问题的,关键是你这里除了这些极端数据之外数据本来就是均匀分布的,就不应该收集直方图信息的.
当然,这里加粗的红色部分是我理解不了的,希望高人指点一下.
扫一扫
119
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
