SQL调优之位图索引统计信息出错

老外发来邮件，叫我对2个视图进行优化 ---------------邮件内容-------------------------------------------------------------------Hi,But this view was yesterday running for 15 minutes – instead of regular 1-2 minselect * from intrc_exec_inbr_1ya_vw I have today similar issueBelow view – should finish in 20 sec – and does not finish at allselect * from intrc_rpt_bench_vw where sid="1522215" and rpt_name="3ININ_MB"---------------邮件内容------------------------------------------------------------------- 第一个SQL老外说要跑15分钟，但是以往只是跑1-2分钟。第二个SQL老外说以前20秒，现在不出结果了，估计他跑了10分钟以上每出结果。现在来看第一个SQL,它是一个视图，视图定义如下： SELECTID.INITV_SKID INITV_SKID,ID.INITV_ID INITV_ID,TD.TIME_PERD_SKID TIME_PERD_SKID,TD.MTH_NAME MTH_NAME,GD.GEO_NAME GEO_NAME,PD.PROD_NAME BRAND_NAME,to_char（INITV_SHPMT_START_DATE,"<YYYYMMDD>DD-Mon-YY"） INITV_SHPMT_START_DATE_TXT,ID.INITV_NAME INITV_NAME,ID.INITV_GLOBL_PARNT_ID INITV_GLOBL_PARNT_ID,ID.INITV_GLOBL_PARNT_NAME INITV_GLOBL_PARNT_NAME,ID.INITV_REGN_PARNT_ID INITV_REGN_PARNT_ID,ID.INITV_REGN_PARNT_NAME INITV_REGN_PARNT_NAME,"<"||nvl（upper（decode（ID.INITV_GLOBL_PARNT_NAME,"Undefined","ZZ",ID.INITV_GLOBL_PARNT_NAME））,"ZZ"）||" "||nvl（ID.INITV_GLOBL_PARNT_ID,""）|| nvl（upper（decode（ID.INITV_REGN_PARNT_NAME,"Undefined","ZZ",ID.INITV_REGN_PARNT_NAME））,"ZZ"）||" "||nvl（ID.INITV_REGN_PARNT_ID,""）|| upper（ID.INITV_NAME）||" "||">" INITV_NAME_SORT,"MKT" INITV_LVL,ID.ON_SHELF_DATE ON_SHELF_DATE,ID.INITV_SHPMT_START_DATE INITV_SHPMT_START_DATE,F.VAL_SHARE_ACTL BRAND_1YA_VAL_SHARE_ACTL,F.VOL_SHARE_ACTL BRAND_1YA_VOL_SHARE_ACTL,ID.DELET_DATE DELET_DATEfromINTRC_INBR_FCT F,INTRC_INITV_DIM ID,INTRC_TIME_DIM TD,INTRC_PROD_DIM PD,INTRC_GEO_DIM GD,INTRC_INITV_TIME_BRDG_DIM TB,（select td2.time_perd_skid , td1.time_perd_skid time_perd_skid_ya from intrc_time_dim td1, intrc_time_dim td2where td1.time_perd_lvl=3and td2.time_perd_lvl=3and td1.time_perd_end_date=add_months（td2.time_perd_end_date,-12）） T,INTRC_INPR_BRDG_DIM PBwhere ID.GEO_SKID=GD.GEO_SKIDand ID.PROD_SKID=PD.PROD_SKIDand TB.INITV_SKID=ID.INITV_SKIDand TB.TIME_PERD_LVL=3and TD.TIME_PERD_SKID=TB.TIME_PERD_SKIDand ID.DELET_DATE is nulland TB.TIME_PERD_SKID=T.TIME_PERD_SKIDand T.TIME_PERD_SKID_YA=F.TIME_PERD_SKIDand ID.INITV_SKID=PB.INITV_SKIDand PB.PROD_LVL=6and PB.PROD_SKID=F.PROD_SKIDand ID.GEO_SKID=F.GEO_SKID and td.time_perd_lvl=3 现在来看看执行计划： SQL> explain plan for select * from ADWGU_INTRC.intrc_exec_inbr_1ya_vw; Explained. Elapsed: 00:00:00.84SQL> select * from table（dbms_xplan.display）; PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 10848280 -------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 273 | 9223P （0）| | | || 1 | TABLE ACCESS BY INDEX ROWID | INTRC_PROD_DIM | 1 | 41 | 2 （0）| 00:00:01 | | || 2 | NESTED LOOPS | | 1 | 273 | 9223P （0）| | | || 3 | NESTED LOOPS | | 1 | 232 | 9223P （0）| | | || 4 | NESTED LOOPS | | 330 | 72600 | 9223P （0）| | | || 5 | NESTED LOOPS | | 1312K| 255M| 9223P （0）| | | ||* 6 | HASH JOIN | | 6558 | 1146K| 34 （9）| 00:00:01 | | || 7 | TABLE ACCESS FULL | INTRC_GEO_DIM | 2532 | 53172 | 3 （0）| 00:00:01 | | ||* 8 | HASH JOIN | | 6558 | 1011K| 31 （10）| 00:00:01 | | ||* 9 | TABLE ACCESS FULL | INTRC_INITV_DIM | 833 | 94962 | 6 （0）| 00:00:01 | | ||* 10 | HASH JOIN | | 6558 | 281K| 24 （9）| 00:00:01 | | || 11 | PARTITION RANGE SINGLE | | 171 | 2736 | 8 （0）| 00:00:01 | 1 | 1 ||* 12 | TABLE ACCESS FULL | INTRC_TIME_DIM | 171 | 2736 | 8 （0）| 00:00:01 | 1 | 1 ||* 13 | HASH JOIN | | 6558 | 179K| 15 （7）| 00:00:01 | | || 14 | PARTITION RANGE SINGLE | | 171 | 2565 | 8 （0）| 00:00:01 | 1 | 1 ||* 15 | TABLE ACCESS FULL | INTRC_TIME_DIM | 171 | 2565 | 8 （0）| 00:00:01 | 1 | 1 || 16 | PARTITION RANGE SINGLE | | 6558 | 85254 | 6 （0）| 00:00:01 | 1 | 1 ||* 17 | TABLE ACCESS FULL | INTRC_INITV_TIME_BRDG_DIM | 6558 | 85254 | 6 （0）| 00:00:01 | 1 | 1 || 18 | PARTITION RANGE SINGLE | | 200 | 5000 | 9223P （0）| | 1 | 1 ||* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| INTRC_INBR_FCT | 200 | 5000 | 9223P （0）| | 1 | 1 || 20 | BITMAP CONVERSION TO ROWIDS | | | | | | | || 21 | BITMAP INDEX FULL SCAN | INTRC_INBR_FCT_BX1 | | | | | 1 | 1 || 22 | PARTITION RANGE SINGLE | | 1 | 16 | 9223P （0）| | 1 | 1 || 23 | BITMAP CONVERSION TO ROWIDS | | 1 | 16 | 9223P （0）| | | || 24 | BITMAP AND | | | | | | | ||* 25 | BITMAP INDEX SINGLE VALUE | INTRC_TIME_DIM_BX1 | | | | | 1 | 1 || 26 | BITMAP CONVERSION FROM ROWIDS | | | | | | | || 27 | SORT ORDER BY | | | | | | | ||* 28 | INDEX RANGE SCAN | INTRC_TIME_DIM_PK | 1 | | 1 （0）| 00:00:01 | 1 | 1 || 29 | BITMAP CONVERSION FROM ROWIDS | | | | | | | ||* 30 | INDEX RANGE SCAN | INTRC_TIME_DIM_NX1 | 1 | | 1 （0）| 00:00:01 | 1 | 1 || 31 | BITMAP CONVERSION TO ROWIDS | | 1 | 12 | 9223P （0）| | | || 32 | BITMAP AND | | | | | | | || 33 | BITMAP CONVERSION FROM ROWIDS | | | | | | | ||* 34 | INDEX RANGE SCAN | INTRC_INPR_BRDG_DIM_PK | 1 | | 0 （0）| 00:00:01 | | ||* 35 | BITMAP INDEX SINGLE VALUE | INTRC_INPR_BRDG_DIM_BX1 | | | | | | ||* 36 | INDEX RANGE SCAN | INTRC_PROD_DIM_PK | 1 | | 1 （0）| 00:00:01 | | |------------------------------------------------------------------------------------------------------------------------------------- Predicate Information （identified by operation id）:--------------------------------------------------- 6 - access（"ID"."GEO_SKID"="GD"."GEO_SKID"） 8 - access（"TB"."INITV_SKID"="ID"."INITV_SKID"） 9 - filter（"ID"."DELET_DATE" IS NULL） 10 - access（"TB"."TIME_PERD_SKID"="TD2"."TIME_PERD_SKID"） 12 - filter（"TD2"."TIME_PERD_LVL"=3） 13 - access（"TD"."TIME_PERD_SKID"="TB"."TIME_PERD_SKID"） 15 - filter（"TD"."TIME_PERD_LVL"=3） 17 - filter（"TB"."TIME_PERD_LVL"=3） 19 - filter（"ID"."GEO_SKID"="F"."GEO_SKID"） 25 - access（"TD1"."TIME_PERD_LVL"=3） 28 - access（"TD1"."TIME_PERD_SKID"="F"."TIME_PERD_SKID"） filter（"TD1"."TIME_PERD_SKID"="F"."TIME_PERD_SKID"） 30 - access（"TD1"."TIME_PERD_END_DATE"=ADD_MONTHS（INTERNAL_FUNCTION（"TD2"."TIME_PERD_END_DATE"）,-12）） 34 - access（"ID"."INITV_SKID"="PB"."INITV_SKID" AND "PB"."PROD_SKID"="F"."PROD_SKID"） 35 - access（"PB"."PROD_LVL"=6） 36 - access（"ID"."PROD_SKID"="PD"."PROD_SKID"） 63 rows selected. Elapsed: 00:00:02.61 根据以往的经验，如果某个SQL以前只需要1-2分钟就能执行完，现在突然反常跑了很久，那么多半是由于统计信息没有收集导致的，所以我重新对该SQL涉及到的所有表都收集了统计信息，不过很郁闷的是，这个SQL依然要执行15分钟。 Ok,这个时候，我又根据经验，将某些表move了，同时rebuild了某些 index………………………………….4424 rows selected. Elapsed: 00:05:32.15这一次，SQL跑了5分钟。看来以往的经验还是有点用的，起码SQL执行时间降低到了5分钟。不过显然不符合老外1-2分钟出结果的要求。好了，现在我必须认真分析这个视图，认真分析这个执行计划，你可能要问我为什么一开始不认真分析执行计划，只是靠经验呢？其实SQL调优做多了就没啥感觉了，有时候真的是靠感觉。请注意观察执行计划，ID=5的这一行引起了我的注意：它是NESTED LOOPS,那么它的out table是ID=18,19,20,21这些行取得的结果。18 | PARTITION RANGE SINGLE | | 200 | 5000 | 9223P （0）| | 1 | 1 ||* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| INTRC_INBR_FCT | 200 | 5000 | 9223P （0）| | 1 | 1 || 20 | BITMAP CONVERSION TO ROWIDS | | | | | | | || 21 | BITMAP INDEX FULL SCAN | INTRC_INBR_FCT_BX1 | | | | | 1 | 1 | 好了，现在来看看这个位图索引的选择率： SQL> select a.index_type,decode（partitioned,"YES",b.partition_name,"NO"） partition,b.subpartition_name 2 subpartition_name,b.num_rows,b.distinct_keys,b.num_rows/b.distinct_keys avg_row_per_key,b.distinct_keys/b.num_rows 3 SELECTIVITY from dba_indexes a,dba_ind_statistics b where a.owner=b.owner and 4 a.index_name=b.index_name and a.owner=upper（"ADWGU_INTRC"） and a.index_name=upper（"INTRC_INBR_FCT_BX1"）; INDEX_TYPE PARTITION SUBPARTITION_NAME NUM_ROWS DISTINCT_KEYS AVG_ROW_PER_KEY SELECTIVITY-------------------- -------------------- -------------------- ---------- ------------- --------------- -----------BITMAP PMAX 2 2 1 1BITMAP 2 2 1 1 Elapsed: 00:00:01.01 从统计信息上面看，居然只有2行，晕了，我收集过统计信息的哈。 SQL> select time_perd_lvl, count（*） from ADWGU_INTRC.INTRC_INBR_FCT group by time_perd_lvl ; TIME_PERD_LVL COUNT（*）------------- ---------- 3 17789 4 222 Elapsed: 00:00:01.11根据SQL统计来看，这个位图索引只有2个值，现在再去看看视图定义：有很多的这样的过滤条件where td1.time_perd_lvl=3and td2.time_perd_lvl=3同样的，可以从filter里面看到 15 - filter（"TD"."TIME_PERD_LVL"=3） 17 - filter（"TB"."TIME_PERD_LVL"=3）很明显了啊，这里不应该走位图索引啊，不要告诉我你还不懂哈。那么我unusable了这个索引，再看看执行计划： SQL> alter index ADWGU_INTRC.INTRC_INBR_FCT_BX1 unusable; Index altered. Elapsed: 00:00:00.93SQL> explain plan for select * from ADWGU_INTRC.intrc_exec_inbr_1ya_vw; Explained. Elapsed: 00:00:01.54SQL> select * from table（dbms_xplan.display）; PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3816989412 -------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 273 | 168 （11）| 00:00:02 | | ||* 1 | TABLE ACCESS BY LOCAL INDEX ROWID| INTRC_TIME_DIM | 1 | 15 | 2 （0）| 00:00:01 | 1 | 1 || 2 | NESTED LOOPS | | 1 | 273 | 168 （11）| 00:00:02 | | || 3 | NESTED LOOPS | | 1 | 258 | 166 （11）| 00:00:02 | | || 4 | NESTED LOOPS | | 1 | 217 | 164 （11）| 00:00:02 | | ||* 5 | HASH JOIN | | 1 | 196 | 162 （12）| 00:00:02 | | || 6 | PARTITION RANGE SINGLE | | 18011 | 439K| 14 （15）| 00:00:01 | 1 | 1 || 7 | TABLE ACCESS FULL | INTRC_INBR_FCT | 18011 | 439K| 14 （15）| 00:00:01 | 1 | 1 ||* 8 | HASH JOIN | | 19302 | 3223K| 147 （11）| 00:00:02 | | ||* 9 | HASH JOIN | | 287 | 45633 | 31 （10）| 00:00:01 | | ||* 10 | HASH JOIN | | 287 | 12915 | 24 （9）| 00:00:01 | | ||* 11 | HASH JOIN | | 7 | 224 | 17 （6）| 00:00:01 | | || 12 | PARTITION RANGE SINGLE | | 171 | 2736 | 8 （0）| 00:00:01 | 1 | 1 ||* 13 | TABLE ACCESS FULL | INTRC_TIME_DIM | 171 | 2736 | 8 （0）| 00:00:01 | 1 | 1 || 14 | PARTITION RANGE SINGLE | | 171 | 2736 | 8 （0）| 00:00:01 | 1 | 1 ||* 15 | TABLE ACCESS FULL | INTRC_TIME_DIM | 171 | 2736 | 8 （0）| 00:00:01 | 1 | 1 || 16 | PARTITION RANGE SINGLE | | 6558 | 85254 | 6 （0）| 00:00:01 | 1 | 1 ||* 17 | TABLE ACCESS FULL | INTRC_INITV_TIME_BRDG_DIM | 6558 | 85254 | 6 （0）| 00:00:01 | 1 | 1 ||* 18 | TABLE ACCESS FULL | INTRC_INITV_DIM | 833 | 94962 | 6 （0）| 00:00:01 | | ||* 19 | TABLE ACCESS FULL | INTRC_INPR_BRDG_DIM | 122K| 14M| 115 （10）| 00:00:02 | | || 20 | TABLE ACCESS BY INDEX ROWID | INTRC_GEO_DIM | 1 | 21 | 2 （0）| 00:00:01 | | ||* 21 | INDEX RANGE SCAN | INTRC_GEO_DIM_PK | 1 | | 1 （0）| 00:00:01 | | || 22 | TABLE ACCESS BY INDEX ROWID | INTRC_PROD_DIM | 1 | 41 | 2 （0）| 00:00:01 | | ||* 23 | INDEX RANGE SCAN | INTRC_PROD_DIM_PK | 1 | | 1 （0）| 00:00:01 | | || 24 | PARTITION RANGE SINGLE | | 1 | | 1 （0）| 00:00:01 | 1 | 1 ||* 25 | INDEX RANGE SCAN | INTRC_TIME_DIM_PK | 1 | | 1 （0）| 00:00:01 | 1 | 1 |------------------------------------------------------------------------------------------------------------------------------- Predicate Information （identified by operation id）:--------------------------------------------------- 1 - filter（"TD"."TIME_PERD_LVL"=3） 5 - access（"TD1"."TIME_PERD_SKID"="F"."TIME_PERD_SKID" AND "PB"."PROD_SKID"="F"."PROD_SKID" AND "ID"."GEO_SKID"="F"."GEO_SKID"） 8 - access（"ID"."INITV_SKID"="PB"."INITV_SKID"） 9 - access（"TB"."INITV_SKID"="ID"."INITV_SKID"） 10 - access（"TB"."TIME_PERD_SKID"="TD2"."TIME_PERD_SKID"） 11 - access（"TD1"."TIME_PERD_END_DATE"=ADD_MONTHS（INTERNAL_FUNCTION（"TD2"."TIME_PERD_END_DATE"）,-12）） 13 - filter（"TD1"."TIME_PERD_LVL"=3） 15 - filter（"TD2"."TIME_PERD_LVL"=3） 17 - filter（"TB"."TIME_PERD_LVL"=3） 18 - filter（"ID"."DELET_DATE" IS NULL） 19 - filter（"PB"."PROD_LVL"=6） 21 - access（"ID"."GEO_SKID"="GD"."GEO_SKID"） 23 - access（"ID"."PROD_SKID"="PD"."PROD_SKID"） 25 - access（"TD"."TIME_PERD_SKID"="TB"."TIME_PERD_SKID"） 51 rows selected. Elapsed: 00:00:02.12 再跑一下SQL： 4424 rows selected. Elapsed: 00:02:25.35 现在只跑了2分钟，好了，现在可以给老外交差了我不知道以前这个SQL的执行计划到底是什么样子的，不过从目前来看，原因在于CBO选择了那个位图索引，而选择这个位图索引的原因是统计信息不对，我又重新收集了统计信息，用过很多种方法，包括100%的采样率，但是还是发现统计信息没有一点改变。。。算了没办法只有unusable这个索引，暂时先这样吧。Oracle 11g R1 数据字典的 latch: cache buffers chains问题SQL调优之使用并行特征相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）