SQL Plan Management handy commands

I have a blog about SPM previously. Now I want to introduce a set of handy commands to operate SPM.

The script is ksh script. Here is the summary commands:

 ora spm
  - spmlike <pattern>                 List SQL Plan Baselines by matching sql_text to pattern
  - spmlist <sql_handle/hash_value> [plan_name]             List SQL Plan Baselines for giving SQL
  - spmshow <sql_handle/hash_value> [plan_name]             Show SQL Plan Baselines for a given SQL
  - spmload <hash_value> [plan_hash_value]             Load itself's sql plan from cursor cache for a given hash_value
  - spmbind <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Bind <good_hash_value>'s plan to <sql_handle/hash_value>
  - spmloadgood <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Load <good_hash_value>'s plan to <sql_handle/hash_value>
  - spmalter <sql_handle/hash_value> <attribute_name> <attribute_value> [plan_name]     Change SQL Plan Baseline attribute
  - spmdisable <sql_handle/hash_value> [plan_name]             Disable the Plan
  - spmenable <sql_handle/hash_value> [plan_name]             Enable the Plan
  - spmfix <sql_handle/hash_value> [plan_name]             Fix the Plan
  - spmunfix <sql_handle/hash_value> [plan_name]             Unfix the Plan
  - spmdrop <sql_handle/hash_value> [plan_name]             Drop the plan from SPM
  - spmevolve <sql_handle/hash_value> [plan_name]             Evolve the plan from SPM
  - spmmigol <attribute_name> <attribute_value> [fixed]           Migrate the plan to SPM using DBMS_SPM.MIGRATE_STORED_OUTLINE
  - spmmigolname <outline_name> [fixed]           Migrate outline plan to SPM by name
  - spmmigolcat <category_name> [fixed]           Migrate outline plan to SPM by category

Some examples:
Ex1. Find a SQL with SQL Plan Baselines using “ora spmlike”

$ ora spmlike "id=20"

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e YES NO  NO                 select * from t1 where id=20
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO                 select * from t1 where id=20

Ex2. Drop a Sql Plan Baseline of this SQL using “ora spmdrop”

$ ora spmdrop SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e
List SQL Plan Baselines of sql handle SQL_7e22e17af815c16b

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e YES NO  NO                 select * from t1 where id=20
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO                 select * from t1 where id=20

Drop Sql Plan Baselines of sql_handle SQL_7e22e17af815c16b
...Dropped 1 SQL Plans

PL/SQL procedure successfully completed.

List SQL Plan Baselines of sql handle SQL_7e22e17af815c16b

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO                 select * from t1 where id=20

Ex3. Bind a good plan to this SQL using “ora spmloadgood”

--1.  Suppose FULL TABLE SCAN is better for this SQL. Using hint or other methods to get the FULL TABLE SCAN plan in cursor cache.
SQL> set autotrace on
SQL> select /*+ full(t1) */ * from t1 where id=20;

        ID
----------
NAME
--------------------------------------------------------------------------------------------------------------------------------
        20
COL$

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    27 |    49 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    27 |    49 |
----------------------------------------------------------

...

--2. Get the hinted SQL's hash value
SQL> set autotrace off
SQL> select hash_value,sql_text from v$sql where sql_text like 'select /*+ full(t1) */ * from t1 where id=20';

HASH_VALUE SQL_TEXT
---------- ------------------------------------------------------------
2336257891 select /*+ full(t1) */ * from t1 where id=20

--3. Bind the good plan to the original SQL.
$ ora spmloadgood SQL_7e22e17af815c16b 2336257891
Load SQL 2336257891 plan in cursor cache to sql_handle SQL_7e22e17af815c16b
...Loaded 1 SQL Plans

PL/SQL procedure successfully completed.

List SQL Plan Baselines of sql handle SQL_7e22e17af815c16b

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbb844cb98a YES YES NO      2008498169 select * from t1 where id=20
SQL_7e22e17af815c16b SQL_PLAN_7w8r1gbw1bhbbdbd90e8e YES YES YES     2008498169 select * from t1 where id=20

As you can see, the good plan is loaded as FIXED plan SQL_PLAN_7w8r1gbw1bhbbdbd90e8e

Ex4. Migrate outlines to SPM using “ora spmmigolname”

--1. Suppose we have an outline
SQL> select ol_name,sql_text from outln.ol$ where ol_name='OL_288078590';

OL_NAME                        SQL_TEXT
------------------------------ ------------------------------------------------------------
OL_288078590                   select *
                               from t1
                               where id=50
