5 Replies Latest reply on Jul 14, 2016 6:41 AM by Beauty_and_dBest

    Tune EBS Report

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.1.3

      OL6

       

      All of our Material Issuances Report usually run very long time and get ORA-01555.

      Is "Materials Issuances Report" a standard report or custom report?

      Is there a way to "tune" it so that it will not get ORA-01555 error?

       

      Kind regards,

      jc

       

            

      CONC_PROGREQUESTED_DATEACTUAL_START_DATEACTUAL_COMPLETION_DATEElapsed Time (sec)Completion Remarks
      EEI Accounts Payable (Trade) - Subsidiary Ledger Report12-JUL-2016 10:58:3212-JUL-2016 10:58:3212-JUL-2016 19:58:3132399Request Completed Normal
      Materials Issuances Report13-JUL-2016 14:06:1313-JUL-2016 14:06:1313-JUL-2016 15:40:025629ORA-01555: snapshot too old: rollback segment number 382 with name "_SYSSMU382_1383721623$" too small
      Materials Issuances Report13-JUL-2016 16:08:2113-JUL-2016 16:08:2113-JUL-2016 17:30:044903ORA-01555: snapshot too old: rollback segment number 414 with name "_SYSSMU414_1399858328$" too small
      Materials Issuances Report13-JUL-2016 14:46:4013-JUL-2016 14:46:4013-JUL-2016 16:04:334673ORA-01555: snapshot too old: rollback segment number 428 with name "_SYSSMU428_1399858679$" too small
      EEI Accounts Payable (Trade) - Subsidiary Ledger Report12-JUL-2016 09:36:3212-JUL-2016 09:36:3312-JUL-2016 10:49:454392ORA-01555: snapshot too old: rollback segment number 368 with name "_SYSSMU368_1383719690$" too small
      Materials Issuances Report13-JUL-2016 08:12:0613-JUL-2016 08:12:0613-JUL-2016 09:24:134327ORA-01555: snapshot too old: rollback segment number 337 with name "_SYSSMU337_1365415363$" too small
      Materials Issuances Report12-JUL-2016 09:18:0012-JUL-2016 09:18:0112-JUL-2016 10:15:193438ORA-01555: snapshot too old: rollback segment number 349 with name "_SYSSMU349_1366077368$" too small
        • 1. Re: Tune EBS Report
          John_K

          Is it a custom report? You should be able to answer that! As for the actual issue, see note 40689.1.

          1 person found this helpful
          • 2. Re: Tune EBS Report
            Pravin Takpire

            I would suggest you check below note for ORA-01555

             

            Master Note for ORA-1555 Errors (Doc ID 1307334.1)


            Also see if you can tune that report.

            ORA-01555 can be avoided if you have enough UNDO_TABLESPACE or you have the correct setting of UNDO_RETENTION parameter


            regards

            Pravin

            1 person found this helpful
            • 3. Re: Tune EBS Report
              Beauty_and_dBest

              Hi Ora, Pravin and ALL,

               

               

              I am new in this company and there is no documentations given by the previous implementor about the list of custom reports.

              Is there a FND table I can query to check if  the report is standard oracle report or customed report?

               

              Aside from increasing undo retention,we would like to check if "tuning" the report would also help resolve ORA-01555.

              I understand that if it is a standard report, we can raise an SR to ask help from support to tune the report.

               

               

              Kind regards,

              • 4. Re: Tune EBS Report
                Pravin Takpire

                To avoid Ora-01555 you don't need to have any previous documentation. What you need to find out the correct values of UNDO_TBSP and UNDO_RETENTION.

                Let that program complete first, lets take longer time. and then try to tune it.

                As your program is running more than 18 hours, your UNDO_RETENTION should be more than 18 hours at least

                regards

                Pravin

                1 person found this helpful
                • 5. Re: Tune EBS Report
                  Beauty_and_dBest

                  Thanks Pravin and ALL,

                   

                  You mentioned >> As your program is running more than 18 hours, your UNDO_RETENTION should be more than 18 hours at least

                  Is this 18 hours displayed in the report? or this is just an example?

                   

                  So as a conclusion... ORA-01555 can be solved only by increasing undo_retention, and any other solutions like tuning the query is not applicable

                   

                  As a conclusion also...I can not tell whether a report is standard or customized one? Unless it is stated in the documentation?