Skip to Main Content
  • Questions

  • REDO LOG growing for SMON Background process

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ramiro.

Asked: February 22, 2017 - 10:18 pm UTC

Last updated: March 07, 2017 - 1:48 am UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hello Tom,

We experienced a continuous growing on redo log for background processes, specifically:

SMON: 309 Gb Now, 300 Gb Last week, 291 two weeks ago
DBW0: 141 Gb Now, 135 Gb Last week, 128 two weeks ago
DBW1: 118 Gb Now, 110 Gb Last week, 105 two weeks ago

Note: we have only two writer processes (DBW0, DBW1)

Could you please, give me some light in what may cause this and how to solve it ?
Perhaps the fact of having only 2 writer processes ?

Me as DBA, I read on many books on:
Oracle Help center
Oracle Base
and googled it, with no success to discover this mystery

Note 2: this started, on November 2016, when we needed restart our database for other problem (other DBA performed restart, not me), then when database wake up, it does with an initial value of about 100 GB each background process, but we thought is the normal redo restoring after reboot, but seems is not.

we can restart the instance, but we cannot afford more than 1 hour down time (this can be done at midnight)

query I use to gather this data is: (Redo log size [not historic])

<
SELECT s.sid
, n.name
, s.value
, (SELECT ROUND ( ( p.value * s.value ) / power ( 1024, 3 ), 2 )
FROM GV$SYSTEM_PARAMETER p
WHERE p.name = 'db_block_size'
) AS GBytes
, sn.type
, sn.username AS SchemaName
, sn.OSUSER
, sn.program
, sn.module
, sn.PREV_EXEC_START
FROM v$sesstat s
INNER JOIN v$statname n
ON n.statistic# = s.statistic#
INNER JOIN v$session sn
ON sn.sid = s.sid
WHERE n.name LIKE '%redo entries%'
AND s.value >= 1024 * 15 -- I ignore values below 0.13 GB
ORDER BY value DESC;
/>

Thanks in advance for your attention

Sincerely, Ramiro.

and Connor said...

Your query is not exactly correct, because redo logs are not written in database blocks. A redo entry could be just a smattering of bytes, so your numbers will be excessively high.

As to *why* background processes could be generating redo, and some methods to explore exactly what they are doing, check this link

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7554870060885


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Even with smattering of bytes...

Ramiro Juarez, February 23, 2017 - 2:23 pm UTC

Hello Tom, thanks for the quick answer
but the question remains. As I mentioned I searched many sources, including ASK Tom articule you sent me:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7554870060885

Unfortunately, this is not my case, for them (on the article) is eventually, happening during 30 min, and go back to normal then next 10 min.

"which create archive file of 50 MB each for 10-20 minutes, this doesn't happen in normal usage"

In my case, is continuously growing..
Even my query is not correct because I used the block size to compute numbers, let's say I use a constant factor, it will decrease the result amount ...but not the fact that is continuously growing, let me sent you back my query without using block size

<
SELECT s.sid
, n.name
, s.value
/*, (SELECT ROUND ( ( p.value * s.value ) / power ( 1024, 3 ), 2 )
FROM GV$SYSTEM_PARAMETER p
WHERE p.name = 'db_block_size'
) AS GBytes
*/
, ROUND ( ( "smattering of bytes constant" * s.value ) / power ( 1024, 3 ), 2 )
, sn.type
, sn.username AS SchemaName
, sn.OSUSER
, sn.program
, sn.module
, sn.PREV_EXEC_START
FROM v$sesstat s
INNER JOIN v$statname n
ON n.statistic# = s.statistic#
INNER JOIN v$session sn
ON sn.sid = s.sid
WHERE n.name LIKE '%redo entries%'
AND s.value >= 1024 * 15
ORDER BY value DESC;
/>


Thanks again
Connor McDonald
February 25, 2017 - 12:56 am UTC

I'm not disputing that some background processes are generating redo. For example, AWR snapshots write lots of data to tables at regular intervals.

But check the link I sent, ie, you'll want to:

- determine *which* background processes are responsible
- do some selective tracing to see what their doing
- then look at taking remedial action if required

Wrong metric

Jeff, February 23, 2017 - 3:10 pm UTC

I think you're misinterpreting the statistic "redo entries". It's not a "size" measured in bytes, it's a counter - so of course it's going to be continuously increasing.

From the 11.1 Database reference:
https://docs.oracle.com/cd/B28359_01/server.111/b28320/stats002.htm#i375475

redo entries:
Number of times a redo entry is copied into the redo log buffer


Investigate, processes

Ramiro Juarez, March 03, 2017 - 4:29 pm UTC

Hello Tom,

