Re: Tuning for CLOB usage

"Jeremy" <jeremy0505_at_gmail.com> schreef in bericht news:MPG.2134a7cf411c64e798a493_at_news.individual.net...
>
> Oracle 9iR2
>
> We have recently implemented a new way of generating web pages from our
> app (mod_plsql) which involves storing the basic page template in a CLOB
> and then replacing "place holders" with data from the application's
> tables.
>
> We have noticed that, since using this new approach, the execution time
> has increased significantly. When there is some concurrency, as each
> call is taking longer to execute, we are finding that the server is
> running low on resources occasionally maxing out on sessions.
>
> As I see it there are three possibilities:
> 1) make the code more efficient
> 2) tune Oracle
> 3) allocate more hardware
>
> My question today is: are there some parameters in Oracle that should be
> examined that might have a significant impact on its ability to
> effectively handle a greater number of CLOB-related activities?
>
> Thanks for your time.
>
>
> --
> jeremy
>
> ============================================================
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ============================================================

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...!!!).

It looks like pages are built dynamically, but how often do they change? 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. 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?

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.

Shakespeare Received on Tue Aug 21 2007 - 03:10:25 CDT