Re: Database Time Spent Waiting

From: kyle Hailey <kylelf_at_gmail.com>
Date: Wed, 20 Jul 2011 09:15:28 -0700
Message-ID: <CADsdiQjkAr190qRjV794SNEOu=vExQA17Kx=q4HYce1ycFH4jw_at_mail.gmail.com>

The diagnostic pack earns Oracle a fair bit of money, so that's their prerogative especially since it's all packaged up for mass consumption. I'm fine with for charging packaging and automation of a management system - it creates a market opportunity for someone to do it better. I do find it bothersome that one has to pay for the diagnostic data itself, such as ASH, but ASH can be reproduced with S-ASH : http://ashmasters.com/ash-simulation/

It's nice that alerts are part of standard, so metrics must be as well. Statspack is free, which is awesome so there is not as much need for AWR. All that's missing is ADDM. Personally I can do without ADDM and it makes sense for ADDM to be part of the pay for automated diagnostics pack.

• Kyle http://dboptimizer.com

On Wed, Jul 20, 2011 at 8:56 AM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> As much as little :). Incidentally you can see and consume these 'alerts'
> in a standard edition database but can't diagnose/investigate them.. Me
> cynical about the whole diagnostics pack implementation supposedly not being
> a core database feature, nah.
>
> On Wed, Jul 20, 2011 at 4:25 PM, kyle Hailey <kylelf_at_gmail.com> wrote:
>
>> 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
>>> *
>>>
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

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

Received on Wed Jul 20 2011 - 11:15:28 CDT