Re: Performance issues with SSD on DELL servers

From: Timur Akhmadeev <timur.akhmadeev_at_gmail.com>
Date: Tue, 30 Aug 2016 13:52:40 +0300
Message-ID: <CACGsLC+7vF6dZCU6_xSsXkK36UKdDRYFt4CZdh0bvc0A3chOOw_at_mail.gmail.com>

On Mon, Aug 29, 2016 at 8:06 AM, Apps DBA <dbaorapps_at_gmail.com> wrote:

> Thanks for your response. I dont see any problem in the wait events,
>

You need to look at the histogram of wait events of a particular type in AWR report or with a direct query:

col event_name format a30

select
-- instance_number inst_id,
-- event_name,
  to_char(begin_interval_time, 'yyyymmdd hh24mi') end_period,

  max(decode(wait_time_milli, 1, wait_count)) "1ms",
  max(decode(wait_time_milli, 2, wait_count)) "2ms",
  max(decode(wait_time_milli, 4, wait_count)) "4ms",
  max(decode(wait_time_milli, 8, wait_count)) "8ms",
  max(decode(wait_time_milli, 16, wait_count)) "16ms",
  max(decode(wait_time_milli, 32, wait_count)) "32ms",
  max(decode(wait_time_milli, 64, wait_count)) "64ms",
  max(decode(wait_time_milli, 128, wait_count)) "128ms",
  max(decode(wait_time_milli, 256, wait_count)) "256ms",
  max(decode(wait_time_milli, 512, wait_count)) "512ms",
  max(decode(wait_time_milli, 1024, wait_count)) "1024ms",
  max(decode(wait_time_milli, 2048, wait_count)) "2048ms"

from
(
  select
    s.begin_interval_time,
    h.event_name,
    wait_time_milli,
    wait_count - lag(wait_count) over (partition by h.dbid, h.instance_number, h.event_name, h.wait_time_milli order by s.begin_interval_time) wait_count
  from
    dba_hist_event_histogram h,
    dba_hist_snapshot s
  where

    h.dbid = s.dbid and
    h.snap_id = s.snap_id and
    h.instance_number = s.instance_number and
    s.begin_interval_time between sysdate - 14 and sysdate and
    h.event_name in ('&event_name')

)
group by event_name, to_char(begin_interval_time, 'yyyymmdd hh24mi') order by 2
;

And yes, few AWR/ASH/ADDM reports covering good/bad periods is a good starting point.

-- 
Regards
Timur Akhmadeev

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

Received on Tue Aug 30 2016 - 12:52:40 CEST