SQL Statment Causing Database Server CPU Spike
(Doc ID 2898780.1)
Last updated on JANUARY 06, 2023
Applies to:
Oracle Health Insurance Claims Adjudication - Version 3.20.2.0.0 and laterInformation in this document applies to any platform.
Symptoms
During Claims batch run, the below SQL is spiking DB server CPU to 95% or above.
SELECT t0.ID, t0.DYN_CHAR_001, t0.DYN_CHAR_002, t0.DYN_CHAR_003, t0.DYN_CHAR_004, t0.DYN_CHAR_005, t0.DYN_DATE_001, t0.DYN_DATE_002, t0.DYN_DATE_003, t0.DYN_DATE_004, t0.DYN_DATE_005, t0.DYN_NUMBER_001, t0.DYN_NUMBER_002, t0.DYN_NUMBER_003, t0.DYN_NUMBER_004, t0.DYN_NUMBER_005, t0.CREATED_BY, t0.CREATION_DATE, t0.END_DATE, t0.LAST_UPDATED_BY, t0.LAST_UPDATED_DATE, t0.OBJECT_VERSION_NUMBER, t0.START_DATE, t0.VERSION, t0.IND_VOIDED, t0.DYN_DIAG_ID_001, t0.DYN_DIAG_ID_002, t0.DYN_DIAG_ID_003, t0.DYN_DIAG_ID_004, t0.DYN_DIAG_ID_005, t0.DYN_FLCO_ID_001, t0.DYN_FLCO_ID_002, t0.DYN_FLCO_ID_003, t0.DYN_FLCO_ID_004, t0.DYN_FLCO_ID_005, t0.DYN_PROC_ID_001, t0.DYN_PROC_ID_002, t0.DYN_PROC_ID_003, t0.DYN_PROC_ID_004, t0.DYN_PROC_ID_005, t0.DYN_PROV_ID_001, t0.DYN_PROV_ID_002, t0.DYN_PROV_ID_003, t0.DYN_PROV_ID_004, t0.DYN_PROV_ID_005, t0.CADE_ID, t0.INSE_ID FROM CLA_ADJUDICATION_CASES t0 WHERE (((((((t0.CADE_ID = :1 ) AND (t0.INSE_ID = :2 )) AND (t0.IND_VOIDED = :3 )) AND ((t0.START_DATE <= :4 ) OR (:5 IS NULL))) AND ((t0.END_DATE >= :6 ) OR (t0.END_DATE IS NULL))) AND EXISTS (SELECT :7 FROM CLA_ADJ_CASE_DETAILS t1 WHERE (((t1.ADCA_ID = t0.ID) AND (t1.TYPE IN (:8 , :9 ))) AND (((t1.IND_PRELIMINARY = :10 ) OR (t1.CLAI_ID = :11 )) OR t1.CLLI_ID IN (SELECT t2.ID FROM CLA_CLAIM_LINES t2 WHERE (t2.CLAI_ID = :12 ))))) ) AND NOT EXISTS (SELECT :13 FROM CLA_ADJ_CASE_DETAILS t3 WHERE (((t3.ADCA_ID = t0.ID) AND ((t3.CLAI_ID = :14 ) OR t3.CLLI_ID IN (SELECT t4.ID FROM CLA_CLAIM_LINES t4 WHERE (t4.CLAI_ID = :15 )))) AND (t3.END_DATE < :16 ))) ) ORDER BY t0.START_DATE
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |