第二十章   使用plan stability

使用plan stability可以保证性能特性，使数据库从RBO到CBO升级更加方便

10g中的stored outlines特性在11g仍然保留，但oracle建议使用sql plan management代替。（可以产生计划基线，确保性能和稳定性）

一、使用plan stability保留执行计划
计划稳定器（plan stability）确保数据库环境的改变不会影响应用的性能。包括统计信息的修改、优化方式的修改以及影响内存结构的参数修改（sort_area_size、bitmap_megre_area_size等）。当不希望应用的性能发生变化时可以使用该特性。

计划稳定器保留了outlines中存储的执行计划。outlines（概要信息）是与sql语句等义的一系列优化器hint。如果语句的outlines开启，数据库自动存储hints并根据hints生成执行计划。
oracle中可以为一或多条语句创建共有或私有outline。然后当开启outlines时优化器生成等价的执行计划。还可以将outlines分类，指定使用哪类outlines来简化管理。
数据库小版本的升级以及环境的修改不影响outlines。

1.在plan stability中使用hints
plan stability控制执行计划的程度取决于hint机制，因为oracle使用hints来记录计划的。
sql text和outline之间是一对一的关系。如果语句中的字面值不一样，也会应用不同的outline。为避免这种情况，可以使用绑定变量。

当outline是使用create_Stored_outlines参数生成，而非create outline语句生成时，oracle可以将字面值替换为系统生成的绑定变量来允许相似语句共享sql。

计划稳定器依赖于某一个满足性能要求的时间点的执行计划。而很多情况下，dates、order numbers等数据类型经常变化，这时固定使用的执行计划可能会造成性能随着数据特性的变化而变差。
因此，依赖于动态环境下保留计划的技术有时候背离了使用查询优化器的目的。查询优化器尝试基于准确反映数据状态的统计信息来生成执行计划。那么就必须在控制计划稳定性和数据类型改变之间取得平衡（you must balance the need to control plan stability with the benefit obtained from the optimizer's ability to adjust to changes in data characteristics.）

outlines如何使用hints
outline存储的主要是一些hints信息，这些hints等价于经过优化的特定语句的执行计划。当创建一个outline是，plan stability用与生成执行计划相同的数据检测优化结果。也就是说，oracle使用执行计划的输入信息生成outline，而不是执行计划本身。

存储outlines
在OL$\OL$HINTS\OL$NODES表中存储outline数据。oracle不会自动删除这些信息。
在缓存执行计划上，outlines唯一的影响就是数据库除了使用sql text，还会使用outline category name来决定计划是否在缓存中。这确保了oracle不会在需要其他分类的情况下使用在另一种分类下为该sql语句编译的执行计划。

开启plan stability
设置suffix_enabled结尾的参数：
■QUERY_REWRITE_ENABLED
■STAR_TRANSFORMATION_ENABLED
■OPTIMIZER_FEATURES_ENABLE

使用系统包管理outlines：
dbms_outln和dbms_outln_edit
相关方法有：
clear_used 清除特定outline
drop_by_cat 删除一类outlines
update_by_cat 修改一类
exact_text_signatures 改名
generate_signature 命名

2.创建outlines
oracle自动为所有的sql创建outlines，我们也可以为特定sql创建。outlines的输入信息是从优化器得到的。
当create_storeD_outlnes=true时，oracle自动创建outlines。手工创建的语句是create outline……
当创建或修改outline是，outline数据被写入到system的全局临时表中，这些表通过OL$\OL$HINTS\OL$NODES访问。

为outlines分类
为便于管理，可以对outlines分类。create outline语句可以指定分类。如果没指定，就是default类。
当用create_stored_outlines参数指定了一个分类后，所有已创建的outlines如果未重设，都是此分类下的。将该参数设置为false则终止outline生成。

如果create_stored_outlines设置为true或者使用不带分类名的create outline语句，oracle将其归在default类下。

