Skip to Content

This blog is about the new 11g feature Deferred Segment Creation

Remark, i did this blog when 11.2.0.1 was available, as Lars mentionend in the comments with 11.2.0.2 the new DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS produre can be better than my brspace-method.

After you upgrade an existing database to 11.2.0 all segments are already created and are eating up space even it they contain no data. To get rid of them you can use brspace and do an online table reorg.

The system before the reorg:

SQL> select count(*) from dba_segments where owner = 'SAPSR3';

  COUNT(*)
----------
    172076

SQL> select sum(bytes)/1024/1024 "MB" from dba_segments where owner = 'SAPSR3';

        MB
----------
130958.938

We have a total of 172076 of segments owned by SAP. Let us compile a list of probably empty tables for the reorg:

SQL> select table_name from dba_tables where num_rows = 0 and owner = 'SAPSR3';

The select returns a total 50049 tables. With this list we create a bunch of brspace commands with 1000 tables each. Here is an example:

brspace -c force -f tbreorg -a reorg -p 8 -t "WDY_FIELDGROUP,VRS_AR_IDX,VBWRK,VBMOD,VBLOG..."

Surprisingly the whole run took a bit over 12 hours, I expected a shorter runtime. First I thought I accidentally hit a few large tables with no statistics, but i couldn’t find any. 

 

A quick check for for errors (grep ‘terminated with errors’ *tbr) showed two weird deadlocks on the oracle dictionary. The tables were CRWB_BADI_INITWF and UGMD2156, closer investigation showed remains of a failed online reorg from six months ago. A brspace -a cleanup solved the problem.

 

After the all reorgs done,  we end up with around 55’000 segments left:

SQL> select count(*) from dba_segments where owner = 'SAPSR3';
  COUNT(*)
----------
     55585
SQL> select sum(bytes)/1024/1024 "MB" from dba_segments where owner = 'SAPSR3';

        MB
----------
121581.875

We have deleted about 120’000 Segments, we have also saved 9gb of database space. This is within expectations, because 100’000 times 64kb -> ~6gb.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann
    … but with 11.2.0.2 there’s even a more elegant way to “dematerialize” empty tables:

    The DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS Procedure
    (http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_spadmn.htm#CACGGIAF)
    can be started with just a schema name and it will automatically work on all tables in that schema.

    Of course your reorganization strategy allows to move segments to another tablespace and thereby freeing up data files which in turn means that filesystem space can be freed up.
    This is obviously not possible with the procedure call.

    Thanks for sharing your 11g experience!
    It’s always interesting to hear what others make of the new features.

    regards,
    Lars

    (0) 
    1. Bert Ernie Post author
      Thanks, you are right. In most cases the procedure should be the way to go. I will add a remark at the start of the blog, as soon as 11.2.0.2 is out for SAP.
      (0) 

Leave a Reply