1 2 3 Previous Next 36 Replies Latest reply on Apr 15, 2016 11:11 AM by Bashar.

    How to find concurrent program?

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.2.4

      11gR2

      OL6

       

       

      Our database is generating lots of archivelogs as in 1Gb every 2 minutes. This is happening even when there are no users logged or even on holidays.

      I am suspecting that a developer had scheduled a batch concurrent program that is constantly updating a large table. I also suspect that the sql is not "tuned" or maybe its updating the entire table instead of selective only.

       

      For example:

      In a certain workflow process, It might keep on updating a flag='Y'  even if it is"Y" already.

      Update table1 set flag='Y';

      instead of

      Update table1 set flag='Y' where flag != 'Y'

       

      Can you help me which batch program is constantly updating large table cause our archivelogs to blowup?

      -rw-r-----. 1 oraprod dba  932014080 Mar 27 07:09 1_24091_898613761.dbf

      -rw-r-----. 1 oraprod dba  915078656 Mar 27 07:12 1_24092_898613761.dbf

      -rw-r-----. 1 oraprod dba  927602688 Mar 27 07:16 1_24093_898613761.dbf

      -rw-r-----. 1 oraprod dba 1010039296 Mar 27 07:20 1_24094_898613761.dbf

      -rw-r-----. 1 oraprod dba  971700736 Mar 27 07:21 1_24095_898613761.dbf

      -rw-r-----. 1 oraprod dba  919386624 Mar 27 07:23 1_24096_898613761.dbf

      -rw-r-----. 1 oraprod dba  914632192 Mar 27 07:32 1_24097_898613761.dbf

      -rw-r-----. 1 oraprod dba  920769536 Mar 27 07:35 1_24098_898613761.dbf

      -rw-r-----. 1 oraprod dba  925391872 Mar 27 07:39 1_24099_898613761.dbf

      -rw-r-----. 1 oraprod dba 1012310016 Mar 27 07:43 1_24100_898613761.dbf

      -rw-r-----. 1 oraprod dba  949238272 Mar 27 07:44 1_24101_898613761.dbf

      -rw-r-----. 1 oraprod dba  946697216 Mar 27 07:45 1_24102_898613761.dbf

      -rw-r-----. 1 oraprod dba  950224384 Mar 27 07:56 1_24103_898613761.dbf

      -rw-r-----. 1 oraprod dba  912006144 Mar 27 07:59 1_24104_898613761.dbf

      -rw-r-----. 1 oraprod dba  933958656 Mar 27 08:04 1_24105_898613761.dbf

      -rw-r-----. 1 oraprod dba  905351168 Mar 27 08:08 1_24106_898613761.dbf

      -rw-r-----. 1 oraprod dba  968449536 Mar 27 08:09 1_24107_898613761.dbf

      -rw-r-----. 1 oraprod dba  971558400 Mar 27 08:11 1_24108_898613761.dbf

      -rw-r-----. 1 oraprod dba  955933184 Mar 27 08:20 1_24109_898613761.dbf

      -rw-r-----. 1 oraprod dba  914467840 Mar 27 08:23 1_24110_898613761.dbf

      -rw-r-----. 1 oraprod dba  921986048 Mar 27 08:27 1_24111_898613761.dbf

      -rw-r-----. 1 oraprod dba  913320960 Mar 27 08:31 1_24112_898613761.dbf

      -rw-r-----. 1 oraprod dba 1006709760 Mar 27 08:32 1_24113_898613761.dbf

      -rw-r-----. 1 oraprod dba  945099264 Mar 27 08:33 1_24114_898613761.dbf

       

       

      How do I select in the database (apps schema) all concurrent batch programs running and find their respective sqlcode?

      Or can I identify which program causing lots of archivelog generation?

       

       

      Kind regards,

      jc

        • 1. Re: How to find concurrent program?
          Nikhil Kumar

          Hi Jenna,

           

          Below query will give the output of Jobs,status and count in last one hour, Check which Job has a maximum count, there is a very good probability that the same job is running repeatedly. Further, you can troubleshoot.

           

          select

          fcpt.USER_CONCURRENT_PROGRAM_NAME,

          DECODE(fcr.phase_code,

          ‘C’, ‘Completed’,

          ‘I’, ‘Inactive’,

          ‘P’, ‘Pending’,

          ‘R’, ‘Running’,

          fcr.phase_code

          ) PHASE ,

          DECODE(fcr.status_code,

          ‘A’, ‘Waiting’,

          ‘B’, ‘Resuming’,

          ‘C’, ‘Normal’,

          ‘D’, ‘Cancelled’,

          ‘E’, ‘Errored’,

          ‘F’, ‘Scheduled’,

          ‘G’, ‘Warning’,

          ‘H’, ‘On Hold’,

          ‘I’, ‘Normal’,

          ‘M’, ‘No Manager’,

          ‘Q’, ‘Standby’,

          ‘R’, ‘Normal’,

          ‘S’, ‘Suspended’,

          ‘T’, ‘Terminating’,

          ‘U’, ‘Disabled’,

          ‘W’, ‘Paused’,

          ‘X’, ‘Terminated’,

          ‘Z’, ‘Waiting’,

          fcr.status_code

          ) STATUS,

          count(*)

          from apps.fnd_concurrent_programs_tl fcpt,apps.FND_CONCURRENT_REQUESTs fcr

          where fcpt.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID

          and fcpt.language = USERENV(’Lang’)

          and fcr.ACTUAL_START_DATE > sysdate - 1/24

          group by fcpt.USER_CONCURRENT_PROGRAM_NAME,fcr.phase_code,fcr.status_code

          /

          Also checkout the link, maybe helpful Oracle Concurrent Manager

           

          Regards

          Nikhil

          1 person found this helpful
          • 2. Re: How to find concurrent program?
            Beauty_and_dBest

            Thanks Nik and ALL,

             

            I got this output.

             

            USER_CONCURRENT_PROGRAM_NAME                                                                           PHASE      STATUS        COUNT(*)

            ----------------------------------------------------------------------------------------------------------------------------------------

            Create Accounting - Receiving                                                                          Completed Normal               12

            Create Accounting - Cost Management                                                                    Completed Normal               11

            Interface Trip Stop - SRS                                                                              Completed Warning              43

            Accounting Program                                                                                     Running   Normal                1

            Build Attribute Mapping Rules                                                                          Completed Normal                1

            OAM Applications Dashboard Collection                                                                  Completed Normal                2

            Create Accounting                                                                                      Running   Normal                1

            Generate COGS Recognition Events                                                                       Completed Normal                6

            Record Order Management Transactions                                                                   Completed Normal               11

            Create Accounting                                                                                      Completed Normal                2

            Workflow Background Process                                                                            Completed Normal               11

            Collect Revenue Recognition Information                                                                Completed Normal               11

            Accounting Program                                                                                     Completed Normal                2

             

             

            13 rows selected.

             

             

            Are all the above programs running in batch mode even if no users logged in?

            How do I knew which one is generating lots of archivelogs?

            I am suspecting it is the custom program that is not "tuned", is it "Interface Trip Stop"?

            Which are the custom programs in above list?

            How do I get the sqlcode of "Interface Trip Stop"?

             

            Kind regards,

            • 3. Re: How to find concurrent program?
              Bashar.

              None of them is a custom program.

              Please add the following to the select clause and post the results:

               

              avg(round((fcr.actual_completion_date - fcr.actual_start_date)*24*60,1)) average_duration

               

              Regards,

              Bashar

              1 person found this helpful
              • 4. Re: How to find concurrent program?
                Beauty_and_dBest

                Thanks bashar and all,

                 

                 

                AVERAGE_DURATION

                ----------------------------------------------------------------------------------------------------

                Create Accounting - Receiving, Completed, Normal, 11, .1

                Create Accounting - Cost Management, Completed, Normal, 12, .1

                Interface Trip Stop - SRS, Completed, Warning, 41, 0

                Accounting Program, Completed, Errored, 1, 17.5

                Accounting Program, Running, Normal, 1,

                Build Attribute Mapping Rules, Completed, Normal, 1, .1

                Create Accounting, Completed, Errored, 1, 18.5

                OAM Applications Dashboard Collection, Completed, Normal, 2, .1

                Create Accounting, Running, Normal, 1,

                Generate COGS Recognition Events, Completed, Normal, 6, 0

                Record Order Management Transactions, Completed, Normal, 11, 0

                Workflow Background Process, Completed, Normal, 11, .1

                Collect Revenue Recognition Information, Completed, Normal, 11, 0

                 

                13 rows selected.

                 

                Capture.PNG

                Capture2.PNG

                Capture3.PNG

                 

                 

                How do I match which of the program is it from the above statistics?

                 

                Is seems our database is generating 1Gb size archivelogs every 1 minute, is this okay? Or do I need to increase my redo log to 5Gb size each?

                 

                Kind regards,

                • 5. Re: How to find concurrent program?
                  Bashar.

                  It seems that the accounting program is working on a large set of data and it fails. Check the log file of the request.

                  Also please post the output of this query:

                   

                  select application_id, process_status_code, event_status_code, count(0)

                  from xla_events

                  where process_status_code != 'P'

                  group by application_id, process_status_code, event_status_code

                  order by 1,2,3;

                   

                  Regards,

                  Bashar

                  1 person found this helpful
                  • 6. Re: How to find concurrent program?
                    Beauty_and_dBest

                    Thanks Bashar,

                     

                    This issue of generating lots of archivelogs is already existing eversince at the start.

                     

                    SQL> connect apps/apps

                    Connected.

                    SQL> select application_id, process_status_code, event_status_code, count(0)

                    from xla_events

                    where process_status_code != 'P'

                    group by application_id, process_status_code, event_status_code

                    order by 1,2,3;

                     

                    APPLICATION_ID P E   COUNT(0)

                    -------------- - - ----------

                               200 D U          2

                               200 I U          5

                               200 U I          6

                               200 U U        239

                               222 D U      14124

                               222 U I          3

                               222 U U        241

                     

                    7 rows selected.

                    • 7. Re: How to find concurrent program?
                      Bashar.

                      Try to submit the Create Accounting request in Receivables for older dates (for example, on a monthly manner).

                       

                      Regards,

                      Bashar

                      1 person found this helpful
                      • 8. Re: How to find concurrent program?
                        Beauty_and_dBest

                        Thanks Bash,

                         

                        I do not know what will be affected if I run the program. It is the functional and end users who has authority to do it.

                        Is it okay to run it anytime and anybody?

                        Is the error in Create Accounting the cause of generating lots of archive logs?

                         

                         

                        Kind regards,

                        • 9. Re: How to find concurrent program?
                          Bashar.

                          The end users are the ones who usually submit it but anyone else can do the same.

                          From the Receivables Manager responsibility, you can run the "Submit Accounting" request (which in turn will run Create Accounting).

                          Set the date parameter to the end of the month for the past three months, one by one.

                          The error does not cause excessive redo log generation. The request itself does.

                           

                          Regards,

                          Bashar

                          1 person found this helpful
                          • 10. Re: How to find concurrent program?
                            Pravin Takpire

                            Hi,

                            please refer below and find out the session which is generating lot of redo.

                             

                            https://oracleracdba1.wordpress.com/2014/10/09/find-a-session-which-are-generating-more-archive-logs/

                             

                            then from session find out concurrent program. Based on that find out SQLs which are generating lo of redo

                             

                            regards

                            Pravin

                            1 person found this helpful
                            • 11. Re: How to find concurrent program?
                              Beauty_and_dBest

                              Hi ALL,

                               

                              I stand corrected, it is not really 1Gb per minuteThe maximum per hour is 18 archivelogs only.

                              So it is not really too much after all? but still manageable?

                               

                              Thanks for this script:

                              Track the Amount of Redo Generated per Hour :

                              SQL> SELECT  Start_Date,   Start_Time,   Num_Logs,Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname

                              FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date,To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs

                              FROM V$log_History Vlh GROUP BY To_Char(Vlh.First_Time,  'YYYY-MM-DD'),To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,V$log Vl ,  V$database Vdb

                              WHERE Vl.Group# = 1ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time;

                              START_DATESTART_TIMENUM_LOGSMBYTESDBNAME
                              3/26/20160:001515000PROD
                              3/26/20161:001515000PROD
                              3/26/20162:001717000PROD
                              3/26/20163:001616000PROD
                              3/26/20164:001717000PROD
                              3/26/20165:001616000PROD
                              3/26/20166:001717000PROD
                              3/26/20167:001515000PROD
                              3/26/20168:001717000PROD
                              3/26/20169:001616000PROD
                              3/26/201610:001515000PROD
                              3/26/201611:001515000PROD
                              3/26/201612:001818000PROD
                              3/26/201613:001212000PROD
                              3/26/201614:001212000PROD
                              3/26/201615:001515000PROD
                              3/26/201616:001616000PROD
                              3/26/201617:001414000PROD
                              3/26/201618:001313000PROD
                              3/26/201619:001313000PROD
                              3/26/201620:001616000PROD
                              3/26/201621:001212000PROD
                              3/26/201622:001414000PROD
                              3/26/201623:001616000PROD
                              3/27/20160:001414000PROD
                              3/27/20161:001414000PROD
                              3/27/20162:001616000PROD
                              3/27/20163:001414000PROD
                              3/27/20164:001616000PROD
                              3/27/20165:001414000PROD
                              3/27/20166:001717000PROD
                              3/27/20167:001414000PROD
                              3/27/20168:001616000PROD
                              3/27/20169:001515000PROD
                              3/27/201610:001515000PROD
                              3/27/201611:001515000PROD
                              3/27/201612:001616000PROD
                              3/27/201613:001515000PROD
                              3/27/201614:001515000PROD
                              3/27/201615:001515000PROD
                              3/27/201616:001616000PROD
                              3/27/201617:001414000PROD
                              3/27/201618:001414000PROD
                              3/27/201619:001616000PROD
                              3/27/201620:001111000PROD
                              3/27/201621:0088000PROD
                              3/27/201622:001313000PROD
                              3/27/201623:001717000PROD
                              3/28/20160:001515000PROD
                              3/28/20161:001515000PROD
                              3/28/20162:001717000PROD
                              3/28/20163:001616000PROD
                              3/28/20164:001717000PROD
                              3/28/20165:001717000PROD
                              3/28/20166:001717000PROD
                              3/28/20167:001717000PROD
                              3/28/20168:001818000PROD
                              3/28/20169:001313000PROD
                              3/28/201610:001616000PROD
                              3/28/201611:001313000PROD
                              3/28/201612:001313000PROD
                              3/28/201613:001313000PROD
                              3/28/201614:001010000PROD
                              3/28/201615:001010000PROD
                              • 12. Re: How to find concurrent program?
                                Pravin Takpire

                                18GB per hour is ok. don't worry about it. It is manageable.

                                regards

                                Pravin

                                • 13. Re: How to find concurrent program?
                                  Bashar.

                                  Is this a 24x7 system?!

                                  If not then this is not a healthy situation.

                                  How many transactions are entered per day (including weekends)?

                                   

                                  Regards,

                                  Bashar

                                  1 person found this helpful
                                  • 14. Re: How to find concurrent program?
                                    Beauty_and_dBest

                                    Thanks...

                                     

                                    This is a regular office day processing only, around 9am-6pm plus OT upto 10pm. Mondays to Fridays and  some OT on Saturdays

                                    1 2 3 Previous Next