Increasing Priority of lgwr Process using _high_priority_processes

At the Hotsos Symposium last week, Tanel Poder pointed out an interesting hidden parameter (_high_priority_processes). We were discussing the occasional need to increase priority on the LGWR process when dealing with “log file sync” issues. The aforementioned parameter is used to tell Oracle to automatically set background processes to a higher than normal priority when the database is started. On linux this means putting them in the RR class, in Solaris, the RT class is used. The parameter defaults to LMS* in 10.2 and to LMS*||VKTM in 11gR1. (LMS* are the Lock Manager Server processes in a RAC instance and VKTM is the new Time Keeper process in 11g). So it makes sense that these processes would run at a higher priority.

Recently I have been working on a system that was spending a significant amount of time waiting on “log file sync”. Increasing the priority of the LGWR process made a significant improvement. It’s not a silver bullet though. When the system becomes really busy, the “log file sync” times still suck, just not as much. But I digress, this post is just to point out that Oracle has a built in mechanism to increase priority on background processes, not to tell you how to reduce long “log file sync” times. There are numerous posts on that subject already. Here a couple of pretty good ones:


Manly-Men Only Use Solid State Disk for Redo Logging – Kevin Closson

Tuning Log File Sync Wait Events – Riyaj Shamsudeen
Hotsos 2010 – Day 4 – Doug Burns (see the comments section)

Back to my story. Unfortunately, every time the database is bounced, LGWR goes back to his original priority and class and we have to get the Unix guys to reset it. So I got to wondering if the _high_priority_processes parameter could be used to do this automatically. And sure enough it looks like it can. First here’s a look at the class and priority info of the standard high priority processes on a Solaris system and a Linux system.


==== Solaris - RT processes (10g RAC) ====

    oracle  6036   system   RT   global   - 100    1   -       16:31 ora_lms3_posp1
    oracle  6032   system   RT   global   - 100    1   -       16:14 ora_lms2_posp1
    oracle  6028   system   RT   global   - 100    1   -       16:20 ora_lms1_posp1
    oracle  6024   system   RT   global   - 100    1   -       16:50 ora_lms0_posp1


==== Redhat Linux - RR processes (11gR1 RAC) ====

    15823 RR   41   - 00:00:00 ora_vktm_SCNPRD1
    15849 RR   41   - 00:01:19 ora_lms0_SCNPRD1
    15853 RR   41   - 00:01:27 ora_lms1_SCNPRD1

Here is an example of setting the parameter to include a couple of other processes (LGWR and PMON) and the changes to the class and priority after restarting the instance.

==== Redhat Linux - Non-RAC ====
 
> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Mar 15 12:53:31 2010
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
 
 
SYS@LAB102> @parms
Enter value for parameter: _high_priority_processes
Enter value for isset: 
Enter value for show_hidden: Y
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
_high_priority_processes                           LMS*                                                                   TRUE     FALSE      FALSE
 
SYS@LAB102> !ps -eo pid,class,pri,nice,time,args | grep LAB102 | grep -v LAB1024 | grep -v grep | sort -k 6,6
19080 TS   24   0 00:00:00 ora_a001_LAB102
 6989 TS   23   0 00:00:00 ora_cjq0_LAB102
 6480 TS   22   0 00:00:00 ora_ckpt_LAB102
19047 TS   18   0 00:00:01 oracleLAB102 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
 5965 TS   23   0 00:00:00 ora_dbw0_LAB102
 6127 TS   23   0 00:00:00 ora_dbw1_LAB102
 6296 TS   23   0 00:00:00 ora_lgwr_LAB102
 5780 TS   23   0 00:00:00 ora_mman_LAB102
 7370 TS   23   0 00:00:00 ora_mmnl_LAB102
 7174 TS   23   0 00:00:00 ora_mmon_LAB102
