Re: Reducing CPU Impact of DML's on LOB Segments.

The first of the three is, in my opinion, about the best opportunity for a big win you have. My experience with not caching the LOBs results in heavy load from direct path reads and writes (as those LOBs never hit the buffer cache), and depending on how transient the messages are in your system, that read/write activity can dominate your system.

Also, if the messages tend to be small (under 4000 bytes), give Oracle the option of storing the LOB inline, as you'd then eliminate some LIOs to update the LOB index and LOB segment as well. I wonder how much of the CPU you see is related to those activities.

Is AQ part of your JMS solution? If not and if you have control over the application and don't have multi-platform issues, it may be worth considering as well, as AQ is pretty-well optimized for persistent messaging applications and supports point-to-point as well as publish/subscribe messaging.
Adam Musch
ahmusch_at_spammenot.gmail.com
On Thu, May 21, 2009 at 9:51 PM, Fmhabash <fmhabash_at_gmail.com> wrote:

> We have a case where an application using a JMS (Java messeging)
> involving a high frequent DML operational set of LOB insert, update, and
> delete statements. When this set runs against the db, we see an increase
> in system CPU which, in the past, had some undesirable effects on
> overall application performance.
>
> We are looking into ways we can optimize the DML work run agaist the db
> and specifically on these LOB segments. I have done some research today
> using ML, developer guides and db admin guide and came up with some
> potential ideas.
>
> 1) Enable caching for these lob segments and to avoid buffer
> interferance with application data, we can dedicate a separate block
> size for this data in the sga.
> 2) disable 'logging' on the lob segments, but this will affect our
> standby configuration.
> 3) Not sure if changing the chuncksize (is 8k now) will have any effect on
> performance and reducing IO load.
>
> If you have experienced a lob related performance issues, please comment on
> the above and share.
>
> ---------------
> Thank You.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l