Re: Database Time Spent Waiting

From: mek s <sidi.bouzid.meknessy_at_gmail.com>
Date: Wed, 20 Jul 2011 18:31:14 +0200
Message-ID: <CAJX5RkT6ErMCr6E_M+FWdbNrVVgwRWXG9vHNSdjBe9JaQ4fKvA_at_mail.gmail.com>

Kyle, thank you,
We are using statspack level 7, as we don't have the diagnostic pack license.
Do you know how to troubleshoot these warnings using statspack, I mean the queries and from where I should start looking into...

On Wed, Jul 20, 2011 at 6:15 PM, kyle Hailey <kylelf_at_gmail.com> wrote:

>
> 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:31:14 CDT