SQL Query produces a non-ending Oracle Process

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Article Type: General
Product: Aleph
Product Version: 18.01

Description:
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:

Test:

SELECT STATEMENT ()
SORT (AGGREGATE)
HASH JOIN ()
HASH JOIN ()
TABLE ACCESS (BY INDEX ROW Z30
INDEX (RANGE SCAN) Z30_ID10

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.

Resolution:
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