How to prioritize an Oracle Database background process ?
Posted by FatDBA on April 18, 2022
Recently while looking into a system (was running on 19.3.0.0.0 standalone) where ‘log file sync’ was bugging the database, and after we tried all other possible solutions, we thought to increase the priroty of the LGWR background process to see if that helps.
Increasing the LGWR priority is putting the LGWR process in the Round-Robin (SCHED_RR) class. You can increase process’s priority both using OS (renice, nice commands) or Database methods, but this post is about setting the priority using ‘_high_priority_process’ an undocumented/hidden parameter that prioritizes your database managed processes.
I am using Oracle 19.3 for the test where the LGWR is not by default comes with any priority in the DB, starting from 21.3.0.0.0 LGWR process is part of _high_priority_processes group along with VKTM & LMS* processes.
Note: This being a hidden/undocumented parameter I advise to consult with Oracle support before going and changing the parameter value. Try other possible ways to reduce log file sync, before jumping into this crude method of prioritizing LGWR over others.
[oracle@oracleontario ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 10 03:36:06 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @hidden
Enter value for param: high_prio
old 5: and a.ksppinm like '%¶m%'
new 5: and a.ksppinm like '%priority_processes%'
Parameter Session Value Instance Value descr
--------------------------------------------- ------------------------- ------------------------- ------------------------------------------------------------
_highest_priority_processes VKTM VKTM Highest Priority Process Name Mask
_high_priority_processes LMS*|VKTM LMS*|VKTM High Priority Process Name Mask
And by default in Oracle version 19.3.0.0 the parameter is set to prioritize VKTM (Virtual keeper of time) and LMS (Lock Manager, a RAC process). Let me check VKTM’s current priority class, and it is set to RR class (SCHED_RR scheduling class) for the process as its defined via _high_priority_processes parameter.
[oracle@oracleontario ~]$ ps -eo pid,class,pri,nice,time,args |grep vktm
23871 RR 41 - 00:00:53 ora_vktm_dixitdbAbout the LGWR process, and it is set to TS (SCHED_OTHER) class and it has no priority class attached to it.
[oracle@oracleontario ~]$ ps -eo pid,class,pri,nice,time,args |grep ora_lg*
23990 TS 19 0 00:00:07 ora_lgwr_dixitdbLet’s change the priority and reboot the database to persistent the change!
SQL> alter system set "_high_priority_processes"='LMS*|VKTM|LGWR' scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1593831936 bytes
Fixed Size 8897024 bytes
Variable Size 1107296256 bytes
Database Buffers 469762048 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> @hidden
Enter value for param: high_priority_processes
old 5: and a.ksppinm like '%¶m%'
new 5: and a.ksppinm like '%high_priority_processes%'
Parameter Session Value Instance Value descr
--------------------------------------------- ------------------------- ------------------------- ------------------------------------------------------------
_high_priority_processes LMS*|VKTM|LGWR LMS*|VKTM|LGWR High Priority Process Name Mask
At the same time I can see the same was logged into the Alert log file.
2022-04-10T03:54:31.488732-04:00
LGWR started with pid=8, OS id=26058 at elevated (RT) priority
So, we have reniced the priority of LGWR on the system, I mean the higher value of priority actually makes the process lower priority; it means the process demands fewer system resources (and therefore is a “nicer” process). Now lets check the scheduling class of the process at the OS, it should be now changed to RR from TS.
SQL> !ps -eo pid,class,pri,nice,time,args |grep ora_lm*
26058 RR 41 - 00:00:00 ora_lgwr_dixitdb
Let me check at the OS Level what has changed now.
[oracle@oracleontario 26058]$ pwd
/proc/26058
[oracle@oracleontario 26058]$ more sched
ora_lgwr_dixitd (26058, #threads: 1)
-------------------------------------------------------------------
se.exec_start : 26820431.663015
se.vruntime : -2.963799
se.sum_exec_runtime : 1858.211503
se.nr_migrations : 0
nr_switches : 4038
nr_voluntary_switches : 4023
nr_involuntary_switches : 15
se.load.weight : 1024
policy : 2 -----> Policy, the 0-99 are real-time priorities
prio : 98
clock-delta : 59
mm->numa_scan_seq : 0
numa_migrations, 0
numa_faults_memory, 0, 0, 1, 0, -1
numa_faults_memory, 1, 0, 0, 0, -1
-- output from top utility
top - 05:09:14 up 7:32, 3 users, load average: 0.14, 0.10, 0.11
Tasks: 2 total, 0 running, 2 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
GiB Mem : 4.3 total, 0.0 free, 1.1 used, 3.1 buff/cache
GiB Swap: 3.9 total, 3.9 free, 0.0 used. 2.1 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26027 oracle -2 0 2016104 20096 17064 S 1.7 0.4 1:28.22 ora_vktm_dixitdb ---> Look at the PR (priority) column with value -2 (higher pri)
26058 oracle -2 0 2017136 30360 26768 S 0.0 0.7 0:01.86 ora_lgwr_dixitdb ---> Look at the PR (priority) column with value -2 (higher pri)
So, when nothing was working for us, this workaround helped and we were able to reduce LFS waits by more than 80% …
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
Ravin Maharaj said
Thank you for sharing all your knowledge
You have been posted quite a few articles recently where changes are needed to be made on hidden parameters. Oracle Support always warns us not to make such changes without their consent
Do you have a utility to track all changes made to hidden parameters?
FatDBA said
Thanks Ravin!
Not a utility, but you can always write a script using all those x$ base tables/arrays, I don’t have any utility as such.
Yes, its not good to make changes to those undocumented params without consulting Oracle support, and the reason I always have the disclaimer mentioned in all of blog posts.