1 2 3 4 Previous Next 51 Replies Latest reply on Aug 2, 2019 6:52 AM by Beauty_and_dBest

    How to monitor specific concurrent program?

    Beauty_and_dBest

      EBS R12.1.3

      11gR2

      OL6

       

      Hi ALL,

       

      We have concurrent a program that is running very long time.

      Please share what sql scripts to monitor what this concurrent program is doing, or what table is it accessing now, whether it is doing insert, update, delete, or whether it is locked up, or is it lacking TEMP & ROLLBACK space/segments?

      Right now is should be inserting 250,000 rows to an interface table, but does the commit after some more sql process. The commit is at the end of the group/batch of sql's.

      How do I check that it is doing insert? I count the table but still no rows since it does not do commit per line.

      How do I know what is happening to this program?

       

       

      Please help...

       

       

      Kind regards,

      jc

        • 1. Re: How to monitor specific concurrent program?
          3244661

          please use the below script to find long running concurrent request. 

           

          then , you can find the session id from the request id which running longer and you can easily find the query also.

           

          select

          fcr.request_id,

          fcpv.user_concurrent_program_name request,

          to_char(fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') started,fu.user_name "Submittedby",

          round((nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date)*1440,2) "TimeSpent(Min)"

            from

          apps.fnd_concurrent_programs_vl fcpv,

          apps.fnd_concurrent_requests fcr,

          apps.fnd_user fu

          where

          fcpv.concurrent_program_id = fcr.concurrent_program_id

          and fcr.requested_by=fu.user_id

          and

          fcpv.application_id = fcr.program_application_id

          and

          fcr.status_code = 'R'

          and

          fcr.phase_code = 'R'

          • 2. Re: How to monitor specific concurrent program?
            Maaz Khan

            Hi Jc,

             

            I would first get sid from request id -

             

            Query1-

            SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID

            FROM apps.fnd_concurrent_requests a,

            apps.fnd_concurrent_processes b,

            v$process c,

            v$session d

            WHERE a.controlling_manager = b.concurrent_process_id

            AND c.pid = b.oracle_process_id

            AND b.session_id=d.audsid

            AND a.request_id = &Request_ID

            AND a.phase_code = 'R';

             

             

            Get current sql using below query passing sid from query1-

            Query2-

            select a.sid, a.serial#, b.sql_text

               from v$session a, v$sqlarea b

                where a.sql_address=b.address

                 and a.sid = &sid;

             

             

            Get more details using below passing sid from query1 -

             

            set head off

            set verify off

            set echo off

            set pages 1500

            set linesize 100

            set lines 120

            prompt

            prompt Details of SID / SPID / Client PID

            prompt ==================================

            select /*+ CHOOSE*/

            'Session  Id.............................................: '||s.sid,

            'Serial Num..............................................: '||s.serial#,

            'User Name ..............................................: '||s.username,

            'Session Status .........................................: '||s.status,

            'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,

            'Server Process ID ......................................: '||p.spid Server,

            'Sql_Address ............................................: '||s.sql_address,

            'Sql_hash_value .........................................: '||s.sql_hash_value,

            'Schema Name ..... ......................................: '||s.SCHEMANAME,

            'Program  ...............................................: '||s.program,

            'Module .................................................: '|| s.module,

            'Action .................................................: '||s.action,

            'Terminal ...............................................: '||s.terminal,

            'Client Machine .........................................: '||s.machine,

            'LAST_CALL_ET ...........................................: '||s.last_call_et,

            'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600

            from v$session s, v$process p

            where p.addr=s.paddr and

            s.sid=nvl('&sid',s.sid) and

            p.spid=nvl('&spid',p.spid) and

            nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

             

             

             

             

            >>I count the table but still no rows since it does not do commit per line.

            Can you please check code of concurrent program, as it is inserting in your interface table, it will eventually move these records to base tables and purge interface tables.

             

            Regards,

            mAaz

            • 3. Re: How to monitor specific concurrent program?
              Kanda-Oracle

              You can enable the DB trace dynamically..

              • 4. Re: How to monitor specific concurrent program?
                Beauty_and_dBest

                Thanks ALL,

                 

                >>I count the table but still no rows since it does not do commit per line.

                Can you please check code of concurrent program, as it is inserting in your interface table, it will eventually move these records to base tables and purge interface tables.

                 

                Yes it will move to the base tables, but for now we are sure in is on the insert phase and is not yet completed.

                I want to know how to get the cause of the hanging/frozen insert?

                 

                How do you enable db trace dynamically?

                I wonder why sometimes loading 300,000 rows gets completed without issue. But now we have only 250,000 rows. It hangs or takes very long.

                Do all above scripts above be able to show what is the issue? like locking, waiting resources?

                Our work-around was to split the file into 50,000  and all were able to complete successfully.

                What is the solution to be able to load more data in one run? I understand 1 million data should be chicken feed for an Oracle database to handle right?

                 

                 

                Kind regards,

                • 5. Re: How to monitor specific concurrent program?
                  John_K

                  If you have the session id you can query v$session_longops (will only show in here if there is a specific step in a sql statement taking a while such as a full table scan - nested loops etc tend not to show up) to see whether a particular step of a plan is running long, and v$active_session_history to see what is currently being executed. From the session id you can also query v$session to get the sql_id and then query v$sql to see the statement which is being executed.

                   

                  That said - can you use a tool like Toad or SQL Developer which has all these session monitoring utilities built it?

                  • 6. Re: How to monitor specific concurrent program?
                    Beauty_and_dBest

                    Thanks John,

                     

                    How do I get the session id for a concurrent program?

                     

                    We do not use toad as it has license cost.

                    We only use free sqldeveloper, but I can not see its monitoring db performance utilities?

                    Or can you share link if you have one?

                     

                     

                    Kind regards,

                    • 7. Re: How to monitor specific concurrent program?
                      Kanda-Oracle

                      Hi

                       

                      1. You wish to monitor whether it is progressing (if it was doing a full scan or sorting, etc), you may check v$session_longops
                      2. To enable DB trace on the fly ;

                       

                      SQL> SELECT oracle_process_id "Spid"  FROM fnd_concurrent_requests

                      WHERE request_id='&Concurrent Request Id ';

                       

                      sqlplus /nolog

                       

                      SQL> connect / as sysdba

                      SQL> ALTER SESSION SET tracefile_identifier = 'STACK_10046';

                      SQL> oradebug setospid <SPID from Previous Query>

                      SQL> oradebug unlimit

                      SQL> oradebug event 10046 trace name context forever,level 12

                       

                       

                      Thanks!

                      • 8. Re: How to monitor specific concurrent program?
                        Maaz Khan

                        Hi Jc,

                         

                        >>I wonder why sometimes loading 300,000 rows gets completed without issue. But now we have only 250,000 rows. It hangs or takes very long.

                         

                        Can you have 2 awr reports generated when loading 300,000 rows getting completes without issue and one when you have 250,000 rows getting inserted and taking longer duration.

                         

                        @$ORACLE_HOME/rdbms/admin/awrrpt.sql

                         

                        Regards,

                        Maaz

                         

                        • 9. Re: How to monitor specific concurrent program?
                          Beauty_and_dBest

                          Thanks Maaz, Kanda, John, and ALL,

                           

                           

                          Regarding tracing below:

                           

                          1. To enable DB trace on the fly ;

                           

                          SQL> SELECT oracle_process_id "Spid"  FROM fnd_concurrent_requests

                          WHERE request_id='&Concurrent Request Id ';

                           

                          sqlplus /nolog

                           

                          SQL> connect / as sysdba

                          SQL> ALTER SESSION SET tracefile_identifier = 'STACK_10046';

                          SQL> oradebug setospid <SPID from Previous Query>

                          SQL> oradebug unlimit

                          SQL> oradebug event 10046 trace name context forever,level 12

                           

                           

                          Do I run this while the concurrent_request is running? or about to run? What happened if it completed already?

                          Where do I get it output trace, location?

                           

                           

                          Kind regards,

                          • 10. Re: How to monitor specific concurrent program?
                            Kanda-Oracle

                            Hi,

                             

                            Analyzing AWR report (for the given period) is the best approach if the request has already run.

                             

                            The above procedure (run-time / dynamic) , helps when concurrent request is being executed and taking longer time.

                             

                            Thanks!

                            • 11. Re: How to monitor specific concurrent program?
                              Beauty_and_dBest

                              Thanks ALL,

                               

                              I have run the report, but I do not know how to interpret it

                              The report is too long, which part do I need to look into firsts?

                              Can you guide me how to analyze the report and find the issue please?

                               

                              Kind regards,

                              • 12. Re: How to monitor specific concurrent program?
                                Kanda-Oracle

                                you ran awr report for 14:00 to 15:00. was the request running during that time ?

                                • 14. Re: How to monitor specific concurrent program?
                                  Beauty_and_dBest

                                  Hi all, see you tomorrow, going home time here....

                                   

                                  Regards,

                                  1 2 3 4 Previous Next