Skip to Main Content
  • Questions

  • LGWR waiting on "control file sequential read"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ram.

Asked: August 17, 2017 - 3:43 pm UTC

Last updated: August 18, 2017 - 12:50 pm UTC

Version: >=11.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi TOM,

Recently, we faced an issue in one of our production databases where we identified 100s of sessions waiting on library cache locks and the holder was LGWR process which itself was waiting on "control file sequential read". Trying to understand why or how LGWR process has to wait on "control file sequential read".

and Connor said...

We store a *lot* of stuff in the control file (not just the datafile information).

SQL> select * from V$CONTROLFILE_RECORD_SECTION;

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID     CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
DATABASE                             316             1            1           0          0          0          0
CKPT PROGRESS                       8180            11            0           0          0          0          0
REDO THREAD                          256             8            1           0          0          0          0
REDO LOG                              72            16            3           0          0          3          0
DATAFILE                             520           100           11           0          0        619          0
FILENAME                             524          2298           13           0          0          0          0
TABLESPACE                           180           100            9           0          0         63          0
TEMPORARY FILENAME                    56           100            1           0          0       1657          0
RMAN CONFIGURATION                  1108            50            0           0          0          0          0
LOG HISTORY                           56           292          292         280        279        571          0
OFFLINE RANGE                        200           163            3           1          3          3          0
ARCHIVED LOG                         584           112          112          12         11        571          0
BACKUP SET                            96           170           18           1         18         18          0
BACKUP PIECE                         780           209           18           1         18         18          0
BACKUP DATAFILE                      200           245           24           1         24         24          0
BACKUP REDOLOG                        76           215            0           0          0          0          0
DATAFILE COPY                        736           200            0           0          0          0          0
BACKUP CORRUPTION                     44           371            0           0          0          0          0
COPY CORRUPTION                       40           409            0           0          0          0          0
DELETED OBJECT                        20           818            1           1          1          1          0
PROXY COPY                           928           246            0           0          0          0          0
BACKUP SPFILE                        124           131           17           1         17         17          0
DATABASE INCARNATION                  56           292            2           1          2          2          0
FLASHBACK LOG                         84          2048            0           0          0          0          0
RECOVERY DESTINATION                 180             1            0           0          0          0          0
INSTANCE SPACE RESERVATION            28          1055            1           0          0          0          0
REMOVABLE RECOVERY FILES              32          1000            0           0          0          0          0
RMAN STATUS                          116           141            6           1          6          6          0
THREAD INSTANCE NAME MAPPING          80             8            8           0          0          0          0
MTTR                                 100             8            1           0          0          0          0
DATAFILE HISTORY                     568            57            0           0          0          0          0
STANDBY DATABASE MATRIX              400           128          128           0          0          0          0
GUARANTEED RESTORE POINT             256          2048            0           0          0          0          0
RESTORE POINT                        256          2108            0           0          0          0          0
DATABASE BLOCK CORRUPTION             80          8384            0           0          0          0          0
ACM OPERATION                        104            64           10           0          0          0          0
FOREIGN ARCHIVED LOG                 604          1002            0           0          0          0          0
PDB RECORD                           780            10            0           0          0          0          0
AUXILIARY DATAFILE COPY              584           128            0           0          0          0          0
MULTI INSTANCE REDO APPLY            556             1            0           0          0          0          0
PDBINC RECORD                        144           113            0           0          0          0          0
TABLESPACE KEY HISTORY               108           151            0           0          0          0          0

42 rows selected.


In particular, every time we switch/archive a logfile, we're going to be accessing the control file contents. So one common cause of heavy activity on the controlfile is too frequent log file switching, so check v$log_history and if appropriate, increase the redo log group sizes.

Some worst case scenarios are when your archive destination or FRA etc get full, so LGWR almost gets into a continuous cycle of *trying* to switch a logfile (and smashing the control file accordingly).

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why read during switch ?

Ram Dittakavi, August 18, 2017 - 2:19 am UTC

Hi Connor,

Thanks for the reply.

I'm interested to know why LGWR needs to "READ" from control file during a log switch when all it has to do probably is to write (may be the seq# info) into the control file ?
Connor McDonald
August 18, 2017 - 12:50 pm UTC

"when all it has to do probably is to write"

:-)

SQL> show parameter control_file_record_keep_time

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
control_file_record_keep_time        integer     7


All sorts of management needs to go on with the control file in terms of history of information etc.

Why read during switch ?

Ram Dittakavi, August 18, 2017 - 2:22 am UTC

Hi Connor,

Thanks a lot for the reply. Trying to understand why LGWR has to "READ" from control file where it probably has to "WRITE" into it ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database