Home » RDBMS Server » Performance Tuning » improve sql performance (long sql here) (11g)
 improve sql performance (long sql here) [message #649051] |
Thu, 10 March 2016 21:30  |
 |
marzbuzz
Messages: 10
Registered: March 2016
|
Junior Member |
|
|
Gurus,
I'm learning sql tuning and I'm trying to improve the performance of the following sql. Right now, it took about 15 seconds to run and we want to cut it to less than 5 seconds. Attached please find execution plan and TKPROF trace output(explain_plan_and_sql_trace.txt).
Thanks..
WITH "Rpt"
AS ( SELECT LIM.COMPANY_CD,
LIM.COMPANY_NAME,
LIM.PAY_END_DT,
LIM.PAYGROUP,
LIM.PAYGROUP_DESCR_LONG,
LIM.INVOICE_NUMBER,
LIM.DEDUCTION_CODE,
LIM.DESCR_LONG,
LIM.DEDUCTION_CLASS,
LIM.PLAN_TYPE,
(SELECT PLAN_TYPE_D.BEN_PLAN_TYPE_DESCR_LONG
FROM DW.BEN_PLAN_TYPE_D PLAN_TYPE_D
WHERE PLAN_TYPE_D.BEN_PLAN_TYPE_CD = LIM.PLAN_TYPE
AND LIM.INVOICE_DT BETWEEN PLAN_TYPE_D.EFF_START_DT
AND PLAN_TYPE_D.EFF_END_DT
AND SYSDATE BETWEEN PLAN_TYPE_D.VALID_START_DT
AND PLAN_TYPE_D.VALID_END_DT)
DEDUCTION_DESCRIPTION,
LIM.SEPCHK,
LIM.EMPLID,
LIM.NAME EE_NAME,
LIM.TAXABLE_AMOUNT,
LIM.NON_TAXABLE_AMOUNT,
LIM.CALCULATED_BASE,
(CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.MSUM ELSE 0 END)
MSUM,
(CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.QSUM ELSE 0 END)
QSUM,
(CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.YSUM ELSE 0 END)
YSUM
FROM ( SELECT COMPANY,
EMPLID,
EMPL_RCD,
CODE,
DEDUCTION_CLASS,
INVOICE,
INV_DT,
SUM (
CASE
WHEN MNTH_DT = CHK_MNTH_DT THEN AMOUNT
ELSE 0
END)
MSUM,
SUM (
CASE
WHEN MNTH_DT = CHK_MNTH_DT THEN HOURS
ELSE 0
END)
MHRS,
SUM (
CASE
WHEN QTR_DT = CHK_QTR_DT THEN AMOUNT
ELSE 0
END)
QSUM,
SUM (
CASE
WHEN QTR_DT = CHK_QTR_DT THEN HOURS
ELSE 0
END)
QHRS,
SUM (AMOUNT) YSUM,
SUM (NVL (HOURS, 0)) YHRS
FROM (SELECT PRF.COMPANY,
PRF.PAYGROUP,
PRF.PAY_END_DT PAY_END_DT,
PRF.EMPLID,
PRF.EMPL_RCD,
INV_DT_D.INVOICE_DT INV_DT,
INV_DT_D.CHECK_DT CHK_DT,
REG_D.CODE,
PRF.CODE_TYPE,
DED_D.PAY_DED_CLASS_CD DEDUCTION_CLASS,
PRF.AMOUNT,
PRF.HOURS,
INV_DT_D.INVOICE,
PRF.VALID_FLAG,
TRUNC (INV_DT_D.CHECK_DT, 'MM') MNTH_DT,
TRUNC (INV_DT_D.CHECK_DT, 'Q') QTR_DT,
TRUNC (INV_DT_D.CHECK_DT, 'YEAR') YEAR_DT,
TRUNC (CK_DT_D.CALENDAR_DT, 'MM') CHK_MNTH_DT,
TRUNC (CK_DT_D.CALENDAR_DT, 'Q') CHK_QTR_DT,
TRUNC (CK_DT_D.CALENDAR_DT, 'YEAR')
CHK_YEAR_DT
FROM DW.PAY_REGISTER_F PRF,
DW.PAY_PAYROLL_REGISTER_D REG_D,
DW.PAY_DEDUCTION_CLASS_D DED_D,
DW.SYS_CALENDAR_D CK_DT_D,
(SELECT COMPANY,
INVOICE_NUMBER INVOICE,
INVOICE_DT,
( PRF.CHECK_DT_D_SID
+ TO_DATE ('1/1/1900', 'MM/DD/YYYY'))
AS CHECK_DT,
TRUNC (
( PRF.CHECK_DT_D_SID
+ TO_DATE ('1/1/1900',
'MM/DD/YYYY')),
'YYYY')
AS START_DT,
( PRF.CHECK_DT_D_SID
+ TO_DATE ('1/1/1900', 'MM/DD/YYYY'))
AS END_DT
FROM DW.PAY_REGISTER_F PRF
WHERE COMPANY = '12C'
AND INVOICE_NUMBER = '1941765'
AND VALID_FLAG = 'Y'
AND ROWNUM = 1) INV_DT_D
WHERE PRF.COMPANY = INV_DT_D.COMPANY
AND '1941765' >= PRF.INVOICE_NUMBER
AND PRF.PAY_PAYROLL_REGISTER_D_SID =
REG_D.PAY_PAYROLL_REGISTER_D_SID
AND PRF.PAY_DEDUCTION_CLASS_D_SID =
DED_D.PAY_DEDUCTION_CLASS_D_SID
AND PRF.CHECK_DT_D_SID =
CK_DT_D.CALENDAR_D_SID
AND CK_DT_D.CALENDAR_DT >= INV_DT_D.START_DT
AND CK_DT_D.CALENDAR_DT <= INV_DT_D.END_DT
AND (DED_D.PAY_DED_CLASS_CD > 'K')) YTD_DEDS
WHERE YTD_DEDS.VALID_FLAG = 'Y'
GROUP BY COMPANY,
EMPLID,
EMPL_RCD,
CODE,
DEDUCTION_CLASS,
INVOICE,
INV_DT) AGG_DATA,
DW.HCM_EMPLOYEE_D EE_D,
DW.PAY_PAYROLL_REGISTER_D PAY_REG_D,
DW.T2_DW_ADMIN_STDRPT_ACCESS ACC,
( SELECT CO_D.COMPANY_CD,
CO_D.COMPANY_NAME,
PRF2.INVOICE_NUMBER,
PRF2.EMPLID,
PRF2.EMPL_RCD,
PRF2.SEPCHK,
PER_D.NAME,
SUBSTR (PPRD.DISPLAY_CODE, 1, 2) PLAN_TYPE,
SUBSTR (PPRD.DISPLAY_CODE, 4, 10) DEDUCTION_CODE,
PPRD.DISPLAY_CODE CODE,
PPRD.DESCR_LONG DESCR_LONG,
DC_D.PAY_DED_CLASS_CD DEDUCTION_CLASS,
PRF2.PAYGROUP,
PG_D.PAYGROUP_DESCR_LONG,
PRF2.PAY_END_DT,
PRF2.INVOICE_DT,
PRF2.CALCULATED_BASE,
ROW_NUMBER ()
OVER (
PARTITION BY PRF2.EMPLID,
SUBSTR (PPRD.DISPLAY_CODE, 4, 10),
DC_D.PAY_DED_CLASS_CD
ORDER BY
PRF2.EMPLID,
SUBSTR (PPRD.DISPLAY_CODE, 4, 10),
PRF2.SEPCHK NULLS LAST)
ROW_NUMBER,
SUM (
CASE
WHEN DC_D.PAY_DED_CLASS_CD = 'T'
THEN
PRF2.AMOUNT
ELSE
0
END)
TAXABLE_AMOUNT,
SUM (
CASE
WHEN DC_D.PAY_DED_CLASS_CD IN ('N', 'P')
THEN
PRF2.AMOUNT
ELSE
0
END)
NON_TAXABLE_AMOUNT,
SUM (PRF2.AMOUNT) AMOUNT
FROM DW.PAY_REGISTER_F PRF2
INNER JOIN DW.PAY_PAYROLL_REGISTER_D PPRD
ON PRF2.PAY_PAYROLL_REGISTER_D_SID =
PPRD.PAY_PAYROLL_REGISTER_D_SID
INNER JOIN DW.HCM_COMPANY_D CO_D
ON PRF2.HCM_COMPANY_D_SID = CO_D.HCM_COMPANY_D_SID
INNER JOIN DW.PAY_PAYGROUP_D PG_D
ON PRF2.PAY_PAYGROUP_D_SID =
PG_D.PAY_PAYGROUP_D_SID
INNER JOIN DW.HCM_PERSON_D PER_D
ON PRF2.HCM_PERSON_D_SID = PER_D.HCM_PERSON_D_SID
INNER JOIN DW.PAY_DEDUCTION_CLASS_D DC_D
ON PRF2.PAY_DEDUCTION_CLASS_D_SID =
DC_D.PAY_DEDUCTION_CLASS_D_SID
INNER JOIN DW.PAY_DEDUCTION_CLASS_D ded_d
ON PRF2.PAY_DEDUCTION_CLASS_D_SID =
DED_D.PAY_DEDUCTION_CLASS_D_SID
WHERE INVOICE_NUMBER = '1941765'
AND PRF2.VALID_FLAG = 'Y'
AND (DED_D.PAY_DED_CLASS_CD > 'K')
GROUP BY CO_D.COMPANY_CD,
PRF2.INVOICE_NUMBER,
CO_D.COMPANY_NAME,
PRF2.EMPLID,
PRF2.EMPL_RCD,
PRF2.SEPCHK,
PER_D.NAME,
SUBSTR (PPRD.DISPLAY_CODE, 1, 2),
SUBSTR (PPRD.DISPLAY_CODE, 4, 10),
PPRD.DISPLAY_CODE,
PPRD.DESCR_LONG,
DC_D.PAY_DED_CLASS_CD,
PRF2.PAYGROUP,
PG_D.PAYGROUP_DESCR_LONG,
PRF2.PAY_END_DT,
PRF2.INVOICE_DT,
PRF2.CALCULATED_BASE) LIM
WHERE EE_D.EMPLID = AGG_DATA.EMPLID
AND EE_D.EMPL_RCD = AGG_DATA.EMPL_RCD
AND EE_D.VALID_FLAG = 'Y'
AND EE_D.COMPANY_CD = LIM.COMPANY_CD
AND AGG_DATA.INV_DT BETWEEN EE_D.EFF_START_DT
AND EE_D.EFF_END_DT
AND AGG_DATA.CODE = PAY_REG_D.CODE
AND AGG_DATA.INV_DT BETWEEN PAY_REG_D.EFF_START_DT
AND PAY_REG_D.EFF_END_DT
AND SYSDATE BETWEEN PAY_REG_D.VALID_START_DT
AND PAY_REG_D.VALID_END_DT
AND AGG_DATA.INVOICE = LIM.INVOICE_NUMBER
AND AGG_DATA.EMPLID = LIM.EMPLID
AND AGG_DATA.EMPL_RCD = LIM.EMPL_RCD
AND AGG_DATA.CODE = LIM.CODE
AND AGG_DATA.DEDUCTION_CLASS = LIM.DEDUCTION_CLASS
AND ACC.COMPANY_ID = AGG_DATA.COMPANY
AND ACC.LOGIN_USER = '1357626'
AND ACC.REPORT_ID IN ('C10BENREG',
'C10BENREGCAN',
'C10BENREGSB')
AND DW.CHECK_EE_REPORT_ACCESS (AGG_DATA.COMPANY,
'1357626',
ACC.REPORT_ID,
EE_D.EMPLID,
EE_D.DEPTID,
EE_D.LOCATION_ID) = 1
ORDER BY LIM.NAME,
LIM.EMPLID,
LIM.DEDUCTION_CODE,
LIM.SEPCHK)
SELECT "Rpt"."EMPLID" "Employee_ID",
"Rpt"."EE_NAME" "Employee_Name",
"Rpt"."PLAN_TYPE" "Plan_Type",
CASE
WHEN "Rpt"."PLAN_TYPE" IS NULL
OR "Rpt"."DEDUCTION_DESCRIPTION" IS NULL
THEN
NULL
ELSE
"Rpt"."PLAN_TYPE"
|| ' - '
|| "Rpt"."DEDUCTION_DESCRIPTION"
END
"Type",
"Rpt"."DEDUCTION_DESCRIPTION"
"Benefit_Plan_Description",
"Rpt"."DEDUCTION_CODE" "Deduction_Code",
CASE
WHEN "Rpt"."DEDUCTION_CODE" IS NULL
OR "Rpt"."DESCR_LONG" IS NULL
THEN
NULL
ELSE
"Rpt"."DEDUCTION_CODE"
|| ' - '
|| "Rpt"."DESCR_LONG"
END
"Deduction",
"Rpt"."DESCR_LONG" "Payroll_Description",
"Rpt"."PAYGROUP_DESCR_LONG" "Pay_Group",
CAST ("Rpt"."INVOICE_NUMBER" AS VARCHAR (10))
"Invoice_Number",
"Rpt"."PAY_END_DT" "Pay_End_Date",
"Rpt"."CALCULATED_BASE" "Calculated_Benefit_Base",
"Rpt"."TAXABLE_AMOUNT" "Taxable_Amount",
"Rpt"."NON_TAXABLE_AMOUNT" "Non_Taxable_Amount",
"Rpt"."MSUM" "MTD",
"Rpt"."QSUM" "QTD",
"Rpt"."YSUM" "YTD"
FROM "Rpt"
|
|
|
|
|
|
|
|
|
|
|
|
| Re: improve sql performance (long sql here) [message #649069 is a reply to message #649067] |
Fri, 11 March 2016 09:40  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
marzbuzz wrote on Fri, 11 March 2016 15:31I guess all these less than a seconds added up since there are lots of joins in this SQL. By looking at SQL alone, is there anything that I can improve?
Thanks.. No. Nothing. The query ran in 0.74 seconds. From your trace:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.29 0.31 0 108 0 0
Execute 1 0.00 0.00 0 48 0 0
Fetch 61 0.41 0.42 0 44019 0 887
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63 0.71 0.74 0 44175 0 887
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 06:13:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
