Performance problems with ADM-bib SQL queries involving Z103

• Article Type: General
• Product: Aleph
• Product Version: 20
• Relevant for Installation Type: Dedicated-Direct; Direct; Local;

Sometimes SQL queries across libraries -- such as item ADM queries getting title information from the bib library via the Z103 -- will run for 1/2 hour or and hour and then get an error such as:

ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

or:

snapshot too old: rollback segment number 2 with name 'R01' too small

Resolution:
We have found that, in some cases, changing:

substr (z103_rec_key_1,6,9)

to:

to_char(substr (z103_rec_key_1,6,9),'FM000000000')

improves the performance dramatically.

Also, adding "DYNAMIC SAMPLING", as described in KB 16384-25265 (see link in Additional Information below).