Oracle Exadata posts #1 TCP-H result

Grag Rahn's Structured Data blog provides the data that Kevin Closson had to remove from his own blog. From an HP/Oracle point of view, a very good performance, reducing cost/QphH by a factor of 4.

However, it is interesting to see that the HP/Oracle solution is still more than 4 times the cost/QphH of the #2 placed Exasol solution (running on Fujitsu Primergy, and reported a year ago) - while the absolute performance improvement is relatively slight (1.16M queries/hr against 1.02M).

Exadata - has it been in development for two years?

Just my nit-picking mind, but why does the Exadata technical white paper say (at the time of writing at least) that it is "Copyright © 2006, Oracle"? I don't think they've been working on it that long - much more likely some soon-to-be-embarrassed technical writer has cut and paste the standard boilerplate from an out of date source.

What, no rule-driven content management?

Exadata and the Database Machine - the Oracle "kilopod"

There has already been plenty of interesting posts about Oracle Exadata - notably of course from Kevin Closson here and here (update: and this analysis from Christo Kutrovsky)- but I just have one thing to say.

Larry Ellison was quoted in a number of reports saying the Oracle Database Machine "is 1,400 times larger than Apple’s largest iPod".

Larry, when you want to get over that something is big - really big that is, industrial scale even - just don't compare it with a (however wonderful) consumer toy. Not even with 1,400 of them. 1.4 kilopods is so not a useful measure.

By the way, can I trademark the word kilopod please? (presumably not - a quick google found a 2005 article using the same word, and there is some kind of science-in-society blog at kilopod.com).

Vote now to open up Metalink!

Richard Harding has proposed on Oracle Mix that Metalink should be opened up:

There is goldmine of useful information in Metalink and having access to it would optimize the efficiency of people using Oracle toolsets, in my opinion enhancing productivity and by inference Oracle adoption globally which would be win win for everyone.

I've expressed the same thoughts myself in the past. So all of you Oracle professionals who would benefit from access to Metalink but are not included in your employer's arrangements (especially for freelancers like me) - go and vote!

BEA Aqualogic broken up

The Register reports on how Oracle is going about the integration / breakup of BEA's Aqualogic SOA products (splitting them between ex-Stellent and Oracle Fusion product lines). Oracle's actions are described as "firm but fair".

Performance problems concatenating LOBs

Greg Pike talks about some potential performance pitfalls with LOBs; I would comment on his blog, but it's a login only. Anyway, I posted on these problems last year (specifically with DBMS_LOB.APPEND), and posted some code to deal with it. Batching up appends makes a big difference - a 15-20 fold improvement, in the example shown (against 9.2.0.5).

MS subpoenas Oracle in Juxtacomm ETL patent case

Vincent McBurney summarises the latest developments in the Juxtacomm "we invented ETL, yeah really, give us all your money" patent case, which I have mentioned before. Dang, don't these "obvious" cases take a lot of time and effort to clear up.

H-Store - a new architectural era, or just a toy?

Philip Howard's commentary Merchant relational databases: over-engineered and out-of-date? supports the idea that perhaps general purpose relational databases should now be treated as "legacy". He references a paper The End of an Architectural Era (It's time for a rewrite) by Michael Stonebraker and others from MIT.

My first thought was that RDBMS developers such as Oracle have seen off previous architectural challengers - most notably object oriented databases (OODBMS) - in the past 25-30 years. What makes Stonebraker's H-Store any different?

First, a quick summary of the paper:

• RDBMSs were designed 30 years ago - since then memory and cpu have become faster, cheaper and bigger, changing the balance against magnetic (disc) storage
• increasingly they are failing to meet today's complex challenges
• niche solutions have overtaken "general purpose" RDBMS in many areas (eg data appliances for business intelligence; specialist text search engines; etc)
• and now even OLTP, the "core competence" of the RDBMS, is no longer safe; a new approach (such as H-Store) can easily beat traditional RDBMS by cutting out non-functional architectural features (eg: redo logs stored on disc) and achieving the same goals (ACID transactions) in another way.

The paper claims that H-Store can beat a traditional RDBMS at TPC-C style benchmarks; an early version runs up to 80 times faster than "a very popular RDBMS" which itself underwent several days of tuning by a "professional DBA".

