Question

4
Replies
1124
Views
DebashisB1976 Member since 2014 3 posts
HighMark
Posted: 4 years ago
Last activity: 3 years 9 months ago
Closed

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:-

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            

 

Data Integration
Moderation Team has archived post
Share this page LinkedIn