11g new features: fast and slow execution speed

Today, I am calling some fixed SQL statements, adjusting the writing method, adding some notes, and changing several aliases. There is nothing to do with it. As a result, an SQL statement suddenly gets stuck. A total of three left join EXISTS, each table cannot exceed 1 million rows. It is reasonable to say that such SQL statements will not take more than one minute even if they are slow. Result execution

Today, I am calling some fixed SQL statements, adjusting the writing method, adding some notes, and changing several aliases. There is nothing to do with it. As a result, an SQL statement suddenly gets stuck. A total of three left join EXISTS, each table cannot exceed 1 million rows. It is reasonable to say that such SQL statements will not take more than one minute even if they are slow. Result execution

Today, I am calling some fixed SQL statements, adjusting the writing method, adding some notes, and changing several aliases. There is nothing to do with it.

As a result, an SQL statement suddenly gets stuck. A total of three left join EXISTS, each table cannot exceed 1 million rows. It is reasonable to say that such SQL statements will not take more than one minute even if they are slow. I am confused about the result execution effect. The first execution is very fast and results are generated in two seconds. It will take a long time to execute it again. I have never waited for the result. It took up to half an hour to wait. I have finished Smecta for lunch...

Look at the plan. No problem, no problem at all. It's okay to check the database load. It can be said that the database is idle. Why? If the execution speed is high, it means that Oracle can find a basic and correct plan. But why is the second time slow? I suddenly thought of a new feature, a new improvement to the execution plan within 11 GB.

_ OPTIMIZER_USE_FEEDBACK

It can be simply understood that this is a continuous improvement from Oracle to CBO. The PLAN is a speculation about the actual execution time based on various data. COST is also based on the results of some algorithms and is not a definite value. After Oracle executes this statement, some sampling results can be obtained in the actual environment. These results are actually run, not speculative or computed. These values are much more reliable than the calculated COST and ROWS, so the 11g intends to use these values for dynamic speculation, A more practical plan improvement is achieved based on detailed details of a trusted execution plan.

But as the saying goes, Oracle cannot use R2, And it coughs...

My SQL was optimized by this parameter. Oracle optimized the result based on the first execution, and then I got depressed...

Let's talk about the verification and solution. HINT, OPT_PARAM ('_ OPTIMIZER_USE_FEEDBACK', 'false') can be added for verification '). If the execution effect is no longer messy after it is added, change the implicit parameter to false.

Note that the implicit parameter should be enclosed in quotation marks and written as "a" = B ". If you can see the person here, you can change the parameter, right. You can change the session and system without restarting.

Original article address: 11g new features are fast and slow, thanks to the original author for sharing.