Lli le 21 mars 2023 à 16:21

2023-03-21T16:21:45Z

← Version précédente		Version du 21 mars 2023 à 18:21
Ligne 171 :		Ligne 171 :

	```		```
			[[Catégorie:Oracle]]

Lli : Page créée avec « - 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... »

2023-03-21T16:21:06Z

Page créée avec « - 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... »

Nouvelle page

- 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

<nowiki>-------------------------------</nowiki> ----------

_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;

```

OPTIMIZER USE FEEDBACK - Historique des versions

Lli le 21 mars 2023 à 16:21