SQL调优之使用并行特征

iteye_20478

于 2010-08-05 16:57:00 发布

阅读量73

点赞数

Developer 发来邮件，叫我调整下面的SQL。

注：HPUX ,8CPU，RAC 4节点，数据仓库环境

-----邮件内容--------

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;

----邮件内容-------------

通过OC得知，上面的SQL要跑40分钟左右。

SQL> select count(*) from adwu.GLOBL_DEMND_FRCST_WK_FCT; ---表GLOBL_DEMND_FRCST_WK_FCT有10亿条数据

COUNT(*)
----------
1079544821

SQL> select count(*) from adwu.prod_9005_gdf_wk_fdim;--表prod_9005_gdf_wk_fdim有1千多万的数据

COUNT(*)
----------
1186493

Elapsed: 00:00:01.20

表GLOBL_DEMND_FRCST_WK_FCT是个组合分区表，有900多个sub partition分区信息如下：

...............省略..............................................

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不是分区表

执行计划如下：

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.

请注意观察执行计划：其实这里的统计信息是不准确的，因为10亿数据表的FULL SCAN 才3255K，说明统计信息出问题了。不过凭俺SQL调优的经验，即使现在对10亿数据表再去收集统计信息，执行计划也不会变的。同样会对10表进行全表扫描。对于1千万的表，使用了2个索引，一个是主键，一个是位图索引，这里没有什么好说的。

对于这个SQL，可以在10亿上面的3个列建立组合索引，从而避免对10亿大表全表扫描，不过这样做会让导入，更新，删除变得很慢，而且也浪费空间。所以我放弃了这总方法（一般对2列建立组合索引，超过3列就。。。。。）

好了，怎么优化呢？我这里是仓库环境，10亿的那张表有900多个分区，那么你想到了什么？并行运算啊

对于仓库环境，如果表已经经过分区，那么我们可以使用并行扫描的方法来提高速度。

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

SQL> alter table adwu.GLOBL_DEMND_FRCST_WK_FCT parallel 8;

Table altered.

执行下面的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分钟，大大的超出了开发人员的预期。不过我这样做也有问题，因为我设置了degree，这个将会导致对表的查询更倾向于全表扫描，所以这里不能这么设置，可以使用HINT 提示来让优化器选择并行运算，而不是设置degree。

所以最终，让开发人员使用下面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