Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 12 Oct 2011 20:51:51 +0000 (UTC)
Message-ID: <pan.2011.10.12.20.51.51_at_gmail.com>

On Wed, 12 Oct 2011 12:40:02 -0700, dombrooks wrote:

> For the target statement, you don't need anything else but the sql text.
> No sql id. Nothing.
> Because the sql text is run through something like
> dbms_sqltune.sqltext_to_signature.
> Signature is the key for baselines - no sql id, no hash value, etc.

It works. Thanks for helping me out with that one. I was using SQL_ID and text for the target statement and plan for the source (hinted) statement.

SQL> declare
  2 v_sql_text CLOB :=
  3 'select count(*) from big_table where first_char=''Z''';   4 v_sql_id varchar2(13) := '9h20pt9r130ck';   5 v_hash number := 599409829;
  6 plans_loaded number;
  7 begin

  8  	 plans_loaded:=dbms_spm.load_plans_from_cursor_cache(
  9  	     sql_id => v_sql_id,
 10  	     plan_hash_value=>v_hash,
 11  	     sql_text => v_sql_text);
 12  	 dbms_output.put_line('Loaded '||plans_loaded||' plans.');

 13 end;
 14 /
Loaded 1 plans.

PL/SQL procedure successfully completed.

Here is what the dbms_xplan,display_sql_plan_baseline says:

PLAN_TABLE_OUTPUT

---

---

SQL handle: SQL_de2fd4b7f0ff1d09
SQL text: select count(*) from big_table where first_char='Z'

---

---

Plan name: SQL_PLAN_dwbynqzsgy789c74815fa	  Plan id: 3343390202
Enabled: YES	 Fixed: NO	Accepted: YES	  Origin: MANUAL-LOAD


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

Plan hash value: 599409829

---

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


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

|   0 | SELECT STATEMENT   |	       |     1 |     1 |    66	 (8)| 
00:00:01 |
|   1 |  SORT AGGREGATE    |	       |     1 |     1 |	    
|	       |
|*  2 |   TABLE ACCESS FULL| BIG_TABLE |     2 |     2 |    66	 (8)| 

00:00:01 |

---

Predicate Information (identified by operation id):

---

   2 - filter("FIRST_CHAR"='Z')

25 rows selected.

Elapsed: 00:00:00.08
SQL> Subsequent execution confirms that the right baseline is used: SQL> select count(*) from big_table where first_char='Z';

  COUNT(*)

---

         2

Elapsed: 00:00:00.06

Execution Plan

---

Plan hash value: 599409829

---

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


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

|   0 | SELECT STATEMENT   |	       |     1 |     1 |    66	 (8)| 
00:00:01 |
|   1 |  SORT AGGREGATE    |	       |     1 |     1 |	    
|	       |
|*  2 |   TABLE ACCESS FULL| BIG_TABLE |     2 |     2 |    66	 (8)| 

00:00:01 |

---

Predicate Information (identified by operation id):

---

   2 - filter("FIRST_CHAR"='Z')

Note

---

• SQL plan baseline "SQL_PLAN_dwbynqzsgy789c74815fa" used for this statement

SQL> Thanks again for straightening me up. OK, now I know the trick.

-- 
http://mgogala.byethost5.com

Received on Wed Oct 12 2011 - 15:51:51 CDT