3.使用outlines
将user_stored_outlines设置为true后，oracle在编译sql语句时就会存储outlines。这些outlines归为default类中。如果用use_stored_outlines参数设置了类，oracle使用这个分类名直到重设该分类名或将use_stored_oulines设置为false为止。如果制定了一个分类名，oracle没有找到匹配此分类的语句，就会在default类中查找。
alter outline可以明确使用或禁用特定outline、重命名、重设或重新生成一个outline。
use_private_outlines指定了只控制当前会话和用户的outline。本回话中的修改在在其他会话中不可见。保存后才可见。

通过v$sql视图的outline_category列可以查看数据库是否使用了outline。如果用了，则该列显示分类名，否则为空。

4.查看outlines信息
user/all/dba_outliness
user/all/dba_outline_hints

5.移动outline表
oracle根据OL$\OL$HINTS表分别为各个用户创建了user_outlines、user_outline_hints视图。这些表是OUTLN用户下的。
默认情况下OUTLN用户的对象存储在system表空间，如果outlines使用了大量空间，可以将其移走。
方法为：创建一个独立的表空间，并将outline表移进去。

如果开启了create_stored_outlines参数，且应用中有很多使用字面值的sql语句，system表空间可能会耗尽。可以使用dbms_outln.drop_usused删除有字面值的outlines。

移动outline表的步骤
1）用expdp命令导出outln用户下的OL$\OL$HINTS\OL$NODES:
% expdp outln DIRECTORY=outln_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES Password: password
2)以OUTLN用户登录数据库
3）删除以上这三个表
4）创建新的表空间
CREATE TABLESPACE outln_ts DATAFILE 'tspace.dat' SIZE 2M
DEFAULT STORAGE ( INITIAL 10K NEXT 20K MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10 ) ONLINE;
5）为OUTLN用户更改默认表空间
ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;
6）为了使表正常导入新的表空间，进行如下操作：
将system表空间OUTLN用户的配额设置为OK
撤销unlimited tablespace权限和所有角色
设置新建立的表空间的配额
7）使用导入命令导入：
% impdp outln DIRECTORY=outln_dir DUMPFILE=exp.dmp TABLES=OL$,OL$HINTS,OL$NODES Password: password
8）适当地为OUTLN用户增加权限

二、使用plan stability进行查询优化器的升级
利用查询优化器的功能可以使性能显著提高。系统升级时，plan stability可以在确保系统执行计划满足性能要求的同时在sql语句上充分利用新的优化器特性。
当迁移后某些特定语句发生了性能问题时，可以为其打开stored outlin，作为一种恢复原行为的方法。
由于其阻止了计划根据数据的属性进行调整，stored outlines不一定是迁移过程中性能问题最好的解决方法，但至少可以作为一种定位问题的方式。

1.从RBO想查询优化器移动
RBO时代很多的sql性能优化都需要手工调整，可以使用plan stability促使从RBO升级到quer optimization过程中性能稳定

在切换到查询优化器之前为应用创建（RBO环境下的）outlines，则切换后语句生成新计划的同时，旧的计划仍可用。
以下是创建步骤，使用前需要特别小心并仔细考虑。
1）确保用户有create any outline的权限
2）为outlines命名，如：
ALTER SESSION SET CREATE_STORED_OUTLINES = rbocat;
3）应用运行足够长的时间，确保所有重要的sql语句记录到stored outlines
4）中止outline生成：
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
5）用dbms_stats包收集统计信息
6）将参数optimizer_mode设置为choose
7）指定数据库使用刚刚生成的分类中的outline（本例中是RBOCAT）：
ALTER SESSION SET USE_STORED_OUTLINES = rbocat;
8）开启应用

plan stability的限制项、应用中sql语句的access paths不要修改

2.在查询优化器模式下（CBO）升级oracle版本
query optimization模式下升级oracle版本，由于优化器的修改，可能造成一些sql语句的执行计划修改。但有时候，我们决定系统运行已经很好了，不想冒险更改其执行计划，这时可以在升级前使用下面的步骤创建outlines：
1）允许outlines的创建：
ALTER SESSION SET CREATE_STORED_OUTLINES = ALL_QUERIES;
2）运行应用很长时间以确保所有关键sql被记录到stored outlines中
3）结束outline的生成
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
4）升级到新的笨笨
5）运行应用

