Re: Tuning for CLOB usage

In article <46ca9e01$0$233$e4fe514c_at_news.xs4all.nl>, 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.

-- 
jeremy