Conference orarep::nomahs::rdb_60

4966.0. "Enqlm exceeded at 32767 on Alpha" by M5::LWILCOX (Chocolate in January!!) Mon Jan 27 1997 12:26

Customer is on 6.1-04 on an Alpha and is running out of enqlm even though
it's set at 32767.  Processes bugcheck in LCK$HANDLE_ENQ_FAILURE with
exceeded enque quota.  The process name in the bugcheck is the one that
he expects, and enqcnt is 0.  He states that this only started happenning
since they went to the Alpha and it looks somewhat similar to note 2452.
Fast Commit is disabled.  Number of buffers is 32 and buffer length is 32.
The VISIT_ACTIVITY table has about 1,000,000 rows in it.  I have suggested
using RUN/ENQ=.

He has a number of constraints that are constructed like this, and I believe
that's contributing to the problem and wanted to see if someone had some
suggestions on how to reword them.  He would actually like to just drop
it or see if he could accomplish the same thing via triggers.


Information for table VISIT_ACTIVITY

Columns for table VISIT_ACTIVITY:
Column Name                     Data Type        Domain
-----------                     ---------        ------
EMPLOYEE_ID                     CHAR(4)          EMPLOYEE_ID
DATE_VISIT                      DATE VMS         DATE_FIELD
PATIENT_ID                      CHAR(5)          PATIENT_ID
ECORT                           CHAR(1)
VISIT_CODE                      CHAR(3)          VISIT_CODE
DIRECT_VISIT                    INTEGER
HHHA_SUPERVISION                INTEGER
TRAVEL_TIME                     INTEGER
FINAL_VISIT                     CHAR(1)
BRANCH_OFFICE                   CHAR(2)
STAMP_DATE                      DATE VMS         DATE_FIELD
VISIT_NUMBER                    INTEGER
WEEK_ENDING                     DATE VMS         DATE_FIELD
TYP_VISIT                       CHAR(1)
TIME_ARRIVED                    DATE VMS         DATE_FIELD
TIME_DEPARTED                   DATE VMS         DATE_FIELD
BATCH_NUMBER                    INTEGER
INVOICE                         INTEGER
WORK_DAY                        CHAR(1)
CARE_KAID                       CHAR(12)
VISIT_CHARGE                    INTEGER
BILLED_DATE                     DATE VMS
VISIT_OVERRIDE                  CHAR(1)
TRAVEL_EXP                      INTEGER(2)
MILES                           REAL
BONUS                           INTEGER(2)
AUTH_FIELD                      CHAR(9)          EM_EMPLOYEE_ID
RATE_OVERRIDE                   INTEGER(2)
PAYROLL_OVERRIDE                CHAR(3)
HRS_PAY                         INTEGER(2)
EM_EMPLOYEE_ID                  CHAR(9)          EM_EMPLOYEE_ID
INTAKE_ID                       INTEGER          INTAKE_ID

Table constraints for VISIT_ACTIVITY:
INVALID_DISCIPLINE_EMPLOYEE
 Check constraint
 Table constraint for VISIT_ACTIVITY
 Evaluated on COMMIT
 Source:
        CHECK(((VISIT_ACTIVITY.STAMP_DATE < DATE VMS '22-SEP-1994 00:00:00.00')
                      or exists (select * from EMPLOYEE C2
                      where ((C2.EMPLOYEE_ID = VISIT_ACTIVITY.EMPLOYEE_ID)
                          and (((((((((C2.DISCIPLINE = 'HA')
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) >= 700
))
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) <= 799
))
                          or (((C2.DISCIPLINE = 'OT')
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) >= 400
))
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) <= 499
)))
                          or (((C2.DISCIPLINE = 'PT')
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) >= 200
))
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) <= 299
)))
                          or (((C2.DISCIPLINE = 'RD')
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) >= 600
))
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) <= 699
)))
                          or (((C2.DISCIPLINE = 'RN')
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) >= 100
))
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) <= 199
)))
                          or (((C2.DISCIPLINE = 'ST')
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) >= 300
))
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) <= 399
)))
                          or (((C2.DISCIPLINE = 'SW')
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS INTEGER) >= 500
))
                          and (CAST(VISIT_ACTIVITY.VISIT_CODE AS
      INTEGER) <= 599)))))))

TIMESTAMP_LESS_THAN_VISIT_DATE
 Check constraint
 Table constraint for VISIT_ACTIVITY
 Evaluated on COMMIT
 Source:
        CHECK((VISIT_ACTIVITY.STAMP_DATE >= VISIT_ACTIVITY.DATE_VISIT))

