What's the point of decreasing consistent gets by using IOTs?

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Thu, 17 Apr 2003 17:48:51 GMT
Message-ID: <3e9eda2b.197473862@nyc.news.speakeasy.net>

We have some small failrly heavily used tables (500K selects per hour or so). Each lookup takes 2 block reads, one for index and one for table. I figured I'll replace the table to an IOT and reduce it to 1 block read per select, hoping to get if not 50% execution time improvement, but at least 30% or so.

However, even when I went to 10 million selects (in a loop), the effect was barely noticable--3.6% improvement in timing or so. So my question is? What's the point? Consistent gets are halved, but time to get that data barely changes!

Now I can only see the benefit of IOTs if used instead of very large tables so that fewer blocks would have to be bufferred and fewer would have to be read from the disk, possibly doing a lot of inserts/deletes. For small frequently accessed tables it seems almost useless.

Is it really worth to tune queries by <5% that take up less than <10-15% of overall database load (memory-read-wise)

Here are my outputs::

no IOT:
Time took: 713.24 secs
Consistent gets: 10,000,000

IOT:
Time took: 687.35 secs
Consistent gets: 5,000,000

If you want to reproduce:
table defs:
CREATE TABLE position (

        position_id     NUMBER(5),
        position_min    NUMBER(2) NOT NULL,
        position_limit  NUMBER(2) NOT NULL,
        CONSTRAINT position_pk PRIMARY KEY (position_id)
                STORAGE (INITIAL 8K NEXT 8K)

)
STORAGE (INITIAL 8K NEXT 8K)  create table position_iot

  (position_id number(5) primary key,
   position_min number(2) not null,
   position_limit number(2) not null)

  organization index
  storage (initial 8k next 8k)

SQL> select * from position;

          0           11             11
          1            1              3
          2            3              5
          3            3              5
          4            1              1

 insert into position_iot
 select * from position

PL/SQL block to run the test:
declare

var			number(2);
l_start 		number;
l_end			number;
v_after_gets		number;
v_before_gets		number;

begin
select value into v_before_gets
  from v$mystat my, v$statname s
  where s.statistic#=my.statistic# and s.name='consistent gets';

l_start:=dbms_utility.get_time;
for i in 1 .. 1000000
loop
/* each of these statements selects from position_iot in IOT test */

  select position_min into var from position where position_id=0; 
  select position_min into var from position where position_id=1;
  select position_min into var from position where position_id=2;
  select position_min into var from position where position_id=3;
  select position_min into var from position where position_id=4;

end loop;
l_end:=dbms_utility.get_time;

select value into v_after_gets
  from v$mystat my, v$statname s
  where s.statistic#=my.statistic# and s.name='consistent gets';   dbms_output.put_line ('Time took: '||to_char(round( (l_end-l_start)/100,5),'999.99') ||' secs'); dbms_output.put_line ('Consistent gets:
'||to_char(v_after_gets-v_before_gets,'999,999,999,999')||' ');

end;
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Apr 17 2003 - 12:48:51 CDT