SQL调优之位图索引统计信息出错
SQL调优之位图索引统计信息出错
老外发来邮件，叫我对2个视图进行优化
---------------邮件内容-------------------------------------------------------------------
Hi,
But this view was yesterday running for 15 minutes – instead of regular 1-2 min
select * from intrc_exec_inbr_1ya_vw
I have today similar issue
Below view – should finish in 20 sec – and does not finish at all
select * from intrc_rpt_bench_vw where sid='1522215' and rpt_name='3ININ_MB'
---------------邮件内容-------------------------------------------------------------------
第一个SQL老外说要跑15分钟，但是以往只是跑1-2分钟。
第二个SQL老外说以前20秒，现在不出结果了，估计他跑了10分钟以上每出结果。
现在来看第一个SQL,它是一个视图，视图定义如下：
SELECT
ID.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_DATE
from
INTRC_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 td2
where td1.time_perd_lvl=3
and td2.time_perd_lvl=3
and td1.time_perd_end_date=add_months(td2.time_perd_end_date,-12)) T,
INTRC_INPR_BRDG_DIM PB
where
ID.GEO_SKID=GD.GEO_SKID
and ID.PROD_SKID=PD.PROD_SKID
and TB.INITV_SKID=ID.INITV_SKID
and TB.TIME_PERD_LVL=3
and TD.TIME_PERD_SKID=TB.TIME_PERD_SKID
and ID.DELET_DATE is null
and TB.TIME_PERD_SKID=T.TIME_PERD_SKID
and T.TIME_PERD_SKID_YA=F.TIME_PERD_SKID
and ID.INITV_SKID=PB.INITV_SKID
and PB.PROD_LVL=6
and PB.PROD_SKID=F.PROD_SKID
and 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.84
SQL> 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 1
BITMAP 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=3
and 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.93
SQL> explain plan for select * from ADWGU_INTRC.intrc_exec_inbr_1ya_vw;
Explained.
Elapsed: 00:00:01.54
SQL> 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这个索引，暂时先这样吧。
评论暂时关闭