Monday, October 27, 2014

How To Change The Priority Of Oracle Background Processes

How To Change The Priority Of Oracle Background Processes


Before you get in a huf, it can be done! You can change an Oracle Database background process

priority through an instance parameter! I'm not saying it's a good idea, but it can be done.

In this post I explore how to make the change, just how far you can take it and when you may want to consider changing an Oracle background process priority.

To get your adrenaline going, check out the instance parameter _high_priority_processes from one of your production Oracle system with a version of 11 or greater. Here is an example using my OSM tool, ipx.sql on my Oracle Database version 12.1.0.2.0.
SQL> @ipx _highest_priority_processes
Database: prod40                                               27-OCT-14 02:22pm
Report:   ipx.sql              OSM by OraPub, Inc.                Page         1
                         Display ALL Instance Parameters

Instance Parameter and Value                       Description          Dflt?
-------------------------------------------------- -------------------- -----
_highest_priority_processes         = VKTM         Highest Priority     TRUE
                                                   Process Name Mask
Then at the Linux prompt, I did:
$ ps -eo pid,class,pri,nice,time,args | grep prod40
 2879 TS   19   0 00:00:00 ora_pmon_prod40
 2881 TS   19   0 00:00:01 ora_psp0_prod40
 2883 RR   41   - 00:02:31 ora_vktm_prod40
 2889 TS   19   0 00:00:01 ora_mman_prod40
 2903 TS   19   0 00:00:00 ora_lgwr_prod40
 2905 TS   19   0 00:00:01 ora_ckpt_prod40
 2907 TS   19   0 00:00:00 ora_lg00_prod40
 2911 TS   19   0 00:00:00 ora_lg01_prod40
...
Notice the "pri" for priority of the ora_vktm_prod40 process? It is set to 41 while all the rest of the Oracle background processes are set to the default of 19. Very cool, eh?

Surprised By What I Found


Surprised? Yes, surprised because changing Oracle process priority is a pandoras box. Just imagine if an Oracle server (i.e., foreground) process has its priority lowered just a little and then attempts to acquire a latch or a mutex? If it doesn't get the latch quickly, I might never ever get it!

From a user experience perspective, sometimes performance really quick and other times the application just hangs.

This actually happened to a customer of mine years ago when the OS started reducing a process's priority after it consumed a certain amount of CPU. I learned that when it comes to Oracle processes, they are programed to expect an even process priority playing field. If you try to "game" the situation, do so at your own risk... not Oracle's.

Then why did Oracle Corporation allow background process priority to be changed. And why did Oracle Corporation actually change a background processes priority?!

Doing A Little Exploration


It turns out there are a number of "priority" related underscore instance parameters! On my 11.2.0.1.0 system there 6 "priority" parameters. On my 12.1.0.1.0 system there are 8 "priority" parameters. On my 12.1.0.2.0 system there are 13 "priority" parameters! So clearly Oracle is making changes! In all cases, the parameter I'm focusing on, "_high_priority_processes" exists.

In this posting, I'm going to focus on my Oracle Database 12c version 12.1.0.2.0 system. While you may see something different in your environment, the theme will be the same.

While I'll be blogging about all four of the below parameters, in this posting my focus will be on the _high_priority_processes parameter. Below are the defaults on my system:
_high_priority_processes        LMS*
_highest_priority_processes     VKTM
_os_sched_high_priority         1
_os_sched_highest_priority      1

Messing With The LGWR Background Processes


I'm not testing this on a RAC system, so I don't have an LMS background process. When I saw the "LMS*" I immediately thought, "regular expression." Hmmm... I wonder if I can change the LGWR background process. So I made the instance parameter change and recycled the instance. Below shows the instance parameter change:
SQL> @ipx _high_priority_processes
Database: prod40                                               27-OCT-14 02:36pm
Report:   ipx.sql              OSM by OraPub, Inc.                Page         1
                         Display ALL Instance Parameters

Instance Parameter and Value                       Description          Dflt?
-------------------------------------------------- -------------------- -----
_high_priority_processes            = LMS*|LGWR    High Priority        FALSE
                                                   Process Name Mask

Below is an operating system perspective using the ps command:

ps -eo pid,class,pri,nice,time,args | grep prod40
...
 5521 RR   41   - 00:00:00 ora_vktm_prod40
 5539 TS   19   0 00:00:00 ora_dbw0_prod40
 5541 RR   41   - 00:00:00 ora_lgwr_prod40
 5545 TS   19   0 00:00:00 ora_ckpt_prod40
 5547 TS   19   0 00:00:00 ora_lg00_prod40
 5551 TS   19   0 00:00:00 ora_lg01_prod40
...

How Far Can I Take This?


At this point in my journey, my mind was a blaze! The log file sync wait event can be really difficult to deal with and especially so when there is a CPU bottleneck. Hmmm... Perhaps I can increase the priority of all the log writer background processes?

So I made the instance parameter change and recycled the instance. Below shows the instance parameter change:
SQL> @ipx _high_priority_processes
Database: prod40                                               27-OCT-14 02:44pm
Report:   ipx.sql              OSM by OraPub, Inc.                Page         1
                         Display ALL Instance Parameters

