Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4864562
  • 博文数量: 983
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 12857
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(983)

文章存档

2019年(73)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2012-02-08 15:55:43

执行计划会根据数据库表的统计信息该表而改变,我们要使某个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--
阅读(3145) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册