After the answer from Connor (thanks BTW), I checked out how many processes I have for Oracle and I found 31 ... not sure is too much or not, I wanted also to knew what SQL instructions they run at certain moment, but no SQL appear, even when the process status is ACTIVE ...

SID vary between 1 .. 730+
Modules are (KTSJ, Streams, backup incr datafile, RMAN and DBMS_SCHEDULER)

These having info about Module, SQLText, and Schema Owner, are the less important for this "mystery"

The ones I would like to know what they do and If we can get rid of them, to reduce REDO Entries
but the specific info is null, info such as:
Schema Owner,
Exec_start,
Run_time,
Module,
SQLText,
SQL_ID.

When I searched for the most producers of redo entries, I was always able to identify the SID, Schema, OS_user, and SQL_Text.
that's why I found that some background process are involved.
Going deep, I run this query trying to get more info

Could you please tell me:
1. I'm not founding SQL text, because some of my joins are not correct
2. I'm not searching on the correct SYS Dynamic views

SELECT ses.sid
     , ses.serial
     , ses.status
     , ses.username as schema
     , to_char(ses.exec_start, 'dd-mm-yyyy hh24:mi:ss') as exec_start_paris  --at time zone 'Europe/Paris'
     , ses.Run_Time_Hours
     , ses.Run_Time_Minutes
     , ses.Run_Time_sec
     , ses.os_user
     , ses.machine
     , ses.module
     , ses.sql_text as sql_text
     , ses.killer
     , ses.sql_id
     , ses.seq
  FROM ( WITH vs AS
              ( SELECT rownum rnum
                     , inst_id
                     , sid
                     , serial#
                     , status
                     , USERNAME
                     , machine
                     , OSUSER
                     , MODULE
                     , type
                     , sql_id
                     ,lockwait
                     ,sql_exec_start
                 FROM gv$session 
              )
           SELECT VS.INST_ID
                , VS.sid
                , SERIAL# AS serial
                , VS.SQL_ID
                , VS.USERNAME AS username
                , CAST ( VS.sql_exec_start AS TIMESTAMP ) AS exec_start
                , VS.MACHINE AS machine
                , VS.OSUSER AS os_user
                , LOWER ( VS.STATUS ) AS Status
                , vs.module AS Module
                , round(T.ELAPSED_TIME / 1000000 / 3600, 2) AS Run_Time_Hours
                , round(T.ELAPSED_TIME / 1000000 / 60, 2) AS Run_Time_Minutes
                , round(T.ELAPSED_TIME / 1000000 , 2) AS Run_Time_Sec
                , T.SQL_FULLTEXT AS sql_text
                , 'ALTER SYSTEM KILL SESSION ' || CHR ( 39 ) || TO_CHAR ( VS.sid ) || ', ' || SERIAL# ||', @' || INST_ID || CHR ( 39 ) || 'IMMEDIATE;'
                     AS killer
                , ROW_NUMBER ( ) OVER ( PARTITION BY VS.sid, SERIAL#, VS.SQL_ID ORDER BY VS.sid, SERIAL#, VS.SQL_ID ) AS Seq
              FROM vs
               LEFT JOIN v$sql t
                   ON vs.SQL_ID = t.SQL_id
       ) ses
  WHERE ses.seq = 1
    AND ses.username in ('SYS', 'oracle')
     OR ses.OS_USER = 'oracle' 
     OR ses.Module = 'DBMS_SCHEDULER'        
  ORDER BY ses.SID DESC, ses.EXEC_START nulls last ;


Thanks again and best regards :)

Connor McDonald
March 07, 2017 - 1:48 am UTC

Background processes are often seen as active, because they are sitting in a constant "wait for someone to tell me what to do" state. So they might be active but they'll consume minimal resources, unless they are actually needed to be doing something.

You can look (for example) at the 'CPU used by this session' statistic for each session to see how much server cpu they've consumed, and similar metrics for IO etc.

The PROGRAM column on V$SESSION should be sufficient to tell you which background process is of concern, eg

SQL> select program from v$session;

PROGRAM
------------------------------
ORACLE.EXE (VKTM)
ORACLE.EXE (DIAG)
ORACLE.EXE (LGWR)
ORACLE.EXE (LG01)
ORACLE.EXE (MMON)
ORACLE.EXE (ARC2)
ORACLE.EXE (W003)
ORACLE.EXE (CJQ0)
ORACLE.EXE (QM02)
ORACLE.EXE (Q001)
sqlplus.exe
ORACLE.EXE (W001)
ORACLE.EXE (GEN0)
ORACLE.EXE (DBRM)
ORACLE.EXE (CKPT)
ORACLE.EXE (RECO)
ORACLE.EXE (MMNL)
...
...



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.