Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ann.

Asked: February 22, 2016 - 6:24 pm UTC

Last updated: March 29, 2016 - 5:37 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

I have a database with a single table that holds historic BLOB data so this table is quite large currently (1.2TB). I range partitioned based on the ID my PK (no date column exists for this table) and am making all but the current partition read-only for manageability. My goal is to make each partition 10G so I looped through and determined the appropriate IDs to use for each partition initially using dbms_redefinition. The ranges are all over the place because the size of the BLOBs vary greatly. My thought was that going forward I would simply split the live partition as the DB grew to create additional 10G read-only partitions as needed just determining the ID needed to make new partitions 10G. The problem I am having with splitting the live partition is the time it takes to split and fact that the table is not available for the users to continue to add new data during the split. I can not have any downtime on this database at all for partition maintenance so I need to find a way to split and let the application continue to function. From my research the answer is to add new partitions to the table ahead of when they are needed instead of splitting the most current partition but for me I don't want to do that since I am trying to make them somewhat consistent in size. Any suggestions would be appreciated.

and Connor said...

THere's an old stock market phrase: "Past behaviour is not a reliable indicator for future behaviour", which might apply here in that all it takes is a few very very large blobs to easily get you to 10g in just a few rows, whereas tomorrow, it might take millions of rows (each with tiny blobs) to get you to 10g. So you are probably going to have some sort of compromise.

That compromise might be relying on the "fast split" optimization where you pick a point where no data will exist on the "right" hand side of the split point.

Here's something to get you going - the logic is this:

- walk along the partitions in order
- if "this" partition in empty, check the previous one for its size
- if its greater than 'x' then
- split it into 2 partitions at a logical point so that one has all the data, one empty one (ptmp)
- split the "maxvalue" highest partition into two (pn and pmax), to establish a new sensible boundary point for the partition
- merge ptmp and pn

So in numbers, lets say

       values less than          current hwm
p1     1000                      1000
p2     2000                      1500
pmax   maxvalue                  -


and we think p2 is now "too large". We do this

Step1 - split p2

       values less than          current hwm
p1     1000                      1000
p2     1600                      1500
ptmp   2000                      -
pmax   maxvalue                  -


So we've now capped p2 at 1600 (ie, left some leeway whilst inserts still carry on)

Step2 - split pmax

       values less than          current hwm
p1     1000                      1000
p2     1600                      1500
ptmp   2000                      -
p3     3000                      -
pmax   maxvalue                  -


We've done this, because 'ptmp' is no good to us because its capped at 2000, where as we want it capped at a sensible logical value

Step3 - merge p3 and ptmp

       values less than          current hwm
p1     1000                      1000
p2     1600                      1500
p3     3000                      -
pmax   maxvalue                  -


And we're done. All of these operations, because they are working on empty partitions are virtually instaneous.

So here's the demo, where 30meg is the size limit for a segment

