Re: Step Right Up and Test Your Tuning Skill!

> > >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?
> > >>
> > >>

Assume the FK constraints are against very small tables, whose indexes all fit into 1 block each. That's 8 gets.

Assume 5 of your non-unique indexes have a depth-2 tree, and 5 have a depth-3 tree: that's 10 + 15 = 25 gets that Oracle has to do to locate the block for the new entry

Assume your PK has depth-3: that 3 more gets to locate the block where the PK entry goes.

Add one get for a block to insert the table data.

Total:

        8 + 25 + 3 + 1 = 37
Your 32 is not too bad. Received on Wed Aug 14 1996 - 00:00:00 CEST