H-Store's secret sauce is that it is (in effect) single threaded. It makes the assumptions that all transactions are very fast; then it executes each transaction in turn. This gets rid of the need for complex read-consistency models. Other optimisations include keeping undo in memory (because transactions are short and sharp) and discarding it at the end of the transaction.

Well, go off and read the paper for the details, but here's what I think.

On the negative side:

• As a comparative benchmark, this fails through insufficient disclosure. For a paper that passes as academic, there is remarkably little detail on what they actually did.
• Stonebraker assumes that "most" OLTP systems can be represented by a hierarchical model - what he calls a "constrained tree application" (CTA). As a result these applications are relatively easy to partition over a shared-nothing architecture. I wonder whether this is really the case. Parts of your application may be like that, or they may be like that for some periods (during the online day, for example). But even OLTP applications need to manage longer transactions, complex reporting, and updates to the "read only" tables. In his example, he assumes (section 5.0) that the Items table is read only, so it doesn't break his tree and it can easily be replicated. But we know that new items will be added; others will be re-priced, re-categorised, phased out. Can that be handled without interrupting a 24/7 H-Store style application?
• He also seems to assume that there is only one axis of partitioning - in his case, the warehouse. But over time, the main focus of interest changes. An order is taken at a shop; it is ordered from a warehouse; it is delivered to the customer. Different "CTAs" at each stage. How does the H-Store morph its representation through the course of the information lifecycle?

On the positive side, though, this represents a call to action for the traditional vendors.

• Any performance specialist knows that the best way to tune something is to stop doing it. If we really can change the rules of the game, we can avoid all that expensive "insurance". We're used to making calculated design tradeoffs for performance; this could be just another one of those.
• I suspect that the RDBMS vendors will (more or less rapidly) steal any really good ideas. Stonebraker states that "no [RDBMS] system has had a complete redesign since its
  inception". But, like the proverbial axe, RDBMS internals have been refreshed, a piece at a time. Oracle's database kernel has had at least two major rehashes in its lifetime. It's well within Oracle's or IBM's capability to incorporate the more realistic ideas from this paper, and to find a way to blend them with current state of the art.
• They can also learn from the approach MySQL has taken of supporting multiple storage engines - horses for courses. Oracle already merges XML, relational and OLAP data stores; building in a high performance OLTP kernel to address specific classes of OLTP application is not at all inconceivable. Although it will lead to all sorts of information lifecycle difficulties, we are already used to migrating data from OLTP to OLAP; with good tool support it should be possible to work round the constraints that allow H-Store to dispense with so much that we normally take for granted.

I may revisit this paper to tease out other issues - for example Stonebraker rants against SQL (perhaps he's never got over Ingres being forced by the market to provide SQL rather than the more academically respectable Quel). H-Store uses C++, and may move to Ruby; the implication is that applications will be object-oriented, making row-by-row navigations (like so many J2EE apps, and suspiciously like COBOL/Codasyl) rather than being set-oriented.

Let's watch this space.

Patently ridiculous

SCO has had a pretty hostile press over the last few years as it morphed from a real software business into a patent mining outfit. Well, I've now come across another case of patent ambulance chasing over at Vincent McBurney's blog on ITToolbox. JuxtaComm and Teilhard versus IBM Microsoft Informatica SAP etc tells the story so far - basically that a small company is claiming it invented ETL, patented it in 1997, and can it now have loads-a-money if you please.

I followed Vince's link to Groklaw where InterSystems, one of the parties, is asking for help.

One of the parties is DataMirror (now part of IBM, and the purchaser of my former employer Constellar). As tne SQL Group, Constellar had an ETL system that seems to fit the key claims as early as 1994 (to my personal knowledge - it may have been on the market in 1993)

I'm stunned that (apparently) Oracle has already caved, handing over $2 million. One of the reasons I am so surprised is:

• I was actually working for Oracle UK in 1994 - the Constellar Hub (then known as Information Junction) was evaluated for one of my clients (who ended up purchasing ETI instead).
• Oracle UK was involved in quite a few pre-sales to Telecoms, Pharma and others over the period 94-2000.
• Oracle US consultants helped support the product in at least a few US clients.
• Constellar's US office was actually sublet from Oracle Corp, and I know many efforts were made to sell (the product and/or the company) to Oracle in around 1997 timescale.
• Oracle's own WarehouseBuilder must date from around then if not earlier.

Of course, Constellar wasn't alone at the time. We competed over time against ETI, Prism, Vmark/Ardent/Ascential, Informatica, and all the others (as well as the ubiquitous roll-your-own, which is still probably the market leader).

Slipping the litigants $2 million may be cheaper than arguing the case for Oracle - but for the rest of us (especially any cash strapped open source projects) it's not an option. And I'm sure a sharp lawyer at IBM might like a bit of ammunition to see them off. Hey IBM, you're innocent!

So if any ETL pioneer (or their heirs and successors) wants my testimony to fight off JuxtaComm's malicious and meritricious suit - just let me know! I'm available for testing out the trans-atlantic business class beds any time you like...

Update 13 March 2008: more from Vincent here.

I cite, you borrow, he steals...

Anyone who appreciates the hard work that goes into a decent presentation or book should read Doug Burns' tale of woe. Isaac Newton is widely quoted acknowledging his debt to other scientists: "If I see further, it is because I am standing on the shoulder of giants". Don't let's tolerate those who seek to take the credit for another's efforts without due courtesy or recompense.

I find it particularly annoying as Doug (whose blog I read regularly, but whom I have never met in person) seems to be particularly (even pathologically?) careful to acknowledge where he gets his ideas from.

Take care to read the comments as well as the blog itself. This one will run and run...

Happy UKOUG by the way (from darkest Lancashire at the moment)!

Sonic's Dave Chappell joins Oracle

Oracle must be well pleased to pick up Dave Chappell, formerly a VP and chief evangelist at Progress Software's Sonic division (he's still on the management team there, according to this link). When that gets taken down, you can find his O'Reilly author profile here.

Dave will be development VP for SOA/ESB. I wonder which products that includes - Oracle Data Integrator perhaps? Good luck Dave, enjoy the ride at Oracle - I hope we meet again at another JavaOne some day.

PS, don't confuse him with any other Dave Chappell (like this one who runs an eponymous IT consultancy and speaks (for example) on BPEL, or this one who (I think it's safe to say) has no IT connection at all.

Oracle captures Hyperion - a well planned thrust at SAP?

Lots of buzz today about Orace's rumoured - then confirmed - acquisition of Hyperion (formerly known as, and still largely known for, the eponymous Essbase multi-dimensional database). Oracle bloggers like Mark Rittman have concentrated on the BI side of things, but it is worth remembering that Hyperion has also assembled a set of financial applications (mainly planning and modelling, as you’d expect) - not to mention a BPM product line. That could be very interesting as an add-on to Oracle Apps (not to mention PeopleSoft and Siebel).

Looking at their latest Q2 results, there’s no breakdown of revenue between product lines - but it’s interesting that the headcount is 1745 in Americas, 632 in EMEA and only 212 in APAC. Oracle’s wider/deeper international network could give a big boost to sales in Asia (as well as making admin savings at home). They'll keep the salesforce but dump the top-heavy administration.

The financial market seems reasonably positive about the news (see for example Barron's Eric Savitz. As well as providing a sell-up for Oracle Apps, Peoplesoft and Siebel, this can also get Oracle's foot further into the door at SAP sites.

"Hyperion is the latest move in our strategy to expand Oracle’s offerings to SAP customers,” said Oracle President Charles Phillips. "... Now Oracle's Hyperion software will be the lens through which SAP's most important customers view and analyze their underlying SAP ERP data."

There is also a feeling that Oracle got a good price, catching Hyperion with its share price down. The impact on other BI players like Business Objects and Cognos is mixed: M&A activity might be expected to push up their price - but their share prices already factored in a bit of a punt on Larry Ellison coming round to tea; now he's spent his money on Hyperion, the others may well fall back.

Good luck to the Oracle BPM, BI and Apps product managers trying to make sense of it all!

PS: Another good take here from Curt Monash at DBMS2

Google Oracle - Good start, now what about Metalink?

The news is out - here from Eye on Oracle - that Oracle has finally opened the door to Google indexing of Oracle's documentation.

That's marvellous, but it's not exactly the fall of the Berlin wall. Many Oracle docs have been online and available for Google search for years - certainly for the database. Some of these have been (accidentally?) made available on customer websites (eg http://www.lc.leidenuniv.nl/awcourse/oracle/nav/docindex.htm - it's often universities) and others by Oracle themselves (eg http://download-east.oracle.com/docs/cd/B14117_01/nav/portal_3.htm). Just Google for key phrases like Browse the list of books, including PDF for printing for 9i or ADM link goes to the Administrator's Guide which gets you the 10g book list - and you'll see what I mean. I've included the 'repeat the search with omitted the results included' option.

What would be really useful is to open up Metalink itself (only customer SRs excepted). Too many Oracle developers need access, but can't get it because of short sighted officiousness. We developers (especially freelancers like me) often get the short straw - we're expected to be have all Oracle knowledge at our fingertips and yet we are effectively prevented from using one of the best available resources for it. Our employers just won't give us access to the support contract information necessary to get connected. BTW, I stress that the main fault is not Oracle's; it lies more with jobsworths (Brit expression I think - those people who say "it's more than my job's worth" as an excuse for anything).

Oracle Corp can and has made valid arguments for secrecy - mainly around its intellectual property rights and its competitors. But anyone who wants to indulge in industrial espionage can probably afford to buy a support license and get into Metalink anyway. The net effect is that the security defeats exactly the wrong group.

So go on Redwood - open up a bit more; you know you want to!

Problems with Statspack

Everyone's talking about it - statspack isn't perfect. Driven from Jonathan Lewis to Doug Burns to Daniel Fink's OptimalDBA I thought it was worth pointing out that as a snapshot technique, your knowledge of the value of the increment is uncertain.

Suppose snapshot N of metric has value s(N)
lets call the increment N to N+1 x(N)

All we can say for sure is:
A) if f(N+1) <>= f(N+1)
B) if f(N+1) >= f(N) then x(N) >= f(N+1) - f(N)

In normal use (when the shared pool is stable), we only see the case B, and we replace the >= with =. But if your shared pool is being trashed (see earlier posts) then remember uncertainty, and consider building case (A) into your reports.

Loading iostat output into Oracle

Alex Gorbachev posted an item on Pythian's blog last week about Basic I/O monitoring on Linux.

That's reminded me to post a couple of scripts here that can be used to load cpu and disk stats from iostat into Oracle tables.

I assume your iostat command line looks something like this:

iostat -x -t [interval] > iostat.log

You can then process iostat.log using this script to make it more SQL*Loader friendly:

cat iostat.log | processiostat > iostat.dat

And here's the processiostat script:

#!/bin/sh
#
# read and process an iostat.out file
# assumption is that it has been collected using -x -t
#
# Format looks like this (excluding leading comment sign
#=======================
#Linux 2.4.21-27.ELsmp (myserver.preferisco.com)        02/05/06
#
#Time: 13:01:58
#avg-cpu:  %user   %nice    %sys %iowait   %idle
#           0.96    0.06    0.49    1.93   96.55
#
#Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
#sda         80.85   0.00 22.62 29.90  388.10  522.72   194.05   261.36    17.34     0.00    0.08   0.02   0.11
#sde         44.04   0.00 12.19  1.78  203.58   14.28   101.79     7.14    15.59     0.01    0.61   0.33   0.46
#sdi         44.41   0.00 11.68  1.87  195.50   15.71    97.75     7.86    15.59     0.01    0.79   0.45   0.61
#
#Time: ...
#=======================
#
# Read the file from stdin
read os version longhost date
# extract hostname - works for rac2-4 at least
# discard empty line
read discard
while read label time
do
    # discard cpu headings
   read discard
   # read cpu figures
   read usercpu nicecpu syscpu iowait idle
   echo CPU ${date}-$time $longhost $usercpu $nicecpu $syscpu $iowait $idle
   # discard blank line
   read discard
   # discard headings
   read discard
   # now read in device stats
   device="START"
   until [ "$device" = "" ]
   do
      read device rrqms wrqms rps wps rsps wsps rkbs wkbs rqsz qusz await svctm util
      if [ "$device" != "" ] 
      then
         echo DISK ${date}-$time $longhost $device $rrqms $wrqms $rps $wps $rsps $wsps $rkbs $wkbs $rqsz $qusz $await $svctm $util
      fi
   done
done

That .dat output can then be loaded using this control file:

-- general ctl format for iostat tables
load data
infile *
into table IOSTAT_CPU REPLACE
WHEN (1:3)="CPU"
fields terminated by whitespace
( TSTAMP position (5) DATE "mm/dd/yy-hh24:mi:ss" 
, HOST
, USERCPU
, NICECPU
, SYSCPU
, IOWAIT
, IDLE
)
into table IOSTAT_DISK REPLACE
WHEN (1:4)="DISK"
fields terminated by whitespace
( TSTAMP position(6) DATE "mm/dd/yy-hh24:mi:ss" 
, HOST
, DEVICE
, RRQMS
, WRQMS
, RPS
, WPS
, RSPS
, WSPS
, RKBS
, WKBS
, RQSZ
, QUSZ
, AWAIT
, SVCTM
, UTIL
)

