ITPub博客

首页 > 数据库 > Oracle > 查看生成redo或者归档较多的session

查看生成redo或者归档较多的session

Oracle 作者:Curry_0316 时间:2015-12-01 20:01:21 0 删除 编辑
文章转自MOS:
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/29953799/viewspace-1850903/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-10-11

  • 博文量
    86
  • 访问量
    244329