Re: Tuning for CLOB usage

In article <1187748977.080992.22700_at_j4g2000prf.googlegroups.com>, Noons says...

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

I already set that - didn't seem to make any difference? [1]

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

Thanks been reading up on exactly this.

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

OK

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

Pretty sure there was no char set conversion going on.
>
> 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...

OK

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

Same principle as suggested by Shakespeare up there ^^^^

We dramatically improved performance by chaning the coding to use CLOBs only when total size of generated output exceeds 32k

[1] It's ok I know it's a joke. It is, right?

-- 
jeremy