Re: Step Right Up and Test Your Tuning Skill!

Chris Dipple wrote:
>
> In article <320D6BC5.6837_at_wirehub.net>, Gert Rijs <gem_at_wirehub.net>
> writes
> >John Dennis wrote:
> >>
> >> I'm getting a large number of buffer gets while I'm performing a
> >> insert statement to a table and don't know why. The table has several
> >> (8) FK constraints which are contributing to the number of gets but
> >> not all of them. Also there is one unique (PK) and 10 non-unique
> >> indexes on the table. There are approx 32 buffer gets (16K blocks) for
> >> every row inserted.
> >>
> >> I believe that (1) gets are performed to check the FK constraints are
> >> not violated and (2) gets are performed to check that any unique (PK,
> >> etc) constraints are not violated. These two might add 10 gets, tops,
> >> to the insert but I have no idea what the others are coming from.
> >>
> >> Any clever ideas?
> >>
> >>
> >Some gets are probably due to the compilation of your sql statement.
>
> Some gets are probably due to the 10 nonunique indexes!

Think of it not as 32 distinct gets, but as (32 x 16K) being 'got'. There could be 32 separate 'checks' which cause a buffer get, but it seems far more likely that there are <32 - say 19 - some of which can't be satisfied with a single 16K's worth. (Ten indexes and 8 FKs, by crikey!)

32 strikes me as a nice round number, too - but that may be coincidence.

-- 
Phil Edwards                    phil_at_news400.com
Editor, NEWS/400.UK             +44 (0)161 929 0777