***********************SYS SESSION*************

SQL> show parameter optimiz

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------
optimizer_capture_sql_plan_baselines boolean                          FALSE
optimizer_dynamic_sampling           integer                          2
optimizer_features_enable            string                           11.2.0.1
optimizer_index_caching              integer                          80
optimizer_index_cost_adj             integer                          45
optimizer_mode                       string                           ALL_ROWS
optimizer_secure_view_merging        boolean                          TRUE
optimizer_use_invisible_indexes      boolean                          FALSE
optimizer_use_pending_statistics     boolean                          FALSE
optimizer_use_sql_plan_baselines     boolean                          TRUE
plsql_optimize_level                 integer                          2
SQL>

*************PIOTRTAL SESSION *************************

SQL> CREATE TABLE spm_test_tab (
  2    id           NUMBER,
  3    description  VARCHAR2(50)
  4  );

Table created.

SQL> DECLARE
  2    TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE;
  3    l_tab t_tab := t_TAB();
  4  BEGIN
  5    FOR i IN 1 .. 10000 LOOP
  6      l_tab.extend;
  7      l_tab(l_tab.last).id := i;
  8      l_tab(l_tab.last).description := 'Description for ' || i;
  9    END LOOP;
 10
 11    FORALL i IN l_tab.first .. l_tab.last
 12      INSERT INTO spm_test_tab VALUES l_tab(i);
 13
 14    COMMIT;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats('PIOTRTAL', 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace trace;
SQL> SELECT description
  2  FROM   spm_test_tab
  3  WHERE  id = 99;


Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462

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

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

   1 - filter("ID"=99)


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

SQL>

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

********* SYS SESSION ***************
SQL> SELECT sql_id
  2  FROM   v$sql
  3  WHERE  sql_text LIKE '%spm_test_tab%'
  4  AND    sql_text NOT LIKE '%dba_sql_plan_baselines%'
  5  AND    sql_text NOT LIKE '%EXPLAIN%';

SQL_ID
-------------
31w06ap2t45j6

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_plans_loaded  PLS_INTEGER;
  3  BEGIN
  4    l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  5      sql_id => '31w06ap2t45j6');
  6
  7    DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
  8  END;
  9  /
Plans Loaded: 1

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%spm_test_tab%'
  4  AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_116bdffe30629741       SQL_PLAN_12uyzzss655u1b65c37c8 YES YES

SQL> alter system flush shared_pool;

System altered.

SQL>

************** PIOTRTAL SESSION ************

SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats('PIOTRTAL', 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace trace
SQL> SELECT description
  2  FROM   spm_test_tab
  3  WHERE  id = 99;


Execution Plan
----------------------------------------------------------
Plan hash value: 3121206333

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    25 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    25 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("ID"=99)


Statistics
----------------------------------------------------------
       2421  recursive calls
         48  db block gets
        570  consistent gets
          2  physical reads
      15068  redo size
        545  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
   sql_id => '31w06ap2t45j6', plan_hash_value=> 1107868462);

  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

SELECT description
FROM   spm_test_tab
WHERE  id = 99;

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '',''));