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)
