SQL调优之使用并行特征

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（*）
----------
   1186493Elapsed: 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_FCT                1                   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                                                                                       2215Elapsed: 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.72SQL调优之位图索引统计信息出错Oracle 10g中 system 回滚段的作用相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）