Instance Parameter and Value                       Description          Dflt?
-------------------------------------------------- -------------------- -----
_high_priority_processes            = LMS*|LG*     High Priority        FALSE
                                                   Process Name Mask

Below is an operating system perspective using the ps command:

ps -eo pid,class,pri,nice,time,args | grep prod40
...
 5974 TS   19   0 00:00:00 ora_psp0_prod40
 5976 RR   41   - 00:00:00 ora_vktm_prod40
 5994 TS   19   0 00:00:00 ora_dbw0_prod40
 5996 RR   41   - 00:00:00 ora_lgwr_prod40
 6000 TS   19   0 00:00:00 ora_ckpt_prod40
 6002 RR   41   - 00:00:00 ora_lg00_prod40
 6008 RR   41   - 00:00:00 ora_lg01_prod40
 6014 TS   19   0 00:00:00 ora_lreg_prod40
...

So now all the log writer background processes have a high priority. My hope would be that if there is an OS CPU bottleneck and the log writer background processes wanted more CPU, I now have the power to give that to them! Another tool in my performance tuning arsenal!

Security Hole?


At this point, my exuberance began to turn into paranoia. I thought, "Perhaps I can increase the priority of an Oracle server process or perhaps any process." If so, that would be a major Oracle Database security hole.

With fingers trembling, I changed the instance parameters to match an Oracle server process and recycled the instance. Below shows the instance parameter change:

SQL> @ipx _high_priority_processes
Database: prod40                                               27-OCT-14 02:52pm
Report:   ipx.sql              OSM by OraPub, Inc.                Page         1
                         Display ALL Instance Parameters

Instance Parameter and Value                       Description          Dflt?
-------------------------------------------------- -------------------- -----
_high_priority_processes            =              High Priority        FALSE
LMS*|LG*|oracleprod40                              Process Name Mask

Below is an operating system perspective using the ps command:

$ ps -eo pid,class,pri,nice,time,args | grep prod40
...
 6360 TS   19   0 00:00:00 ora_psp0_prod40
 6362 RR   41   - 00:00:00 ora_vktm_prod40
 6366 TS   19   0 00:00:00 ora_gen0_prod40
 6382 RR   41   - 00:00:00 ora_lgwr_prod40
 6386 TS   19   0 00:00:00 ora_ckpt_prod40
 6388 RR   41   - 00:00:00 ora_lg00_prod40
 6394 RR   41   - 00:00:00 ora_lg01_prod40
 6398 TS   19   0 00:00:00 ora_reco_prod40
...
 6644 TS   19   0 00:00:00 oracleprod40...
...

OK, that didn't work so how about this?

SQL> @ipx _high_priority_processes
Database: prod40                                               27-OCT-14 02:55pm
Report:   ipx.sql              OSM by OraPub, Inc.                Page         1
                         Display ALL Instance Parameters

Instance Parameter and Value                       Description          Dflt?
-------------------------------------------------- -------------------- -----
_high_priority_processes            =              High Priority        FALSE
LMS*|LG*|*oracle*                                  Process Name Mask

Let's see what happened at the OS.

$ ps -eo pid,class,pri,nice,time,args | grep prod40
...
 6701 RR   41   - 00:00:00 ora_vktm_prod40
 6705 RR   41   - 00:00:00 ora_gen0_prod40
 6709 RR   41   - 00:00:00 ora_mman_prod40
 6717 RR   41   - 00:00:00 ora_diag_prod40
 6721 RR   41   - 00:00:00 ora_dbrm_prod40
 6725 RR   41   - 00:00:00 ora_vkrm_prod40
 6729 RR   41   - 00:00:00 ora_dia0_prod40
 6733 RR   41   - 00:00:00 ora_dbw0_prod40
...
 6927 RR   41   - 00:00:00 ora_p00m_prod40
 6931 RR   41   - 00:00:00 ora_p00n_prod40
 7122 TS   19   0 00:00:00 oracleprod40 ...
 7124 RR   41   - 00:00:00 ora_qm02_prod40
 7128 RR   41   - 00:00:00 ora_qm03_prod40

Oh Oh... That's not good! Now EVERY Oracle background process has a higher priority and my Oracle server process does not.

So my "*" wildcard caused all the Oracle processes to be included. If all the processes a high prioirty, then the log writer processes have no advantage over the others. And to make matters even worse, my goal of increasing the server process priority did not occur.

However, this is actually very good news because it appears this is not an Oracle Database security hole! To me, it looks like the priority parameter is applied during the instance startup for just the background processes. Since my server process was started after the instance was started and for sure not included in the list of background processes, its priority was not affected. Good news for security, not as good of news for a performance optimizing fanatic such as myself.

Should I Ever Increase A Background Process Priority?


Now that we know how to increase an Oracle Database background process priority, when would we ever want to do this? The short answer is probably never. But the long answer is the classic, "it depends."

Let me give you an example. Suppose there is an OS CPU bottleneck and the log writer background processes are consuming lots of CPU while handling all the associated memory management when server process issues a commit. In this situation, performance may benefit by making it easier for the log writer processes to get CPU cycles, therefore improving performance. But don't even think about doing this unless there is a CPU bottleneck. And even then, be very very careful.

In my next block posting, I'll detail an experiment where I changed the log writer background processes priority.

Thanks for reading!

Craig.