Home > LOB > High-Performance Batch LOB Insert. Part 2. Subpartitions

High-Performance Batch LOB Insert. Part 2. Subpartitions

In my recent post High-Performance Batch LOB Insert. Part 1 I described attempts to transfer high number of records from one table with LOB column into another table. To my surprise my preferred LOB storage option of “disable storage in row nocache nologging” resulted in insert being too long to be practical. I arrived at following results when doing 100,000 record insert test:

-- Test Summary for 100,000 records:

                       Option 1              Option 2        Option 3
 Sizes                 disable               disable          enable
                   storage in row         storage in row   storage in row
                   nocache nologging          cache       (cache) nologging

Elapsed Time,min    >45(interrupted)         10:59            2:28
Table, MB            N/A                      75              315
LOB Segment,MB       N/A                      313              39
LOB Index,MB         N/A                      7                 1
Redo,MB              N/A                      337             0.1

Extrapolating timings we can arrive at following runtime estimates for 12,000,000 records we were expecting to transfer: 22 hrs for Option 2 and 5 hrs for Option 3. These estimates seemed to leave only Option 3 “enable storage in row” as being able to fit in one-night maintenance window.

There was one deficiency with both options 2 and 3 in above tests though – serialization.

With 24 CPUs on the system it was a shame to see only 2 or 3 processes working during insert phase.

Since I really wanted to move LOB out of line to have smaller table segment, I kept searching on the Web, Oracle soc, Support sites. Then I ran across somebody in oracle forums mentioning subpartitons as helping LOB inserts. I did not note where exactly did I find this mentioned, but will edit this post once I find it again.

I decided to try to improve Option 2 by recreating the table with subpartitions and increasing max_parallel_servers to 96.

nologging parallel
lob(PORTAL_IMAGE) store as PORTALX_IMAGE_LOB (tablespace EWPTSDB_LOB_2K chunk 2K disable storage in row cache retention )
partition by range (CREATEDDATE)
subpartition by hash(ORDERNUM)
subpartition template    -- 8-way subpartitions
         ( subpartition a
          ,subpartition b
          ,subpartition c
          ,subpartition d
          ,subpartition e
          ,subpartition f
          ,subpartition g
          ,subpartition h

alter system set parallel_max_servers=96 scope=memory;
alter session enable parallel dml;
insert ...

Below is the Grig Control screenshot.

First time I ran the test with 4-way subpartitions and then again with 8-way.

As we can see, after the table was recreated with subpartitions the insert process broke some serialization bottleneck. With 8-way subpartitioning there were almost 20 parallel processes doing the work (either on CPU or doing I/O).

In the end the 12 million records were loaded into out of line LOB table with good degree of parallelism in just 1 hr.

That was quite an improvement from initially estimated 22 hrs for Option 2 !

The 82GB LOB table has been reorganized into 43GB

12841629 rows created.
Elapsed: 01:01:26.63

After the load sizes were:

col size_gb for 99.99
col segment_type for a30

select segment_name, segment_type, sum(bytes)/1024/1024/1024 as size_gb,
  count(1) as seg_count
from user_segments
(     ( segment_name in ( 'PORTALX_IMAGE' ) )
   or ( segment_name in ( select segment_name from user_lobs
                          where TABLE_NAME = 'PORTALX_IMAGE') )
   or ( segment_name in ( select index_name from user_lobs
                          where table_name = 'PORTALX_IMAGE' ) )
   or ( segment_name in ( select index_name from user_indexes
                          where table_name = 'PORTALX_IMAGE' ) )
group by segment_name, segment_type

SEGMENT_NAME                   SEGMENT_TYPE                   SIZE_GB  SEG_COUNT
------------------------------ ------------------------------ ------- ----------
PORTALX_IMAGE_LOB              LOB SUBPARTITION                 39.75        128
IND_PORTALX_IMG_ORDREG         INDEX                              .40          1
SYS_IL0000955202C00004$$       INDEX SUBPARTITION                 .76        128
IND_FUN_PORTALX_IMG_ORDSEG     INDEX                              .47          1
PORTALX_IMAGE                  TABLE SUBPARTITION                1.29        128
IND_PORTALX_IMG_ORDSEGREG      INDEX                              .51          1


Disclaimer: all tests were performed on Oracle running under Solaris 2.10.

Out of line LOB was using 2k tablespace with manual segment management.



  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: