Things you learn while studying for OCP

So, these days I’m studying for my Oracle certification. I’m studying with a friend, and we use a book from Oracle Press to prepare. We are both experienced DBAs, so we make a game of finding mistakes in the book. Its a fun game, and keeps us alert while going over rather boring material.

Yesterday, I’ve read that Oracle doesn’t allocate space for a column, even if it is fixed size, until there is data in it.
While this is certainly space efficient, it seemed like a very difficult way to manage space – you have to keep moving columns around when people update them. So, we suspected that the book is making a mistake, and that Oracle allocates the space for fixed size column when the row is inserted, even if the column is empty.

Time for verification:
create table t1
(X integer, Y integer)

I created an empty table, with two fixed size columns, and checked the size:

SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 65536         8             1

Nothing surprising. So lets insert some data, but only to the first column. Keep the second empty:

begin
for i in 1..30000 loop
    insert into t1 (X) values (i);
end loop;
end;

And check size again:

SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 458752        56             7

Look, the table just got bigger.
Just for the heck of it, lets see what happens when I insert one row with both columns. Will this be enough to allocate trigger allocation in all rows?

insert into t1 values (1,1);
SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 458752        56             7

Nope. Nothing much changed.
So lets update all rows with value in the second column and see what happens:

update t1 set Y=1;
SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 851968        104            13

So, the table doubled. The book was correct – the second table was not allocated when I did the first insert, only after the update.
Since I assume that Oracle will now put one column of a row in one extent and the second in another extent, the update probably involved moving a bunch of rows around to the new extents. Something to keep in mind when trying to figure out why update is using to much IO.

Advertisements

8 Comments on “Things you learn while studying for OCP”

  1. dombrooks says:

    > Since I assume that Oracle will now put one column of a row in one extent and the second in another extent

    Don’t think that’s a correct assumption at all.
    The PCTFREE settings will determine how much free space on the block is reserved for updates to the existing rows. If an update to a row means that the row is too large to fit on the block, you’ll get row migration, etc, etc.

  2. Nigel says:

    Hi

    And another thing to learn: INTEGER is not fixed size. It’s an ANSII datatype which gets mapped to NUMBER(38) (see SQL Reference – Data Types). So its length can vary like all NUMBERs (not to be confused with PLS_INTEGER…).

    SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 11 08:48:56 2007
    Connected.
    SQL> select dump(1) from dual
    2 /

    DUMP(1)
    ——————
    Typ=2 Len=2: 193,2

    SQL> select dump(123456) from dual
    2 /

    DUMP(123456)
    ————————-
    Typ=2 Len=4: 195,13,35,57

    SQL>

    Regards Nigel

  3. Don Seiler says:

    I didn’t find the Oracle Press all-in-one guide very helpful at all for my recent OCA exam. The self-study CD from Oracle University was extremely helpful. Probably helps that both the CD and the exam are from Thomson Prometric.

    Hopefully that holds true for the OCP exam (next year).

    Anyway, my $0.02.

  4. Sidhu says:

    Hi Chen

    Good one !

    Got link to your blog from Doug’s blog. Nice content. I am bit new to total DBA stuff and you know I too became DBA just like you. When I started my job, I wanted to do something else (core programming like C) but was made to do something else, then from there I changed my job and wanted to do PL/SQL & Java but was made DBA 🙂 These days I am working as a DBA for a project in India.

    Hoping to see some great stuff here. Good Luck !

    Sidhu

  5. prodlife says:

    Hi Dombrooks,
    I think we are both saying exactly the same thing. If there is not enough space on the block to accommodate the row after an update, the row will be migrated to a new block.
    A single row will be split between two rows only if the row itself requires more space than a block size. This sounds like a horrible performance issue, and I think something like this was documented in the Oracle Insights book.

  6. prodlife says:

    Hi Nigel,

    Good catch! It didn’t affect the results of the test I ran, but thats because the test was not very accurate anyway.

  7. prodlife says:

    Hi Don,

    Thanks for the tip. I’ll look for the self study guide. I agree that the Oracle Press book is not very good, but since I already know most of the material, and just need some review, I thought it was good enough.

  8. prodlife says:

    I’ve just noticed the price for the Self Study CD. I doubt if I’ll get reimbursed for 600$ CD after getting 100$ book.

    Don has a very generous employer.


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