首页 / 数据库 / MySQL / 一次400行SQL的优化过程
主要环境如下:SQL> select * from v$version;BANNER
-------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production4节点 HPUX RAC OLAP 环境SQL> show parameter db_block_sizeNAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_block_size integer 16384
SQL> show parameter db_fileNAME 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,
&n
易网时代-编程资源站