SQL: How to Find Sessions Generating Lots of Redo or Archive logs

mosdoc發表於2017-01-04
SQLSessionHive
SQL: How to Find Sessions Generating Lots of Redo or Archive logs (文件 ID 167492.1)
**Checked for relevance on 13-Oct-2015*** 
    
	
	
  • 
		goal: How to find sessions generating lots of redo
	
    • 

	
  • 
		fact: Oracle Server - Enterprise Edition 8
	
    • 

	
  • 
		fact: Oracle Server - Enterprise Edition 9
	
    • 

	
  • 
		fact: Oracle Server - Enterprise Edition 10
	
    • 



fix:

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:
       SQL> SELECT s.sid, s.serial#, s.username, s.program,
         2  i.block_changes
         3  FROM v$session s, v$sess_io i
         4  WHERE s.sid = i.sid
         5  ORDER BY 5 desc, 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:
      SQL> SELECT s.sid, s.serial#, s.username, s.program, 
        2  t.used_ublk, t.used_urec
        3  FROM v$session s, v$transaction t
        4  WHERE s.taddr = t.addr
        5  ORDER BY 5 desc, 6 desc, 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.



                                                    

                                        

                    

                                                    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31393455/viewspace-2131912/，如需轉載，請註明出處，否則將追究法律責任。
                                            


                




相關文章