首页 > 代码库 > 【转】Oracle Outline的使用及注意事项

【转】Oracle Outline的使用及注意事项

2024-07-03 12:32:36   227人阅读

概述 
Oracle Outline是用来保持SQL执行计划（execution plan）的一个工具。我们可以通过outline工具防止SQL执行计划在数据库环境变更（如统计信息，部分参数等）而引起变化。 
Outline的主要使用在以下情况： 
1. 
为避免在升级后某些sql出现严重性能下降而且在短时间内不能优化的情况， 
我们可以使用outline的功能将原生产库中的sql执行计划实施在新的数据库上。 
2. 
为避免SQL的执行计划在统计数据不准确的情况（如未能及时收集表或索引的统计信息）下导致变化从而引起的性能降低。 
3. 
避免大规模分布实施的应用出现数据库版本、配置等区别引起的优化器产生不同的执行计划。 
4. 
某些Bug引起优化器生成较差的执行计划。在bug修复前我们可以使用outline来强制SQL的执行计划的正确。 
Outline的机制是将所需要的执行计划的hint保存在outline的表中。当执行SQL时，Oracle会与outline中的SQL比较，如果该SQL有保存的outline，则通过保存的hint生成执行计划。 
Outline的使用注意事项 
Outline的使用需要注意以下事项。 
1． 
Outln用户是一个非常重要的系统用户，其重要性跟sys，system一样。在任何情况下都不建议用户删除outln，否则会引起数据库错误。 
2． 
优化器通过Outline生成执行计划前提是outline内所有hint都有效的。如：索引没有创建的前提下，索引的hint是失效的，导致该SQL的outline计划不会被使用。 
3． 
参数Cursor_sharing=force时不能使用outline。 
4． 
literial sql的共享程度不高，Outline针对绑定变量的sql较好。针对literial sql的情况，需要每条sql都生成outline。 
5． 
创建outline需要有create any outline的权限。 
6． 
要注意从CBO的角度来看，数据库表和索引的统计信息是随着数据量的变化而不断改变的。固定的执行计划在某些时段并不一定是最优的执行计划。所以outline的使用是要根据具体情况来决定的。 
Outline使用举例 
本文举例说明如何使用outline，并且将outline的内容从8i迁移到10g的数据库上使用。 
操作步骤以scott用户为例说明。 
8i，10g中在scott用户下创建测试表以说明outline的使用. 
Login as scott 
Create table t_test（col1 varchar2(2)）; 
1． 
确定8i生产库的db，listener处于关闭的状态。 
2． 
启动8i生产库instance. 
3． 
8i库使用system用户登陆，赋create any outline权限给sql执行用户。 
Grant create any outline to scott; 
4． 
8i库使用scott用户登陆。 
Create outline t_ol1 for category special on select * from t_test where col1=’00’; 
T_ol1àoutline name 
(注意每个outline都需要使用唯一的名字，不能重复) 
Specialàoutline所属的类（category） 
Select * from t_test where col1=’00’;à需要保存outline的sql 
5． 
10g，8i库Unlock并修改outlin用户口令。注意，outln用户的口令可以修改但是outln用户不能删除。 
Alter user outln identified by outln account unlock; 
6． 
在8i库使用outln用户，导出outline数据。 
Exp outln/outln tables=ol/$ ol/$hints file=ol.dmp log=ol_exp.log 
将export的数据拷贝到10g库所在机器 
7． 
在10g库使用outln用户导入outline数据 
imp outln/outln file=ol.dmp ignore=y log=ol_imp.log 
8． 
在10g库使用sys用户更新ouline的signature 
connect sys/manager 
exec dbms_outln.update_signatures; 
启用stored outline 
alter system set use_stored_outlines=special; 
à指定outline category 
9． 
检测outline是否被使用 
connect scott/tiger 
create index I_test on t_test (col1); 
à创建索引，以改变执行计划 
explain plan for select * from t_test where col1=’00’; 
@?/rdbms/admin/utlxplp 
PLAN_TABLE_OUTPUT 
Plan hash value: 4036493941 
---------------------------------------------------------------------------- 
| Id  | Operation                                         | Name      | Rows  | Bytes      | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------- 
|   0 | select STATEMENT                       |                 |     1      |     3           |  1200   (4)       | 00:00:17 | 
|*1  |TABLE ACCESS FULL                   | T_TEST |     1      |     3           |  1200   (4)      | 00:00:17 | 
---------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter("COL1"=‘00‘) 
Note 
----- 
   - outline "OL1" used for this statement 
à 
注意执行计划指出online已经使用 
17 rows selected. 
说明outline已经启用。 
如果没有outline的情况下应该使用索引,执行计划如下。 
PLAN_TABLE_OUTPUT 
-------------------------------------------------------------------------------- 
Plan hash value: 614253159 
--------------------------------------------------------------------------- 
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------- 
|   0 | select STATEMENT     |                |     1      |          3 |     3   (0)          | 00:00:01 | 
|*  1 |  INDEX RANGE SCAN| I_TEST |     1      |          3 |     3   (0)          | 00:00:01 | 
--------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - access("COL1"=‘00‘) 
Outline维护 
停止db使用outline功能： 
alter system set use_stored_outlines=false; 
disable/enable具体outline： 
alter outline ol_name disable; 
alter outline ol_name enable; 
删除outline category： 
9i, 10g: exec dbms_outln.drop_by_cat(‘category_name’); 
8i: exec outln_pkg.drop_by_cat(‘category_name’); 
outline相关视图 
dba_outlines 
检查outline是否存在 
select 
name, category, owner from dba_outlines; 
dba_outline_hints 
该视图列出outline的hints内容


联系
我们