SQL> create table T ( x int, y char(200))
  2  partition by range (x)
  3  (
  4    partition p1 values less than (300000),
  5    partition p2 values less than (600000),
  6    partition p3 values less than (900000),
  7    partition p4 values less than (1200000),
  8    partition pmax values less than (maxvalue)
  9  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into T
  2  select rownum, rownum
  3  from dual
  4  connect by level <= 1000000;

1000000 rows created.

SQL>
SQL> create index IX on T ( x ) local;

Index created.

SQL>
SQL> alter table T add constraint T_PK primary key ( x ) using index local
  2  /

Table altered.

SQL>
SQL> select bytes
  2  from user_segments
  3  where segment_name = 'T';

     BYTES
----------
  75497472
  75497472
  75497472
  25165824


Now in another session I'm running inserts in batches to simulate online activity

SQL> declare
  2    l_start int;
  3    l_cnt int := 1;
  4  begin
  5    select max(x) into l_start from t;
  6
  7    for i in 1 .. 1000 loop
  8      for j in 1 .. 10000 loop
  9        insert into t values (l_start+l_cnt,i);
 10        l_cnt := l_cnt + 1;
 11      end loop;
 12      commit;
 13      dbms_lock.sleep(4);
 14    end loop;
 15  end;
 16  /


And here's my routine that keep's an eye on things and automatically does the partition maintenance

SQL> set serverout on
SQL> declare
  2    l_has_Rows int;
  3    l_hi_par  varchar2(30);
  4    l_hi_bytes int;
  5    l_hwm int;
  6
  7    procedure ddl(m varchar2) is
  8    begin
  9      dbms_output.put_line(m);
 10      execute immediate m;
 11    end;
 12  begin
 13    for i in ( select p.partition_name, s.bytes, p.partition_position
 14               from   user_segments s,
 15                      user_tab_partitions p
 16               where  p.table_name= 'T'
 17               and    p.table_name = s.segment_name(+)
 18               and    p.partition_name = s.partition_name(+)
 19               order by p.partition_position asc
 20               )
 21    loop
 22        execute immediate 'select count(*) from t partition ( '||i.partition_name||') where rownum = 1' into l_has_rows;
 23        dbms_output.put_line(i.partition_name||':'||i.bytes||':'||l_has_rows);
 24        if l_has_rows > 0 then
 25          --
 26          -- we've hit a partition with rows
 27          --
 28          if i.partition_name = 'PMAX' then
 29            raise_application_error(-20000,'We got rows in PMAX...thats a problem');
 30          end if;
 31
 32          l_hi_par := i.partition_name;
 33          l_hi_bytes := i.bytes;
 34        else
 35          --
 36          -- see if we've hit PMAX, check size of prev one
 37          --
 38          if l_hi_bytes > 30*1024*1024 then
 39            execute immediate 'select max(x) from t partition ( '||l_hi_par||')' into l_hwm;
 40
 41            ddl('alter table T split partition '||l_hi_par||' at ('||(l_hwm+10000)||') into (partition '||l_hi_par||', partition ptmp)');
 42            ddl('alter table T split partition pmax at ('||(l_hwm+10000+300000)||') into (partition p'||i.partition_position||', partition pmax)');
 43            ddl('alter table T merge partitions ptmp,p'||i.partition_position||' into partition p'||i.partition_position);
 44
 45          end if;
 46          exit;
 47        end if;
 48    end loop;
 49  end;
 50  /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:25165824:1
PMAX::0

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
PMAX::0
alter table T split partition P4 at (1020000) into (partition P4, partition ptmp)
alter table T split partition pmax at (1320000) into (partition p5, partition pmax)
alter table T merge partitions ptmp,p5 into partition p5

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:8388608:1
PMAX::0

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:16777216:1
PMAX::0

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:33554432:1
PMAX::0
alter table T split partition P5 at (1140000) into (partition P5, partition ptmp)
alter table T split partition pmax at (1440000) into (partition p6, partition pmax)
alter table T merge partitions ptmp,p6 into partition p6

PL/SQL procedure successfully completed.

SQL> /
P1:75497472:1
P2:75497472:1
P3:75497472:1
P4:33554432:1
P5:33554432:1
P6:8388608:1
PMAX::0

PL/SQL procedure successfully completed.

SQL>


Enjoy !

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about exchange partition from ctas with indexes

A reader, February 23, 2016 - 10:47 am UTC

Will exchange partition with ctas table work in this case. Why we need to split ?

Are users directly inserting data in this monster table ? That may not be good.

Why not keep the transaction table separate from historical table to which data can be added based on transaction table with exchange partition ctas.

Is this a DW env ?
Connor McDonald
February 23, 2016 - 1:28 pm UTC

The question stated:

"The problem I am having with splitting the live partition is the time it takes to split and fact that the table is not available for the users to continue to add new data during the split. I can not have any downtime on this database at all for partition maintenance"

so I'm running under the assumption its more a transactional table.

Exactly What I Needed!!!

Ann Steele, February 23, 2016 - 2:00 pm UTC

Thank you so much this is exactly what I needed to solve my problem.

Ann Steele, February 23, 2016 - 2:08 pm UTC

To clarify this table is transactional in nature and I am making the older partitions read-only for manageability for building standby databases, refreshing test environments, backups, etc. All data needs to be available for instant access at all times once it is loaded in the table.

Blob is bloating the table

A reader, February 23, 2016 - 2:19 pm UTC

Probably the blob is bloating the table and there may not be many rows in the table.

The idea of 1.2 tb transactional data is hard to digest from design perspective.

Yes Connor you did excellent answer with respect to split. Great. But you also need to look beyond the question to give design suggestions. There are more than one ways.

Ann Steele, February 23, 2016 - 3:37 pm UTC

Currently 2.2 million rows. My role is not design but managing the backups and standby site and since the table was previously not partitioned, refreshing test and rebuilding standby was problematic because of the size.

Ann Steele, February 23, 2016 - 8:57 pm UTC

As I rebuild the test environment to run through this, I am looking at the logic and have a question. If I can do a "fast split" by splitting off my new partition with no data to the right of the split point, why can I not just go directly by watching pmax until it gets to a size I want to split on and then doing the split adding say 100 or so to the split point to make sure the process completes before the split partition fills up completely? I am trying to understand the need for the middle partitions that are created and merged together etc.?
Connor McDonald
February 24, 2016 - 1:15 am UTC

[slaps palm across forehead]

You're dead right. I've over-engineered it for no good reason.

Here's a much simpler version

declare
  l_has_Rows int;
  l_last_par int;
  l_hwm int;
  l_bytes int;
  
  procedure ddl(m varchar2) is 
  begin
    dbms_output.put_line(m);
    execute immediate m;
  end;
begin
  select max(partition_position) 
  into l_last_par 
  from   user_tab_partitions
  where  table_name= 'T';
  
  select count(*) 
  into l_has_rows
  from t partition (pmax)
  where rownum = 1;

  if l_has_rows > 0 then
    select bytes
    into   l_bytes
    from   user_segments
    where  segment_name = 'T'
    and    partition_name = 'PMAX';

    if l_bytes > 30*1024*1024 then

      select max(x) 
      into   l_hwm
      from t;   -- this could also just be select pk_seq.nextval from dual, if one exists

      ddl('alter table T split partition pmax at ('||(l_hwm+10000)||') into (partition p'||l_last_par||', partition pmax)');
    end if;
  end if;
end;


Ann Steele, February 24, 2016 - 12:43 pm UTC

Awesome thank you Connor!!!

Ann Steele, March 28, 2016 - 2:32 pm UTC

I am finally implementing this for creating my partitions and it works perfectly when testing. However, I have a few new questions. 1) If they are loading a very large amount of data in a single load I could get in trouble with my tablespace size. My goal is 10G per partition but what if they are loading a large amount of 1GB files for example. I am concerned that I could grow beyond the 32G limit imposed by 8K block size. I am thinking to get around this with bigfile tablespaces for each new partition. Is there any downside to bigfile tablespace or limitation on how many I can have? 2) Second I am trying to figure out the best way to schedule this. Initially I was planning to do a scheduled tasks to run every 5 minutes to check. I was also thinking about an after insert trigger on the table instead of a scheduled task but am concerned with locking issues. What would you recommend?
Chris Saxon
March 29, 2016 - 5:37 am UTC

Nothing wrong with bigfile tablespaces, but one per partition does perhaps seem like overkill. And 32G is not a *tablespace* limit, its a limit on the number of blocks in a *file*...so you can just add files.

And in terms of scheduled task and trigger, they don't have to be mutually exclusive.

Have a task that runs every (say) hour checking for sufficient free space, but perhaps add in resumable statement trigger to catch those rare occasions where a sudden "whammy" chews up all of your space.


Ann Steele, March 29, 2016 - 2:11 pm UTC

My 32G limit comment is because my automatic process adds 1 tablespace and one datafile and in a perfect world that is all I need since I split before it grows to 32G. On that rare run-away situation is what I am worried about. I could either 1) have an issue with the application because the 32G limit was met and then manually add a new datafile to fix it or 2) create the tablespace as bigfile which is overkill but does address that rare case where the runaway space happens. Seems easiest to just use a big file table space and call it a day as long as there is no real downside to that other than just overkill. As for the scheduling, I can just do a scheduled task and not worry about the size if using bigfile so that seems like the easiest thing to do. Thanks!

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.