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

Description:
I have an SQL query that checks for patrons with fines who do not have a type 02 ID record, and thus can't be exported to our financial systems.

It runs twice a month, and until now hasn't been a problem. But since last week, it won't run anymore. We've left it processing for at least 3 hours with no results. It just sits there eating up 25% CPU.

The embedded subquery runs quickly by itself, just a couple seconds. And it returns only 3 results. If I type in those 3 keys, instead of using the subquery, then the main query does run in a few seconds.

I've tried removing all the SUBSTR and using the full z305_rec_key. It doesn't help. I've also rebuilt the z305_id index twice.

The only thing I can think of that changed since it last ran successfully is that I've installed version 18 service pack 864-1117 (March - Sept. 2007). Several changes in that range worked on Oracle statistics and performance issues. Could that be related to the problem?

Resolution:
Making the changes described in the document "Oracle_Optimizer_Changes_for_Aleph_update_apr_30_2007.doc" corrected this problem.

This document was distributed as an attachment to the email sent to the SysAdmin list on March 31, 2007, with the Subject, "Oracle Analyzed Tables / Statistics / Cost-Based Optimizer with ALEPH 18 / Oracle 10". It was referenced in the entry for SKB 8192-3980 in that email. It is also included as an attachment to the CRM SKB 8192-3980 itself.