Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Aleph

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Aleph
    3. Knowledge Articles
    4. SQL scripts fail or run slow after optimizer changes

    SQL scripts fail or run slow after optimizer changes

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    No headers
    • Article Type: General
    • Product: Aleph
    • Product Version: 18.01

    Description:
    We implemented the changes outlined in the "Oracle Optimizer Changes for Aleph" document (see KB 14852). I understand these steps are intended to delete the statistics that Oracle 10 typically uses for cost-based optimization, as Aleph uses Oracle "hints", instead. While this seems to work okay with our online system, several of our SQL scripts now fail, run for many hours without returning results, or run until Oracle runs out of temp space (generating an ORA-01652 error). Is there a way to make our SQL scripts run successfully after implementing the optimizer changes?

    Resolution:
    I believe I have found a solution by adding a hint to the script, like this:

    <cut text>
    ...
    FROM (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */
    SUBSTR(Z13_TITLE,1,(INSTR(Z13_TITLE,'/',3,1)-1)) TITLE,
    SUBSTR(Z00R_TEXT, INSTR(Z00R_TEXT,'(')+1, INSTR(Z00R_TEXT, ')')- (INSTR(Z00R_TEXT,'(')+1 )) RTIME,
    <cut text>

    This seems to force dynamic sampling without requiring ongoing table analysis statistics. I ran a SQL script that had been failing since our changes and it now completes in 13 minutes.


    Thanks to Mike Rogers, University of Tennessee, for sharing this information


    • Article last edited: 10/8/2013
    View article in the Exlibris Knowledge Center
    1. Back to top
      • SQL retrieves all items for an ADM record, rather than just a specific item
      • SQL search in Z30_CALL_NO $$k subfield
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. 18.01
      2. contype:kba
      3. Prod:Aleph
      4. Type:General
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved