Re: Step Right Up and Test Your Tuning Skill!

Chris Dipple <chin_at_chin.demon.co.uk> wrote:
>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

I have more questions really than answers. One thing I would try/check is for the constrained table see that the FK columns are indexed. This may should reduce the number of buffer/block visits to the parent (PK).

Hope this helps
R2
rmramos.gbs_at_worldnet.att.net Received on Wed Aug 14 1996 - 00:00:00 CEST