_OPTIMIZER_USE_FEEDBACK & _OPTIMIZER_GATHER_FEEDBACK in Oracle

Use of _OPTIMIZER_USE_FEEDBACK Parameter

_OPTIMIZER_USE_FEEDBACK
It controls whether the query optimizer choose to use the feedback’s and produced more execution plan for compare. If it’s set to TRUE (the default), the feature is enabled.
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:

  1. cardinality feedback is only stored in the SGA RAM
  2. cardinality feedback is not shared between sessions
  3. 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;
This entry was posted in Oracle on by .

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.