Question
HighMark
US
Last activity: 25 Nov 2016 5:29 EST
DB Oracle SQL Query Tuning
We have faced performance issue with below db query generated by list view in production. Execution of the query is taking long time and giving time out with large production data.
In production, we have approx. 4 million rows in the table "cpmhc_work". How can we optimize this SQL query with using list view?
Oracle SQL Query:-
We have faced performance issue with below db query generated by list view in production. Execution of the query is taking long time and giving time out with large production data.
In production, we have approx. 4 million rows in the table "cpmhc_work". How can we optimize this SQL query with using list view?
Oracle SQL Query:-
SELECT "PC0".pxObjClass AS "pxObjClass", "IndexPage".CHANNEL AS "Channel" , "IndexPage".CONTACTRELATION AS "ContactRelation" , "IndexPage".PROVIDERID AS "ProviderID" , "IndexPage".PROVIDERNAME AS "ProviderName" , "IndexPage".CONTACT AS "CONTACT" , "IndexPage".CONTACTRELATION AS "ContactRelation" , "IndexPage".MISC1 AS "Misc1" , "IndexPage".MISC2 AS "Misc2", "PC0".PYID AS "pyID" , "PC0".PYLABEL AS "pyLabel" , "PC0".PYSTATUSWORK AS "pyStatusWork" , "PC0".PXCREATEDATETIME AS "pxCreateDateTime" , "PC0".CONTACTNAME AS "ContactName" , "PC0".MEMBERID AS "MemberID" , "PC0".POLICYNUMBER AS "PolicyNumber" , "PC0".PYRESOLVEDTIMESTAMP AS "pyResolvedTimestamp" , "PC0".PZINSKEY AS "pzInsKey" , "PC0".PXINSNAME AS "pxInsName" , "PC0".CONTACTID AS "ContactID" , "PC0".INQUIRYNUMBER AS "InquiryNumber" , "PC0".PYCUSTOMERNAME AS "pyCustomerName", "PC0".PZINSKEY as "pxInsHandle" FROM cpmhc_work "PC0" JOIN HMK$CSD_INDEX_INTERACTIONS "IndexPage" ON "PC0".PYID = "IndexPage".PYID WHERE ( "PC0".pxObjClass LIKE ? ) AND ( ( ( "PC0".RECENTWORKIND >= ? ) AND ( "PC0".RECENTWORKIND <= ? ) AND ( ( (UPPER( "PC0".PXUPDATEOPERATOR) = ? ) AND "PC0".ASSIGNEDTO <> "PC0".PXUPDATEOPNAME ) OR ( (UPPER( "PC0".PYRESOLVEDUSERID) = ? ) ) OR ( (UPPER( "PC0".ROUTEDBY) = ? ) ) ) ) AND ( "PC0".ASPID = ? ) ) AND "IndexPage".pxObjClass LIKE ? ORDER BY "PC0".PXCREATEDATETIME DESC
Pega version:- Pega PRPC 6.2 SP2
Oracle DB Version:- Oracle 11g
We have below index:-
Table Name Index Name Column Pos Type ress Vals
---------------------------- --------------------------------------------- ------------------------- --- -------- ---- ------------
PEGA_CSD.CPMHC_WORK PEGA_CSD.CPMHC_WORK_01_N PYSTATUSWORK 1 None 29
PXOBJCLASS 2 62
PEGA_CSD.CPMHC_WORK_02_N PXINSNAME 1 None 9,126,449
CAPARENTID 2 3,199,744
PXOBJCLASS 3 62
PEGA_CSD.CPMHC_WORK_03_N PXCREATEDATETIME 1 None 5,742,080
PYID 2 9,126,449
PEGA_CSD.CPMHC_WORK_04_N PYID 1 None 9,126,449
PEGA_CSD.CPMHC_WORK_05_N CAPARENTID 1 None 3,199,744
PXOBJCLASS 2 62
PEGA_CSD.CPMHC_WORK_06_N PXINSNAME 1 None 9,126,449
PXOBJCLASS 2 62
PEGA_CSD.CPMHC_WORK_07_N INQUIRYCASEKEY 1 None 4,745,216
PXOBJCLASS 2 62
PYSTATUSWORK 3 29
PEGA_CSD.CPMHC_WORK_09_N RECENTWORKIND 1 None 1,328
PEGA_CSD.CPMHC_WORK_10_N PERFGCLIENT 1 None 102
PEGA_CSD.CPMHC_WORK_99_N ASPID 1 FUNCTION None 3
RECENTWORKIND 2 1,328
SYS_NC00150$ 3 1,884
SYS_NC00151$ 4 1,599
PEGA_CSD.CPMHC_WORK_AL APPEALLEVEL 1 None 1
PEGA_CSD.CPMHC_WORK_CI CLAIMID 1 None 553,728
PEGA_CSD.CPMHC_WORK_CN CERTIFICATIONNUMBER 1 None 0
PEGA_CSD.CPMHC_WORK_CT HCCUSTOMERTYPE 1 None 2
PEGA_CSD.CPMHC_WORK_EG EMPLOYERGROUPID 1 None 0
PEGA_CSD.CPMHC_WORK_INSKEY PXCOVERINSKEY 1 None 4,757,504
PEGA_CSD.CPMHC_WORK_MI MEMBERID 1 None 2,383,872
PEGA_CSD.CPMHC_WORK_PK PZINSKEY 1 UNIQUE None 9,126,449
PEGA_CSD.CPMHC_WORK_PN PROVIDERNUMBER 1 None 216,624
p10c Columns Indexed For Owner "pega_csd", Table Name "cpmhc_work" 03/19/16 09:33:13
Comp Distinct
Table Name Index Name Column Pos Type ress Vals
---------------------------- --------------------------------------------- ------------------------- --- -------- ---- ------------
PEGA_CSD.CPMHC_WORK PEGA_CSD.CPMHC_WORK_PO POLICYNUMBER 1 None 0
Comp Distinct
Table Name Index Name Column Pos Type ress Vals
---------------------------- --------------------------------------------- ------------------------- --- -------- ---- ------------
PEGA_CSD.HMK$CSD_INDEX_INTER PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_2_N PXCREATEDATETIME 1 FUNCTION None 5,350,912
STATUSWORK 2 29
PXOBJCLASS 3 1
SYS_NC00066$ 4 1,953,920
PROVIDERNUMBER 5 216,272
DELIMCLAIMNUMBERS 6 554,368
CONTACT 7 2,365,696
SYS_NC00067$ 8 4,637
SYS_NC00068$ 9 2,357,504
STATUSCATEGORY 10 3
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_3_N PXCREATEDATETIME 1 None 5,350,912
STATUSWORK 2 29
PXOBJCLASS 3 1
INQUIRYNUMBER 4 503,488
PYID 5 8,265,764
STATUSCATEGORY 6 3
ENROLLMENTSOURCE 7 44
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_4_N PXCREATEDATETIME 1 None 5,350,912
STATUSWORK 2 29
PXOBJCLASS 3 1
PROVIDERNUMBER 4 216,272
DELIMCLAIMNUMBERS 5 554,368
CONTACT 6 2,365,696
STATUSCATEGORY 7 3
ENROLLMENTSOURCE 8 44
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_5_N PXCREATEDATETIME 1 None 5,350,912
STATUSWORK 2 29
PXOBJCLASS 3 1
CONTACT 4 2,365,696
DELIMCLAIMNUMBERS 5 554,368
STATUSCATEGORY 6 3
ENROLLMENTSOURCE 7 44
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_6_N PXCREATEDATETIME 1 None 5,350,912
STATUSWORK 2 29
PXOBJCLASS 3 1
PROVIDERNUMBER 4 216,272
INTERACTIONTYPE 5 10
REASON 6 64
INSINQFIRSTINDICATOR 7 47
STATUSCATEGORY 8 3
ENROLLMENTSOURCE 9 44
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_7_N SYS_NC00066$ 1 FUNCTION None 1,953,920
PXCREATEDATETIME 2 5,350,912
STATUSWORK 3 29
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_7_N PXOBJCLASS 4 FUNCTION 1
PROVIDERNUMBER 5 216,272
DELIMCLAIMNUMBERS 6 554,368
CONTACT 7 2,365,696
SYS_NC00067$ 8 4,637
SYS_NC00068$ 9 2,357,504
STATUSCATEGORY 10 3
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_8_N PROVIDERNUMBER 1 None 216,272
PXCREATEDATETIME 2 5,350,912
STATUSWORK 3 29
PXOBJCLASS 4 1
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_9_N SYS_NC00069$ 1 FUNCTION None 2,192,128
PXCREATEDATETIME 2 5,350,912
STATUSWORK 3 29
PXOBJCLASS 4 1
PEGA_CSD.HMK$CSD_INDEX_INTERACTIONS_PK PZINSKEY 1 UNIQUE None 8,265,764
PEGA_CSD.HMK$CSD_INDEX_INTERACT_94_N PYID 1 None 8,265,764
PEGA_CSD.HMK$CSD_INDEX_INTERACT_95_N INQUIRYNUMBER 1 None 503,488
ENROLLMENTSOURCE 2 44
PEGA_CSD.HMK$CSD_INDEX_INTERACT_98_N PXINSINDEXEDKEY
Is this an OTB list view or you have written one of your own. Have you considered using pagination to limit the number of records retrieved from the DB?
Do you need the listview to run against the entire list of 4 million records? If not, consider implementing a date range partition on the DB. Oracle also provides tools to gather statistics on the query performance that you could leverage to identify the bottleneck.
HighMark
US
Its not OOTB list view, its our own written. There is date range of 6 months and pagination also there, still we are getting issue.I am looking for SQL tuning. Can any database expert advise on it?
Cognizant
IN