"Translated from MoS article" under Win2003 SP1, encountering unexplained database performance degradation

Translated from MoS article: Under Win2003 SP1, encountering unexplained database performance degradation
Unexplained Database slowdown Seen on Windows 2003 Service Pack 1 (Doc ID 464683.1)

Applies To:

Oracle server-enterprise edition-version 9.2.0.1 to 11.1.0.6 [Release 9.2 to 11.1]
Z*obsolete:microsoft Windows Server 2003
Microsoft Windows Itanium (64-bit)
Z*obsolete:microsoft Windows XP (64-bit AMD64 and Intel EM64T)
Microsoft Windows Server 2003 (64-bit Itanium) Microsoft Windows Server 2003Microsoft Windows XP (64-bit AMD64 and Intel EM 64T)

Description

Periodic Database slowdowns seen in Windows 2003 environments with lots of memory, running Service Pack 1

Occurrence

Environments with a high level of concurrency, many CPU s and large amounts of physical RAM is prone to this type of prob Lem.

Symptoms

Symptoms

Symptoms include any or all of the following:

1. General slowdowns in the database, which could appear to be hung, even though systemstate dumps could not show evidence of  Hangs or locking contention.  Sometimes, the database may recover from these slowdowns in its own, and begin responding with no user intervention. At and Times, the database may appear to be hung for long periods of time.

2. Other symptoms may include slow disk I/O-i.e. a large increase in average I/O times May is observed when reviewing a WR reports covering the time period in question.

3. It may also is observed that network latency increases dramatically. If running in a RAC environment, your may notice an increase in cluster latency-i.e. An increase in the wait times for GL Obal Cache requests and messages (again, as observed in AWR reports or in some cases as seen via real-time monitoring too LS).

4. If running in a RAC environment, we could see IPC timeouts in the ipcdbg logfiles.   On Oracle 10g, the ipcdbg logs is found in the $ORACLE _home\rdbms\log subdirectory.   In Oracle 9i, the ipcdbg logs is found in C:\WINDOWS\system32\osd9i. Examples of entries in the ipcdbg logfiles, indicate a slowdown would be entries such as the following:

Thread (06C4): 10/25/07 05:59:56 TCP/IP IPC errorlog, version[10.2.0.2.0.99]
10/25/07 05:59:58 | INFO | 02DC | Initialized proc [ORACLE:OPPRODRC1: # # #] from interface at#######
10/25/07 07:35:27 | ERROR | 1ae8 | Ipc_tcpconnect:async Connect err 10048 to [opp: ####:###:#####]
10/25/07 07:38:44 | ERROR | 1c6c | Ipc_tcpconnect:async Connect err 10048 to [opp: ###:###:#####]
10/25/07 07:43:37 | ERROR | 1414 | [IPCOSD.C: # # # # # #] Ipc_tcplisten2:timeout 63050 exceeds max 25000 + margin 5000
10/25/07 07:43:37 | ERROR | 1414 | [IPCOSD.C: # # # # # #] Ipcinitport:timeout 63140 exceeds max 25000 + margin 5000
10/25/07 07:43:40 | ERROR | 15f0 | [IPCOSD.C: # # # # # #] Ipc_tcplisten2:timeout 64950 exceeds max 25000 + margin 5000

5. Monitoring of system resources via Perfmon or other monitoring utilities may also reveal a increase in system time, I Ndicating a large amount of work being do by the OS kernel.

Diagnosing the problem:

This problem can being difficult to diagnose, and it's therefore advised that the solution recommended below be implemented  Proactively. The symptoms listed in this note won't always be manifest in the same.

In some cases, this problem is diagnosed by verifying via Perfmon stats, AWR reports, and ipcdbg logs as noted above. In order to get confirmation of the problem, as noted above, systemstate dumps could not show no symptoms other than slow Processing, so it could be necessary to run ADPlus and take user level dumps of the oracle.exe process, in addition to Coll   Ecting the normal systemstate dumps and hanganalyze dumps. Having a combination of systemsate dumps and ADPlus dumps of the oracle.exe process could make this issue easier to debug, b  UT in some cases it may also is necessary to get a kernel dump to confirm the exact cause. The OS or Hardware vendor should is consulted for assistance in obtaining a kernel dump.

In order to take an ADPlus dump of the oracle.exe process, at a high level, you must install the ' Debugging Tools for wind oWS ' and then run ADPlus as such:

Adplus-hang-p <pid for Oracle.exe>

Be sure to use the correct PID corresponding to the correct oracle.exe. If you are running a ASM instance, you'll most likely has at least the oracle.exe processes, but you'll most likely Want to run ADPlus and dump the oracle.exe associated with the database instance, not the ASM instance

For a complete description of "What ADPlus are and how to run it," refer to the following Microsoft support note.

Http://support.microsoft.com/kb/286350/en-us

@ for the issue described in the alert, the Adplus dumps of the Oracle process may show threads

@ kiswapcontext

@ As noted above, in some cases, it may is necessary to take a

Workaround

The cause of this problem are the following Microsoft bug, which is described in the following links:

http://support.microsoft.com/kb/919341

http://support.microsoft.com/kb/922658

This issue are known to being a problem in Windows 2003 with Service Pack 1, and can affect any application with large memory  requirements, including single-instance Oracle database, Oracle RAC Databases and Non-oracle Databases. It is strongly advised the this this Windows hotfix being installed proactively on all Oracle installations running on Windows 20 As of Service Pack 1, or that those installations is upgraded to Service Pack 2 proactively.

Patches

Microsoft have a hotfix available on top of service Pack 1, and the issue should also is resolved by upgrading to Service P  Ack 2. As noted above, see the following referenced Microsoft article for information in this fix:

Http://support.microsoft.com/kb/919341/en-us

"Translated from MoS article" under Win2003 SP1, encountering unexplained database performance degradation