Re: Single record insert intermittently slow
Date: Fri, 13 Apr 2012 04:08:43 +0300
Message-ID: <CAMHX9JJtC-38rH+bP8UMgyWfdjMOXwALe8s_NxbQsdvxR4eBRg_at_mail.gmail.com>
Check this, a very old LOB internals presentation, from 9i times, so some details may have chanced in newer versions, but the data structure fundamentals should be the same:
http://www.slideshare.net/mobile/tanelp/oracle-lob-internals-and-performance-tuning
Check especially the slide 12 which explains the physical storage differences of the 3 different LOB item scenarios (enable in row - inline, enable in row out-of-line and disable in row).
As you are not doing updates (and can delete with your "partitioning" strategy) then inline lobs would probably not use a LOB index at all (I remembered wrong by the way, it's 12 LOB chunk pointers which can be kept in the row itself - allowing you to insert up to 96kB LOB items without inserting anything into LOB index.
So, if LOB index is not inserted into, then you won't have these hiccups due to the (free space) scans through LOB index either. A side-effect is that your main table gets larger due to some smaller lobs stored in-line and the larger LOB items storing the chunk pointers right inline in the row (as opposed to the LOB index).
This still would require testing like any other change, but probably less as you'd just be changing the LOB storage attribute, not the whole storage datatype to SecureFiles. If you can consistently replicate this problem in test, then try the change the storage attribute there and see if the issue goes away.
Tanel.
P.S. There's one more question though - how come this bug is not fixable? We don't usually see 30 second hangs when inserting into regular indexes, unless there's a bug (which then gets fixed).
On Fri, Apr 13, 2012 at 1:54 AM, Rich <richa03_at_gmail.com> wrote:
> Hi Tanel,
> Thanks for the reply.
>
> This LOB is stored out-of-line in a separate tablespace (chunk 2KB).
>
> Correct, only inserts (2-5M per day) and very few selects - no updates.
> We were deleting from this table during maintenance hour and noticed
> the degradation sooner if we do that, so we stopped deletes quite a
> while ago.
> We use a poor-mans partitioning on this area (put simply) renaming the
> table and creating a new one (in new tablespaces) during downtime.
>
> Rich
>
> On Thu, Apr 12, 2012 at 2:03 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:
> > 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-lReceived on Thu Apr 12 2012 - 20:08:43 CDT