查看: 2888|回复: 13

[性能调整] 一次400行SQL的优化过程

[复制链接]
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
发表于 2011-7-21 22:00 | 显示全部楼层 |阅读模式
主要环境如下:

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

4节点 HPUX RAC OLAP 环境

SQL> show parameter db_block_size

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------
db_block_size                        integer                           16384
SQL> show parameter db_file

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------
db_file_multiblock_read_count        integer

ETL开发人员找我调查一个long running的JOB,该JOB已经跑了7小时了还没跑完。

那个JOB 是一个insert into ... select ..... 语句。insert 肯定不会7小时还
未完成,所以,这里主要的调整应该关注 select 部分

select部分的SQL语句如下,这是一个接近400行的SQL,大家不要头晕哈,可以直
接跳过这个SQL语句,看我下面的分析

SELECT  ACTVY_SKID,
                      FUND_SKID,
                      PRMTN_SKID,
                      PROD_SKID,
                      DATE_SKID,
                      ACCT_SKID,
                      BUS_UNIT_SKID,
                      FY_DATE_SKID,
                      ESTMT_VAR_COST_AMT,
                      ESTMT_FIXED_COST_AMT,
                      REVSD_ESTMT_VAR_COST_AMT,
                      ACTL_VAR_COST_AMT,
                      ACTL_FIXED_COST_AMT,
                      COST_PLAN_AMT,
                      COST_CMMT_AMT,
                      COST_BOOK_AMT,
                      ESTMT_COST_OVRRD_AMT,
                      LA_TOT_BOOK_AMT,
                      MANUL_COST_OVRRD_AMT,
                      ACTL_COST_AMT
               FROM   (SELECT ACTVY_SKID,
       FUND_SKID,
       PROD_SKID,
       PRMTN_SKID,
       DATE_SKID,
       ACCT_SKID,
       BUS_UNIT_SKID,
       FY_DATE_SKID,
       ESTMT_VAR_COST_AMT,
       ESTMT_FIXED_COST_AMT,
       REVSD_ESTMT_VAR_COST_AMT,
       0 as ACTL_COST_AMT,
       ACTL_VAR_COST_AMT,
       ACTL_FIXED_COST_AMT,
       MANUL_COST_OVRRD_AMT,
       ESTMT_COST_OVRRD_AMT,
       COST_BOOK_AMT,
       -- Updated by Luke for QC3369
       -- If the committed amount on Activity level <0 then return 0
       (CASE
         WHEN SUM(ESTMT_COST_OVRRD_AMT - ACTL_VAR_COST_AMT -
                  ACTL_FIXED_COST_AMT) OVER(PARTITION BY ACTVY_SKID) < 0 THEN
          0
         ELSE
          COST_CMMT_AMT
       END) AS COST_CMMT_AMT,
       -- Updated by Luke for QC3369
       (CASE
         WHEN SUM(ESTMT_COST_OVRRD_AMT - ACTL_VAR_COST_AMT -
                  ACTL_FIXED_COST_AMT) OVER(PARTITION BY ACTVY_SKID) < 0 THEN
          0
         ELSE
          COST_PLAN_AMT
       END) AS COST_PLAN_AMT,
       LA_TOT_BOOK_AMT
  FROM (SELECT ACTVY_SKID,
               FUND_SKID,
               PROD_SKID,
               PRMTN_SKID,
               DATE_SKID,
               ACCT_SKID,
               BUS_UNIT_SKID,
               FY_DATE_SKID,
               ESTMT_VAR_COST_AMT,
               ESTMT_FIXED_COST_AMT,
               REVSD_ESTMT_VAR_COST_AMT,
               ACTL_VAR_COST_AMT,
               ACTL_FIXED_COST_AMT,
               MANUL_COST_OVRRD_AMT,
               (CASE
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'E' THEN
                  ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN
                  ESTMT_FIXED_COST_AMT +
                  DECODE(REVSD_BPT_COST_AMT,
                         0,
                         REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                         REVSD_BPT_COST_AMT) --BPT Revised Cost
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN
                  MANUL_COST_OVRRD_AMT
                 WHEN ESTMT_COST_IND IS NULL THEN
                  DECODE(CORP_PRMTN_TYPE_CODE,
                         'Annual Agreement',
                         ESTMT_FIXED_COST_AMT +
                         DECODE(REVSD_BPT_COST_AMT,
                                0,
                                REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                                REVSD_BPT_COST_AMT), --BPT Revised Cost
                         ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT)
               END) AS ESTMT_COST_OVRRD_AMT,
               (ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT) AS COST_BOOK_AMT,
               DECODE(PRMTN_STTUS_CODE,
                      'Confirmed',
                      --Estimate Total Cost - Actual Cost
                      --Add the logic of Activity Stop date and Pyment allow IND
                      --For Defect 2913 Luke 2010-5-5
                      (CASE
                        WHEN (ACTVY_STOP_DATE IS NULL OR ACTVY_STOP_DATE > SYSDATE OR
                             NVL(PYMT_ALLWD_STOP_IND, 'N') = 'Y') THEN
                         (CASE
                        WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'E' THEN
                         ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT
                        WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN
                         ESTMT_FIXED_COST_AMT +
                         DECODE(REVSD_BPT_COST_AMT,
                                0,
                                REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                                REVSD_BPT_COST_AMT) --BPT Revised Cost
                        WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN
                         MANUL_COST_OVRRD_AMT
                        WHEN ESTMT_COST_IND IS NULL THEN
                         DECODE(CORP_PRMTN_TYPE_CODE,
                                'Annual Agreement',
                                ESTMT_FIXED_COST_AMT +
                                DECODE(REVSD_BPT_COST_AMT,
                                       0,
                                       REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                                       REVSD_BPT_COST_AMT), --BPT Revised Cost
                                ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT)
                      END) - (ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT)
                       ELSE 0 END), 0) AS COST_CMMT_AMT,
               (CASE
                 WHEN (PRMTN_STTUS_CODE IN ('Planned', 'Revised') AND
                      NVL(APPRV_STTUS_CODE, 'Nothing') <> 'Rejected' AND
                      --Add the logic of Activity Stop date and Pyment allow IND
                      --For Defect 2913 Luke 2010-5-5
                      (ACTVY_STOP_DATE IS NULL OR ACTVY_STOP_DATE > SYSDATE OR
                      NVL(PYMT_ALLWD_STOP_IND, 'N') = 'Y')) THEN
                  (CASE
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'E' THEN
                  ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN
                  ESTMT_FIXED_COST_AMT +
                  DECODE(REVSD_BPT_COST_AMT,
                         0,
                         REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                         REVSD_BPT_COST_AMT) --BPT Revised Cost
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN
                  MANUL_COST_OVRRD_AMT
                 WHEN ESTMT_COST_IND IS NULL THEN
                  DECODE(CORP_PRMTN_TYPE_CODE,
                         'Annual Agreement',
                         ESTMT_FIXED_COST_AMT +
                         DECODE(REVSD_BPT_COST_AMT,
                                0,
                                REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                                REVSD_BPT_COST_AMT), --BPT Revised Cost
                         ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT)
               END) - (ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT) ELSE 0 END) AS COST_PLAN_AMT,
               (CASE
                 WHEN MTH_START_DATE > TRUNC(SYSDATE, 'MM') AND
                      PRMTN_STTUS_CODE IN ('Planned', 'Confirmed', 'Revised') THEN
                  (CASE
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'E' THEN
                  ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN
                  ESTMT_FIXED_COST_AMT +
                  DECODE(REVSD_BPT_COST_AMT,
                         0,
                         REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                         REVSD_BPT_COST_AMT) --BPT Revised Cost
                 WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN
                  MANUL_COST_OVRRD_AMT
                 WHEN ESTMT_COST_IND IS NULL THEN
                  DECODE(CORP_PRMTN_TYPE_CODE,
                         'Annual Agreement',
                         ESTMT_FIXED_COST_AMT +
                         DECODE(REVSD_BPT_COST_AMT,
                                0,
                                REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost
                                REVSD_BPT_COST_AMT), --BPT Revised Cost
                         ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT)
                   END)
               WHEN MTH_START_DATE <= TRUNC(SYSDATE, 'MM') THEN
                  (ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT)
               ELSE 0 END) AS LA_TOT_BOOK_AMT
          FROM (SELECT  ACTVY_MTH_GTIN.ACTVY_SKID,
                       ACTVY_MTH_GTIN.FUND_SKID,
                       ACTVY_MTH_GTIN.PROD_SKID,
                       ACTVY_MTH_GTIN.PRMTN_SKID,
                       ACTVY_MTH_GTIN.MTH_SKID AS DATE_SKID,
                       ACTVY_MTH_GTIN.ACCT_SKID,
                       ACTVY_MTH_GTIN.BUS_UNIT_SKID,
                       ACTVY_MTH_GTIN.FY_DATE_SKID,
                       PRMTN.PRMTN_STTUS_CODE,
                       PRMTN.APPRV_STTUS_CODE,
                       ACTVY.ESTMT_COST_IND,
                       ACTVY.CORP_PRMTN_TYPE_CODE,
                       ACTVY.ACTVY_STOP_DATE,
                       ACTVY.PYMT_ALLWD_STOP_IND,
                       CAL.MTH_START_DATE,
                       ROUND(NVL(DECODE(ACTVY.COST_TYPE_CODE,
                                        '% Fund',
                                        (ACTVY_MTH_GTIN.ESTMT_VAR_COST * -- added by Rita for defect 3105 in R10
                                        ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE),
                                        DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                               'Annual Agreement',
                                               AA.ESTMT_VAR_COST_AMT,
                                               ESTMT_VAR_COST.ESTMT_VAR_COST_AMT)),
                                 0),
                             7) AS ESTMT_VAR_COST_AMT,
                       -- Modified by Simon For CR389 in R10 on 2010-3-18
                       ROUND(NVL(DECODE(ACTVY.COST_TYPE_CODE,
                                        -- % Fund
                                        '% Fund',
                                        ACTVY_MTH_GTIN.ESTMT_FIX_COST *
                                        ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,
                                        -- Fixed
                                        'Fixed',
                                        ACTVY_MTH_GTIN.ESTMT_FIX_COST *
                                        ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,
                                        -- Not % Fund or Fixed
                                        DECODE(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                                      'Annual Agreement',
                                                      SUM(NVL(AA.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID),
                                                      SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID)),
                                               0,
                                               ACTVY_MTH_GTIN.ESTMT_FIX_COST *
                                               BRAND_MTH_RATE,
                                               ACTVY_MTH_GTIN.ESTMT_FIX_COST *
                                               NVL(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                                          'Annual Agreement',
                                                          AA.ESTMT_VAR_COST_AMT,
                                                          ESTMT_VAR_COST.ESTMT_VAR_COST_AMT),
                                                   0) /
                                               DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                                      'Annual Agreement',
                                                      SUM(NVL(AA.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID),
                                                      SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID)))),
                                 0),
                             7) AS ESTMT_FIXED_COST_AMT,
                       -- Change in R10 for Revised Cost logic
                       ROUND(NVL(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                        'Annual Agreement',
                                        AA.REVSD_ESTMT_VAR_COST_AMT,
                                        REVSD_VAR_COST.REVSD_ESTMT_VAR_COST_AMT),
                                 0),
                             7) AS REVSD_ESTMT_VAR_COST_AMT,
                       ROUND(NVL(ESTMT_VAR_COST.REVSD_BPT_COST_AMT, 0), 7) AS REVSD_BPT_COST_AMT,
                       ROUND(NVL((ACTVY_MTH_GTIN.ACTL_VAR_COST *
                                 ACTVY_MTH_GTIN.ACTVY_GTIN_ACTL_WGHT_RATE),
                                 0),
                             7) AS ACTL_VAR_COST_AMT,
                       ROUND(NVL((ACTVY_MTH_GTIN.ACTL_FIX_COST *
                                 ACTVY_MTH_GTIN.ACTVY_GTIN_ACTL_WGHT_RATE),
                                 0),
                             7) AS ACTL_FIXED_COST_AMT,
                       ROUND(NVL(DECODE(ACTVY.COST_TYPE_CODE,
                                        '% Fund',
                                        ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT *
                                        ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,
                                        'Fixed',
                                        ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT *
                                        ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,
                                        DECODE(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                                      'Annual Agreement',
                                                      SUM(NVL(AA.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID),
                                                      SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID)),
                                               0,
                                               ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT *
                                               BRAND_MTH_RATE,
                                               ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT *
                                               NVL(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                                          'Annual Agreement',
                                                          AA.ESTMT_VAR_COST_AMT,
                                                          ESTMT_VAR_COST.ESTMT_VAR_COST_AMT),
                                                   0) /
                                               DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
                                                      'Annual Agreement',
                                                      SUM(NVL(AA.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID),
                                                      SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,
                                                              0))
                                                      OVER(PARTITION BY
                                                           ACTVY_MTH_GTIN.ACTVY_SKID)))),
                                 0),
                             7) AS MANUL_COST_OVRRD_AMT
                  FROM OPT_ACTVY_DIM ACTVY,
                       OPT_PRMTN_DIM PRMTN,
                       OPT_CAL_MASTR_DIM CAL,
                       (SELECT ACTVY.ACTVY_SKID,
                               ACTVY_GTIN_BRAND.ACTVY_ID,
                               ACTVY.FUND_SKID,
                               ACTVY.ACCT_PRMTN_SKID AS ACCT_SKID,
                               ACTVY_GTIN_BRAND.PROD_SKID,
                               ACTVY_GTIN_BRAND.PROD_ID,
                               ACTVY_GTIN_BRAND.PRMTN_SKID,
                               ACTVY.BUS_UNIT_SKID,
                               ACTVY_GTIN_BRAND.MTH_SKID,
                               ACTVY_GTIN_BRAND.FY_DATE_SKID,
                               ACTVY.VAR_COST_ESTMT_AMT AS ESTMT_VAR_COST,
                               ACTVY.PRDCT_FIXED_COST_AMT AS ESTMT_FIX_COST,
                               ACTVY.CALC_INDEX_NUM AS ACTL_FIX_COST,
                               ACTVY.ACTL_VAR_COST_NUM AS ACTL_VAR_COST,
                               ACTVY.ESTMT_COST_OVRRD_AMT,
                               ACTVY.MANUL_COST_OVRRD_AMT,
                               ACTVY_GTIN_BRAND.ACTVY_GTIN_ACTL_WGHT_RATE,
                               ACTVY_GTIN_BRAND.ACTVY_GTIN_ESTMT_WGHT_RATE,
                               ACTVY_GTIN_BRAND.BRAND_MTH_RATE
                          FROM OPT_ACTVY_FCT             ACTVY,
                               OPT_ACTVY_GTIN_BRAND_SFCT ACTVY_GTIN_BRAND,
                               OPT_ACCT_DIM              ACCT
                         WHERE ACTVY.ACTVY_SKID = ACTVY_GTIN_BRAND.ACTVY_SKID
                           AND ACCT.ACCT_SKID = ACTVY.ACCT_PRMTN_SKID
                           -- Optima11, B018, 9-Oct-2010, Kingham, filter out TSP account
                           AND ACCT.FUND_FRCST_MODEL_DESC not like 'TSP%') ACTVY_MTH_GTIN,
                       --Estamate variable cost aggregated to brand level
                       (SELECT  ESTMT.ACTVY_ID AS ACTVY_ID,
                               BRAND_HIER.BRAND_ID AS PROD_ID,
                               ESTMT.DATE_SKID AS DATE_SKID,
                               ESTMT.BUS_UNIT_SKID AS BUS_UNIT_SKID,
                               SUM(ESTMT.ESTMT_VAR_COST_AMT) AS ESTMT_VAR_COST_AMT,
                               SUM(ESTMT.REVSD_BPT_COST_AMT) AS REVSD_BPT_COST_AMT
                          FROM OPT_ACTVY_GTIN_ESTMT_SFCT ESTMT, -- add by rita
                               OPT_PROD_BRAND_ASSOC_DIM  BRAND_HIER,
                               CAL_MASTR_DIM             CAL
                         WHERE ESTMT.PROD_ID = BRAND_HIER.PROD_ID
                           AND ESTMT.DATE_SKID = CAL.CAL_MASTR_SKID
                           AND CAL.FISC_YR_SKID = BRAND_HIER.FY_DATE_SKID
                         GROUP BY ESTMT.ACTVY_ID,
                                  BRAND_HIER.BRAND_ID,
                                  ESTMT.DATE_SKID,
                                  ESTMT.BUS_UNIT_SKID) ESTMT_VAR_COST,
                       --Revised variable cost aggregated to brand level
                       (SELECT REVSD.ACTVY_ID AS ACTVY_ID,
                               BRAND_HIER.BRAND_ID AS PROD_ID,
                               REVSD.DATE_SKID AS DATE_SKID,
                               REVSD.BUS_UNIT_SKID AS BUS_UNIT_SKID,
                               SUM(REVSD.REVSD_ESTMT_VAR_COST_AMT) AS REVSD_ESTMT_VAR_COST_AMT
                          FROM OPT_ACTVY_GTIN_REVSD_SFCT REVSD,
                               OPT_PROD_BRAND_ASSOC_DIM  BRAND_HIER,
                               CAL_MASTR_DIM             CAL
                         WHERE REVSD.PROD_ID = BRAND_HIER.PROD_ID
                           AND REVSD.DATE_SKID = CAL.CAL_MASTR_SKID
                           AND CAL.FISC_YR_SKID = BRAND_HIER.FY_DATE_SKID
                         GROUP BY REVSD.ACTVY_ID,
                                  BRAND_HIER.BRAND_ID,
                                  REVSD.DATE_SKID,
                                  REVSD.BUS_UNIT_SKID) REVSD_VAR_COST,
                       --AA Variable Cost aggregated to Brand Level
                       (SELECT  AA.ACTVY_ID AS ACTVY_ID,
                               BRAND_HIER.BRAND_ID AS PROD_ID,
                               AA.MTH_SKID AS DATE_SKID,
                               AA.BUS_UNIT_SKID AS BUS_UNIT_SKID,
                               SUM(AA.ESTMT_VAR_COST_AMT) AS ESTMT_VAR_COST_AMT,
                               SUM(AA.REVSD_VAR_ESTMT_COST_AMT) AS REVSD_ESTMT_VAR_COST_AMT
                          FROM OPT_ACTVY_BUOM_GTIN_COST_TFADS AA,
                               OPT_PROD_BRAND_ASSOC_DIM       BRAND_HIER
                         WHERE AA.BUOM_GTIN_PROD_SKID = BRAND_HIER.PROD_SKID
                           AND BRAND_HIER.FY_DATE_SKID = AA.FY_DATE_SKID
                         GROUP BY AA.ACTVY_ID,
                                  BRAND_HIER.BRAND_ID,
                                  AA.MTH_SKID,
                                  AA.BUS_UNIT_SKID) AA
                 WHERE ACTVY_MTH_GTIN.ACTVY_ID = ESTMT_VAR_COST.ACTVY_ID(+)
                   AND ACTVY_MTH_GTIN.MTH_SKID = ESTMT_VAR_COST.DATE_SKID(+)
                   AND ACTVY_MTH_GTIN.PROD_ID = ESTMT_VAR_COST.PROD_ID(+)
                   AND ACTVY_MTH_GTIN.ACTVY_ID = REVSD_VAR_COST.ACTVY_ID(+)
                   AND ACTVY_MTH_GTIN.MTH_SKID = REVSD_VAR_COST.DATE_SKID(+)
                   AND ACTVY_MTH_GTIN.PROD_ID = REVSD_VAR_COST.PROD_ID(+)
                   AND ACTVY_MTH_GTIN.ACTVY_ID = AA.ACTVY_ID(+)
                   AND ACTVY_MTH_GTIN.MTH_SKID = AA.DATE_SKID(+)
                   AND ACTVY_MTH_GTIN.PROD_ID = AA.PROD_ID(+)
                   AND ACTVY_MTH_GTIN.ACTVY_SKID = ACTVY.ACTVY_SKID
                   AND ACTVY_MTH_GTIN.PRMTN_SKID = PRMTN.PRMTN_SKID
                   AND ACTVY_MTH_GTIN.MTH_SKID = CAL.CAL_MASTR_SKID))
                   );

