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

最新推荐文章于 2024-05-11 13:37:18 发布

iteye_20478

最新推荐文章于 2024-05-11 13:37:18 发布

阅读量83

点赞数

老外发来邮件，叫我对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

-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

| 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 | | |

|* 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

-------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------

| 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这个索引，暂时先这样吧。

iteye_20478

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL调优之位图索引统计信息出错

老外发来邮件，叫我对2个视图进行优化---------------邮件内容-------------------------------------------------------------------Hi,But this view was yesterday running for 15 minutes – instead of regular 1-2 minsele...
复制链接

扫一扫