Then you can do comparisons, trend analysis, even draw pretty graphs using your favourite GUI (SQL*Plus in my case...)

Enjoy...

Using global context variables

I recently stumbled on another 'new' feature - though in fact it was introduced way back in Oracle 9i. A questioner on the OTN database forum wanted to know how to invalidate cached package state derived from a table that might be updated from another session.

My first suggestion was to implement a time-to-live cache - treat the data as stale if it has been cached for longer than X seconds (using DBMS_UTILITY.get_time to record the load time). All sessions manage their own private caches; a cache can be inconsistent for up to X seconds (if the underlying data is updated immediately after the cache is loaded).

Vetaran OTN poster Jens Petersen came up with what could be a much neater solution, which is to use a global context. You can define your own namespace for use with the standard SYS_CONTEXT function, and arrange to get/set attribute name/value pairs - on a per-session or instance-global basis. Like a DIRECTORY, a CONTEXT isn't owned by a specific schema.

Creating the context

To be able to create a context the user must be granted CREATE ANY CONTEXT privilege.

create or replace context my_context
using testuser.my_context_pkg
accessed globally -- including this makes attribute values visible to all sessions
/

A package is linked to the context; this is the only interface that can be used to set attribute values in the context. This coupling is a little inelegant, but it does mean that only users with EXECUTE permission on the package will be able to set values.

create or replace package my_context_pkg
as
procedure set_context
(av_attribute in VARCHAR2
,av_value in VARCHAR2
);
end my_context_pkg;
/

create or replace package body my_context_pkg
as
procedure set_context
(av_attribute in VARCHAR2
,av_value in VARCHAR2
)
is
begin
DBMS_SESSION.SET_CONTEXT
( namespace=>'MY_CONTEXT'
, attribute=>av_attribute
, value=>av_value
);
end set_context;

end my_context_pkg;
/

Any API you like to define is supported; this example is the simplest, allowing a completely unconstrained setting of attributes pairs. Here's how you define and use an attribute:

my_context_pkg.set_context('Attribute Name', 'Attribute Value');

Now, what about that original problem - how to notify active sessions that data in their package state might be out of date?

Well, there are two approaches: replace the cache directly with (one or more) CONTEXTs, or construct a dirty cache notification scheme:

Replace cache with CONTEXT

Simply maintain the cache as attribute/values in a CONTEXT. This could be appropriate if the cache was relatively simple (eg a set of lookup codes and descriptions). The PL/SQL caching can be completely discarded; instead use SYS_CONTEXT to get the descriptions, and set up triggers on the lookup table that use MY_CONTEXT_PKG to update the context. The only minor snag is that the context values need to be set (at least once) on instance startup; this could be a scheduled job, or be incorporated into a logon trigger.

Dirty cache notification

Define a 'cache update sequence' context attribute; let's suppose it uses MY_CONTEXT with an attribute name of 'cacheseq'.

When the package initialises its cache, it should read and retain the latest value for 'cacheseq' (treating null as 0). Later, before using values from the cache, it should re-read the latest value for 'cacheseq'. If the numeric value is greater than the retained value, the cache must be dirty. Applications that update the underlying tables (and so invalidate any caches) must increment 'cacheseq' - normally by using a statement level trigger on insert/update/delete.

my_context_pkg.set_context
( 'cacheseq'
, nvl(to_number(SYS_CONTEXT('MY_CONTEXT','cacheseq')),0)+1
);

Updating a CONTEXT attribute is not transactional, and almost certainly not read consistent; if an update is rolled back, we may find our cache being refreshed unnecessarily. If two updates to the table are made concurrently, we might see 'cacheseq' increase by one only. But our dependent sessions should all see that there has been some update activity.

Performance

Before jumping in and using CONTEXTs, I wanted to be sure that the performance wouldn't be a limiting factor. I didn't want to find SYS_CONTEXT calls producing a hidden SELECT ... FROM DUAL as we all remember for SYSDATE, USER etc in the past. So here's a quick test:

