Oracle SQL Plan Baseline

Oracle SPM


select * from DBA_SQL_PLAN_BASELINEs --where ACCEPTED='YES'

Steps:

-- load baseline from sql cache

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  sql_id => 'dxt4r9mp8wvx9');
  DBMS_OUTPUT.PUT_LINE('dropped: '||l_plans_loaded);
END;
/

-- get plan from base line plan name
select * from TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_5jbmma7s7tv2h17fb8113'));

---- drop a plan from baseline 

DECLARE

  l_plans_dropped  PLS_INTEGER;

BEGIN

  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

    sql_handle => NULL,

    plan_name  => 'SQL_PLAN_5jbmma7s7tv2h17fb8113');

    

  DBMS_OUTPUT.put_line('dropped: ' || l_plans_dropped);

END;

/

--- Export & Import (http://expertoracle.com/2016/05/17/move-sql-baseline-to-other-instance/)
-- 1. create staging table
DECLARE
  stgtbl number;
BEGIN
  stgtbl := DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage2');
  DBMS_OUTPUT.PUT_LINE('stage:=' || stgtbl);
END;
/

-- 2. pack SQL plan baselines
DECLARE
  my_plans number;
BEGIN
  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'stage2',
    enabled => 'yes',
    ACCEPTED => 'yes',
    SQL_HANDLE => 'SQL_58ae7351f07cec50');
    DBMS_OUTPUT.PUT_LINE('packed: '|| my_plans);
END;
/

-- 3. export staging table
expdp sysadm tables=stage2 directory=DPDIR_FSSBX dumpfile=stage2.exp.dmp logfile=stage2.exp.log compression=all

-- 4. import stage into new DB
impdp sysadm file=stage2.dmp tables=stage2 directory=DPDIR_MYDB logfile=stage2.imp. log

-- unpack plan baselines
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'STAGE2',
    table_owner     => 'SYSADM',
    creator         => 'SYSADM');
DBMS_OUTPUT.put_line('Unpacked: ' || l_plans_unpacked);
END;
/

related Oracle parameters:

1. SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
2. select name, value from  gV$SES_OPTIMIZER_ENV where sid=24 and upper(name)='OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES';
3. select * from gv$parameter where upper(name) in select * from gv$parameter where upper(name) in ('OPTIMIZER_USE_SQL_PLAN_BASELINES', 'OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES')

Performance Qeries