Re: Database Time Spent Waiting

From: kyle Hailey <kylelf_at_gmail.com>
Date: Wed, 20 Jul 2011 08:25:27 -0700
Message-ID: <CADsdiQhqLOFE=Pm6qj9r4xEw90gn9ZgYcDastt1Zoj_0Ofv8Yg_at_mail.gmail.com>

The alert is of little use in itself.
Check ADDM, ASH report or AWR for that time period to find out what is happening:

select output
from table(

   dbms_workload_repository.ash_report_text(     (select dbid from v$database),

        1, -- instance id
        sysdate - 2/24, -- startdate
        sysdate - 1/24, -- enddate
        0)) ;

ADDM report manually (I'd normally use OEM for ADDM reports)

select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks
where task_id = (

                  select max(t.task_id)
                  from   dba_advisor_tasks t,
                         dba_advisor_log l
                  where t.task_id = l.task_id and
                        l.status = 'COMPLETED' and
                        l.execution_end < sysdate - 2/24 and -- end date
            t.advisor_name = 'ADDM');

• Kyle http://dboptimizer.com

On Wed, Jul 20, 2011 at 2:56 AM, mek s <sidi.bouzid.meknessy_at_gmail.com>wrote:

> *Hi Experts,
>
> In my RAC 11gR2 environments, we daily have these warnings in DBA_OUTSTANDING_ALERTS
> view.
> I would like to anticipate any kind of future problems, Could you please
> let me know how to troubleshoot these warnings and know the root cause?
>
> Thanks*
>
>
> *REASON
>
> ----------------------------------------------------------------------------------------------------
> METRIC_VALUE MESSAGE_TYPE TO_CHAR(CREATION_TIME,'DD-MON
> ------------ ------------ -----------------------------
> HOST_ID
>
> ----------------------------------------------------------------------------------------------------
> Metrics "Database Time Spent Waiting (%)" is at 67.9796 for event class
> "Other"
> 67.9796018 Warning 20-JUL-2011 11:38:09
> db02
>
> Metrics "Database Time Spent Waiting (%)" is at 38.06099 for event class
> "Other"
> 38.0609854 Warning 20-JUL-2011 11:35:46
> db04
> *
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 20 2011 - 10:25:27 CDT