Redo log store all changes made to the database. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Archive is generated due to log switches.In fact dependent on the redo generated. Cause of redo generation is any change in the database like, insert, delete or update query.
If you commit frequently, the amount of redo you generate goes UP as well (not only that, but it runs slower overall).
If we have issue of huge generation of archive log as immediately we can create enough room in the mount point to accommodate archive logs by compressing or moving the files.
Parallely we can search for the session generating high redo.
Query to find sessions when session is connected:
select s.username, s.osuser, s.status,s.sql_id, sr.* from
(select sid, round(value/1024/1024) as "RedoSize(MB)"
from v$statname sn, v$sesstat ss
where sn.name = 'redo size'
and ss.statistic# = sn.statistic#
order by value desc) sr,
v$session s
where sr.sid = s.sid
and rownum <= 10;
select sid,value from v$sesstat natural join v$statname where name='redo size' order by 2;
If session is not connected at present and wanted to know the details of session which generated more redo we can reach as follows:
1. Take AWR report,check in session "Segments by DB Blocks Changes" for the segment having more db_block_changes.Now search for sql text in session 'Complete List of SQL Text' for corresponding segments.
or
2. Below query can be helpful:
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
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date('2018_06_04 12','YYYY_MM_DD HH24')
AND to_date('2018_04_04 12','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
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
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND dhsso.object_name = 'OWN_OBJECT'
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI')
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 '%OWN_OBJECT%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id
SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
user_id,
program
FROM dba_hist_active_sess_history
WHERE sql_id = 'jy5i4p6t05v'
AND snap_id BETWEEN 53234 AND 53245
Related doc id:
Diagnosing excessive redo generation [ID 199298.1]
Excessive Archives / Redo Logs Generation Troubleshooting [ID 832504.1]
Troubleshooting High Redo Generation Issues [ID 782935.1]
How to Disable (Temporary) Generation of Archive Redo Log Files [ID 177218.1]
Excessive Archives / Redo Logs Generation Troubleshooting [ID 832504.1]
NOTE:1035935.6 - Example of How To Resize the Online Redo Logfiles
NOTE:111886.1 - How to Setup LogMiner
NOTE:167492.1 - SQL: How to Find Sessions Generating Lots of Redo or Archive logs
NOTE:188691.1 - How to Avoid Generation of Redolog Entries
NOTE:199298.1 - Diagnosing excessive redo generation
NOTE:300395.1 - Using LogMiner, How to determine the cause of lots of redo generation.
NOTE:45042.1 - Archiver Best Practices
NOTE:461100.1 - STARTUP ERRORS ora-00824 cannot set sga_target with statistics_level=BASIC
Master Note: Troubleshooting Redo Logs and Archiving[Article ID 1507157.1]
SQL: How to Find Sessions Generating Lots of Redo or Archive logs [Article ID 167492.1]
Troubleshooting High Redo Generation Issues[Article ID 782935.1]
Archive is generated due to log switches.In fact dependent on the redo generated. Cause of redo generation is any change in the database like, insert, delete or update query.
If you commit frequently, the amount of redo you generate goes UP as well (not only that, but it runs slower overall).
If we have issue of huge generation of archive log as immediately we can create enough room in the mount point to accommodate archive logs by compressing or moving the files.
Parallely we can search for the session generating high redo.
Query to find sessions when session is connected:
select s.username, s.osuser, s.status,s.sql_id, sr.* from
(select sid, round(value/1024/1024) as "RedoSize(MB)"
from v$statname sn, v$sesstat ss
where sn.name = 'redo size'
and ss.statistic# = sn.statistic#
order by value desc) sr,
v$session s
where sr.sid = s.sid
and rownum <= 10;
select sid,value from v$sesstat natural join v$statname where name='redo size' order by 2;
If session is not connected at present and wanted to know the details of session which generated more redo we can reach as follows:
1. Take AWR report,check in session "Segments by DB Blocks Changes" for the segment having more db_block_changes.Now search for sql text in session 'Complete List of SQL Text' for corresponding segments.
or
2. Below query can be helpful:
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
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date('2018_06_04 12','YYYY_MM_DD HH24')
AND to_date('2018_04_04 12','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
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
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND dhsso.object_name = 'OWN_OBJECT'
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI')
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 '%OWN_OBJECT%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id
SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
user_id,
program
FROM dba_hist_active_sess_history
WHERE sql_id = 'jy5i4p6t05v'
AND snap_id BETWEEN 53234 AND 53245
Related doc id:
Diagnosing excessive redo generation [ID 199298.1]
Excessive Archives / Redo Logs Generation Troubleshooting [ID 832504.1]
Troubleshooting High Redo Generation Issues [ID 782935.1]
How to Disable (Temporary) Generation of Archive Redo Log Files [ID 177218.1]
Excessive Archives / Redo Logs Generation Troubleshooting [ID 832504.1]
NOTE:1035935.6 - Example of How To Resize the Online Redo Logfiles
NOTE:111886.1 - How to Setup LogMiner
NOTE:167492.1 - SQL: How to Find Sessions Generating Lots of Redo or Archive logs
NOTE:188691.1 - How to Avoid Generation of Redolog Entries
NOTE:199298.1 - Diagnosing excessive redo generation
NOTE:300395.1 - Using LogMiner, How to determine the cause of lots of redo generation.
NOTE:45042.1 - Archiver Best Practices
NOTE:461100.1 - STARTUP ERRORS ora-00824 cannot set sga_target with statistics_level=BASIC
Master Note: Troubleshooting Redo Logs and Archiving[Article ID 1507157.1]
SQL: How to Find Sessions Generating Lots of Redo or Archive logs [Article ID 167492.1]
Troubleshooting High Redo Generation Issues[Article ID 782935.1]
=========================================================================High redo generation is always a consequence of certain activity in the database.
The main cause of high redo generation is usually a high DML activity during a certain period of time and it’s a good practice to first examine modifications on either database level (parameters, any maintenance operations,…) and application level (deployment of new application, modification in the code, increase in the users,..).
What we need to examine:
Is supplemental logging enabled? The amount of redo generated when supplemental logging is enabled is quite high when compared to when supplemental logging is disabled.
What Causes High Redo When Supplemental Logging is Enabled (Doc ID 1349037.1)
Are a lot of indexes being used?, reducing the number of indexes or using the attribute NOLOGGING will reduce the redo considerably.
Are all the operation really in need of the use of LOGGING? From application we can reduce redo by making use of the clause NOLOGGING. Note that only the following operations can make use of NOLOGGING mode:
- direct load (SQL*Loader)
- direct-load INSERT
- CREATE TABLE ... AS SELECT
- CREATE INDEX
- ALTER TABLE ... MOVE PARTITION
- ALTER TABLE ... SPLIT PARTITION
- ALTER INDEX ... SPLIT PARTITION
- ALTER INDEX ... REBUILD
- ALTER INDEX ... REBUILD PARTITION
- INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
To confirm if the table or index has "NOLOGGING" set.
Issue the following statement.
select table_name,logging from all_tables where table_name = <table name>;
-or-
select table_name,logging from all_indexes where index_name = <index name>;
Do tables have triggers that might cause some indirect DML on other tables?
Is Auditing enabled the contributor for this excessive redo generation?
Are tablespaces in hot backup mode?
select a.tablespace_name
from sys.dba_data_files a, sys.v_$backup b
where b.status = ‘ACTIVE’
and b.file# = a.file_id
group by a.tablespace_name;
Examine the log switches:
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member
from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
select to_char(first_time,'YYYY-MON-DD') "Date", to_char(first_time,'DY') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23" ,
count(*) Total from v$log_history group by to_char(first_time,'YYYY-MON-DD'), to_char(first_time,'DY')
order by to_date(to_char(first_time,'YYYY-MON-DD'),'YYYY-MON-DD')
This will give us an idea of the times when the high peaks of redo are happening
Examine AWR report:
Next step will be examining the AWR from the hour where we have had the highest number of log switches, and confirm with the redo size that these log switches are actually caused by a lot of redo generation.
In the AWR we can also see the sql with most of the gets/executions to have an idea of the activity that is happening in the database and generating redo and we can also see the segments with the biggest number of block changes and the sessions performing these changes.Another way to find these sessions is described in SQL: How to Find Sessions Generating Lots of Redo or Archive logs (Doc ID 167492.1)
To find these segments we can also use queries:
SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
dhso.object_name,
sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('11-01-28 13:00','YY-MM-DD HH24:MI') <<<<<<<<<<<< Need to modify the time as per the above query where more redo log switch happened (keep it for 1 hour)
AND to_date('11-01-28 14:00','YY-MM-DD HH24:MI') <<<<<<<<<<<< Need to modify the time as per the above query where more redo log switch happened (interval shld be only 1 hour)
GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
dhso.object_name
HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;
-- Then : What SQL was causing redo log generation :
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,
dbms_lob.substr(sql_text,4000,1) SQL,
dhss.instance_number INST_ID,
dhss.sql_id,
executions_delta exec_delta,
rows_processed_delta rows_proc_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%<segment_name>%' >>>>>>>>>>>>>>>>>> Update the segment name as per the result of previous query result
AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_number=dhs.instance_number
AND dhss.sql_id=dhst.sql_id
AND begin_interval_time BETWEEN to_date('11-01-28 13:00','YY-MM-DD HH24:MI') >>>>>>>>>>>> Update time frame as required
AND to_date('11-01-28 14:00','YY-MM-DD HH24:MI') >>>>>>>>>>>> Update time frame as required
Finally, to troubleshoot further the issue and know the exact commands are being recorded at that particular time frame we can use log miner and mine the archivelog from the concerned time frame. We can look on v$archived_log and find the archived log generated at that particular time frame.
How To Determine The Cause Of Lots Of Redo Generation Using LogMiner (Doc ID 300395.1)
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:
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;
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:
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;
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.
Check which session is generating more redo.
set pages 1000
set lines 140
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;
No comments:
Post a Comment