Re: Tables Larger When using DBMS_REDEFINITION

From: Kenny Payton <k3nnyp_at_gmail.com>
Date: Fri, 20 Feb 2015 14:56:27 -0500
Message-Id: <7D872432-91D3-450F-A2A5-08381E11D618_at_gmail.com>



Source and target tablespaces the same?

> On Feb 20, 2015, at 2:46 PM, Michael Cunningham <napacunningham_at_gmail.com> wrote:
>
> Forgot to mention versions
>
> Linux Redhat 6.5
> Oracle 12.1.0.1 Enterprise
>
> On Fri, Feb 20, 2015 at 11:44 AM, Michael Cunningham <napacunningham_at_gmail.com <mailto:napacunningham_at_gmail.com>> wrote:
> 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 <mailto: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 <mailto: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
>
>
>
> --
> Michael Cunningham

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 20 2015 - 20:56:27 CET

Original text of this message