This blog entry is to discuss a method to identify the objects inducing higher amount of redo. First,we will establish that redo size increased sharply and then identify the objects generating more redo. Unfortunately, redo size is not tracked at a segment level. However, you can make an educated guess using ‘db block changes’ statistics. But, you must use logminer utility to identify the objects generating more redo scientifically.
Detecting redo size increase
AWR tables (require Diagnostics license) can be accessed to identify the redo size increase. Following query spools the daily rate of redo size. You can easily open the output file redosize.lst in an Excel spreadsheet and graph the data to visualize the redo size change. Use pipe symbol as the delimiter while opening the file in excel spreadsheet.
spool redosize.lst
REM You need Diagnostic Pack licence to execute this query!
REM Author: Riyaj Shamsudeen
col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time , startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY startup_time, sysst.instance_number
ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
EXTRACT (DAY FROM (end_interval_time-begin_interval_time))*24*60*60+
EXTRACT (HOUR FROM (end_interval_time-begin_interval_time))*60*60+
EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+
EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA
FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-90
)
select instance_number,
to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt
, sum(stat_value) redo1
from redo_sz
group by instance_number,
to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY')
order by instance_number, 2
/
spool off
Visualizing the data will help you to quickly identify any pattern anomalies in redo generation. Here is an example graph created from the excel spreadsheet and see that redo size increased recently.
Guess the object using ‘db block changes’ statistics
A quick method to guess the objects generating higher redo size is to use ‘db block changes’ statistics. The philosophy behind this technique is that, if the object is modified heavily then that object will probably generate more redo. But, it is not an entirely accurate statement as less frequently modified objects can generate more redo and vice versa. If you are lucky, one or two objects will stand out as a problem and you can review those segments further to reduce redo size.
@segment_stats.sql
To show all segment level statistics in one screen
Enter value for statistic_name: db block changes
old 6: where value >0 and statistic_name like '%'||'&&statistic_name' ||'%'
new 6: where value >0 and statistic_name like '%'||'db block changes' ||'%'
INST_ID STATISTIC_NAME OWNER OBJECT_NAME OBJECT_TYP VALUE PERC
---------- ------------------------------ ------------ ----------------- ---------- ------------ ------
1 db block changes INV SALES_TEMP_N1 INDEX 3831599856 48.66
3 INV MTL_RESERV TABLE 3794818912 23.78
3 ZX DET_FACTORS_ INDEX 2468120576 15.47
2 APPLSYS FND_TAB TABLE 2346839248 16.33
….
Segment_stats.sql script can be found in segment_stats.
Identify objects using logminer
Scientific method to identify the object generating higher redo uses log mining package. Objects can be identified by the following steps:
Step 1: Start log miner from sys or system user in SQL*Plus. Example given here is for finprod2 instance archivelog file.
begin
sys.dbms_logmnr.ADD_LOGFILE ('/opt/app/prod/finprod2/arch/finprod_212_2_1212121221.arch');
end;
/
begin
sys.dbms_logmnr.START_LOGMNR;
end;
/
Step 2: Create a table by querying the data from v$logmnr_contents dynamic performance view. I tend to create a separate table for each archive log file for two reasons: (a) to improve the query performance (b)I haven’t tested thoroughly with multiple archivelog files. Following SQL statement finds the length of redo record by subtracting the RBA (Redo Byte Address) of the current record from the RBA of next record. Redo byte address provides the physical location of a redo record in a redo log file. Using the physical location of current redo record and the next redo record, we can find the length of current redo record.
Update 1: As Greg pointed out in comments section, I was using hard-coded 512 bytes for redo block size in my script, which is true in Solaris and Linux platform. But, in HP platform, redo block size is 1024 bytes. You can use the following SQL statement to identify the redo block size. I have modified the create table script to query redo block size dynamically.
SQL>select max(lebsz) from x$kccle;
MAX(LEBSZ)
----------
512
drop table redo_analysis_212_2; CREATE TABLE redo_analysis_212_2 nologging AS SELECT data_obj#, oper, rbablk * le.bsz + rbabyte curpos, lead(rbablk*le.bsz+rbabyte,1,0) over (order by rbasqn, rbablk, rbabyte) nextpos FROM ( SELECT DISTINCT data_obj#, operation oper, rbasqn, rbablk, rbabyte FROM v$logmnr_contents ORDER BY rbasqn, rbablk, rbabyte ) , (SELECT MAX(lebsz) bsz FROM x$kccle ) le /
Step 3: Query the table to identify the object_name: In this step, we join the table created and obj$ table to identify the objects inducing redo size. Outer join is needed as the object may have been dropped recently. START indicates the redo record for the start of a transaction and COMMIT indicates the redo record for the end of a transaction.
set lines 120 pages 40
column data_obj# format 9999999999
column oper format A15
column object_name format A60
column total_redo format 99999999999999
compute sum label 'Total Redo size' of total_Redo on report
break on report
spool /tmp/redo_212_2.lst
select data_obj#, oper, obj_name, sum(redosize) total_redo
from
(
select data_obj#, oper, obj.name obj_name , nextpos-curpos-1 redosize
from redo_analysis_212_2 redo1, sys.obj$ obj
where (redo1.data_obj# = obj.obj# (+) )
and nextpos !=0 -- For the boundary condition
and redo1.data_obj#!=0
union all
select data_obj#, oper, 'internal ' , nextpos-curpos redosize
from redo_analysis_212_2 redo1
where redo1.data_obj#=0 and redo1.data_obj# = 0
and nextpos!=0
)
group by data_obj#, oper, obj_name
order by 4
/
...
46346 INSERT WSH_EXCEPTIONS 87006083
12466144 INTERNAL MSII_N9 95800577
12427363 INTERNAL MSII_N1 96445137
0 START internal 125165844
0 COMMIT internal 205600756
12960642 UPDATE XLA_GLT_1234567890 243625297
---------------
Total Redo 3681252096
spool off
Notice that objects identified using log miner tool is not matching with the objects from db block changes statistics. In this example, the discrepancy is probably because, I am looking at segment stats from the start of instance which may not be accurate.
In summary, log miner utility can be used to identify the objects generating higher redo. This will help you to understand why the redo generation is higher and may be, gives you a mechanism to reduce redo.
