星期三, 2月 03, 2016

[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


沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...