该SQL执行计划如下
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 22:01 | 显示全部楼层
该SQL执行计划如下

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2005223222

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                                |     1 |   249 |  3855  (39)| 00:00:27 |       |       |
|   1 |  VIEW                                           |                                |     1 |   249 |  3855  (39)| 00:00:27 |       |       |
|   2 |   WINDOW BUFFER                                 |                                |     1 |   308 |  3855  (39)| 00:00:27 |       |       |
|   3 |    VIEW                                         |                                |     1 |   308 |  3855  (39)| 00:00:27 |       |       |
|   4 |     WINDOW SORT                                 |                                |     1 |   376 |  3855  (39)| 00:00:27 |       |       |
|   5 |      NESTED LOOPS                               |                                |       |    |       |          |       |       |
|   6 |       NESTED LOOPS                              |                                |     1 |   376 |  3854  (39)| 00:00:27 |       |       |
|   7 |        NESTED LOOPS                             |                                |     1 |   351 |  3852  (39)| 00:00:27 |       |       |
|*  8 |         HASH JOIN OUTER                         |                                |     1 |   338 |  3851  (39)| 00:00:27 |       |       |
|*  9 |          HASH JOIN OUTER                        |                                |     1 |   281 |  3536  (41)| 00:00:25 |       |       |
|* 10 |           HASH JOIN OUTER                       |                                |     1 |   237 |  3223  (43)| 00:00:23 |       |       |
|* 11 |            HASH JOIN                            |                                |     1 |   180 |  3218  (43)| 00:00:23 |       |       |
|  12 |             NESTED LOOPS                        |                                |       |    |       |          |       |       |
|  13 |              NESTED LOOPS                       |                                |     1 |   116 |   535   (7)| 00:00:04 |       |       |
|* 14 |               HASH JOIN                         |                                |     1 |    65 |   533   (7)| 00:00:04 |       |       |
|  15 |                PARTITION LIST ALL               |                                |     1 |    26 |   315   (6)| 00:00:03 |     1 |    17 |
|* 16 |                 TABLE ACCESS FULL               | OPT_ACCT_DIM                   |     1 |    26 |   315   (6)| 00:00:03 |     1 |    17 |
|  17 |                PARTITION LIST ALL               |                                |   114K|  4363K|   216   (7)| 00:00:02 |     1 |    17 |
|  18 |                 TABLE ACCESS FULL               | OPT_ACTVY_FCT                  |   114K|  4363K|   216   (7)| 00:00:02 |     1 |    17 |
|* 19 |               INDEX RANGE SCAN                  | OPT_ACTVY_DIM_PK               |     1 |    |     1   (0)| 00:00:01 |  |       |
|  20 |              TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACTVY_DIM                  |     1 |    51 |     2   (0)| 00:00:01 | ROWID | ROWID |
|  21 |             PARTITION LIST ALL                  |                                |    19M|  1212M|  2423  (45)| 00:00:17 |     1 |    17 |
|  22 |              TABLE ACCESS FULL                  | OPT_ACTVY_GTIN_BRAND_SFCT      |    19M|  1212M|  2423  (45)| 00:00:17 |     1 |    17 |
|  23 |            VIEW                                 |                                |     1 |    57 |     5  (20)| 00:00:01 |       |       |
|  24 |             HASH GROUP BY                       |                                |     1 |   108 |     5  (20)| 00:00:01 |       |       |
|  25 |              NESTED LOOPS                       |                                |       |    |       |          |       |       |
|  26 |               NESTED LOOPS                      |                                |     1 |   108 |     4   (0)| 00:00:01 |       |       |
|  27 |                TABLE ACCESS FULL                | OPT_ACTVY_BUOM_GTIN_COST_TFADS |     1 |    87 |     2   (0)| 00:00:01 |       |       |
|* 28 |                INDEX RANGE SCAN                 | OPT_PROD_BRAND_ASSOC_DIM_PK    |     1 |    |     2   (0)| 00:00:01 |  |       |
|  29 |               TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PROD_BRAND_ASSOC_DIM       |     1 |    21 |     2   (0)| 00:00:01 | ROWID | ROWID |
|  30 |           VIEW                                  |                                |   718 | 31592 |   313  (17)| 00:00:03 |       |       |
|  31 |            HASH GROUP BY                        |                                |   718 | 51696 |   313  (17)| 00:00:03 |       |       |
|* 32 |             HASH JOIN                           |                                |   718 | 51696 |   311  (17)| 00:00:03 |       |       |
|* 33 |              HASH JOIN                          |                                |   872 | 40112 |   211   (8)| 00:00:02 |       |       |
|  34 |               PARTITION LIST ALL                |                                |   872 | 31392 |     3   (0)| 00:00:01 |     1 |    17 |
|  35 |                TABLE ACCESS FULL                | OPT_ACTVY_GTIN_REVSD_SFCT      |   872 | 31392 |     3   (0)| 00:00:01 |     1 |    17 |
|  36 |               TABLE ACCESS FULL                 | OPT_CAL_MASTR_DIM              | 36826 |   359K|   207   (8)| 00:00:02 |       |       |
|  37 |              PARTITION LIST ALL                 |                                |   671K|    16M|    91  (28)| 00:00:01 |     1 |    17 |
|  38 |               TABLE ACCESS FULL                 | OPT_PROD_BRAND_ASSOC_DIM       |   671K|    16M|    91  (28)| 00:00:01 |     1 |    17 |
|  39 |          VIEW                                   |                                |  6174 |   343K|   315  (17)| 00:00:03 |       |       |
|  40 |           HASH GROUP BY                         |                                |  6174 |   446K|   315  (17)| 00:00:03 |       |       |
|* 41 |            HASH JOIN                            |                                |  6174 |   446K|   313  (17)| 00:00:03 |       |       |
|* 42 |             HASH JOIN                           |                                |  8998 |   421K|   213   (8)| 00:00:02 |       |       |
|  43 |              PARTITION LIST ALL                 |                                |  8998 |   333K|     4   (0)| 00:00:01 |     1 |    17 |
|  44 |               TABLE ACCESS FULL                 | OPT_ACTVY_GTIN_ESTMT_SFCT      |  8998 |   333K|     4   (0)| 00:00:01 |     1 |    17 |
|  45 |              TABLE ACCESS FULL                  | OPT_CAL_MASTR_DIM              | 36826 |   359K|   207   (8)| 00:00:02 |       |       |
|  46 |             PARTITION LIST ALL                  |                                |   671K|    16M|    91  (28)| 00:00:01 |     1 |    17 |
|  47 |              TABLE ACCESS FULL                  | OPT_PROD_BRAND_ASSOC_DIM       |   671K|    16M|    91  (28)| 00:00:01 |     1 |    17 |
|  48 |         TABLE ACCESS BY INDEX ROWID             | OPT_CAL_MASTR_DIM              |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|* 49 |          INDEX UNIQUE SCAN                      | OPT_CAL_MASTR_DIM_PK           |     1 |    |     0   (0)| 00:00:01 |  |       |
|* 50 |        INDEX RANGE SCAN                         | OPT_PRMTN_DIM_PK               |     1 |    |     1   (0)| 00:00:01 |  |       |
|  51 |       TABLE ACCESS BY GLOBAL INDEX ROWID        | OPT_PRMTN_DIM                  |     1 |    25 |     2   (0)| 00:00:01 | ROWID | ROWID |
--------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="ESTMT_VAR_COST"."ACTVY_ID"(+) AND
              "ACTVY_GTIN_BRAND"."MTH_SKID"="ESTMT_VAR_COST"."DATE_SKID"(+) AND "ACTVY_GTIN_BRAND"."PROD_ID"="ESTMT_VAR_COST"."PROD_ID"(+))
   9 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="REVSD_VAR_COST"."ACTVY_ID"(+) AND
              "ACTVY_GTIN_BRAND"."MTH_SKID"="REVSD_VAR_COST"."DATE_SKID"(+) AND "ACTVY_GTIN_BRAND"."PROD_ID"="REVSD_VAR_COST"."PROD_ID"(+))
  10 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="AA"."ACTVY_ID"(+) AND "ACTVY_GTIN_BRAND"."MTH_SKID"="AA"."DATE_SKID"(+) AND
              "ACTVY_GTIN_BRAND"."PROD_ID"="AA"."PROD_ID"(+))
  11 - access("ACTVY"."ACTVY_SKID"="ACTVY_GTIN_BRAND"."ACTVY_SKID")
  14 - access("ACCT"."ACCT_SKID"="ACTVY"."ACCT_PRMTN_SKID")
  16 - filter("ACCT"."FUND_FRCST_MODEL_DESC" NOT LIKE 'TSP%')
  19 - access("ACTVY"."ACTVY_SKID"="ACTVY"."ACTVY_SKID")
  28 - access("AA"."BUOM_GTIN_PROD_SKID"="BRAND_HIER"."PROD_SKID" AND "BRAND_HIER"."FY_DATE_SKID"="AA"."FY_DATE_SKID")
  32 - access("REVSD"."PROD_ID"="BRAND_HIER"."PROD_ID" AND "CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")
  33 - access("REVSD"."DATE_SKID"="CAL"."CAL_MASTR_SKID")
  41 - access("ESTMT"."PROD_ID"="BRAND_HIER"."PROD_ID" AND "CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")
  42 - access("ESTMT"."DATE_SKID"="CAL"."CAL_MASTR_SKID")
  49 - access("ACTVY_GTIN_BRAND"."MTH_SKID"="CAL"."CAL_MASTR_SKID")
  50 - access("ACTVY_GTIN_BRAND"."PRMTN_SKID"="PRMTN"."PRMTN_SKID")

