Re: Lob Performance

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Fri, 27 Aug 2010 06:45:15 +0200
Message-ID: <AANLkTinqRopkOZHgEefAk1QZFpsxCu559_aehhn1u5SY_at_mail.gmail.com>

There probably isn't a "comprehensive" guide, because it largely matters what you're doing with your lobs, and what kind / size of data you're storing in them.

I would probably start by asking the following questions:

• How big are the individual lobs ?

Point being, if they are large, as in multiple MBs or bigger, you may find that using a large blocksize and store the lobs in a separate tablespace gives you a major performance gain. Benchmark it for your environment first, though. You can measure the size using dbms_lob.getlength()

• Do they fit in a database block ?

If they do, it may be worth using, or it may destroy your performance. The key being the lob storage option ENABLE / DISABLE STORAGE IN ROW. If you store them in-line, you can save IO if you're reading them. But it blows up your table size, which may kill you if you do a lot of full scans on it.

• Securefiles available ?

You didn't mention your Oracle version, but if its 11gR1+, and if it's allowed in your environment, consider using securefiles. In my experience, they generally offer a performance vs space usage trade-off. They use more storage than traditional lobs, but they're faster.

• Compression option available ?

Depending on what kind of data you're storing, compression may improve your performance. Particularly on systems where the CPU load is low, and the IO load is high. You'll be doing smaller amounts of IO, and if you can spare the CPU needed to decompress them, you'll be going faster too. Also, this goes hand in hand with the deduplication. Both require the advanced compression option though, $$$

• Write intensive lobs ?

If your lobs are mostly read-only, consider enabling CACHE READS. It'll keep your cache warmer.

Just a few pointers off the top of my hat. As always, you're probably off best to create your own little benchmark. Load a bunch of files that represent your actual data into a table, and start measuring what works best for you.

HTH Stefan

---

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

On Mon, Aug 23, 2010 at 12:01 AM, Kenneth Naim <kennethnaim_at_gmail.com>wrote:

> I haven't dealt with lob performance much. Can someone point to a good site
> or whitepaper how to measure lob size, test and improve (b)lob performance.
> I'm checking google and mos and haven't found a comprehensive article.
>
> Thanks,
> Ken
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 26 2010 - 23:45:15 CDT