DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE typo

I've played a little with Oracle SQL Plan Management and something caught my eye.

Let's create simple test.

SQL> select /* test_spm */ count(*) from admin.objekti;

  COUNT(*)
----------
     76378

SQL> set lines 200
SQL> col sql_text for a50 wrapped
SQL> select sql_id, sql_text from v$sql
  2  where sql_text like '%test_spm%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------
cpzgdw9swdvzk select /* test_spm */ count(*) from admin.objekti
ap9td7vafq26n select sql_id, sql_text from v$sql where sql_text
              like '%test_spm%'

I will try to load plan from the cursor cache using SQL_ID.

SQL> var cnt number
SQL> execute :cnt := DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE(sql_id => 'cpzgdw9swdvzk');
BEGIN :cnt := DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE(sql_id => 'cpzgdw9swdvzk'); END;

                       *
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00302: component 'LOAD_PLAN_FROM_CURSOR_CACHE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Hm... PLS-00302 error when I use LOAD_PLAN_FROM_CURSOR_CACHE.

Change function name and use PLANS instead of PLAN.

SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'cpzgdw9swdvzk');

PL/SQL procedure successfully completed.

SQL> print cnt

       CNT
----------
         1

Now everything works as it should.

It is well written in documentation to use PLANS:

LOAD_PLANS_FROM_CURSOR_CACHE Functions

This function loads one or more plans present in the cursor cache for a SQL statement, or a set of SQL statements. It has four overloads: using SQL statement text, using SQL handle, using SQL ID, or using attribute_name and attribute_value pair.

But, try to search LOAD_PLAN_FROM_CURSOR_CACHE on Google.

There are many sites mentioning that procedure.

Even in Oracle documentation - "Oracle® Database Upgrade Guide 12c Release 1 (12.1)" (and earlier releases) this procedure is mentioned:

1. In the source release of Oracle Database, use the DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE procedure or Oracle Enterprise Manager to load all of the execution plans in the cursor cache into the SQL Management Base.

Now I'm a bit confused.

Let's check procedure names on three database releases.


12.1.0.1.0

SQL> select procedure_name from dba_procedures
  2  where procedure_name like 'LOAD_PLAN%';

PROCEDURE_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOAD_PLANS_SET
LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE

6 rows selected.

11.2.0.4.0

SQL> select procedure_name from dba_procedures
  2  where procedure_name like 'LOAD_PLAN%';

PROCEDURE_NAME
------------------------------
LOAD_PLANS_SET
LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE

6 rows selected.

11.1.0.7.0

SQL> select procedure_name from dba_procedures
  2  where procedure_name like 'LOAD_PLAN%';

PROCEDURE_NAME
------------------------------
LOAD_PLANS_SET
LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE

6 rows selected.

Still not a sign about LOAD_PLAN_FROM_CURSOR_CACHE or maybe I'm looking in wrong direction.

At the end I think that this is just often used typo.