drop table t1; create table t1(n1 number) pctfree 0;
Assuming that the table is created in a tablespace with an 8KB block size, what’s the largest number of row entries I can create in a block ?
P.S. it’s a bit of a trick question.
Update: The answer is here, but do read the comments on this post before you look at it.
Oracle Scratchpad 
My calculations says it’s 1374 rows, but testing – only 734.
Comment by Timur Akhmadeev — May 20, 2009 @ 12:12 pm BST May 20,2009 |
Correction: 1347 rows for calculation.
Comment by Timur Akhmadeev — May 20, 2009 @ 12:46 pm BST May 20,2009 |
What about PCTUSED? ;)
Comment by lascoltodelvenerdi — May 20, 2009 @ 12:52 pm BST May 20,2009 |
Does it not depend on the actual values in the column n1?
The greater the value, the more bytes it will take to store the row, so the fewer rows can be stored in a single block.
Comment by Steve — May 20, 2009 @ 1:09 pm BST May 20,2009 |
If the table is completely filled with NULLs (n1 can be null):
Keeping in mind the Oracle documentation (no database at hand)
“A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less,…”
and
“Nulls are stored in the database if they fall between columns with data values. In these cases they require 1 byte to store the length of the column (zero).”
(Not the case)
8192 / 3 = 2730 rows per block.
Cheers.
Carlos.
Comment by carlosal — May 20, 2009 @ 1:59 pm BST May 20,2009 |
Carlos,
That describes the typical row storage, (column count, flag byte, lock byte) but omits the two bytes needed by the “row directory” in the top of the block.
See also Martin’s note below about the minimum space reservation for migrated rows.
Comment by Jonathan Lewis — May 20, 2009 @ 5:08 pm BST May 20,2009 |
BLOCK_SIZE = 114 + (2 + 9) * NUM_ROWS
NUM_ROWS = (8192 -114 )/11 = 734 <—
But test says 733! Why?
begin
for I in 1..734 loop
insert into t1 values (NULL);
end loop;
commit;
end;
/
select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NUMBER, COUNT(*) from T1
group by DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);
BLOCK_NUMBER COUNT(*)
———— ———-
776 1
772 733
Comment by Roberto — May 20, 2009 @ 2:16 pm BST May 20,2009 |
Hi,
I am a bit late, at max 733 rows are packed in a block.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1(n1 number) pctfree 0;
Table created.
SQL> insert into t1 select null from dual connect by level commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from t1 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
———————————— ———-
5830 733
5865 733
5828 733
5832 733
5831 733
5866 602
5829 733
7 rows selected.
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
SQL>
Comment by Asif Momen — May 20, 2009 @ 2:22 pm BST May 20,2009 |
The formula is for DMT, for LMT with ASSM is:
BLOCK_SIZE = 122 + (2 + 9) * NUM_ROWS
NUM_ROWS = (8192 -122 )/11 = 733 <— OK!
Comment by Roberto — May 20, 2009 @ 2:40 pm BST May 20,2009 |
The main question is how you came to that formula? And, BTW, I *can* put 734 rows to that table :) Can you guess why?
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from t1 2 group by dbms_rowid.rowid_block_number(rowid); DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*) ------------------------------------ ---------- 3146 734 3147 734 3148 32Comment by Timur Akhmadeev — May 20, 2009 @ 2:45 pm BST May 20,2009 |
Hi,
well, I’m confused at the previous results, since I can get all 734 onto a single block, i.e.
SQL> begin
2 for I in 1..734 loop
3 insert into t1 values (NULL);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select dbms_rowid.rowid_block_number(rowid), count(*) from t1
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
———————————— ———-
666194 734
Oracle Database 10g Release 10.2.0.3.0 – 64bit Production
PL/SQL Release 10.2.0.3.0 – Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production
Comment by Martin — May 20, 2009 @ 2:42 pm BST May 20,2009 |
I know what caused that difference (733 vs. 734), but can’t explain why it exists, so I’ll wait till someone’s reasonable explanation.
Comment by Timur Akhmadeev — May 20, 2009 @ 2:48 pm BST May 20,2009 |
Timur,
Interesting that there’s a little difference between freelist managed and ASSM – and there are other odd little details that show up for other reasons: try insert /*+ append */, try alter table t1 move.
The strangest thing with the ASSM one is that the amount of free space in the block shows that there is still room for one more row – did you find a way of making Oracle use that space ?
Comment by Jonathan Lewis — May 21, 2009 @ 7:41 am BST May 21,2009 |
For internal block structure (dictionary managed tablespaces: DMTs) go to://www.ixora.com.au/q+a/datablock.htm
However:
select TYPE, TYPE_SIZE, DESCRIPTION from V$TYPE_SIZE where TYPE in (‘KCBH’, ‘UB4’, ‘KTBBH’, ‘KTBIT’, ‘KDBH’);
…
DB_BLOCK_SIZE – HSIZE = common_block_header + transaction_header + common_data_header = (KCBH+UB4) + (KTBBH +(INITRANS-1)*KTBIT) + KDBH = 110 bytes
overhead_block = (DB_BLOCK_SIZE – HSIZE) + table_directory + row_directory =
110 + 4 + 2*num_rows
…
For LMTs I think some bytes are changed.
I don’t understand your case, maybe a matter of platform implementation – I ran test on Oracle Enterprise Edition 10.2.0.1, 32 bit Windows XP Professional.
Comment by Roberto — May 20, 2009 @ 3:29 pm BST May 20,2009 |
depending on the fillfactor, right? pctused (or something like like) ;-) the counterpart of pctfree…. just a wild guess ;-)
Comment by cosmin — May 20, 2009 @ 3:42 pm BST May 20,2009 |
LMT with ASSM doesn’t use PCTUSED (instead PCTFREE decides on new rows inserts yet)
Comment by Roberto — May 20, 2009 @ 3:54 pm BST May 20,2009 |
more specifically, depending on the pctused, which, based on the inquisitive question posted — typical Jonathan Lewis ;-) — perhaps, changes from version to version? …. another very wild guess… [this or some other parameter which changes from version to version) LOL
Comment by cosmin — May 20, 2009 @ 3:57 pm BST May 20,2009 |
hmm… Roberto, is Jonathan running ASSM on his laptop, I wonder? ;-) I don’t think he specified any of these architecture setups and granted, they may affect things… and yeah, I’ve been out of the Oracle world for about a year now so my skills are rusty ;-)
Comment by cosmin — May 20, 2009 @ 4:01 pm BST May 20,2009 |
MDINH@test10g:PRIMARY> create table t1(n1 number) pctfree 0;
Table created.
MDINH@test10g:PRIMARY> desc dba_object;
ERROR:
ORA-04043: object dba_object does not exist
MDINH@test10g:PRIMARY> desc dba_objects;
Name Null? Type
—————————————————– ——– ————————————
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
MDINH@test10g:PRIMARY> insert into t1 select object_id from dba_objects;
147202 rows created.
MDINH@test10g:PRIMARY> commit;
Commit complete.
MDINH@test10g:PRIMARY> SELECT MIN (cnt), MAX (cnt), AVG (cnt), COUNT (*)
FROM (SELECT DBMS_ROWID.rowid_block_number (ROWID), COUNT (*) cnt
FROM t1
GROUP BY DBMS_ROWID.rowid_block_number (ROWID)); 2 3 4
MIN(CNT) MAX(CNT) AVG(CNT) COUNT(*)
———- ———- ———- ———-
602 733 732.348259 201
MDINH@test10g:PRIMARY>
Comment by mdinh — May 20, 2009 @ 5:20 pm BST May 20,2009 |
Hang on… this is about migrated rows isn’t it? Of course, it’s the old “blocksize/12” approximation… 736? ;-)
Comment by Martin — May 20, 2009 @ 7:46 pm BST May 20,2009 |
Martin,
The minimum space requirement for allowing migration to take place is important – and has an interesting side-effect – but it’s not the critical detail in this case.
Comment by Jonathan Lewis — May 21, 2009 @ 7:37 am BST May 21,2009 |
Since Oracle can reuse stuff in blocks, it ought to be unlimited. The question becomes, how efficient is oracle at reusing stuff in blocks. V-2 Schneider showed complete efficiency for indices.
Comment by joel garry — May 20, 2009 @ 10:28 pm BST May 20,2009 |
Joel,
Definitely getting warm with that comment.
Comment by Jonathan Lewis — May 21, 2009 @ 7:35 am BST May 21,2009 |
I think Jonathan means, with “row entries”, the pointers into row directory. If I delete rows the row directory space doesn’t come back immediately. The next rows inserted don’t use (immediately) old pointers, hence “row entries” number grow.
Comment by Roberto — May 21, 2009 @ 8:04 am BST May 21,2009 |
I have no DB instance to test this:
So nothing was said about specifying segment compression at the tablespace level.
I’d be interested if someone could test this.
Thanks,
Comment by Adnras Gabor — May 21, 2009 @ 8:38 am BST May 21,2009 |
Adnras,
Good thought – but in extreme cases, Oracle is cautious, defensive, and wasteful. I may find time to blog about that some future point.
Comment by Jonathan Lewis — May 21, 2009 @ 8:51 am BST May 21,2009 |
[…] under: Infrastructure, Troubleshooting — Jonathan Lewis @ 8:52 am UTC May 21,2009 Yesterday I asked the question: how many row entries can you create in an 8KB block with pctfree 0. It brought out some […]
Pingback by Row Directory « Oracle Scratchpad — May 21, 2009 @ 8:52 am BST May 21,2009 |
[…] Lewis has the perfect thing to follow your Log Buffer read—a lunchtime quiz on the Oracle Scratchpad. There are lots of answers already posted, so no peaking below the […]
Pingback by Log Buffer #147: a Carnival of the Vanities for DBAs | Pythian Group Blog — May 22, 2009 @ 4:44 pm BST May 22,2009 |
[…] po’ di tempo fa (esattamente era il 20 maggio) Jonathan Lewis ha lanciato sul suo blog un quiz che chiedeva per una tabella (di cui riportava lo script di creazione) con una colonna e con il […]
Pingback by Struttura interna di un blocco dati Oracle « Oracle and other — June 10, 2009 @ 10:03 am BST Jun 10,2009 |