Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 27, 2017 - 7:46 am UTC

Last updated: January 30, 2017 - 3:47 pm UTC

Version: 10.2.0

Viewed 1000+ times

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

Comments

A reader, January 27, 2017 - 11:33 am UTC

Hi,

I am checking execution plan also.

Here I am asking how to avoid temp SQL which has executing repeatedly.

Temp query is taking more than 1 min 15 sec.


There are 10 queries which has same temp statement.

Please tell is there any other way.


Thanks
Krishna









Chris Saxon
January 27, 2017 - 5:27 pm UTC

You could store the results in a MV. Which might help. Or maybe you can optimize the SQL in the with clause so it runs "fast enough".

Storing the results in a temporary table before running the main query is unlikely to help. Unless you'll reuse the data multiple times in the same session. This is because you've got the same query execution time (probably), plus the overhead of inserting it and reading it back out again.

We need to see the execution plan to give you meaningful advice though!

A reader, January 27, 2017 - 11:53 am UTC

Hi,

Here I am concentrating how to stop executing TEMP query which has executing repeatedly.

For all 10 reports temp query is same.

By using temp, Programmers do some calculations for every report.

I am assuming,we can improve Query performance by doing some simple changes to the query.

Example 1:

With temp as (
select sum(sal) sal,sum(comm) comm from emp)
select sal from emp;

Example2

With temp as (
select sum(sal) sal,sum(comm) comm from emp)
select comm from emp;

In above examples temp query is same.


Thanks
Krisha
Chris Saxon
January 27, 2017 - 5:28 pm UTC

The query may be the same, but the data could be different!

Krishna, January 28, 2017 - 1:20 am UTC

*** string too long, truncated *** (30687), ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Here Is the Execution plan of the Qeruy.

SQL_ID cp2yuxq8dq8t9, child number 0
-------------------------------------
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,REM
ARK_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) PHYS

