Search

OakieTags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

Affiliations

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.