You Asked
Hi Team,
Issue: Need improve performance of the query
with temp as
(
SELECT CLAIM_ACCOUNT, CLAIM_PATIENT, CLAIM_TOTALCHARGE,CLAIM_ACTPAYAMOUNT, CLAIM_PATIENTRESP, CLAIM_DATE, CL_SVC_DOS,
SUM(SERVICE_ADJUSTAMOUNT) SERVICE_ADJUSTAMOUNT, SUM(SERVICE_DENIALAMOUNT) SERVICE_DENIALAMOUNT,
PAYER_SHOT,CLAIM_NPI, IS_DUPLICATE,APPEALED_AMOUNT,RECOVERED_AMOUNT,CONTRACT,IS_RAC,IS_COMMENT,UCS_STATUS,PHYSICIAN,VISIT_TYPE,REMARK_CODE ,
HCPCS_CODE,HCPCS_DESC
FROM (
SELECT A.CLAIM_EX_ID,
A.BILL_TYPE,A.SERVICE_DOS CL_SVC_DOS,C.SERVICE_DOS,A.CLAIM_ACCOUNT,ROUND(SYSDATE - A.CLAIM_DATE) AGING, A.CLAIM_DATE,
A.CLAIM_SURNAME||' '||A.CLAIM_FIRSTNAME CLAIM_PATIENT,A.CLAIM_TOTALCHARGE,A.CLAIM_ACTPAYAMOUNT, A.CLAIM_PATIENTRESP, C.SERVICE_ADJUSTAMOUNT,
C.SERVICE_DENIALAMOUNT,B.PAYER_CODE PAYER_SHOT,A.CLAIM_NPI, A.DMS_CLFACT_KEY,C.DMS_SVCFACT_KEY, A.CLAIM_COMMENT REMARK_CODE, A.IS_DUPLICATE,
A.APPEALED_AMOUNT, A.RECOVERED_AMOUNT,A.CONTRACT,A.IS_RAC,A.IS_COMMENT,G.UCS_STATUS, A.VISIT_TYPE_KEY VISIT_TYPE,
nvl(A.PHYSICIAN_NAME,A.CLAIM_PHY_ID) PHYSICIAN ,
H.HCPCS_CODE,H.HCPCS_DESC
FROM F_DMS_CLAIM partition (RCD) A inner join D_PAYER B on A.PAYER_KEY = B.PAYER_KEY
inner join D_ORGANIZATION on A.ORG_KEY = D_ORGANIZATION.ORG_KEY
inner join D_FACILITY on A.FACILITY_KEY = D_FACILITY.FACILITY_KEY
inner join F_DMS_SERVICE partition (RCD) C on A.DMS_CLFACT_KEY=C.DMS_CLFACT_KEY
inner join D_UCS G on C.SERVICE_UCS_ID = G.UCS_ID AND C.FACILITY_KEY = G.FACILITY_KEY
inner join D_HCPCS H on H.HCPCS_KEY = C.HCPCS_KEY
left outer join F_DMS_SERVICE_ADJ partition (RCD) SA on C.DMS_SVCFACT_KEY = SA.DMS_SVCFACT_KEY
left outer join D_DENIAL D on SA.DENIAL_KEY = D.DENIAL_KEY
where G.UCS_STATUS <> 'Excluded' AND H.HCPCS_KEY<>'-9' AND TO_CHAR(D_ORGANIZATION.ORG_KEY) = '2' AND TO_CHAR(D_FACILITY.FACILITY_KEY) = '480' AND 2=2
GROUP BY A.CLAIM_EX_ID,A.BILL_TYPE,A.SERVICE_DOS ,C.SERVICE_DOS, A.CLAIM_ACCOUNT,C.SERVICE_ADJUSTAMOUNT,C.SERVICE_DENIALAMOUNT,ROUND(SYSDATE - A.CLAIM_DATE),
A.CLAIM_DATE, A.CLAIM_SURNAME||' '||A.CLAIM_FIRSTNAME, A.CLAIM_TOTALCHARGE,A.CLAIM_ACTPAYAMOUNT,A.CLAIM_PATIENTRESP,B.PAYER_CODE,A.CLAIM_NPI,
A.DMS_CLFACT_KEY, C.DMS_SVCFACT_KEY,A.CLAIM_COMMENT,A.IS_DUPLICATE, A.APPEALED_AMOUNT,A.RECOVERED_AMOUNT,A.CONTRACT,A.IS_RAC,A.IS_COMMENT,
G.UCS_STATUS,A.VISIT_TYPE_KEY , nvl(A.PHYSICIAN_NAME,A.CLAIM_PHY_ID),H.HCPCS_CODE,H.HCPCS_DESC
)
GROUP BY CLAIM_ACCOUNT, CLAIM_PATIENT, CLAIM_TOTALCHARGE,CLAIM_ACTPAYAMOUNT,CLAIM_PATIENTRESP, PAYER_SHOT, CLAIM_DATE, CL_SVC_DOS,
CLAIM_NPI,IS_DUPLICATE, APPEALED_AMOUNT,RECOVERED_AMOUNT,CONTRACT,IS_RAC,IS_COMMENT,UCS_STATUS,PHYSICIAN,VISIT_TYPE,REMARK_CODE,HCPCS_CODE ,HCPCS_DESC
)
select HCPCS_CODE,HCPCS_DESC, sum(CLAIM_TOTALCHARGE) TOTAL_CHARGES,sum(CLAIM_ACTPAYAMOUNT) TOTAL_PAYMENTS,
sum(SERVICE_ADJUSTAMOUNT) TOTAL_ADJUSTMENTS,sum(SERVICE_DENIALAMOUNT) TOTAL_DENIALS,sum(TOTAL_REC) NO_OF_ACCOUNTS
from (
SELECT HCPCS_CODE,HCPCS_DESC, NVL(SUM(CLAIM_TOTALCHARGE),0) CLAIM_TOTALCHARGE, null CLAIM_ACTPAYAMOUNT, null SERVICE_ADJUSTAMOUNT,
NVL(SUM(SERVICE_DENIALAMOUNT),0) SERVICE_DENIALAMOUNT,COUNT(*) TOTAL_REC
from
(
select HCPCS_CODE,HCPCS_DESC,CLAIM_ACCOUNT,CLAIM_PATIENT,CLAIM_DATE,max(CLAIM_TOTALCHARGE) CLAIM_TOTALCHARGE,max(SERVICE_DENIALAMOUNT) SERVICE_DENIALAMOUNT FROM temp
where (HCPCS_CODE,HCPCS_DESC,CLAIM_ACCOUNT,CLAIM_PATIENT,CLAIM_DATE) in (select HCPCS_CODE,HCPCS_DESC,CLAIM_ACCOUNT,CLAIM_PATIENT,max(CLAIM_DATE)
from temp group by HCPCS_CODE,HCPCS_DESC,CLAIM_ACCOUNT,CLAIM_PATIENT)
group by HCPCS_CODE,HCPCS_DESC,CLAIM_ACCOUNT,CLAIM_PATIENT,CLAIM_DATE
) GROUP BY HCPCS_CODE,HCPCS_DESC
union
select HCPCS_CODE,HCPCS_DESC, null,nvl(sum(CLAIM_ACTPAYAMOUNT),0),NVL(SUM(SERVICE_ADJUSTAMOUNT),0),null,null from temp GROUP BY HCPCS_CODE,HCPCS_DESC
) GROUP BY HCPCS_CODE,HCPCS_DESC
order by TOTAL_ADJUSTMENTS desc nulls last
The above query contains two parts
1) With Temp
2) Calculations Based on Temp table.
We have 10 report quires like this which are facing performance issue.
For all 10 queries TEMP (first part) is common reaming calculation part will differ based on application logic .
I am planing to implement to below steps to improve performance of the Ten queries
1) If I store result of first query In a table then will use this table in 2nd part of the query ,Performance of the query also improve because of pre-calculated result.
Here the issue is, I need to refresh huge table every day(because some calculations in the query based on sysdate )
2)If I store result In a mview and go ahead with query rewrite option .
Issue is, I am using sysdate in query, So I could not enable query rewrite on option.
Here also I need to refresh Mview daily. But we have some techniques to improve mview performance.
Could you please help me how fix this performance issue .
Thanks & Regards
Krishna.
and Chris said...
To fix your performance issue, the first thing you need to do is understand the query is currently doing!
To do this, you need an execution plan. Note this must include details of what the query actually did - how many rows it processed, gets it did, etc.
You can find instructions on how to get one at:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution This will help you figure out why your query is currently slow. It'll also give you a baseline to check your changes against.
If you need help once you have the plan, post it here and we'll see what we can do.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment