HPUX_SCHED_NOAGE parameter, improve OLTP throughput
Posted by John Hallas on May 27, 2009
Sometimes it is easy to miss the simple stuff and I must admit that although I have managed Oracle on HPUX over many years I have not in the last few years (mostly Solaris and Linux).
Having started work at a solely HP site in the last year I never bothered to read the Installation guide as a tried and tested set of practise were in place.
Recently we were having problems with a Peoplesoft database and we went the whole hog in re-striping disk and re-evaluating everything. A HP specialist mentioned the init.ora parameter HPUX_SCHED_NOAGE which I had not come across before. On investigation the parameter was in place on our 10.2.0.3 databases but set to 0. Looking at some 11g databases I was surprised to see that it had been set also but with the recommended value of 178. Obviously it is now enabled by default on 11g on HPUX
So what does it do. Well in a non Unix specialist way I can describe it as ensuring that all the database processes run at the same priority and therefore do not get aged out as new processes come along and look for resource. Older processes are more likely to stay on the CPU which results in less context switching. On a busy system it is likely that the lgwr process will perform better as it gets more time on the CPU and is not switched out as often to make way for a process that is waiting in the normal time-share manner. It is not expected that gains will be seen in OLTP systems rather than DSS systems as there will be more process competing for resource than in a DSS system where fewer longer running processes will be the norm.
Whilst writing this up I came across I came across a blog that describes it all in a much more technical manner than I can, complete with diagrams Christian Bilien
Metalink note 217990.1 (Mar 2009) states This could be suited to online transaction processing (OLTP) environments because OLTP environments can cause competition for critical resources. Overall performance improvements of 5 to 10% for OLTP applications.
If the parameter setting is out of range, Oracle sets the parameter to a permissible value and continues with the SCHED_NOAGE policy with the new value.
It also generates a message in the alert_sid.log file about the new setting. Oracle Corporation recommends that you set the parameter to the required priority level for Oracle processes.
The Oracle manual also shows you how to set the unix pre-reqs for the HPUX_SCHED_NOAGE init.ora parameter to work
To permit Oracle Database to use the SCHED_NOAGE scheduling policy, the OSDBA group (typically, the dba group) must have the RTSCHED and RTPRIO privileges to change the scheduling policy and set the priority level for Oracle processes. To give the dba group these privileges:
1. Log in as the root user.
2. Using any text editor, open the /etc/privgroup file, or create it if necessary.
3. Add or edit the following line, which begins with the name of the OSDBA group, specifying the privileges RTPRIO and RTSCHED that you want to grant to this group every time the system restarts:
4. Save the file, and quit the text editor.
5. Enter the following command to grant the privileges to the OSDBA group:
# /usr/sbin/setprivgrp -f /etc/privgroup
Enter the following command to verify that the privileges are set correctly:
# /usr/sbin/getprivgrp dba
So did it make any difference to us. I can only give an example of how it helped a specific problem for us. On a Peoplesoft HR system we made 2 changes, bad practise I know but time constraints forced it upon us. We added the scheduling parameter and also reduced the filecache_min and filecache_max kernel parameters from 3%,5 % to 1%,2%. These parameters control the amount of physical memory used for caching file I/O data and we were tight on memory anyway. On a specific time and labour batch processing benchmark we saw an immediate improvement of 12% which was very acceptable.
PS to show the process in Glance use ‘s’ then select any PID associated with the database and note in the left hand column that scheduler=NOAGE and Priority=178