79 rows selected.

Elapsed: 00:00:03.45

由于这个SQL语句的执行计划太复杂,所以这里暂不关注执行计划,我们来监控该SQL语句的等待事件

我手工运行该SQL,监控等待事件,发现在等待 direct path write temp

SQL> select username,inst_id,sid,serial#,event,p1,p2,p3,sql_id,sql_child_number
  2  from gv$session where username='ADWU_OPTIMA_LA11' and osuser='luobi';

USERNAME                INST_ID        SID    SERIAL# EVENT                                  P1         P2         P3 SQL_ID        SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11              2       4754      10050 direct path write temp              20025     857328          7 6qsuc8mafy20m                0

SQL> /

USERNAME                INST_ID        SID    SERIAL# EVENT                                  P1         P2         P3 SQL_ID        SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11              2       4754      10050 direct path write temp              20025     406768          7 6qsuc8mafy20m                0
SQL> /

USERNAME                INST_ID        SID    SERIAL# EVENT                                  P1         P2         P3 SQL_ID        SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11              2       4754      10050 direct path write temp              20007    2849264          7 6qsuc8mafy20m                0

SQL> /

USERNAME                INST_ID        SID    SERIAL# EVENT                                  P1         P2         P3 SQL_ID        SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11              2       4754      10050 direct path write temp              20007     115341          7 6qsuc8mafy20m                0

