query execution taking long time in oracle (parse bug)

Oracle Query Execution Long Time Due to Parsing Bug

Issue Description

In certain Oracle environments, a specific query has been observed to exhibit erratic execution times, ranging significantly from approximately 3 seconds to 50 seconds. Upon detailed analysis, it was determined that the variability in execution time was primarily attributable to the parsing phase of the query.

This prolonged parsing time was often exacerbated by sub-optimal application coding practices, which led to the query undergoing frequent hard parses rather than leveraging soft parsing. Hard parsing is resource-intensive and contributes significantly to query latency when repeated often.

Resolution

The root cause of this inconsistent parsing performance was identified as a parsing bug within the Oracle Database software. The following workaround was implemented to mitigate the issue:

ALTER SYSTEM SET "_fix_control"='13836796:OFF' SCOPE=BOTH SID='*';
ALTER SYSTEM SET "_optimizer_cost_based_transformation"=OFF SCOPE=BOTH SID='*';

Explanation of Parameters:

• _fix_control='13836796:OFF': This is a hidden parameter used to disable a specific internal Oracle fix (identified by bug ID 13836796). Setting it to OFF reverts a change that might be causing the parsing issue.

• _optimizer_cost_based_transformation=OFF: This hidden parameter controls whether the optimizer performs cost-based query transformations. Disabling it can prevent certain transformation paths that might interact negatively with the parsing bug, leading to more stable execution plans and faster parsing.

Important Note:

It is crucial to understand that setting hidden parameters (those prefixed with an underscore _) should generally be avoided without prior consultation and guidance from Oracle Support. Hidden parameters are internal to Oracle and their behavior or impact can change between database versions or even patches, potentially leading to unforeseen side effects or stability issues. Always open a Service Request (SR) with Oracle Support for confirmation and advice before applying such changes in a production environment.