11g 's DBMS_SPM

11g introduces more admin-friendly features to maintain/control query plan stability.

I was going through this new package introduced in 11g for SQL PLAN Management(SPM) called DBMS_SPM.and to be honest, I actually got distracted while I was reading abt this.. Instead of knowing how to actually use this the way its intended to be used (many plans for a sql and flip flopping which one should the DBA wants to be used)..I thought something else and found it useful in a different way..


If you been to environments where developers just go nuts when
- they see the plan in production was completely different than what they expected
- they see a SQL taking forever because the plan changed in Production for no reason (most likely after stats gatheration or shutdown)

and there have been countless situations a DBA/Consultant would walk-in like an emergency doctor and take a look at all those "v$sql" patients and finally conclude "You are suffering from bind peeking problem". (which roughly means the plan in the shared pool was optimized for the first input value and unfit/inefficient for subsequent calls with varying input values)

Setup:
------

Sql>ed
Wrote file afiedt.buf

1  Create table test_sql_plan
2  as select 100 id, rpad(rownum,10,'x') name
3* from dual connect by level<999>/

Table created.

Sql>ed
Wrote file afiedt.buf

1  insert into  test_sql_plan
2   select rownum id, rpad(rownum,10,'x') name
3* from dual connect by level<11>/

10 rows created.

Sql>commit;

Commit complete.

Sql>Create index test_sp_idx on test_sql_plan(id);

Index created.

Sql>begin
2   dbms_stats.gather_table_stats(tabname=>'TEST_SQL_PLAN',
3   ownname=>'VENKAT',
4   method_opt=>'for all indexed columns size 254');
5  end;
6  /

PL/SQL procedure successfully completed.

Testing begins from here...

Session 1:
----------
Sql>exec :v := 2;

PL/SQL procedure successfully completed.

Sql>select * from test_sql_plan plan_with_2_first where id=:v;

     ID NAME
---------- ----------
      2 2xxxxxxxxx
  
  
      -- session 2
      -- ----------
      sq2>ed
  Wrote file afiedt.buf

    1  select sql_text sqltext,sql_id,plan_hash_value,executions from v$sql
    2  where sql_text like
    3  'select * from test_sql_plan plan_with_2_first%where id=%'
    4* and executions>0
  sq2>/

  SQLTEXT                                       SQL_ID        PLAN_HASH_VALUE EXECUTIONS
  --------------------------------------------- ------------- --------------- ----------
  select * from test_sql_plan plan_with_2_first 69yujf79wwkja      3623521558          1
  where id=:v

  sq2>-- go back to session 1 and rerun the same sql for different bind value

Sql>exec :v := 100;

PL/SQL procedure successfully completed.

Sql>select * from test_sql_plan plan_with_2_first where id=:v;
...
...
...
    100 991xxxxxxx
    100 992xxxxxxx
    100 993xxxxxxx
    100 994xxxxxxx
    100 995xxxxxxx
    100 996xxxxxxx
    100 997xxxxxxx
    100 998xxxxxxx

998 rows selected.

Sql>

 -- session 2
 -- ----------
 sq2>/

 SQLTEXT                                       SQL_ID        PLAN_HASH_VALUE EXECUTIONS
 --------------------------------------------- ------------- --------------- ----------
 select * from test_sql_plan plan_with_2_first 69yujf79wwkja      3623521558          2
 where id=:v

 sq2>ed
 Wrote file afiedt.buf

   1  select OPERATION,OBJECT_OWNER,OBJECT_NAME from v$sql_plan
   2  where PLAN_HASH_VALUE=3623521558
   3  and sql_id='69yujf79wwkja'
   4* order by id
 sq2>/

 OPERATION                      OBJECT_OWNER                   OBJECT_NAME
 ------------------------------ ------------------------------ -----------------------------
 SELECT STATEMENT
 TABLE ACCESS                   VENKAT                         TEST_SQL_PLAN
 INDEX                          VENKAT                         TEST_SP_IDX

-- now we know we have a bad plan in shared pool ..
-- how do we go about and eliminate the plan
-- without brutally flushing the entire shared pool
--

DBMS_SPM to our rescue..

sq2>l
1  declare
2  lv_res PLS_INTEGER;
3  begin
4  lv_res:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'69yujf79wwkja');
5  dbms_output.put_line(lv_res);
6* end;
sq2>/
1

PL/SQL procedure successfully completed.

sq2>select sql_handle,sql_text from dba_sql_plan_baselines
2   where sql_text like
3   'select * from test_sql_plan plan_with_2_first%where id=%'
4  /

SQL_HANDLE                     SQL_TEXT
------------------------------ ----------------------------------------------------------
SYS_SQL_5053e5a52d822dcf       select * from test_sql_plan plan_with_2_first where id=:v

sq2>declare
2   lv_res pls_integer;
3   begin
4   lv_res:=DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle=>'SYS_SQL_5053e5a52d822dcf');
5   dbms_output.put_line(lv_res);
6  end;
7  /
1

PL/SQL procedure successfully completed.

for bind variable 100, the sql was initially doing index lookup before (whereas full table scan was optimal one)

Lets run it again

--session 1 runs the same sql again (with value =100)
Sql>select * from test_sql_plan plan_with_2_first where id=:v;
...
...
...
    100 991xxxxxxx
    100 992xxxxxxx
    100 993xxxxxxx
    100 994xxxxxxx
    100 995xxxxxxx
    100 996xxxxxxx
    100 997xxxxxxx
    100 998xxxxxxx

998 rows selected.

-- session 2
sq2>select sql_text sqltext,sql_id,plan_hash_value,executions from v$sql
2  where sql_text like
3  'select * from test_sql_plan plan_with_2_first%where id=%'
4  and executions>0
5  /

SQLTEXT                                       SQL_ID        PLAN_HASH_VALUE EXECUTIONS
--------------------------------------------- ------------- --------------- ----------
select * from test_sql_plan plan_with_2_first 69yujf79wwkja      3623521558          2
where id=:v

select * from test_sql_plan plan_with_2_first 69yujf79wwkja      1269960815          1
where id=:v  <---- ***** NEW PLAN *****

sq2>ed
Wrote file afiedt.buf

1  select OPERATION,OPTIONS,OBJECT_OWNER,OBJECT_NAME from v$sql_plan
2  where PLAN_HASH_VALUE=1269960815
3  and sql_id='69yujf79wwkja'
4* order by id
sq2>/

OPERATION                      OPTIONS                        OBJECT_OWNER                   OBJECT_NAME
------------------------------ ------------------------------ ------------------------------ -------
SELECT STATEMENT
TABLE ACCESS                   FULL                           VENKAT                         TEST_SQL_PLAN

Now that looks like easy fix without having to run dbms_stats or flushing shared pool or running an alter table (to forcefully invalidate the plan in shared pool)