Re: Slow commits

Have you looked at the way in which the Forms have been developed.

ie have any explicit locks been built into the code, or alternatively bad code that effectively takes row locks on a complete query set.

In Forms any input to a retrieved row will cause a row lock to be taken out and its not unusual for developers to happily apply assignments in post-query processing, missing the fact that these locks are occurring.

As a very long shot has the application been migrated from an early legacy forms version which was developed against a database version before row locking was available, with a possibility that, again, it has unfortunate explicit lock handling built into the code?

I know you say in your mail that you have analysed stats on the database, but - and this is probably a silly question - have you run a real time analysis with the users in the 'commit wait' period?

good luck

-- 
Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com
http://www.Lane-Associates.com


Kristen Cameron wrote:


> 

> 

> I am a brand new DBA, and  apologize in advance for the far too

> general nature of my question.  However, I have reached the

> end of my meagre knowledge.  I'm looking for a little guidance

> from the more experienced.  I've attached BSTAT/ESTAT reports

> for the truly generous, along with a couple of other text files of

> statistics.  Contact me if you want them but didn't receive them.

> 

> Using an Oracle Forms application, users are finding that

> commits take 3 to 5 minutes when done on certain tables

> in the database.  It would seem that the problem should be

> obvious, but I cannot find it.  There are about 15 tables involved

> in the part of the application that is experiencing this

> slowdown.  Most of them are look up tables, but a few are

> relatively large (10,000+ records) tables of data that are being

> updated.  The tables seem to be indexed reasonably.  Most

> items are referenced using an id number, and the id column

> is indexed either alone or jointly with another column.

> 

> We are using Oracle 7.3.4 on Sun Solaris 2.6.  Our users are

> using Windows 95.  Our network is Novell.

> 

> To date, I have run all kinds of statistics on the database, but

> have found no problems.  CPU, memory, storage, waits, latches,

> I/O, locks, hit ratios, etc. all seem good.  There may be some disk

> contention, but it appears minimal and may be unavoidable, since

> we only have two processors, anyway.  Actually, on the whole, the

> numbers look fantastic, as near as I can tell from my reading on the

> subject - Oracle documentation, online articles, Niemiec's book on

> Performance Tuning, and Loney's DBA Handbook.

> 

> At this point, I am assuming that the problem is within the

> application code itself, especially since it began when the

> application was developed.  However, it seems to be getting worse.

> 

> Is there anything further I can do to test it, or better yet, fix it?

> 

> Thanks for any and all help.

> 

> - Kristen Cameron

>   Indian and Northern Affairs Canada

> 

> 

>   ---------------------------------------------------------------------------

> <encoded content removed -- binaries not allowed by ListGuru>

> 

>   ---------------------------------------------------------------------------

> The previous attachment was filtered out by the ListGuru mailing

> software at fatcity.com because binary attachments are not appropriate

> for mailing lists.  If you want a copy of the attachment which was

> removed, contact the sender directly and ask for it to be sent to

> you by private E-mail.

> 

> This warning is inserted into all messages containing binary

> attachments which have been removed by ListGuru.  If you have questions

> about this message, contact Postmaster_at_fatcity.com for clarification.

> 

>   ---------------------------------------------------------------------------

> <encoded content removed -- binaries not allowed by ListGuru>

> 

>   ---------------------------------------------------------------------------

> The previous attachment was filtered out by the ListGuru mailing

> software at fatcity.com because binary attachments are not appropriate

> for mailing lists.  If you want a copy of the attachment which was

> removed, contact the sender directly and ask for it to be sent to

> you by private E-mail.

> 

> This warning is inserted into all messages containing binary

> attachments which have been removed by ListGuru.  If you have questions

> about this message, contact Postmaster_at_fatcity.com for clarification.

> 

>   ---------------------------------------------------------------------------

>                    Name: sqlarea3.lst

>    sqlarea3.lst    Type: application/x-unknown-content-type-LST_auto_file

>                Encoding: 7bit

> 

>   ---------------------------------------------------------------------------

> The previous attachment was filtered out by the ListGuru mailing

> software at fatcity.com because binary attachments are not appropriate

> for mailing lists.  If you want a copy of the attachment which was

> removed, contact the sender directly and ask for it to be sent to

> you by private E-mail.

> 

> This warning is inserted into all messages containing binary

> attachments which have been removed by ListGuru.  If you have questions

> about this message, contact Postmaster_at_fatcity.com for clarification.

> 

>   ---------------------------------------------------------------------------