SQL Plan Management handy commands
January 5, 2012 Leave a Comment
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 List SQL Plan Baselines by matching sql_text to pattern";
echo " - spmlist [plan_name] List SQL Plan Baselines for giving SQL";
echo " - spmshow [plan_name] Show SQL Plan Baselines for a given SQL";
echo " - spmload [plan_hash_value] Load itself's sql plan from cursor cache for a given hash_value";
echo " - spmbind [plan_hash_value] Bind 's plan to ";
echo " - spmloadgood [plan_hash_value] Load 's plan to ";
echo " - spmalter [plan_name] Change SQL Plan Baseline attribute";
echo " - spmdisable [plan_name] Disable the Plan";
echo " - spmenable [plan_name] Enable the Plan";
echo " - spmfix [plan_name] Fix the Plan";
echo " - spmunfix [plan_name] Unfix the Plan";
echo " - spmdrop [plan_name] Drop the plan from SPM";
echo " - spmevolve [plan_name] Evolve the plan from SPM";
echo " - spmmigol [fixed] Migrate the plan to SPM using DBMS_SPM.MIGRATE_STORED_OUTLINE";
echo " - spmmigolname [fixed] Migrate outline plan to SPM by name";
echo " - spmmigolcat [fixed] Migrate outline plan to SPM by category";
exit 0
fi
#Sql Plan Managment functions
function spm_hv2sqlhandle {
sqlplus -s "/ as sysdba" <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 List SQL Plan Baselines by matching sql_text to pattern";
exit;
fi
sqlplus -s "/ as sysdba" <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 [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" <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 [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" <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 [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" <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 [plan_hash_value] Bind 's plan to ";
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" <'$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 [plan_hash_value] Load 's plan to ";
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 [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" < '$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 [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 [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 [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 [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 [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" <'$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 [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" <'$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 [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" <'$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 [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 [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 a pity that I don’t know how to wrap the following part to a function in ksh. So the below part is repeated several times.
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
It’s very easy to use at UNIX/LINUX hosts. Also, it can be converted to PL/SQL procedures easily.
Advertisement
About Alex Zeng
