|
Developer 發(fā)來郵件，叫我調(diào)整下面的SQL。
注：HPUX ,8CPU，RAC 4節(jié)點，數(shù)據(jù)倉庫環(huán)境
-
-----郵件內(nèi)容--------
Hi Robinson,
Could you take a look at the SQL below? It runs very slowly.
select b.prod_4_id, a.SRCE_REGN_ID, count(1)
from adwu.GLOBL_DEMND_FRCST_WK_FCT a, adwu.prod_9005_gdf_wk_fdim b
where a.prod_skid = b.prod_skid
and b.prod_4_id in
('1105060745', '1105060767', '1106406452', '1106540881')
and ETL_RUN_ID = '304898'
group by b.prod_4_id, a.SRCE_REGN_ID;
----郵件內(nèi)容-------------
通過OC得知，上面的SQL要跑40分鐘左右。
SQL> select count(*) from adwu.GLOBL_DEMND_FRCST_WK_FCT; ---表GLOBL_DEMND_FRCST_WK_FCT有10億條數(shù)據(jù)
COUNT(*)
----------
1079544821
SQL> select count(*) from adwu.prod_9005_gdf_wk_fdim;--表prod_9005_gdf_wk_fdim有1千多萬的數(shù)據(jù)
COUNT(*)
----------
1186493
Elapsed: 00:00:01.20
表GLOBL_DEMND_FRCST_WK_FCT是個 組合分區(qū)表，有900多個sub partition分區(qū)信息如下：
...............省略..............................................
TABLESPACE "DEM_PLAN01M"
PARTITION BY RANGE ("DAY_SKID")
SUBPARTITION BY LIST ("SRCE_REGN_ID")
SUBPARTITION TEMPLATE (
SUBPARTITION "NA" values ( 'NA' ),
SUBPARTITION "LA" values ( 'LA' ),
SUBPARTITION "WE" values ( 'WE' ),
SUBPARTITION "CE" values ( 'CE' ),
SUBPARTITION "GC" values ( 'GC' ),
SUBPARTITION "NE" values ( 'NE' ),
SUBPARTITION "AA" values ( 'AA' ),
SUBPARTITION "GL" values ( 'GL' ) )
PARTITION "P2008052" VALUES LESS THAN (
.................省略..............................................
表prod_9005_gdf_wk_fdim不是分區(qū)表
執(zhí)行計劃如下：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 453637057
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 713 | 175K (12)| 00:25:26 | | |
| 1 | SORT GROUP BY | | 23 | 713 | 175K (12)| 00:25:26 | | |
|* 2 | HASH JOIN | | 2212K| 65M| 174K (11)| 00:25:25 | | |
|* 3 | VIEW | index$_join$_002 | 23153 | 384K| 2190 (2)| 00:00:20 | | |
|* 4 | HASH JOIN | | | | | | | |
| 5 | INLIST ITERATOR | | | | | | | |
| 6 | BITMAP CONVERSION TO ROWIDS| | 23153 | 384K| 8 (0)| 00:00:01 | | |
|* 7 | BITMAP INDEX SINGLE VALUE | PROD_9005_GDF_WK_FDIM_BX16 | | | | | | |
| 8 | INDEX FAST FULL SCAN | PROD_9005_GDF_WK_FDIM_PK | 23153 | 384K| 2180 (2)| 00:00:19 | | |
| 9 | PARTITION RANGE ALL | | 3255K| 43M| 172K (12)| 00:25:05 | 1 | 119 |
| 10 | PARTITION LIST ALL | | 3255K| 43M| 172K (12)| 00:25:05 | 1 | 8 |
|* 11 | TABLE ACCESS FULL | GLOBL_DEMND_FRCST_WK_FCT | 3255K| 43M| 172K (12)| 00:25:05 | 1 | 952 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."PROD_SKID"="B"."PROD_SKID")
3 - filter("B"."PROD_4_ID"='1105060745' OR "B"."PROD_4_ID"='1105060767' OR "B"."PROD_4_ID"='1106406452' OR
"B"."PROD_4_ID"='1106540881')
4 - access(ROWID=ROWID)
7 - access("B"."PROD_4_ID"='1105060745' OR "B"."PROD_4_ID"='1105060767' OR "B"."PROD_4_ID"='1106406452' OR
"B"."PROD_4_ID"='1106540881')
11 - filter("ETL_RUN_ID"=304898)
29 rows selected.
請注意觀察執(zhí)行計劃：其實這里的統(tǒng)計信息是不準(zhǔn)確的，因為10億數(shù)據(jù)表的FULL SCAN 才3255K，說明統(tǒng)計信息出問題了。不過憑俺SQL調(diào)優(yōu)的經(jīng)驗，即使現(xiàn)在對10億數(shù)據(jù)表再去收集統(tǒng)計信息，執(zhí)行計劃也不會變的。同樣會對10表進(jìn)行全表掃描。對于1千萬的表，使用了2個索引，一個是主鍵，一個是位圖索引，這里沒有什么好說的。
對于這個SQL，可以在10億上面的3個列建立組合索引，從而避免對10億大表全表掃描，不過這樣做會讓導(dǎo)入，更新，刪除變得很慢，而且也浪費空間。所以我放棄了這總方法（一般對2列建立組合索引，超過3列就。。。。。）
好了，怎么優(yōu)化呢？我這里是倉庫環(huán)境，10億的那張表有900多個分區(qū)，那么你想到了什么？并行運算啊
對于倉庫環(huán)境，如果表已經(jīng)經(jīng)過分區(qū)，那么我們可以使用并行掃描的方法來提高速度。
SQL> select table_name,degree,instances,status from dba_tables where
2 owner=upper('&owner') and table_name=upper('&table_name');
Enter value for owner: ADWU
Enter value for table_name: GLOBL_DEMND_FRCST_WK_FCT
old 2: owner=upper('&owner') and table_name=upper('&table_name')
new 2: owner=upper('ADWU') and table_name=upper('GLOBL_DEMND_FRCST_WK_FCT')
TABLE_NAME DEGREE INSTANCES
------------------------------ -------------------- --------------------------
GLOBL_DEMND_FRCST_WK_FCT 1 1
SQL> alter table adwu.GLOBL_DEMND_FRCST_WK_FCT parallel 8;
Table altered.
執(zhí)行下面的SQL
SQL> select b.prod_4_id, a.SRCE_REGN_ID, count(1)
2 from adwu.GLOBL_DEMND_FRCST_WK_FCT a, adwu.prod_9005_gdf_wk_fdim b
3 where a.prod_skid = b.prod_skid
4 and b.prod_4_id in
5 ('1105060745', '1105060767', '1106406452', '1106540881')
6 and ETL_RUN_ID = '304898'
7 group by b.prod_4_id, a.SRCE_REGN_ID
8 ;
PROD_4_ID SRCE_REGN_ID COUNT(1)
--------------------------------------------- ------------------------------------------------------------------------------------------ ----------
1105060745 GL 11628
1106406452 GL 97529
1105060767 GL 2215
Elapsed: 00:04:10.14
這里，這個查詢只花了4分鐘，大大的超出了開發(fā)人員的預(yù)期。不過我這樣做也有問題，因為我設(shè)置了degree，這個將會導(dǎo)致對表的查詢更傾向于全表掃描，所以這里不能這么設(shè)置，可以使用HINT 提示來讓優(yōu)化器選擇并行運算，而不是設(shè)置degree。
所以最終，讓開發(fā)人員使用下面SQL：
SQL> Select /*+ parallel(a,8) */ b.prod_4_id, a.SRCE_REGN_ID, count(1)
2 from adwu.GLOBL_DEMND_FRCST_WK_FCT a, adwu.prod_9005_gdf_wk_fdim b
3 where a.prod_skid = b.prod_skid
4 and b.prod_4_id in
5 ('1105060745', '1105060767', '1106406452', '1106540881')
6 and ETL_RUN_ID = '304898'
7 group by b.prod_4_id, a.SRCE_REGN_ID;
PROD_4_ID SRCE_REGN_ID COUNT(1)
--------------------------------------------- ------------------------------------------------------------------------------------------ -------
1105060745 GL 11628
1105060767 GL 2215
1106406452 GL 97529
Elapsed: 00:04:39.72
本文出自：億恩科技【www.23lll.com】
服務(wù)器租用/服務(wù)器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]
|
0371-60135900
京公網(wǎng)安備41019702002023號
香港服務(wù)器租用
美國服務(wù)器租用
電信骨干機房
聯(lián)通骨干機房
