Find the session generating the most redo

Problem:

Got an alert email from my own cron job on a normally quiet database: "FRA is more than 75% used! When it reaches 80%, Oracle will delete RMAN disk backup (Ref: Oracle Doc 1369341.1)". The database suddenly starts to generate more than 30 times of redo or archived log, consistently, per v$archived_log.

Troubleshooting:

Use my OSSW tool, Oracle Session Statistic Watcher (http://yong321.freeshell.org/freeware/ossw.html), to see which session has the highest delta of "redo size" statistic, in case the session does not quickly log out.

--- begin screenshot ---
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ ./ossw -S 'redo size' -s provp -u system -p thepassword -i 1048576 #shows sessions creating more than 1MB redo in 5-sec interval
Output template:
2-1234-33:5:1:HR-v3besextm001
where 2 is instance ID, 1234 SID, 33 serial#, 5 so far for the tracked statistic, 1 increment
since last sampling, user HR on machine v3besextm001

*** Oracle Session Statistic Watcher for statistic #313 "redo size" ***





2-592-53717:42205496:7031420:PVUSER-PRDPROVAPP1





1-29-14104:63306188:7033952:PVUSER-PRDPROVAPP1




2-592-53717:49237012:7031516:PVUSER-PRDPROVAPP1


^C
--- end screenshot ---

The above shows session 592 on RAC instance 2 and session 29 on instance 1 in the provp database occsionally generate about 7 MB of redo in 5-second intervals. Both sessions are login as PVUSER, from app server PRDPROVAPP1.

What are these sessions? Gv$session shows that these are JDBC thin driver sessions (therefore not much info is given, no module, action, client_info), with so-and-so logon times and client side programs. The SQLs they run come and go quickly but sometimes can be captured in sql_id column of gv$session.

What table(s) are incurring the most changes? We can check gv$segment_statistics.

SQL> select owner,object_name,object_type,round(sum(value)*8192/1048576) mb from gv$segment_statistics where statistic_name='db block changes' group by owner, object_name, object_type having sum(value)>1000000 order by 4;

OWNER      OBJECT_NAME               OBJECT_TYPE         MB
---------- ------------------------- ----------------------
SYS        I_WRI$_OPTSTAT_H_ST       INDEX             8026
SYS        EXP_STAT$                 TABLE            10082
SYS        EXP_OBJ$                  TABLE            13068
SYS        HISTGRM$                  TABLE            15844
SYS        I_H_OBJ#_COL#             INDEX            15979
PVOWNER    INTERFACE_MESSAGE         TABLE           499867 <-- by far the most changes

After observing for a while, it's consistently table INTERFACE_MESSAGE owned by PVOWNER, and its db block changes keeps going up.

With this information, we can narrow down to two SQLs that generate a lot of redo, both begin with "UPDATE INTERFACE_MESSAGE ...".

The INTERFACE_MESSAGE table turns out to be only 3 rows. Only one row is constantly being updated. Its long data type column, messagetext, is 3 MB long.[note]

More detective work is more specific to the app. Basically, based on the info in other columns, it looks like the app server repeatedly tries to connect to a remote server but fails, and logs something in this table.

Solution:

Contact the app team and notify my finding. They recently decommissioned the remote server but forgot to stop the outbound connection service on their app server PRDPROVAPP1. They stopped it, and the database redo immediately dropped to the previous level.

Comment:

If a high redo-generating session logs in and out very quickly, my OSSW won't catch it, because OSSW calculates the delta of the session statistic ('redo size' in this case) between two samplings. But gv$segment_statistics still helps narrow down to the table(s) and index(es) that have had the most changes since instance startup. (Since the changes are cumulative, you may want to monitor for a while and do a delta between samplings.) From there, you can check gv$sqlarea to find DML SQLs operated on the relevant tables. Once you find them, look at module and action columns. Check gv$session and gv$open_cursor to see which sessions run these SQLs.

[note]
The length of a long type column can't be directly measured. I have to do this:
create table yongtest as select to_lob(messagetext) x from INTERFACE_MESSAGE where 1=2;
insert into yongtest select to_lob(messagetext) from INTERFACE_MESSAGE where id=...;
select length(x) from yongtest;
truncate table yongtest;
insert into yongtest select to_lob(messagetext) from INTERFACE_MESSAGE where id=...;
select length(x) from yongtest;
...
drop table yongtest;


2026-06