Monday, February 28, 2011

How to ... redo logs generation?

All around Internet there are many articles which says how to narrow redo log generation in Oracle. However I still found out my self in position to ask what is the real cause of redo log generation. In this post I'll try to answer all aspects of the cause redo log generation. This subject is more interesting for those who are running stand by databases which redo log transport on remote location may caused many problems.

Who is generating redo logs now?

One of the first question, which cover firefighter is: "Who is generating redo logs in this moment". Idea is to determine action on ad hoc basis. For that I use following script, which I call top_redo.sql:

If you want to concentrate on real oracle users (avoid core Oracle processes in result) place next condition in outer where clause:

and b.username is not null

When and how many redo logs generation occurred?

Beside current analyze in many times wider analyze/compare is even more interesting. So questions like:

When do we have most of redo log generation?

Where was the peak of log generation?

Did we have any "strange" redo log generation?

need a little different approach-query v$log_history view. It holds historic data which retention period is initially controlled with MAXLOGHISTORY, defined while creating database (fixed not changeable without recreation of control file) and CONTROL_FILE_RECORD_KEEP_TIME which is changeable.
In mine case it was set to 31 days (exact number of days for longest month):

Redo logs generation is grouped by hours where last column (TOT) is sum of all redo logs in one day.
According this it is more then obvious where redo log generation was highest, so our interest may be focused on presented point in time.

How much is that in Mb?

Total redo logs size (and according that, archived log size) cannot be computed from previous query because not all redo log switches occur when redo log was full. For that you might want to use this very easy query:

Which segments are generating redo logs?

After we found out our point of interest, in mine case where were most of the redo logs generation, it is very useful to find out which segments (not tables only) are causing redo log generation.
For that we need to use "dba_hist" based tables, part of "Oracle AWR (Automated Workload Repository)", which usage I have described in topic Automated AWR reports in Oracle 10g/11g.
For this example I'll focus on data based on time period:
11-01-28 13:00-11-01-28 14:00.
Query for such a task should be:

Let us focus on first segment "USR_RACUNI_MV", segment with highest number of changed blocks (what mean directly highest redo log generation). Just for information, this is MATERIALIZED VIEW.

What SQL was causing redo log generation

Now when we know when, how much and what, time is to find out how redo logs are generated.
In next query "USR_RACUNI_MV" and mentioned period are hard codded, because we are focused on them. Just to point that SQL that start with "SELECT" are not point of our interest because they do not make any changes.

For both of examples it was impossible to find out number of rows changed according operation that was performed.
Let us see output of another example (NC_TRANSACTION_OK_T table) where we can meet with DDL that generate redo logs!

If you are focused on pure number of changes, then you might to perform query where inst_id and sql_id are irrelevant (excluded from query). Here is a little modified previous example, for "Z_PLACENO" segment (pure oracle table):

Here you can see directly number executions and number of involved rows.

Query based on segment directly

Sometimes you do not want to focus on period, so your investigation may start with segment as starting point. For such a tasks I use next query.
This is small variation of previous example where "USR_RACUNI_MV" segment is hard codded.

querying v$log_history will give you result based on instance you are logged in. querying gv$log_history will give you result based whole RAC (all instances). Look in this RAC example:[code]SQL> select count(*) from v$log_history;

This is a very useful document and i am following this document for a long time to get the information of redo generation, currently i am struggling with FND_LOB table and SYSLOB segment, there are high redo generation on these tables by when i check the attachments they are hardly 2 to 3 MB attachment uploaded during high redo generation but it created 400 to 500 archivelog file that is very strange.

Can you please help me troubleshoot the issue, any queries related to this will help

If you are focused on pure number of changes, then you might to perform query where inst_id and sql_id are irrelevant (excluded from query). Here is a little modified previous example, for "Z_PLACENO" segment (pure oracle table):SELECT when, sql, SUM(sx) executions, sum (sd) rows_processed...

Hi Damir, Again its very good work .Looks like you need to make some correction Inbelow query which i have taken from your above post. Please look at first line , looks like the variables used are not what inner query refer.(SUM(sx) executions, sum (sd) rows_processed )