Oracle SQL

    Alter Table Shrink Space and SmartScan

    Posted on May 4, 2017 by Roger MacNicol Posted in oracle, SmartScan

    There are three main tools available for cleaning up a segment (Alter Table Shrink, Alter Table Move, and export/import), but one of them isn’t as helpful as you might have thought.  

    Consider the following sequence of events where we update the 256th column to cause widespread fragmentation:

    SQL> update t set c256 = 'abcdefghijklmnopqrstuvwxyz'; 
    2000000 rows updated.
    SQL> commit;
    Commit complete.
    
    SQL> analyze table t compute statistics;
    Table analyzed.
    
    SQL> select chain_cnt from dba_tables where table_name = 'T' and owner = 'FUSION';
    CHAIN_CNT
    ----------
       2000000
    
    SQL> select sum(blocks) from user_segments where segment_name = 'T';
    
    SUM(BLOCKS)
    -----------
         139264
    SQL> alter table t enable row movement;
    Table altered.
    SQL> alter table t shrink space cascade;
    Table altered.
    SQL> analyze table t compute statistics;
    Table analyzed.
    
    SQL> select chain_cnt from dba_tables where table_name = 'T' and owner = 'FUSION';
    
    CHAIN_CNT
    ----------
       1970068
    
    1 row selected.
    

    Note: ‘chain_cnt” does not count chained rows, rather it counts rows whose row pieces are chained across more than one block.  A Row that is in three pieces but all three pieces are in the same block has a zero chain_cnt.

    In this particular artificial scenario Shrink has not gained us much reduction in space used, and more importantly it hasn’t reduced the kind of fragmentation that affects SmartScan performance.

    This is because Shrink works in two phases. In Phase 1, the segment is scanned down from the end of the segment to the beginning. Rows with their head piece in the currently scanned block are moved together with all their row pieces. The segment is scanned from beginning upwards looking for space for the entire row. When it is unable to move any more entire rows, Phase 2 starts scanning down again from the end of the segment trying to move individual row pieces to blocks with space. This meant that while Phase 1 could potentially reduce chaining for relocated rows, Phase 2 was very unlikely to reduce the chain count and could in fact increase the chain_cnt. The moral of this is that Shrink really is for freeing up blocks close to the High Water Mark and not for cleaning up fragmented rows.

    Now let’s try Alter Table move with the same segment:

    SQL> alter table t move; 
    
    Table altered.
    
    SQL> analyze table t compute statistics;
    Table analyzed.
    
    SQL> select chain_cnt from dba_tables where table_name = 'T' and owner = 'FUSION';
    CHAIN_CNT
    ----------
         45976
    1 row selected.
    
    SQL> select sum(blocks) from user_segments where segment_name = 'T';
    
    SUM(BLOCKS)
    -----------
          92160
    1 row selected.
    

    OK, that did what we hoped: more space has been reclaimed but more importantly for SmartScan, the number of fragmented rows has been reduced considerably.

    With the fix for 19433200, the mechanics of Shrink have been reworked and it is now better at reducing the chain_cnt. However, even with the improvements made, when faced with heavily fragmented rows, Alter Table Move or export/import are likely to provide significantly better table scan performance with SmartScan.

    Roger MacNicol

    Oracle Data Storage Technology 

    Print PDF eBook
    oracle Roger MacNicol SmartScan
    « SQL validation during PL/SQL compilation
    Controlling the offload of specific operators »
    photo Sayan Malakshinov

    Oracle Database Developer Choice Award winner
    Oracle ACE Oracle ACE
    Oracle performance tuning expert
    UK / Aylesbury / Transmedia Dynamics
    sayan@orasql.org

    Simple Android Oracle client

    Get it on Google Play

    Recent Posts

    • Where does the commit or rollback happen in PL/SQL code?
    • :1 and SP2-0553: Illegal variable name “1”.
    • ORA exceptions that can’t be caught by exception handler
    • Another interesting troubleshooting case
    • SQL*Plus tips #9: Reading traces and incident files

    Recent Comments

    • Oracle SQL | Shining some light on Database In-Memory vs the Exadata Columnar Cache in 12.1.0.2 on Tracing Hybrid Columnar Compression in an offload server
    • Oracle SQL | More on tracing the offload server on Tracing Hybrid Columnar Compression in an offload server
    • Oracle SQL | DMLs and the Columnar Cache on ADW on How to tell if the Exadata column cache is fully loaded
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 3
    • Oracle SQL | Serial Scans failing to offload on When bloggers get it wrong – part 2
    • Oracle SQL | Serial Scans failing to offload on When bloggers get it wrong – part 1
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 2
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 1
    • Oracle SQL | The beginners guide to Oracle Table Scans on When bloggers get it wrong – part 1
    • Case Study | Oracle Scratchpad on Lateral view decorrelation(VW_DCL) causes wrong results with rownum

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL