- Article Type: General
- Product: Aleph
- Product Version: 18.01
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.
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:
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:
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
The writing to multiple archive destination causes a heavy overhead, and should be cancelled if not necessary.
- Article last edited: 10/8/2013