• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
Our Production system is extremely slow:
* we are seeing slow OPAC response time, with 1-2,000 "network connection timeout"s per day;
* p_file_20 takes 55 hours to process 250,000 patrons (4,500/hour)
* there is a (constant) queue of 10,000+ z07 records waiting to be processed by the abc01 ue_01
* patron look-up by name is extremely slow
* the ARC ETL takes several days to run
Attached is an Oracle AWR (Automatic Workload Repository) report for your analysis.

Resolution:
The analysis of the AWR report by Ex Libris found that the basic problem was the wrong optimizer mode, which was causing the Oracle to work as cost-based, whereas Aleph is designed to work with a rule-based Oracle. (See below.) The implementation of the Ex Libris recommendations resulted in p_file_20 run time being reduced by 80%, no queue of z07's, etc.

Note: though in this particular case the UTIL-A-8 "List of tables and indexes analyzed" showed "no rows selected", in other cases the wrong Optimizer mode may result in tables/indexes appearing in this list....
---------- Ex Libris AWR report analysis: ----------
1. Oracle Optimizer settings:
The following parameters are set like this:

optimizer_mode string ALL_ROWS
optimizer_dynamic_sampling integer 2
sqltune_category string DEFAULT

They should be set to this:
OPTIMIZER_DYNAMIC_SAMPLING=0
OPTIMIZER_MODE=CHOOSE
SQLTUNE_CATEGORY=limited

Note: KB 8192-3980 describes the history of Oracle Optimizer Changes for ALEPH.

2. Other settings which are not defined according to our requirements are:

2.1 recyclebin
recyclebin string on
should be set to:
recyclebin string off

2.2 Data Guard:
Seems like there is a partial configuration for Data Guard, even though it is not completely set:
log_archive_dest_1 string LOCATION=/ora_archive/pdAleph
log_archive_dest_10 string LOCATION=USE_DB_RECOVERY_FILE_DEST
log_archive_dest_9 string LOCATION=/global/orabkp/PDALEPH/Archive
db_file_name_convert string /oradata/abc16, /oradata/pdAl
dg_broker_config_file1 string /aleph/app/oracle/product/10.2/dbs/dr1pdAleph.dat
dg_broker_config_file2 string /aleph/app/oracle/product/10.2/dbs/dr2pdAleph.dat
dg_broker_start boolean FALSE
fal_client string
fal_server string

The writing to multiple archive destination causes a heavy overhead, and should be cancelled if not necessary.

• Article last edited: 10/8/2013