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

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

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

3 comments

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

   Reply

2. 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. Thanks for stopping by Frits. Always good to have your input – especially on performance as it pertains to hardware/software.

      Cheers,
      Connor

      Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.