We have a general purpose "export queue" table containing a CLOB column along with a dozen or so other columns of varying types. Rows having a 'PENDING' status are continually inserted by various triggers but the CLOB column is always initially NULL. Within a few minutes, various batch jobs will look for pertinent 'PENDING' rows, update the CLOB with generated data - typically XML a little more than 32K in size - and mark the rows as 'NEW' for other processes to consume and mark as 'COMPLETE'.
This all works fine until we decide to "archive" the old, completed rows to a history table having an identical structure. The archive process is PL/SQL package which does bulk <tt>INSERT /*+ APPEND */ INTO … SELECT … FROM …</tt> statements into another similarly structured table, followed by bulk deletes. (Yes, I know partitioning/ILM may be a better way to keep the old data away from the active data but we didn't pay for that when this was originally cooked up.) Sadly, this archival is V_E_R_Y___S_L_O_W - less than 2 rows per second on average - and it appears to be the SELECT … FROM … part that's slowing everything down.
I actually got a chance to speak personally to Tom Kyte once and asked him about this specific issue. His primary suggestion was that we design our process differently, and I couldn't argue since better designs certainly exist, but we unfortunately aren't really able to do that.
I haven't provided much technical detail and my options for getting a trace file are inconvenient at best, but here are the top 5 worst offending wait events, based on their percentage of the overall cumulative time spent waiting.
db file sequential read - 90.36%
db file scattered read - 6.59%
Data file init write - 0.89%
log buffer space - 0.81%
log file switch completion - 0.40%
There are no readers of these CLOBs except for one application which reads each value ONCE when they're 'NEW', and an the occasional human who may view the CLOB value during research. In general, they're written, read once, and then ignored forever, but must be retained for some time just in case.
Here's our current LOB segment. There was likely very little thought given to these parameters when the table was first created.
LOB (DATA) STORE AS (
TABLESPACE OMEXPQ
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
CACHE
LOGGING
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
I mentioned above that the CLOBs average a bit over 32K in size. Increasing the <tt>CHUNK</tt> size to 16K 32K would potentially waste some space but would lessen the number of chunks. Would think we'd require an application downtime event to do such a reorganization since <tt>CHUNK</tt> can't be altered, correct?
Since up to 12 LOB pointers supposedly can fit in the row, I don't think were having to jump around the LOB index very much, if ever.
[http://blog.tanelpoder.com/2012/04/22/where-is-lob-data-store]
We have the LOB segment defined as <tt>CACHE</tt>. According to Oracle's LOB Performance Guidelines white paper, "With the <tt>CACHE</tt> option, LOB data reads show up as wait event 'db file sequential read', …" The <tt>CACHE|NOCACHE</tt> option can be altered, but would this have a noticeable effect, or just change my wait events to something else? There may be benefit to other processes in not wasting buffer cache to hold these CLOBs for however long they'd hang around since they'd rarely need to be read ever again.
[http://www.oracle.com/technetwork/articles/lob-performance-guidelines-128437.pdf]
We have a QA DB which was loaded with production-like volumes into the same table but with a few differences noted below and performance was MUCH better.
Could either of these had such a dramatic effect, or was this more likely a function of nicely organized/compacted data from the load?
- The LOB segment was set to <tt>NOCACHE</tt>
- <tt>FREELIST</tt> and <tt>FREELIST GROUPS</tt> were set (or defaulted) to 1
Any words of advice will be greatly appreciated!
-Rick
Edited by: tarpaw on Jun 4, 2012 2:15 PM