1 2 Previous Next 15 Replies Latest reply: Aug 27, 2010 7:30 AM by 618702 RSS

    dba_outstanding_alerts-addm

    dbaforu
      can any one tell me ADDM. I have got message from dba_outstanding_alerts.


      EVENT_CLASS Warning 5

      Metrics "Database Time Spent Waiting (%)" is at 60.98381 for event class "Other"
      Run ADDM to get more performance analysis about your system.

      please clarify this alert.
        • 1. Re: dba_outstanding_alerts-addm
          618702
          Dear dbaforu,

          ADDM will tell you about the "Other" wait event type IF there are problems about the "Other" event type.

          Since you have seen such information, you can simply see the ADDM findings based on the AWR reports. After then you may have an information about the Other wait event type and the problem.

          Hope That Helps.

          Ogan
          • 2. Re: dba_outstanding_alerts-addm
            dbaforu
            how to run addm ..can u tell me the procedure elaborately.I have searched google but there are lot of links,that fustrates me.
            • 3. Re: dba_outstanding_alerts-addm
              618702
              dbaforu,

              Do you have the Enterprise Manager?

              If you installed the EM than please go to the Performance tab and click on the "Run ADDM Now" button. It will generate an AWR report and compare it with the latests AWR report. Under normal circumstances an Oracle database takes an AWR snapshot every 1 hour. This period could be changed though.

              If you do not have the EM then;

              http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm

              Regards.

              Ogan
              • 4. Re: dba_outstanding_alerts-addm
                gjilevski1
                Hi,

                If you use a OEM Database control you will have more information and you will be able to clear the alert easily.

                Regards,
                • 5. Re: dba_outstanding_alerts-addm
                  dbaforu
                  no do not have enterprise manager...can u tell me to run it manually
                  • 6. Re: dba_outstanding_alerts-addm
                    618702
                    dbaforu,

                    I presume you have not read the documentation that i have pointed out;

                    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm

                    +6.2.4.1 Running ADDM Using addmrpt.sql+

                    To invoke ADDM analysis for the scenario previously described, you can simply run the addmrpt.sql script at the SQL prompt:

                    +@$ORACLE_HOME/rdbms/admin/addmrpt.sql+

                    When running the addmrpt.sql report to analyze the specific time period in the example scenario, you need to:

                    +1.+

                    Identify the last snapshot that was taken before or at 7 P.M. and the first snapshot that was taken after or at 9 P.M. of the previous night from the list of recent snapshots that the report initially displays. The output is similar to the following:

                    Listing the last 3 days of Completed Snapshots
                    +...+
                    Snap
                    Instance     DB Name        Snap Id    Snap Started    Level
                    ------------ ------------ --------- ------------------ -----
                    main         MAIN               136 20 Oct 2003 18:30      1
                    +137 20 Oct 2003 19:00 1+
                    +138 20 Oct 2003 19:30 1+
                    +139 20 Oct 2003 20:00 1+
                    +140 20 Oct 2003 20:30 1+
                    +141 20 Oct 2003 21:00 1+
                    +142 20 Oct 2003 21:30 1+

                    +2.+

                    Provide the snapshot Id closest to 7 P.M. when prompted for the beginning snapshot and the 9 P.M. snapshot Id when prompted for the ending snapshot.

                    Enter value for begin_snap: 137
                    Begin Snapshot Id specified: 137

                    Enter value for end_snap: 141
                    End   Snapshot Id specified: 141

                    +3.+

                    Enter a report name or accept the default name when prompted to specify the report name.

                    Enter value for report_name:
                    Using the report name addmrpt_1_137_145.txt
                    Running the ADDM analysis on the specified pair of snapshots ...
                    Generating the ADDM report for this analysis ...

                    After the report name is specified, ADDM analysis over the specific time frame is performed. At the end of the analysis, the SQL script displays the textual ADDM report of the analysis. You can review the report to find the top performance issues affecting the database and possible ways to solve those issues.

                    Instructions for running the report addmrpt.sql in a non-interactive mode can be found at the beginning of the $ORACLE_HOME/rdbms/admin/addmrpt.sql file.

                    Ogan
                    • 7. Re: dba_outstanding_alerts-addm
                      618702
                      dbaforu,

                      Let me show the AWR script just in case you may want here;

                      http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm

                      +5.3.5.1 Running the awrrpt.sql Report+

                      To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

                      +@$ORACLE_HOME/rdbms/admin/awrrpt.sql+

                      First, you need to specify whether you want an HTML or a text report.

                      Enter value for report_type: text

                      Specify the number of days for which you want to list snapshot Ids.

                      Enter value for num_days: 2

                      After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

                      Enter value for begin_snap: 150
                      Enter value for end_snap: 160

                      Next, accept the default report name or enter a report name. The default name is accepted in the following example:

                      Enter value for report_name:
                      Using the report name awrrpt_1_150_160

                      The workload repository report is generated.

                      Regards.

                      Ogan
                      • 8. Re: dba_outstanding_alerts-addm
                        618702
                        dbaforu,

                        Let me show the AWR script just in case you may want here;

                        http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm

                        +5.3.5.1 Running the awrrpt.sql Report+

                        To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

                        +@$ORACLE_HOME/rdbms/admin/awrrpt.sql+

                        First, you need to specify whether you want an HTML or a text report.

                        Enter value for report_type: text

                        Specify the number of days for which you want to list snapshot Ids.

                        Enter value for num_days: 2

                        After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

                        Enter value for begin_snap: 150
                        Enter value for end_snap: 160

                        Next, accept the default report name or enter a report name. The default name is accepted in the following example:

                        Enter value for report_name:
                        Using the report name awrrpt_1_150_160

                        The workload repository report is generated.

                        Regards.

                        Ogan
                        • 9. Re: dba_outstanding_alerts-addm
                          dbaforu
                          hi ,
                          Thanks for your help.i have run addmrpt.sql and found aa addm report that specifying the root cause of performance degrade.
                          In that addm report i show u a portin..


                          FINDING 1: 27% impact (3592 seconds)
                          ------------------------------------
                          SQL statements consuming significant database time were found.

                          RECOMMENDATION 1: SQL Tuning, 14% benefit (1879 seconds)
                          ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
                          "azsyrqrjv7cb7".
                          RELEVANT OBJECT: SQL statement with SQL_ID azsyrqrjv7cb7 and
                          PLAN_HASH 765265162
                          DELETE FROM EXP_MSFT WHERE EM_AMY_MSFT = :B1
                          RATIONALE: SQL statement with SQL_ID "azsyrqrjv7cb7" was executed 11
                          times and had an average elapsed time of 182 seconds.

                          hrer addm say's run SQL Tuning Advisor..

                          can u tell me how can i do that..
                          • 10. Re: dba_outstanding_alerts-addm
                            618702
                            Please see below thread. It will guide you;

                            http://www.databasejournal.com/features/oracle/article.php/3492521/Automatic-SQL-Tuning-using-SQL-Tuning-Advisor.htm

                            The package that you are looking for is;

                            DBMS_SQLTUNE;

                            http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#ARPLS220
                            http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#CHEBEBJJ

                            SQL Tuning Advisor

                            The SQL Tuning Advisor is one of a suite of Advisors, a set of expert systems that identifies and helps resolve database performance problems. Specifically, the SQL Tuning Advisor automates the tuning process of problematic SQL statements. That is, it takes one or more SQL statements as input and gives precise advice on how to tune the statements. The advice is provided is in the form of precise SQL actions for tuning the SQL along with their expected performance benefit.

                            Hope That Helps.

                            Ogan
                            • 11. Re: dba_outstanding_alerts-addm
                              gjilevski1
                              Hi,

                              Look at the post for an idea to create a tuning task for SQL Tuning advisor.

                              http://www.databasejournal.com/features/oracle/article.php/3492521/Automatic-SQL-Tuning-using-SQL-Tuning-Advisor.htm

                              Regards,
                              • 12. Re: dba_outstanding_alerts-addm
                                dbaforu
                                hi,
                                need some info about segment tuning advisory.
                                • 13. Re: dba_outstanding_alerts-addm
                                  618702
                                  Please refer to;

                                  http://www.dba-oracle.com/t_segment_advisor_10g.htm
                                  http://www.dba-oracle.com/oracle10g_tuning/t_oem_dbms_advisor.htm

                                  Ogan
                                  • 14. Re: dba_outstanding_alerts-addm
                                    dbaforu
                                    any other details document...ORACLE document??
                                    1 2 Previous Next