-
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.
-
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
-
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....
-
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.
-
===
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:
SQL> select * 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 0; len 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 0; len 5; (5): 54 41 4d 49 4c -- for TAMIL
col 1; len 6; (6): 00 c0 08 5f 00 00 -- for rowid
Tamil
-
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.
-
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.
-
===
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:
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create index t1_idx on t1(object_id) ;
Index created.
SQL> select object_id, object_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.9534) 2005-02-23 14:27:30.207
----- begin tree dump
branch: 0xc00e1d 12586525 (0: nrow: 53, level: 1)
leaf: 0xc00e1e 12586526 (-1: nrow: 485 rrow: 485)
leaf: 0xc00e1f 12586527 (0: nrow: 478 rrow: 478)
leaf: 0xc00e20 12586528 (1: nrow: 479 rrow: 479)
leaf: 0xc00e21 12586529 (2: nrow: 479 rrow: 479)
leaf: 0xc00e22 12586530 (3: nrow: 479 rrow: 479)
leaf: 0xc00e23 12586531 (4: nrow: 479 rrow: 479)
leaf: 0xc00e24 12586532 (5: nrow: 479 rrow: 479)
leaf: 0xc00e25 12586533 (6: nrow: 478 rrow: 478)
leaf: 0xc00e26 12586534 (7: nrow: 478 rrow: 478)
leaf: 0xc00e27 12586535 (8: nrow: 478 rrow: 478)
leaf: 0xc00e28 12586536 (9: nrow: 479 rrow: 479)
leaf: 0xc00e29 12586537 (10: nrow: 468 rrow: 468)
leaf: 0xc00e2a 12586538 (11: nrow: 449 rrow: 449)
leaf: 0xc00e2b 12586539 (12: nrow: 449 rrow: 449)
Take avg/min of nrow - number of rows in one oracle block.
That's it.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|