Just after analyzing, query in SQL runs slow at first attempt and then runs fast at subsequent attempt

Question

I am executing queries in oracle DB, and I am noticing slowness in query execution.

For solving this problem, I analyzed all the tables used in the query. But I noticed that just after analyzing when I am running the query it executes slow, but it does not take much time in subsequent runs.

So, I checked the explain plan of both the cases and noticed there is a difference.  Query which is running fast shows in its note section that it is using statistic feedback during the execution. But according to me it should be actually vice versa. Query using statistic feedback should be running slow and the one which doesn't use statistic feedback must run fast?

Can anyone please help me with this issue?

Explain plan for slow running query (just after analyzing the tables):

SQL_ID  33mcmpx5swcz6, child number 0

Plan hash value: 853923588

...

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A2"."LINJA"="A1"."LINJA")

...

 208 - access("QM_MATERIAL_SEGMENT"."MSE_PILE_OID"="QM_MATERIAL_PILE"."MPI_PK_OID")

Explain plan for fast running query (subsequent runs after first attempt):

SQL_ID  33mcmpx5swcz6, child number 1

Plan hash value: 1555485642
 
....

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A2"."LINJA"="A1"."LINJA")

...

 206 - access("QM_MATERIAL_SEGMENT"."MSE_PILE_OID"="QM_MATERIAL_PILE"."MPI_PK_OID")
 
Note
-----
   - statistics feedback used for this statement

Answer 1

The behavior you see is exactly how statistics feedback is designed to work:

1. Oracle initially built a flawed execution plan for a highly complex query. Even though the table statistics were up to date, it's impossible for the optimizer to always accurately estimate the cardinality for every join and every condition. The flawed execution plan was slow, but Oracle was at least able to record information about where the statistics led to bad cardinality estimates.
2. On the second execution of the query, Oracle used the statistics feedback generated by the previous run to create a better execution plan. The new query runs faster, and the execution plan displays "Cardinality Feedback used for this statement" to let you know why this second run was different than the first.

Oracle has several adaptive reoptimization features that allow it to improve execution plans based on lessons learned from previous executions. Oracle queries can often run faster the more often they are run. This behavior is only surprising to you because in one of your previous questions you encountered the opposite, buggy, behavior. Now, you are seeing the feature working as it was designed to work.

How to prevent the need for statistics feedback

The prevent this problem from happening again, you can try to force the optimizer to always pick the good plan. You could use one of the many plan fixture features, such as baselines, optimizer profile, patch, or extract the full hints from the good query with dbms_xplan.display(format => '+outline') and then add those hints to the original query. To go a level deeper, and try to help the optimizer pick a better plan, you can try adding dynamic statics, look at the execution plan cardinalities and determine where the bad estimates are made and then improve those estimates by adding multi-column statistics, expression statistics, or OPT_ESTIMATE hints. Keep in mind that you're trying to solve a theoretically impossible halting problem, and the best you can hope for a system that gets the estimates mostly right most of the time.

Each one of those previous techniques is advanced, may take hours to figure out, and has complicated trade-offs. Unless this is a critical query, you might want to let this issue go and just accept that the query will be slow the first time it runs.