Re: Huge Archive generation causing instance to hang

From: Vasu <vasudevanr_at_gmail.com>
Date: Sun, 6 Jan 2013 15:34:35 -0600
Message-ID: <CA+O6cLJ+L0itHSAfwjEYjYVhkh=5r=DgshL-JXZ8bkrX=o=YZg_at_mail.gmail.com>

Comparing the previous and current block change count(segment-wise) helped me in the past.
The rough steps are :
1. Get prev and current date snapshot range by running this script (sqlplus _at_snap.sql)

• snap.sql - get snap ids ------------ set verify off alter session set nls_date_format='YYYYMMDD.HH24MISS'; select distinct snap_id from dba_hist_snapshot a where a.begin_interval_time = (select min(a.begin_interval_time) from dba_hist_snapshot a where a.begin_interval_time>=to_date('&idate')); exit;

  ---

  Run the above script 4 times, to get previous date start and end snaps, and repeat it for the next day. 2. ---------- SQL to verify segment-wise block change history , substitute snap-id's here ----------- select a.instance_number,a.owner,a.object_name,a.blkchg,b.blkchg,trunc(((b.blkchg/a.blkchg)-1)*100) from
  (
  select a.instance_number,b.owner,b.object_name, sum(a.db_block_changes_delta) blkchg, sum(a.physical_writes_delta) wrts from dba_hist_seg_stat a, dba_hist_seg_stat_obj b where a.snap_id between 99884 and 99931 -- use prev day snap range and b.dataobj#=a.dataobj# and owner in ('SCHEMA1','SCHEMA2') -- MAJOR SCHEMAS group by a.instance_number,b.owner,b.object_name having sum(a.db_block_changes_delta) > 5000 -- min block change to consider , raise or lower this number order by a.instance_number,owner, 4 desc ) a,
  (
  select a.instance_number,b.owner,b.object_name, sum(a.db_block_changes_delta) blkchg, sum(a.physical_writes_delta) wrts from dba_hist_seg_stat a, dba_hist_seg_stat_obj b where a.snap_id between 99932 and 99979 -- use curr.day snap range and b.dataobj#=a.dataobj# and owner in ('SCHEMA1','SCHEMA2') group by a.instance_number,b.owner,b.object_name having sum(a.db_block_changes_delta) > 5000 order by a.instance_number,owner, 4 desc ) b where a.instance_number=b.instance_number and a.owner=b.owner and a.object_name = b.object_name
  • and b.blkchg > (a.blkchg *1.20) -- % increase filter OPTIONAL order by a.instance_number, a.blkchg desc

    ---

    In the above SQL , the 1st Sub-Query is to list block changes for prev-day & next is for the current/problematic day. If new segments were created the next day, you need to adjust the query to get them..(using outer join or run sub-query separately).

Thanks,
Vasu
PS- its a delayed reply.. posting it as this may be of use/corrected further.

Date: Mon, 31 Dec 2012 09:41:32 +0800
Subject: Re: Huge Archive generation causing instance to hang From: Sriram Kumar <k.sriramkumar_at_gmail.com>

Hi,
Not sure what version you are on. AWR might not directly indicate which segment caused high redo. if you have tuning and diagnostic license, you could query dba_hist_seg_stat for the time period to identify the segments with maximum block changes and then you could progressively drill down into dab_hist_sqlstat to identify the exact sql statements that caused the redo activity

best regards

sriram kumar

On Mon, Dec 31, 2012 at 2:00 AM, Apps DBA <dbaorapps_at_gmail.com> wrote:

> Hi Gurus,
> I am a newbie in reading AWR and concluding the issue lies at some point.
> Would require your advice in knowing where the issue lies during my
> production hung due to high archive generation today? Basically, redo
> generation bumped up caused the alerting mechanism to fail resulting in
> instances hung state. Please help me in understanding and analyzing the

AWR

> report.
>
> Thanks,
> RK
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l

-- 
-Vasu


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

Received on Sun Jan 06 2013 - 22:34:35 CET