Rebuilding z79 and z111 Oracle indexes to improve performance
- Product: Aleph
- Product Version: 20, 21, 22, 23
- Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care
Description
1. As part of the fiscal period close/rollover, we run the acq-04 Service to regenerate the z79 (Acquisitions Order Index). Staff have found that, following this procedure, the Order index is unusually slow (3-6 seconds). And we have found that doing util a/17/3 to rebuild the z79_id and z79_id2 Oracle indexes improves performance significantly (now, 0-2 seconds).
2. A customer found that z111 (ADM Keywords Index) searching was extremely slow.
Resolution
Normally the Oracle indexes for the Aleph index tables involved are rebuilt as the last step in the rebuilding of an Aleph index. (This appears in the $aleph_proc/p_manage_nn file as a "util_a_17_i znn" step.)
But, it has been found that the p_acq_04 and p_manage_111 are exceptions: the z79_id and z79_id2 and the z111_id, z111_id1, and z111_id2 Oracle indexes are built at the very beginning of the run. And, thus, can become fragmented by the all writing that's done to the table/indexes. In these particular cases, doing util a/17/3 to rebuild them can improve their performance.
Development staff note:
The p_acq_04 job runs with oracle indexes and builds the z79 records one by one, in contrast to a job like p_manage_01 that creates files per cycles, uploads the files to the oracle tables (z95, z97, z98) and creates oracle indexes for those tables at the end of the job.
So the z79 oracle indexes can be built segmented. Maybe it would help to have realistic sizes in the file_list for z79_id and z79_id2 before running p_acq_04? [Likewise, p_manage_111.]
The generation of the Oracle indexes on these tables is quite quick. We suggest routinely doing this after running these jobs.
Note: The z353 (Patron index) is another ADM library index table. Though we don't have any real evidence, it may be that the z353_idn Oracle indexes can also suffer from this problem, and would also, in a similar fashion, benefit from a util a/17/3 rebuild. It should also be quite quick.
- Article last edited: 10-Feb-2017