Oracle Scratchpad

May 20, 2009

Lunchtime Quiz

Filed under: Infrastructure — Jonathan Lewis @ 11:32 am BST May 20,2009

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.

29 Comments »

  1. 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 | Reply

  2. What about PCTUSED? ;)

    Comment by lascoltodelvenerdi — May 20, 2009 @ 12:52 pm BST May 20,2009 | Reply

  3. 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 | Reply

  4. 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 | Reply

    • 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 | Reply

  5. 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 | Reply

  6. 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 | Reply

  7. 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 | Reply

    • 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         32
      

      Comment by Timur Akhmadeev — May 20, 2009 @ 2:45 pm BST May 20,2009 | Reply

  8. 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 | Reply

    • 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 | Reply

      • 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 | Reply

  9. 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 | Reply

  10. 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 | Reply

  11. 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 | Reply

  12. 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 | Reply

  13. 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 | Reply

  14. 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 | Reply

  15. 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 | Reply

    • 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 | Reply

  16. 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 | Reply

  17. 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 | Reply

  18. 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 | Reply

    • 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 | Reply

  19. [...] 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 | Reply

  20. [...] 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 | Reply

  21. [...] 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers