Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Recipe for application design to run on RAC

Re: Recipe for application design to run on RAC

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Fri, 29 Nov 2002 14:53:40 -0800
Message-ID: <F001.0050F370.20021129145340@fatcity.com>


To be more precise, the real problems in application-partitioning for OPS/RAC are UPDATE, SELECT ... FOR UPDATE, and DELETE statements due to their WHERE clauses...

A SELECT statement does not force exclusive access to a database block and so does not directly cause contention for a block in OPS/RAC. An INSERT statement also does not cause contention on tables because the use of FREELIST GROUPS can keep blocks utilized by different instances separate from one another. If the INSERT is inserting a monotonically-ascending data value into an associated index, then contention on the highest-value leaf block can be reduced by using REVERSE indexes. If the INSERT is not inserting monotonically-ascending data values into any indexes, then contention during parallel cache management between indexes should be minimal.

So SELECTs are inherently benign and INSERT operations can be controlled with mechanisms to prevent inter-instance contention for blocks.

It is the UPDATE, SELECT ... FOR UPDATE, and DELETE statements which truly require consideration in making an OPS/RAC-based application scaleable. If these statements, which operate on database blocks according to their respective WHERE clauses generated by application logic, do not have some form of "awareness" of assignment of certain data values to specific database instances, then one can expect problems in scaling. Neither OPS nor RAC has any mechanism to minimize contention between instances for block buffers (as with INSERT statements), so it is up to the application itself (which controls the generation of the WHERE clause) to "segregate" the application somehow. Whether it is by major application module (i.e. "sales and marketing" versus "order entry and inventory" versus "general ledger", etc) as Boris had illustrated, or by some other mechanism (i.e. all customers whose names start with A-M on one node, all whose names start with N-Z on the other node, etc), the application must be able to partition.

---

In Oracle7 OPS and Oracle8 OPS and Oracle8i OPS, the mechanism (i.e.
"pinging" performed through the I/O subsystem) was quite slow on most
platforms, resulting in huge latencies.  The major exception to this rule
was DEC/Compaq/HP OpenVMS, where the performance of the "pinging" mechanism
is so fast as to be quite unnoticeable.  Not surprising if one considers the
history of VMS and OpenVMS...

Beginning with pieces of the cache-coherency mechanisms in Oracle8i OPS and
fully implemented in Oracle9i RAC, the "cache-fusion" mechanism still
performs the same locking and data-transfer of database block buffers
between instances, only faster.  How much faster is dependent on the OS and
configuration.  But the additional latency is still there.  Obviously, if
the inter-connect mechanism between nodes is not fast or misconfigured, then
"cache-fusion" cannot be fast either...

---

When Oracle states that "applications can be migrated to RAC without
modification", they are saying so in the faith that the reduced latencies in
the cache-fusion mechanism and other improvements in the
sharing/modification of global enqueues will result in almost-zero latency,
or at least latency that is within the tolerance of the end-users.  As the
old saying goes, "your mileage may vary" or YMMV.  As OpenVMS and its near
zero-latency "pinging" mechanism shows, the choice and configuration of
platform really matters also!

---

In order to assess if an application is likely to scale effectively when
migrating from non-RAC to RAC, I would pay close attention the nature,
frequency, and volume of UPDATE, SELECT ... FOR UPDATE, and DELETE
statements generated by the application.  While still in its non-RAC
implementation, I would recommend collecting and examining such SQL
statements generated by application and understanding what program modules
are generating each statement and why.  I would then prioritize these
statements by their volume and the business criticality of the generating
program module.  Last, according to this prioritization, I would examine how
the WHERE clauses and the data values used in them can be controlled by
application logic.  For example, if a business-critical online form is
generating lots of UPDATE and SELECT ... FOR UPDATE statements, is it
possible to determine whether those statements are generated against rows
previously INSERTed by the same session?  Or, does that online form perform
UPDATE and SELECT ... FOR UPDATE operations against any data in the database
at all?

