« OPTIMIZER USE FEEDBACK » : différence entre les versions
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... » |
Aucun résumé des modifications |
||
Ligne 171 : | Ligne 171 : | ||
``` | ``` | ||
[[Catégorie:Oracle]] |
Dernière version du 21 mars 2023 à 18:21
- 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;
```