You can use the V$SQL.IS_RESOLVED_ADAPTIVE_PLAN column to know whether the execution plan associated to a child cursor is adaptive or not. Specifically, to know whether the query optimizer selected either an adaptive join method or an adaptive star transformation (notice that it is not set when the hybrid hash distribution is involved).

The column takes one of the following values:

  • NULL: the execution plan associated to the cursor is not adaptive
  • N: the execution plan is adaptive but the final execution plan is not yet determined
  • Y: the execution plan is adaptive and the final execution plan was determined

If you are interested in such an information for a SQL statement that is neither cached in the library cache nor present in AWR, as of version 12.1.0.2 you might want to check the SQL_ADAPTIVE_PLAN_RESOLVED column provided by the views V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY. In fact, according to the documentation, it provides the following information:

Indicates whether the SQL plan of the sampled database session is a resolved adaptive plan or not

Even though the documentation provides no information about the valid values for that column, given that according to my observations only the values “0” and “1” are used, I conclude that adaptive plans should be marked with the value “1”.

Unfortunately, this is not the case. In fact, also executions that are not using an adaptive plan are marked with the value “1”!

I reproduced the issue with the following test case (notice that even though the OPTIMIZER_ADAPTIVE_PLANS parameter is set to FALSE and the cursor has the IS_RESOLVED_ADAPTIVE_PLAN column set to NULL, according to the SQL_ADAPTIVE_PLAN_RESOLVED column an execution involving an adaptive plan took place):

SQL> CREATE TABLE t1 (id PRIMARY KEY, n, pad)
  2  AS
  3  SELECT rownum, CASE WHEN rownum < = 10000 THEN rownum ELSE 666 END, lpad('*',100,'*')
  4  FROM dual
  5  CONNECT BY level <= 10150;
 
SQL> execute dbms_stats.gather_table_stats(user,'t1')
 
SQL> CREATE TABLE t2 (id PRIMARY KEY, n, pad)
  2  AS
  3  SELECT rownum, rownum, lpad('*',100,'*')
  4  FROM dual
  5  CONNECT BY level < = 10000;
 
SQL> execute dbms_stats.gather_table_stats(user,'t2')
 
SQL> CREATE OR REPLACE FUNCTION burn_cpu(p_wait IN INTEGER) RETURN INTEGER IS
  2    l_begin NUMBER;
  3  BEGIN
  4    l_begin := dbms_utility.get_time();
  5    LOOP
  6      EXIT WHEN l_begin+(p_wait*100) < dbms_utility.get_time();
  7    END LOOP;
  8    RETURN 1;
  9  END burn_cpu;
 10  /
 
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
 
SQL> show parameter optimizer_adaptive
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_plans             boolean     FALSE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_adaptive_statistics        boolean     FALSE
 
SQL> SELECT count(t1.pad), count(t2.pad)
  2  FROM t1, t2
  3  WHERE t1.id = t2.id
  4  AND t1.n = 666
  5  AND burn_cpu(t1.id/t1.id) = 1;
 
COUNT(T1.PAD) COUNT(T2.PAD)
------------- -------------
            1             1
 
SQL> SELECT sql_id, child_number, is_resolved_adaptive_plan, elapsed_time
  2  FROM v$sql
  3  WHERE (sql_id, child_number) = (SELECT prev_sql_id, prev_child_number
  4                                  FROM v$session
  5                                  WHERE sid = sys_context('userenv','sid'));
 
SQL_ID        CHILD_NUMBER IS_RESOLVED_ADAPTIVE_PLAN ELAPSED_TIME
------------- ------------ ------------------------- ------------
8ygb8f7cycp4f            0                              152687501
 
SQL> SELECT sql_id, sql_adaptive_plan_resolved, count(*)
  2  FROM v$active_session_history
  3  WHERE session_id = sys_context('userenv','sid')
  4  AND sample_time > systimestamp - INTERVAL '150' SECOND
  5  GROUP BY sql_id, sql_adaptive_plan_resolved;
 
SQL_ID        SQL_ADAPTIVE_PLAN_RESOLVED   COUNT(*)
------------- -------------------------- ----------
8ygb8f7cycp4f                          1        150

Even though I still have to open a service request, in my opinion this is a bug.

UPDATE 2018-01-10: it’s interesting to point out that the V$SQL_MONITOR view shows the correct information:

SQL> SELECT sql_id, is_adaptive_plan
  2  FROM v$sql_monitor
  3  WHERE sid = sys_context('userenv','sid');
 
SQL_ID        IS_ADAPTIVE_PLAN
------------- ----------------
8ygb8f7cycp4f N

UPDATE 2018-06-05: after almost 6 months, today I closed the service request. Through it I got the following information from development:

SQL_ADAPTIVE_PLAN_RESOLVED=0 only while the SQL plan is currently being adapted — once it is adapted, SQL_ADAPTIVE_PLAN_RESOLVED=1. If the plan adaption is disabled, it SQL_ADAPTIVE_PLAN_RESOVLED will always = 1 because the SQL plan is never in adaption.

Hence, what I describe in this blog post is a feature, not a bug! But, for what is worth, in my opinion it’s a bad implementation. In fact, because of that definition, we can’t know whether a plan was adaptive by looking at ASH. That isn’t a major problem, but a missed opportunity to show that information as well as an inconsistency with other dynamic performance views.