SQL> /

USERNAME                INST_ID        SID    SERIAL# EVENT                                  P1         P2         P3 SQL_ID        SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11              2       4754      10050 direct path write temp              20007      81029          7 6qsuc8mafy20m                0

我又查询这个SESSION到底是HASH JOIN 暂用了 temp tablespace 还是 sort 占用了temp tablespace

SQL> select a.username,a.inst_id, a.sid, a.serial#, a.machine,a.sql_id,b.tablespace, b.blocks*
  2  (select value from v$parameter where name='db_block_size')/1024/1024 "Size(M)",b.segtype
  3  from gv$session a, gv$tempseg_usage b where a.inst_id=b.inst_id and a.saddr = b.session_addr
  4  and a.inst_id=2 and a.sid=4754
  5  ;

USERNAME                INST_ID        SID    SERIAL# MACHINE              SQL_ID        TABLESPACE                         Size(M) SEGTYPE
-------------------- ---------- ---------- ---------- -------------------- ------------- ------------------------------- ---------- ---------
ADWU_OPTIMA_LA11              2       4754      10050 ASIAPACIFIC\BLUO7    6qsuc8mafy20m TEMP                                     1 DATA
ADWU_OPTIMA_LA11              2       4754      10050 ASIAPACIFIC\BLUO7    6qsuc8mafy20m TEMP                                     1 LOB_DATA
ADWU_OPTIMA_LA11              2       4754      10050 ASIAPACIFIC\BLUO7    6qsuc8mafy20m TEMP                                     1 INDEX
ADWU_OPTIMA_LA11              2       4754      10050 ASIAPACIFIC\BLUO7    6qsuc8mafy20m TEMP                                     1 LOB_DATA
ADWU_OPTIMA_LA11              2       4754      10050 ASIAPACIFIC\BLUO7    6qsuc8mafy20m TEMP                                  3304 HASH

