Does this forumla still work good in Oracle 10g for calculating the size of a table?
AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15
Or is there anyother better way to calculate? Pls advice.
Printable View
Does this forumla still work good in Oracle 10g for calculating the size of a table?
AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15
Or is there anyother better way to calculate? Pls advice.
select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' will give you the real table size
the above query never was accurate and was dependant on the table being analyzed right up to date
surely you have this information since you work for oracle?
hey thx dave. used to work for oracle, not anymore.
and so, u r saying we need not analyze the table if i am using
select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' ??
hmm.. big diff between this and the query i mentioned (though i was analyzed up to date)
of course you need to analyze the table for the CBO to work properly! How much of a difference?
select avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15
from dba_tables where table_name = 'PRODUCT'
/
AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15
------------------------------------------
13925.12
SQL> select sum(bytes) from user_segments where segment_name = 'PRODUCT';
SUM(BYTES)
----------
65536
So? One is (aproximately) how much space is needed to store all those rows, the other is how much space is actually allocated by that table.
I guess, I don't know why, that your tables uses one extent and this extent is probably something like 64k large... :pQuote:
SQL> select sum(bytes) from user_segments where segment_name = 'PRODUCT';
SUM(BYTES)
----------
65536
I think you have added more confusion :confused:Quote:
Originally Posted by jmodic
First of all which is which? I presume the first statement means "how much space is needed to store all those rows" and the second means "the other is how much space is actually allocated by that table".
Secondly I didnt understand what you meant by "how much space is needed to store all those rows". What does "all the rows" mean here? You mean the rows that are existing? And also what does "how much space is needed..." mean? You mean what is the maximum space it can occupy?
Thridly I didn't understand what you meant by "the other is how much space is actually allocated by that table". You mean how is actually used? If so "used out of what (how much)?"
Two statements you made and they are so cryptic :)
Hi.
Sorry for coming late to the party, but if you're using 10g you might want to take look at this:
http://www.oracle-base.com/articles/...rce_estimation
It takes some of the guesswork out of sizing estimations and growth trends.
Cheers
Tim...
Vipassana, what do you want to know?
How big your table is now? - use dba_segments
How big your table could be? - use your formula and consider how many rows will be inserted and the rate they are inserted.
Perhaps a refresh of the concepts manual is in order.
Bazza
I think it's about time you finaly do some reading of oracle documentation, in particular The Concepts Manual.Quote:
Originally Posted by Vipassana
Untill you don't have at least a slightest idea what each of your two queries are doing and untill you don't know some of the very basic Oracle database vocabulary, any further explanation will only add more confusion. But nevertheless, I'll try to address some of your questions:
I'll leave this for your homework - I hope you'll managed to find out which of your two queries is doing what.Quote:
First of all which is which? I presume the first statement means "how much space is needed to store all those rows" and the second means "the other is how much space is actually allocated by that table".
It means all the rows that are currently in your PRODUCT table. It means NUM_ROWS from your first query.Quote:
What does "all the rows" mean here?
It means how much space do you need in the table to store those NUM_ROWS rows in it. Your query reported that you need approximately 14 Kb free space to store those rows in your table. So, if your create a new table to be 14Kb in size that would probably be sufficient to insert those rows in it. If you create your new table to be 64Kb, you'll be fine too - when you insert those rows that you currently have in your PRODUCT table, they will occupy about 14KB of your new table, leaving 50Kb free space in it. But if you create your new table to be 10Kb large, without the ability for it to dinamicaly add new extents, you won't be able to insert all of the rows from your PRODUCT table in it, because they require 14Kb (according to your formula), but you only have 10Kb free space in it.Quote:
And also what does "how much space is needed..." mean?
As I said, take a look at the Concepts manual and read about "blocks" and "segments" and "extents", about "data files" and "tablespaces". It will all become so clear......Quote:
Thridly I didn't understand what you meant by "the other is how much space is actually allocated by that table". You mean how is actually used? If so "used out of what (how much)?"
OK. Let's make this simple. The lack of detail in this answer is intentional!
Consider this:
You insert 1,000,000 rows into a new table, causing it to increase in size so it can hold all the rows. At the end of this load process the table is 1M in size. It's safe to say that it takes 1M to store 1,000,000 rows.
You then decide to delete 999,999 rows, leaving a single row in the table. The table has not shrunk so it is still 1M in size, but obviously has lots of emty space in it. Can we use the same logic as before to make a conclusion? No. If we did we would be saying that it takes 1M to store 1 row. That's obviously nonsense.
So what?
Well, measuring the size of a table by summing all the blocks or bytes gives you the size of the table, not the size of the data it is holding.
Estimating the size of the data based on num_rows and avg_row_length gives you an idea of the size of the data, not the current size of the table holding it.
What next?
Decide what you actually want to know:
- The size of the table currently.
- The size of the data in the table.
- Something else.
Cheers
Tim...
Hi Tim
I really really admire your patience :-D
regards
Hrishy
Hi
Oops i should be calling you rather Prof Tim
regards
Hrishy
You wouldn't say that if you ever worked with me. I can be a right .... :)Quote:
Originally Posted by hrishy
Where did that come from?Quote:
Originally Posted by hrishy
Hmmm lots of details. Good, now atleast I am clear that I lack certain basic fundamentals. Will give it a reading... and get back if I still have some clarifications. Thanks for all your time and patience guys.
Quote:
Originally Posted by TimHall
Well thats becoz you had all the patience in the world to explain to the students here :-) Prof Tim
Hi,
the query
select sum(bytes) from user_segments where segment_name = 'tablename'; is giving sum(bytes) as blank, not even a zero. Please help. Does the user need dba permission to run this query?
Please reply asap.
Thanks and regards,
rcm
did you actually type the tablename in lowercase?
Just encountered this post and thought some of the people encountering it as well and that would like to calculate existing row and table sizes in order to calculate the entire sizing impact of new rows in the database can use a script I wrote. The script predicts the extra size needed for new rows inserted into a table with the table indexes and all refereing tables (with foreign keys) as well. Might be useful as it was to me...
You can find it here.
(http://www.oraclehotspot.com/2015/06...ct-of-new.html)