Oracle 12c adaptive plans – sorry, I had to pass this time…and so did OEM13c

While troubleshooting performance problems on an Oracle 12.1.0.2.0 database, I had to disable the Oracle feature optimizer_adaptive_features, which is enabled by default.  While the whole concept of optimizer plans that can adapt on the fly to changing estimations sounds great, it is not a one-size-fits-all solution.  This is the story of one particular situation where it was not a good fit.

I have been having a good deal of fun lately trying to stay ahead of a vendor product that it seems was created to challenge all preconceived notions of performance tuning.  Some of my actions to stabilize this system include adding and removing SQL hints, performing hourly stats updates on key tables, and disabling the generation of histograms on key tables.  Like I said, lots of fun.  After a change, the performance would stabilize for awhile, then tank – which is a technical term for ‘go bad’.  I know one of the previous changes of hourly stats updates on the key tables was not a long-term solution, so I updated the stats once more, then locked the stats in place. The performance stayed stable for about a week, then it was back to performance complaints from the users.

This time, performance was poor across all rendered screens of the application this database was supporting and the load on the database server stayed in the realm of 20-30%. While this is not much, it is enough to cause user frustration when drop-down lists that populate in 1-3 seconds are suddenly taking 16 seconds.  I started by reviewing items in place that may affect performance:

–The automatic statistics generation job was still running nightly and had been from the start:

SELECT CLIENT_NAME, STATUS

FROM   DBA_AUTOTASK_CLIENT

WHERE  CLIENT_NAME = 'auto optimizer stats collection';

CLIENT_NAME                                                      STATUS

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

auto optimizer stats collection                                  ENABLED

--Checking to see if the last executions have been completing:

select operation||decode(target,null,null,'-'||target) operation,

to_char(start_time,'MM/DD/YYYY HH24:MI:SS') start_time,

to_char(  end_time,'MM/DD/YYYY HH24:MI:SS') end_time

from dba_optstat_operations

where operation like '%auto%'

order by start_time desc;

OPERATION                                START_TIME          END_TIME

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

gather_database_stats(auto)              09/17/2015 22:00:00 09/17/2015 22:03:25

gather_database_stats(auto)              09/16/2015 22:00:02 09/16/2015 22:07:13

gather_database_stats(auto)              09/15/2015 22:00:08 09/15/2015 22:07:57

gather_database_stats(auto)              09/14/2015 22:00:08 09/14/2015 22:06:17

This proves that the auto stats job has been operating far longer than the most recent performance problem, so I rule this out for the time being as the source of instability.

My next suspect comes courtesy of an examination of one of the queries reported as being slow:

PLAN_TABLE_OUTPUT

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

SQL_ID  c3kr9629gscpq, child number 0

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

SELECT QTYID, BEGDATE, ENDDATE, QTYTYPE, QTYSTATUS, QTYCLASSIFICATION

FROM CALCULATEDMEASUREDQTYVIEW WHERE ((((((QTYOBJECTTYPE = :1 ) AND

(QTYOBJECTID = :2 )) AND (FLOWDIRECTION = :3 )) AND (BEGDATE < :4 ))

AND (ENDDATE > :5 )) AND (MEASUREMENTBASIS IS NULL))

 

Plan hash value: 3194394658

 

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

| Id  | Operation                              | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                       |                                |       |       |    18 (100)|          |

|   1 |  NESTED LOOPS                          |                                |    54 |  4158 |    18   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                         |                                |   207 |  4158 |    18   (0)| 00:00:01 |

|   3 |    NESTED LOOPS                        |                                |     1 |    52 |     5   (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| MEASUREDQTY                    |     1 |    23 |     3   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN                  | IDX2_MEASUREDQTY               |     1 |       |     2   (0)| 00:00:01 |

|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| MEASUREDQTYCLASSIFICATION      |     1 |    29 |     2   (0)| 00:00:01 |

|*  7 |      INDEX RANGE SCAN                  | IDX5_MEASUREDQTYCLASSIFICATION |     1 |       |     1   (0)| 00:00:01 |

|*  8 |    INDEX RANGE SCAN                    | IDX5_CALCULATEDMEASUREDQTY     |   207 |       |     2   (0)| 00:00:01 |

|*  9 |   TABLE ACCESS BY INDEX ROWID          | CALCULATEDMEASUREDQTY          |    55 |  1375 |    13   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

5 - access("MQ"."QUANTITYOBJECTID"=:2 AND "MQ"."QUANTITYOBJECTTYPE"=:1 AND "MQ"."MEASUREMENTBASIS" IS NULL)

filter("MQ"."MEASUREMENTBASIS" IS NULL)

7 - access("MQC"."QUANTITY_ID"="MQ"."QUANTITY_ID" AND "MQC"."FLOWDIRECTION"=:3)

filter("MQC"."FLOWDIRECTION"=:3)

8 - access("CMQ"."QTYCLASSIFICATION_ID"="MQC"."QTYCLASSIFICATION_ID")

9 - filter(("CMQ"."ENDDATE">:5 AND "CMQ"."BEGDATE"<:4))

 

Note

-----

- this is an adaptive plan

According to the note in this explain table output, this plan is designated as an ‘adaptive plan’, which means the execution plan changed after the first execution.  In a nutshell, the Oracle 12c adaptive feature takes a sampling of the data at query run time after the first execution if the cardinality is significantly different than the optimizer estimation.  If so, the query is halted and new joins are imposed to try to correct the cardinality.  More detailed information about these adaptive features can be found at http://kerryosborne.oracle-guy.com/papers/12c_Adaptive_Optimization.pdf.

I am unsure how the locking of table stats affects the adaptive feature, but it still introduces an element of instability.  Disabling the optimizer_adaptive_features brought the load on the database server down almost immediately and allowed the system to return to an acceptable level of performance.

I realize that this is not definitive proof that adaptive query optimization has a problem with locked table stats, but it is my hypothesis that it may introduce performance instability.  I will need to update this blog once I can find another example.

12/21/15 Update –

Oracle released Oracle Enterprise Manager 13c release 1 on 12/18/15.  In the basic installation document, one of the prerequisites when installing OEM13c using Oracle database 12c as the Oracle Management Repository (OMS) is that the optimizer_adaptive_features be set to FALSE, disabling this feature.  See section 4.4 of this Oracle document – http://docs.oracle.com/cd/E63000_01/EMUPG/prerequisites.htm#CIHECFHD

10/3/16 Update –

My Oracle Support (MOS) note 2187449.1 states that in version 12.2, the parameter optimizer_adaptive_features has been obsoleted in favor of two new parameters – optimizer_adaptive_plans and optimizer_adaptive_statistics.  The first parameter controls whether the optimizer creates adaptive plans and defaults to TRUE.  The second parameter controls whether the optimizer uses adaptive statistics and defaults to FALSE.  According to the note, these defaults have been chosen to place emphasis on achieving stable SQL execution plans.

 

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s