19049 TS   23   0 00:00:00 ora_p000_LAB102
19051 TS   23   0 00:00:00 ora_p001_LAB102
19053 TS   23   0 00:00:00 ora_p002_LAB102
19055 TS   23   0 00:00:00 ora_p003_LAB102
19057 TS   23   0 00:00:00 ora_p004_LAB102
19059 TS   23   0 00:00:00 ora_p005_LAB102
19061 TS   23   0 00:00:00 ora_p006_LAB102
19063 TS   23   0 00:00:00 ora_p007_LAB102
19065 TS   23   0 00:00:00 ora_p008_LAB102
19067 TS   23   0 00:00:00 ora_p009_LAB102
19069 TS   23   0 00:00:00 ora_p010_LAB102
19071 TS   23   0 00:00:00 ora_p011_LAB102
19073 TS   23   0 00:00:00 ora_p012_LAB102
19075 TS   23   0 00:00:00 ora_p013_LAB102
19077 TS   23   0 00:00:00 ora_p014_LAB102
 5413 TS   23   0 00:00:00 ora_pmon_LAB102
 5599 TS   23   0 00:00:00 ora_psp0_LAB102
 6845 TS   23   0 00:00:00 ora_reco_LAB102
 6667 TS   23   0 00:00:00 ora_smon_LAB102
 
SYS@LAB102> -- notice that all processes are in TS class with priorities around 23
SYS@LAB102>
SYS@LAB102> alter system set "_high_priority_processes"='LMS*|LGWR|PMON' scope=spfile;                           
 
System altered.
 
SYS@LAB102> startup force
ORACLE instance started.
 
Total System Global Area 3221225472 bytes
Fixed Size                  1264380 bytes
Variable Size            1493173508 bytes
Database Buffers         1711276032 bytes
Redo Buffers               15511552 bytes
Database mounted.
Database opened.
SYS@LAB102> !ps -eo pid,class,pri,nice,time,args | grep LAB102 | grep -v LAB1024 | grep -v grep | sort -k 6,6
22170 TS   23   0 00:00:00 ora_a001_LAB102
13368 TS   23   0 00:00:00 ora_cjq0_LAB102
12912 TS   22   0 00:00:00 ora_ckpt_LAB102
22091 TS   18   0 00:00:01 oracleLAB102 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
12396 TS   23   0 00:00:00 ora_dbw0_LAB102
12558 TS   23   0 00:00:00 ora_dbw1_LAB102
 4827 TS   23   0 00:00:00 ora_j000_LAB102
12732 RR   90   - 00:00:00 ora_lgwr_LAB102
12248 TS   23   0 00:00:00 ora_mman_LAB102
13713 TS   23   0 00:00:00 ora_mmnl_LAB102
13566 TS   23   0 00:00:00 ora_mmon_LAB102
22093 TS   23   0 00:00:00 ora_p000_LAB102
22095 TS   23   0 00:00:00 ora_p001_LAB102
22097 TS   23   0 00:00:00 ora_p002_LAB102
22099 TS   23   0 00:00:00 ora_p003_LAB102
22105 TS   23   0 00:00:00 ora_p004_LAB102
22107 TS   23   0 00:00:00 ora_p005_LAB102
22110 TS   23   0 00:00:00 ora_p006_LAB102
22118 TS   23   0 00:00:00 ora_p007_LAB102
22125 TS   23   0 00:00:00 ora_p008_LAB102
22127 TS   23   0 00:00:00 ora_p009_LAB102
22134 TS   23   0 00:00:00 ora_p010_LAB102
22142 TS   23   0 00:00:00 ora_p011_LAB102
22152 TS   23   0 00:00:00 ora_p012_LAB102
22160 TS   23   0 00:00:00 ora_p013_LAB102
22166 TS   23   0 00:00:00 ora_p014_LAB102
11817 RR   90   - 00:00:00 ora_pmon_LAB102
12108 TS   23   0 00:00:00 ora_psp0_LAB102
13209 TS   23   0 00:00:00 ora_reco_LAB102
13050 TS   23   0 00:00:00 ora_smon_LAB102
 
SYS@LAB102> -- now the lgwr and pmon processes are in RR class and priority is 90
SYS@LAB102>
SYS@LAB102> !ps -eo pid,class,pri,nice,time,args | grep LAB102 | grep RR
 1040 TS   20   0 00:00:00 /bin/bash -c ps -eo pid,class,pri,nice,time,args | grep LAB102 | grep RR
11817 RR   90   - 00:00:00 ora_pmon_LAB102
12732 RR   90   - 00:00:00 ora_lgwr_LAB102

So you can see that the database did indeed cause the background processes specified in the _high_priority_processes parameter to run with a higher priority / scheduling class. Cool, maybe we can let the Unix guy get a little sleep now.

Disclaimers:

  1. This is a hidden parameter – so you probably shouldn’t use it.
  2. Reducing commits is the best way to solve “log file sync” issues.
  3. Isolating the lgwr process via processor affinity is another possible approach.
  4. Sit up straight and don’t talk with your mouth full.
  • Leave a Reply