Whoa! Tim, thanks a lot for sharing this. Quite an
insight.
So SELECTs are not a concern. INSERTs are a "come and
see DBA" thing (physical design issue).
DELETEs are relatively infrequent and many get
translated into UPDATE (logical as opposed to physical
delete).
Application "partitioning" as you clearly explained in
your email... Would it be closer to a logical or
physical design?
Seems like something that data modeler/architect
should be aware of. So in a sense all modeler needs to
worry about is UPDATEs as far as future physical
implementation for RAC is concerned?
The reason I get stuck on "phys vs logical" here is
because client I am with has a clear separation
between the two.
It's not only different people that deal with it, but
in fact different vendors.
<soapbox>
Some background (I probably should've included it in
my original post):
Mission critical system to replace around 30 small
in-house developed apps and do some business
re-engineering as we go :(
Data modeling is done by one vendor, development by
another (don't ask), DB support and maintenance are
left for internal DBA staff
One of the conditions that CTO office "mentioned" to
the modeling company is to keep HA requirements (not
really defined yet - but that's another story) in
mind.
Ok, they turn around bring the data model and declare
that they not only "kept it in mind", but in fact
their model is "RAC aware".
Well I can't describe how happy damanagement is - such
a successful choice they made (to pick this particular
company)!
But the curious side of me wonders how this data model
is different from the one designed for a single node
DB?
</soapbox>
And another thing. Tim, you explained clearly how
application should be assessed for non-RAC to RAC
migration.
In my case however application exists mostly on paper
(not taking into account these 30 micky mouse apps -
the new system suppose to cover much more than that).
- I guess simulation would be one way to estimate
SQL statements of the app and make a decision on
whether it can scale on RAC well or not.
(And BTW simulation might be worth the trouble
irrespective of whether we use RAC or we don't)
But frankly so far I've seen prototyping or simulation
for the sake of sizing, capacity planning,
understanding DML and query profiles, critical tx,
memory footprint required etc, etc... only in James
Moorle book :(
That is not prototyping for the sake of "getting
client involved at the early development stages" -
that's been done with all new apps here. And not
benchmarking of already existing application, but the
one that hasn't been developed yet.
- More importantly simulation is obviously the most
expensive of all alternatives. Analytical modeling of
some sort would be much more welcomed by damanagement.
What would be your take on this? How new essentially
designed from scratch app can be assessed if it can
scale well on RAC?
Thanks again for your help!
- Tim Gorman <Tim_at_SageLogix.com> wrote: > 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
>
=== message truncated ===
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).
Received on Wed Dec 04 2002 - 09:53:58 CST
