High Archives Generated (Which cause performance issue)

High Archives Generated (Which cause performance issue) 

Whenever huge archives generates following are the points to check.

-Check hot backup is in progress or not.

If hot backup is running then moving archives to different mount that's only one solution

-Check after hot backup any tablespace was left in begin backup mode and failed to do end backup for any tablespace

-Check tbs in begin backup mode:

select a.tablespace_name
from sys.dba_data_files a, sys.v_$backup b
where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name;

Make that tablespace to end backup
eg:
alter tablespace end backup;

-Check which session is generating more redo.

rem -----------------------------------------------------------------------
rem Purpose: Transaction which generating more redo
rem This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed
rem by the session. High values indicate a session generating lots of redo.
rem Run the query multiple times and examine the delta between each occurrence
rem of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.
rem -----------------------------------------------------------------------

set pages 1000
set lines 140
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;

rem -----------------------------------------------------------------------
rem Purpose: Transaction which generating more redo
rem Run the query multiple times and examine the delta between each occurrence
rem of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
rem the session.
rem -----------------------------------------------------------------------

SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


Try to find the queries to find the sid, the related sql and the request which was causing the huge number of archives.

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

The output of the query gives the sid, number of undo blocks used and Number of undo records used that were happening during that time.

From the sid we can get the request id and the request name.

select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;

with above query we can find the details and contact application team to stop the activity and schedule at non - peak hours of business.