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

I think there are three important
point implicit in your posting.

1. It's a good idea to test, and better to publish the results. This can only lead to intelligent debate rather than waffle and bull-shit.
2. There are too many people busy saying "X is a good idea, do it" without making any allowances for the environment. Just because an idea is, in principle, good isn't a reason for doing it straight away.
3. Something that tests well serially on a single-cpu machine might not behave so well on a large multi-CPU system

Looking at your overall post, it seems likely that the benefit you would get from the change is small. But if the cost of implementation is tiny, and there are no side-effects, you might as well go ahead. (Actually a single table hash cluster MIGHT be marginally better).

Bear in mind that under high pressure, there may be many people trying to do the same thing at the same time, though. Don't just check the consistent gets, check the latching (especially cache buffers chains latch). Repeat the tests on a multiple CPU system, and see if the latching is sufficiently heavy that you get a contention issue. By reducing buffer accesses you tend also to reduce latch acquisitions - and on a highly concurrent system that may be the more significant benefit.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message
news:3e9eda2b.197473862_at_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