Re: Tuning for CLOB usage

"Jeremy" <jeremy0505_at_gmail.com> wrote in message 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
> ============================================================

Start by tracing a few sessions, or running statspack snapshots at level 7 for a while, to see where the time is going.

If most of the time spent is used for I/O relating to LOBs then you can start thinking about the best way to handle LOBs for your requirement. Ditto if the segment statistics show most of your I/O to be on the LOB segments.

General thoughts -

    should your lobs be allowed "in row", or should     you always disable "in row"

    should your blobs be using cache or not

    is there an optimum block size for your lob handling

    are you seeing side effects of multi-byte character sets.

"Disable storage in row" is often a good idea, and CACHE can make a dramatic difference . But if you CACHE, then you probably need to have a separate cache for the LOBs - either by using the KEEP or RECYCLE pools, but possibly by using a different block size for the LOB segment so that most or the most popular LOBs fit in one block. If the LOBs don't go through the cache, and are logged, then Oracle uses direct path reads and writes on them - which become directly visible as wait states to the end-user - and logs whole blocks to the redo log.

For similar reasons to the previous, if you use temporary LOBs (dbms_lob.create_temporary) then remember to think about the "cache => true" option.

Remember that if the CLOB uses a multibyte character set it will be translated internally to a fixed two-byte character set - so the length() or dbms_lob.getlength() calls, that report the character count, will be out by a factor of two when it comes to counting bytes.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html