Adaptive feature caused performance in Oracle 12c

In Oracle 12c, Adaptive feature is released with 12.1 release of Oracle Database.
optimizer_adaptive_features is the parameter in 12.1 released control the behavior of optimizer with one parameter optimizer adaptive features

--Disable
alter system set optimizer_adaptive_features=false scope=both;
--Enable
alter system set optimizer_adaptive_features=true scope=both;

 
In release 12.2, Oracle depreciated the optimizer_adaptive_features parameter into two parameters Optimizer_Adaptive_plans and optimizer_Adaptive_statistics.


--Disable
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = FALSE SCOPE=BOTH;
--Enable
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE SCOPE=BOTH;

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE SCOPE=BOTH;
--Disable
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = FALSE SCOPE=BOTH;

 
Adaptive feature in optimizer is used for making the run time discussion about choose of execution plan and take discussion of Stats. This feature try to generate more optimal plan for query.

Adaptive feature in oracle 12c caused the performance issue in many platform. It increase the overhead of system to choose better plan during execution of Query which some time cause performance issue.

optimizer_adaptive_features default is True has a huge negative impact on the performance of database.

Example:

We got following sql statement from statspack report having lot of execution in database which caused the performance degradation in Oracle Database.
On checking on net, it is related with optimizer_adaptive_features (i.e adaptive features introduced in Oracle 12c)

SELECT executions, end_of_fetch_count
    ,elapsed_time / px_servers elapsed_time
    ,cpu_time / px_servers cpu_time
    ,buffer_gets / executions buffer_gets
  FROM
    ( SELECT
          SUM (executions) AS executions,SUM (
            CASE
              WHEN px_servers_executions > 0
              THEN px_servers_executions
              ELSE executions
            END
          ) AS px_servers 
          ,SUM (end_of_fetch_count) AS end_of_fetch_count
          ,SUM (elapsed_time) AS elapsed_time
          ,SUM (cpu_time) AS cpu_time
          ,SUM (buffer_gets) AS buffer_gets
        FROM
          gv$sql
        WHERE
          executions > 0
          AND sql_id = : 1
          AND parsing_schema_name = : 2

optimizer_adaptive_features feature is used for making the life of dba easy. So, that optimizer can choose the better plan and stats at run time but in few cases it have very negative impact of database. so you need to disable it by following parameters

alter system set optimizer_adaptive_features=false scope=both;

we can set this hidden parameter:

alter system set "_optimizer_dsdir_usage_control"=0 scope=both;

Dynamic Sampling is also caused performance issue, So, we can also disable with following parameter or you follow link for more detail: Dynamic Sampling

alter system set optimizer_dynamic_sampling=0 scope=both;

Advertisements

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 )

w

Connecting to %s

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