Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Category : Home > Databases > Oracle > Performance Tuning     

Copy SQL plan baseline into another database


-----------------
-- Source database
-----------------
sqlplus /


VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '3x2bacus9sf6x',
PLAN_HASH_VALUE    => '3966657152');

-- queries DBA_SQL_PLAN_BASELINES
select * from DBA_SQL_PLAN_BASELINES;

-- Run below if table not exists
exec DBMS_SPM.create_stgtab_baseline(TABLE_OWNER=>'DBAUSER', TABLE_NAME=>'STGTAB_BASELINE',
TABLESPACE_NAME=>'USERS');

-- Check staging table BEFORE packing baseline
select count(*) from DBAUSER.STGTAB_BASELINE;

-- CREATOR is the user who created the baseline in above step
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.PACK_STGTAB_BASELINE(TABLE_OWNER=>'DBAUSER',
TABLE_NAME=>'STGTAB_BASELINE', ENABLED=>'YES', CREATOR=>'OPS$ORACLE');

-- Check staging table AFTER packing baseline, should be one more record
select count(*) from DBAUSER.STGTAB_BASELINE;


-----------------
-- Target database 
-----------------
-- Run below if table not exists
exec DBMS_SPM.create_stgtab_baseline(TABLE_OWNER=>'DBAUSER', TABLE_NAME=>'STGTAB_BASELINE',
TABLESPACE_NAME=>'USERS');

-- Create database link to source database if not exists
CREATE DATABASE LINK "SOURCEDB" USING 'SOURCEDB' ;

--
-- Run below as user who owns the db link
--
select count(*) from DBAUSER.STGTAB_BASELINE@SOURCEDB;

select count(*) from DBAUSER.STGTAB_BASELINE;

insert into DBAUSER.STGTAB_BASELINE select * from DBAUSER.STGTAB_BASELINE@SOURCEDB;

commit;


VARIABLE cnt NUMBER
EXECUTE :cnt :=DBMS_SPM.UNPACK_STGTAB_BASELINE(TABLE_OWNER=>'DBAUSER',
TABLE_NAME=>'STGTAB_BASELINE', ENABLED=>'YES', CREATOR=>'OPS$ORACLE');


-- queries DBA_SQL_PLAN_BASELINES
select * from DBA_SQL_PLAN_BASELINES;

--
-- To drop baseline:
--
-- VARIABLE cnt NUMBER
-- exec :cnt := DBMS_SPM.drop_sql_plan_baseline( sql_handle=>'SQL_7bdb07c74cf9330b' );

-- check that baseline is being used
alter system flush shared_pool;
select inst_id, sql_id, PLAN_HASH_VALUE, sql_plan_baseline, executions from gv$sql where
sql_id = '3x2bacus9sf6x';



  Options
 
   del.icio.us  |  newsvine  |  digg  |  furl  |  google  |  yahoo  |  Ma.gnolia  |  vigillar  |  reddit  |  technorati  |  icerocket  |  pubsub

       Rate this tip:    

Start discussion or add comment to this tip

  Details
Tip reference : #311
views : 948
Added on : 05/26/16
Submited by : h8dk97
 
Send a message Send a message Printer friendly output Printer friendly output
Display this member's tips Display this member's tips (219)
 
 
Next >>
 Most viewed tips 
  Databases > Oracle > Security : How to unlock Oracle user account  
  Databases > Oracle > Performance Tuning : How to enable trace in Oracle  
  Databases > Oracle > DR : Adding new datafiles to standby database  
  Operating Systems > Unix : How to kill Unix user session  
   
  All categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com