How to find Sessions generating high redo_archives in Oracle

12345678910111213141516171819202122232425262728293031323334	Run the below query tofind out the archive generation/hour forlast one week. SELECT FROM(SELECT FROM(SELECT TO_CHAR(FIRST_TIME,'DD/MM')AS"DAY",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999')"00:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999')"01:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999')"02:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999')"03:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999')"04:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999')"05:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999')"06:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999')"07:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999')"08:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999')"09:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999')"10:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999')"11:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999')"12:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999')"13:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999')"14:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999')"15:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999')"16:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999')"17:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999')"18:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999')"19:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999')"20:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999')"21:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999')"22:00",TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999')"23:00"FROMV$LOG_HISTORYWHERE extract(year FROM FIRST_TIME)=extract(year FROM sysdate)GROUP BY TO_CHAR(FIRST_TIME,'DD/MM'))ORDER BY TO_DATE(extract(year FROM sysdate)		DAY,'YYYY DD/MM')DESC)WHERE ROWNUM<8;

If there is a drastic change in redo/archive generation run below queries.. Get the segment that experienced the most changes during a specific period. This is helpful in tracking history data.Note:- Change the date ranges according to the time frame.

1234567891011121314

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI')snap_time, dhsso.object_name, sum(db_block_changes_delta) FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhsso, dba_hist_snapshot dhs WHERE dhs.snap_id=dhss.snap_id ANDdhs.instance_number=dhss.instance_number ANDdhss.obj# = dhsso.obj# ANDdhss.dataobj# = dhsso.dataobj# ANDbegin_interval_time BETWEEN to_date('2013_10_22 12','YYYY_MM_DD HH24') ANDto_date('2013_10_23 12','YYYY_MM_DD HH24') GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'), dhsso.object_name order by3desc;

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.The methods are:1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.The query you can use is:

12345	SQL>SELECTs.sid,s.serial#, s.username, s.program, 2 i.block_changes 3 FROMv$sessions,v$sess_ioi 4 WHEREs.sid=i.sid 5 ORDER BY5desc,1,2,3,4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.2) Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).The query you can use is:

12345	SQL>SELECTs.sid,s.serial#, s.username, s.program, 2 t.used_ublk,t.used_urec 3 FROMv$sessions,v$transactiont 4 WHEREs.taddr=t.addr 5 ORDER BY5desc,6desc,1,2,3,4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Anil Panda

Lead DBAAnil is working as a DBA Lead in a reputated MNC. He loves to watch Cricket and WWE in his leisure time. He is always busy with helping others. Lastly he is a great fan of Mr. Sachin Tendulkar (Cricketer)._Related_How to install Memcache with LAMP on Ubuntu 12.04July 25, 2013In "Apache"How to Collect AWS EC2 – Instance MetadataApril 1, 2013In "AWS"How To Block Exploits, SQL Injections, File Injections, Spam, User Agents, Etc. to your NginxSeptember 9, 2013In "Nginx"

					Categories: [Database](http://www.linuxfunda.com/category/database/), [Oracle](http://www.linuxfunda.com/category/database/oracle/)
				Tags: [Oracle](http://www.linuxfunda.com/tag/oracle/)##

One thought on “How to find Sessions generating high redo/archives in Oracle” Reply Dinesh Patil on November 8, 2013 at 1:26 amsaid:Good article .It really worked in our case to trace out the candidate object and programs responsible forheavy archive generation.Once we know the segment with heavy block changes we can get the SQL information related to those objects:We can use below query to trace the sql statement causing heavy block changes on identified segment . Provide the time duration of heavy archive generation and identified segment as per article in filter condition as %segmentname%’ given below.SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’),

     dbms_lob.substr(sql_text,4000,1),
     dhss.instance_number,
     dhss.sql_id,executions_delta,rows_processed_delta

FROM dba_hist_sqlstat dhss,

     dba_hist_snapshot dhs,
     dba_hist_sqltext dhst

WHERE upper(dhst.sql_text) LIKE ‘%segmentname%’

AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number

AND begin_interval_time BETWEEN to_date(‘2013_06_22 17′,’YYYY_MM_DD HH24’)

                                       AND to_date(‘2013_07_22 21′,’YYYY_MM_DD HH24’)
AND dhss.sql_id = dhst.sql_id;Once queries are traced we can fix or reduce the archive generation by possible query optimization.Regards

Dinesh Patil.### Leave a Reply