Oracle DBA – A lifelong learning experience

Large CLOBs and attention to detail needed while reading Oracle Support notes.

Posted by John Hallas on October 3, 2013

This post was forwarded to me by Vitaly Kaminsky who did work with me but has now bettered himself elsewhere. He writes :-

I have recently been involved with performance tuning of a database layer for the major Dutch website which was preparing for the “crazy days” of sales on the 2nd to 5th of October.

The general setup was as follows:
2-node physical RAC cluster with fast storage layer running ORACLE 11gR2 SE and ASM.

The important bit above is the “SE” which stands for Standard Edition and implies no performance packs, no AWR, no SQL tuning and no partitioning.

The shop application makes heavy use of Oracle Text functionality and the purpose of the tuning was to ensure we can get 12000 orders per hour through the system. Each of the orders would create a single insert and numerous updates of a CLOB in XML format. This is actually the total info we managed to get from the vendor on what the application performance tuning should be focused on.

As expected, after the first test runs, it became apparent that the stumbling block was literally a block of CLOB storage.  When there was enough padding space, the application ran fine and then, suddenly, the database would grind to a halt with “enq: HW – contention” waits.

The waiting time per transaction on the application server side started to increase and the application terminated the test run when transactions begun to time out.

Simple statspack report confirmed what was happening:

Event Waits Time (s) Avg Wait Time (ms) % Total Call Time
enq: HW – contention 61,236 118,515 1935 89.6
CPU time   8,432   6.4
db file sequential read 152,792 1,729 11 1.3
PX Nsq: PQ load info query 2,337 468 200 .4
gc current block 2-way 722,660 323 0 .2

So, how many calls resulted in waits?

select ksqstreq, ksqstwat from x$ksqst where ksqsttyp='HW' 
KSQSTREQ   KSQSTWAT
100536      21519

Considerable, 21% - every fifth request was waiting for space to be allocated.

Now we come to the basic Russian questions  of  “Who’s fault it is” and “What to do”.

The answer to the first one will be the default “Application vendor” and I’ve decided to throw the following response to the second one because the obvious solution of hash partitioning was not available:

  1. Move the offending table and CLOB to a separate tablespace. (If you decide to implement this, don’t forget that CLOBS are not moved when you issue “alter table .. move tablespace” – you need to move them separately with “alter table… move LOB… store as”).
  2. Set new storage parameters for the new tablespace to avoid any “small chunk increases” and to set PCTVERSION to 0 to avoid any versioning of CLOBS:
    1. CREATE BIGFILE TABLESPACE “PERFORMANCE” DATAFILE ‘+DATA’ SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 200M SEGMENT SPACE MANAGEMENT AUTO
    2. ALTER TABLE BASKET MOVE LOB ITEMS STORE AS (TABLESPACE PERFORMANCE PCTVERSION 0 DISABLE STORAGE IN ROW CACHE LOGGING)

Now, it’s time to test the new setup:

clob

As you can see, everything went quite well for a while until we hit the same problem, which looks a bit intermittent and occurring, roughly, every four minutes.

After some basic calculation, the 4 minutes interval was proven to be the time taken for the application to insert about 200MB of CLOBS and the datafile then needs additional extents.

OK, we found the cause of the slowdown for inserts, but why Oracle does not reuse some space of updated CLOBS?

Here we come to the interesting bit – Bug 6376915. Oracle support says:

I have to admit that I came across this but earlier, while looking for a solution and the above confirmation was all I needed to know and I did not examine the page any further, missing one little bit at the bottom:

After setting the events with

ALTER SYSTEM SET EVENT=’44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024′ scope=spfile

  1. and restarting the database, the problem has gone away and does not show its ugly head any more.

The morale of the story: the lack of an easy fix like partitioning forces you to go back to basics and to examine the problem in-depth which may lead you to better and more elegant solution and the question is – are we losing some skills  by relying on the newer and newer technologies to solve our problems?

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 185 other followers

%d bloggers like this: