Re: Single record insert intermittently slow

[removed most of the rest of the thread to prevent over-quoting]

I think I might have seen that preso a long time ago, however, I get a spin trying to open the URL below for over 15min. This link works for me:
http://www.slideshare.net/tanelp/oracle-lob-internals-and-performance-tuning and I do remember it - very well done.
Is there anything like for securefiles?

The decision to store our LOBs "disable storage in row" was made long before I arrived here.
I have inquired as to why and have no reasonable response, however am reticent to change it at this point.
Your posit ("not doing updates (and can delete with your "partitioning" strategy) then inline lobs would probably not use a LOB index at all") seems reasonable.

Oracle Support/Dev seem fairly intent on stating that they won't fix this (LOB index) bug.
They are suggesting a move to securefiles (SF) "where this won't happen". From this, I gather they are "abandoning" basicfiles (BF) over the longer term. They now reference other bugs (6060688, 6977863, 10420980 all closed as "Not a bug") and Doc 978045.1 (we have repro'd the issue with recyclebin turned off and stated as such in the SR a long time ago)

Thus, we have decided to try to move to SF LOB as I feel that is in our best interest.

We have seen:
http://www.vldb.org/pvldb/1/1454170.pdf
and
http://kevinclosson.files.wordpress.com/2011/09/dbfs-benchmark-367122.pdf however, small (2KB) LOBs like we insert fairly heavily are not represented in these.

We note that as the sizes of the LOBs inserted into SF LOBs increase, they get better throughput.
We also note that tests become CPU bound in single node configuration as the size of the LOB is decreased (capped at about 64 with 10KB LOBs in the above tests).

We are currently testing with 30 concurrent sessions all doing ~2KB inserts into SF LOBs - that test should be complete Monday.

We are concerned about the scalability of SF with small LOBs like this and the potential cost of additional CPU required vs BF.

It appears to me that SF is built for larger LOBs (1MB) - ref DBFS.

Does anyone have experience with fairly high concurrent small insert using Securefile?

Thanks,
Rich

On Thu, Apr 12, 2012 at 6:08 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:
> 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

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