Re: ? Forms load test, keystroke files, 2nd commit LONG ?

In article <bamon-210194082907_at_amon.cc.oberlin.edu>, bamon_at_ocvaxc.cc.oberlin.edu (Jennifer R. Amon) wrote:

> Here's the Question:
>
> The first time we query up a master/detail set and make a change, the
> commit takes about 1-3 seconds, and the second time we query the same
> m/d set and make a change, the commit takes between 30-120 seconds,
> with most commits falling into the 30-50 second range.
>
> I'm wondering if the second change is forcing the first change to
> be written to the database from the buffers? I'm really at a loss
> here. Ideas?
>
> The data looks like this:
>
> _______
> | A |
> |_______| If a significant change is made to
> | tables C, D, E, or F, then a whole
> _______ new set of rows is inserted into
> | B | the tables, committed and the tables
> |_______| are re-queried. This is done with an
> | on-update trigger, that chooses between
> _______ insert and update on the basis of which
> | C | fields have been changed. The tables
> |_______| are locked in share update mode for
> | this change.
> ___________________
> | | |
> _______ _______ _______
> | D | | E | | F |
> |_______| |_______| |_______|
>
>
> Thanks in advance for your help.
>

We found it! A missing index on a table that is update only when rows are deleted from table F. The insert does not do the extra update, only the delete - hence, the difference in commit times.

We were in on a Saturday, when a little extra overhead won't hurt anyone, and after we upgraded to 7.0.15, we turned on the timed statistics and ran the form with the -s switch. It didn't take us long to find the culprit.

I really wish the statistics could be enabled/disabled without having to shutdown and restart Oracle. I would use them a lot more frequently in that case.

Jennifer R. Amon            PHONE: (216) 775-6987
Houck Computing Center        FAX: (216) 775-8573
Oberlin College
Oberlin, OH 44074        INTERNET: bamon_at_ocvaxc.cc.oberlin.edu
_____________________________________________________________________