1 2 Previous Next 24 Replies Latest reply: Aug 9, 2013 12:20 AM by sybrand_b RSS

    AWR, ASH, ADDM

    yxes2013

      Hi all,

       

      11.2.0.1

      Aix 6.1 5L (quadcore, 16GRam)

       

      I am still confused how to take full advantage of these monitoring tools.

       

      Actually the our database performance is currently satisfactory, except for occasional few minutes spikes of CPU highs > 80 .

      I just want to catch the culprit process/program responsible for this spikes, as I have no I idea who it is.

       

      Is it wise to run ASH, AWR, ADDM with an input from time 1AM to 1AM next day?

      What I mean is I will analyze a 1-day period, so that I can catch the program/process that has the higest cpu/memory usage for the day.

       

      Is this possible?

       

       

      Thanks a lot,

       

      zxy

        • 1. Re: AWR, ASH, ADDM
          DK2010

          Hi

           

          >>except for occasional few minutes spikes of CPU highs > 80

          Why not use the OS level command like topas and sar(for history value)

           

          >>Is it wise to run ASH, AWR, ADDM with an input from time 1AM to 1AM next day?

          i beleive no, you can not get specific info for  such long interval.


          are you useing OEM for Your database ? sometime OEM java process consume high OS resources,

           

          HTH

          • 2. Re: AWR, ASH, ADDM
            Jonathan Lewis

            A five minute spike will disappear in a 24 hour average.

             

            AWR and ADDM run every hour by default - run the ADDM report for the hour holding the spike and you may get lucky, but even a one hour average may hide the spike.  Run the ash report (or start with just the OEM Active session screen) for the few minutes around the spike. (See $ORACLE_HOME/rdbms/admin/ashrpt.sql)

             

            Regards

            Jonathan Lewis

            http://jonathanlewis.wordpress.com

            Now on Twitter: @jloracle

            • 3. Re: AWR, ASH, ADDM
              Mr.D.

              DK2010 ha scritto:

               

              Hi

               

              >>except for occasional few minutes spikes of CPU highs > 80

              Why not use the OS level command like topas and sar(for history value)

               

              >>Is it wise to run ASH, AWR, ADDM with an input from time 1AM to 1AM next day?

              i beleive no, you can not get specific info for  such long interval.


              are you useing OEM for Your database ? sometime OEM java process consume high OS resources,

               

              HTH

               

              Talk with your SO Admin and produce a nmon report. you can monitor your system with topas as DK2010 says.

               

              ASH, AWR uses snapshots and default retantion is 31 days (if i remember).

              So you can execute a report of 1 day, 2 days, 7 days.

              As I have already said in another post you ca use ASH Viewer to graphically view your database.

              You need to install it on your pc.

               

              Regards

              • 4. Re: AWR, ASH, ADDM
                yxes2013

                Are you useing OEM for Your database ? sometime OEM java process consume high OS resources,

                We seldom use OEM


                Why not use the OS level command like topas and sar(for history value)

                He already has the monitoring tool plus the graph, but there no corresponing PID or process being captured against the top cpu peak in the graph. or can this be captured in topas or sar?

                • 5. Re: AWR, ASH, ADDM
                  DK2010

                  Hi.

                  >>We seldom use OEM

                  but agent are running continuously on the server , it you are not using stop it .

                  >>He already has the monitoring tool plus the graph, but there no corresponing PID or process being captured against the top cpu peak in the graph. or can this be captured in topas or sar?

                  i donot know which monitoring tool you are using, but. it should be there

                  you can use sar for the history report check with your SA.

                   

                  HTH

                  • 6. Re: AWR, ASH, ADDM
                    yxes2013

                    Run the ash report (or start with just the OEM Active session screen) for the few minutes around the spike. (See $ORACLE_HOME/rdbms/admin/ashrpt.sql)


                    I already run the ASH for the specified timeframe of within 10 minute period, and I got this sql programs captured.

                    A.)

                    SQL*Net break/reset to client    90.71 ** Row Source Not Available **      90.71
                    begin dbms_aqin.aq$_dequeue_in( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :1
                    2, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :2
                    8, :29); end;

                    This dbms_aqin.aq$_dequeue_in is the one running.

                    Is this tunable?


                    B.) How to I resolve enqueque?


                              475hxdks610gr           3555540364                    4          24.24
                    enq: TX - row lock contention    24.24 FOR UPDATE 24.24
                    SELECT isotrcnum FROM SERVICEPROVIDERPROFILE FOR UPDATE


                    C.) How do I resolve sqlnet break/reset


                    ccd38r7qn1mn9           1923507850                    0          18.18
                    SQL*Net break/reset to client    18.18 SELECT STATEMENT                    18.18
                    SELECT TRMNL_STATUS.cntrlid cntrlid,TRMNL_STATUS.mrchid mrchid,TRMNL_STATUS.dtcd
                    e dtcde,TRMNL_STATUS.stats stats,TRMNL_STATUS.termid termid,NVL(BATCH_SETTLE.adr
                    sncod, 0) adrsncod FROM TRMNL_STATUS TRMNL_STATUS,BATCH_SETTLE BATCH_SETTLE WHE
                    RE TRMNL_STATUS.cntrlid=3 AND TRMNL_STATUS.mrchid=0 AND TRMNL_STATUS.dtcde=1 AND


                    • 7. Re: AWR, ASH, ADDM
                      Mr.D.

                      >  How do I resolve sqlnet break/reset

                      How do I resolve sqlnet break/reset

                       

                      > How to I resolve enqueque?

                      Talk with developer and remove FOR UPDATE.

                       

                       

                      • 8. Re: AWR, ASH, ADDM
                        Aman....

                        The question is that if the performance of the database is satisfactory, do you really feel that tuning those unoften spikes would really be helpful? You said you seldom use EM but may I say that you are doing it all wrong if you don't know how to use AWR, ASH etc but still don't use EM?

                         

                        Aman....

                        • 9. Re: AWR, ASH, ADDM
                          jgarry

                          EM uses some of the same tables in its performance screens.  You might seriously consider looking at top activity when you have the performance spike.  It color-codes the spike and can list the sessions - so if it is something in Oracle, you can often drill right to it (or sometimes not, I've seen log writer be the culprit and not give what it is doing - but at least you know it is log writer - looking at it right now, the "unknown SQL" is log file parallel write), and if it is something outside of Oracle, that may become obvious too.  I get a lot of the latter from my app, I see a big light-green CPU "other" on the home screen I know to look at top.

                           

                          I notice that after the log writer and db writer have some waits, OMS starts being a piggy.  Sometimes also in the late afternoon when most user activity drops off.

                          • 10. Re: AWR, ASH, ADDM
                            yxes2013

                            I thank you all

                             

                            I am really still confused how to maximize usage of ASH, AWR, ADD.

                            Does it mean I have to run each tool to get reports every hour, for 24 hrs, just to be able to get the TOP CPU for the hour in 1 day?

                            There is no shortcut "select" command the will get which hour of the day has the top cpu usages?

                             

                             

                            Thanks again

                            • 11. Re: AWR, ASH, ADDM
                              yxes2013

                              Hi MrD,

                              > How to I resolve enqueque?

                              Talk with developer and remove FOR UPDATE.

                               

                              Is there no change in program logic if the dev remove this?

                              I am afraid the it will mess the output

                               

                              Thanks,

                              • 12. Re: AWR, ASH, ADDM
                                jgarry

                                It does change the program logic.  The question is, is the logic correct?  I had a dynamic code-generation environment that defaulted inquiry-only programs to FOR UPDATE.

                                 

                                There might be some better answer about using Oracle tools for OS problems, but I'm not all that expert on them, I just use OS tools.  I use Oracle tools for figuring out which Oracle problem to solve, or whether to look elsewhere.  But see AWR reports: interpreting CPU usage | Oracle Diagnostician

                                • 13. Re: AWR, ASH, ADDM
                                  Aman....

                                  Why do you think that removing the FOR UPDATE would change the output?

                                   

                                  Aman....

                                  • 14. Re: AWR, ASH, ADDM
                                    yxes2013

                                    Thanks dear

                                     

                                    Then why dooes Oracle provide that command option FOR UPDATE when It is not good to use after all?

                                    1 2 Previous Next