Re: strange intermittent hangs (10-20s) Oracle 8.1.7 Win32

cbo wrote:
> hello,
> i have a problem with an Oracle Server 8.1.7.0.0 (!)
>
> the database is frequently accessed by a process (via ODBC)
> that executes small SQL orders (select by pk or unique index)
> and single line updates/deletes.
>
> all these sql orders perform generally very fast (milliseconds)
> but on this server, frequently, some orders perform in about 10-20 seconds
>
> Symptoms (i cannot be SYS on this server to run oradebug / tkprof) :
> - during the "freeze", ODBC process consumes 0% cpu, as Oracle.exe process
> - no disk Writes on these process (except Oracle that writes some blocks
> regularly)
> - no disk Reads
> - no network packet exchanged
>
> i ran some scripts (locks, waiting sessions etc..) and some tools
> (sysinternals filemon, diskmon ...) but i see nothing that can explain
> this freeze
>
> the strange thing is that, when it freezes, the execution of a "select 1
> from dual" on an sqlplus session make it restart immediatly (i can
> reproduce this 100%)
>
> Win32 network issue maybe ?
> uh... server is Windows 2003 SP1 on 32bit Intel (i know it is not
> supported!)
>
> any idea welcome !
> Christian

I agree with Mr. Morgan - patch 8i to at least 8.1.7.4 (or 8.1.7.3). There are a number of bug fixes that were corrected in just the 8.1.7.3 patch. Any reason why you are not running a version of Oracle that is supported on Windows 2003?

You should be able to run a 10046 trace at level 8 to determine the cause of the wait. One possibility: some platform independent applications go to great lengths to work around the read consistency differences between database platforms. One of the rules in the program may require the execution of a statement similar to this prior to retrieving or updating data in the database: UPDATE
  SYSTEM_CHANGE_COUNT
SET
  CHANGE_COUNT=CHANGE_COUNT+1
WHERE
  ID='PO';

If session 1 updates that table, enters invoices for the PO (or some other process), and then a dialog box appears on the user's screen "Did it Print OK", sessions 2-1000 will be waiting for the session 1 user's response before processing a COMMIT - the other sessions will likely have to wait in a queue for their chance to perform the same UPDATE statement.

You have to rule out a programming rule in the application - a 10046 trace should provide that information. You may need to grab a book to help decode the 10046 trace file.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Sep 20 2006 - 15:56:38 CDT