Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Aleph
    Ex Libris Knowledge Center

    Aleph Production too slow

    1. Last updated
    2. Save as PDF
    • 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