执行计划会根据数据库表的统计信息该表而改变,我们要使某个SQL固定不变的使用某个执行计划,可以通过oralce里的outline来实现,查询的SQL语句必须跟outline里定义的完全相同.
条件值固定的outline
步骤1:创建表和索引并初始化数据
Create Table tb_test
(
Id Number Not Null,
Name Varchar2(1000)
);
Declare
Begin
For i In 1 .. 10000 Loop
Insert Into Tb_Test Values (i, i);
End Loop;
End;
Create Index idx_tb_test On tb_test(Id);
步骤2:查看执行计划
SQL> set line 10000;
SQL> alter session set optimizer_mode=choose;
SQL> explain plan for select * from tb_test where Id>100;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3578346379
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_TEST |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST |
---------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
2 - access("ID">0)
Note
------ rule based optimizer used (consider using cbo)
18 rows selected.
-- 可以看到目前走的是索引范围扫描
步骤3:创建outline
create or replace outline Outline_Test
for category my_category
on
select * from tb_test where id>100;
步骤4.分析表
exec dbms_stats.gather_table_stats(ownname => 'OSS03',tabname => 'TB_TEST',cascade =>true);
这个时候查看执行计划
SQL> explain plan for select * from tb_test where id>100;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------Plan hash value: 1092599453
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9957 | 79656 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_TEST | 9957 | 79656 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------- 1 - filter("ID">100)
13 rows selected.
-- 可以看到表经过分析后走了全表扫表,要是我想让这个SQL走之前的执行计划,如何做呢,接下来可以使用刚才创建的outline来固定执行计划
步骤5:使用outline固定执行计划
SQL> alter session set use_stored_outlines = my_category;
Session altered.
SQL> explain plan for select * from tb_test where id>100;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------Plan hash value: 3578346379
----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9957 | 79656 | 42 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_TEST | 9957 | 79656 | 42 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST | 9957 | | 22 (0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------- 2 - access("ID">100)
Note
-----
- outline "OUTLINE_TEST" used for this statement
-- 这里可以看到使用了刚才创建的outline OUTLINE_TEST,走的是索引范围扫描.
带有绑定变量的outline
上面步骤测试的是条件值是固定的执行计划固定,但是我们的查询不是每次只查询id>100的数据,条件id值会有改变,使用固定条件值的outline的话,执行是使用不到条件id>100的outline的.那这个时候就需要创建使用带有绑定变量的outline.
步骤1.创建带绑定变量的outline.
create or replace outline outline_test01 for category my_category01 on
select * from tb_test where id> :y;
步骤2:查看查看id>200的执行计划
SQL> alter session set use_stored_outlines = my_category01;
SQL> var y number;
SQL> exec :y:=200;
PL/SQL procedure successfully completed.
SQL> explain plan for select * from tb_test where id> :y;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------plan hash value: 3578346379
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 503 | 4024 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_TEST | 503 | 4024 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST | 91 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------- 2 - access("ID">TO_NUMBER(:Y))
Note
----- outline "OUTLINE_TEST01" used for this statement
--可以看到使用了带有绑定变量的outline.
步骤3:将条件id的值由200修改为300后,查看执行计划
SQL> exec :y:=300;
PL/SQL procedure successfully completed.
SQL> explain plan for select * from tb_test where id> :y;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------Plan hash value: 3578346379
----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 503 | 4024 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_TEST | 503 | 4024 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST | 91 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------- 2 - access("ID">TO_NUMBER(:Y))
Note
----- outline "OUTLINE_TEST01" used for this statement
18 rows selected.
-- id值改变后执行计划保持跟之前的一致
-- The End--
