Re: Tables Larger When using DBMS_REDEFINITION

From: Michael Cunningham <napacunningham_at_gmail.com>
Date: Fri, 20 Feb 2015 12:54:04 -0800
Message-ID: <CAPt39tvsKu4OGLmN7J6G5G6En5yoywitHVc8SiRuehv91GMBTw_at_mail.gmail.com>

In this case yes, but it will happen regardless if the source and target tablespaces are different.

On Fri, Feb 20, 2015 at 11:56 AM, Kenny Payton <k3nnyp_at_gmail.com> wrote:

> 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> 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>
>> 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
>>
>
>
>
> --
> Michael Cunningham
>
>
>

-- 
Michael Cunningham

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

Received on Fri Feb 20 2015 - 21:54:04 CET