Re: Single record insert intermittently slow

From: Rich <richa03_at_gmail.com>
Date: Tue, 10 Apr 2012 09:17:25 -0700
Message-ID: <CALgGkeCQNnKj5dSudQxhFCy4Hi3o3d+udioFqYon0v2Os-QwMg_at_mail.gmail.com>

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

Received on Tue Apr 10 2012 - 11:17:25 CDT