星期三, 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,這個計畫就會被後續的執行所選擇。

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...