v18 SQL query stopped working after applying Service Pack 864-1117
- 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.
Additional Information
faq
- Article last edited: 10/8/2013