Re: Tables Larger When using DBMS_REDEFINITION

From: Michael Cunningham <napacunningham_at_gmail.com>
Date: Fri, 20 Feb 2015 11:44:15 -0800
Message-ID: <CAPt39tsEzrDzc09UrT_Rgp+cG4gC+Qf_6ZbbobN+g3807U+=YA_at_mail.gmail.com>

I logged an SR with Oracle. FYI, the code to reproduce is below.

• Create the test objects #################################################### drop sequence redef_table_size1_seq;

drop sequence redef_table_size2_seq;

drop table redef_table_size1 purge;

drop table redef_table_size2 purge;

create table redef_table_size1
(

  id_1  number(15),
  id_2  number(15),
  id_3  number(15)

);

create unique index redef_table_size1_uix on redef_table_size1( id_1 );

create sequence redef_table_size1_seq start with 5000000000;

create table redef_table_size2
(

  id_1  number(15),
  id_2  number(15),
  id_3  number(15)

);

create unique index redef_table_size2_uix on redef_table_size2( id_1 );

create sequence redef_table_size2_seq start with 5000000000;

• Populate the test tables ################################################### insert into redef_table_size1 select redef_table_size1_seq.nextval, trunc( dbms_random.value( 20, 80 ) ), trunc( dbms_random.value( 20, 80 ) ) from all_objects where rownum <= 20000;

insert into redef_table_size2
select redef_table_size2_seq.nextval,
trunc( dbms_random.value( 20, 80 ) ),
trunc( dbms_random.value( 20, 80 ) )
from all_objects
where rownum <= 20000;

commit;

exec dbms_stats.gather_table_stats( user, 'redef_table_size1', cascade => true );

exec dbms_stats.gather_table_stats( user, 'redef_table_size2', cascade => true );

• View storage info about the test objects ################################### select table_name, num_rows, avg_row_len from user_tables where table_name like 'REDEF_TABLE_SIZE%';

select * from user_segments where segment_name like 'REDEF_TABLE_SIZE%';

select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) from redef_table_size1
group by dbms_rowid.rowid_block_number( rowid );

select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) from redef_table_size2
group by dbms_rowid.rowid_block_number( rowid );

• REDEF on of the test tables ################################################ exec dbms_redefinition.redef_table( user, 'REDEF_TABLE_SIZE2', table_part_tablespace => 'P6TBS', index_tablespace => 'P6TBS', lob_tablespace => 'COMMONTBS' );
• View storage info about the test objects ###################################
• This is where the changes can be seen ################################### select table_name, num_rows, avg_row_len from user_tables where table_name like 'REDEF_TABLE_SIZE%';

select * from user_segments where segment_name like 'REDEF_TABLE_SIZE%';

select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) from redef_table_size1
group by dbms_rowid.rowid_block_number( rowid );

select dbms_rowid.rowid_block_number( rowid ) as block#, count(*) from redef_table_size2
group by dbms_rowid.rowid_block_number( rowid );

On Fri, Feb 20, 2015 at 10:08 AM, Brent Day <coloradodba_at_gmail.com> wrote:

> Yes I saw this with regular tables and using advanced compression. I
> didn't log an SR but talked to some of the internal Oracle techs I have
> access to and never got any real answers.
>
> In fact for our compression efforts we do a CTAS to get full use of
> compression. One example I can give was a small table that used 101k blocks
> and using DBMS_REDEFINITION with ADVANCED COMPRESSION for ALL OPERATIONS
> the table had 127k blocks.
>
> If you decide to log an SR let me know and I will log one as well.
>
> Brent
>
> On Fri, Feb 20, 2015 at 10:22 AM, Michael Cunningham <
> napacunningham_at_gmail.com> wrote:
>
>> I'm going to log a bug with Oracle on this, but has anyone experienced
>> this?
>>
>> When I use DBMS_REDEFINTION the finished size of a table is larger than
>> it was prior to redef. It looks like the problem lies in the AVG_ROW_LEN.
>> For some reason the redef code is inserting the rows into the table and the
>> AVG_ROW_LEN is larger resulting in less rows per block.
>>
>> In some cases the table is as much as 55% larger. BTW, I only see this on
>> some tables. When this does happen on a table it is consistent no matter
>> how many times I run the redef on that table.
>>
>> Also, I tested on an offending table with a simple CTAS and the size of
>> the new table is what I expect.
>>
>> --
>> Michael Cunningham
>>
>
>

--

Michael Cunningham

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

Received on Fri Feb 20 2015 - 20:44:15 CET