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
просто и понятно, спасибо