OPTIMIZER USE FEEDBACK

- Use of _OPTIMIZER_USE_FEEDBACK Parameter

   _OPTIMIZER_USE_FEEDBACK

Cela contrôle si l'optimiseur de requête choisit d'utiliser les retours d'information et produit plus de plans d'exécution pour comparer. Si c'est défini sur TRUE (par défaut), la fonctionnalité est activée.

The cardinality feedback allows the SQL optimizer to learn from its mistakes.

   It will try to improve the sql execution plan by generating more execution plan and compare it for better execution.

   > Note about cardinality feedback:

   cardinality feedback is only stored in the SGA RAM

   cardinality feedback is not shared between sessions

   only the single-table cardinality feedbacks

- Enable or disable the _optimizer_use_feedback

   ```

   ---Check value before change

   col Name for a31

   col Value for a10

   col Description for a31

   select ksppinm "Name",ksppstvl "Value"---,ksppdesc "Description"

   from x$ksppi a, x$ksppsv b

   where a.indx=b.indx and ksppinm = '_optimizer_use_feedback';

   Name                            Value

   ------------------------------- ----------

   _optimizer_use_feedback         TRUE

   ```

- Enable the cardinality feedback

   ```

   alter system set "_optimizer_use_feedback" = true scope=both;

   ```

- Disable the cardinality feedback

   ```

   alter system set "_optimizer_use_feedback" = false scope=both;

   ```

- You can test at session level.

  - Enable the cardinality feedback

```

alter session set "_optimizer_use_feedback" = true;

```

- Disable the cardinality feedback

   ```

   alter session set "_optimizer_use_feedback" = false;

   ```

_OPTIMIZER_GATHER_FEEDBACK

It controls whether the query optimizer asks the execution engine to check for mis-estimates then create feedback,

If it’s set to TRUE (the default), the feature is enabled.

Note: 1. this one is independent from OPTIMIZER_ADAPTIVE_STATISTICS

- Check the value before change

```

col Name for a31

col Value for a10

col Description for a31

select ksppinm "Name",ksppstvl "Value"---,ksppdesc "Description"

from x$ksppi a, x$ksppsv b

where a.indx=b.indx and ksppinm = '_optimizer_gather_feedback';

Name                            Value

------------------------------- ----------

_optimizer_gather_feedback      TRUE

```

-Enable the gather feedback

```

SQL> alter system set "_optimizer_gather_feedback" = TRUE scope=both;

```

- Disable the gather feedback

   ```

   SQL> alter system set "_optimizer_gather_feedback" = FALSE scope=both;

   ```

- _OPTIMIZER_USE_FEEDBACK_FOR_JOIN

   OPTIMIZER_USE_FEEDBACK_FOR_JOIN controls whether the query optimizer uses, for its estimations, the feedbacks that aren’t controlled by _OPTIMIZER_USE_FEEDBACK.

   If it’s set to FALSE (the default), the query optimizer doesn’t use the join cardinality feedbacks.

   ```

   --Check the value before change by default its false means disable

   col Name for a32

   col Value for a10

   col Description for a31

   select ksppinm "Name",ksppstvl "Value"--,ksppdesc "Description"

   from x$ksppi a, x$ksppsv b

   where a.indx=b.indx and ksppinm = '_optimizer_use_feedback_for_join';

   Name                             Value

   -------------------------------- ----------

   _optimizer_use_feedback_for_join FALSE

   ```

--Enable

```

SQL> alter system set "_optimizer_use_feedback_for_join" = TRUE scope=both;

```

--Disable

```

SQL> alter system set "_optimizer_use_feedback_for_join" = FALSE scope=both;

```