Some useful scripts
Now is the time to see some important scripts that could help your life become a little bit easier when dealing with redo.
Redo generated since instance startup
The script redo_since_startup.sql will show statistics regarding redo since the instance was started.
The following is an example of the output of this script:
Redo Log Statistics Statistic Statistic Name Value ---------------------------------------------- ---------- redo KB read 209048 redo KB read (memory) 0 redo KB read (memory) for transport 0 redo KB read for transport 0 redo blocks checksummed by FG (exclusive) 205974 redo blocks checksummed by LGWR 0 redo blocks read for recovery 8532 redo blocks written 367495 redo buffer allocation retries 32 redo entries 277700 redo entries for lost write detection 0 redo k-bytes read for recovery 4266 redo k-bytes read for terminal recovery 0 redo log space requests 5 redo log space wait time 26 redo ordering marks 1 redo size 179922296 redo size for direct writes 14202508 redo size for lost write detection 0 redo subscn max counts 0 redo synch long waits 703 redo synch poll writes 0 redo synch polls 0 redo synch time 863 redo synch time (usec) 8790494 redo synch time overhead (usec) 4.2761E+10 redo synch time overhead count (<128 msec) 10 redo synch time overhead count (<2 msec) 301 redo synch time overhead count (<32 msec) 38 redo synch time overhead count (<8 msec) 441 redo synch time overhead count (>=128 msec) 33 redo synch writes 879 redo wastage 2536524 redo write broadcast ack count 0 redo write broadcast ack time 0 redo write broadcast lgwr post count 0 redo write info find 823 redo write info find fail 0 redo write time 5373 redo writes 11599 40 rows selected.
Redo generated since session startup
The script redo_since_session_started.sql shows how much redo was generated since your session started. The value it shows here is in bytes:
REDO_SIZE ---------- 25736452
Redo generated by current user sessions
The useful script redo_generated_by_session.sql shows how much redo is being generated by each active session in the database:
SID USERNAME REDO_SIZE ---------- ------------------------------ ---------- 44 TEST 25736656
Current status for redo logs
The following is an interesting script (redo_current_status.sql) showing some very important information regarding the redo log files. The script reports on the threads, number of members per group, whether a group was archived or not, size, and SCN. The following screenshot illustrates this:
Redo log group and log switch information
This section's script (redo_switch_info.sql) is in my opinion one of the most important scripts in this chapter. It will clearly show you how many log switches occurred by day and hour. That information helps you easily identify common hours of overload in redo generation. It also helps you spot the need to tune your redo log files size to reduce log switches. Let's have a look at the following screenshot:
NOLOGGING objects in the database
The following are some queries that will help you identify objects (tables, indexes, tablespaces, and partitions) in the database that are currently in the NOLOGGING mode:
SQL> SELECT owner , table_name 2 FROM dba_tables 3 WHERE logging='NO'; SQL> SELECT owner , table_name, index_name 2 FROM dba_indexes 3 WHERE logging='NO'; SQL> SELECT tablespace_name 2 FROM dba_tablespaces 3 WHERE logging='NOLOGGING'; SQL> SELECT TABLE_NAME, PARTITION_NAME 2 FROM user_tab_partitions 3 WHERE logging='NO';