VISIT_ACTIVITY_FOREIGN1
 Foreign Key constraint
 Table constraint for VISIT_ACTIVITY
 Evaluated on COMMIT
 Source:
        FOREIGN key (INTAKE_ID)
                      references INTAKE (INTAKE_ID)

Constraints referencing table VISIT_ACTIVITY:
No constraints found

Indexes on table VISIT_ACTIVITY:
BRANCH_WK_VISIT_IDX             with column BRANCH_OFFICE
                                and column WEEK_ENDING
                                and column VISIT_CODE
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

PATIENT_DATE_CODE_INDEX         with column PATIENT_ID
                                and column DATE_VISIT
                                and column VISIT_CODE
                                and column STAMP_DATE
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

VISIT_ACTIVITY_BATCH_IDX        with column BATCH_NUMBER
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

VISIT_ACTIVITY_EMPLOYEE_IDX     with column EMPLOYEE_ID
                                and column WEEK_ENDING
                                and column TYP_VISIT
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

VISIT_ACTIVITY_INTAKE_ID_IDX    with column INTAKE_ID
  Duplicates are allowed
  Type is Hashed Scattered
  Compression is DISABLED
 Store clause:          STORE in VISIT_NUMBER_MA_00019

VISIT_ACTIVITY_VCODE_IDX        with column VISIT_CODE
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

VISIT_ACT_INVOICE               with column INVOICE
                                and column DATE_VISIT
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

VISIT_DATE_IDX                  with column DATE_VISIT
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

VISIT_EMPLOYEE_DATE_IDX         with column EMPLOYEE_ID
                                and column DATE_VISIT
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED

VISIT_NUMBER_IDX                with column VISIT_NUMBER
  No Duplicates allowed
  Type is Hashed Scattered
  Compression is DISABLED
 Store clause:          STORE in VISIT_NUMBER_MA_00019

VISIT_WEEK_ENDING_IDX           with column WEEK_ENDING
                                and column VISIT_CODE
                                and column STAMP_DATE
                                and column PATIENT_ID
                                and column VISIT_NUMBER
  No Duplicates allowed
  Type is Sorted
  Compression is DISABLED
  Node size  987
 Store clause:          STORE IN UNIFORM_AREA_002
                               (THRESHOLDS ARE (68,68,68))

WEEK_ENDING_HASHED_1            with column INVOICE
                                and column PATIENT_ID
                                and column WEEK_ENDING
  Duplicates are allowed
  Type is Hashed Scattered
  Compression is DISABLED
Store clause:           STORE in WEEK_ENDING_MA_00001

Storage Map for table VISIT_ACTIVITY:
     MAP_92

Triggers on table VISIT_ACTIVITY:
     VISIT_ACTIVITY_INSERT
 Source:
  VISIT_ACTIVITY_INSERT
                                   after insert on VISIT_ACTIVITY
                                   when (((not exists (select * from PATIENT C2
                                       where (C2.PATIENT_ID = VISIT_ACTIVITY.PAT
IENT_ID)))
                                       or (not exists (select * from EMPLOYEE C3
                                       where (C3.EMPLOYEE_ID = VISIT_ACTIVITY.EM
PLOYEE_ID))))
                                       or (not exists (select * from VISIT_CODE
C4
                                       where (C4.VISIT_CODE = VISIT_ACTIVITY.VIS
IT_CODE))))
                                       (ERROR
                                       ) for each row
     VISIT_ACTIVITY_BEFORE_UPDATE
 Source:
  VISIT_ACTIVITY_BEFORE_UPDATE
                                         before update of employee_id,date_visit
,visit_code,patient_id on visit_activity
                                              referencing new as new
                                                          old as old
                                              when ((((OLD.PATIENT_ID <> new.PAT
IENT_ID)
                                                    and (not exists (select * fr
om PATIENT C3
                                                                     where (C3.P
ATIENT_ID = new.PATIENT_ID))))
                                              or ((OLD.EMPLOYEE_ID <> new.EMPLOY
EE_ID)
                                              and (not exists (select * from EMP
LOYEE C4
                                              where (C4.EMPLOYEE_ID = new.EMPLOY
EE_ID)))))
                                              or ((OLD.VISIT_CODE <> new.VISIT_C
ODE)
                                              and (not exists (select * from VIS
IT_CODE C5
                                              where (C5.VISIT_CODE = new.VISIT_C
ODE)))))
                                              (ERROR) for each row



