3 Replies Latest reply on Nov 27, 2018 9:48 AM by Pravin Takpire

    Concurrent Program Took Very Long Time

    Ramaraju

      Hi All,

      Concurrent program taking 90 mins ; before it was took around 10 mins. Parameters are same for these jobs. I didn't find any locks at that time.

      I didn't find sql_id in V$SQL I have find it in V$SQLSTATS.

      Can you please help me on this how to trouble shoot this.

      EBS: R12.2.7

      DB: 12.1.0.2

      OS: RHEL 5.11

       

      Thanks,

      Ramaraju

        • 1. Re: Concurrent Program Took Very Long Time
          John_K

          Come on... I like a challenge but at least give us a clue.

           

          * What is the concurrent program?

          * What parameters did you use?

          * What was the SQL execution plan?

          * Does the concurrent program log show anything?

          * Have you enabled 10049 trace to see if there is any particular process taking a long time?

          • 2. Re: Concurrent Program Took Very Long Time
            Ramaraju

            * What is the concurrent program?

            --> iStore Section Search Refresh

             

             

            * What parameters did you use?

            --> 671, 960, 11041, 72958118, 0

                671, 960, 11041, 72928868, 0

                671, 960, 11041, 72887136, 0

             

             

            * What was the SQL execution plan?

            --> SQL> select * from TABLE(dbms_xplan.display_awr('3t5r1hj96h36y'));

                no rows selected

             

             

             

             

            * Have you enabled 10049 trace to see if there is any particular process taking a long time?

            --> No, Following sql_id taking long time

            SQL_ID: 3t5r1hj96h36y

             

             

            SQL_TEXT: TRUNCATE TABLE IBE.IBE_SECTION_SEARCH_PART drop storage

             

             

            SQL_FULLTEXT: TRUNCATE TABLE IBE.IBE_SECTION_SEARCH_PART drop storage

             

             

             

             

            * Does the concurrent program log show anything?

            --> No

            +---------------------------------------------------------------------------+

            iStore: Version : 12.2

            Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

            IBESERSCT: iStore Section Search Refresh

            +---------------------------------------------------------------------------+

            Current system time is 26-NOV-2018 08:39:21

            +---------------------------------------------------------------------------+

            **Starts**26-NOV-2018 08:39:22

            **Ends**26-NOV-2018 11:28:24

            +---------------------------------------------------------------------------+

            Start of log messages from FND_FILE

            +---------------------------------------------------------------------------+

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Calling Alter table exchange partition

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Inserting into IBE_SECTION_SEARCH_PART

            Calling Alter table exchange partition

            the loop index after insert is  = 0

            Sync the intermedia index

            Procedure completed sucessfully

            +---------------------------------------------------------------------------+

            End of log messages from FND_FILE

            +---------------------------------------------------------------------------+

            +---------------------------------------------------------------------------+

            No completion options were requested.

            Output file size:

            0

            Deleting empty output file.

            +---------------------------------------------------------------------------+

            Concurrent request completed successfully

            Current system time is 26-NOV-2018 11:28:24

            • 3. Re: Concurrent Program Took Very Long Time
              Pravin Takpire

              Can you enable trace on concurrent request ? Check why eaxctly that truncate statement is taking long ? Run AWR report. Find all relevant details

               

              regards

              Pravin