5 Replies Latest reply on Nov 13, 2018 10:30 AM by Subbu K

    PTY Match fails repeatedly

    Raj Raj Active Member

      Hello,

       

      The PTY match fails repeatedly due to TEMP tablespace issues. There has been no change in match rules and the volume has been the same for the past couple of years and we had no issues.

      The DBA doubled the TEMP tablespace from 400 GB to 800 GB and we also reduced the batch size from 10,000,000 to 1 MIllion. Any help is greatly appreciated.

       

      Thanks.

       

      [2018-11-08 11:48:31,747] [VIE00005| Party] [ERROR] com.delos.cmx.server.datalayer.repository.object.ReposTableObject$TableObjectExecutionThread: Thread[VIE00005| Party,5,Pooled Threads]

      com.siperian.common.SipRuntimeException: SIP-09069: Failed to execute java null

              Failed to execute java null

              at com.siperian.common.SipRuntimeException.createNotExternalized(SipRuntimeException.java:74)

              at com.delos.cmx.server.datalayer.repository.object.ReposTableObject$TableObjectExecutionThread.processException(ReposTableObject.java:2398)

              at com.delos.cmx.server.datalayer.repository.object.ReposTableObject$TableObjectExecutionThread.run(ReposTableObject.java:2423)

      Caused by: com.siperian.mrm.cleanse.api.CleanseException: -99: SIP-16165: Error trying to executeUpdate. SQL is CREATE TABLE T$MLE_B_PTY NOLOGGING  TABLESPACE CMX_TEMP PARALLEL (DEGREE 4) AS SELECT /*+ USE_HASH (C_MT_PTY_ADDR , C_MT_PTYRLE_PHN , C_MT_PTYRLE_PTYRLE_ADDR) */  MIN(C_MT_PTY_ADDR.ROWID_OBJECT) GROUP_ID , C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR  AS EX_BLDG_AND_FLOOR, C_MT_PTY_ADDR.EX_ADDRESSLINE1  AS EX_ADDRESSLINE1, C_MT_PTY_ADDR.EX_POSTALCODE  AS EX_POSTALCODE, C_MT_PTY_ADDR.EX_CITY  AS EX_CITY, C_MT_PTY_ADDR.EX_STATE  AS EX_STATE, C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL  AS EX_PHONE_MATCH_EXCL, C_MT_PTYRLE_PHN.EX_PHONETYPE  AS EX_PHONETYPE, C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT  AS EX_PHONENUMBEREXT, C_MT_PTYRLE_PHN.EX_PHONENUMBER  AS EX_PHONENUMBER, C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL  AS EX_ADDRESS_MATCH_EXCL, C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE  AS EX_PARTYTYPE, C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  AS EX_FULLNAME FROM C_MT_PTY_ADDR INNER  JOIN C_MT_PTYRLE_PHN ON C_MT_PTYRLE_PHN.ROWID_OBJECT=C_MT_PTY_ADDR.ROWID_OBJECT LEFT OUTER  JOIN C_MT_PTYRLE_PTYRLE_ADDR ON C_MT_PTYRLE_PTYRLE_ADDR.ROWID_PTYRLE=C_MT_PTYRLE_PHN.ROWID_PTYRLE WHERE C_MT_PTY_ADDR.ROWID_OBJECT IN ( SELECT ROWID_OBJECT FROM T$MAQ_B_PTY )  AND C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE = 'Org' GROUP BY  C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR   , C_MT_PTY_ADDR.EX_ADDRESSLINE1   , C_MT_PTY_ADDR.EX_POSTALCODE   , C_MT_PTY_ADDR.EX_CITY   , C_MT_PTY_ADDR.EX_STATE   , C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL   , C_MT_PTYRLE_PHN.EX_PHONETYPE   , C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT   , C_MT_PTYRLE_PHN.EX_PHONENUMBER   , C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL   , C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE   , C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  , error is ORA-12801: error signaled in parallel query server P005, instance ex07db03vm01.us1.ocm.s1517515.oraclecloudatcustomer.com:MDMCQA3 (3)

      ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

       

       

              CREATE TABLE T$MLE_B_PTY NOLOGGING  TABLESPACE CMX_TEMP PARALLEL (DEGREE 4) AS SELECT /*+ USE_HASH (C_MT_PTY_ADDR , C_MT_PTYRLE_PHN , C_MT_PTYRLE_PTYRLE_ADDR) */  MIN(C_MT_PTY_ADDR.ROWID_OBJECT) GROUP_ID , C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR  AS EX_BLDG_AND_FLOOR, C_MT_PTY_ADDR.EX_ADDRESSLINE1  AS EX_ADDRESSLINE1, C_MT_PTY_ADDR.EX_POSTALCODE  AS EX_POSTALCODE, C_MT_PTY_ADDR.EX_CITY  AS EX_CITY, C_MT_PTY_ADDR.EX_STATE  AS EX_STATE, C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL  AS EX_PHONE_MATCH_EXCL, C_MT_PTYRLE_PHN.EX_PHONETYPE  AS EX_PHONETYPE, C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT  AS EX_PHONENUMBEREXT, C_MT_PTYRLE_PHN.EX_PHONENUMBER  AS EX_PHONENUMBER, C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL  AS EX_ADDRESS_MATCH_EXCL, C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE  AS EX_PARTYTYPE, C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  AS EX_FULLNAME FROM C_MT_PTY_ADDR INNER  JOIN C_MT_PTYRLE_PHN ON C_MT_PTYRLE_PHN.ROWID_OBJECT=C_MT_PTY_ADDR.ROWID_OBJECT LEFT OUTER  JOIN C_MT_PTYRLE_PTYRLE_ADDR ON C_MT_PTYRLE_PTYRLE_ADDR.ROWID_PTYRLE=C_MT_PTYRLE_PHN.ROWID_PTYRLE WHERE C_MT_PTY_ADDR.ROWID_OBJECT IN ( SELECT ROWID_OBJECT FROM T$MAQ_B_PTY )  AND C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE = 'Org' GROUP BY  C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR   , C_MT_PTY_ADDR.EX_ADDRESSLINE1   , C_MT_PTY_ADDR.EX_POSTALCODE   , C_MT_PTY_ADDR.EX_CITY   , C_MT_PTY_ADDR.EX_STATE   , C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL   , C_MT_PTYRLE_PHN.EX_PHONETYPE   , C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT   , C_MT_PTYRLE_PHN.EX_PHONENUMBER   , C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL   , C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE   , C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  ;      ORA-12801: error signaled in parallel query server P005, instance ex07db03vm01.us1.ocm.s1517515.oraclecloudatcustomer.com:MDMCQA3 (3)

      ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

        • 1. Re: PTY Match fails repeatedly
          Subbu K Guru

          Can you have your DBA analyze the statistics for all the tables in the entire ORS schema? How big is your SGA? Is it configured to suit your data size? DBA can also analyze the statistics on SYS tablespace too?

          • 2. Re: PTY Match fails repeatedly
            Raj Raj Active Member

            Analyze tables was performed a couple of days on the ORS scheme and did not help.

            Yes. SGA is configured higher than what we anticipate.

            We have not analyzed SYS tablespaces yet.

             

            I found this error yesterday.

             

            Duplicate record exists in tokenization table C_B_PTY_STRP. An UPDATE should be used for this record.

                    SIP-16125: Duplicate record exists in tokenization table C_B_PTY_STRP. An UPDATE should be used for this record.

                    C_B_PTY_STRP

             

            KB 333562 talks about it. Can there be any other causes for this error?

             

            Thanks

             

             

             

             

            [2018-11-09 05:54:06,693] [[ACTIVE] ExecuteThread: '12' for queue: 'weblogic.kernel.Default (self-tuning)'] [ERROR] com.siperian.sif.server.RequestRouter: Request processing exception: com.siperian.sif.message.mrm.TokenizeRequest@c38b7b4/BASE_OBJECT.C_B_PTY

            com.siperian.common.SipRuntimeException: SIP-16125: Duplicate record exists in tokenization table C_B_PTY_STRP. An UPDATE should be used for this record.

                    SIP-16125: Duplicate record exists in tokenization table C_B_PTY_STRP. An UPDATE should be used for this record.

                    C_B_PTY_STRP

                    at com.siperian.common.SipRuntimeException.appendContext(SipRuntimeException.java:50)

                    at com.siperian.sif.server.mrm.TokenizeProcessor.process(TokenizeProcessor.java:110)

                    at com.siperian.sif.server.RequestRouter.process(RequestRouter.java:322)

                    at com.siperian.sif.server.RequestRouter.process(RequestRouter.java:270)

                    at com.siperian.sif.server.RequestRouter.process(RequestRouter.java:264)

                    at com.siperian.sif.server.RequestRouter.process(RequestRouter.java:259)

                    at com.siperian.sif.server.ejb.RequestLocalEjbBean.processTxRequiresNew(RequestLocalEjbBean.java:84)

                    at com.siperian.sif.server.ejb.RequestLocalEjb_9l5igg_RequestLocalEjbImpl.__WL_invoke(Unknown Source)

                    at weblogic.ejb.container.internal.SessionLocalMethodInvoker.invoke(SessionLocalMethodInvoker.java:33)

                    at com.siperian.sif.server.ejb.RequestLocalEjb_9l5igg_RequestLocalEjbImpl.processTxRequiresNew(Unknown Source)

                    at com.siperian.sif.server.ejb.RequestMDB.process(RequestMDB.java:289)

                    at com.siperian.sif.server.ejb.RequestMDB.onObjectMessage(RequestMDB.java:227)

                    at com.siperian.sif.server.ejb.RequestMDB.onMessage(RequestMDB.java:125)

                    at weblogic.ejb.container.internal.MDListener.execute(MDListener.java:453)

                    at weblogic.ejb.container.internal.MDListener.transactionalOnMessage(MDListener.java:376)

                    at weblogic.ejb.container.internal.MDListener.onMessage(MDListener.java:311)

                    at weblogic.jms.client.JMSSession.onMessage(JMSSession.java:4855)

                    at weblogic.jms.client.JMSSession.execute(JMSSession.java:4529)

                    at weblogic.jms.client.JMSSession.executeMessage(JMSSession.java:3976)

                    at weblogic.jms.client.JMSSession.access$000(JMSSession.java:120)

                    at weblogic.jms.client.JMSSession$UseForRunnable.run(JMSSession.java:5375)

                    at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:553)

                    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:311)

                    at weblogic.work.ExecuteThread.run(ExecuteThread.java:263)

            Caused by: com.siperian.common.SipException: SIP-16125: Duplicate record exists in tokenization table C_B_PTY_STRP. An UPDATE should be used for this record.

                    C_B_PTY_STRP

                    at com.siperian.mrm.match.TokenizeUtils.executeBatch(TokenizeUtils.java:87)

                    at com.siperian.mrm.match.GenerWriter.insertSingle(GenerWriter.java:190)

                    at com.siperian.mrm.match.tokenize.Tokenize.setSSADatatoDatabase(Tokenize.java:404)

                    at com.siperian.mrm.match.tokenize.TokenizeAPIConnection.setSSADatatoDatabase(TokenizeAPIConnection.java:136)

                    at com.siperian.mrm.match.tokenize.TokenizeAPI.prepareAndSetSSAtoDatabase(TokenizeAPI.java:205)

                    at com.siperian.mrm.match.tokenize.TokenizeAPI.execute(TokenizeAPI.java:139)

                    at com.siperian.sif.server.mrm.TokenizeProcessor.processNewAPI(TokenizeProcessor.java:143)

                    at com.siperian.sif.server.mrm.TokenizeProcessor.process(TokenizeProcessor.java:97)

                    ... 22 more

            • 3. Re: PTY Match fails repeatedly
              Subbu K Guru

              Can you truncate STRP table and run the tokenization job to generate all tokens? This is an one-time job to avoid such duplicate issues.

              • 4. Re: PTY Match fails repeatedly
                Raj Raj Active Member

                The STRP table was truncated and the match tokens were regenerated and still got the same error.

                 

                We are going to analyze the ORS schema one more time. This should capture the SYS tables also, right?

                Our final resort will be to open a case.
                Thanks
                Raj.

                 

                 

                 

                [2018-11-12 15:46:52,172] [[STUCK] ExecuteThread: '8' for queue: 'weblogic.kernel.Default (self-tuning)'] [ERROR] com.delos.cmx.server.interact.CleanseServlet: problem in call to match SIP-16165: Error trying to executeUpdate. SQL is CREATE TABLE T$MLE_B_PTY NOLOGGING  TABLESPACE CMX_TEMP PARALLEL (DEGREE 4) AS SELECT /*+ USE_HASH (C_MT_PTY_ADDR , C_MT_PTYRLE_PHN , C_MT_PTYRLE_PTYRLE_ADDR) */  MIN(C_MT_PTY_ADDR.ROWID_OBJECT) GROUP_ID , C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR  AS EX_BLDG_AND_FLOOR, C_MT_PTY_ADDR.EX_ADDRESSLINE1  AS EX_ADDRESSLINE1, C_MT_PTY_ADDR.EX_POSTALCODE  AS EX_POSTALCODE, C_MT_PTY_ADDR.EX_CITY  AS EX_CITY, C_MT_PTY_ADDR.EX_STATE  AS EX_STATE, C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL  AS EX_PHONE_MATCH_EXCL, C_MT_PTYRLE_PHN.EX_PHONETYPE  AS EX_PHONETYPE, C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT  AS EX_PHONENUMBEREXT, C_MT_PTYRLE_PHN.EX_PHONENUMBER  AS EX_PHONENUMBER, C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL  AS EX_ADDRESS_MATCH_EXCL, C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE  AS EX_PARTYTYPE, C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  AS EX_FULLNAME FROM C_MT_PTY_ADDR INNER  JOIN C_MT_PTYRLE_PHN ON C_MT_PTYRLE_PHN.ROWID_OBJECT=C_MT_PTY_ADDR.ROWID_OBJECT LEFT OUTER  JOIN C_MT_PTYRLE_PTYRLE_ADDR ON C_MT_PTYRLE_PTYRLE_ADDR.ROWID_PTYRLE=C_MT_PTYRLE_PHN.ROWID_PTYRLE WHERE C_MT_PTY_ADDR.ROWID_OBJECT IN ( SELECT ROWID_OBJECT FROM T$MAQ_B_PTY )  AND C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE = 'Org' GROUP BY  C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR   , C_MT_PTY_ADDR.EX_ADDRESSLINE1   , C_MT_PTY_ADDR.EX_POSTALCODE   , C_MT_PTY_ADDR.EX_CITY   , C_MT_PTY_ADDR.EX_STATE   , C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL   , C_MT_PTYRLE_PHN.EX_PHONETYPE   , C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT   , C_MT_PTYRLE_PHN.EX_PHONENUMBER   , C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL   , C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE   , C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  , error is ORA-12801: error signaled in parallel query server P005, instance ex07db03vm01.us1.ocm.s1517515.oraclecloudatcustomer.com:MDMCQA3 (3)

                ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

                 

                 

                        CREATE TABLE T$MLE_B_PTY NOLOGGING  TABLESPACE CMX_TEMP PARALLEL (DEGREE 4) AS SELECT /*+ USE_HASH (C_MT_PTY_ADDR , C_MT_PTYRLE_PHN , C_MT_PTYRLE_PTYRLE_ADDR) */  MIN(C_MT_PTY_ADDR.ROWID_OBJECT) GROUP_ID , C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR  AS EX_BLDG_AND_FLOOR, C_MT_PTY_ADDR.EX_ADDRESSLINE1  AS EX_ADDRESSLINE1, C_MT_PTY_ADDR.EX_POSTALCODE  AS EX_POSTALCODE, C_MT_PTY_ADDR.EX_CITY  AS EX_CITY, C_MT_PTY_ADDR.EX_STATE  AS EX_STATE, C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL  AS EX_PHONE_MATCH_EXCL, C_MT_PTYRLE_PHN.EX_PHONETYPE  AS EX_PHONETYPE, C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT  AS EX_PHONENUMBEREXT, C_MT_PTYRLE_PHN.EX_PHONENUMBER  AS EX_PHONENUMBER, C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL  AS EX_ADDRESS_MATCH_EXCL, C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE  AS EX_PARTYTYPE, C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  AS EX_FULLNAME FROM C_MT_PTY_ADDR INNER  JOIN C_MT_PTYRLE_PHN ON C_MT_PTYRLE_PHN.ROWID_OBJECT=C_MT_PTY_ADDR.ROWID_OBJECT LEFT OUTER  JOIN C_MT_PTYRLE_PTYRLE_ADDR ON C_MT_PTYRLE_PTYRLE_ADDR.ROWID_PTYRLE=C_MT_PTYRLE_PHN.ROWID_PTYRLE WHERE C_MT_PTY_ADDR.ROWID_OBJECT IN ( SELECT ROWID_OBJECT FROM T$MAQ_B_PTY )  AND C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE = 'Org' GROUP BY  C_MT_PTY_ADDR.EX_BLDG_AND_FLOOR   , C_MT_PTY_ADDR.EX_ADDRESSLINE1   , C_MT_PTY_ADDR.EX_POSTALCODE   , C_MT_PTY_ADDR.EX_CITY   , C_MT_PTY_ADDR.EX_STATE   , C_MT_PTYRLE_PHN.EX_PHONE_MATCH_EXCL   , C_MT_PTYRLE_PHN.EX_PHONETYPE   , C_MT_PTYRLE_PHN.EX_PHONENUMBEREXT   , C_MT_PTYRLE_PHN.EX_PHONENUMBER   , C_MT_PTYRLE_PTYRLE_ADDR.EX_ADDRESS_MATCH_EXCL   , C_MT_PTYRLE_PTYRLE_ADDR.EX_PARTYTYPE   , C_MT_PTYRLE_PTYRLE_ADDR.EX_FULLNAME  ;      ORA-12801: error signaled in parallel query server P005, instance ex07db03vm01.us1.ocm.s1517515.oraclecloudatcustomer.com:MDMCQA3 (3)

                ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

                ; rc was:-99

                • 5. Re: PTY Match fails repeatedly
                  Subbu K Guru

                  You need analyze SYS schema separately, outside of ORS.