那么根据查询,这个会话在等磁盘HASH操作,ETL开发人员跟我报告的时候,我用TOAD监控了一下,
那个job确实在等待 direct patch write temp 操作。

在OLAP中,由于数据量巨大,业务逻辑复杂,有时候确实无法避免磁盘HASH,磁盘SORT等操作

大家请注意观察p3, p3=7 也就是说一次 temp 写入只能写入7个block

好的,那么我现在kill 这个SESSION,我设置workarea 手工管理

SQL> alter session set workarea_size_policy = manual;

Session altered.

SQL> alter session set hash_area_size = 2100000000;

Session altered.

SQL> alter session set sort_area_size = 2100000000;

Session altered.

Oracle有个限制,每个进程分配的最大内存不能够操作2G 。在workarea自动管理中,每个进程的work area不能超过1G

所以当你尝试分配2G的hash_area给 这个进程,会报错

SQL> alter session set hash_area_size = 2147483648;
alter session set hash_area_size = 2147483648
                                   *
ERROR at line 1:
ORA-02017: integer value required


SQL> alter session set hash_area_size = 2147483647;

Session altered.

好了 关于此话题,就到此结束,本人博客前面提到过这个问题,我们再来运行一下这个SQL,并且监控等待事件

SQL> select username,inst_id,sid,serial#,event,p1,p2,p3,sql_id,sql_child_number
  2  from gv$session where username='ADWU_OPTIMA_LA11' and osuser='luobi';

