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).

Joins over histograms

Here's an awesome paper from Alberto Dell'Era and Wolfgang Breitling looking at "the formula used by the Cost Based Optimizer to estimate the cardinality of a [single-column] equijoin, when both the columns referenced in the join predicate have histograms collected."

Awesome because of the thought given to the presentation, and the care taken to test the results. Alberto has followed the lead taken by Jonathan Lewis's CBO book to carefully deconstruct and reverse engineer the algorithms used - with one or two surprising results.

Well worth a read...

Unique IDs for multi-master replication - Sequence or SYS_GUID?

Oracle-L is proving to be a good source of inspiration at the moment. Oracle ACE Syed Jaffar Hussain asked the question Is it possible to share a database sequence between multiple databases?

Using remote sequences
A couple of the replies took the question a bit too literally, and said yes, you can define a sequence on one database, and use it from another:

Select SequenceOwner.RemoteSeqName.NextVal@DBLinkName From dual

This works - and it can be made 'location transparent' by creating a local synonym for the remote sequence. But it is asymmetric (one database has to own the sequence) and it introduces a point of failure. If database 1 owns the sequence, database 2 can only insert rows if database 1 is actually available. If you could guarantee availability, why would you bother with replication?

Using a sequence local to each database
Several replies (including mine) suggested using carefully defined sequences which will deliver discrete values on two or more master databases. There are two basic patterns:

Partitioned: Suggested by several posters, the number space is divided up in blocks:

on db 1: create sequence myseq start with 1000000 max 1999999 
on db 2: create sequence myseq start with 2000000 max 2999999

A variant on this is to use positive numbers for database 1, and negative numbers for database 2.

Interleaved: The more popular option is to use the old trick of assigning odd numbers to database 1, and even numbers to database 2:

on db 1: create sequence myseq start with 1 increment by 2
on db 2: create sequence myseq start with 2 increment by 2

This mechanism is much easier to manage (essentially, it doesn't need any further management). It is also easy to extend to 3, 4, 27 or 127 masters - just set the "start with" to the database number, and "increment by" to the maximum anticipated number of databases required.

A third option was also proposed by Mark D Powell: use SYS_GUID(). That has some disadvantages:

• SYS_GUID() is bigger (16 bytes on 9iR2) than a NUMBER (eg a 13 digit integer needs around 8 bytes). Obviously the extra space follows through to indexes, foreign keys etc.


• Another is simply that it is a RAW, which has some possibly undesirable implications; for example several common tools (including SQL*Developer 1.0) can't directly display RAW values; you have to explicitly select RAWTOHEX(id_column).

On the other hand, SYS_GUID can be defined as the default value for a column - unlike NEXTVAL which is normally set in a trigger or directly as part of an insert into/select from statement. Worse, it is very common to see a separate SELECT seq.NEXTVAL FROM DUAL for every ID generated. I've never investigated the relative performance of SYS_GUID() against getting a sequence number - anyone else like to share that? That may well be the most important consideration of all.

So I think I'll be coming back to this subject in future.

Update - later the same day:

I've quickly timed a million iterations of select sys_guid() from dual, and a million iterations of select sequence.nextval from dual (Oracle XE 10.2.0.1, HP dv1665 Centrino Duo running Windows XP)

    select sys_guid()  into variable from dual: 89 seconds
    select seq.nextval into variable from dual: 40 seconds

For good measure, I've added a variant - one million calls of sys_guid() without a select:

    variable := sys_guid()                    : 95 seconds 

One of the potential advantages of sys_guid() I had anticipated is that it could be called directly from PL/SQL - but it looks like the implementation is less efficient than it might be; tracing shows that the PL/SQL function recursively selects SYS_GUID() from dual.

So I think I'll stick to traditional and more convenient sequence.nextval for now.

Chin chin!

Avoiding application suicide by session pool

A recent post on Oracle-L mentioned:

We had an issue affecting one of our production DBs. The middle tier application for some reason, went crazy spawning processes chewing up the DBs process parameter. DB started throwing errors indicating max process exceeded.

He went on to ask how to wake up PMON to clean up these processes - he'd had to bounce the database to get things cleaned up.

My response was to ask about the root cause, rather than the symptom. The poster may have been solving the wrong problem (or rather, after putting out the fire, he needed to find the cause and stop it happening again).

"Mid tier went crazy spawning processes" is often a symptom of session pool
madness. In such an application, X number of users share a smaller Y number of Oracle sessions. Everything tootles along happily; Users (midtier threads if you like) loop around:

• get a session from the pool


• issue one or two SQL


• commit/rollback


• give the session back

As long as the users spend less time in Oracle than they do in the rest of the
application (and waiting for user input etc), no problem.

Then something goes wrong; maybe a session sits on a lock that everyone needs; maybe a sequence cache isn't big enough (or is ordeed) and/or you forgot that We Don't Use RAC; maybe you had an SGA problem like ORA-4031.

What happens next:

• all the Oracle sessions in the pool are busy


• next midtier thread asks for an Oracle session


• midtier pool manager says "no problem", launches a new Oracle session and adds it to the pool


• that session becomes busy


• and the next thread, and the next thread, and the next thread...

Soon instead of sharing say 100 Oracle sessions across 1000 processing threads,
your mid tier has responded to the blockage by adding 900 new sessions to the
load. That's probably made the problem worse, not better - kind of like slamming your foot on the accelerator when you see brakelights ahead in the fog.

I had exactly this problem performance last year, testing a J2EE app, using OC4J. We hit a 4031 problem (no bind variables in one part of the system) and then fairly immediately the application server did its lemming impersonation as described above.

Things to consider:
1) reduce the upper limit on the session pool size (definitely to below your Oracle processes level!)
2) if possible, slow down the rate of session starts (eg set a delay in the mid-tier session manager)
3) find out what caused the problem in the first case.

