Database Growth
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Database Growth

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Database Growth

    Hi, Is it possible to know by how much the DB grows when a particular process is completed in the application - like for e.g. if I want to know by how much the DB grows everytime a user takes a test - is it possible ? I know what are the tables that get rows inserted and I can find the bytes taken by an average row for all these tables. What about the index size ? Is there a way to calculate that too ?

    Thanks.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can calculate approximately the table and index size.

    Since the index stores rowid (its size 10 bytes) along with column value, you need to do a little bit of math to arrive the total size.

    ROWID takes 10 bytes - 4 for the data layer object id, 1 for the tablespace relative file number, 4 for the block number, and 1 for the row number in the block. This called the "extended" rowid format. Oracle also uses a 6-byte "restricted" rowid format internally where the object id is invariant.

    Tamil

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Thanks for ur info on rowid Tamil.
    I came across this link on the web:
    http://www.suhas.com/isize.html

    not sure how correct it is though....

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If we're talking about existing tables & indexes, and if the number of deletes is not significant - I've found it sufficient for capacity planning to assume that the space taken by the indexes is proportional to the table size.

    I'm sure exceptional cases can be invented - but in real-life that's been OK for me.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    Since the index stores rowid (its size 10 bytes) along with column value, you need to do a little bit of math to arrive the total size.
    ===

    Sorry, I made a mistake. In the INDEX, oracle stores only restricted rowid (6 bytes only);
    PHP Code:
    SQLselect from t1 ;

            
    ID NAME
    ---------- ------------------------------
            
    10 TAMIL
            11 SELVAN
     
    SQL
    >create index t1_idx on t1(name) ;

    I took a dump of the index block.

    row#0[8016] flag: -----, lock: 0
    col 0len 6; (6):  53 45 4c 56 41 4e --- for SELVAN
    col 1
    len 6; (6):  00 c0 08 5f 00 01 --- for rowid
    row
    #1[8001] flag: -----, lock: 0
    col 0len 5; (5):  54 41 4d 49 4c     -- for TAMIL
    col 1
    len 6; (6):  00 c0 08 5f 00 00  -- for rowid 
    Tamil

  6. #6
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Will this be a safe formula for index size estimation :
    no_of_rows * (bytes for rowid + avg_length_indexed_columns) * (1 + pct_free/100)

    Thanks,
    Shiva.

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Test it in development, that is a sure way of knowing.
    Remember, we are probably talking about procedures that developers developed.
    I remember when this place was cool.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    Will this be a safe formula for index size estimation :
    no_of_rows * (bytes for rowid + avg_length_indexed_columns) * (1 + pct_free/100)

    Thanks,
    Shiva.

    ===

    What about block header that is approximately 110 bytes?

    I do not use a formula. Simple take tree dump to see how many rows are in one block.
    PHP Code:
    SQLcreate table t1 as select from dba_objects;

    Table created.

    SQLcreate index t1_idx on t1(object_id) ;

    Index created.

    SQLselect object_idobject_name from dba_objects where object_name='T1_IDX';

     
    OBJECT_ID OBJECT_NAME
    ---------- ---------------------------------
         
    66056 T1_IDX

    SQL
    alter session 
         set events 
    'immediate trace name treedump level 66056' ;

    Session altered.

    Go to udump dir and read the trace file.

    *** 
    2005-02-23 14:27:30.208
    *** SESSION ID:(36.95342005-02-23 14:27:30.207
    ----- begin tree dump
    branch
    0xc00e1d 12586525 (0nrow53level1)
       
    leaf0xc00e1e 12586526 (-1nrow485 rrow485)
       
    leaf0xc00e1f 12586527 (0nrow478 rrow478)
       
    leaf0xc00e20 12586528 (1nrow479 rrow479)
       
    leaf0xc00e21 12586529 (2nrow479 rrow479)
       
    leaf0xc00e22 12586530 (3nrow479 rrow479)
       
    leaf0xc00e23 12586531 (4nrow479 rrow479)
       
    leaf0xc00e24 12586532 (5nrow479 rrow479)
       
    leaf0xc00e25 12586533 (6nrow478 rrow478)
       
    leaf0xc00e26 12586534 (7nrow478 rrow478)
       
    leaf0xc00e27 12586535 (8nrow478 rrow478)
       
    leaf0xc00e28 12586536 (9nrow479 rrow479)
       
    leaf0xc00e29 12586537 (10nrow468 rrow468)
       
    leaf0xc00e2a 12586538 (11nrow449 rrow449)
       
    leaf0xc00e2b 12586539 (12nrow449 rrow449)

    Take avg/min of nrow number of rows in one oracle block.
    That's it. 

  9. #9
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Hi Tamil, thanks for your reply. For me it is'nt a easy thing to get tree dumps.
    I looked up this note in metalink
    http://metalink.oracle.com/metalink/...l2_gui.startup

    and it was what I had wanted.
    Thanks a lot for all your help everyone.

    Shiva.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width