Friday Jan 24, 2014

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

Gathering Database Statistics in a Test Harness

Use of the Automatic Workload Repository (AWR) is fundamental to understanding Oracle Database performance. By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. You can also manually create snapshots, but this is usually not necessary.

When benchmarking, it is helpful to be able to create an AWR report that covers the exact interval of benchmark execution. Since my benchmarks don't begin or end at the top of the hour, I take an AWR snapshot immediately before and after benchmark execution. At benchmark completion, an AWR report is generated that exactly covers the benchmark execution interval.

Here is a simple shell script "snap.sh" that takes a new snapshot and returns the ID of the snapshot. I call this script before and after benchmark execution.

#!/bin/bash

result=$(sqlplus -S sys/SysPass as sysdba << EOF

set head off
set term off
set echo off
set trim on
set trimspool on
set feedback off
set pagesize 0
exec sys.dbms_workload_repository.create_snapshot;
select max(snap_id) from dba_hist_snapshot;
EOF
)

echo $result


Here is a script "gen_awr.sh". I call this script after benchmark execution with my preferred name for the report, and the before and after snap IDs.

sqlplus / as sysdba << @EOF
@$ORACLE_HOME/rdbms/admin/awrrpt
html
1
$2
$3
$1
exit
@EOF

Here is a script "run.sh" that controls the gathering of data for the benchmark execution environment:

Comments:
run.sh

#!/usr/bin/env bash

###
# Usage
###
[ $# -eq 0 ] && { echo "Usage: $0 <TestSize>"; exit 1; }

###
# Set up
###
size=$1
export DateStamp=`date +%y%m%d_%H%M%S`
export DIR=/STATS/`hostname`_${size}_$DateStamp
mkdir -p $DIR

###
# Start gathering statistics
###
vmstat 1 > $DIR/vmstat.out &
vmstat_pid=$!
Take an AWR snapshot before execution
first=`./snap.sh`

###
# Run the benchmark
###
time sqlplus User/UserPass @RunAll $size > $DIR/std.out 2> $DIR/std.err

###
# Stop gathering statistics
###
Take an AWR snapshot after execution last=`./snap.sh`

kill $vmstat_pid

###
# Generate the report
###
Generate the AWR report
./gen_awr.sh $size $first $last > /dev/null
mv $size.lst $DIR/awr_$size.html


###
# View the AWR report
###
firefox $DIR/awr_$size.html

After many benchmark runs, I have one directory per benchmark execution, named <nodename>_<size>_<date>_<time>, and each directory contains one AWR report that exactly covers the benchmark interval.


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