Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE

From: dombrooks <dombrooks_at_hotmail.com>
Date: Wed, 12 Oct 2011 00:52:34 -0700 (PDT)
Message-ID: <27ecaf33-11eb-46e2-a0c9-21a3b2623135_at_g23g2000vbz.googlegroups.com>

I don't have 11.2.0.3 yet, but can confirm that the approach works in 11.2.0.2.

See demo below.

SQL> select * from v$version;

BANNER

---

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production

SQL>
SQL> create table t1
  2 (col1 number
  3 ,constraint pk_t1 primary key (col1));

Table created.

SQL>
SQL> select /*+ index(t1 pk_t1) */
  2 *
  3 from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

---

SQL_ID an6t9h9g5s3vh, child number 0

---

select /*+ index(t1 pk_t1) */ * from t1

Plan hash value: 646159151

---

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


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

|   0 | SELECT STATEMENT |       |       |       |     2
(100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |     1 |    13 |     2   (0)|

00:00:01 |

---

Note

---

• dynamic sampling used for this statement (level=4)

17 rows selected.

SQL>
SQL> select * from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

---

SQL_ID 27uhu2q2xuu7r, child number 0

---

select * from t1

Plan hash value: 3617692013

---

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


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

|   0 | SELECT STATEMENT  |      |       |       |     2
(100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)|

00:00:01 |

---

Note

---

• dynamic sampling used for this statement (level=4)

17 rows selected.

SQL> declare
  2 sqltext clob;
  3 spm_op pls_integer;
  4 begin
  5 sqltext := 'select * from t1';
  6 spm_op :=
  7 dbms_spm.load_plans_from_cursor_cache   8 (sql_id => 'an6t9h9g5s3vh',
  9 plan_hash_value => 646159151,
 10 sql_text => sqltext);
 11 end;
 12 /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

---

SQL_ID 27uhu2q2xuu7r, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 27uhu2q2xuu7r, CHILD_NUMBER: 1

      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache

(check v$sql_plan)

8 rows selected.

SQL> select * from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

---

SQL_ID 27uhu2q2xuu7r, child number 1

---

select * from t1

Plan hash value: 646159151

---

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


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

|   0 | SELECT STATEMENT |       |       |       |    26
(100)|          |

| 1 | INDEX FULL SCAN | PK_T1 | 82 | 1066 | 26 (0)| 00:00:01 |

---

Note

---

• SQL plan baseline SQL_PLAN_6x6k5dhdcczn6690169bf used for this statement

17 rows selected.

SQL> Received on Wed Oct 12 2011 - 02:52:34 CDT