A naukri.com initiative
Dbi-Services
1M
8
Oracle: A possible method to address a slow query if there is time pressure
- In time-sensitive situations where addressing a slow query quickly is necessary, it can be helpful to test if the query ran faster with a previous OPTIMIZER_FEATURES_ENABLE-setting to identify the root cause of the performance issue.
- To find the optimizer bug fix causing a suboptimal plan, start by checking different OPTIMIZER_FEATURES_ENABLE settings to see when the query started running slow.
- ASH data can provide insights into how long the query has been running slowly and guide the investigation.
- By testing various OPTIMIZER_FEATURES_ENABLE settings, such as '11.2.0.1' to '11.2.0.2', you can pinpoint the version change that impacted query performance.
- Identifying the specific bug fix that caused the suboptimal plan involves testing _fix_control settings introduced with the affected OPTIMIZER_FEATURES_ENABLE version.
- Creating a script to test each _fix_control setting helps determine the exact bug fix responsible for the query slowdown.
- Once the problematic bug fix is identified, such as bugno 9195582, implementing a workaround like disabling the fix through a hint can improve query performance.
- Verifying the effectiveness of the workaround and creating a SQL patch for the query can help ensure that the performance issue is addressed and documented for future reference.
- When applying workarounds, it is essential to make minimal changes and document them properly, aiming to resolve the root cause in the long term.
- Tools like Oracle's SQLTXPLAIN can provide detailed analysis of plan changes due to optimizer settings and underscore parameters for in-depth query optimization.
Read Full Article
Like
For uninterrupted reading, download the app