Shulou(Shulou.com)05/31 Report--

Oracle redo overdiagnosis is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1. Overdiagnosis of redo

To find a session that generates a large number of redo, you can use any of the following methods. Both methods check the generated withdrawal sales.

When a transaction generates a redo, it automatically generates a redo.

Use the first query when you need to check to generate a large number of programs. Redo when these programs activate multiple transactions.

The latter query can be used to find out which specific transactions are being generated and redone.

1) method 1 (for a program to accumulate redo generation)

Query V$SESS_IO, this view contains the indicated column BLOCK_CHANGES.

How many blocks have been changed in this session. A higher value indicates that the session generates a large number of redo.

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

SID SERIAL# USERNAME PROGRAM BLOCK_CHANGES

331 393 SYS sqlplus@orastb.bonc.com.cn (TNS V1-V3) 210481

241 1 oracle@orastb.bonc.com.cn (SMON) 65308

2) method 2 (for the amount of redo generated by running transactions)

Query V$TRANSACTION. This view contains undo blocks and undo records about transaction information. (in USED_UBLK and USED_UREC)

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

SID SERIAL# USERNAME PROGRAM USED_UBLK USED_UREC

--

1 227 ANDY sqlplus@orastb.bonc.com.cn (TNS V1-V3) 37 2044

Second, how to diagnose excessive redo generation.

High redo usually falls into two categories:

one. At a certain time of day.

two. A sudden increase in archived logs.

In both cases, the first thing to check is any changes at the database level (modification of any parameters, any maintenance operations performed,.) And application level (deploy new applications, modify code, add users,..).

To know the exact reason for the high redo, we need information about the redo activity and detailed information about the load. The following information needs to be collected during high redo generation.

1] to know the trend of log switching, you can use the following query.

SQL > alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

SQL > select trunc (first_time, 'HH'), count (*)

From v$loghist

Group by trunc (first_time, 'HH')

Order by trunc (first_time, 'HH')

TRUNC (FIRST_TIME,'HH COUNT (*)

--

29-MAY-2008 13:00:00 126

29-MAY-2008 14:00:00 135

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.