Re: Lob Performance

  • From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
  • To: kennethnaim@xxxxxxxxx
  • Date: Fri, 27 Aug 2010 06:45:15 +0200

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@xxxxxxxx

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

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

=========================


On Mon, Aug 23, 2010 at 12:01 AM, Kenneth Naim <kennethnaim@xxxxxxxxx>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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>

Other related posts: