Search My Oracle Blog

Custom Search

28 May, 2011

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
.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016