My Oracle Support Banner

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 later
Information 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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.