1.oracle的sql trace实用程序
1.1对简单查询使用SQL_TRACE的简单步骤
1）初始化参数
SQL_TRACE --在10G中SQL_TRACE是不建议（向下兼容、将来不用）使用参数。
TIMED_STATISTICS=TRUE --收集时间统计
MAX_DUMP_FILE_SIZE=unlimited
USER_DUMP_DEST
2）对SQL*Plus会话启用SQL_TRACE
alter session set SQL_TRACE true;
alter session set SQL_TRACE false;
3）查找跟踪文件
orcl_ora_3372.trc--3372是进程ID号（SPID）
select spid,s.sid,s.serial#,p.username,p.program
from v$process p,v$session s
where p.addr=s.paddr
and s.sid=(select sid from v$mystat where rownum=1) ;
也可以在里面放置一个标记，如：select 'rich1' from dual
4）tkprof
[oracle@db1 ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
5）tkprof示例
tkprof ora_7792.trc 7792.txt explain=system//manager sort=(FCHCPU,EXECPU,PRSCPU) print=5
tkprof ora_7792.trc 7792.txt sort=parm1 sort=parm2
【技巧】所跟踪的查询如果有大量物理读取，通常表明缺少索引。disk列显示了物理读取次数（通常都是没有使用索引的情况），而把query列加到current列所得
到的值就表明总的块读取量（物理读取包括在这个数量中）。一个查询如果有大量查询读取和较少的磁盘读取，就说明使用了索引。但如果查询读取过高，就表明
存在一个差的索引或差的表连接序列。一个查询如果有大量的当前读取量，通常表明它是一个大的DML(UPDATE、INSERT、DELETE)查询。
【附：sql trace与autotrace的异同】
1.sql trace 与autotrace，都可以看执行计划，sql_trace的执行计划不是实时的，autotrace的执行计划是实时的；
2.sql_trace的统计信息更详细
3.sql_trace可以嵌入计划任务中，autotrace只能实时执行
4.sql_trace的执行计划记录在跟踪文件中，需要使用tkprof翻译执行计划，而autotrace的执行计划是直接阅读的。
5.sql_trace可以设置为实例级，autotrace只能设置为会话级。
2. TRACE 输出部分
2.1 SQL语句
2.2 统计部分
该部分由8个列组成，第一列（call）就是对数据库的调用类型。有3种调用方式：分析（parse）、执行（execute）和取出（fetch）。每种调用方式都生成一行单独的统计。
count：这个类型的调用次数；
cpu：这条语句中所有这种类型调用的总CPU时间。
elapsed:这个调用的总消耗时间
disk：为满足这次调用而从磁盘检索的数据块数目。这也就是物理读取数
query：进行这个类型调用时从内存检索的数据缓冲区数目。select语句通常在这个模式下检索缓冲区。这也就是一致get数。
current：进行这个类型调用时从内存检索的数据缓冲区数目。在这个模式下，UPDATE、insert、delete通常访问缓冲区，而select语句也会使用少量的缓冲区。
这也是数据库块gets数
rows：这条语句处理的总行数。select语句所处理的行数将出现在fetch统计中。insert、update和delete都出现在execute行中。
2.3 信息部分
信息部分包含了有关分析和执行调用中丢失的库缓存的数量的信息。如果这个丢失率很高，这就是说共享池的大小出了问题。应当对库缓存的命中率和重载率进行检查。
这部分显示了最近分析这条语句的用户的用户名。还有一些关于当前优化器模式设置的信息。
2.4 行源操作部分
行源操作部分列出了交叉引用操作中涉及的行的数目。(us=0.001ms)
【技巧】注意，跟踪文件是系统在某一个特定时刻的实时反映。相反，EXPLAIN PLAN是在分析TKPROF程序清单时生成的，有一定的延迟。行源操作程序清单也是
跟踪文件的一部分，并且可以用来查看是否有数据库对象在执行跟踪后发生了变化。
2.5 EXPLAIN PLAN
这部分的TKPROF信息最有用。
3.更复杂的TKPROF输出
【查看TKPROF输出时可能发现的问题】
分析数字太大：应该增大SHARED_POOL_SIZE
磁盘读取量太高：没有使用索引或根本就不存在索引
query和/或current列值（内存读取）太高：索引位于低基数的列上（由一个值组成了表中大部分记录的列;比如y/n字段）。删除/限制索引，或使用直方图或位图索引或许
可以提高性能。表连接顺序或连接索引的顺序不好也会发生这个情况。
分析所需要的时间太多：可能是开放游标的数量有问题；
EXPLAIN PLAN里某一行语句要处理的行数相对于其他行语句而言太多：这可能表明有一个索引对唯一键（一个列上的唯一值）进行了较差的分布。还可能说明语句写得太差。
在分析期间库缓存里MISSES值大于1：这表明需要重载这条语句。可以增大init.ora文件中的SHARED_POOL_SIZE值，或者执行一次较好的共享SQL任务。
4.深入探讨TKPROF输出
通过比较TKPROF输出和实际对象的物理特征，我们就可以了解到ORACLE的工作原理。
【实际对象的物理特征】
select sum(blocks) from dba_segments where segment_name='CUSTOMER';--已分配的块
select blocks,empty_blocks from dba_tables where table_name='CUSTOMER';--已使用的块、未使用的块
以上结果与TKPROF的输出比较，可以知道ORACLE执行的是全表扫描，还是索引检索。
5.使用DBMS_MONITOR(10G的新特性)
在具有连接池或共享服务器的多层环境中，一个会话可以跨越多个进程，甚至跨越多个实例。
需要具有DBA角色才可以使用DBMS_MONITOR
【端对端的应用程序跟踪可以基于如下】
会话：基于会话ID（SID）和序列号。
客户端标识符：允许跨越多个会话设置跟踪。基于登录ID指定终端用户。使用DBMS_SESSION.SET_IDENTIFIER过程设置该值。
实例：基于实例名指定给定的实例。
服务名：指定一组相关的应用程序。使用DBMS_SERVICE.CREATE_SERVICE过程设置该值。
模块名：开发人员在其应用程序代码中使用DBMS_APPLICATION_INFO.SET_MODULE过程设置该值。使用该名称表示执行的模块或代码。
操作名：开发人员在其应用程序代码中使用DBMS_APPLICATION_INFO.SET_ACTION过程设置该值。使用该名称表示模块或代码。
最后3个跟踪选项在层次上关联；不可以在没有指定模块名和服务名的情况下就指定操作名，但是可以只指定服务名，或者只指定服务名和模块名。
5.1 基于会话ID和序列号设置跟踪
1）确定需要跟踪的会话的SID和序列号
select sid,serial#,username from v$session;
2）启用跟踪
dbms_monitor.session_trace_enable(156,3588,true,false);
第三个参数用于等待（默认为TRUE），第四个参数用于绑定变量（默认为FALSE）。
3）关闭跟踪
dbms_monitor.session_trace_disable(156,3588);
4）跟踪当前的会话
dbms_monitor.session_trace_enalbe(null,null);
5.2 基于客户端标识符设置跟踪
1）设置标识符
dbms_session.set_identifier('bryan id');
--为了验证客户端的标识符，可执行如下语句：
select sid,serial#,username,client_identifier
from v$session
where client_identifier is not null;
2）设置跟踪
dbms_monitor.client_id_trace_enable('bryan id',true,false);
3）禁用跟踪
dbms_monitor.client_id_trace_disable('bryan id');
5.3 设置服务名/模块名/操作名的跟踪
--启用服务名
dbms_monitor.serv_mod_act_trace_enable(service_name => 'ebk2');
--启用服务名、模块名、操作名的组合
dbms_monitor.serv_mod_act_trace_enable(service_name => 'ebk2',module_name => 'salary_update',action_name => 'insert_item');
--禁用
dbms_monitor.serv_mod_act_trace_disable(service_name => 'ebk2',module_name => 'salary_update',action_name => 'insert_item');
5.4启用跟踪视图
DBA_ENABLED_TRACES
DBA_ENABLED_AGGREGATIONS
【附：DBMS_MONITOR与SQL TRACE应用比较】
1.SQL TRACE一般是用来跟踪当前的会话；
2.DBMS_MONITOR可以通过sid、序列号，跟踪任意会话;
6. 使用TRCSESS将多个跟踪文件保存到一个文件中（10g的新特征）
可以有选择性地从多个跟踪文件中提取跟踪数据，并且将这些跟踪数据基于会话ID或模块名等标准保存到一个跟踪文件中。这个命令行实用程序在连接池
和共享服务器配置中特别有用。
[oracle@db1 ~]$ trcsess
trcsess [output=
7. 单独使用EXPLAIN PLAN
EXPLAIN PLAN不用真正地执行SQL语句--它只是列出了所要使用的计划，并把这个执行计划插入到一个ORACLE表中(plan_table)。在使用EXPLAIN PLAN命令之前，执行该
命令的ORACLE账户必须先执行一个utlxplan.sql文件（ORACLE_HOME/rdbms/admin）。
经测试：使用create user创建用户后，可以直接访问plan_table.
【查看PLAN_TABLE】
select * from table(dbms_xplan.display());
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
select * from table(dbms_xplan.display_cursor('bd502nbh9abbs'));
select * from table(dbms_xplan.display_awr('cqgv56fmuj63x'));
【使用EXPLAIN PLAN】
explain plan
for
select *
from customer
where customer_number=111;
或
explain plan
set statement_id='customer'--区分大小写
for
select *
from customer
where customer_number=111;
【查看EXPLAIN PLAN--使用自定义SQL】
select lpad(' ',2*(level-1))||operation || ' ' || options || ' ' || object_name || ' ' ||decode(id,0,'Cost = '||position) "query plan"
from plan_table
start with id=0 and statement_id='query1'
connect by prior id=parent_id and 'query1';
8. EXPLAIN PLAN--从上至下读取和从下至上读取
--从上向下读的例子
select lpad(' ',2*(level-1))||level||'.'||decode(id,0,0,position)||operation || ' ' || options || ' ' || object_name || ' ' ||decode(id,0,'Cost = '||position) "query plan"
from plan_table
start with id=0 and statement_id='query3'
connect by prior id=parent_id and statement_id='query3';
---------------------------------------------------------------
query plan
1 1.0SELECT STATEMENT Cost = 4
2 2.1NESTED LOOPS
3 3.1TABLE ACCESS BY INDEX ROWID DEPT_SORT
4 4.1INDEX UNIQUE SCAN IND_DEP
5 3.2TABLE ACCESS BY INDEX ROWID DEPT
6 4.2INDEX RANGE SCAN IDX_DE
---------------------------------------------------------------
【运行顺序】执行计划的运行顺序为：4.1-3.1-4.2-3.2-2.1-1.0
9. 阅读EXPLAIN PLAN
9.1启用AUTOTRACE
运行AUTOTRACE，用户必须是PLUSTRACE角色（运行plustrce.sql，通常位于ORACLE_HOME/rdbms（或sqlplus）/admin文件夹下）。
--先运行plustrce.sql增加PLUSTRACE角色，然后grant PLUSTRACE to username;
【技巧】如果在设置AUTOTRACE时出现了unable to verify plan table format or existence错误，必须使用utlxplan.sql脚本来创建一个计划表。
【警告】如果AUTOTRACE不能用于查询系统视图，可能是因为用户没有查看底层对象的权限。
【AUTOTRACE选项】 set autot on [exp] [stat] ; set autot trace [exp] [stat]
9.2在分区模式下使用的EXPLAIN PLAN
【示例】
1）创表
create table dept1
(deptno number(2),
dept_name varchar2(30))
partition by range(deptno)
(partition d1 values less than(10),
partition d2 values less than(20),
partition d3 values less than(maxvalue));
insert into dept1 values (1,'dept 1');
insert into dept1 values(7,'dept 7');
insert into dept1 values(10,'dept 10');
insert into dept1 values(15,'dept 15');
insert into dept1 values(22,'dept 22');
commit;
create index dept_idx on dept1(deptno) local(partition d1,partition d2,partition d3);
2）强制全表扫描
explain plan for
select dept_name
from dept1
where deptno||''=1 or deptno||'' =15;
select operation,options,id,object_name,partition_start,partition_stop
from plan_table;
-------------------------------------------------------------------------
OPERATION OPTIONS ID OBJECT_NAME PARTITION_START PARTITION_STOP
1 SELECT STATEMENT 0
2 PARTITION RANGE ALL 1 1 3
3 TABLE ACCESS FULL 2 DEPT1 1 3
-------------------------------------------------------------------------
select * from table(dbms_xplan.display());
-------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
1 Plan hash value: 2415070041
2
3 ---------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
5 ---------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 2 | 60 | 5 (0)| 00:00:01 | | |
7 | 1 | PARTITION RANGE ALL| | 2 | 60 | 5 (0)| 00:00:01 | 1 | 3 |
8 |* 2 | TABLE ACCESS FULL | DEPT1 | 2 | 60 | 5 (0)| 00:00:01 | 1 | 3 |
9 ---------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - filter(TO_NUMBER(TO_CHAR("DEPTNO")||'')=1 OR
15 TO_NUMBER(TO_CHAR("DEPTNO")||'')=15)
16
17 Note
18 -----
19 - dynamic sampling used for this statement
-------------------------------------------------------------------------
3）索引范围扫描
explain plan for
select dept_name
from dept1
where deptno =15;
select operation,options,id,object_name,partition_start,partition_stop
from plan_table;
select * from table(dbms_xplan.display());
-------------------------------------------------------------------------
OPERATION OPTIONS ID OBJECT_NAME PARTITION_START PARTITION_STOP
1 SELECT STATEMENT 0
2 PARTITION RANGE SINGLE 1 2 2
3 TABLE ACCESS BY LOCAL INDEX ROWID 2 DEPT1 2 2
4 INDEX RANGE SCAN 3 DEPT_IDX 2 2
-------------------------------------------------------------------------
【分区模式小结】
1）经测试，以下三种方法展示的执行计划，都包括分区信息
select operation,options,id,object_name,partition_start,partition_stop from plan_table;
select * from table(dbms_xplan.display());
set autotrace on
2）PL/SQL DEVELOPER中的执行计划，不包含分区信息
9.3在不使用TRACE的情况下查找大的硬盘和/或内存读取量
除了跟踪查询处，可以使用V$SQLAREA查找出有问题的磁盘和内存读取信息。
--磁盘读取
select disk_reads,sql_text
from v$sqlarea t
where t.DISK_READS>100
order by disk_reads desc;
--内存读取
select buffer_gets,sql_text
from v$sqlarea t
where t.BUFFER_GETS>5000
order by disk_reads desc;
10.使用DBMS_XPLAN
在Oracle9i和以后的版本中，Oracle提供了查看Explain Plan的较为容易的方法。现在可以使用DBMS_XPLAN查询执行计划。使用该程序包的一些注意事项如下：
该程序包自动查询PLAN_TABLE中的最后一个计划。
该程序包使用TABLE()函数和另一个管道函数。
文本截取操作可能存在问题。
该程序包将在计划后提供额外的信息，包括如下：
如果是当前计划表，则突出显示过滤器和连接条件。
当使用计划表的老版本时，显示警告消息。
示例：
select * from table(dbms_xplan.display);
11.另一种EXPLAIN PLAN输出方法：构建树结构
explain plan
set statement_id='query3'
for
select *
from dept t
inner join dept_sort s on(s.deptno=t.deptno)
where t.deptno=100;
select lpad(' ',2*(level-1))||level||'.'||decode(id,0,0,position)||operation "operation", options ,
decode(to_char(id),'','COST='||nvl(to_char(position),'n/a'),object_name) "object_name" ,
id||'-'||nvl(parent_id,0)||'-'||nvl(position,0) "ORDER",substr(optimizer,1,7) "OPT"
from plan_table
start with id=0 and statement_id='query3'
connect by prior id=parent_id and statement_id='query3';
-----------------------------------------------------------------------
operation OPTIONS object_name ORDER OPT
1 1.0SELECT STATEMENT 0-0-4 ALL_ROW
2 2.1NESTED LOOPS 1-0-1
3 3.1TABLE ACCESS BY INDEX ROWID DEPT_SORT 2-1-1 ANALYZE
4 4.1INDEX UNIQUE SCAN IND_DEP 3-2-1 ANALYZE
5 3.2TABLE ACCESS BY INDEX ROWID DEPT 4-1-2 ANALYZE
6 4.1INDEX RANGE SCAN IDX_DE 5-4-1 ANALYZE
-----------------------------------------------------------------------
【构建执行树】根据所述的执行计划，可以构建一个执行树，这样可以更好地了解Oracle如何处理语句。为了构建这个树，只要从第1步开始，找到那些父步骤
为1的所有子步骤，并把它们画入树中即可。重复这一操作，直到画入了全部的步骤。
执行顺序由这些步骤的父/子关系决定。基本上总是先执行子步骤（至少一次），然后由此转换到父步骤。当一个父步骤有很多子步骤时，子步骤就按按照步骤位置（也就
是执行计划里ORDER列所显示的第三个数字的顺序来执行）。当构建执行树时，对于父步骤来说较低位置的子步骤应该从左到右排序，执行树会从左到右、从下到上进行读取。
【注】上面描述的读取顺序是“树的后根遍历”。
用L、R、D表示二叉树的的左子叶、右子叶、根，那么LRD、LDR、DLR分别代表二叉树的后根遍历、中根遍历、先根遍历。二叉树只是树结构的一个特例。
14.PLAN_TABLE表中的重要列
STATEMENT_ID 在EXPLAIN PLAN语句里指定STATEMENT_ID参数的值。
TIMESTAMP 执行EXPLAIN PLAN语句所出现的日期和时间。
REMARKS 与每步EXPLAIN PLAN相关的注释（可以达到80字节）。如果需要在PLAN_TABLE的任何行添加或修改注释，则可使用UPDATE语句来修改PLAN_TABLE表的行。
OPERATION 在这步操作所执行的内部操作名称。在为这个语句生成的第一行里，这个列包含了下面4个值的一个：DELETE、INSERT、SELECT或UPDATE，这取决于这个语句的类型。
OPTIONS【技巧】表PLAN_TABLE的OPERATION和OPTIONS列对调整查询非常重要。OPERATION列显示了所执行的实际操作（包括连接类型），
而OPTIONS列告诉您什么时候执行（可能需要索引）全表扫描。
OBJECT_NODE ：用来引用对象的数据库链接名（表或视图名称）。对于使用了并行查询选项的本地查询来说，这个列描述了执行操作的输出顺序。
-----------------------------------------------------------------------------------------------------------------------
REMARKS OPERATION OPTIONS OBJECT_NODE
1 SELECT STATEMENT REMOTE
2
3 TABLE ACCESS BY INDEX ROWID ORA10G1.REGRESS.RDBMS.DEV.US.ORACLE.COM
4 INDEX UNIQUE SCAN ORA10G1.REGRESS.RDBMS.DEV.US.ORACLE.COM
5 TABLE ACCESS BY INDEX ROWID ORA10G1.REGRESS.RDBMS.DEV.US.ORACLE.COM
6 INDEX RANGE SCAN ORA10G1.REGRESS.RDBMS.DEV.US.ORACLE.COM
-----------------------------------------------------------------------------------------------------------------------
OBJECT_OWNER ：拥有包含表或索引的模式的用户名。
OBJECT_NAME:索引或表的名称。
OBJECT_INSTANCE：对象在最初语句里出现时与对象所处位置相对应的数字。按从左到右、从外到内的顺序计数，考虑到最初的语句文本。注意，视图扩展会产生不可预期的数字。
OBJECT_TYPE：提供与对象相关描述信息的修改器，例如，索引的NON-UNIQUE。
OPTIMIZER：优化器的当前模式。
ID：分配给执行计划中每一步骤的数字。
PARENT_ID：对此ID步骤的输出结果执行操作的下一步执行步骤的ID。
POSITION：对那些拥有相同PARENT_ID的步骤的处理顺序。
OTHER：用户认为有用的针对执行步骤的其他信息。
OTHER_TAG：OTHER列的内容。
COST：利用优化器的基于成本的方法所估算的操作成本。这个列的值并没有任何特别重要的可供测试的单元；它只是用来与执行计划的成本相比的权重值。
CARDINALITY：基于成本的方法所估算这个操作的访问行数。
BYTES：基于成本的方法估算这个操作所访问的字节数。
【技巧】当估测应如何调整查询时，BYTES列就特别重要。如果使用了索引并且字节数非常大时，这就表明使用全表扫描会更有效率（也就是说，读取索引和数据的成本要比
只在全表扫描里检索数据的成本更大）。同时，字节数还可以帮助我们判断哪个表应该在查询时先被访问（驱动表），因为有些表可能会限制其他表需要的字节数。
【技巧】记住查询中的COST和BYTES值都是估算出来的。成本或字节数估算值较高的查询可能比估算值较低的查询运行得快。
15.Oracle支持的一些有用的程序包
select sid,serial# from v$session where username='SCOTT';
--跟踪指定会话
dbms_system.set_sql_trace_in_session(9,190,true);
--跟踪当前会话
dbms_session.set_sql_trace(true);
16.适用于未记录入档的TRACE操作的初始参数
select ksppinm "parameter Name",ksppstvl "value",ksppstdf "default"
from x$ksppi x,x$ksppcv y
where x.indx=y.indx
and ksppinm like '/_%trace%' escape '/';
【技巧】X$ksppi表只能由SYS用户访问。
16.1在Oracle里跟踪错误可以得到更多信息
alter session set events='4031 trace name errorstack level 4';
16.2通过启用事件来跟踪
alter session set events '10046 trace name context forever,level 1';
level的值可以为1（常规的跟踪）、4（跟踪绑定变量）、8（跟踪等待状态）或12（常规的跟踪、跟踪绑定变量和跟踪等待状态）。
alter session set events '10046 trace name context off';
17.使用存储纲要
STORED OUTLINES(存储纲要),允许在查询运行的任意时间内使用先前决定好的执行计划。
17.1设置stored Outlines
【使用STORED OUTLINES所需要的特权】
CREATE ANY OUTLINE
EXECUTE_CATALOG(使用DBMS_OUTLN程序包)
PLUSTRACE(如果应用，使用AUTOTRACE）
【需要设置的参数】
QUERY_REWRITE_ENABLED=TRUE
STAR_TRANSFORMATION_ENABLED=TRUE
OPTIMIZER_FEATURES_ENABLED=10.1.0(举例来说)
USE_STORED_OUTLINES=TRUE(使用已有的STORED OUTLINES)
CREATE_STORED_OUTLINES=TRUE(创建或编辑STORED OUTLINES)
USE_PRIVATE_OUTLINES=TRUE（使用专用纲要，只用于当前的会话）
17.2如何存储纲要
STORED OUTLINES的主要视图：
USER_OUTLINES
USER_OUTLIN_HINTS
可以在自己的模式里创建纲要表（这是个好主意）。如果没用，这些纲要将被存储到SYSTEM表空间里（这通常是个错误）。利用DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES过程里的命令
可以在当前模式里创建纲要表：
exec dbms_outln_edit.create_edit_tables
17.3创建和使用存储纲要
有两种STORED OUTLINES:会话特有的专用（private)类型和可以影响整个数据库的公共(public)类型。使用哪一种纲要由会话参数USE_PRIVATE_OUTLINES的设置决定：
如果参数被设为TRUE，则使用专用纲要。通常，除非要生成优化的执行计划，否则最好还是使用专用纲要。专用纲要可以用CREATE OR REPLACE PRICATE OUTLINE命令以公
共方式存储；公共纲要可以使用CREATE OR REPLACE OUTLINE ...FROM PRIVATE ..命令来由专用纲要创建。这个过程又叫做编辑，可以把一个已有的专用纲要复制成公有纲要。
【技巧】Oracle可以对每个查询运用STORED OUTLINES来查询执行计划。为了对某个查询执行该功能，这个查询必须与原先存储的查询完全相同。稍许的改变都会导致Oracle
认为这两个查询不一样，从而不使用这个纲要。这个规则就跟游标共享一样，Oracle用于通过共享池分析查询。
建立纲要方法：
1）设置会话参数CREATE_STORED_OUTLINES=true
可以为每个执行的查询输出一个纲要（每个纲要的名字会加上SYS前缀）。
2）使用CREATE OUTLINE来得到某个查询的纲要
create or replace outline pb_outline on
select e.last_name,e.salary
from s_emp e
where userid='lngao';
Oracle提供了很多处理STORED_OUTLINES的程序包。在使用STORED_OUTLINES时，可以对DBMS_OUTLN和DBMS_OUTLN_EDIT程序包研究其他的可能性。
17.4删除存储纲要
--删除所有没有用过的纲要
execute dbms_outln.drop_unused
--删除已经使用过的纲要
dbms_outln_clear_used过程，它可以接收纲要名称（来自USER_OUTLINES视图）。
--判断纲要是否被使用过：
1）检查USER_OUTLINES里的USED列
2）检查V$SQL视图的OUTLINE_CATEGORY列，如：
select outline_category,outline_sid
from v$sql
where sql_text='portion of query%';
18.使用Plan Stability（存储纲要）
【STORED OUTLINES示例】
analyze table emp compute statistics;
alter session set query_rewrite_enabled =true;
alter session set use_stored_outlines =true;
alter session set star_transformation_enabled =true;
alter session set create_stored_outlines =true;
alter session set use_private_outlines =true;
--first create the public outline without a hint(user the index on userid)
create or replace outline pb_outline on
select e.empno from emp e where e.empno='7902';
--create storage tables for private outlines
--OL$,OL$HINTS and OL$NODES
exec dbms_outln_edit.create_edit_tables;
create or replace private outline pr_outline from pb_outline;
--edit the ol$hints table
--use a full table scan rather than the index just to see if this works
update ol$hints
set hint_text='FULL(E)'
where hint#=6;
commit;
--resynch stored outline definition
--alter system flush shared_pool;
--or
create or replace private outline pr_outline from private pr_outline;
--this is probably a better option
--to test the new private outline
alter session set use_private_outlines=true;
set autotrace on;
select e.empno from emp e where e.empno='7902';
set autotrace off;
--make your changes permanent
create or replace outline pb_outline from private pr_outline;
--use the new public outline
alter session set use_private_outlines=false;
【在不同的会话使用STORED OUTLINES】
1.创建STORED OUTLINES
1）删除EMP的索引
2）
analyze table emp compute statistics;
--alter session set query_rewrite_enabled =true;
--alter session set use_stored_outlines =true;
--alter session set star_transformation_enabled =true;
alter session set create_stored_outlines =true;
--alter session set use_private_outlines =true;
--first create the public outline without a hint(user the index on userid)
create or replace outline pb_outline on
select e.empno from emp e where e.empno='7902';
2.使用STORED OUTLINES
1）在EMP.EMPNO上建索引
2）
analyze table emp compute statistics;
alter session set query_rewrite_enabled =true;
alter session set use_stored_outlines =true;
alter session set star_transformation_enabled =true;
alter session set create_stored_outlines =true;
alter session set use_private_outlines =true;
exec dbms_outln_edit.create_edit_tables;
create or replace private outline pr_outline from pb_outline;
set autotrace on;
select e.empno from emp e where e.empno='7902';
-----------------------------------------------------------------------
SQL> select e.empno from emp e where e.empno='7902';
EMPNO
----------
7902
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."EMPNO"=7902)
Note
-----
- outline "PR_OUTLINE" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
147 db block gets
35 consistent gets
0 physical reads
596 redo size
409 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
-----------------------------------------------------------------------
SQL> alter session set use_private_outlines =false;
Session altered.
SQL> select e.empno from emp e where e.empno='7902';
EMPNO
----------
7902
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."EMPNO"=7902)
Note
-----
- outline "PB_OUTLINE" used for this statement
Statistics
----------------------------------------------------------
291 recursive calls
3 db block gets
62 consistent gets
0 physical reads
812 redo size
409 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7901922/viewspace-1060015/ ，如需转载，请注明出处，否则将追究法律责任。