--2. There is no SQL Plan Baseline for this SQL now
$ ora spmlist 288078590
Try to get sql_handle by hash_value 288078590:
...Not found sql_handle for hash_value 288078590, exit...

--3. Let's load migrate the outline to SPM
$ ora spmmigolname OL_288078590

-------------------------------------------------------------------------------
               Migrate Stored Outline to SQL Plan
Baseline Report
-------------------------------------------------------------------------------

Summary:
--------
Number of stored
outlines to be migrated: 1

Stored outlines migrated successfully: 1

Changing converted outlines to PLAN_MIGRATED category

PL/SQL procedure successfully completed.

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_dedf84a0faafbebe OL_288078590                   YES YES YES      288078590 select *
                                                                               from t1
                                                                               where id=50

Ex5. Show the SQL Plan Baselines using “ora spmshow”

ora spmshow 288078590
Try to get sql_handle by hash_value 288078590:
...Got sql_handle SQL_dedf84a0faafbebe
List SQL Plan Baselines of sql handle SQL_dedf84a0faafbebe

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX ONE_HASH_VALUE SQL_TEXT_80
-------------------- ------------------------------ --- --- --- -------------- --------------------------------------------------
SQL_dedf84a0faafbebe OL_288078590                   YES YES YES      288078590 select *
                                                                               from t1
                                                                               where id=50

Show SQL Plan Baselines of sql handle 288078590

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_dedf84a0faafbebe
SQL text: select * from t1 where id=50
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: OL_288078590         Plan id: 2219620746
Enabled: YES     Fixed: YES     Accepted: YES     Origin: STORED-OUTLINE
--------------------------------------------------------------------------------

Plan hash value: 3636266709

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    27 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    27 |     2 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1 |     1 |       |     1 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=50)

Note
-----
   - cpu costing is off (consider enabling it)

29 rows selected.

Here is the script:

#!/bin/ksh
#set -x
#
# 12/19/2011  AlexZeng -- 'ora spm*' for SQL Plan Management

if [ "$1" = "spm" ]; then
    echo " ora spm"
    echo "       - spmlike <pattern>                 List SQL Plan Baselines by matching sql_text to pattern";
    echo "       - spmhandle2hv <SQL_HANDLE>         Get SQL hash value from shared pool";
    echo "       - spmlist <sql_handle/hash_value> [plan_name]             List SQL Plan Baselines for giving SQL";
    echo "       - spmshow <sql_handle/hash_value> [plan_name]             Show SQL Plan Baselines for a given SQL";
    echo "       - spmload <hash_value> [plan_hash_value]             Load itself's sql plan from cursor cache for a given hash_value";
    echo "       - spmbind <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Bind <good_hash_value>'s plan to <sql_handle/hash_value>";
    echo "       - spmloadgood <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Load <good_hash_value>'s plan to <sql_handle/hash_value>";
    echo "       - spmalter <sql_handle/hash_value> <attribute_name> <attribute_value> [plan_name]     Change SQL Plan Baseline attribute";
    echo "       - spmdisable <sql_handle/hash_value> [plan_name]             Disable the Plan";
    echo "       - spmenable <sql_handle/hash_value> [plan_name]             Enable the Plan";
    echo "       - spmfix <sql_handle/hash_value> [plan_name]             Fix the Plan";
    echo "       - spmunfix <sql_handle/hash_value> [plan_name]             Unfix the Plan";
    echo "       - spmdrop <sql_handle/hash_value> [plan_name]             Drop the plan from SPM";
    echo "       - spmevolve <sql_handle/hash_value> [plan_name]             Evolve the plan from SPM";
    echo "       - spmmigol <attribute_name> <attribute_value> [fixed]           Migrate the plan to SPM using DBMS_SPM.MIGRATE_STORED_OUTLINE";
    echo "       - spmmigolname <outline_name> [fixed]           Migrate outline plan to SPM by name";
    echo "       - spmmigolcat <category_name> [fixed]           Migrate outline plan to SPM by category";
exit 0
fi

#Sql Plan Managment functions
function spm_hv2sqlhandle {
sqlplus -s "/ as sysdba" <<EOF
set line 1000 pagesize 0 feedback off tab off echo off heading off numwidth 30
set serveroutput on
declare
v_sql_handle varchar2(100);
begin
  select sql_handle into v_sql_handle
    from dba_sql_plan_baselines b, v\$sql s
   where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE 
     and s.hash_value=$1
     and rownum=1;
  dbms_output.put_line(v_sql_handle);
exception when others then
  dbms_output.put_line('NOT_FOUND');
end;
/
EOF
}


