PS Query & SQL

 View Only

  • 1.  Query Spins On Wildcard

    Posted 4 hours ago

    Hello. I have an interesting query issue that is only occurring in our PROD instance. I don't think the issue has to do with our recent go-live with PUM 36 and PT 8.62 since the query runs fine in test instance. Specifically, when entering the wildcard value (%) in the prompt field and then running the query, it just spins and does not time out. However, when a value such as 'HIST' is entered into the the prompt field, the query runs as expected. Previously entering the wildcard here would pull every class section without issue, usually within seconds.

    Has anyone experienced a similar issue with any query post PUM/PT upgrade? The query runs as expected in our test environments where the PUM/PUT upgrade has also been applied. 



    ------------------------------
    Pheng Xiong Ph.D.
    Senior Associate Registrar
    Rochester Institute of Technology
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------
    Alliance 2026 Recordings are here!


  • 2.  RE: Query Spins On Wildcard

    Posted 4 hours ago

    We have this periodically where a query was running fine and then doesn't perform well.  When queries are running long, our DBA runs query tuner against it.  Our dba creates an SQL profile using the delivered sqltrpt.sql script (under $ORACLE_HOME).  This can help with query performance by capturing and applying specific execution characteristics that optimize how a query runs. The script is often used to generate SQL tuning profiles based on performance data, which can then be leveraged to enhance query execution.  By selecting the right profile, you can often get significant improvements in the speed and efficiency of your SQL queries.   NOTE:  Sometimes when you fix one query, it can break others.

     

    If query tuner can't find a profile.  I've rewritten queries that will change the SQL just enough to trick the optimizer into picking a different plan.

     

     

    Thanks!

    Dana Pawlowicz

    Business Systems Analyst Sr - ERP

    Digital Platforms & Innovation
    Digital Technology Solutions

    University of Cincinnati

    51 Goodman Dr.

    Cincinnati, Oh 45221

     

     




    Alliance 2026 Recordings are here!


  • 3.  RE: Query Spins On Wildcard

    Posted 4 hours ago

    Good morning Pheng,

    Assuming this is an optional Prompt, have you tried leaving the value completely blank?

    What is the query performance like if you delete the prompt and hard-code a value in criteria?

    I work in HCM, so my system isn't exactly the same as yours. 

    But when we write prompts, the assumed behavior is that an optional prompt will attempt to return "everything" when left blank.

    In my experience, the wildcard '%' acts like a LIKE operator, allowing the prompt to return values that are similar to what you entered.

    For example, %Sco should return 'Scott' and %ott% should also return 'Scott'.

    Theoretically '%   ' should also return everything, but with the PUM something may have changed.

    We noticed that our Prompts for Tree Node began causing query performance issues a few months ago.



    ------------------------------
    Scott Frey
    Connected Campus CoP Member - Senior Data Analyst-HR Info Systems
    University of Colorado System
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Alliance 2026 Recordings are here!


Related Content