Question by Michael Wilson: What is the mechanism to set
priority for batch-jobs? I want by stored procedures to have lower
priority than the real time processes. We want the batch job access
wait, while the application processes get a higher dispatching priority.
The batch
jobs are submitted via dbms_scheduler and I want them to have less
priority after they are submitted.
Answer: When you refer to "priority" we mean the CPU dispatching
priority, the queue that manages how Oracle processes receive cycles. Oracle
sessions create a process which is visible to the operating system.
Once you
have found the PID for these session associated with the PL/SQL stored
procedure, you can use the "nice" and "priocntl" commands to change the
dispatching priority. Just remember, the higher the nice number,
the LOWER the task priority.
When I "nice up" a batch job (remember, a high
number is a low priority), I do it from the OS side.
1 - Schedule a cron job to start one minute
after the Oracle job starts.
2 - Use a "ps" command piped to "grep" to find the OS
process associated with the PL/SQL stored procedure
3 - Issue the "nice" or "prioctl" command to make the batch job
lower in the CPU dispatching queue.
If you wan to do it all with Oracle, you can
issue OS commands from inside Oracle but it's tricky.
You can also use Oracle resource groups to
prioritize CPU dispatching, although this approach is not widely used.
The docs note:
"CPU Method
This method enables you to specify how CPU resources are to be
allocated among consumer groups or subplans.
Multiple levels of CPU resource allocation (up to eight levels)
provide a means of prioritizing CPU usage within a plan schema.
Consumer groups and subplans at level 2 get resources that were not
allocated at level 1 or were not consumed by a consumer group or
subplan at level 1. "
For full details, there are two books that will
help you understand Oracle batch job scheduling and management:
Again, the first step is to find the OS process
associated with the PL/SQL and then change the CPU dispatching priority
for that process.
How to find the OS PID for an Oracle session task
PL/SQL runs inside the individual OS process that is associated with the
"session" and you can track backwards from the session to the OS process
with the
v$session and
v$process views..
The session maps to an OS process, that that's what you need to use
"nice" on:
Once you identify the OS PID for the PL/SQL then you can use "nice"
or "priorctl" to change the dispatching priority for the process. You
can write a shell script to do this automatically.
The Oracle background processes and the sesion processes (with their
PGA) are the only processes that the OS is aware of, and all dispatching
is done at the OS level!
In Windows, Oracle controls the dispatching of his own processes (the
"thread" model), whereas in UNIX/Linux, the OS controls them.
This is how Oracle see's a session and his related process:
select
spid,
osuser,
s.program
from
v$process p,
v$session s
where
p.addr=s.paddr;
For full scripts to manage processes, task and sessions, see the
Oracle
scripts collection.
How to change dispatching priorities
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.
In emergency situations where you cannot immediately get more CPU
capacity, 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.
In UNIX and Linux, you can use the
ps elf command to see each
task and its dispatching priority. In the example below, the NI column
shows the existing dispatching priority for the task. Note that there
are special nice valuesSY (system) and RT (real time)and these have
the highest dispatching priority.
root>
ps -elf|more
F S UID PID PPID C
PRI NI
SZ
STIME TTY TIME CMD
19 T root 0 0 0
0 SY
0
Dec 21 ? 0:00 sched
8 S root 1 0 0 41
20
98
Dec 21 ? 0:00 init -
19 S root 2 0 0
0 SY
0
Dec 21 ? 0:00 pageout
19 S root 3 0 1
0 SY
0
Dec 21 ? 22:13 fsflush
8 S root 182 1 0 41
20
217
Dec 21 ? 0:00 usr/lib
8 S qmaill 173 161 0 41
20
207
Dec 21 ? 0:00 splog
8 S root 45 1 0 48
20
159
Dec 21 ? 0:00 devfs
8 S root 47 1 0 49
20
284
Dec 21 ? 0:00 devf
8 S root 139 1 0 46
20
425
Dec 21 ? 0:00 syslod
8 S root 126 1 0 77
20
247
Dec 21 ? 0:00 inetd
8 S root 1600 1 0
0 RT
268
Dec 22 ? 0:00 xntpd
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.
The Oracle system administrator guide notes that dispatching is done
at the server-level:
"The service class of the Oracle regions determines the relative
dispatching priority of the background processes and other special
tasks within the Oracle instances. The Oracle regions typically
consume very moderate amounts of CPU resources. Normally, the bulk
of the CPU resources that are consumed to process database requests
are incurred by the client address spaces for local requests .
. .
In general, the Oracle regions can be configured for lower
dispatching priority (or lower importance) than high priority CICS
TS and TSO workloads, and at about the same priority as high
importance (or non-discretionary) batch workload.
If the Oracle dispatching priority is too low, and if the system
suffers from significant CPU contention (indicated by high processor
delay in the Oracle regions), then some important Oracle internal
requests might not get immediately processed, or the background
tasks might not get dispatched often enough to perform the required
work."
Also see my notes on using the
_high_priority_processes
parameter to change dispatching priority of background tasks.
