[Oracle] 如何從cursor import sql 至baseline.

P.S. 從cursor import sql , 進來之後, SQL的狀態就會是enabled , accepted狀態了

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%'

select *

from table(dbms_xplan.display_cursor(sql_id => 'ap9td7vafq26n'));

variable cnt number;

execute :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'ap9td7vafq26n');

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED

from dba_sql_plan_baselines

WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

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

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO

SET SERVEROUTPUT ON

DECLARE

  l_plans_altered  PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',

    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',

    attribute_name  => 'ENABLED',

    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

ref:
http://blog.xuite.net/zerofirst/blog/205592015-Oracle+11G%E6%96%B0%E5%8A%9F%E8%83%BD+-+SPM+%E4%BB%8B%E7%B4%B9


執行計畫的演化指Plan History裡的執行計畫從NON-ACCEPTED，變成ACCEPTED的過程。如果上所述，由於ACS和Bind Peeking的作用，存在baseline的SQL有可能生成新的執行計畫，被保存到Plan History中。 Oracle提供了API，通過自動或手工的方式，將一個計畫標記為ACCEPTED，這個計畫就會被後續的執行所選擇。

後記:
SQL Plan Management支援以下幾個導入方式

批量導入 (這些導入的baseline都會被自動標記為ACCEPTED)
   Oralce提供四種方式把計畫導入到sql plan baseline中。
   + 從 SQL Tuning Set STS 導入
      DBMS_SPM.LOAD_PLANS_FROM_SQLSET
   + 從Stored Outlines 中導入
      DBMS_SPM.MIGRATE_STORED_OUTLINE
   + 從記憶體中存在的計畫中導入
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;
   + 通過staging table從另外一個系統中移植
      DBMS_SPM.CREATE_STGTAB_BASELINE
      DBMS_SPM.PACK_STGTAB_BASELINE
      DBMS_SPM.UNPACK_STGTAB_BASELINE

ref:
https://blogs.oracle.com/optimizer/sql-plan-management-part-1-of-4-creating-sql-plan-baselines
https://oracle-base.com/articles/11g/sql-plan-management-11gr1
https://blogs.oracle.com/database4cn/oracle-11g-sql-sql-plan-management