Re: Tuning for CLOB usage

On Aug 21, 5:43 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
> 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?
>

Unfortunately, apart from
_go_fast=TRUE,
I can't think of any...
:-)

CLOBs and BLOBs are deadly on performance in anything other than 11g and even there the jury is out. So if you're relying on them alone to improve performance, I don't know...

A few things to consider:

1- separate the CLOB into its own tablespace if most are > 4K. Don't let Oracle migrate them, do it at definition time! If most are much < than 4k, then consider using a smaller db block size to match.

2- Optimize access to the devices that hold the LOB tablespace.

3- Make sure there isn't any character set conversion, implicit or explicit, happenning behind the scenes. If your db is UTF16 and your client-side application is using UTF8, you got the start of some hidden processing right there. To be sure, store as BLOB and do all other processing on the application side. CLOBs are subject to all implicit character set conversions, unlike BLOBs.

4- do not even THINK of updating the LOB: just delete and create a new one, and do it sparingly if you want performance. And do the delete by flagging the row -not the LOB - as "gone" and then a batch process to do the actual delete at some suitable off-time. Update management with LOBs is pretty poor at the moment...

5- Implement some cache at app level whereby you keep the "skeleton" LOB for the page in memory and extract from there, not the db, for most pages.

HTH Received on Tue Aug 21 2007 - 21:16:17 CDT