Re: Single record insert intermittently slow
- From: Rich <richa03@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 10 Apr 2012 09:17:25 -0700
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@xxxxxxxxx> 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@xxxxxxxxxx> 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
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
