Oracle Explain Plan

https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231

Create explain plan table

@$ORACLE_HOME/rdbms/admin/utlxplan.sql 
select table_name from all_tables where table_name like '%PLAN%';

Move existing and recreation

rename plan_table to plan_table_test;
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 

Explain Plan

explain plan 
set statement_id = 'REQUEST_01_STATEMENT' 
into plan_table_test 
for YOUR REQUEST_01

Did something if result > 0

select count(*) from plan_table_test;

Visual display

select * from table(dbms_xplan.display('plan_table_test', 'REQUEST_01_STATEMENT','BASIC'));

Less visual but can where this

select id,parent_id,operation,options,object_name,object_type from plan_table_test where statement_id = 'REQUEST_01_STATEMENT';

Require DBA

select * from V$SQL_PLAN;

Using DBMS_SPM (DBA & EE required)

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_spm.htm

This procedure creates a staging table that will be used for the purpose of transporting SQL plan baselines from one system to another.

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name => 'plan_baseline',
	  table_owner => 'database_owner');
END;
/

Just in case

drop table database.plan_baseline

This function packs (exports) SQL plan baselines from SQL management base into a staging table.

DECLARE
my_plans number;
BEGIN
  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'plan_baseline',
	table_owner => 'database',
    enabled => 'yes',
    creator => 'database');
END;
/

Some intel

select * from dba_sql_plan_baselines;
select * from database.plan_baseline;

Import (unpack) database.plan_baseline onto the new database

DECLARE
my_plans number;
BEGIN
  my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
    table_name => 'database.plan_baseline',
    fixed => 'yes');
END;
/