Oracle DB. Changing and fixing query execution plan using dbms_spm.

Oracle Query Optimizer generally works well, but sometimes it makes your fast and polished queries slow down. Often, you can not simply add hints and recompile them on production environment. In such case be ready to use dbms_spm package.

---

Don’t forget about appropriate grants.
grant select on V_$SESSION to test;
grant select on V_$SQL_PLAN to test;
grant select on V_$SQL_PLAN_STATISTICS_ALL to test;
grant select on V_$SQL to test;

---

OK, we have a table with a set of data and two indexes.

create table t_plan_test
(
n1 number not null,
n2 number not null
);

create index idx_t_plan_test_1 on t_plan_test(n1);
create index idx_t_plan_test_2 on t_plan_test(n2, n1);

insert into t_plan_test
select rownum, rownum
from dual
connect by rownum <=1000;

commit;

Let’s execute a query to check a data.

select t.* from t_plan_test t where t.n1 = 100;

100	100

Data is here. Let’s check which index is used.

explain plan for select t.* from t_plan_test t where t.n1 = 100;
select * from table(dbms_xplan.display);

Plan hash value: 2822388801

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_PLAN_TEST       |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_PLAN_TEST_1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."N1"=100)

Note
-----
- dynamic sampling used for this statement (level=2)

Optimizer used idx_t_plan_test_1 as expected. But let’s imaging, optimizer is wrong and it should use idx_t_plan_test_2 instead.
First, we need to know our query’s sql_id. Take “Plan hash value” from the previous query and get sql_id using the following query.

select s.sql_text, s.sql_id, s.plan_hash_value
from v$sql s where s.plan_hash_value = 2822388801;

select t.* from t_plan_test t where t.n1 = 100 	2w8k1yhuk8k1v	2822388801
explain plan for select t.* from t_plan_test t where t.n1 = 100 	2rcswv78fcp13	2822388801

Please do not confuse with explain plan query.

Let’s optimize our query now and add a hint so it will use idx_t_plan_test_2 index instead.

select /*+INDEX(t idx_t_plan_test_2)*/ t.* from t_plan_test t where t.n1 = 100;

Check idx_t_plan_test_2 index is used.

explain plan for select /*+INDEX(t idx_t_plan_test_2)*/t.* from t_plan_test t where t.n1 = 100;
select * from table(dbms_xplan.display);

Plan hash value: 3105628069

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |    26 |     5   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_T_PLAN_TEST_2 |     1 |    26 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."N1"=100)
filter("T"."N1"=100)

Note
-----
- dynamic sampling used for this statement (level=2)

OK, now take “Plan hash value” of a second query and get it sql_id.

select s.sql_text, s.sql_id, s.plan_hash_value
from v$sql s where s.plan_hash_value = 3105628069;

explain plan for select /*+INDEX(t idx_t_plan_test_2)*/t.* from t_plan_test t where t.n1 = 100 	4jnycbw4j9cav	3105628069
select /*+INDEX(t idx_t_plan_test_2)*/ t.* from t_plan_test t where t.n1 = 100 	43x83pbfc3x5n	3105628069

Fine. Now we have all necessary parameters to fix query plan.

---

Following queries executing using SYS user. Grant all appropriate privileges to your user, if you need to.

---

First, we need to load first query plan using correspond sql_id and plan_hash_value.

begin
  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '2w8k1yhuk8k1v', plan_hash_value => 2822388801)); 
end;

Second, we need to get it sql_handle and disable it.

select sql_handle, to_char(sql_text), plan_name from DBA_SQL_PLAN_BASELINES;

SQL_cbead429bd574d05	"select t.* from t_plan_test t where t.n1 = 100"	SQL_PLAN_cruqn56ypfm8591102725

begin
  dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_cbead429bd574d05',attribute_name => 'enabled',attribute_value => 'NO' ));
end;

Third, we need to fix second query plan using it sql_id и plan_hash_value and sql_handle from a previous query.

begin
  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '43x83pbfc3x5n', plan_hash_value => 3105628069, fixed => 'YES', sql_handle => 'SQL_cbead429bd574d05')); 
end;

All done. Let’s check first query plan.

explain plan for select t.* from t_plan_test t where t.n1 = 100;
select * from table(dbms_xplan.display);

Plan hash value: 3105628069

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     4 |   104 |     0   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_T_PLAN_TEST_2 |     4 |   104 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."N1"=100)
filter("T"."N1"=100)

Note
-----
- SQL plan baseline "SQL_PLAN_cruqn56ypfm8500b9313e" used for this statement