Re: Oracle RAC Stored Procedure scalability

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Thu, 03 Aug 2006 08:10:24 -0400
Message-ID: <4je7bmF7jc3fU1@individual.net>

HansF wrote:

>> The different members of the cluster all access the same disk.  So if the
>> sql statements can scale horizontillay then the stored proc can
>> Jim

>
> In support of that, if the SQL statement can take advantage of parallelism
> on an SMP box, it can also take advantage of RAC.
>
> The network traffic concern is somewhat valid until you realize that a
> separate network (including separate network cards) is needed for RAC
> interconnect purposes anyway.
>
> The question implies some holes in basic understanding of RAC
> architecture. As that is too large a topic to review in a newsgroup,
> I'd suggest it's time to hit the RAC concepts books (again).
>

Actually I don't think the poster is asking a bad question here. Procedures are in their very nature procedural. That means that by default statements are to execute in a specific order. Hence the procedural logic is, by default, a serialization point. Each SQL statement can parallelize, but the logic itself cannot.

When the user knows that e.g. a cursor loop with n SQL statements inside can be parallelized then executing n-procedures in parallel without stepping on each others toes, each processing a part of the cursor makes a lot of sense.
I have seen this in Oracle PL/SQL applications (especially batch processing) and I use it myself.

Now if Oracle has solved the problem of transparent parallelization of logic in general without instrumentation of the language I'm sure the parallel Fortran compiler guys downstairs from me would love to talk to them :-)

Note my use of "by default". Contructs like FORALL or BULK COLLECT are specific language directives.

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Received on Thu Aug 03 2006 - 07:10:24 CDT