I'm trying to run large sqlplus jobs in the background (Oracle 9.2.0.6). These jobs are mostly mass deletes from large tables, and index rebuilds. I run them with 'nohup' so they won't stop if my session stops. They are SO slow, and watching them with 'top', I see that the CPU is 90+% idle, and my background processes get very little processing time (the cumulative cpu time rises very slowly). It also seems that if I logoff (or if the VPN logs me off), the process, which is still running, drops to the bottom or near-bottom of the 'top' list. I have tried renicing to change this but to no avail.

How do I get the CPU to stop ignoring these processes? How do I get more CPU time for background jobs?

John F

Hi
I wouldn't fully rely on "top" for background processes. Sometimes in my case "top" was showing no sys/user/wait CPU activity, while nmon showed completely different figures. So, try first to download nmon or use "vmstat" and see what they say.
Otherwise how does the disk usage look like? And (perhaps a stupid question) what about the runtime of your jobs? How much "SO" slow are they? Isn't it that you're just running too many jobs, too many for the disks to keep up? On which kind of disk system do the Oracle datafiles reside and how large are the tables that you are modifying/rebuilding?

'vmstat' shows the same conditions. And, the box is not bogged down, the application isnt running. I dont know how the disk is performing: this version of 'top' doesnt' have an 'i/o wait' display, and there's no 'iostat' on this machine (or its not in the root user's path). How else can I get disk wait time?

jaf

Once you've invoked sqlplus to run the script, the actual processing is being done by the rdbms engine procs PMON, SMON, DBWR, LGWR, CKPT (iirc).
The client sqlplus proc uses very little cpu, most of the time.

nmon shows disk activity. Here are some of the keys:
"c": cpu
"m": memory
"d": disks
"t": top processes
...

I downloaded nmon and used it. CPU idle time is high, and oracle
processes needing CPU are getting very little. And by the way, it is the actual connection process, a child of the sqlplus process, that accumulates the CPU time.

So an import job ran all night, and by this morning had accululated 4 minutes of CPU time on a mostly idle machine! What controls this? How can I change it? Is is the scheduler? One article I've read recommends a different scheduler for database operations. Help! I haven't got time to wait for the OS to dole out CPU cycles at its leisure.

I note that foreground processes seem to get more CPU, regardless of nice value.

jaf