Capturing SQL PLAN Baselines

An introductory :

SQL> select count(*) from dba_sql_plan_baselines ;

COUNT(*)
----------
       2

SQL>
SQL> declare
2  ret_value pls_integer;
3  sql_id_in varchar2(30);
4  cursor c1 is
5  select distinct sql_id from v$sql,dba_users
6  where parsing_user_id = user_id
7  and username = 'HEMANT'
8  order by 1;
9  begin
10  open c1 ;
11  loop
12  fetch c1 into sql_id_in;
13  exit when c1%NOTFOUND;
14  dbms_output.put_line('For SQL_ID  :  '     ||  sql_id_in);
15  ret_value := dbms_spm.load_plans_from_cursor_cache(sql_id=>sql_id_in);
16  -- dbms_output.put_line(ret_value);
17  end loop;
18  end;
19  /
For SQL_ID  :  0v8ygnt3qfnwq
For SQL_ID  :  1761jd8v8v5vu
For SQL_ID  :  2rcwxc7j3b6cu
For SQL_ID  :  3kvqxcy1cg1av
For SQL_ID  :  3qxyrb5h82cra
For SQL_ID  :  5qgz1p0cut7mx
For SQL_ID  :  7cfz5wy9caaf4
For SQL_ID  :  7hys3h7ysgf9m
For SQL_ID  :  7na10rxj8ksw5
For SQL_ID  :  9rvgxuv7w16qg
For SQL_ID  :  buuzqhh3qshuw
For SQL_ID  :  cf19zu91tn7mj
For SQL_ID  :  cuwm7p53jggtb
For SQL_ID  :  cw6vxf0kbz3v1
For SQL_ID  :  d6vwqbw6r2ffk
For SQL_ID  :  dpbugmu3fw5qk
For SQL_ID  :  dt0hqkzcdq5ny
For SQL_ID  :  du2u5hgh368qt
For SQL_ID  :  dyk4dprp70d74
For SQL_ID  :  g3f3cw3zy5aat
For SQL_ID  :  g4y6nw3tts7cc
For SQL_ID  :  gpp3q1rch3vpx

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from dba_sql_plan_baselines ;

COUNT(*)
----------
      21

SQL>  

(Note : 1 had already been captured earlier)
Evolving baselines to follow...
.


Another blog post (by Kellyn Pot'vin) :  Capturing SQL Baselines for Consistent Performance for 10g Upgrades to 11g



UPDATE  14-Jun-19 :  See this post :  Automatic SQL Plan Management in 19c