Re: Preparing a table to reorg

This discussion inspired me to see how the data s physically stored in the datafile. We were tought, around verion 7.3, that a data block corresponds to a defined and formatted area in the datafile, and that rows are written into that area starting from the bottom (greatest offset) up (to lower offsets). The concepts guide still seems to reinforce that idea. Also according to my understanding, when fields are updated they are updated in situ, that is the new value replaces the old in the datafile. Indeed this underlies the need for rollback (undo) segments.

I remember that a year or 2 ago, during a hands-on workshop on column-based encryption, we were using hexdump to inspect a datafile to see that data was encrypted and protected after enabling encryption. Only to discover that the unencrypted data still existed in the datafile, next to encrypted data. The instructor was clearly not expecting this, and had no explenation.

This time, I tested on a 10.2.0.4 database on Mac OS/X and an 11.2.0.1 database on Linux, both with and without the CLOB - I seem to remember having read somewhere that LOB columns are versioned and copies remain. Below I only reproduce the results for Mac OS/X and without the CLOB for brevity (as I somehow expected this with the CLOB anyway), but the outcome is the same for all combinations. To me it appears that the column value is not overwritten, but the new value is inserted above the existing one instead. When the CLOB enters in the equation, the entire record appears to be duplicated every time a value changes. In this case it does not matter what the order is, or which column changes.

I observed this behaviour in all tests, in both versions. It seems to me that this behaviour somewhat invalidates the assumptions we make on how many rows will fit in a data block, as fragmentation of "free" space is built-in. Pre-allocating space for the CLOB problem in the OP would under this scenario not make a difference, as the new value would still cause migration. Or am I missing something?

__The test case (without LOB)__

as user:

    create table test1 ( hond varchar2(10) , kat number (2) ) ;

    Table created.

    insert into test1 values ( 'regel vier', 10 ) ;

    1 row created.

    commit;

    Commit complete.

As sys:

    alter system checkpoint;

    System altered.

in the OS:

hexdump -C ~/oradata/TVL01/users01.dbf |less

*

00051fe0 00 00 00 00 00 00 00 00 00 00 00 2c 01 02 0a 72 |...........,...r|
00051ff0 65 67 65 6c 20 76 69 65 72 02 c1 0b 03 06 fe 58 |egel vier......X|
00052000 00 a2 00 00 29 00 00 00 00 00 00 00 00 00 01 05 |....)...........|
00052010 29 a7 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |)...............|
00052020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

|................|

In sqlplus, as user:

      update test1 set hond = 'line four' where hond = 'regel vier';

      1 row updated.

      commit;

      Commit complete.

As sys:

    alter system checkpoint;

    System altered.

in the OS:

hexdump -C ~/oradata/TVL01/users01.dbf |less

*

00051fd0 00 00 00 00 00 00 00 00 00 00 00 2c 02 02 09 6c |...........,...l|
00051fe0 69 6e 65 20 66 6f 75 72 02 c1 0b 2c 00 02 0a 72 |ine four...,...r|
00051ff0 65 67 65 6c 20 76 69 65 72 02 c1 0b 02 06 31 59 |egel vier.....1Y|
00052000 00 a2 00 00 29 00 00 00 00 00 00 00 00 00 01 05 |....)...........|
00052010 29 a7 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |)...............|
00052020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

|................|

The new value ('line four') is in the file, but the old value ( 'regel vier' ) still is in the same spot where it was before!

After completing test, as user:

    select * from test1;

    HOND      |      KAT
    ==========|==========
    line four |       10

    1 row selected.

    select vsize(hond), vsize(kat) from test1;

    VSIZE(HOND)|VSIZE(KAT)
    ===========|==========
              9|         2

Cheers,
Tony

(PS. sorry to send HTML, but it may make reading the output above easier, as it will wrap badly in plain text.. )

On 17/06/10 5:54 AM, Mark W. Farnham wrote:

>
> Have you considered having a standard 2K (or so) CLOB to slap in there 
> when there row is born?
>
> Depending on the aggregate vsize of the other columns in a row and the 
> details of the block overhead of your tablespace and segment 
> specifications, you're probably getting 3 rows per block. If your skew 
> around that average of 2099 is not too bad, this will minimize row 
> migration at a cost of the extra redo on the insert.
>
> If you elect to leave the standard filler CLOB in place, that will 
> cost you an extra 1/6^th storage for where you have existing nulls. 
> Whether it would be cheaper overall to whack those to NULL in your 
> process depends on your cost of storage and the redo to whack it.
>
> That is only a win if the overhead from the row migration is actually 
> a problem to you on the access side, and it treats your outlier bigger 
> than average clobs as noise.
>
> Your details matter, and it might be possible to set pctfree, pctused, 
> and the size of the default CLOB so that a single row takes the block 
> off the free list and when its clob gets updated to the real value it 
> only puts the row back on the free list if it is likely another row 
> will fit. (I'm not saying 2K is the right size to do that. Your 
> mileage will vary.
>
> Another reasonable thing to do that comes to mind is having another 
> table altogether for the CLOB. Existing queries could be supported via 
> a view. Whether that would be an improvement over your current 
> situation with many migrated rows is open to question and measurement.
>
> I could see using partitioning to solve this as well, where you let 
> the current "period" get built ugly and then create a nice clean image 
> when current becomes previous and use partition exchange to swap that 
> back in for the ugly version of the same data.
>
> If you don't have partitioning, you could try some hybrid where you're 
> building new rows into two tables union-all'd with the permanent table 
> and cycle the rows into the permanent table periodically. That only 
> works if your operational schedule has the right opportunties or 
> you're willing to do the "poor man's partitioning" shell game with 
> views and synonyms.
>
> Or maybe someone else has some better ideas,
>
> mwf
>
> ------------------------------------------------------------------------
>

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