Ideas, thoughts?

Thanks.

Liz

    what is he doing when it runs out of ENQLM?

Do they have global buffers?
Have they disabled ALG?

Have they SYSGEN'ned the enq quotas properly???

Rick

   <<< Note 4966.2 by NOVA::R_ANDERSON "Oracle Corporation (603) 881-1935" >>>

>>Do they have global buffers?

No global buffers

>>Have they disabled ALG?

I'll check.

>>Have they SYSGEN'ned the enq quotas properly???

I'm not sure what you're asking here?

And, Norm, he's doing a commit after insert when he runs out.

~~           <<< Note 4966.3 by M5::LWILCOX "Chocolate in January!!" >>>

~~And, Norm, he's doing a commit after insert when he runs out.

Any deferred constraints?

How many pages in their buffers total? (approx).

Page or row locking?

     <<< Note 4966.4 by BOUVS::OAKEY "I'll take Clueless for $500, Alex" >>>


>>Any deferred constraints?

Yes, as show in .0, and I think that's part of the problem.

>>How many pages in their buffers total? (approx).

I'm not sure, they're 32x32, but I don't know the db page sizes so will check.

>>Page or row locking?

I think it was page, but that's another thing I'll check on.

Thanks for the replies so far!

Liz

> I think it was page, but that's another thing I'll check on.
    
    If it is page then that explains it all...

Even if you enable ALG etc. that doesn't offer a guarantee  that
you would never run out of enqlm? It's just that RDB will try to minimize
the # of locks it uses. 

Some of our [DEC/EDI] customers have tables that contain 100,000's of entries
and this limit has caused endless grief. 

The best answer, I sure it's mentioned elsewhere, is to upgrade to OpenVMS V7.1
as that release can permit up  to 4M locks per process. 

M

Just a little more on this and I'll wrap it up.

ALG is enabled, row level locking is enabled for all storage areas.

Re .last, did you mean 4 million locks per PROCESS?  Or was it per system?
Or per some other measure?

This happens to be the same customer that Bob Litton is working on with the
running out of enqlm on RMU/VERIFY (note 4993).

Would it possibly help to reword the constraint(s)? (how to reword?). and/or
to tweak with the buffer size/number of buffers?

Thanks again.

Liz

    I'm not sure where the 4 million number came from, but from the OpenVMS
    V7.1 DCL help:
    
    V71_FEATURES
    
      System_Management_Features
    
        OpenVMS_Clusters
    
          Lock_Manager_Quotas_and_Limits
    
               The OpenVMS lock manager has been enhances for OpenVMS
               Version 7.1. Some internal restrictions on the number of locks
               and resources available on the system have been eased and a
               method to allow enqueue limit quota (ENQLM) of greater than
               32767 has been added. No application changes are required to
               take advantage of these increases.
    
               Specifically, the OpenVMS lock manager includes the following
               additions:
    
               o  ENQLM greater than 32767 allowed
    
                  If you set ENQLM to a value of 32767 in the SYSUAF.DAT file,
                  OpenVMS treats it as no limit and allows an application to own
                  up to 16,776,959 locks, the architectural maximum allowed by
                  the OpenVMS lock manager.
    
               o  Sub-resources and sub-locks greater than 65535 allowed
    
               o  Resource hash table greater than 65535 allowed
    
               o  LOCKIDTBL size restrictions removed (LOCKITDBL_MAX obsolete)
    
               While most processes do not require very many locks
               simultaneously (typically less than 100), large scale database
               or server applications can easily exceed the previous thresholds.

<<< Note 4966.9 by HOTRDB::LASTOVICA "Is it possible to be totally partial?" >>>

Thanks, Norm!

re: 4 million  - I don't know where that came from either! It should
about 16 million.


This represents the 'architectural limit' and it is per -process.

However as to whether you can get all 16 million or not will probably
depend upon other factors. Most VMS quota's eventually boil down to pool
availability. 

Mark

Also that help is wrong about applications - it should say *most* applications
will not need to change. 

In practice, not all routes to process creation lead to 32767 being 
interpreted as the 'architectural limit'. Sometimes it just means
32767. 

This restriction only applies to those applications that create their own
processes.

Mark

    Correct.  LOGINOUT is the processor of the information in SYSUAF and it
    does the magic conversion.  If you call $CREPRC (or, I suppose,
    LIB$SPAWN) directly, and specify the quota, you get what you request.

Great, thanks for the info!!

Liz