Using nice and priocntl to Change Execution Priority Oracle Database Tips by Donald Burleson

While CPU shortages generally require the addition of more processors on the server, there are some short-term things that you can do to keep running until the new processors arrive. Within the server, all tasks are queued to the CPUs according to their dispatching priority, and the dispatching priority is commonly referred to as the nice value for the task. Those tasks with a low nice value are scheduled ahead of other tasks in the CPU queue, while those tasks with a high nice value are serviced later (see Figure 6-5).

Figure 6-32: Task list and dispatching priorities

In emergency situations where you cannot immediately get more CPUs, you can assign a very low dispatching priority to the Oracle background process, causing them to get CPU cycles ahead of other tasks on the server. This will ensure that Oracle gets all of the CPU that it requires, but it will slow down any external tasks that are accessing the Oracle database. To do this, the systems administrator can alter the CPU dispatching priority of tasks with the UNIX nice or priocntl commands. The UNIX nice command is used to change dispatching priorities, but these numeric ranges vary by operating system. In general, the lower the nice value, the higher the priority.

Displaying the nice Values

In UNIX, you can use the ps ?elf command to see each task and its dispatching priority. In the following example, the NI column shows the existing dispatching priority for the task. Note that there are special nice values'sY (system) and RT (real time)?and these have the highest dispatching priority.

L 6-11

Changing nice Values

Again, we need to note that there are huge dialect differences when using the nice command. In Linux, you can use nice to change the dispatching priority, but in Solaris you must use the priocntl command. You must have root authority to change the dispatching priority, and you will need to consult with your systems administrator before changing CPU dispatching priorities.

Now that we have an understanding of the processors on an Oracle database server, let's turn our attention to monitoring the RAM memory consumption on our Oracle server.

Monitoring Server Memory Consumption

In the UNIX environment, RAM memory is automatically managed by the operating system. In systems with ?virtual? memory, a special disk called swap is used to hold chunks of RAM that cannot fit within the available RAM on the server. In this fashion, a virtual memory server can allow tasks to allocate memory above the RAM capacity on the server. As the server is used, the operating system will move some memory pages out to the swap disk in case the server exceeds its physical capacity. This is called a page-out operation. Page-out operations occur even when the database server has not exceeded the RAM capacity.

RAM memory shortages are evidenced by page-in operations. Page-in operations cause Oracle slowdowns because tasks must wait until their memory region is moved back into RAM from the swap disk (see Figure 6-6). The remedy for memory overload is to add more memory or to reduce the demands on memory by reducing sort_area_size, implementing the multithreaded server, or reducing the values for shared_pool or db_block_buffers.

Figure 6-33: Periodic RAM memory overload on a database server

Let's begin by looking at how memory is configured for a database server and explore how to manage memory on a large server.

Also see my notes on using the _high_priority_processes parameter to change dispatching priority of background tasks.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.