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

    SQL Query produces a non-ending Oracle Process

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

    I am trying to run a fairly simple query against our production database, which is listed below:

    select count(*) from abc01.Z13, abc50.Z30, abc50.Z103
    WHERE z30_sub_library like 'CHEC%'
    AND z30_update_date >= 20070701
    AND z30_update_date <= 20070930
    AND z30_item_process_status is null
    AND 'ABC01' || Z13_REC_KEY = Z103_REC_KEY_1
    and substr (Z103_REC_KEY,1,14) = 'ABC50' || substr (Z30_REC_KEY,1,9);

    If I run this query on the test instance, it returns quickly and with the correct data, although I do have to modify the date span to get some data because of the time at which we took the snapshot of production. I did have some problems earlier in the month doing the Z13/Z103 translation but with help from a few of the kb articles I was able to run a query quite similar to the one above without problems on production. The only change since that time is that we have installed the July SP. Here is our current version info:

    aleph-web3-a18(1) >>ver
    SUN_OS_2, aleph-web3, Aleph 18 revision 01 copy 1, 28-Feb-2006
    Oracle product
    Service Pack: 1117
    ARC ETL version: 1.1
    PDS version: 1.3.20045

    If I run this query on the production instance, it runs for hours and causes an Oracle Process to hit 100% CPU utilization. I used the explain plan function in Oracle and the results between the two instances are different:


    HASH JOIN ()
    HASH JOIN ()

    Is it possible that the problems we had with service pack installation, see previous incidents, caused some indexing problems on the Oracle side? I can provide other data as needed.

    Our dba's have taken another look at this, and do think the problem is in our optimizer settings which we will work on adjusting.

    • Article last edited: 10/8/2013