USERNAME                INST_ID        SID    SERIAL# EVENT                                  P1         P2         P3 SQL_ID        SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11              2       4885      11759 direct path write temp              20012      71053         64 6qsuc8mafy20m                1

请注意观察 p3 ,当设置 workarea 手工管理的时候,一次能写入64个block,相比以前的 7个block来说写入速度加快9倍

那么现在大家也该明白了,这个SQL的主要性能问题就是在于在 在workarea 自动管理模式下磁盘HASH 的时候一次只能写入7个block
而设置workarea 手工管理,可以让磁盘HASH 一次写入64个block。我现在还没搞明白为什么一次 磁盘HASH只能写入 7个block
我不是SYSDBA,不能做详细测试,关于这个问题就暂时到此为止

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 22:01 | 显示全部楼层
其实这个SQL不光有 磁盘hash 这个问题存在,它的执行计划也有问题的

大家请看执行计划中ID=13 到 ID=19 的步骤

|  13 |              NESTED LOOPS                       |                                |     1 |   116 |   535   (7)| 00:00:04 |       |       |
|* 14 |               HASH JOIN                         |                                |     1 |    65 |   533   (7)| 00:00:04 |       |       |
|  15 |                PARTITION LIST ALL               |                                |     1 |    26 |   315   (6)| 00:00:03 |     1 |    17 |
|* 16 |                 TABLE ACCESS FULL               | OPT_ACCT_DIM                   |     1 |    26 |   315   (6)| 00:00:03 |     1 |    17 |
|  17 |                PARTITION LIST ALL               |                                |   114K|  4363K|   216   (7)| 00:00:02 |     1 |    17 |
|  18 |                 TABLE ACCESS FULL               | OPT_ACTVY_FCT                  |   114K|  4363K|   216   (7)| 00:00:02 |     1 |    17 |
|* 19 |               INDEX RANGE SCAN                  | OPT_ACTVY_DIM_PK               |     1 |    |     1   (0)| 00:00:01 |  |       |

首先 是 OPT_ACCT_DIM 与  OPT_ACTVY_FCT HASH 连接,然后 作为一个嵌套循环的驱动行源,大家请看
这个HASH 连接, CBO认为它只返回1行数据, 为什么返回一行数据呢,原因在于 Oracle认为 扫描 OPT_ACCT_DIM只会返回1行数据
那么我来查询一下 扫描 OPT_ACCT_DIM 要返回多少数据

SQL> select count(*) from OPT_ACCT_DIM ;

  COUNT(*)
----------
     94398

Elapsed: 00:00:01.37
SQL> select count(*) from OPT_ACTVY_FCT;

  COUNT(*)
----------
    114066

很明显了,OPT_ACCT_DIM表的统计信息没收集,而OPT_ACTVY_FCT的统计信息是对的,于是我马上对OPT_ACCT_DIM收集统计信息

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_la11',
  3  tabname => 'OPT_ACCT_DIM',
  4  estimate_percent => 30,
  5  method_opt=>'for all columns size auto',
  6  degree => DBMS_STATS.AUTO_DEGREE,
  7  cascade=>TRUE
  8  );
  9  END;
10  /

PL/SQL procedure successfully completed.