if [ "$1" = "spmlike" ]; then
if [ "$2" = "" ]; then
echo "ora spmlike <pattern>                 List SQL Plan Baselines by matching sql_text to pattern";
exit;
fi
sqlplus -s "/ as sysdba" <<EOF
col SQL_HANDLE for a20
col SQL_TEXT_80 for a50
select
   sql_handle, plan_name,
   enabled, accepted, fixed,
   (select hash_value from v\$sql s where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE and rownum=1) as one_hash_value,
   dbms_lob.substr(sql_text,80) as sql_text_80
from dba_sql_plan_baselines b
where upper(dbms_lob.substr(sql_text,4000)) like upper('%$2%')
order by sql_text_80 desc
;
EOF
exit;
fi


if [ "$1" = "spmlist" ]; then
if [ "$2" = "" ]; then
echo "ora spmlist <sql_handle/hash_value> [plan_name]             List SQL Plan Baselines for giving SQL";
exit;
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
		 echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
		 echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

if [ "$3" = "" ]; then
  filter="1=1"
  echo "List SQL Plan Baselines of sql handle $iv"
else
  filter="PLAN_NAME='$3'"
  echo "List SQL Plan Baselines of sql handle $iv with PLAN_NAME $3"
fi

sqlplus -s "/ as sysdba" <<EOF
col SQL_HANDLE for a20
col SQL_TEXT_80 for a50
select
   sql_handle, plan_name,
   enabled, accepted, fixed,
   (select hash_value from v\$sql s where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE and rownum=1) as one_hash_value,
   dbms_lob.substr(sql_text,80) as sql_text_80
from dba_sql_plan_baselines b
where sql_handle='$p_sql_handle' and $filter
order by one_hash_value desc,enabled, accepted, fixed
;
EOF
exit;
fi


if [ "$1" = "spmshow" ]; then
if [ "$2" = "" ]; then
echo "ora spmshow <sql_handle/hash_value> [plan_name]             Show SQL Plan Baselines for a given SQL";
exit;
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
		 echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
		 echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle

if [ "$3" = "" ]; then
pname="NULL";
echo "Show SQL Plan Baselines of sql handle $iv"
else
pname="'$3'";
echo "Show SQL Plan Baselines of sql handle $iv with PLAN_NAME $3"
fi

sqlplus -s "/ as sysdba" <<EOF
SELECT p.*
   FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('$p_sql_handle', $pname, 'TYPICAL')) p;
EOF
exit;
fi


if [ "$1" = "spmload" ]; then
if [ "$2" = "" ]; then
echo "ora spmload <hash_value> [plan_hash_value]             Load itself's sql plan from cursor cache for a given hash_value";
exit;
fi
if [ "$3" = "" ]; then
phv="NULL";
else
phv="$3";
fi

echo "Load SQL $2 plan in cursor cache to SQL Plan Baselines"
sqlplus -s "/ as sysdba" <<EOF
set serveroutput on
declare
  cnt number := 0;
  v_sql_id varchar2(100);
begin
  select sql_id into v_sql_id from v\$sql where hash_value=$2 and rownum=1;
  cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>v_sql_id, plan_hash_value=>$phv, fixed=>'NO',enabled=>'YES');
  dbms_output.put_line('...Loaded '||cnt||' SQL Plans');
end;
/
EOF
ora spmlist $2
exit;
fi


if [ "$1" = "spmbind" ]; then
if [ "$2" = "" -o "$3" = "" ]; then
echo "ora spmbind <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Bind <good_hash_value>'s plan to <sql_handle/hash_value>";
exit;
fi

if [ "$4" = "" ]; then
  filter="1=1";
else
  filter="plan_hash_value=$4";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
		 echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
		 echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

echo "Load SQL $3 plan in cursor cache to sql_handle $p_sql_handle"
sqlplus -s "/ as sysdba" <<EOF
set serveroutput on
declare
  cnt number := 0;
  cnt_total number := 0;
  v_sql_id varchar2(100);
  v_phv number;
begin
  for r in (select sql_id, plan_hash_value from V\$SQL where hash_value=$3 and $filter) loop
    cnt := dbms_spm.load_plans_from_cursor_cache(sql_handle=>'$p_sql_handle', 
                                                 sql_id=>r.sql_id, plan_hash_value=>r.plan_hash_value,
																								 fixed=>'YES',enabled=>'YES');
    cnt_total := cnt_total + cnt;
  end loop;
  dbms_output.put_line('...Loaded '||cnt_total||' SQL Plans');
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi


if [ "$1" = "spmloadgood" ]; then
if [ "$2" = "" -o "$3" = "" ]; then
echo "ora spmloadgood <sql_handle/hash_value> <good_hash_value> [plan_hash_value]    Load <good_hash_value>'s plan to <sql_handle/hash_value>";
exit;
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "...SQL $2 didn't yet, load it...\n";
     ora spmload $iv
     p_sql_handle=`spm_hv2sqlhandle $iv`
     if [ "$p_sql_handle" = "NOT_FOUND" ]; then
       echo "...Looks like load SQL $iv failed or something is wrong, exit..."
       exit;
     fi
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmbind $p_sql_handle $3 $4
exit;
fi


if [ "$1" = "spmalter" ]; then
if [ "$2" = "" -o "$3" = "" -o "$4" = "" ]; then
echo "ora spmalter <sql_handle/hash_value> <attribute_name> <attribute_value> [plan_name]     Change SQL Plan Baseline attribute";
exit;
fi

if [ "$5" = "" ]; then
pname="NULL";
else
pname="'$5'";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle

echo "Change SQL Plan Baseline attribute '$3' to '$4'"
sqlplus -s "/ as sysdba" <<EOF
set serveroutput on
declare
  cnt number := 0;
begin
  cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( SQL_HANDLE => '$p_sql_handle', PLAN_NAME => $pname, 
																					 ATTRIBUTE_NAME => '$3', ATTRIBUTE_VALUE => '$4');
  dbms_output.put_line('...Altered '||cnt||' SQL Plans');
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi

if [ "$1" = "spmdisable" ]; then
if [ "$2" = "" ]; then
echo "ora spmdisable <sql_handle/hash_value> [plan_name]             Disable the Plan";
exit;
fi
ora spmalter $2 enabled NO $3
exit;
fi

if [ "$1" = "spmenable" ]; then
if [ "$2" = "" ]; then
echo "ora spmenable <sql_handle/hash_value> [plan_name]             Enable the Plan";
exit;
fi
ora spmalter $2 enabled YES $3
exit;
fi

if [ "$1" = "spmfix" ]; then
if [ "$2" = "" ]; then
echo "ora spmfix <sql_handle/hash_value> [plan_name]             Fix the Plan";
exit;
fi
ora spmalter $2 fixed YES $3
exit;
fi

if [ "$1" = "spmunfix" ]; then
if [ "$2" = "" ]; then
echo "ora spmunfix <sql_handle/hash_value> [plan_name]             Unfix the Plan";
exit;
fi
ora spmalter $2 fixed NO $3
exit;
fi


if [ "$1" = "spmdrop" ]; then
if [ "$2" = "" ]; then
echo "ora spmdrop <sql_handle/hash_value> [plan_name]             Drop the plan from SPM";
exit;
fi
if [ "$3" = "" ]; then
pname="NULL";
else
pname="'$3'";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "...Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle
echo "Drop Sql Plan Baselines of sql_handle $p_sql_handle"
sqlplus -s "/ as sysdba" <<EOF
set serveroutput on
declare
  cnt number := 0;
  v_sql_handle varchar2(100);
begin
  cnt := dbms_spm.drop_sql_plan_baseline(sql_handle=>'$p_sql_handle', plan_name=>$pname);
  dbms_output.put_line('...Dropped '||cnt||' SQL Plans');
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi


if [ "$1" = "spmevolve" ]; then
if [ "$2" = "" ]; then
echo "ora spmevolve <sql_handle/hash_value> [plan_name]             Evolve the plan from SPM";
exit;
fi
if [ "$3" = "" ]; then
pname="NULL";
else
pname="'$3'";
fi

iv=$2
if [ "$iv" -eq "$iv" 2> /dev/null ]; then
  echo "Try to get sql_handle by hash_value $iv:"
  p_sql_handle=`spm_hv2sqlhandle $iv`
  if [ "$p_sql_handle" = "NOT_FOUND" ]; then
     echo "..Not found sql_handle for hash_value $iv, exit..."
     exit;
  else
     echo "...Got sql_handle $p_sql_handle"
  fi
else
  p_sql_handle=$iv
fi

