Adaptive plans can be run in reporting mode by setting the parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY to TRUE.   When this is set, the affected SQL plans can be reported through the SQL

SQL> SELECT * FROM table  (DBMS_XPLAN.DISPLAY_CURSOR(format=> ‘+report’));

Adaptive Parallel Distribution:  In a parallel processing mode, the distribution of rows to a parallel query slave can be sub-optimal keeping some of the slaves underutilized degrading the performance of the overall parallel processing.  As part of the adaptive optimization, oracle introduced HYBRID HASH distribution method which defer the rows distribution until execution and take a decision based on the number of records in a row source.  Means the distribution can switch between HASH to BROADCAST or RANDOM etc.


Oracle memory Guard:  Detects memory pressure in a node and re-direct the incoming sessions to the other available instances.


Oracle Memory management is part of QoS (Quality of Service) Management for RAC which identifies run time performance metrics and allocate services with maximum availability.


QoS management architecture


Note: From Oracle onwards Memory Guard is integrated with Grid Infrastructure and no need to enable QoS.

The heart of the QoS Management is the Policy Set, consists of two components

  • Server pools
  • Performance classes
  • Performance policies


PQ_DISTRIBUTE hint can be used direct the communication between QC and slaves.  There are multiple options and they are –

  • PQ_DISTRIBUTE(<table name>, none)
  • PQ_DISTRIBUTE(<table name>, PARTITION)
  • PQ_DISTRIBUTE(<table name>, RANDOM)


Oracle 12c identifies alternate plans based on the row source cardinality.  You can enable disable this feature by setting the parameter by OPTIMIZER_ADAPTIVE_REPORTING_ONLY to TRUE and by default it is FALSE.

A new column IS_RESOLVED_ADAPTIVE_PLAN added to the V$SQL view to report any sql is used the new feature.  The column value can be Y/N/none.

Adaptive optimization got two components

  • Adaptive Plans
  • Adaptive statistics

OPTIMIZER_ADAPTIVE_FEATURES (TRUE/FALSE) parameter controls the overall functioning of this feature.


  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: