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

November 24, 2013

1–2 minutes

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

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

Nuno Pinto do Souto

November 25, 2013 at 1:30 pm

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!
🙂

Reply
Frits Hoogland

August 25, 2015 at 6:00 pm

This is a very important topic, and indeed, lots of people are following the latest and greatest improvements as it has been advertised to them like a donkey following the carrot on the stick in front of their nose.

Outside of your example, it’s important to realise that what you do with your data plays an important role once you did overcome the IO latency or bandwidth barrier. This means that tests with count(*) (->which does read the row directory in the block, but does not dive into the rows) probably are way more positive than using an average (avg()) function, which does dive into the rows, and needs additional processing.

Outside of that, with exadata (which is one way of overcoming traditional IO latency and bandwidth limitations) I found that block checking is another thing which can become a huge stakeholder of the processing time. Please mind I did investigate this into detail on a V2 (nehalem cpu), so it might be better with modern processors. Also, this is not recorded in the wait interface, you need to look this up with perf on linux.

So yes, what you aim to say, and I fully agree with: with faster storage, it doesn’t mean you can create anything you like and it will fly. No. Still, good design and careful performance testing is mandatory to create something that is performing well in real life. With faster disks, it just means you hit a performance problem because of disk IO latency later, or, you run into another issue. There is no get out of jail for free card for performance. Or, as Cary would put it, there is a point where you can’t hardware yourself out of a software problem.

Reply
1. connormcdonald
  
  August 25, 2015 at 6:20 pm
  
  Thanks for stopping by Frits. Always good to have your input – especially on performance as it pertains to hardware/software.
  
  Cheers,
  Connor
  
  Reply