There are many variables that can affect query performance including the presence of other jobs running on the database, a recent upgrade to either Oracle, Team Approach or both, database maintenance issues like stale statistics or a missing index, the code of the query itself, and/or normal changes to the database that cause the record set returned to dramatically increase.
To assist support in determining the cause, please provide the information below when requesting assistance with unexpectedly long-running queries.
- The name of the query and output (if applicable) in question.
- Whether the query is new or has been used regularly for a significant period of time in its present state.
- Whether the query, output OR Query Output Schedule job was modified since the last successful run and, if so, how.
- Whether Oracle or Team Approach was upgraded since the last successful run.
- Whether the query, output and/or Query Output Schedule job exists on the test system.
Once those questions have been answered, please create a support request at www.blackbaud.com/support providing the details above and referencing this solution.
NOTE - If the query is found to contain calculated fields, it should be reviewed and benchmark testing conducted. Without an established history to suggest a change in the run times of a query, explain plans and testing on the query sql and calculated field sql are the best paths of investigation to improve performance.