Monday 29 March 2010

Quirky Oracle LOBS

Recently a client of mine took to storing volitile session related data in the database in the form of a BLOB. Whilst I argued the virtues of storing session related information at the app tier and the reduced scalability of such a design, for the short term it looked like the only solution.

Naturally I was inclined to test and tune a variety of configurations for the LOB such as chunk size, storage in and out of row (see the "SecureFiles and Large Objects Developer's Guide" in www.oracle.com/pls/db111/portal.all_books).

As I explained to the developers, when dealing with LOBS, you can chuck it and suffer the consequences (as they were seeing in their own tests) or do it properly. I was fairly confident that I would be able to improve upon the performance that comes by default.

Initially the lob was 8k, so I wouldn't have the luxury of choosing between in-row and out-of-row design (anything over 4k forces you to the latter). My choice was then reduced to lobs that were cached, but generated logging (with Oracle LOBS you can't have one without the other for out-of-row LOBS) or non-cached without logging. Load testing revealed the load profile in the database and here's a snipped from AWR (1 hr snapshot with LOB configured of non-caching):

Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s)
-------------- ----------- ------------- ------ -------- ---------
289,226 70,151 4.1 53.7
update mybooking set data = :1 where id = :2

data is the the LOB (a BLOB in my case). This revealed that the app updates the LOB 70k times per hour.

The average size was 22k, so I set chunksize to 24K (rounded up to multiple of db block size = 8k). You can work out the average size with:

AVG(DBMS_LOB.GETLENGTH(DATA))

I then tried the caching/logging option and found that physical disk reads reduced by 70-80% but of course redo generation rose dramatically to 500 MB/hr. Therein lies the dilemma. Admittedly the application response times were similar with both configuration, but at the time of the tests the application was being throttled with a 100ms delay.

I didn't have to scratch my head for long however (so I still have hair!)... The application was changed such that the average LOB size reduced to 2k. I jumped at the chance of storing the LOB in-row, knowing that I could create the table with the NOLOGGING option. I would benefit from caching and nologging hooray! So what happened...?

To my dissappointment, Oracle kept logging away, much like before. It turns out that there is a bug in Oracle (Bug 4205241) that effects 9.2.0.4 all the way up to 11g (groan).

No comments:

Post a Comment