Using nice with sqlplus 1

[kasparov]

Does anyone have any comments about using nice to decrease the priority of running sqlplus?

I need to run a PL/SQL program which is quite processor intensive – I can run it at night when I won’t affect users but I’d like to run it during the day. I’ve tried using nice but my (very cursory) testing indicates that using nice -19 (which should make my sqlplus session run at the lowest possible priority) still affects the application significantly.

2 questions:
• Would we expect

nice -19 sqlplus ‘/ as sysdba’

to affect a PL/SQL program subsequently called within the sqlplus session? I assume so because any process called by another should inherit its parent’s priority.
• Has anyone else tried this – what’s your experience?

I’m interested to hear what people have to say about this. For info this is Oracle 9.2 on Solaris 9.

Chris

 

[hoinz]

Hello,

I never tried it myself. I agree with your assumptiom that any process called by another should inherit its parent's priority.

If you see that sqlplus still affects the application significantly:
Did you verify that 'nice -19' did work, e.g. with 'ps -el'? (not sure about the correct Solaris way, though.)
Both sqlplus and its Oracle shadow process should show a value of 39.

I am asking this because, depending on your shell, 'nice -19' might not do what you are expecting.
E.g. in C-shell you would have to use 'nice +19'.

regards

 

[kasparov]

Thanks for the reply hoinz. I wasn't aware of the -l flag to ps (or the PRI & NI values) - I'll give it another go & confirm that it's trying to run it at a lower priority anyway.

Any more comments welcome.

 

[kasparov]

Have tried again and yes they (the sqlplus & the child process) are running at a lower priority. But I still suspect it's affecting the users more than I would have hoped.

I'm curious to find out so I'm running it now (during the day) anyway & I'll see if anyone complains (probably not what a good System Administrator should do ...)

 

[SamBones]

You have to keep in mind that SQL*Plus just hands the query off to the database processes which will do the actual work at their normal priority. SQL*Plus communicates with the DB processes via the SGA (shared memory), so there is no spawned process to inherit the priority. Your only solution would be to run the entire database at a lower priority which would be pretty disasterous to everyone.

Also, nice doesn't guarantee that a process will get less CPU. The priority is used by the kernel when scheduling the run queue, but if a process is needing more CPU than a higher priority process, it will still be getting a large amount of processing just due to it needing to be in the run queue more. In other words, if you have a CPU intensive process, it will get high CPU usage regardless of it's priority.

The biggest way to reduce the CPU usage that a query imposes on the server is to look at optimising the query. That may be just changing how it does a join, or adding an index to change how the query is processed. An "EXPLAIN PLAN" should be your first stab at reducing it's load on the system.

Hope this helps.