| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 4993.1 | | DUCATI::LASTOVICA | Is it possible to be totally partial? | Mon Feb 03 1997 13:06 | 4 |
| Is there a question here?
The easiest solution would be to upgrade to VMS 7.1 where there
the ENQLM limit is relaxed.
|
| 4993.2 | also scratching my head looking for the question | NOVA::SMITHI | Don't understate or underestimate Rdb! | Mon Feb 03 1997 14:21 | 20 |
| With such a terse note we will have to guess your question...
~ RMU/VERIFY/ALL/TRANS=RO or RMU/VERIFY/CONSTRAINTS/TRANS=RO
The syntax would be READ_ONLY, not RO.
So you expected that a READ ONLY transaction would take no locks?
~ Exceeded quota
~ Exceeded enquue quota
~
~ Enqcnt is 0. Enqlm = 32767
How big is the database?
~ Problem only on Alpha 8200 Model 5/300 running VMS 6.2-1H3
~ ALG is enabled.
Does this mean that the command works on other hardware?
Ian
|
| 4993.3 | | HOTRDB::PMEAD | Paul, [email protected], 719-577-8032 | Mon Feb 03 1997 14:49 | 5 |
| You may want to find out what retrieval strategy is being used to
verify the constraints. It may be that there is a very inefficient
strategy being used that could benefit from different indices, etc. I
believe you should be able to use regular debug flags to capture the
strategy.
|
| 4993.4 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Mon Feb 03 1997 14:58 | 31 |
| I suggest you use "Sn" to get the strategy as well as the name of the
constraint...
Here is part of the log from MF_PERSONNEL
~S: Constraint "JH_EMP_ID_EXISTS" evaluated
Conjunct
Match
Outer loop
Index only retrieval of relation JOB_HISTORY
Index name JH_EMPLOYEE_ID [0:0]
Inner loop (zig-zag)
Aggregate-F1 Index only retrieval of relation EMPLOYEES
Index name EMP_EMPLOYEE_ID [0:0]
~S: Constraint "SH_EMP_ID_EXISTS" evaluated
Conjunct
Match
Outer loop
Index only retrieval of relation SALARY_HISTORY
Index name SH_EMPLOYEE_ID [0:0]
Inner loop (zig-zag)
Aggregate-F1 Index only retrieval of relation EMPLOYEES
Index name EMP_EMPLOYEE_ID [0:0]
~S: Constraint "EMPLOYEE_ID_REQUIRED" evaluated
Index only retrieval of relation EMPLOYEES
Index name EMPLOYEES_HASH [1:1] Direct lookup
...etc...
Ian
p.s. you will also get all the access strategy to the RDB$ system tables too.
You can ignore those.
|
| 4993.5 | Limitation(VMS) or DB design? | M5::BLITTIN | | Mon Feb 03 1997 15:00 | 21 |
|
Sorry for the abbreviations...
RMU/VERIFY/TRANSACTION_TYPE=READ_ONLY/ALL is the command
Database is approx 3gig. There are many constraints on the tables.
According to the customer, he did not experience this problem on
the VAX... Only since migrating to the Alpha's.
They have several db's. Supposedly, gets the same error(s) on a
smaller db. I'm having the ct do an rmu/verify/area=x/trans=read_only
on a large area to help narrow the behavior.
Is this a limitation(vms) and/or a problem with their db design?
I've read other related notes, but not seen anything definitive relative
to a solution.
I'll suggest that they upgrade to 7.1
Thanks for the feedback
|
| 4993.6 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Mon Feb 03 1997 15:37 | 23 |
| ~ RMU/VERIFY/TRANSACTION_TYPE=READ_ONLY/ALL is the command
~ Database is approx 3gig. There are many constraints on the tables.
They are sure expecting to do a lot of work. It might be better to look at
the script generated from RMU/EXTRACT/ITEM=VERIFY for this database. It will
break the verify into smaller pieces and give the same coverage. It can also
be run in parallel.
I do not believe the constraints could be the problem. if they are really
running a READ ONLY transaction then there is minimal locking.
~ Is this a limitation(vms) and/or a problem with their db design?
~ I've read other related notes, but not seen anything definitive relative
~ to a solution.
Well I would think that RMU would use very few locks. How many buffers? (show
log rdm$bind_buffers)
~ I'll suggest that they upgrade to 7.1
Why? Do you really think it will solve there problem?
Ian
|
| 4993.7 | Thank You... | M5::BLITTIN | | Mon Feb 03 1997 16:31 | 9 |
|
re: upgrade
I doubt it. An RMU/VERIFY/AREA=X ran successfully. I asked the ct
to do as you suggested in .6 and pointed out that upgrading may not be
the correct solution. Also pointed the ct to Chapter 5 in the
Guide to DB Maint. (Verifying DB).
Thank You
|
| 4993.8 | \ | NOVA::R_ANDERSON | Oracle Corporation (603) 881-1935 | Mon Feb 03 1997 16:35 | 15 |
| Did you run an RMU/SHOW LOCKS while the verify was running (preferably
with the /PROCESS=rmu_verify_pid qualifier) to display what these locks
are? It should be fairly obvious after the first several screen
fulls...
Alternately, the RMU/SHOW STATISTIC "Locking (one stat field)" should
indicate an incredible "locks requested" rate, so that should help
identify the culprit...
If the culprit is record locks (as I suspect it is), and ALG is really
enabled, then there must be sizeable activity on the area and ALG is
useless. You're probably also not running with the correct snapshot
mode in that case...
Rick
|
| 4993.9 | RDMS_DEBUG_FLAG info | M5::BLITTIN | | Thu Feb 06 1997 17:47 | 42 |
|
Additional info from rdms$debug_flags:
Since this only occurs if the /constraints qualifier is on...
-S: Constraint VISIT_ACTIVITY_FOREIGN1 evaluated
Conjunct
Match
Outer loop
Sort Conjunct Get
Retrieval sequentially of relation VISIT_ACTIVITY
Inner loop (zig-zag)
Aggregate-F1 Conjunct Index only retrieval of relation INTAKE
Generating bugcheck dump file .....
=====================================================================
Select count(*) from VISIT_ACTIVITY;
1096987
Select count(*) from INTAKE:
29594
=====================================================================
VISIT_ACTIVITY_FOREIGN1
Foreign Key constraint
Table constraint for VISIT_ACTIVITY
Evaluated on COMMIT
Souce: FOREIGN key (INTAKE_ID)
references INTAKE (INTAKE_ID)
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
=================================================================
When ct dropped the this constraint and reran the verify, it came
up with the same results, only a different, same format, constraint.
Is there anything in the constraint construct that can/should be
redesigned?
Thank You....
|
| 4993.10 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Thu Feb 06 1997 18:08 | 5 |
| Change the DEBUG_FLAGS to "SnT" and show us the results.
thanks,
Ian
|
| 4993.11 | RDMS_DEBUG_FLAGS = "SnT" | M5::BLITTIN | | Fri Feb 07 1997 15:03 | 52 |
| RDMS_DEBUG_FLAGS = "SnT"
S: Constraint USER_GROUPER_FOREIGN5 evaluated
Conjunct
Match
Outer loop
Index only retrieval of relation USER_GROUPER
Index name USER_GROUPER_SORTED_IDX_004 [0:1]
Inner loop (zig-zag)
Aggregate-F1 Conjunct
Index only retrieval of relation EMPLOYEE_LOGIN
Index name EMPLOYEE_LOGIN_SORTED_IDX_319 [0:0]
S: Constraing USER_GROUPER_FOREIGN4 evaluated
Conjunct
Match
Outer loop
Index only retrieval of relation USER_GROUPER
Index name USER_GROUPER_SORTED_IDX_231 [0:1]
Inner loop (zig-zag)
Aggregate-F1 Conjunct Index only retrieval of relation GROUPER
Index name GROUPER_SORTED_IDX_031 [0:0]
S: Constraint USER_GROUPER_FOREIGN1 evaluated
Conjunct
Match
Outer loop
Index only retrieval of relation USER_GROUPER
Index name USER_GROUPER_SORTED_IDX_118 [0:1]
Inner loop (zig-zag)
Aggregate-F1 Conjunct
Index only retrieval of relation BRANCH_PAYROLL_UNIT
Index name BRANCH_PAYROLL_U_SORTED_IDX_078 [0:0]
Rollback_transaction on db: X00000001
%RMU-I-ENDVCONST, completed verification of constraints for database
msi.rdb;1
Compile transaction on db: X00000001
T Transaction Parameter Block: (len=4)
0000 (00000) TPB$K_VERSION = 1
0001 (00001) TPB$K_DEGREE3 (serializable)
0002 (00002) TPB$K_WAIT
0003 (00003) TPB$K_READ (read_only)
Start_transaction on db: X0000001, db count=1
%RMU-F-ABORTVER, fatal error encountered; aborting verification
Error from system services request
Exceeded quota
Exceeded enqueue quota
Fatal error while accessing Rdb.
Rollback_transaction on db: X00000001
Some process info:
Peak page file size: 471952
Peak working set size: 220064
|
| 4993.12 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Fri Feb 07 1997 16:37 | 10 |
| What happened to the start of the log?
~ Rollback_transaction on db: X00000001
This has no matching start.
Looks like the constraints are not the problem, as they completed. The
transaction did a ROLLBACK so locks will be released.
Ian
|
| 4993.13 | Start et al? | M5::BLITTIN | | Wed Feb 12 1997 12:43 | 55 |
|
re: .12
Ian, there are 262 costraints being verified. I've got the output.
As you pointed out, the constraint verification is completing, and
the enqueue problem occurs after this...appears that enque is not
being released(?) after the rollback(?).
Any suggestions?
Rollback_transaction on db: X00000001
%RMU-I-BGNVCONST, beginning verification of constraints for database
msi.rdb;1
Compile transaction on db: X00000001
~T Transaction Parameter Block: (len=0)
Start_transaction on db: X00000001, db count=1
~S: CONSTRAINT APPLICATION_CASE_T_NOT_NULL1 evaluated
.
.
.
~S: CONSTRAINT USER_GROUPER_FOREIGN1 evaluated
Conjunct
Match
Outer loop
Index only retrieval of relation USER_GROUPER
Index name USER_GROUPER_SORTED_IDX_118 [0:1]
Inner loop (zig-zag)
Aggregate-F1 Conjunct
Index only retrieval of relation BRANCH_PAYROLL_UNIT
Index name BRANCH_PAYROLL_U_SORTED_IDX_078 [0:0]
Rollback_transaction on db: X00000001
%RMU-I-ENDVCONST, completed verification of constraints for database
msi.rdb;1
Compile transaction on db: X00000001
~T Transaction Parameter Block: (len=4)
0000 (00000) TPB$K_VERSION = 1
0001 (00001) TPB$K_DEGREE3 (serializable)
0002 (00002) TPB$K_WAIT
0003 (00003) TPB$K_READ (read only)
Start_transaction on db: X00000001, db count=1
%RMU-F-ABORTVER, fatal error encountered; aborting verification
Error from system services request
Exceeded quota
Exceeded enqueue quota
Fatal error while accessing Rdb.
Rollback_transaction on db: X00000001
KAREEM job terminated at 7-FEB-1997 10:28:32.49
Accounting information:
Buffered I/O count: 1266 Peak working set size:220064
Direct I/O count: 44683 Peak page file size: 471952
Page faults: 39922 Mounted volumes: 0
Charged CPU time: 0 00:02:53.45 Elapsed time: 000:07:08.17
|
| 4993.14 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Wed Feb 12 1997 14:52 | 10 |
| ~ the enqueue problem occurs after this...appears that enque is not
~ being released(?) after the rollback(?).
This is such a fundamental part of Rdb I would be surprised if the locks were
not being released by ROLLBACK. Did you run without constraints?
You will have to look at other reasons for the ENQ failure. Such as looking
at what is being locked.
Ian
|