文章內(nèi)容

SQL調(diào)優(yōu)之位圖索引統(tǒng)計(jì)信息出錯

發(fā)布時(shí)間: 2012/8/24 17:20:50

-

老外發(fā)來郵件，叫我對2個視圖進(jìn)行優(yōu)化

---------------郵件內(nèi)容-------------------------------------------------------------------

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'
---------------郵件內(nèi)容-------------------------------------------------------------------

第一個SQL老外說要跑15分鐘，但是以往只是跑1-2分鐘。
第二個SQL老外說以前20秒，現(xiàn)在不出結(jié)果了，估計(jì)他跑了10分鐘以上每出結(jié)果。

現(xiàn)在來看第一個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

現(xiàn)在來看看執(zhí)行計(jì)劃：

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

根據(jù)以往的經(jīng)驗(yàn)，如果某個SQL以前只需要1-2分鐘就能執(zhí)行完，現(xiàn)在突然反常跑了很久，那么多半是由于統(tǒng)計(jì)信息沒有收集導(dǎo)致的，所以我重新對該SQL涉及到的所有表都收集了統(tǒng)計(jì)信息，不過很郁悶的是，這個SQL依然要執(zhí)行15分鐘。

Ok,這個時(shí)候，我又根據(jù)經(jīng)驗(yàn)，將某些表move了，同時(shí)rebuild了某些 index

………………………………….

4424 rows selected.

Elapsed: 00:05:32.15

這一次，SQL跑了5分鐘。
看來以往的經(jīng)驗(yàn)還是有點(diǎn)用的，起碼SQL執(zhí)行時(shí)間降低到了5分鐘。不過顯然不符合老外1-2分鐘出結(jié)果的要求。

好了，現(xiàn)在我必須認(rèn)真分析這個視圖，認(rèn)真分析這個執(zhí)行計(jì)劃，你可能要問我為什么一開始不認(rèn)真分析執(zhí)行計(jì)劃，只是靠經(jīng)驗(yàn)?zāi)?其實(shí)SQL調(diào)優(yōu)做多了就沒啥感覺了，有時(shí)候真的是靠感覺。

請注意觀察執(zhí)行計(jì)劃，ID=5的這一行引起了我的注意：它是NESTED LOOPS,那么它的out table是ID=18,19,20,21這些行取得的結(jié)果。
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 |

好了，現(xiàn)在來看看這個位圖索引的選擇率：

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

從統(tǒng)計(jì)信息上面看，居然只有2行，暈了，我收集過統(tǒng)計(jì)信息的哈。

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

根據(jù)SQL統(tǒng)計(jì)來看，這個位圖索引只有2個值，現(xiàn)在再去看看視圖定義：
有很多的這樣的過濾條件
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)

很明顯了啊，這里不應(yīng)該走位圖索引啊，不要告訴我你還不懂哈。
那么我unusable了這個索引，再看看執(zhí)行計(jì)劃：

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

現(xiàn)在只跑了2分鐘，好了，現(xiàn)在可以給老外交差了

我不知道以前這個SQL的執(zhí)行計(jì)劃到底是什么樣子的，不過從目前來看，原因在于CBO選擇了那個位圖索引，而選擇這個位圖索引的原因是統(tǒng)計(jì)信息不對，我又重新收集了統(tǒng)計(jì)信息，用過很多種方法，包括100%的采樣率，但是還是發(fā)現(xiàn)統(tǒng)計(jì)信息沒有一點(diǎn)改變。。。算了沒辦法只有unusable這個索引，暫時(shí)先這樣吧。

本文出自：億恩科技【mszdt.com】

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]