Friday 2 October 2020

Loading plans in the baselines with a Linux Shell script.

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