topic Re: Can a sql command be assigned a nice value? in Operating System - HP-UX

Can a sql command be assigned a nice value?

Joe Robinson_2 — Wed, 23 Mar 2005 16:24:52 GMT

just wondering...

if a script runs an sql script, does the nice value get passed on to the child process?

Re: Can a sql command be assigned a nice value?

Jim Mallett — Wed, 23 Mar 2005 16:28:06 GMT

This is taken from the nice manpage:

Normally, all processes inherit the system nice value of their parent process when they are spawned. The shell (sh, csh, ksh, etc.) can create a child process with a different priority from the current shell process by spawning the child process via the nice command.

See man nice for more details.

Jim

Re: Can a sql command be assigned a nice value?

Hein van den Heuvel — Wed, 23 Mar 2005 16:36:52 GMT

Are you going through tnsnames / the listener or connecting directly and locally? The oracle slaves will inheret the listener attributes.

If I need to renice (or nasty) Oracle slaves, then I tend to need a quick sql script to run over v$sessions and/or v$process

Hien.

Re: Can a sql command be assigned a nice value?

TwoProc — Wed, 23 Mar 2005 17:49:08 GMT

Joe, the oracle shadow process that's going to do all the work for you is the the thing that you'd like to renice (I think). This gets its value from the tns listener.

Kind of fishing through this list:
SELECT vp.spid||' '||vs.sid||' '||vp.pid||' '||vs.username||' '||vs.osuser||' '||vs.machine ||' '|| vs.module||' '||vs.program||' '||vs.process||' '||vs.action
from FROM v$session vs, v$process vp
WHERE vs.paddr = vp.addr(+)

you can come up with a script that will suit your needs.
The following generates a list to a file that can be run from a unix looping script to renice things for a user connecting as oracle user 'JOER' from OS user 'jrobinson' --
if it's JOER and user jrobinson -it's reniced to 5, if it's JOER and anyone else, it's reniced to 7, otherwise in the default case it's niced to 9. These numbers won't make sense for you - you'll have to look at what you want to do to set them properly. In this example I used single digits b/c it allowed me to cram them on a single line on the screen hoping that they would display the same for the forum readers (it probably won't).

There's another example for a ksmith in there, so you can see how to add more decodes as you need.

set heading off;
set feedback off;
set pagesize 500;
set linesize 280;
spool reprioritize

SELECT decode(nvl(p.spid,'x'),'x',' ','renice -n ')||
decode ( nvl(p.spid,'x'),'x',' ',
decode ( s.username,
'JOER',decode(s.osuser,'jrobinson','5','7'),
'KSMT',decode(s.osuser,'ksmith','8','9'),
9))
||' '||p.spid

exit;

This would create the file reprioritize.lst
do a chmod on it, and then you can run it to renice that process. Since it's a renice command, you'd have to run the output file as root (use sudo command).

You can/should of course use others of the columns I indicated in the first generic statment above to make your own rules.