对有问题的sql，进行如下操作来使用stored outlines：
1）对有问题的sql，将相关的stored outline分类改为一个相似的名字:
ALTER OUTLINE outline_name CHANGE CATEGORY TO problemcat;
2)ALTER SESSION SET USE_STORED_OUTLINES = problemcat;

第二十一章 使用应用跟踪工具
端到端应用跟踪
trcsess utility
sql trace 和tkprof

一、端到端的跟踪
包括几个层次：
client identifier
service
module
action
session
instance
可以使用dbms_monitor包开启，如：
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(client_id => 'OE.OE');
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE(client_id => 'OE.OE');

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'ACCTG',
module_name => 'PAYROLL');
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'ACCTG',
module_name => 'GLEDGER', action_name => 'INSERT ITEM');

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(service_name => 'ACCTG',
module_name => 'GLEDGER', action_name => 'INSERT ITEM');

通过dba_enabled_aggregations可以查看全局的统计信息
v$service_stats
v$serv_mod_act_stats
v$servicemetric

端对端跟踪的启禁用：
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'OE.OE',
waits => TRUE, binds => FALSE);
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'OE.OE');

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG',
waits => TRUE, binds => FALSE, instance_name => 'inst1');

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG',
module_name => 'PAYROLL', waits => TRUE, binds => FALSE,
instance_name => 'inst1');

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'ACCTG',
instance_name => 'inst1');

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'ACCTG',
module_name => 'PAYROLL', instance_name => 'inst1');

如果是会话，
SELECT SID, SERIAL#, USERNAME FROM V$SESSION;

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,
waits => TRUE, binds => FALSE);

二、使用trcsess utility
用trcsess命令，根据Session ID、Client ID、Service Name、Action name、module name之一生成trace文件
语法：
trcsess [output=output_file_name]
[session=session_id]
[clientid=client_id]
[service=service_name]
[action=action_name]
[module=module_name]
[trace_files]

三、sql trace 和tkprof
跟踪文件为每条语句生成如下统计信息：
  Parse, execute, and fetch counts
■CPU and elapsed times
■Physical reads and logical reads
■Number of rows processed
■Misses on the library cache
■Username under which each parse occurred
■Each commit and rollback
■Wait event data for each SQL statement, and a summary for each trace file
■Row operations showing the actual execution plan of each SQL statement
■Number of rows, number of consistent reads, number of physical

tkprof除了可以格式化跟踪文件，将其输出到容易查看的文件外，还可以创建存储统计信息的脚本，决定sql语句的执行计划。

四、使用sql trace facility 和tkprof
步骤：
1 为跟踪文件管理设置初始化参数
1）TIMED_STATISTICS, MAX_DUMP_FILE_SIZE, USER_DUMP_DEST
2）考虑如何识别跟踪文件，如：
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'my_trace_id';
在通过设置的名字查找
3）如果系统使用多版本的文件，确保最高版本限制高于要生成跟踪信息的sql所在文件版本
4）确保生成的跟踪文件在tkprof处理前可以被操作（有权限）

2 enabling the sql trace facility
三种方式：
■DBMS_MONITOR.DATABASE_TRACE_ENABLE procedure (recommended)
■DBMS_SESSION.SET_SQL_TRACE procedure
■ALTER SESSION SET SQL_TRACE = TRUE;

3 用tkprof格式化跟踪文件

语法：tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

例：TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10
生成耗用物理i/o最多的前10条sql；

TKPROF examp12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)

4 解释tkprof结果 见526页
五、避免tkprof的陷阱
1.避免有争议的陷阱
explain plan不能获得绑定变量的数据类型，因此都假设为varchar。如果是数字或日期类型，就可能出问题。这时候应该显式地进行转换。

2.避免一致性读的陷阱

3.避免模式陷阱

4.避免时间陷阱