How to use Cardinality Feedback and _ optimizer_use_feedback in oracle

This parameter is related to the Cardinality Feedback feature. The optimizer can estimate many reasons for incorrect base numbers, such as missing statistics, inaccurate statistics, or complex predicates, the base statistics feedback helps the optimizer to generate a more reasonable execution plan. for more information about this feature, see the following documents:

1.Tuning-by-Cardinality-Feedback.pdf
2. Statistics (Cardinality) Feedback-Frequently Asked Questions (Document ID 1344937.1)

This feature has been introduced since the 10g version, but its application is limited by the optimizer_dynamic_sampling parameter. In the 10g version earlier than 11g, this feature is used only when optimizer_dynamic_sampling is greater than 4. in the 10g version, the HINT syntax can be used.
/* + Dynamic_sampling (customer 4) */or set optimizer_dynamic_sampling to 4 in the session/system for testing.

If the SQL statement is used, you can use the note prompt in the SQL execution plan or the USE_FEEDBACK_STATS field in the View V $ SQL _SHARED_CURSOR.

Note prompt in execution plan

Predicate Information (identified by operation id ):
---------------------------------------------------

6-filter (ROWNUM> 0)
8-access ("DATA". "OBJECT_NAME" = "T". "COLUMN_VALUE ")

Note
-----
-Cardinality Feedback used for this statement
V $ SQL _SHARED_CURSOR

Column use_feedback_stats format a18
Column SQL _text format a80

Select c. child_number, c. use_feedback_stats, s. SQL _text from v $ SQL _shared_cursor c, v $ SQL s
Where s. SQL _id = c. SQL _id and c. SQL _id = 'an4zdfz0h7513'
And s. child_number = c. child_number;

CHILD_NUMBER USE_FEEDBACK_STATS SQL _TEXT
------------------------------------------------------------------------------------------
0 Y select * from TABLE (cast (str_func ('a, B, c') as s_type) t
1 N select * from TABLE (cast (str_func ('a, B, c') as s_type) t

For SQL statements that use the base statistics feedback feature, you can find in the trace file 10053 that the SQL statement is converted into a syntax with the hint opt_estimate.
The CF feature is very good. After using the cf feedback evaluation, oracle generates the plan based on this data for more accurate statistics, during the re-execution, the system will continuously assess whether the statistical information is accurate and repeat the parsing as follows:

When a cursor is found to be a candidate for Statistics Feedback it will be hard parsed again using the new estimates. the child cursor will be marked as not being writable able and USE_FEEDBACK_STATS set to 'y' in V $ SQL _SHARED_CURSOR.
Note: As the need for Statistics Feedback was only detected while execution of this cursor, Statistics Feedback will not actually be used for this child. However it will be used for all further child cursors created.

At the next execution, as a result of the cursor being marked as not retriable, a hard parse will again be wrongly Med and a new child created with the optimizer having used the new estimates for creating an optimizer plan.

However, because the evaluation result data of cf only exists in the memory (it needs to be re-accessed after restart), it cannot be shared among sessions, and because there are too many bugs in 11g, the common problem is that the performance decreases significantly during the second SQL execution. therefore, I usually disable this feature for databases earlier than 11.2.0.4 in 11 GB.

Partial bug list

To disable the CF feature, set _ optimizer_use_feedback to false.

Alter session set "_ optimizer_use_feedback" = false;
Or
Alter system set "_ optimizer_use_feedback" = false;