Oracle Database 12c Cardinality feedback

Back in the years 2015-2016, one of my client was a Boston-based Fintech company that used to provide software solutions for mutual fund, retirement, and asset management. One day, the Senior Director for Revenue and Expense Management Solutions informed that the performance of Invoice processing for a specific business group was not consistent. They started experiencing the issue after upgrading to Oracle Database 12c.

After hearing the problem, I asked her team to run the invoices for 3 times spaced across 3 different windows and provide the results including run times.

Here were the (3) runs in (3) separate 15-minute AWR windows.

Run 1 = 00:01:10
Run 2 = 00:16:55
Run 3 = 00:00:58

After analyzing the AWR reports, I summarized the below –

1. The database was on version 12.1.0.2.
2. The 12c database version has a feature called Adaptive Query Optimization & Automatic Dynamic Statistics controlled by the parameters OPTIMIZER_ADAPTIVE_FEATURES and OPTIMIZER_DYNAMIC_SAMPLING
3. For the database in question, their corresponding values were as below –
   OPTIMIZER_ADAPTIVE_FEATURES = TRUE
   OPTIMIZER_DYNAMIC_SAMPLING = 2
4. It was due to the adaptive optimization feature that each time the query was run, it went for different plans. The first iteration of invoice generation took about 1 min, during the 2nd run Oracle tried to re-optimize causing suboptimal performance as the run time was almost 17 mins . Again the third one took less time.
5. Clearly the SQL Automatic Reoptimization is no guarantee that your SQL statement is always faster after re-optimization and this problem seems to happen in complex SQL statements.
6. As a workaround, I suggested to add the hint /*+ NO_AUTO_REOPTIMIZE */ to stop Oracle from trying to re-optimize the particular SQL statement. Else at the system level, we can also prevent automatic reoptimization by setting the following parameters. This will prevent change of plans on subsequent executions after the initial execution.

_optimizer_use_feedback = false /* disables the use of statistics feedback for subsequent executions. Default is True. /

_optimizer_gather_feedback = false /* disables the gathering of execution feedback in the optimizer. Default is True. */

We can try the above workarounds and see if the invoice processing time is consistent.

However, to be sure about the above recommendations, I asked if we can install the tools SQLHC and SQLT and see if the tool recommends similar or different fix.

The SQLT report confirmed the bad plan was due to cardinality feedback which was set to TRUE in this Oracle 12c database. So we thought of two options :

Plan 01: Execute SQL> alter system set “_optimizer_use_feedback”=false;
Plan 02: Set the best plan and create base line for the SQL so that it will always use the same plan

Finally, plan 01 was approved and implemented. We asked business to run the invoices again for a number of times and all took 1 minute +/- 10 seconds.