Freezing in Oracle 7 - Champ question - Give it a try if you can...!

Oracle 7 - Freezing Anomaly

Background
MPMS, Monenco's Project Management System, is an Oracle based, integrated, project management system. It is made up of various modules that relate to aspects of project management and project controls. Some of the modules within MPMS are: Cost Control, Materials Management, Purchasing, Engineering Management, Planning and Document Control.

MPMS version 4.3 was delivered to Hydro Quebec in July, and was installed on a VAX/VMS and tested for functionality and performance. It was moved to new Development, Test, Training and Production environments in September. All modules within MPMS functioned as expected, with good performance, in the VMS environment.

At the same time our corporate MPMS development team were working on the new Windows upgrade in the Cost Control module of MPMS. This has been developed on a SCO Unix platform. Some of the features and functionality in the Windows upgrade were desirable for Hydro Quebec to implement prior to receiving the Windows release. The corporate team took the design and functionality of the Windows development and "reverse engineered" the application by repainting the screens in SQLFORMS Version 3. The functions, procedures and triggers from the Windows development were then used in the upgraded character based cost module. Within MPMS there are no explicit lock statements, nor are there any "select for update" statements. The developed upgrade is received on a unix platform and is then ported up to the VAX to be recompiled and installed.

Environment
The problem is occurring in our Test environment. The VAX is dedicated to the MPMS application. It is split into 3 instances: Test, Training and Production.
Test Environment
- VAX 4000 - 600; VMS; 192 Mb memory

• Oracle 7.0.16.6.0 with a 6Mb SGA; PL/SQL 1.00.41.00.00; SQLFORMS 3.0.16.12.6 ORACLE7 Server Release 7.0.16.6.0 - Production PL/SQL Release 2.0.18.1.0 - Production Using Oracle Toolkit Version 01.00.20.03.01 (Production) Using PL/SQL Version 01.00.41.00.00 (Production) Using SQL*Menu Version 05.00.11.13.05 (Production)

Monenco MPMS Development Environment
- SCO Unix, 32Mb memory

• Oracle 7.0.15.4 with a 6.5 Mb SGA PL/SQL Release 2.0.17.1 - Production 1.00.39.00.01; SQLFORMS 3.0.16.12.7

 Problem
With a single user the Cost module of MPMS functions very well on the VAX. The functionality and performance are as expected. When additional users work within the Cost module, after a short period of time, within 2 - 10 minutes, all Cost module users will freeze in the middle of the task they were performing. This can be anything from: querying; moving from one field to another; in the middle of updating a description; inserting a record; to committing. We can patiently wait and approximately 5 - 10 minutes all frozen users will then continue to work. The freezing will reoccurr and this cycle will continue.
 Observations
- This cost control module does not experience any freezing on a unix
platform with more than one user. This has been tested at the Corporate development location and also on the platform on which we receive the software upgrades.
- The freezing occurs when more than one user is in cost and the cost
screens that are accessed by the users include at least one of the more complex screens.
- The other modules within MPMS continue to function and perform as
expected even when the freezing occurs in the cost control module.
- The other modules do not contain references to stored procedures or
functions. They also do not have triggers associated with their base tables.
- Monitoring of Oracle - SQLDBA

• When the freezing occurs, sometimes there does not appear to be any activity at the database, except SQLAREA.
• Sometimes there appears to be some occurrences of locks.
  - Monitoring of VMS - Monitor
  
• Process/Topcpu - Both users that were frozen taking up all CPU.
• Lock - When there is no freezing the New ENQ Rate is below 30 and that when we go above 30 the freezing will occur.
• Mode - When frozen supervisor node high, when not frozen supervisor node is low.
  - If one of the frozen users issues an interrupt, <ctrl c>, all users
  become unfrozen. For the user who issued the interrupt, the functioning (procedures, functions) is now abnormal, they received the following message: ERROR: POST-CHANGE trigger raised unhandled exception ORA-01013.

Please E-mail to milapier_at_ge.hydro.qc.ca Received on Fri Dec 22 1995 - 00:00:00 CET