现在来看一下执行计划

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 183294992

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                                |    19M|  4718M|       |   848K  (2)| 01:37:06 |       |       |
|   1 |  VIEW                                          |                                |    19M|  4718M|       |   848K  (2)| 01:37:06 |       |       |
|   2 |   WINDOW BUFFER                                |                                |    19M|  5836M|       |   848K  (2)| 01:37:06 |       |       |
|   3 |    VIEW                                        |                                |    19M|  5836M|       |   848K  (2)| 01:37:06 |       |       |
|   4 |     WINDOW SORT                                |                                |    19M|  7125M|  7392M|   848K  (2)| 01:37:06 |       |       |
|*  5 |      HASH JOIN                                 |                                |    19M|  7125M|       | 28490  (12)| 00:03:16 |       |       |
|   6 |       PARTITION LIST ALL                       |                                | 37880 |   924K|       |   407   (3)| 00:00:03 |     1 |    17 |
|   7 |        TABLE ACCESS FULL                       | OPT_PRMTN_DIM                  | 37880 |   924K|       |   407   (3)| 00:00:03 |     1 |    17 |
|*  8 |       HASH JOIN                                |                                |    19M|  6651M|       | 27822  (11)| 00:03:12 |       |       |
|   9 |        TABLE ACCESS FULL                       | OPT_CAL_MASTR_DIM              | 36826 |   467K|       |   200   (4)| 00:00:02 |       |       |
|* 10 |        HASH JOIN RIGHT OUTER                   |                                |    19M|  6405M|       | 27362  (10)| 00:03:08 |       |       |
|  11 |         VIEW                                   |                                |  6174 |   343K|       |   315  (17)| 00:00:03 |       |       |
|  12 |          HASH GROUP BY                         |                                |  6174 |   446K|       |   315  (17)| 00:00:03 |       |       |
|* 13 |           HASH JOIN                            |                                |  6174 |   446K|       |   313  (17)| 00:00:03 |       |       |
|* 14 |            HASH JOIN                           |                                |  8998 |   421K|       |   213   (8)| 00:00:02 |       |       |
|  15 |             PARTITION LIST ALL                 |                                |  8998 |   333K|       |     4   (0)| 00:00:01 |     1 |    17 |
|  16 |              TABLE ACCESS FULL                 | OPT_ACTVY_GTIN_ESTMT_SFCT      |  8998 |   333K|       |     4   (0)| 00:00:01 |     1 |    17 |
|  17 |             TABLE ACCESS FULL                  | OPT_CAL_MASTR_DIM              | 36826 |   359K|       |   207   (8)| 00:00:02 |       |       |
|  18 |            PARTITION LIST ALL                  |                                |   671K|    16M|       |    91  (28)| 00:00:01 |     1 |    17 |
|  19 |             TABLE ACCESS FULL                  | OPT_PROD_BRAND_ASSOC_DIM       |   671K|    16M|       |    91  (28)| 00:00:01 |     1 |    17 |
|* 20 |         HASH JOIN RIGHT OUTER                  |                                |    19M|  5325M|       | 26787   (9)| 00:03:05 |       |       |
|  21 |          VIEW                                  |                                |   718 | 31592 |       |   313  (17)| 00:00:03 |       |       |
|  22 |           HASH GROUP BY                        |                                |   718 | 51696 |       |   313  (17)| 00:00:03 |       |       |
|* 23 |            HASH JOIN                           |                                |   718 | 51696 |       |   311  (17)| 00:00:03 |       |       |
|* 24 |             HASH JOIN                          |                                |   872 | 40112 |       |   211   (8)| 00:00:02 |       |       |
|  25 |              PARTITION LIST ALL                |                                |   872 | 31392 |       |     3   (0)| 00:00:01 |     1 |    17 |
|  26 |               TABLE ACCESS FULL                | OPT_ACTVY_GTIN_REVSD_SFCT      |   872 | 31392 |       |     3   (0)| 00:00:01 |     1 |    17 |
|  27 |              TABLE ACCESS FULL                 | OPT_CAL_MASTR_DIM              | 36826 |   359K|       |   207   (8)| 00:00:02 |       |       |
|  28 |             PARTITION LIST ALL                 |                                |   671K|    16M|       |    91  (28)| 00:00:01 |     1 |    17 |
|  29 |              TABLE ACCESS FULL                 | OPT_PROD_BRAND_ASSOC_DIM       |   671K|    16M|       |    91  (28)| 00:00:01 |     1 |    17 |
|* 30 |          HASH JOIN RIGHT OUTER                 |                                |    19M|  4491M|       | 26215   (8)| 00:03:01 |       |       |
|  31 |           VIEW                                 |                                |     1 |    57 |       |     5  (20)| 00:00:01 |       |       |
|  32 |            HASH GROUP BY                       |                                |     1 |   108 |       |     5  (20)| 00:00:01 |       |       |
|  33 |             NESTED LOOPS                       |                                |       |    |  |            |          |       |       |
|  34 |              NESTED LOOPS                      |                                |     1 |   108 |       |     4   (0)| 00:00:01 |       |       |
|  35 |               TABLE ACCESS FULL                | OPT_ACTVY_BUOM_GTIN_COST_TFADS |     1 |    87 |       |     2   (0)| 00:00:01 |       |       |
|* 36 |               INDEX RANGE SCAN                 | OPT_PROD_BRAND_ASSOC_DIM_PK    |     1 |    |  |     2   (0)| 00:00:01 |       |       |
|  37 |              TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PROD_BRAND_ASSOC_DIM       |     1 |    21 |       |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 38 |           HASH JOIN                            |                                |    19M|  3411M|    14M| 25950   (7)| 00:02:59 |       |       |
|* 39 |            HASH JOIN                           |                                |   114K|    12M|  7104K|  1302   (6)| 00:00:09 |       |       |
|  40 |             PARTITION LIST ALL                 |                                |   115K|  5745K|       |   394   (7)| 00:00:03 |     1 |    17 |
|  41 |              TABLE ACCESS FULL                 | OPT_ACTVY_DIM                  |   115K|  5745K|       |   394   (7)| 00:00:03 |     1 |    17 |
|* 42 |             HASH JOIN                          |                                |   114K|  7273K|  3520K|   672   (6)| 00:00:05 |       |       |
|  43 |              PARTITION LIST ALL                |                                | 94478 |  2398K|       |   315   (6)| 00:00:03 |     1 |    17 |
|* 44 |               TABLE ACCESS FULL                | OPT_ACCT_DIM                   | 94478 |  2398K|       |   315   (6)| 00:00:03 |     1 |    17 |
|  45 |              PARTITION LIST ALL                |                                |   114K|  4363K|       |   216   (7)| 00:00:02 |     1 |    17 |
|  46 |               TABLE ACCESS FULL                | OPT_ACTVY_FCT                  |   114K|  4363K|       |   216   (7)| 00:00:02 |     1 |    17 |
|  47 |            PARTITION LIST ALL                  |                                |    19M|  1212M|       |  2423  (45)| 00:00:17 |     1 |    17 |
|  48 |             TABLE ACCESS FULL                  | OPT_ACTVY_GTIN_BRAND_SFCT      |    19M|  1212M|       |  2423  (45)| 00:00:17 |     1 |    17 |
---------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ACTVY_GTIN_BRAND"."PRMTN_SKID"="PRMTN"."PRMTN_SKID")
   8 - access("ACTVY_GTIN_BRAND"."MTH_SKID"="CAL"."CAL_MASTR_SKID")
  10 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="ESTMT_VAR_COST"."ACTVY_ID"(+) AND "ACTVY_GTIN_BRAND"."MTH_SKID"="ESTMT_VAR_COST"."DATE_SKID"(+) AND
              "ACTVY_GTIN_BRAND"."PROD_ID"="ESTMT_VAR_COST"."PROD_ID"(+))
  13 - access("ESTMT"."PROD_ID"="BRAND_HIER"."PROD_ID" AND "CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")
  14 - access("ESTMT"."DATE_SKID"="CAL"."CAL_MASTR_SKID")
  20 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="REVSD_VAR_COST"."ACTVY_ID"(+) AND "ACTVY_GTIN_BRAND"."MTH_SKID"="REVSD_VAR_COST"."DATE_SKID"(+) AND
              "ACTVY_GTIN_BRAND"."PROD_ID"="REVSD_VAR_COST"."PROD_ID"(+))
  23 - access("REVSD"."PROD_ID"="BRAND_HIER"."PROD_ID" AND "CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")
  24 - access("REVSD"."DATE_SKID"="CAL"."CAL_MASTR_SKID")
  30 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="AA"."ACTVY_ID"(+) AND "ACTVY_GTIN_BRAND"."MTH_SKID"="AA"."DATE_SKID"(+) AND
              "ACTVY_GTIN_BRAND"."PROD_ID"="AA"."PROD_ID"(+))
  36 - access("AA"."BUOM_GTIN_PROD_SKID"="BRAND_HIER"."PROD_SKID" AND "BRAND_HIER"."FY_DATE_SKID"="AA"."FY_DATE_SKID")
  38 - access("ACTVY"."ACTVY_SKID"="ACTVY_GTIN_BRAND"."ACTVY_SKID")
  39 - access("ACTVY"."ACTVY_SKID"="ACTVY"."ACTVY_SKID")
  42 - access("ACCT"."ACCT_SKID"="ACTVY"."ACCT_PRMTN_SKID")
  44 - filter("ACCT"."FUND_FRCST_MODEL_DESC" NOT LIKE 'TSP%')

