Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning for CLOB usage

Re: Tuning for CLOB usage

From: Jeremy <>
Date: Thu, 23 Aug 2007 18:24:04 +0100
Message-ID: <>

In article <46ca9e01$0$233$>, Shakespeare says...

> There is not enough detailed infromation in your post, but from what I read,
> I'd suggest to make your code more efficient. Your 'to do list' of
> possibilities is, to my opinion, in right order to start with.
> One point in your list is missing, and I always give this advice for
> customers starting projects like this: did someone else build functionality
> like this before? Is it on the market? Is standard software available to do
> the same thing? I know it's more fun to build it yourself (tell me...!!!).

Thanks. We looked around for examples of CLOB replacement to identify what we thought was the most efficient method.
> It looks like pages are built dynamically, but how often do they change?

On every call potentially.
> I
> would implement some kind of cache for already built pages (like eg
> OraclePortal does), or even, for more stable pages, generate them only once
> and keep them in a table or set of tables.

We considered this as a possibility - discovered though that there is always *some* dynamic content which would need to be "replaced" (placeholders -> data from db). Would definitely improve things though.

> Are your templates in XML? IF not, this MIGHT help.... or BFILES maybe...
> A diiferent approach could be splitting up pages in stable and variable
> parts (but this would take redesign and I don't know if your templates are
> built in a way to accomplish this, may be the program data is all over the
> page..)
> You could also take a look at the 'search and replace' procedures/functions:
> how efficient are they? Are your templates that big you (always) need a
> CLOB? You write about a new approach, what is the old approach? What
> changed here?

The new approach really was quite different to the old approach (without going into too much (well any really!)). We took another look and my colleague ientified that 99% of the pages generated actually are < 32k in size and has implemented functionality to use varchar2 wherever the total size will not exceeed 32k.

Performance has gone from turgid to lightening fast.

> Not an answer to your "question today" I guess. Just some hints. For tuning:
> In "the old days" (since your still on 9iR2 ;-)) sizing your temp tablespace
> segment size would be something to look at, but hey, I'm NOT a DBA, so
> others could be of better help on this matter.

Thanks very much for all of yuour comments, really appeciate it.

Received on Thu Aug 23 2007 - 12:24:04 CDT

Original text of this message