Re: Step Right Up and Test Your Tuning Skill!

In article <3211AF2F.26BB_at_jlcomp.demon.co.uk>, ora_mail_at_jlcomp.demon.co.uk says...
>
>> > >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.

And don't forget to account for recursive sql gets and gets to aquire the rollback segments. Received on Sat Aug 17 1996 - 00:00:00 CEST