declare
li_cached_integer integer := 0;
begin
for i in 1..100000 loop
li_cached_integer := nvl(to_number(sys_context('MY_CONTEXT','cacheseq')),0);
my_context_pkg.set_context('cacheseq',to_char(li_cached_integer+1));
end loop;
end;

Fetching the value 100,000 times (including char to number conversion) takes around 1 second; saving the modified value 100,000 times takes an additional 2 seconds (on an Intel Pentium 4 3.4Gb dual core running XP SP2 and Oracle XE).

Exporting Context Definitions

IMP/EXP won't import/export these objects, and I presume datapump won't either; so the easiest thing to do is create a SQL script using DBMS_METADATA package:

select DBMS_METADATA.GET_DDL(object_type, object_name)
from DBA_OBJECTS WHERE OBJECT_TYPE='CONTEXT';

Don't specify a schema in the call to GET_DDL, if you do, you get:

ORA-31604: invalid NAME parameter "SCHEMA" for object type CONTEXT in function SET_FILTER


Dictionary Views

There are some dictionary views that can be useful:

ALL_CONTEXT	lists contexts that have been activated (ie a value has been set since startup)
DBA_OBJECTS	(where object_type = 'CONTEXT')
V$CONTEXT	lists attributes set in the current session (appears to ignore globally accessible attributes)

I'm planning to come back to this topic once I've had a longer play with multiple sessions setting and reading locally and globally accessible context variables.

The real cost of denormalisation

Perhaps the most common performance problem I used to come across in my Oracle consultancy days was inappropriate, excessive or just plain dumb denormalisation. Any number of inexperienced (or wrongly experienced) developers would proudly show me how they had 'optimised' their database design because joins are expensive, aren't they.

Ironically, this design decision would often be the direct cause of my visit...

Anyway, while browsing the always entertaining Oracle-L mail list yesterday, I tripped over Jared Still's Normalize for Performance paper - given at Hotsos 2006 a few weeks ago. He's explained some of the typical problems (and also ways around some of them with materialised views). I'll enjoy going through the examples next time I've got a couple of free hours...

Is this an Oracle WTF?

Using JDBC with connection pooling, we see this in our statspack (over a 15 minute period, on one of a 3 node RAC setup:

SQL ordered by Executions for DB: XXX

Executions	Rows Processed	Statement
192,827	192,826	select 'x' from dual
54,644	54,644	INSERT INTO AUDIT ...

We seem to get select 'x' from dual issued exactly once as each connection is opened. This can represent a significant fraction of the executions. I raised this as an SR and was told:

This is a standard feature in the later JDBC libraries (10.1.0.x onwards) and is designed to ensure that the connection remains up and running - this is documented in the JDBC documentation ... This can be altered but not stopped by setting the min connections in the datasource to a minumum needed.

See BUG:4601037 - PINGDATABASE() PERFORMANCE CAN BE IMPROVED IN BOTH DRIVERS (against JDBC 10.1.x) however this is not published, and not fixed until JDBC 11.1.

Is it just me, or does it seem mad to have to test that your connection is up by sending an SQL statement? That's the first thing the app is going to do itself anyway; why not detect the failure then? In a well tuned app, the sessions should spend very little time 'on the shelf' when the app is busy; the database is just as likely to disappear while the session is being actively used as while it is resting. So this logic simply provides extra protection to the very first JDBC operation in a transaction; why not at least be able to tell it not to send this 'heartbeat' query unless the pooled session has been idle for longer than some (configurable) threshold.

Cheers Nigel

DBMS_LOB.APPEND eats resources on 9.2.0.5

We're generating and storing XML data into CLOB columns as part of our reporting solution. We've been aware for a while that there are some performance issues with LOBs in 9.2.0.5, eg:

• 4280096 - HIGH BUFFER GETS FOR INSERT INTO LOB (fixed 9.2.0.8, 10.1.0.5 and 10.2)
• 3315549 - POOR PERFORMANCE WITH USING DBMS_LOB.WRITEAPPEND IN PLSQL LOOP
• 2602264 - DBMS_LOB.WRITEAPPEND() POOR PERFORMANCE WITH LARGE LOB AND SMALL CHUNK SIZE
• 2420525 - DBMS_LOB.WRITEAPPEND() PERFORMANCE ISSUES
• 2595083 - (description not published, although it's a base bug for some of the previous
  ones)

But I'd never sat down and actually measured how much difference it can make - until today. My test appended a short string (around 35 characters) 10,000 times, using each of DBMS_LOB.append, DBMS_LOB.WriteAppend, and TEST.LOB_APPEND - a procedure that buffers the appended strings, and only flushes to the CLOB when the buffer is full:

for i in 1..:loopcount loop
dbms_lob.append(lt_doc_clob, '('||i||')'||lv_padding);
end loop;

Here are the results from tkprof, for 10000 iterations, 30 chars of padding:

call	count	cpu	elapsed	disk	query	current	rows
Append	1	1.57	1.96	0	20087	194567	1
WriteAppend	1	0.45	0.48	0	9999	74343	1
MyAppend	1	0.10	0.10	0	182	2485	1

Repeating 1,000,000 times, timing from SQL*Plus, and trying to help by wrapping the whole lot in DBMS_LOB.OPEN and DBMS_LOB.CLOSE:



dbms_lob.append without open/close: 00:03:38.07
dbms_lob.append with open/close: 00:03:40.07
Simple dbms_lob.writeappend: 00:01:12.02
Append with buffering: 00:00:10.08


We used APPEND for lazy reasons (no need to keep working out the length of the buffer, which WRITEAPPEND needs) - but the subsequent cast from VARCHAR2 to CLOB clearly contributes substantially to the problem.

In our case these LOBs are also being manipulated by many concurrent sessions on a RAC environment - so we've seen evidence of (space management) contention for the LOB segments. Reducing lio's seems to help more than a little... at least while we wait for our client to upgrade to the latest patch - which doesn't look likely any time soon!

"Micro-partitioning" pooled sessions in a RAC environment

One of the problems we've come across testing an OC4J based system against an Oracle 9.2.0.5 RAC database is that as a given application session borrows database sessions from the session pool, it finds itself skipping at high speed around all the nodes in the RAC. If you're not careful, it's possible to insert or update some piece of data (on one database node) and then find that it's not quite there when you take a look a millisecond later via a different node.


You can work around this by setting MAX_COMMIT_PROPAGATION_DELAY to zero. However that does impose extra load on the cluster.


The problem arises because your connect string (in the JDBC URL) specifies a RAC service, something like this:

url="jdbc:oracle:thin:@(description=(LOAD_BALANCE=on)(address=(protocol=tcp)(host=rac1)(port=1521))(address=(protocol=tcp)(host=rac2)(port=1521))(address=(protocol=tcp)(host=rac3)(port=1521))(connect_data=(service_name=RACDB)))"

This typically load-balances across all nodes in an annoyingly random and yet not entirely effective way - especially when there is a sudden increase in connections. Oracle’s default load balancing mechanism is particularly badly suited to a connection pool.


What you'd really like to do is to pin application sessions to database nodes - so that you can be (reasonably) sure that a given user, once logged on, will keep on using the same RAC node (node failure excepted, of course). Just have each app server connect to its 'own' database server.


Unfortunately, the JDBC URL configuration is clustered. Whatever you set it to has to work for all nodes. So we came up with a simple workaround:

• Turn load balancing off


• Add some new IP address mappings to each app server node's /etc/hosts file


• use the 'virtual' hostnames in the JDBC URL

Now you have this URL:

url="jdbc:oracle:thin:@(description=(LOAD_BALANCE=off)(address=(protocol=tcp)(host=db1)(port=1521))(address=(protocol=tcp)(host=db2)(port=1521))(address=(protocol=tcp)(host=db3)(port=1521))(connect_data=(service_name=RACDB)))"


and in each /etc/hosts you have something like:


10.20.30.1 rac1.prt.stl.int rac1 db1

10.20.30.2 rac2.prt.stl.int rac2 db2

10.20.30.3 rac3.prt.stl.int rac3 db3



On each app server, the mappings from racN to dbN are modified, so that each app server "prefers" its own RAC server, forming a stovepipe.
If the app server tier is on nodes app1, app2 and app3, the mapping is:

RAC node	app1 mapping	app2 mapping	app3 mapping
rac1	db1	db2	db3
rac2	db2	db3	db1
rac3	db3	db1	db2

So in normal operation, we have a stovepipe all the way down from web to app to database tiers. Ideally we would have the web load balancer intelligently cluster like users together (eg by organisation or region) to further reduce cluster overhead, but that's currently politically unacceptable. Another time, perhaps!


Cheers Nigel