- 掛牌上市企業
- 60秒人工響應
- 99.99%連通率
- 7*24h人工
- 故障100倍補償
SQL調優之位圖索引統計信息出錯 |
| 發布時間: 2012/8/23 17:09:43 |
|
老外發來郵件，叫我對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,它是一個視圖，視圖定義如下： 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調優做多了就沒啥感覺了，有時候真的是靠感覺。 請注意觀察執行計劃，ID=5的這一行引起了我的注意：它是NESTED LOOPS,那么它的out table是ID=18,19,20,21這些行取得的結果。 |* 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個值，現在再去看看視圖定義： and td2.time_perd_lvl=3 同樣的，可以從filter里面看到 17 - filter("TB"."TIME_PERD_LVL"=3)
很明顯了啊，這里不應該走位圖索引啊，不要告訴我你還不懂哈。 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這個索引，暫時先這樣吧。 本文出自：億恩科技【www.ibaoshan.net】 |
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