Some applications are really quite "partitionable" under the covers, and
only a small amount of such analysis can assure you that RAC is feasible.
Other applications are so dreadfully complex that only by load-testing with
real-world data values can the scalability be determined...

Oracle has correctly identified all of the major bottlenecks in
inter-instance contention and has improved each of these areas in RAC since
OPS.  The question is whether the improvements are sufficient for your
requirements...

---

Relevant wisdom from "The Meaning of Life" by Monty Python (1983):

    Mother (going through labor pains, alert and panicky):  What's that for?

    Obstetrician #1:  That's the machine that goes "ping". << PING! >>  You
                              see?  That means your baby is still alive!

    Obstetrician #2:  And it's the most expensive machine in the whole
                              hospital!

    Obstetrician #1:  Yes, it cost over three quarters of a million pounds!

    Obstetrician #2 (slowly and condescendingly):  AREN'T YOU LUCKY?


----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Wednesday, November 27, 2002 8:49 AM



> Thanks for taking time to reply, Cary. Much
> appreciated.
>
> Did I understand it correctly that in active/active
> setup it would be beneficial to give each node "it's
> own virtual empire" so to speak.
> Like one node to service say marketing and sales,
> while the other to deal with say inventory and
> automation and minimize interdependencies between the
> two?
> I was thinking more along the lines of equally
> distributing/balancing the utilization across the
> nodes (which presumably makes it easier to re-route db
> calls to surviving node in case of instance/node
> failure after remastering, since all nodes are
> "peers")
> I obviously need to do some serious RTFMing here.
>
>
> So if the key is to have application partitioned (by
> probably functional/business areas?), is it at the
> logical design stage that this needs to be accounted
> for?
> Assuming enterprise framework in place, like Zachman's
> (http://www.zifa.com/framework.html) would it be at
> the system model/logical level (or using Oracle
> Designer terminology I guess at the system analysis
> stage) that "design for RAC" comes to the picture for
> a first time?
>
> Thanks again.
>
> --- Cary Millsap <cary.millsap_at_hotsos.com> wrote: >
> If two or more RAC instances will be trying to cache
> > the same data
> > blocks, then this causes the performance problems
> > that you'll see show
> > up as lots of time spent on the event called "global
> > cache cr request".
> > If you can partition your application so that RAC
> > nodes don't have to
> > share blocks very often through the cache fusion
> > mechanism, then your
> > system will scale a lot better.
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Hotsos Clinic, Dec 9-11 Honolulu
> > - Hotsos Clinic 101, Jan 7-9 Knoxville
> > - Steve Adams's Miracle Master Class, Jan 13-15
> > Copenhagen
> > - 2003 Hotsos Symposium, Feb 9-12 Dallas
> >
> >
> > -----Original Message-----
> > Sent: Tuesday, November 26, 2002 3:34 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > Dear List,
> >
> > Number of times I've seen that one of prerequsites
> > for
> > switching from single node DB to OPS/RAC is to have
> > an
> > application specifically designed / architectured to
> > run on RAC.
> > Can somebody elaborate? Is it something "visible" on
> >
> > ERD? That is by looking at the model can RAC guru
> > tell
> > that it wouldn't work well on RAC?
> > Or put it another way can one conclude based on the
> > ERD that app was modeled to run on RAC?
> >
> > What's the recepie for app design for RAC?
> >
> > TIA
> >
> >
> ______________________________________________________________________
> >
> > Post your free ad now! http://personals.yahoo.ca
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Boris Dali
> > INET: boris_dali_at_yahoo.ca
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Cary Millsap
> > INET: cary.millsap_at_hotsos.com
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
> >
>
> ______________________________________________________________________
> Post your free ad now! http://personals.yahoo.ca
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Boris Dali
> INET: boris_dali_at_yahoo.ca
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: Tim_at_SageLogix.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Fri Nov 29 2002 - 16:53:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US