| Data Block Sizing [message #129784] |
Tue, 26 July 2005 12:24  |
srinivas4u2
Messages: 66
Registered: June 2005
|
Member |
|
|
Folks, got a confusion related to DB_BLOCK sizing.
Let's consider the eg below -
Here we have 28 rows in emp1 table. The avg_row_len is 40 which means we must have all of the rows in one data block. Why is it using up all the 16 blocks?
SQL> TRUNCATE TABLE SCOTT.EMP1;
Table truncated.
SQL> INSERT INTO SCOTT.EMP1 SELECT * FROM SCOTT.EMP;
14 rows created.
SQL> INSERT INTO SCOTT.EMP1 SELECT * FROM SCOTT.EMP;
14 rows created.
SQL> COMMIT;
SQL> SELECT COUNT(*) FROM SCOTT.EMP1;
COUNT(*)
----------
28
SQL> SELECT BLOCKS,BYTES,EXTENTS FROM DBA_SEGMENTS
2 WHERE OWNER='SCOTT' AND SEGMENT_NAME='EMP1';
BLOCKS BYTES EXTENTS
---------- ---------- ----------
16 65536 1
SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN FROM DBA_TABLES
2 WHERE OWNER ='SCOTT' AND TABLE_NAME='EMP1';
BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ------------ -----------
16 0 40
Now with regard to data_block sizing, If my block size is 4k then how many rows can the block accomodate when the avg_row_len is as follows -
(1) avg_row_len is 400.
(2) avg_row_len is 40.
Iam aware of the block structure and what the header consists of.. etc.
Thanks,
Sri
|
|
|
|
| Re: Data Block Sizing [message #129793 is a reply to message #129784] |
Tue, 26 July 2005 13:26  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
Did you leave out some steps there, such as analyzing your table to calculate avg_row_len? Show us the complete session, not just a copy and paste of selected portions of it. And your version? And blocksize? And tablespace and table parameters?
|
|
|
|
| Re: Data Block Sizing [message #129823 is a reply to message #129793] |
Tue, 26 July 2005 18:23 |
srinivas4u2
Messages: 66
Registered: June 2005
|
Member |
|
|
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jul 26 18:13:24 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> show user
USER is "SCOTT"
SQL> select count(*) from emp1;
COUNT(*)
----------
28
SQL> analyze table emp1 compute statistics;
Table analyzed.
SQL> select blocks,avg_row_len from user_tables
2 where table_name='EMP1';
BLOCKS AVG_ROW_LEN
---------- -----------
13 40
SQL> select blocks from user_segments
2 where segment_name='EMP1';
BLOCKS
----------
16
SQL>
fyi -
Oracle Version = 9.2.0.4
block_size = 4k
extent_size=64k
Am using locally managed tablespace.
I would be more interested in knowing the answer for -
If my block size is 4k then how many rows can the block accomodate when the avg_row_len is as follows -
(1) avg_row_len is 400.
(2) avg_row_len is 40.
Iam aware of the block structure and what the header consists of.. etc.
Thanks,
Sri
|
|
|
|
| Re: Data Block Sizing [message #129961 is a reply to message #129784] |
Wed, 27 July 2005 08:05 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
To answer your question, as to how many will fit into the block given a certain row length, one thing you can do is simulate it. Create a table, with maybe a single column of char(40) and insert a bunch of rows into that table. Or char(400).
But keep in mind avg_row_len is just an average. char() will always take up a set number of space. Varchar2() will not, so it just depends on the type of your data. Number is a variable length size as well. So even your average length is 40, you might have one row that takes up 120 bytes (or more, depending on the definition of the table).
So even if there is 100 bytes left for data in the block, that won't be enough room, and that longer row will have to go into a new block. And then there are considerations with updates and deletes.
But you provided better data this time around. For instance, because you posted your session, I saw that you were on 9i, meaning the issue with size reported in dba_segments due to recyclebin would not be an issue for you. Also, you are using sqlplus version 9.2.0.1, but db version 9.2.0.4. I would try to use the same version sqlplus as the database if possible.
But also, you did not tell us what the pctfree was for your table and tablespace. That plays a major role.
Just curious, but what is your goal in all of this, to learn more about oracle, or to pick a block size? If it is to learn, then keep exploring as you are doing, and if you want you can download and install the print_table utility from Tom Kyte's site and show us all of the information in dba_tables and dba_tablespaces for your table and tablespace in question.
If it is to pick a block size, both 4k and 8k are consiered fairly standard sizes for typical oltp systems. If you are in a typical situation, and do not have any specific considerations or reasons in mind as to which to pick, then you'll probably be ok, at least for now, with either one, so I'd advise to just pick one and turn your attention to other matters.
|
|
|
|
| Re: Data Block Sizing [message #129969 is a reply to message #129961] |
Wed, 27 July 2005 08:33 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Hi, one other point to make for clarification:
SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'DBA_EXTENTS' AND COLUMN_NAME = 'BLOCKS'
COLUMN_NAME COMMENTS
-----------------------
BLOCKS Size of the extent in ORACLE blocks
i.e. the BLOCKS value does not represent the number of blocks with information in them, it simply represents the number of blocks in the extent.
Jim
[Updated on: Wed, 27 July 2005 08:35] Report message to a moderator |
|
|
|
| Re: Data Block Sizing [message #129999 is a reply to message #129969] |
Wed, 27 July 2005 11:12 |
srinivas4u2
Messages: 66
Registered: June 2005
|
Member |
|
|
Thanks for your responses!
Smartin, Am just exploring Oracle. This is not related to any kind of requirement. yeah..4k or 8k is pretty standard....but I want to know the reason for this behaviour.
And since am using locally managed tablespaces I've left all the values to their defaults.
Jim, The report below indicates that 16 blocks have been allocated and out of those 13 have been used.
Thanks,
Sri
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
I meant to clear my post out as utter nonsense 
but got caught up in something else. That'll teach me (again) to fully read the posts