迁移baseline

你的名字 2022-07-12 04:19 182阅读 0赞

生成baseline  
DECLARE  
  ret PLS\_INTEGER;  
BEGIN  
  ret:=dbms\_spm.load\_plans\_from\_cursor\_cache(sql\_id=>'093bjyjkc76ry',plan\_hash\_value=>4194351669,fixed=>'YES');  
  dbms\_output.put\_line(ret || ' SQL plan baseline(s) created');  
END;  
  
  
\--- 源端DB和目标DB都要执行  
BEGIN  
DBMS\_SPM.CREATE\_STGTAB\_BASELINE(  
   table\_name      =>'spm\_stageing\_tab',  
   table\_owner     => 'TP',  
   tablespace\_name => 'TP');  
END;  
  
\-- 源DB的执行  
DECLARE  
  l\_plans\_packed  PLS\_INTEGER;  
BEGIN  
  l\_plans\_packed := DBMS\_SPM.pack\_stgtab\_baseline(  
  table\_name      =>'spm\_stageing\_tab',  
  table\_owner     => 'TP');  
  DBMS\_OUTPUT.put\_line('Plans Packed: ' || l\_plans\_packed);  
END;  
  
expdp sysem/xxx tables=TP.spm\_stageing\_tab directory=DUMP\_DIR content=data\_only dumpfile=spm\_stageing\_tab.dmp logfile=spm\_stageing\_tab.expdp.log  
  
\-- 目标DB执行  
impdp system/xxx tables=TP.spm\_stageing\_tab directory=DUMP\_DIR content=data\_only dumpfile=spm\_stageing\_tab.dmp logfile=spm\_stageing\_tab.impdp.log  
  
\-- unpack sql\_plan\_baselines  
DECLARE  
  l\_plans\_unpacked  PLS\_INTEGER;  
BEGIN  
  l\_plans\_unpacked := DBMS\_SPM.unpack\_stgtab\_baseline(  
  table\_name      =>'spm\_stageing\_tab',  
  table\_owner     => 'TP');  
  DBMS\_OUTPUT.put\_line('Plans Unpacked: ' || l\_plans\_unpacked);  
END;  
  
\-----baseline迁移结束  
  
\-- drop sql\_plan\_baseline  
DECLARE  
  l\_plans\_dropped  PLS\_INTEGER;  
BEGIN  
  l\_plans\_dropped := DBMS\_SPM.drop\_sql\_plan\_baseline (  
  sql\_handle => 'SQL\_2ac181fb3c1a89ca',  
  plan\_name  => 'SQL\_PLAN\_2phc1zcy1p2fa34b6538d');  
  DBMS\_OUTPUT.put\_line(l\_plans\_dropped);  
END;  
  
DECLARE  
  l\_plans\_dropped  PLS\_INTEGER;  
  cur\_handle       sys\_refcursor;  
  v\_handle         varchar2(100);  
BEGIN  
  open cur\_handle for select distinct sql\_handle from dba\_sql\_plan\_baselines;  
  fetch cur\_handle into v\_handle;  
  while cur\_handle%found loop  
    l\_plans\_dropped := DBMS\_SPM.drop\_sql\_plan\_baseline (sql\_handle => v\_handle);  
    DBMS\_OUTPUT.put\_line(l\_plans\_dropped);  
    fetch cur\_handle into v\_handle;  
  end loop;  
  close cur\_handle;  
END;