1 2 3 4 Previous Next 51 Replies Latest reply on Aug 2, 2019 6:52 AM by Beauty_and_dBest Go to original post
      • 15. Re: How to monitor specific concurrent program?
        Kanda-Oracle

        Hi,

         

        You may just click on 'SQL Statistics' (under 'Main Report') and look for expensive DMLs .

         

        You need to check out 'module' and match it with your problematic report

         

        Hope this helps!

         

         

        Thanks


        N Kandasamy

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

          Thanks Kanda and ALL,

           

          So the first thing to do in analyzing performance issue in AWR is:

           

          1. Click on 'SQL Statistics' (under 'Main Report') and look for expensive DMLs . Check out 'module' and match it with your problematic report

           

           

          Usually I find expensive sql, but I do not know which concurrent program they belong

          That is always my struggle. Is there a way to find out which concurrent program does this expensive sql belong to?

          Or that the case will always be, you get a problematic concurrent program, and find its sql in AWR report?

          The struggle also (if you are not a developer) is how do I know how to find the sql of the certain concurrent program?

           

           

          Kind regards,

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

            Hi

             

            1. As mentioned, note down 'module' .
              1. It gives the form name (if its a .fmb)
              2. (or)  Short name of the concurrent request.
                • You may find out the short name of the concurrent program as follows.SYSADMIN > Concurrent > Program > Define > Query the program (using concurrent request name) > find out short name.

             

            Hope this helps!

             

            Thanks!

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

              Hi

               

              In addition to the above,  You need to pay attention first to 'Elapsed Time' & 'DB Time'. If you find the 'DB time' is very high then there is an issue.

               

              In your case DB time is very less ; suggests there were no major issues.

               

              Thanks!

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

                Thanks Kanda,

                 

                I will check again the previous dates of AWR and check for issues....brb

                 

                 

                Kind regards,

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

                  Hi Kanda, Maaz, and ALL,

                   

                  Below are the two similar loading programs which have different performance behavior.

                  The one with more rows succeeded/ completed in 1+ hrs , but the second run hung and was terminated after 7 hrs.

                  Could there be locking issue?

                   

                   

                  1) Data insert loading 1

                  DATE: 23-JUL-19 -- successful process with 300k+ records

                  Start Time: 03:18 AM

                  End Time : 04:45 AM

                   

                  2.) Data insert loading 2

                  DATE: 24-JUL-19 --- issue encountered with 200k+ records

                  Start Time: 01:50 AM -- terminated after 7 hrs

                   

                   

                  AWR of #1

                  ==========

                  WORKLOAD REPOSITORY report for 

                   

                  DB NameDB IdInstanceInst numStartup TimeReleaseRAC
                  PROD157292119PROD124-Jun-19 05:0611.1.0.7.0NO

                   

                   

                  Host NamePlatformCPUsCoresSocketsMemory (GB)
                  prod.oracle.comLinux x86 64-bit  8  8  2  38.38

                   

                  Snap IdSnap TimeSessionsCursors/Session
                  Begin Snap:7311923-Jul-19 03:00:31298  25.3
                  End Snap:7312023-Jul-19 04:00:34298  25.5
                  Elapsed:   60.04 (mins)
                  DB Time:   40.97 (mins)

                   

                  Report Summary

                  Cache Sizes

                   

                  BeginEnd
                  Buffer Cache:  11,904M  11,904MStd Block Size:  8K
                  Shared Pool Size:  3,072M  3,072MLog Buffer:  58,728K

                  Load Profile

                   

                  Per SecondPer TransactionPer ExecPer Call
                  DB Time(s):  0.7  0.1  0.01  0.02
                  DB CPU(s):  0.7  0.1  0.01  0.02
                  Redo size:  350,876.9  59,102.6
                  Logical reads:  88,980.8  14,988.2
                  Block changes:  2,598.9  437.8
                  Physical reads:  4.9  0.8
                  Physical writes:  55.6  9.4
                  User calls:  28.4  4.8
                  Parses:  6.6  1.1
                  Hard parses:  0.1  0.0
                  W/A MB processed:  609,524.5  102,669.8
                  Logons:  0.1  0.0
                  Executes:  82.7  13.9
                  Rollbacks:  1.5  0.3
                  Transactions:  5.9

                  Instance Efficiency Percentages (Target 100%)

                   

                  Buffer Nowait %:  100.00Redo NoWait %:  100.00
                  Buffer Hit %:  99.99In-memory Sort %:  100.00
                  Library Hit %:  97.65Soft Parse %:  98.69
                  Execute to Parse %:  91.99Latch Hit %:  100.00
                  Parse CPU to Parse Elapsd %:  0.01% Non-Parse CPU:  99.97

                  Shared Pool Statistics

                   

                  BeginEnd
                  Memory Usage %:  42.94  43.04
                  % SQL with executions>1:  93.85  96.38
                  % Memory for SQL w/exec>1:  91.99  96.47

                   

                  Top 5 Timed Foreground Events

                   

                  EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
                  DB CPU 2,426 98.71
                  db file sequential read16,4201310.52User I/O
                  log file sync14,8481110.43Commit
                  SQL*Net more data from client52,628100.05Network
                  local write wait1,182110.05User I/O

                  Host CPU (CPUs: 8 Cores: 8 Sockets: 2)

                   

                  Load Average BeginLoad Average End%User%System%WIO%Idle
                    0.70  1.04  8.7  0.1  0.2  91.2

                  Instance CPU

                   

                  %Total CPU%Busy CPU%DB time waiting for CPU (Resource Manager)
                    8.7  97.9  0.0

                  Memory Statistics

                   

                  BeginEnd
                  Host Mem (MB):  39,299.7  39,299.7
                  SGA use (MB):  15,360.0  15,360.0
                  PGA use (MB):  2,047.3  2,042.4
                  % Host Mem used for SGA+PGA:  44.29  44.29

                  Main Report

                   

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

                   

                   

                  AWR of #2

                  ========

                  WORKLOAD REPOSITORY report for

                  DB NameDB IdInstanceInst numStartup TimeReleaseRAC
                  PROD157292119PROD124-Jun-19 05:0611.1.0.7.0NO
                  Host NamePlatformCPUsCoresSocketsMemory (GB)
                  prod.oracle.comLinux x86 64-bit88238.38
                  Snap IdSnap TimeSessionsCursors/Session
                  Begin Snap:7314124-Jul-19 01:00:2030523.9
                  End Snap:7314224-Jul-19 02:00:2230324.5
                  Elapsed: 60.04 (mins)
                  DB Time: 9.88 (mins)

                  Report Summary

                  Cache Sizes

                  BeginEnd
                  Buffer Cache:11,776M11,776MStd Block Size:8K
                  Shared Pool Size:3,200M3,200MLog Buffer:58,728K

                  Load Profile

                  Per SecondPer TransactionPer ExecPer Call
                  DB Time(s):0.20.10.000.01
                  DB CPU(s):0.20.10.000.01
                  Redo size:14,286.55,654.5
                  Logical reads:18,178.07,194.7
                  Block changes:100.339.7
                  Physical reads:2.30.9
                  Physical writes:2.30.9
                  User calls:22.69.0
                  Parses:6.92.7
                  Hard parses:0.10.0
                  W/A MB processed:554,938.5219,641.2
                  Logons:0.10.0
                  Executes:42.216.7
                  Rollbacks:1.50.6
                  Transactions:2.5

                  Instance Efficiency Percentages (Target 100%)

                  Buffer Nowait %:100.00Redo NoWait %:100.00
                  Buffer Hit %:99.99In-memory Sort %:100.00
                  Library Hit %:97.10Soft Parse %:99.11
                  Execute to Parse %:83.66Latch Hit %:99.99
                  Parse CPU to Parse Elapsd %:0.01% Non-Parse CPU:99.86

                  Shared Pool Statistics

                  BeginEnd
                  Memory Usage %:43.4143.36
                  % SQL with executions>1:94.8796.81
                  % Memory for SQL w/exec>1:95.1897.49

                  Top 5 Timed Foreground Events

                  EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
                  DB CPU 583 98.41
                  db file sequential read7,331611.08User I/O
                  log file sync2,853210.37Commit
                  local write wait594110.10User I/O
                  control file sequential read1,063000.07System I/O

                  Host CPU (CPUs: 8 Cores: 8 Sockets: 2)

                  Load Average BeginLoad Average End%User%System%WIO%Idle
                  0.331.192.30.10.197.6

                  Instance CPU

                  %Total CPU%Busy CPU%DB time waiting for CPU (Resource Manager)
                  2.292.90.0

                  Memory Statistics

                  BeginEnd
                  Host Mem (MB):39,299.739,299.7
                  SGA use (MB):15,360.015,360.0
                  PGA use (MB):1,906.21,906.6
                  % Host Mem used for SGA+PGA:43.9343.93

                  Main Report

                   

                   

                  Please help how to analyze between these two good and bad performance.

                  How do I find the cuase of the issue for job run#2

                   

                   

                  Kind regards,

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

                    To be honest, I think I'd still go with the suggestion of looking at what is actually running when it's hanging. That should (hopefully) let you tie it down to a specific SQL statement.

                     

                    When your concurrent program is running, you can look at active sessions and the SQL they're running using the following statement:

                     

                    select s.sid,
                           s.logon_time,
                           s.program,
                           s.module,
                           s.username,
                           s.osuser,
                           s.machine,
                           s.sql_exec_start,
                           q.sql_id,
                           q.sql_text,
                           s.event,
                           s.wait_class,
                           s.state
                      from v$session s join v$sql q on q.sql_id = s.sql_id
                     where s.status = 'ACTIVE'
                     order by s.sql_exec_start desc;
                    

                     

                    Once you have identified the correct session, you can check current waits for that session (i.e. what is it currently doing?) using

                     

                    select vs.sid,
                           vs.username,
                           vs.status,
                           vs.osuser,
                           vs.sql_address,
                           vsw.event,
                           vsw.state,
                           vsw.wait_class,
                           vsw.wait_time_micro,
                           vsw.time_remaining_micro,
                           vsw.p1,
                           vsw.p1text,
                           vsw.p2,
                           vsw.p2text,
                           vsw.p3,
                           vsw.p3text
                      from v$session_wait vsw, v$session vs
                     where vs.sid = vsw.sid and vs.sid = :sid;
                    

                     

                    and also see the history of what that session has been doing using

                     

                      select vs.sid,
                             vash.sample_time,
                             vash.top_level_sql_id,
                             vash.sql_id,
                             vash.sql_plan_line_id,
                             vash.sql_plan_operation,
                             vash.sql_plan_options,
                             vsp.object_name,
                             vsp.access_predicates,
                             vsp.projection
                        from v$active_session_history vash, v$session vs, v$sql_plan vsp
                       where vs.sid = vash.session_id
                         and vsp.sql_id = vash.sql_id
                         and vsp.id = vash.sql_plan_line_id
                         and vs.sid = :sid
                    order by vs.sid, vash.sample_time desc;
                    

                     

                    If the SQL statement is actually running, then grab a plan for it.

                     

                    select * from table(dbms_xplan.display_cursor(sql_id=>''));
                    

                     

                    inserting the SQL_ID from above.

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

                      Thanks John,

                       

                      So we can not get info hints on AWR report above?

                       

                       

                      Kind regards,

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

                        You can, it's just the above is a bit more focused and looks specifically for the problematic statement and what it is doing. You'll probably get to the root cause of the issue quite a bit faster.

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

                          Hi John,

                           

                          But the performance issue is passed already, I can still used above queries to anaylze past issues?

                           

                           

                          Kind regards,

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

                            if you click 'SQL Statistics' , did Did you find your program in 'module'

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

                              Hi Kanda,

                               

                              The awr range is 5 hours, Is it still okay to check the program? or I need a hr-by-hr snap ?

                               

                              Yes, it is on top:

                               

                               

                              Thanks a lot,

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

                                No - it needs to be running the program to capture what is going on.

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

                                  Hi John, Kanda

                                   

                                  The problematic module is shown above , which is calling other subprograms(UPDATE & INSERT).

                                  Could it be that there is locking issue? or resource contention?

                                   

                                   

                                  Thanks a lot.

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

                                    Well the majority of your time is spent in your custom module - XXCBGLI - can you post the full SQL statement for id 6chg8fu6gsn68 including an execution plan? If there's not been much going on you might be lucky and it'll be in the shared pool:

                                     

                                    select * from table(dbms_xplan.display_cursor(sql_id=>'6chg8fu6gsn68')); 

                                     

                                    otherwise you'll just have to produce a plan again.