Plan hash value: 706710870

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1814K(100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | | |
| 3 | SORT GROUP BY | | 2078K| 4744M| 10G| 1260K (1)| 04:12:10 | | |
| 4 | VIEW | | 2078K| 4744M| | 222K (1)| 00:44:34 | | |
| 5 | SORT GROUP BY | | 2078K| 771M| 1624M| 222K (1)| 00:44:34 | | |
| 6 | NESTED LOOPS OUTER | | 2078K| 771M| | 50637 (1)| 00:10:08 | | |
|* 7 | HASH JOIN | | 2078K| 761M| | 50426 (1)| 00:10:06 | | |
|* 8 | TABLE ACCESS FULL | D_HCPCS | 15435 | 527K| | 40 (0)| 00:00:01 | | |
|* 9 | HASH JOIN | | 2078K| 691M| | 50374 (1)| 00:10:05 | | |
| 10 | TABLE ACCESS FULL | D_PAYER | 94 | 1410 | | 3 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 2078K| 662M| | 50359 (1)| 00:10:05 | | |
|* 12 | INDEX FULL SCAN | PK_D_FACILITY | 1 | 4 | | 1 (0)| 00:00:01 | | |
|* 13 | HASH JOIN RIGHT OUTER | | 2078K| 654M| 61M| 50347 (1)| 00:10:05 | | |
| 14 | PARTITION LIST SINGLE | | 2469K| 32M| | 2252 (2)| 00:00:28 | 3 | 3 |
| 15 | TABLE ACCESS FULL | F_DMS_SERVICE_ADJ | 2469K| 32M| | 2252 (2)| 00:00:28 | 3 | 3 |
|* 16 | HASH JOIN | | 1460K| 440M| | 22375 (1)| 00:04:29 | | |
|* 17 | TABLE ACCESS FULL | D_UCS | 5 | 85 | | 3 (0)| 00:00:01 | | |
|* 18 | HASH JOIN | | 1734K| 494M| 87M| 22363 (1)| 00:04:29 | | |
| 19 | PARTITION LIST SINGLE | | 1734K| 67M| | 8934 (1)| 00:01:48 | 3 | 3 |
|* 20 | TABLE ACCESS FULL | F_DMS_SERVICE | 1734K| 67M| | 8934 (1)| 00:01:48 | 3 | 3 |
|* 21 | HASH JOIN | | 441K| 108M| | 3414 (2)| 00:00:41 | | |
|* 22 | INDEX FULL SCAN | PK_D_ORGANIZATION | 1 | 3 | | 1 (0)| 00:00:01 | | |
| 23 | PARTITION LIST SINGLE| | 441K| 107M| | 3410 (2)| 00:00:41 | KEY | KEY |
| 24 | TABLE ACCESS FULL | F_DMS_CLAIM | 441K| 107M| | 3410 (2)| 00:00:41 | 3 | 3 |
|* 25 | INDEX UNIQUE SCAN | PK_DENIAL_KEY | 1 | 5 | | 0 (0)| | | |
| 26 | SORT ORDER BY | | 2078K| 731M| | 554K (1)| 01:50:50 | | |
| 27 | SORT GROUP BY | | 2078K| 731M| | 554K (1)| 01:50:50 | | |
| 28 | VIEW | | 2078K| 731M| | 553K (1)| 01:50:48 | | |
| 29 | SORT UNIQUE | | 2078K| 113M| 287M| 553K (35)| 01:50:48 | | |
| 30 | UNION-ALL | | | | | | | | |
| 31 | SORT GROUP BY NOSORT | | 1 | 57 | | 366K (1)| 01:13:13 | | |
| 32 | VIEW | | 1 | 57 | | 366K (1)| 01:13:13 | | |
| 33 | SORT GROUP BY | | 1 | 331 | | 366K (1)| 01:13:13 | | |
|* 34 | HASH JOIN | | 1 | 331 | 327M| 366K (1)| 01:13:13 | | |
| 35 | VIEW | VW_NSO_1 | 2078K| 303M| | 200K (1)| 00:40:10 | | |
| 36 | SORT GROUP BY | | 2078K| 303M| 676M| 200K (1)| 00:40:10 | | |
| 37 | VIEW | | 2078K| 303M| | 130K (1)| 00:26:05 | | |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D688C_663B8483 | 2078K| 4744M| | 130K (1)| 00:26:05 | | |
| 39 | VIEW | | 2078K| 352M| | 130K (1)| 00:26:05 | | |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D688C_663B8483 | 2078K| 4744M| | 130K (1)| 00:26:05 | | |
| 41 | SORT GROUP BY | | 2078K| 113M| 287M| 187K (1)| 00:37:35 | | |
| 42 | VIEW | | 2078K| 113M| | 130K (1)| 00:26:05 | | |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D688C_663B8483 | 2078K| 4744M| | 130K (1)| 00:26:05 | | |
------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$13
2 - SEL$DD8D4BD4
4 - SEL$AE5F855D / $vm_view@SEL$DD8D4BD4
5 - SEL$AE5F855D
8 - SEL$AE5F855D / H@SEL$7
10 - SEL$AE5F855D / B@SEL$2
12 - SEL$AE5F855D / D_FACILITY@SEL$4
15 - SEL$AE5F855D / SA@SEL$8
17 - SEL$AE5F855D / G@SEL$6
20 - SEL$AE5F855D / C@SEL$5
22 - SEL$AE5F855D / D_ORGANIZATION@SEL$3
24 - SEL$AE5F855D / A@SEL$2
25 - SEL$AE5F855D / D@SEL$10
28 - SET$1 / from$_subquery$_019@SEL$13
29 - SET$1
31 - SEL$14
32 - SEL$81B4A55D / from$_subquery$_020@SEL$14
33 - SEL$81B4A55D
35 - SEL$F42BAC74 / VW_NSO_1@SEL$81B4A55D
36 - SEL$F42BAC74
37 - SEL$584751EE / TEMP@SEL$16
38 - SEL$584751EE / T1@SEL$584751EE
39 - SEL$584751EF / TEMP@SEL$15
40 - SEL$584751EF / T1@SEL$584751EF
41 - SEL$17
42 - SEL$584751ED / TEMP@SEL$17
43 - SEL$584751ED / T1@SEL$584751ED

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

7 - access("H"."HCPCS_KEY"="C"."HCPCS_KEY")
8 - filter("H"."HCPCS_KEY"<>(-9))
9 - access("A"."PAYER_KEY"="B"."PAYER_KEY")
11 - access("A"."FACILITY_KEY"="D_FACILITY"."FACILITY_KEY")
12 - filter(TO_CHAR("D_FACILITY"."FACILITY_KEY")='480')
13 - access("C"."DMS_SVCFACT_KEY"="SA"."DMS_SVCFACT_KEY")
16 - access("C"."SERVICE_UCS_ID"="G"."UCS_ID" AND "C"."FACILITY_KEY"="G"."FACILITY_KEY")
17 - filter("G"."UCS_STATUS"<>'Excluded')
18 - access("A"."DMS_CLFACT_KEY"="C"."DMS_CLFACT_KEY")
20 - filter("C"."HCPCS_KEY"<>(-9))
21 - access("A"."ORG_KEY"="D_ORGANIZATION"."ORG_KEY")
22 - filter(TO_CHAR("D_ORGANIZATION"."ORG_KEY")='2')
25 - access("SA"."DENIAL_KEY"="D"."DENIAL_KEY")
34 - access("HCPCS_CODE"="$nso_col_1" AND "HCPCS_DESC"="$nso_col_2" AND "CLAIM_ACCOUNT"="$nso_col_3" AND
"CLAIM_PATIENT"="$nso_col_4" AND "CLAIM_DATE"="$nso_col_5")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - SUM("SERVICE_ADJUSTAMOUNT")[22], "HCPCS_CODE"[VARCHAR2,100], "HC...
Chris Saxon
January 30, 2017 - 3:47 pm UTC

2 things:

1. We need to see execution stats. Read the linked blog post, particularly the section on DBMX_Xplan. Ensure the output you get includes the "A-rows", "E-rows" and buffers columns. This tells us what is actually happening. We need to know this to give meaningful help.
2. PLEASE USE THE < code > TAGS! It's really, really hard to read plans once the original formatting with the columns lined up properly is lost.

Once you have this, post the updated plan.

A reader, January 30, 2017 - 7:42 am UTC

Hi,

Please help me how go ahead on this issue.

Thanks
Krishna.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.