Finding Redo Log Space Contentions

• Check the status of redo logs

SQL>set linesize 130
SQL>col member for a50
SQL>col status for a12
SQL>col first_time for a15
SQL>select a.group#,to_char(b.first_time,’DDMonYY HH24:mm’) first_time,b.status,a.member,b.bytes/1024/1024 Megs from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1;

GROUP# FIRST_TIME      STATUS       MEMBER                                                                MEGS
———-     —————      ————     ————————————————–              ———-
1     17Sep08 16:09   ACTIVE       /export/dbredo1/oradata/dbf/redo01_a.log              128
1     17Sep08 16:09   ACTIVE       /export/dbredo2/oradata/dbf/redo01_b.log              128
2     17Sep08 16:09   ACTIVE       /export/dbredo1/oradata/dbf/redo02_a.log              128
2     17Sep08 16:09   ACTIVE       /export/dbredo2/oradata/dbf/redo02_b.log              128
3     17Sep08 16:09   ACTIVE       /export/dbredo1/oradata/dbf/redo03_a.log              128
3     17Sep08 16:09   ACTIVE       /export/dbredo2/oradata/dbf/redo03_b.log              128
4     17Sep08 16:09   CURRENT    /export/dbredo1/oradata/dbf/redo04_a.log              128
4     17Sep08 16:09   CURRENT    /export/dbredo2/oradata/dbf/redo04_b.log              128
5     17Sep08 15:09   INACTIVE    /export/dbredo1/oradata/dbf/redo05_a.log              128
5     17Sep08 15:09   INACTIVE    /export/dbredo2/oradata/dbf/redo05_b.log              128
6     17Sep08 15:09   ACTIVE       /export/dbredo1/oradata/dbf/redo06_a.log              128

GROUP# FIRST_TIME      STATUS       MEMBER                                                   MEGS
———- ————— ———— ————————————————– ———-
6 17Sep08 15:09   ACTIVE       /export/dbredo2/oradata/dbf/redo06_b.log              128

12 rows selected.

• Find average redo switches per hour.

SQL>set feed off
SQL>col date for a10
SQL>select to_char(first_time,’DDMonYY’) as “DATE”,round((count(1)/24),0) avg_swcth_per_hour from V$LOG_HISTORY where first_time > sysdate -10 group by to_char(first_time,’DDMonYY’) order by to_date(to_char(first_time,’DDMonYY’),’DDMonYY’);

DATE       AVG_SWCTH_PER_HOUR
———-           ——————
07Sep08                     0
08Sep08                     1
09Sep08                     2
10Sep08                     2
11Sep08                     3
12Sep08                     2
13Sep08                     0
14Sep08                     0
15Sep08                     1
16Sep08                     3
17Sep08                     2

• Find log and redo buffer waits –for 10g

SQL>alter session SET NLS_DATE_FORMAT = ‘YYYY/MM/DD HH24:MI:SS’;

SQL>col event for a40 trunc
SQL>col TIME_WAITED for 999999999999

SQL>select wait_class,event,total_waits,total_timeouts,time_waited,round(time_waited/100/60,1) Time_Wait_Minutes,average_wait  from V$SYSTEM_EVENT where lower(event) like ‘%log%’or lower(event) like ‘%redo%’ order by 1;

WAIT_CLASS           EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS   TIME_WAITED TIME_WAIT_MINUTES               AVERAGE_WAIT
——————–    ——————————    ———–      ————–                   ————-          —————–                  ————
Commit                 log file sync                       45015          127                             95689               15.9            2.13
Configuration        log buffer space                 1362            23                              37210                6.2             27.32
Configuration        log file switch completion    403             0                                3641                  .6               9.04
Other                   latch: redo allocation           16               0                                0                        0               .03
Other                   LGWR wait for redo copy     2200           0                                14                      0               .01
System I/O           log file parallel write             64508         0                                115360               19.2         1.79
System I/O           Log archive I/O                     20829         0                               541                      .1             .03
System I/O           log file single write               254             0                               104                       0             .41
System I/O           log file sequential read         7835            0                               43688                  7.3           5.58

• Find Request for space contention

SQL>col name for a60
SQL>col value for 999999999999
SQL>select name,value from V$SYSSTAT where name IN (‘redo buffer allocation retries’, ‘redo log space requests’);

NAME                                                                    VALUE
————————————————————     ———-
redo buffer allocation retries                                  19723
redo log space requests                                         1900

The value of “redo log space requests” should be near 0. If this value increments consistently, processes have had to wait for space  in the buffer. This may be caused the checkpointing or log switching.  Improve thus the checkpointing or archiving process.

• Determine the miss ratio and the “immediate” miss ratio for redolog latches

SQL>SELECT substr(ln.name, 1, 20) statistic, gets, misses, immediate_gets, immediate_misses  FROM V$LATCH l, V$LATCHNAME ln WHERE ln.name in (‘redo allocation’, ‘redo copy’) and ln.latch# = l.latch#;

STATISTIC               GETS        MISSES     IMMEDIATE_GETS IMMEDIATE_MISSES
——————–       ———-    ———-    ————–          —————-
redo copy                600          0             4120777             2486
redo allocation         5236662   2940        0                       0

If the ratio of MISSES to GETS exceeds 1%,
or the ratio of IMMEDIATE_MISSES to (IMMEDIATE_GETS + IMMEDIATE_MISSES) exceeds 1%, there is latch contention.

• Find amount of generated redo in current session.

SQL>col value for 999999999999
SQL>select n.name, t.value from v$mystat t join V$STATNAME n on t.statistic# = n.statistic# where n.name = ‘redo size’;

NAME                                                                     VALUE
—————————————————————- ————-
redo size                                                                    0

11G – Explicit Locking of Tables

In 10G if you would like to add a column to any table and if oracle is not able to acquire the exclusive lock on that table because of frquent updates on that table then DDL command would fail immediately.

But in 11G you can ask Oracle to wait before it fails in case of its inability to acquire the necessary DML lock on the table.

The new command LOCK TABLE lets you specify the maximum time a statement should wait to obtain a DML lock on a table.

The syntax for new command is ,

LOCK TABLE … IN lockmode MODE [NOWAIT | WAIT integer]

nowait and wait means :

• Nowait – Immediate error.
• Wait – Will wait number of seconds before it fails to acquire DML lock.You can set the value of this parameter to any integer value you want—there’s no limit.
• If you don’t specify either wait or nowait, the database will wait until the locked table is available and then lock it before returning control to you.