db version: 12.1.0.2.0

rdbms home: /paic/dev/oracle/rdbms/12c/12.1.0.2.170418

we used sql plan management in our production database, recently we found when a plan become unproduced such as index was dropped, the value of dba_sql_plan_baselines.reproduced can't update to 'NO'.

I also tested the scenario in testing environment, for one simple sql, I generated a full table scan plan and a index scan plan in sql plan baseline, after that, I dropped the index, so the index scan plan should not be reproduced, but the value of dba_sql_plan_baselines.reproduced still is 'YES', seems it will never be update to 'NO'. so I want to ask why the value will not update while actually the plan is not reproduced? and when will oracle update the value to 'NO', and how?

updated my testing scripts in attachment?

-----------------------------------------testting scripts---------------------------

--spm parameter
SQL> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_sql_plan_directive_mgmt_control     integer     0
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>
SQL>

-----create test table
create table my_objects as select * from dba_objects;

SQL>
SQL> create table my_objects as select * from dba_objects;

Table created.

SQL>

------create index
create index idx1_my_objects on my_objects(object_id);

SQL> create index idx1_my_objects on my_objects(object_id);

Index created.

-----gather table stats
exec dbms_stats.gather_table_stats('DEVMGR','MY_OBJECTS',cascade => true);

SQL>
SQL> exec dbms_stats.gather_table_stats('DEVMGR','MY_OBJECTS',cascade => true);

PL/SQL procedure successfully completed.

SQL>

-----run query sql, we can see with index scan execution plan

select /*SMP_QUESTION*/  object_id from my_objects where object_id=88;

SQL> set autotrace on
SQL> select /*SMP_QUESTION*/  object_id from my_objects where object_id=88;

OBJECT_ID
----------
        88

Execution Plan
----------------------------------------------------------
Plan hash value: 3367122391

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

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

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

|   0 | SELECT STATEMENT |                 |     1 |     5 |     1   (0)| 00:00:
01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"=88)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

-----load the plan into spm
SQL> set autotrace off
SQL> SELECT SQL_ID,PLAN_HASH_VALUE FROM V$SQL WHERE SQL_TEXT LIKE '%SMP_QUESTION%' and plan_hash_value=3367122391 and sql_text not like '%EXPLAIN PLAN%';

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
4zjugzdm6aus2      3367122391

SQL>

--before load,no baseline
SQL> SELECT sql_handle, plan_name,enabled, accepted ,fixed,reproduced FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%SMP_QUESTION%';

no rows selected

SQL>

--load baseline
declare
v_load_plan_number number;
begin
v_load_plan_number:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'4zjugzdm6aus2',PLAN_HASH_VALUE=>'3367122391');
end;
/

SQL>
SQL> declare
  2  v_load_plan_number number;
  3  begin
  4  v_load_plan_number:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'4zjugzdm6aus2',PLAN_HASH_VALUE=>'3367122391');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL>

--after load，plan enabled=YES,accepted=YES,fixed=NO,reproduced=YES
SELECT sql_handle, plan_name,enabled, accepted ,fixed,reproduced FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%SMP_QUESTION%';

SQL> set linesize 300
SQL> col sql_handle for a30
SQL> col plan_name for a40
SQL> SELECT sql_handle, plan_name,enabled, accepted ,fixed,reproduced FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%SMP_QUESTION%';

SQL_HANDLE                     PLAN_NAME                                ENA ACC FIX REP
------------------------------ ---------------------------------------- --- --- --- ---
SQL_d4456e0393f81c5e           SQL_PLAN_d8jbf0f9zh72yeaf2dc42           YES YES NO  YES

SQL>

--with spm plan SQL_PLAN_d8jbf0f9zh72yeaf2dc42,and it's ACCEPTED，so when run the query sql again, we can see used the spm plan
SQL> set autotrace on
SQL> select /*SMP_QUESTION*/  object_id from my_objects where object_id=88;

OBJECT_ID
----------
        88

Execution Plan
----------------------------------------------------------
Plan hash value: 3367122391

------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX1_MY_OBJECTS |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"=88)

Note
-----
   - SQL plan baseline "SQL_PLAN_d8jbf0f9zh72yeaf2dc42" used for this statement

Statistics
----------------------------------------------------------
         27  recursive calls
         18  db block gets
         12  consistent gets
          0  physical reads
       5488  redo size
        543  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

---then I drop the index
drop index IDX1_MY_OBJECTS;
SQL> set autotrace off
SQL> drop index IDX1_MY_OBJECTS;

Index dropped.

SQL>

--after index dropped, the query go full table scan, and will capture full table scan plan into spm

SQL> set autotrace on
SQL> select /*SMP_QUESTION*/  object_id from my_objects where object_id=88;

OBJECT_ID
----------
        88

Execution Plan
----------------------------------------------------------
Plan hash value: 880823944

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     5 |   421   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |     1 |     5 |   421   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=88)

Statistics
----------------------------------------------------------
        211  recursive calls
         40  db block gets
       1685  consistent gets
          1  physical reads
       8116  redo size
        543  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

---the index has dropped, but when we view DBA_SQL_PLAN_BASELINES, the value of reproduced is still YES, why can't update to 'NO'?

SQL> set autotrace off
SQL> SELECT sql_handle, plan_name,enabled, accepted ,fixed,reproduced FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%SMP_QUESTION%';

SQL_HANDLE                     PLAN_NAME                                ENA ACC FIX REP
------------------------------ ---------------------------------------- --- --- --- ---
SQL_d4456e0393f81c5e           SQL_PLAN_d8jbf0f9zh72y89405218           YES NO  NO  YES
SQL_d4456e0393f81c5e           SQL_PLAN_d8jbf0f9zh72yeaf2dc42           YES YES NO  YES

SQL>