The good news is that if you dampen down this suicidal behaviour, you probably have a better chance of diagnosing the root cause next time.

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...

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

How many binds does it take to save time?

Here's a tale about how something that seems intuitively correct can turn out to be 100% wrong - even though it has been stated as gospel by someone who seems like he should know.


We're building an application that - for the first year or two of operation - will include a legacy system component. Originally based on a kind of multiuser ISAM file system, the legacy data now lives in a series of over 100 Oracle schemas (one per site). The legacy app uses a legacy-to-relational bridge to access the data.

The bridge generates single table SQL to perform file-based actions like find-by-key, next record, previous record and so on. The tables are suitably indexed. However... the bridge (designed to support many different database platforms) issues SQL with no bind variables. So although there is a relatively small number of distinct access paths, each time you try one, it's got a new set of literal values in the predicates, and a hard parse is required.


The quick and easy solution is to have every bridge session start with:

ALTER SESSION SET CURSOR_SHARING='SIMILAR'
'Similar' statements share a parse tree, and the number of hard parses drops. In low volume everything works great. But as you crank up the workrate (we're using LoadRunner) the shared pool eventually gives up under the strain. Although there are relatively few 'families' of similar statements (ie statements which are exactly the same once all literals have been replaced by system generated bind variables), each single statement family may contain 100s of versions and use up to 150M of shared pool.


So the shared pool becomes fragmented and sooner or later it iss ORA-04031 time - unable to allocate memory. Even with 800M allocated to the shared pool, and 10% of it still available, the largest free chunk may be too small to be useful. We got down to a biggest chunk size of 23 bytes at one point.


A nasty side effect is that these errors result in everything slowing up. The main web application runs on clustered OC4J, using JDBC with session pooling to connect to a RAC back end. When queries start to slow down, the pool is exhausted so there's a sudden connection storm - which leads the entire system into a spiral of doom.


Now, the system architects aren't complete idiots. They had Oracle consultants on hand to remind them that bind variables are important. They even had access to the code base for the legacy bridge. So an attempt was made to add bind variable support.


It so nearly succeeded - but there was an itsy-bitsy bug in it.


The original bridge developer was asked to help solve the problem. And this is where he dug himself a big hole and jumped right in. A scientist proposes a theory, and conducts experiments that are designed to disprove it. This person simply stated what he believed:

Bind variables are used to keep certain info constant between calls. This
requires keeping and maintaining bind variable arrays. [...] To do it properly one must maintain a lot of stuff in separate arrays and relate them to the tables and types of calls being used. Rather than altering the session and letting Oracle bind them properly.

He (correctly, if sarcastically) describes what is happening:

I looked at your code briefly and your programmer brilliantly solved all the above problems. He creates a bind array, uses it in whatever the DB call is made, and then DESTROYS the array at the end of the call. It is not being used between calls AT ALL! Each DB call (in other words for every single record you read) creates, uses, then destroys the bind arrays.

And now he leaps to the wrong conclusion:

So technically you are binding, actually you have ADDED overhead and destroyed performance. [...] I am surprised you are not getting unexplained core dumps in a heavy locking situation where it accesses multiple files.


He assumed that:

• using binds in the legacy bridge would be expensive


• closing the OCI statement immediately after use would waste any savings


• using CURSOR_SHARING=SIMILAR in Oracle would (a) work and (b) be more efficient

The team lost heart in the face of this clear statement, and the attempt to use bind variables was abandoned. Attempts were made over many months to resolve the 4031 problem by throwing memory at it, or even ignoring it.


But in fact, his advice was 100% wrong. Here's why:

The cost to the legacy bridge of setting up and using bind variables is barely detectable because:

• Bind variables can easily be added into the statement as it is constructed


• the memory management is simple (malloc and free)


• few or no additional network roundtrips for bind data are needed - OCI piggy backs most or all of the information it needs onto the execution request

The Oracle server, on the other hand, is flat out. For it to avoid a hard parse it has to:

• lexically analyse every incoming SQL to identify whether and where literals have been used


• set up bind variables and extract the literals


• now do a soft or hard parse using the transformed SQL


• retain both forms of the SQL at least until the cursor is closed


• manage all the memory structures in a pool shared by hundreds or thousands of sessions

No contest. Although a small overhead is indeed added at the bridge, a major bottleneck is cleared on the RAC server.


Now - nearly a year later - a more experienced team has had another go. Using bind variables solved the 4031 problem pretty much at the first attempt - just as any old Oracle hand would expect. And that bug? It turned out to be a simple one line coding error, which took a couple of hours code review to find and fix.


The moral of the story is: don't believe dogma; actually try and prove or disprove a hypothesis properly.


And the answer to the question in the title: if you are continually issuing 'similar' SQL statements, it is worth using bind variables even if you discard the prepared statement after a single use.