Re: using all available CPUs

Right, but in order for oracle to allow multiple SQL's to be running concurrently, it needs to spawn off multiple processes that handle those statements. I assume on single processor machines, Oracle just uses threads to handle the concurrencey, but on most systems threads can not be shared across processors. Does oracle spawn a certain processes more than once? Does the CPU_COUNT parameter control the number of this type of processes that is spawned?

Another question:

In a N processor machine, how does oracle handle scheduling the SQL statements to run. Does it pass off a SQL statement to another processes (if there are some idle) for a processes with spawn off another thread and run the SQL then. Are there any parameters that control the number of threads the processes will spawn before putting a wait on the SQL?

Thanks.

spencer wrote:

> 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 Sun Jan 02 2000 - 22:54:52 CST