76 rows selected.

Elapsed: 00:00:04.28


我发现绝大多数表与表之间的连接都走了全表扫描以及HASH 连接,但是请看Id=35这一步
全表扫描 居然CBO也认为只返回 一行

|  34 |              NESTED LOOPS                      |                                |     1 |   108 |       |     4   (0)| 00:00:01 |       |       |
|  35 |               TABLE ACCESS FULL                | OPT_ACTVY_BUOM_GTIN_COST_TFADS |     1 |    87 |       |     2   (0)| 00:00:01 |       |       |
|* 36 |               INDEX RANGE SCAN                 | OPT_PROD_BRAND_ASSOC_DIM_PK    |     1 |    |  |     2   (0)| 00:00:01 |       |       |
|  37 |              TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PROD_BRAND_ASSOC_DIM       |     1 |    21 |       |     2   (0)| 00:00:01 | ROWID | ROWID |

好的, 我检查一下 OPT_ACTVY_BUOM_GTIN_COST_TFADS  统计信息是否过期

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select owner || '.' || table_name name , object_type,stale_stats,last_analyzed from dba_tab_statistics
  2  where owner=upper('adwu_optima_la11') and table_name='OPT_ACTVY_BUOM_GTIN_COST_TFADS';

NAME                                               OBJECT_TYPE          STALE_STATS          LAST_ANALYZED
-------------------------------------------------- -------------------- -------------------- ------------------
ADWU_OPTIMA_LA11.OPT_ACTVY_BUOM_GTIN_COST_TFADS    TABLE                NO                   03-DEC-10

统计信息是没过期的

好的,我现在再RUN一次这个SQL

先跑的是 没有并行的SQL 手动设置 workarea
后炮的是 自动workarea


经过测试 手工设置workarea的SQL 只需要50分钟左右就能完成,而自动的workarea管理的SQL 还在等待direct path write temp


6889440 rows selected.

Elapsed: 00:56:36.08

到此,这个400行的SQL优化完毕。

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 22:02 | 显示全部楼层
最后UPDATE, 那个磁盘 hash, SORT 一次write 7个block最终发现是 一个IO BUG, 具体就不说了

使用道具 举报

回复
论坛徽章:
20
2011新春纪念徽章
日期:2011-01-04 10:26:432014年新春福章
日期:2014-02-18 16:44:082013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:25:51灰彻蛋
日期:2011-08-23 16:25:29ITPUB官方微博粉丝徽章
日期:2011-06-30 10:17:04茶鸡蛋
日期:2011-06-25 10:32:312011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-02-18 11:42:502011新春纪念徽章
日期:2011-02-18 11:42:50
发表于 2011-7-21 22:27 | 显示全部楼层
我地天啊,400行的SQL - -。。

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 22:30 | 显示全部楼层

回复 #5 colin_cat 的帖子

做过 一个 800行的SQL,半个小时搞定了 呵呵 做多了 没办法

使用道具 举报

回复
论坛徽章:
5
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:51灰彻蛋
日期:2012-12-11 13:45:29茶鸡蛋
日期:2012-12-30 23:24:09咸鸭蛋
日期:2013-01-18 11:44:13咸鸭蛋
日期:2013-02-20 10:09:54
发表于 2011-7-22 08:46 | 显示全部楼层
楼主厉害啊!

使用道具 举报

回复
认证徽章
论坛徽章:
41
ITPUB季度 技术新星
日期:2012-05-22 15:10:11祖母绿
日期:2013-09-13 21:16:10蓝锆石
日期:2013-09-13 21:15:34海蓝宝石
日期:2013-09-13 21:13:45最佳人气徽章
日期:2012-03-13 17:39:18优秀写手
日期:2013-12-18 09:29:11ITPUB社区12周年站庆徽章
日期:2013-10-17 13:56:592013年新春福章
日期:2013-02-25 14:51:24玉石琵琶
日期:2012-02-21 15:04:382011新春纪念徽章
日期:2011-02-18 11:43:35
发表于 2011-7-22 09:10 | 显示全部楼层
写过3k行的,报表sql

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2011-7-22 09:18 | 显示全部楼层
nice job

使用道具 举报

回复
论坛徽章:
3
复活蛋
日期:2011-09-09 11:11:31ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292012新春纪念徽章
日期:2012-01-04 11:57:56
发表于 2011-7-22 09:19 | 显示全部楼层
還不到優化sql 那個級別  慢慢來,學習 .  
不過現在看到sql 上了幾十行, 頭就有點暈暈的感覺....

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表