SAP R/3 has taken advantage of this approach for a long time now. It buffers
tables based on settings in the R/3 data dictionary and can buffer single rows,
groups of rows, or entire tables. Tables which are used to store configuration
information are typically fully buffered, while transactional tables are
buffered using either single rows or generic keys. Tables which have high
concurrency aren’t generally buffered at all to prevent inconsistent views of
the data from different application servers. All app servers in a given system
synchronize their buffers (default every 60 seconds if memory serves); if a
buffered row is updated, it’s marked dirty in the buffer, then the dirty bits
are sync’d to the other app servers. The first app server to request that row
re-validates the buffer.
They also handle sequences in a similar way; SAP uses “number ranges” rather
than relying on vendor specific sequences. A number range is just a table of
“min, max, current” numbers basically. For something like sales orders, it
doesn’t really matter if you skip a few numbers occasionally, so they allow you
to buffer these as well, and control how many are buffered. For example, in a
system with 2 app servers and a buffer size of 10 with a number range 0000 –
9999 and current number 1000, the first application server will reset the
“current” field in the table to 1010 and allocate 1000-1009 for itself; all
requests for a sequence for that number range are answered locally on that app
server. The disadvantage to this is that if the app server crashes, you can
lose potentially 10 numbers from your sequence, and your sales orders (or
whatever you’re numbering) can get out of sequence (i.e. not monotonically
increasing over time). Generally this isn’t a problem, but they do allow you
to disable this for each individual number range if you have contractual or
legal requirements for doing so.
Having directly seen the performance impact of both table and sequence
buffering on the application server, I can attest that it’s very useful. A
buffer access on the app server is a micro-second operation, whereas a database
access over the network that’s served from the DB’s buffers is on the order of
10’s of milliseconds. One that has to go clear to disk can be in the 100’s of
milliseconds from the application’s point of view. Granted these numbers
improve every year with technology, but the idea is that memory is faster than
database buffers, which are in turn faster than going clear to disk for
something…
Cheers!
Rich
--
Rich Holland (913) 645-1950 SAP Technical Consultant
print unpack("u","92G5S\=\"!A;F]T:&5R(\'!E<FP\@:&%C:V5R\"[EMAIL PROTECTED]");
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rich Holland
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
