1 2 Previous Next 15 Replies Latest reply on Dec 18, 2017 2:53 PM by Bashar.

    Lots Archive log generated

    Beauty_and_dBest

      EBS R12.1.3

      11gR1

      OL6

       

       

      In previous months, our EBS 12.1.3 generates only around Three (4)  archivelogs  of size 1gb, Daily.

      We only operate  from 8am to 7pm (no office on Weekends).

      But lately I noticed that there are heavy archivelog generations daily its like 20 archivelogs a day (almost 20Gb total size)  < Everyday

      Even after office hours 7pm to 8am , and during Saturdays/Sundays, it generates  lots of archivelogs.

       

      What factor could have caused these lots of archivelog generation?

      Could there be auditing or tracing or diagnostics turn-on and forgotten by the users to turn off?

      Or could there be new concurrent program updates being scheduled to run every minutes or every hours?

      How do I find the sql that is causing lots of updates?

       

      Please help how to check if there are concurrent programs running every minutes or every hours , and also with audits or tracing.

       

       

      Kind regards,

      jc

        • 1. Re: Lots Archive log generated
          3244661

          To find concurrent request was run for last 7 days

          ===========================================================

           

           

          SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,          

          round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,         

          a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),

          To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,     

          (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,         

          d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority FROM   apps.fnd_concurrent_requests a,         

          apps.fnd_concurrent_programs b , apps.FND_CONCURRENT_PROGRAMS_TL c,          

          apps.fnd_user d  WHERE a.concurrent_program_id= b.concurrent_program_id AND         

          b.concurrent_program_id=c.concurrent_program_id AND a.requested_by =d.user_id

          AND trunc(a.actual_completion_date) > sysdate -7

          and status_code='C';

          • 2. Re: Lots Archive log generated
            Kanda-Oracle

            Hi JC,

             

            You can mine the redo log files. Logminer  utility will help to indentify the object Name , Operation  and other details from log file. Use the below

             

            dbms_logmnr_d.build

            dbms_logmnr.add_logfile

             

            SQL> select SEG_OWNER,SEG_NAME,operation,count(*) counts from v$logmnr_contents

            group by SEG_OWNER,SEG_NAME,operation;

             

             

            Hope this helps!

             

            Thanks

             

            N Kandasamy

            • 3. Re: Lots Archive log generated
              Kanda-Oracle

              Hi JC,

               

              Alternatively you can take AWR report for the specific period ( 8am to 9 am ) and look for any extensive DML to know the potential cause.

               

              Thanks

              • 4. Re: Lots Archive log generated
                Beauty_and_dBest

                Thanks Kanda,

                 

                Is there a way to run AWR one time only from 8am to 9am? or I need to run per hour

                 

                 

                Kind regards,

                • 5. Re: Lots Archive log generated
                  Kanda-Oracle

                  you can run one per hour. you can also run for 5-10 snaps together (consolidated for a day).

                   

                  1. Look for DML that is expensive.
                  2. Look at the Segment Statistics

                  Segments by Logical Reads

                  Segments by Physical Reads

                  :

                  :

                  Segments by Physical Writes

                  • 6. Re: Lots Archive log generated
                    Kanda-Oracle

                    Note: Find out any tables has huge insert/update/delete/long running select, then find out the DML that causes that insert/update..

                     

                    Now tune the DML or stop the module/program that runs the DML.

                     

                    Hope this helps!

                     

                    • 7. Re: Lots Archive log generated
                      Beauty_and_dBest

                      Thanks Kanda,

                       

                      I will try that....brb

                      • 8. Re: Lots Archive log generated
                        Beauty_and_dBest

                        Hi Kanda and User3244661,

                         

                        I can not believe this, or is my query correct?

                         

                        Based of the query above provided by User3244661, I counted all the concurrent programs being run today 12-DEC-17 and it says 7,802 ?

                        Is this correct?

                         

                        SELECT COUNT(*) FROM

                        (SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,   

                        round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,  

                        a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),

                        To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
                        (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,  
                        d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority FROM   apps.fnd_concurrent_requests a,  
                        apps.fnd_concurrent_programs b , apps.FND_CONCURRENT_PROGRAMS_TL c,   
                        apps.fnd_user d  WHERE a.concurrent_program_id= b.concurrent_program_id AND  

                        b.concurrent_program_id=c.concurrent_program_id AND a.requested_by =d.user_id

                        AND trunc(a.actual_start_date) > SYSDATE - 1

                        and status_code='C');

                         

                        Count is 7,802

                        And is increasing every seconds

                        Below is the count of each program name:

                         

                        Process transaction interface    3510

                        WIP Move Transaction Manager    2296

                        Format Automatic Remittances Program    2296

                        Workflow Background Process    1474

                        Manager: Lot Move Transactions    234

                        Actual Cost Worker    121

                        OAM Applications Dashboard Collection    112

                        Shipping Exceptions Report    98

                        Autoinvoice Master Program    47

                        Autoinvoice Import Program    47

                        F1 Delivery Receipt and Sales Invoice Template    37

                        Receiving Transaction Processor    35

                        Receipt Traveller Concurrent program    35

                        PO Output for Communication    29

                        Posting: Single Ledger    15

                        F1 Purchase Order Report    3

                        Interface Trip Stop    2

                        Journal Import    2

                        Subledger Accounting Balances Update    2

                        F1 AR Aging by Receipt Date    2

                        Open Account Balances Data Manager Worker Process    2

                        Accounting Program    2

                        Create Accounting    2

                        Open Account Balances Data Manager    2

                        F1 Check Voucher Report    1

                        Record Print Status    1

                        F1 Check Printing    1

                         

                        If I count the whole week ( AND trunc(a.actual_start_date) > SYSDATE -7)

                        The count is 63,824

                         

                        Process transaction interface    27737

                        WIP Move Transaction Manager    18324

                        Format Automatic Remittances Program    18324

                        Workflow Background Process    11610

                        Manager: Lot Move Transactions    1883

                        OAM Applications Dashboard Collection    900

                        Shipping Exceptions Report    776

                        Actual Cost Worker    770

                        Autoinvoice Master Program    285

                        Autoinvoice Import Program    278

                        Receiving Transaction Processor    265

                        Receipt Traveller Concurrent program    265

                        F1 Delivery Receipt and Sales Invoice Template    184

                        PO Output for Communication    121

                        F1 Check Voucher Report    111

                        F1 Check Printing    68

                        Record Print Status    68

                        F1 Salazar Daily Sales Template Loading    41

                        F1 Salazar Daily Sales Interface    25

                        DQM Serial Sync Index Program    25

                        Posting: Single Ledger    23

                        F1 Item Expiration Monitoring Report    20

                        Inventory transaction worker    20

                        @Tax Preference Report    18

                        Transaction register    18

                        F1 Purchase Order Report    15

                        Journal Import    11

                        Print Remittances    10

                        Automatic Remittances Creation Program (API)    10

                        Automatic Receipts/Remittances Execution Report    10

                        Interface Trip Stop    9

                        View Multiple  Organization  Quantity Report    6

                        F1 BIR FORM 1601-E Monthly Remittances Return    4

                        Inventory Value Report - by Subinventory    4

                        F1 AR Aging by Receipt Date    4

                        Withholding Tax by Supplier Report    3

                        F1 Sales Report    2

                        Subledger Accounting Balances Update    2

                        Open Account Balances Data Manager Worker Process    2

                        Create Accounting    2

                        Accounting Program    2

                        Auto Ship Confirm Report    2

                        Open Account Balances Data Manager    2

                        F1 PO Payment Schedule Report    1

                        Initialize Credit Summaries Table    1

                        Purge Order Management Workflow    1

                        F1 Daily Cash Position Report    1

                        F1 Pending PO Report    1

                        F1 PO Matching Report    1

                        Receiving Account Distribution Report    1

                         

                         

                        Please help ....there must be programs being wrongly or inadvertently scheduled to  run many times by the users?

                        Hence resulting to performance degradation and generating lots of archivelogs?

                        • 9. Re: Lots Archive log generated
                          Dawaa Tamangg

                          Hi,

                           

                          Please do check this two factors also along with others LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT.

                           

                          Regards

                          Dawaa Tamang

                          • 10. Re: Lots Archive log generated
                            Kanda-Oracle

                            Hi JC,

                             

                            You can use this query to find out volume of daily concurrent requests.

                             

                             

                            SELECT trunc(REQUESTED_START_DATE), count(*)

                            FROM FND_CONCURRENT_REQUESTS

                            WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate

                            group by rollup(trunc(REQUESTED_START_DATE)) ;

                             

                            If there is a spike on the concurrent request submitted, it can have some impact on the redo log generation.

                             

                            Thanks


                            N Kandasamy

                            • 11. Re: Lots Archive log generated
                              Beauty_and_dBest

                              Thanks Kanda,

                               

                                1  SELECT trunc(REQUESTED_START_DATE), count(*)

                                2  FROM FND_CONCURRENT_REQUESTS

                                3  WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate

                                4* group by rollup(trunc(REQUESTED_START_DATE))

                               

                              TRUNC(REQUESTED   COUNT(*)

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

                              13-NOV-17             1212

                              14-NOV-17             6293

                              15-NOV-17             9350

                              16-NOV-17            10600

                              17-NOV-17            10233

                              18-NOV-17             9832

                              19-NOV-17             9083

                              20-NOV-17            10301

                              21-NOV-17             9697

                              22-NOV-17             9204

                              23-NOV-17             9314

                              24-NOV-17             9867

                              25-NOV-17             9533

                              26-NOV-17            10671

                              27-NOV-17            11810

                              28-NOV-17            12149

                              29-NOV-17            12076

                              30-NOV-17            10937

                              01-DEC-17            11413

                              02-DEC-17            11745

                              03-DEC-17            11088

                              04-DEC-17            12194

                              05-DEC-17            11905

                              06-DEC-17            11357

                              07-DEC-17            13756

                              08-DEC-17            14734

                              09-DEC-17            14369

                              10-DEC-17            13713

                              11-DEC-17            14140

                              12-DEC-17            14289

                              13-DEC-17            11412

                                                          338277

                               

                              32 rows selected.

                               

                               

                               

                              I believe there is something wrong with the frequency scheduled parameter of this top five programs?

                              Maybe the users has wrongfully set the schedule executed it every minutes? instead of every 30 minutes or every hour?

                               

                              Process transaction interface    27737

                              WIP Move Transaction Manager    18324

                              Format Automatic Remittances Program    18324

                              Workflow Background Process    11610

                              Manager: Lot Move Transactions    1883

                              • 12. Re: Lots Archive log generated
                                Kanda-Oracle

                                It (frequency) depends on the business requirement. Hence you can discuss internally with your functional team to decide the frequency.

                                • 13. Re: Lots Archive log generated
                                  Beauty_and_dBest

                                  Thanks Kanda,

                                   

                                   

                                  Can I say 100% sure that one of the following programs is causing heavy updates? How do I know which table  was affected? It should be a big table, right?

                                   

                                  Process transaction interface    27737

                                  WIP Move Transaction Manager    18324

                                  Format Automatic Remittances Program    18324

                                   

                                   

                                   

                                  Kind regards,

                                  • 14. Re: Lots Archive log generated
                                    Kanda-Oracle

                                    You could be right.. you can also run AWR during that time..

                                     

                                    1. Look for DML that is expensive.
                                    2. Look at the Segment Statistics

                                    Segments by Logical Reads

                                    Segments by Physical Reads

                                    :

                                    :

                                    Segments by Physical Writes

                                    1 2 Previous Next