Redo Log Switches

In Gathering Database Statistics in a Test Harness I said that use of the Automatic Workload Repository (AWR) is fundamental to understanding Oracle Database performance.

When I finished the scripts for the blog and ran the first test run, the AWR report was quite clear: I need to increase the size of the redo logs:

Report Summary

Top ADDM Findings by Average Active Sessions

Finding Name Avg active sessions of the task Percent active sessions of finding Task Name Begin Snap Time End Snap Time
Top SQL Statements 1.00 80.56 ADDM:1351308822_1_2046 24-Jan-14 11:36 24-Jan-14 11:42
Log File Switches 1.00 5.38 ADDM:1351308822_1_2046 24-Jan-14 11:36 24-Jan-14 11:42


Instance Activity Stats - Thread Activity

  • Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
log switches (derived) 13 126.78

Finding 2: Log File Switches
Impact is .05 active sessions, 5.38% of total activity.
-------------------------------------------------------
Log file switch operations were consuming significant database time while
waiting for checkpoint completion.
This problem can be caused by use of hot backup mode on tablespaces.  DML to
tablespaces in hot backup mode causes generation of additional redo.

...

   Recommendation 2: Database Configuration
   Estimated benefit is .05 active sessions, 5.38% of total activity.
   ------------------------------------------------------------------
   Action
      Increase the size of the log files to 1552 M to hold at least 20 minutes
      of redo information.

Original log files and groups:

SQL> select GROUP#,THREAD#,BYTES from v$log;

    GROUP#    THREAD#      BYTES
---------- ---------- ----------
     1         1        52428800
     2         1        52428800
     3         1        52428800

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log

Create new ones:

SQL > ALTER DATABASE
  ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log')
      SIZE 5G;

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log')
      SIZE 5G;

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log')
      SIZE 5G;

Drop the old one:

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> alter system checkpoint;
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

(Dropping group 2 is OK after checkpoint)


Done:

SQL> select GROUP#,THREAD#,BYTES from v$log;

    GROUP#    THREAD#       BYTES
---------- ---------- ----------
     4          1     5368709120
     5          1     5368709120
     6          1     5368709120
Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

user12620111

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today