Re: Step Right Up and Test Your Tuning Skill!

In article <320D6BC5.6837_at_wirehub.net>, Gert Rijs <gem_at_wirehub.net> writes
>John Dennis wrote:
>>
>> ... Trying to get creative with my "Subject:" :-)
>>
>> Question:
>>
>> What hidden queries does Oracle do to cause "buffer get(s)" during a
>> insert?
>>
>> or!
>>
>> What in world can one do to reduce the time an insert takes?
>>
>> 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?
>>
>> Virtually,
>>
>>
>Some gets are probably due to the compilation of your sql statement.

Some gets are probably due to the 10 nonunique indexes!

-- 
Chris Dipple