Learning is not a spectator sport

November 24, 2013

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

Filed under: Uncategorized — connormcdonald @ 9:35 pm

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.



About these ads

1 Comment »

  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! :)

    Comment by Nuno Pinto do Souto — November 25, 2013 @ 1:30 pm


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

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: