Re: using all available CPUs

Remember, Oracle runs a whole bunch of processes at the same time... DBWR, LGWR, SMON, PMON, Sxxx, Pxxx, and so on, along with other operating system (OS) and application processes. When SQL statements are executed, Oracle starts up one or more processes that are managed (by the OS) like any others on the machine. (With a parallel query, as you are aware, Oracle starts up multiple processes for the single statement.)

The OS schedules processes onto any available procesors, so a given process can be running "on" any processor at any particular time. So, in short, no, each statement does not "get its own" processor.

There is no restriction as to which tables or tablespaces can be accessed concurrently by multiple processes. Oracle keeps track of which processes modify which rows in which blocks in which files, and is able to provide consistent views of the data to each statement. This, of course, does lead to situations where multiple processes are in contention for the same resource, and one of the statements has to be aborted and rolled back.

So, in your application design, to provide good performance, you'll want to avoid situations where multiple processes are attempting to modify and hold locks on the same row. Received on Sat Jan 01 2000 - 15:47:07 CST