Once you’ve done your I/O…there’s still more to do !

The world is obsessed with I/O nowadays….

This is understandable – we’re in the middle of a pioneering period for I/O – flash, SSD, MLC, SLC, with ever more sophisticated transport mechanisms – infiniband, and the like.

But don’t forget, that once you get those blocks back to Oracle, you need to “consume” them, ie, get those rows and get that data…

And that’s not free !

For example, lets look at two tables, both 500 megabytes, so the I/O cost to consume them is thereabouts the same.

The first one has ~50byte rows.

SQL> create table T pctfree 0
  2  as select rownum x, rpad(rownum,50) y
  3  from ( select rownum from dual  connect by level <= 10000 ),
  4       ( select rownum from dual  connect by level <= 10000 )
  5  where rownum <=
  6    500 * 1024 * 1024   -- 500meg
  7    / 8192              -- blocksize
  8    * ( 8000 / 55 )     -- approx rows per block
  9  /

Table created.

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks, round(blocks*8192/1024/1024) mb
  2  from   user_tables
  3  where  table_name = 'T';

  NUM_ROWS     BLOCKS         MB
---------- ---------- ----------
   9309090      72431        566

SQL> set timing on
SQL> declare
  2    cursor c is select * from T;
  3    type t_list is table of t%rowtype;
  4    r t_list := t_list();
  5  begin
  6    open c;
  7    loop
  8      fetch c bulk collect into r limit 5000;
  9      exit when c%notfound;
 10    end loop;
 11    close c;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.25
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.21

So around 5.2 seconds to consume that data.  Let’s repeat that now with a table with ~500byte rows.  Data size is the same, so about 10 times fewer rows.

SQL> create table T pctfree 0
  2  as select rownum x, rpad(rownum,500) y
  3  from ( select rownum from dual  connect by level <= 10000 ),
  4       ( select rownum from dual  connect by level <= 10000 )
  5  where rownum <=
  6    500 * 1024 * 1024   -- 500meg
  7    / 8192              -- blocksize
  8    * ( 8000 / 500 )     -- approx rows per block
  9  /

Table created.

Elapsed: 00:00:09.03
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.43
SQL>
SQL> select num_rows, blocks, round(blocks*8192/1024/1024) mb
  2  from   user_tables
  3  where  table_name = 'T';

  NUM_ROWS     BLOCKS         MB
---------- ---------- ----------
   1024000      68642        536

Elapsed: 00:00:00.01
SQL>
SQL> alter table T cache;

Table altered.

Elapsed: 00:00:00.01
SQL>
SQL> set timing on
SQL> declare
  2    cursor c is select * from T;
  3    type t_list is table of t%rowtype;
  4    r t_list := t_list();
  5  begin
  6    open c;
  7    loop
  8      fetch c bulk collect into r limit 5000;
  9      exit when c%notfound;
 10    end loop;
 11    close c;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.86
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.82

Notice the difference…row consumption isnt free, and as I/O gets insanely quick..its going to figure more and more in the overall cost of things.


		

One thought on “Once you’ve done your I/O…there’s still more to do !

  1. Back in the days when database theory was taught to IT folks, there were two disciplines we had to go through if we wanted to get into data management.

    First one was Logical DB Design. It dealt precisely with how much “consumption” we’d use, given a set of data and a set of processes to mangle it. And how much that would cost us.

    The second one was physical database design, where we dealt with things like disks, blocks, storage, speed of access, “separate indexes from data”, maintenance, etcetc.

    That was BEFORE relational came along and claimed we didn’t need to do any of that because the world was flat! :)

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