Its a monday morning and Smith the junior dba is muddled since some developers are chasing him to load a good current plan in the baseline to avoid plan regression, Smith being relatively a new and a junior dba is not quite familiar on how he should use plsql and is complacent to do so, since he thinks the plsql code might load everything from shared pool to the baselines and might cause some issues if any, So what Smith does is, he calls Martin the senior dba and asks him if he can help him, Martin replies he already has a shell script which does that, all Smith would need to do is pass the sql id in the shell script argument as a $1and that would do the job. Smith couldnt be more happier and that makes his day.
The shell script is given below in this article , so what it does is, $1 as we are aware is the first argument when any shell script is invoked and $2 and $3 .. and so on could be used for as many arguments as much as you want but here we just need 1 argument to the job. So here $1 we use to pass the sql id in the plsql block of the shell script, please note we use $1 not the oracle plsql &a for that matter. Make sure you give executable permission to this script to make it work with chmod u+x spm.sh
[oracle@node2 dk]$ cat spm.sh
sqlplus / as sysdba << EOF
set echo off
set feedback off
set pages 0
set lin 100
set sqlnumber off
set serveroutput on
declare
a pls_integer;
begin
a := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '$1');
dbms_output.put_line(a|| ' plans loaded in the baseline');
end;
/
exit;
EOF
Lets run the code
[oracle@node2 dk]$ ./spm.sh 1uw84jcq6802a
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
1 plans loaded in the baseline
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
# So there you go , 1 plans loaded in the baseline is successfully returned as per our dbms_output.
Could we validate our plsql block to make sure it works as expected without the shell cover. Lets see :
SQL> declare
a pls_integer;
begin
a := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&a');
dbms_output.put_line(a|| ' plans loaded in the baseline');
end;
/
2 3 4 5 6 7 Enter value for a:
old 4: a := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&a');
new 4: a := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '');
0 plans loaded in the baseline
PL/SQL procedure successfully completed.
So here above we have zero plans loaded because we passed null value
SQL> /
Enter value for a: 1uw84jcq6802a
old 4: a := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&a');
new 4: a := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '1uw84jcq6802a');
1 plans loaded in the baseline
PL/SQL procedure successfully completed.
So here above we give a valid sql id and it makes sure it is loaded in the baseline.
SQL> select count(*) from dba_sql_plan_baselines;
COUNT(*)
----------
1
So yes it does work. Happy shell scripting