ora spmlist $p_sql_handle
echo "Evolve Sql Plan Baselines of sql_handle $p_sql_handle"
sqlplus -s "/ as sysdba" <<EOF
set serveroutput on size unlimited
declare
  rpt clob;
  l_offset number default 1;
begin
  rpt := dbms_spm.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'$p_sql_handle', plan_name=>$pname);	
  dbms_output.put_line( dbms_lob.substr( rpt, 32767, 1 ) );
end;
/
EOF
ora spmlist $p_sql_handle
exit;
fi


if [ "$1" = "spmmigol" ]; then
if [ "$2" = "" -o "$3" = "" ]; then
echo "ora spmmigol <attribute_name> <attribute_value> [fixed]           Migrate the plan to SPM using DBMS_SPM.MIGRATE_STORED_OUTLINE";
exit;
fi
if [ "$4" = "" ]; then
pfixed="YES";
else
pfixed="$4";
fi
sqlplus -s "/ as sysdba" <<EOF
set serveroutput on size unlimited
var pname varchar2(100)
declare
  rpt clob;
begin
  :pname := 'NON';
  rpt := dbms_spm.MIGRATE_STORED_OUTLINE(attribute_name=>'$2', attribute_value=>'$3', fixed=>'$pfixed');
  dbms_output.put_line( dbms_lob.substr( rpt, 32767, 1 ) );
	dbms_output.put_line('Changing converted outlines to PLAN_MIGRATED category');
  if lower('$2') = 'outline_name' then
	    execute immediate 'alter outline $3 change category to PLAN_MIGRATED';
      :pname := '$3';
	elsif lower('$2') = 'category' then
      for r in (select ol_name, 'alter outline '||ol_name||' change category to PLAN_MIGRATED' st from outln.ol\$ where category='$3')
      loop 
        dbms_output.put_line(r.st);
        execute immediate r.st;
      end loop;
	elsif lower('$2') = 'all' then
      for r in (select ol_name, 'alter outline '||ol_name||' change category to PLAN_MIGRATED' st from outln.ol\$)
      loop
        dbms_output.put_line(r.st);
        execute immediate r.st;
      end loop;
  elsif lower('$2') = 'sql_text' then
      for r in (select ol_name,sql_text from outln.ol\$)
      loop
        if substr(r.sql_text,1,4000) = '$3' then
          dbms_output.put_line('alter outline '||r.ol_name||' change category to PLAN_MIGRATED');
          execute immediate 'alter outline '||r.ol_name||' change category to PLAN_MIGRATED';
          :pname := r.ol_name;
        end if;
      end loop;
	end if;
end;
/
col SQL_HANDLE for a20
col SQL_TEXT_80 for a50
select
   sql_handle, plan_name,
   enabled, accepted, fixed,
   (select hash_value from v\$sql s where s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE and rownum=1) as one_hash_value,
   dbms_lob.substr(sql_text,80) as sql_text_80
from dba_sql_plan_baselines b
where PLAN_NAME = upper(:pname)
order by one_hash_value desc,enabled, accepted, fixed
;
EOF
exit;
fi


if [ "$1" = "spmmigolname" ]; then
if [ "$2" = "" ]; then
echo "ora spmmigolname <outline_name> [fixed]           Migrate outline plan to SPM by name";
exit;
fi
if [ "$3" = "" ]; then
pfixed="YES";
else
pfixed="$3";
fi
ora spmmigol outline_name $2 $pfixed
exit;
fi

if [ "$1" = "spmmigolcat" ]; then
if [ "$2" = "" ]; then
echo "ora spmmigolcat <category_name> [fixed]           Migrate outline plan to SPM by category";
exit;
fi
if [ "$3" = "" ]; then
pfixed="YES";
else
pfixed="$3";
fi
ora spmmigol category $2 $pfixed
exit;
fi

It’s very easy to use at UNIX/LINUX hosts. Also, it can be converted to PL/SQL procedures easily.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

4 Responses to SQL Plan Management handy commands

  1. Jinwen Zou says:

    Hi Alex,

    great Handy command.

    I saw your other post about result cache as well. A quick question.

    Can SPM work with result cache?
    I had played with stored outline and dbms_sqldiag_internal to create sql patch, seems that result_cache hint will not be picked up by outline.

  2. R says:

    Alex,

    Great work. I tried to use your shell script when I try to use it I am getting
    ./spm.ksh
    ./spm.ksh: line 6: syntax error at line 32: `(‘ unexpected

    • Alex Zeng says:

      Some “<<EOF" were removed from the post somehow. I updated the script and test it myself. It should work now.
      -Alex

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: