Re: Single record insert intermittently slow

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 13 Apr 2012 00:03:18 +0300
Message-ID: <CAMHX9JJ5Oi3NKXY_Wq4Z8HjB_iaLcWGZKYAb6DKfjk6bc0=G7g_at_mail.gmail.com>

Is your LOB column defined as ENABLE STORAGE IN ROW or DISABLE? Do you only have inserts and selects on the LOB data or do you modify existing LOB items too?

When using ENABLE STORAGE IN ROW, then your 2kB LOBs would be stored in-line and not go to the LOB segment at all also for the ENABLE STOARGE IN ROW any LOBs up to the size of 6 x LOB chunk size (48 kB with 8kB chunk size) would be physically stored in the LOB segment, but all the LOB chunk pointers would be still kept in the original row with the LOB locator, thus no LOB index entries are created.

However, once you start doing DML on these LOB items, LOB index entries are created (as they're needed to point to the old chunk versions for read consistency and rollback needs).

-- 
*Tanel Poder*
Enkitec Europe
http://www.enkitec.com/
Advanced Oracle Troubleshooting v2.0 Seminars in May/June 2012!
http://blog.tanelpoder.com/seminar/



On Tue, Apr 10, 2012 at 7:17 PM, Rich <richa03_at_gmail.com> wrote:



> Update on this - Oracle Support and Dev is SO SLOW and this was a

> "Priority Handled Service Request"...

>

> 11.2.0.2 on RHEL 5.6 x86-64 with ASM

>

> Oracle states "The root cause of the problem is LOB index fragmentation".

> They term this as unfixable bug 13341274: SLOW LOB PERFORMANCE DURING

> INSERTS.

> There is no fix other than recreating the segment(s) - CTAS or

> expdp/impdp (unacceptable).

> This bug is "hittable" with any mix of DML (including 0) - just doing

> inserts (no update nor delete) might hit this bug.

> They suggest moving to Securefiles.

>

> I've asked for a previous bug reference or a note regarding this

> issue, however, I'm still awaiting a response.

>

> Anyone have any experience(s) with Securefiles?

> High concurrency (~100/sec) small (~2KB) LOB insert with Securefiles?

> How about lower concurrency (~10/sec) larger (~32KB) LOB insert with

> Securefiles?

>

> TIA,

> Rich

>

> On Thu, Oct 27, 2011 at 1:42 PM, Rich <richa03_at_gmail.com> wrote:

> > Thanks for the reply Grzegorz.

> >

> > I don't want to do anything which might potentially disrupt the issue

> > we have right now.

> > This issue is only on this instance and we cannot repro at will, yet.

> > So, process level tracing is out of the question currently as that has

> > potential to crash a process.

> > Also, this is a production instance (our staging area, so not very

> > heavy load in any respect), so I have to be somewhat careful.

> >

> > We can't switch to SecureFiles without a LOT of testing and I don't

> > think that's the direction we would/will take anyway.

> >

> > For me to be able to post AWR, I'd have to sanitize it which (at this

> > point) is too much trouble.

> >

> > As previously posted, no enqueue issues that I've seen throughout this

> issue.

> >

> > Thanks again,

> > Rich

> >

> > On Thu, Oct 27, 2011 at 10:36 AM, Grzegorz Goryszewski

> > <grzegorzof_at_interia.pl> wrote:

> >> Hi,

> >>  is there any chance You can use and share with us output from Tanel's

> >>

> ------------------------------------------------------------------------------------

> >> --

> >> -- File name:   oStackProf.sql ( Oradebug short_Stack Profiler )

> >> -- Purpose:     Take target process stack samples and show an execution

> >> profile

> >> --

> >> -- Author:      Tanel Poder

> >> -- Copyright:   (c) http://www.tanelpoder.com

> >> --

> >>

> >> from http://files.e2sn.com/scripts/tpt_public_unixmac.tar.gz

> >>

> >> during slowness, we can see whats going on on calls level maybe that

> >> will help .

> >> Btw is there any chance You can switch to securefiles LOBS ?

> >> And can You show awr report from that period, maybe there is some HWM

> >> enqueue issue ?

> >> Disclaimer:

> >> Dont blame me for any production issues related to my advice :)

> >> Regards

> >> GregG

> >>

> >>

> >> ----------------------------------------------------------------

> >> Konkurs: Wygraj nowoczesna suszarke do wlosow!

> >> Sprawdz >>>  http://linkint.pl/f2a72

> >> --

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

> >>

> >>

> >>

> --

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

>

>

>



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

Received on Thu Apr 12 2012 - 16:03:18 CDT