- 数据库高效优化：架构、规范与SQL技巧
- 马立和 高振娇 韩锋
- 15566字
- 2020-08-27 05:47:11
3.3 执行计划操作
3.3.1 查看执行计划
对于执行计划的操作，最常见的就是查看执行计划，这是对SQL进行优化的前提条件。取得一个真实、准确、有效的执行计划是一个最基本的要求。然而，在Oracle中存在一定的不确定性，即我们看到的执行计划和真实执行的可能完全不同，这取决于我们采用的收集方法。下面我们会着重介绍几种执行计划的查看方法，大家需要区分不同方法的优缺点、适用情况等。
1.EXPLAIN PLAN
执行这条命令可以显示指定SQL语句的执行计划和相关信息，并将它们作为输出存储到一张数据表中，这张表称为计划表（plan table）。在使用这个命令之前，首先要确保计划表存在，否则会抛出错误。在不同版本的Oracle中，创建计划表的方式也不同。以11g为例，如果需要手工创建计划表，执行$ORACLE_HOME/rdbms/admin/utlxplan.sql脚本即可。Oracle默认创建的是名称为plan_table的计划表，我们也可以创建自己的计划表，结构和plan_table一致即可。需要注意的是，该工具的特点是并没有真正执行语句，实际的执行过程可能与Explain分析结果不一样。
下面简单介绍一下这个命令的使用方法，包括如何获取执行计划、如何查看执行计划及查询输出的含义。
（1）获取执行计划
获取执行计划的方法如下：
explain plan {set statement_id='<your ID>'}
{into table <table name>}
for <sql statement>
参数说明：
·sql statement：指定需要提供执行计划的SQL。支持select、insert、update、merge、delete、create table、create index和alter index等类型。
·statement_id：指定一个名字，用来区分存储在计划表中的多个执行计划。名字可以是包含任意字符的字符串，但是每个名字所含字符不能超过30个。这个参数为可选项，默认值是null。
·table_name：指定计划表的名字。这个参数可选，默认为plan_table。当需要的时候，甚至可以为它指定schema、db link等。
相关权限：数据库用户在执行explain plan语句时，必须对作为参数传递过来的SQL语句有执行权限。注意当参数里包含视图时，也需要该用户对视图所基于的基础表及视图有访问权限。
其他说明：
·EXPLAIN PLAN是DML语句，而不是DDL语句。这意味着该会话不会对当前事务进行隐式提交，仅仅是插入几条记录到计划表。
·如果只想看ORACLE采用什么执行计划，而不真正执行语句，可以使用explain plan命令。类似于set autotrace traceonly explain，也可以使用dbms_xplan。这个包也是读取plan_table表。
（2）查看执行计划
查看Explain目录的执行计划有很多种方法，笔者推荐使用DBMS_XPLAN包的方式。
·直接查询计划表。
explain plan set statement_id='query1' for select * from t1;
column plan format a70
select lpad (' ', 3*level) || operation || '(' || options ||') '|| object_name || ' ' ||object_type
from plan_table
connect by prior id=parent_id and statement_id='query1';
·调用dbms_xplan.display函数。
explain plan for select ...; select * from table(dbms_xplan.display) //在9iR2以后的版本中，可以使用这个方式查询，效果更好也更容易。函数display允许带有参数调用
·调用脚本。
explain plan for select ...; @ $ORACLE_HOME/rdbms/admin/utlxpls.sql //查看串行执行计划 @ $ORACLE_HOME/rdbms/admin/utlxplp.sql //查看并行执行计划 //9i下只能查看串行执行计划，10g既可以查看串行计划，也可以查看并行执行计划
（3）输出说明
在Explain的输出中，包含很多信息。辨析这些字段的具体含义十分重要。下面结合一个示例，针对主要的字段加以说明：
SQL> explain plan for select count(*) from t1; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3724264953 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 344 (1)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 85773 | 344 (1)| 00:00:05 | ------------------------------------------------------------------- 9 rows selected.
·COST：指cbo中这一步所耗费的资源，这个值是相对值。
·CARD：指计划中这一步所处理的行数。
·BYTES：指cbo中这一步处理的所有记录的字节数，是估算出来的一组值。
·ROWID：是一个伪列，对每个表都有一个ROWID的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其他列那样使用它，但是不能删除、修改列。一旦一行数据插入数据库，则ROWID在该行的生命周期内是唯一的，即使该行产生行迁移，行的ROWID也不会改变。
·RECURSIVE SQL：有时为了执行用户发出的一个SQL语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称为recursive calls。如当一个DDL语句发出后，ORACLE总是隐含发出一些RECURSIVE SQL语句，来修改数据字典信息，在需要的时候，ORACLE会自动在内部执行这些语句。当然DML语句与SELECT都可能引起RECURSIVE SQL。简单说，我们可以将触发器视为RECURSIVE SQL。
·ROW SOURCE：行源。用在查询中，由上一操作返回的符合条件的行的集合，既可以是表的全部行数据的集合，也可以是表的部分行数据的集合，还可以是对前两个row source进行连接操作（如join连接）后得到的行数据集合。
·PREDICATE：谓词。一个查询中的WHERE限制条件。
·DRIVING TABLE：驱动表，又称为外层表OUTER TABLE。这个概念用于嵌套与哈希连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。如果一个大表能够有效过滤数据（例如在WHERE条件有限制条件），则该大表作为驱动表也是合适的，所以并不是只有小表可以作为驱动表。正确说法应该为：应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，在后面描述中，一般将该表称为连接操作的row source 1。
·PROBED TABLE：被探查表。该表又称内层表INNER TABLE。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表（实际上应该为返回较大row source的表）且相应列上应该有索引。在后面的描述中，一般将该表称为连接操作的row source 2。
·CONCATENATED INDEX：组合索引。由多个列构成的索引，如create index idx_emp on emp(col1,col2)。在组合索引中有一个重要的概念——引导列（leading column）。在上面的例子中，col1列为引导列。当我们进行查询时可以使用"where col1=?"，也可以使用"where col1=?and col2=?"，两者都会使用索引，但是"where col2=?"查询就不会使用该索引。所以限制条件中包含引导列时，该限制条件才会使用该组合索引。
·SELECTIVITY：可选择性。比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。
2.AUTOTRACE
AUTOTRACE是较简单的一种获取执行计划的方式，往往也是最常用的方法。有一点需要注意：使用AUTOTRACE有多种选型，不同选型决定了是否真正执行这条SQL，其对应的执行计划有可能是真实的，也有可能是虚拟的，要加以区分。从本质上来讲，当使用AUTOTRACE时，Oracle实际上启动了两个会话连接。一个会话用于执行查询，另一个会话用于记录执行计划和输出最终的结果。如果进一步查询可以发现，这两个会话是由同一个进程派生出来的（一个进程对应多个会话）。
下面简单介绍一下这个命令的使用方法，包括必要的准备工作，不同选型的区别及查询输出的含义。
（1）准备工作
·创建plan table：
connect / as sysdba @ $ORACLE_HOME/rdbms/admin/utlxplan
·创建同义词：
create public synonym plan_table for plan_table;
·授权：
grant all on plan_table to public;
·创建plustrace角色：
@ $ORACLE_HOME/sqlplus/admin/plustrce.sql
·将plustrace角色授予public：
grant plustrace to public;
（2）常用选项
注意不同选型的区别，可根据自己的需要进行选择。
·SET AUTOTRACE OFF：不生成AUTOTRACE报告，这是默认模式。
·SET AUTOTRACE ON EXPLAIN：AUTOTRACE只显示优化器执行路径报告。
·SET AUTOTRACE ON STATISTICS：只显示执行统计信息。
·SET AUTOTRACE ON：包含执行计划和统计信息。执行完语句后，会显示explain plan与统计信息。这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的SQL语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。
·SET AUTOTRACE TRACEONLY：同SET AUTOTRACE ON，但是不显示查询输出。如果想得到执行计划，而不想看到语句产生的数据，可以采用这种方式。这样还是会执行语句。它与SET AUTOTRACE ON相比的优点是：不会显示出查询的数据，但是还是会将数据输出到客户端，这样当语句查询的数据比较多时，语句执行将会花费大量的时间，因为大部分时间用在将数据从数据库传到客户端上了。笔者一般不用这种方法。
·SET AUTOTRACE TRACEONLY EXPLAIN：如同用EXPLAIN PLAN命令。对于SELECT语句，不会执行语句，而只是产生执行计划。但是对于DML语句，还是会执行语句，不同版本的数据库可能会有小的差别。这样在优化执行时间较长的SELECT语句时，大大减少了优化时间，解决了“SET AUTOTRACE ON”与“SET AUTOTRACE TRACEONLY”命令优化时执行时间长的问题，但同时带来一个新的问题：不会产生statistics数据，而通过statistics数据的物理I/O的次数，我们可以简单地判断语句执行效率的优劣。
·SET AUTOTRACE TRACEONLY STATISTICS：和SET AUTOTRACE TRACEONLY一样，但只显示执行路径。
（3）输出说明
AUTOTRACE的输出有两个部分——查询计划报告和统计数据。下面结合一个示例进行说明：
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
86262
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 344 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 85773 | 344 (1)| 00:00:05 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
31 recursive calls
0 db block gets
1275 consistent gets
1234 physical reads
0 redo size
536 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
查询计划报告如下：
SELECT STATEMENT OPTIMIZER=CHOOSE(Cost=10 Card=328 Bytes=3842)
这个部分包含了若干的属性输出，下面简单介绍一下各个属性。
·Cost：CBO赋予执行计划的每个步骤的成本。
·Card：基数的简写，它是特定查询计划步骤输出的记录行数的估算。
·Bytes：CBO预测的每一个计划步骤将返回的数据字节数量。
·如果Cost、Card和Bytes值不存在，就清楚地表明此查询使用的是RBO。统计数据如下。
·Recursive calls：为执行SQL语句递归调用SQL语句的数目。
·Db block gets：用当前方式从缓冲区高速缓存中读取的总块数。
·Consistent gets：在缓冲区高速缓存中一个块被请求进行读取的次数。读取方式为一致性读取。一致性读取可能需要读取回滚段的信息。
·Physical reads：从数据文件到缓冲区高速缓存物理读取的数目。
·Redo size：语句执行过程中产生的重做信息的字节数。
·Bytes sent via SQL*Net to client：从服务器发送到客户端的字节数。
·Bytes received via SQL*Net from client：从客户端接收的字节数。
·SQL*Net roundtrips to/from client：从客户机发送和接收的SQL*Net消息的总数，包括从多行的结果集中提取的往返消息。
·Sorts(memory)：在内存中的排序次数。
·Sorts(disk)：磁盘排序次数。
·Rows processed：更改或选择返回的行数。
3.SQL Trace（10046）
SQL Trace是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中，SQL Trace是常用的方法。对数据库进行性能诊断可以使用SQL跟踪的方法，把一些信息记录在Trace文件里以便以后分析。
10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强。当SQL跟踪用于高于1的等级时，也称为扩展的SQL跟踪。对应10046有不同的级别，级别越高跟踪的粒度越细，如表3-1所示。
表3-1 10046的级别
下面简单介绍一下这个事件的使用方法，包括必要的准备工作、如何跟踪、格式化输出结果及输出含义。
（1）准备工作
在做SQL Trace之前，需要设置一些参数。这些参数控制跟踪事件的详细程度或者限制跟踪文件大小。
计时信息
参数timed_statistics，用于控制计时信息是否可用，其默认值依赖于另外一个参数statistics_level。如果将statistics_level设定为basic，则timed_statistics默认为false；否则，timed_statistics默认为true。
文件大小
Trace文件的最大尺寸（单位为操作系统块），UMLIMITED表示没有限制。Oracle 8以后可以在后面加上K或M来表示文件大小。决定Trace文件大小的因素：跟踪级别、跟踪时长、会话的活动级别和MAX_DUMP_FILE_SIZE参数。
文件名称
生成的文件有内置的命名规则，也可以手动指定名称，便于辨识。
1）默认规则：
<instance name>_<process name>_<process id>.trc
其中：
·instance name：初始化参数instance_name的小写值。在RAC中，与初始化参数db_name不同，通过v$instance视图的instance_name列可以得到这个值。
·process name：产生跟踪文件进程的小写值。对于专有服务器进程，使用ora；对于共享服务器进程，可以通过v$dispatcher或v$shared_server视图的name列获得；对于并行从属进程，可以通过v$px_process视图server_name列获得；对于其他多数后台进程来说，可以通过v$bgprocess视图的name列获得。
·process id：操作系统的进程标识，可以通过v$process视图的spid列获得。
2）人工标记文件：在生成的文件名中就有这个标记，方便寻找生成的跟踪文件。
·格式：
alter session set tracefile_identifier='test'; alter session set sql_trace=true;
·名称格式：
<instance name>_<process name>_<process id>_<tracefile identifier>.trc
·注意事项：该方法只对专有服务器进程有效。每次会话动态改变该参数的值，都会自动创建一个新的跟踪文件。参数tracefile_identifier的值通过v$process视图的traceid列可以得到。这只对该参数的会话有效，其他会话看到的值为NULL。
文件路径
跟踪文件生成的路径跟服务器的参数设置有关系。不同的数据库版本，文件路径也有差异。如果使用Oracle的共享服务器连接，就会使用一个后台进程，因此，跟踪文件的位置是由BACKUPGROUND_DUMP_DEST确定。如果使用的是专用服务器连接，就会使用一个用户进程和Oracle交互，此时的文件路径在USER_DUMP_DEST下。10g及以前版本的查看方式如下：
select rtrim(c.value,'/')||'/'||d.instance_name||'_ora_'||ltrim(to_char(a.spid)) ||'.trc' as trace_file_name
from v$process a,v$session b,v$parameter c,v$instance d
where a.addr=b.paddr and
b.sid=(select sid from v$mystat where rownum<2) and
b.audsid=sys_context('userenv','sessionid') and
c.name='user_dump_dest';
11g中的查看方式不太一样。在11g中，Oracle以更简便的方式提供了跟踪文件的名称，这依赖于ADR（Automatic Diagnostic Repository）的引入。初始化参数user_dump_dest与backupgroup_dump_dest失效，转而支持初始化参数diagnostic_dest。不过新的初始化参数只设定基本目录，可以使用v$diag_info视图获得跟踪文件的准确位置。
select value from v$parameter where name='diagnostic_dest'; select value from v$diag_info where name = 'Default Trace'; //跟踪目录 select value from v$diag_info where name = 'Default Trace File'; //跟踪文件
这里的Default Trace File就是默认的会话跟踪文件名称。
（2）使用方法（传统方式）
可以以多种粒度去跟踪对象，包括全局、会话等，下面分别说明。
全局
启用全局会导致所有进程的活动被跟踪，包括后台进程及所有用户进程。这通常会导致比较严重的性能问题，所以在生产环境中要谨慎使用。通过启用全局，可以跟踪到所有后台进程的活动，很多在文档中的抽象说明，通过跟踪文件的实时变化，我们可以清晰地看到各个进程之间的紧密协调。具体使用方法可以通过修改参数文件加入以下参数：
sql_trace=true or event="10046 trace name context forever,level 12"
当前会话
大多数时候我们使用sql_trace跟踪当前进程。通过跟踪当前进程可以发现当前操作的后台数据库递归活动（这在研究数据库新特性时尤其有效），研究SQL执行，会发现后台错误等。具体使用方法如下：
alter session set sql_trace=true; alter session set sql_trace=false; or alter session set events '10046 trace name context forever'; alter session set events '10046 trace name context forever, level 8'; alter session set events '10046 trace name context off';
其他用户会话
除了跟踪当前会话外，也可以跟踪其他会话，方法有很多种。
1）dbms_system方法，具体如下：
exec dbms_system.set_sql_trace_in_session( 1234, 56789, true); exec dbms_system.set_sql_trace_in_session( 1234, 56789, false);
默认情况下，dbms_system包只能被sys用户执行。如果执行所需的权限给了其他用户，需要慎重，因为这个包含了其他过程，set_ev过程自身也能用来设定其他事件。如果确实需要提供给其他用户在任意会话内激活或禁止SQL跟踪的能力，建议使用另外包含必需过程的包，这个包就是dbms_support。
2）dbms_support方法，具体如下：
exec dbms_support.start_trace_in_session(sid => 1234,serial# => 56789,waits => true,binds => true); exec dbms_support.stop_trace_in_session(sid => 1234,serial# => 56789);
3）dbms_system.set_ev方法，具体如下：
exec dbms_system.set_ev(si=>9,se=>437,ev=>10046,le=>8,nm=>'test'); exec dbms_system.set_ev(9,437,10046,0,'hf');
其中：
·si：session id，即会话ID。
·se：serial number，即会话序列号。
·ev：event number，即事件号。
·le：level，即TRACE的级别，0表示跟踪结束。
（3）使用方法（新方式）
10g以后提供的dbms_monitor包来开启或关闭SQL跟踪。可以根据会话属性（客户端标记、服务名、模块名以及操作名），开启或关闭SQL跟踪。在大量使用连接池的场合下，用户不用依赖特定的会话，这一特性很重要。默认情况下，只有dba角色的用户才能执行dbms_monitor包提供的过程。可以对会话级、客户端级、组件级、数据库级等多种粒度进行跟踪。
会话级
1）使用方法：跟踪内容是通过waits、binds参数设置完成，不同于以前的通过level来设置。
dbms_monitor.session_trace_enable(session_id=>123,serial_num=>23733,waits=>true,binds=>false); dbms_monitor.session_trace_disable(session_id=>123,serial_num=>23733);
2）查看会话是否被跟踪：
select sql_trace,sql_trace_waits,sql_trace_binds from v$session where sid=xxx;
在10gR2中，当启用跟踪后，v$session会设置相应值。但需要注意的是，只有在session_trace_enable已经被使用且被跟踪的会话至少执行了一个SQL语句的时候才是这样。
3）注意事项：在RAC中，session_trace_enable与session_trace_disable过程要在会话所在的实例上执行。
客户端级
1）使用方法如下：
dbms_monitor.client_id_trace_enable(client_id='hanfeng',waits=>true,binds=>false); dbms_monitor.client_id_trace_disable(client_id='hanfeng');
其中：
·client_id：没有默认值，区分大小写。
·waits：是否跟踪等待事件，默认为true。
·binds：是否跟踪绑定变量，默认为false。
2）查看客户端是否被跟踪：
select primary_id as client_id,waits,binds from dba_enabled_traces where trace_type='CLIENT_ID';
3）注意事项：只有在会话属性客户端标记已经设定后才有用。
组件级
1）使用方法：
dbms_monitor.serv_mod_act_trace_enable(service_name=>'xxxx',module_name=>'mymodule', action_name=>'myaction',waits=>true,binds=>false,instance_name=>null); dbms_monitor.serv_mod_act_trace_diable(service_name=>'xxxx',module_name=>'mymodule', action_name=>'myaction',instance_name=>null);
其中：
·service_name：和数据库相关联的逻辑名字。可以通过初始化参数service_names来进行设置。一个数据库可以有多个服务名。
·module_name：默认值any_module，null也是有效值。
·action_name：默认值any_action，null也是有效值。如果指定了参数action_name，必须指定参数module_name，否则会抛出ORA-13859错误。
·waits：是否跟踪等待事件，默认为true。
·binds：是否绑定变量，默认为false。
·instance_name：如果使用RAC，使用参数instance_name能够用来限制对单实例进行跟踪。默认情况下，SQL跟踪对所有实例都是开启的。
2）查看组件是否被跟踪：
select primary_id as service_name,qualifier_id1 as module_name,qualifier_id2 as action_name,waits,binds from dba_enabled_traces where trace_type='SERVICE_MODULE_ACTION';
如果启用SQL跟踪没有指定服务名、模块名、操作名这三个属性，trace_type列将设定为SERVICE或SERVICE_MODULE，具体设定为哪个取决于使用了哪个参数。
数据库级
1）使用方法：
dbms_monitor.database_trace_enable(waits=>true,binds=>true,instance_name=>null); dbms_monitor.database_trace_disable(instance_name=>null);
其中：
·waits：是否跟踪等待事件，默认为true。
·binds：是否跟踪绑定变量，默认为false。
·instance_name：限制只对单一实例进行跟踪。如果参数instance_name设定为null，也就是默认值，将对所有的实例开启SQL跟踪。
2）查看数据库是否被跟踪：
select instance_name,waits,binds from dba_enabled_traces where trace_type='DATABASE';
（4）分析日志
无论上面采用哪种方式收集日志，都需要对这些日志进行分析。对于日志的分析有两种情况，一种是针对原始日志文件，另外一种是针对TKPROF处理后的日志文件。
原始日志
一般情况下，不会直接对原始日志进行分析。但对于处理后的日志，有些信息会丢失，因此必要时还是会对原始日志进行分析。相对而言，原始日志看起来不是很方便。
下面对内容指标进行说明。
1）PARSING IN CURSOR...END OF STMT：主要记录SQL语句文本。
·len：被分析的SQL的长度。
·dep：产生递归SQL的深度。
·uid：user id。
·otc：Oracle command type命令的类型。
·lid：私有用户的ID。
·tim：时间戳。
·hv：hash value。
·ad：sql address。
2）PARSE表示解析，EXEC表示执行，FETCH表示获取。
·c：消耗的cpu time。
·e：elapsed time操作的用时。
·p：physical reads物理读的次数。
·cr：consistent reads一致性方式读取的数据块。
·cu：current方式读取的数据块。
·mis：cursor miss in cache硬解析次数。
·r：rows处理的行数。
·dep：depth递归SQL的深度。
·og：optimzer goal优化器模式。
·tim：timestamp时间戳。
3）BINDS：绑定变量的定义和值。
4）WAIT：在处理过程中发生的等待事件。
5）STAT：产生的执行计划以及相关的统计。
·id：执行计划的行源号。
·cnt：当前行源返回的行数。
·pid：当前行源的父号。
·pos：执行计划中的位置。
·obj：当前操作的对象ID（如果当前行原是一个对象的话）。
·op：当前行源的数据访问操作。
下面我们来看一个实际示例。
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /opt/oracle/products/10.2.0/db_1 System name: Linux Node name: server1 Release: 2.6.16.60-0.21-default Version: #1 Tue May 6 12:41:02 UTC 2008 Machine: i686 Instance name: testdb Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 25750, image: oracle@server1 (TNS V1-V3) *** 2010-08-31 15:05:14.035 *** ACTION NAME:() 2010-08-31 15:05:14.035 *** MODULE NAME:(SQL*Plus) 2010-08-31 15:05:14.035 *** SERVICE NAME:(SYS$USERS) 2010-08-31 15:05:14.035 *** SESSION ID:(139.1453) 2010-08-31 15:05:14.035
上面是Trace文件的头部，记录了Trace文件路径和名称，Trace生成的时间、数据库版本、操作系统版本、实例名等。
... PARSING IN CURSOR #4 len=210 dep=2 uid=0 oct=3 lid=0 tim=1253162423406877 hv= 864012087 ad='2b691784' select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, dens ity, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 END OF STMT PARSE #4:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=1253162423406868 EXEC #4:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=1253162423407167 FETCH #4:c=0,e=44,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,tim=1253162423407253 STAT #4 id=1 cnt=0 pid=0 pos=1 obj=255 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=55 us)' STAT #4 id=2 cnt=0 pid=1 pos=1 obj=257 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=47 us)' EXEC #1:c=4000,e=4202,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,tim=1253162423410127 FETCH #1:c=4001,e=2300,p=0,cr=69,cu=0,mis=0,r=1,dep=1,og=1,tim=1253162423412502 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=69 pr=0 pw=0 time=2299 us)' STAT #1 id=2 cnt=4622 pid=1 pos=1 obj=51785 op='TABLE ACCESS SAMPLE T2 (cr=69 pr=0 pw=0 time=98 us)'
上面是Oracle对SQL语句做分析，并且有一个游标号——CURSOR #4。这个号在整个Trace文件中不是唯一的。当一条SQL语句执行完毕后，这个号会被另外的SQL语句重用。从上面可以看出这个SQL语句分析了一次，执行两次，FETCH两次。STAT#就是对SQL执行这个步骤资源消耗的一个统计。
TKPROF日志
TKPROF用来解释Trace文件内容，把原始的Trace文件转化为容易理解的文件。其实就是合并汇总Trace文件中的一些项，规范化文件的格式，使文件更具可读性。需要注意TKPROF只能处理10046事件产生的Trace文件。下面解释一下TKPROF的日志输出。
1）纵行（执行的几个阶段）：
·Parse（分析）：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需使用的表、列以及其他引用到的对象是否存在。此阶段是Oracle在共享池中查找（软解析）或创建查询计划（硬解析）的所在。
·Execute（执行）：这步真正由Oracle来执行。对于insert、update、delete操作，这步会修改数据；对于select操作，这步就只是确定选择的记录，在很多情况下为空；对于update语句，此阶段将执行所有工作。
·Fetch（提取）：返回查询语句中获得的记录，这步只有select语句会被执行；对于update，将不显示任何工作。
2）横行（各信息项）：
·COUNT（计数）：数据库调用数量，即这个语句被parse、execute、fetch的次数。
·CPU：这个语句对于所有的parse、execute、fetch所消耗的CPU的时间，以秒为单位。
·ELAPSED（占用时间）：这个语句所有消耗在parse、execute、fetch的总的时间。如果花费的时间大于CPU时间，意味着花费了等待时间。在报告的底部可以看到具体的等待事件。
·DISK（磁盘）：物理读的数据块数量。注意，不是物理IO的数量。如果这个值大于逻辑读的数量（disk>query+current），意味着数据块填充进了临时表空间。
·QUERY（查询）：在一致性读模式下从高速缓存逻辑读取的块数量。
·CURRENT（当前）：在当前模式下从高速缓存逻辑读取的块数量。通常这类逻辑读被insert、delete、merge及update等语句使用。
·ROWS（行）：所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步；对于insert、update、delete操作，返回记录则是在execute这步。
3）查询环境
·“Misses in library cache during parse:n”：是否在库中进行了解析（0为软解析 1为硬解析）。
·“Misses in library cache during execute:n”：执行调用阶段硬解析的数量。
·“Optimizer goal:xxx”：优化器模式。
·“Parsing user id:xxx”：解析SQL语句用户ID
·“(recursive depth:n)”：递归深度。只针对递归SQL语句提供。直接由应用程序执行的SQL语句深度为0，深度为n仅表示另一个深度为n-1的SQL语句执行了这条语句。
4）查询计划：
·“两部分”：如果指定了explain参数的话可能会看到两部分。第一部分被不够准确地称为行源操作（Row Source Operation），是游标关闭且开启跟踪情况下写到跟踪文件中的执行计划。这意味着如果应用程序不关闭游标而重用它们的话，不会有新的针对重用游标的执行计划写入跟踪文件中。第二部分称为执行计划，是由指定explain参数的tkprof生成的。既然是随后生成的，所以和第一部分不完全匹配。如果看到不一致，前者是正确的。
·统计信息：
·cr（number of buffers retrieved for CR reads）：一致性模式下逻辑读出的数据块数。
·pr（number of physical reads）：磁盘物理读出的数据块数。
·pw（number of physical writes）：物理写入磁盘的数据块数。
·time：百万分之一秒记的占用时间；us代表微秒。
·cost：操作的开销评估（以下项11g才提供，以下各项均是如此）。
·size：操作返回的预估数据量（字节数）。
·card：操作返回的预估行数（除了card，上述都是累计的）。
5）等待事件：总结了SQL语句的等待事件，对每种类型的等待事件提供了如下值。
·Times Waited：等待事件占用时间。
·Max Wait：单个等待事件最大等待时间，单位为秒。
·Total Waited：针对一个等待事件总的等待秒数。
6）跟踪文件信息：输出文件的结尾给出所有关于跟踪文件的信息。这部分信息很重要，可以知道整个跟踪文件消耗的时间。
·跟踪文件名称、版本号、用于这个分析所使用的参数sort的值。
·所有会话数量与SQL语句数量。
·组成跟踪文件的行数。对于10g，可以看到所有SQL用去的时间。
7）案例说明：
Trace文件头部的信息描述了tkprof的版本，以及报告中一些列的含义。在下面的报告中，每一条SQL都包含了这条SQL执行过程中的所有信息。
TKPROF: Release 10.2.0.1.0 - Production on Tue Aug 31 15:22:45 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: testdb_ora_25750_hf.trc Sort options: default *********************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ************************************************************************
下面是SQL在解析过程中访问数据字典视图。如果不需要，可以使用tkprof sys=no的方式来屏蔽它们。
alter session set sql_trace=true ... select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property, i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256), i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null, null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
下面的语句是CBO做动态采样的SQL语句，这说明了这个表没有被分析。
...
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ NO_PARALLEL("T2") FULL("T2") NO_PARALLEL_INDEX("T2") */
:"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "T2" SAMPLE BLOCK (:"SYS_B_4" ,
:"SYS_B_5") SEED (:"SYS_B_6") "T2") SAMPLESUB
下面我们执行的SQL。这条语句分析了1次，执行了1次，数据提取了2次（数据提取不一定一次就能提取完成）。
消耗CPU资源0.02秒，总耗时0.01秒。物理读取0个数据块，一致性读取693个块。没有发生current方式的读取，一共提取数据记录数为1。
... select count(1) from t2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 692 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.02 0.01 0 693 0 1
shared pool中没有命中，说明有一个硬解析。如果是软解析，此处是0。
Misses in library cache during parse: 1
当前优化器模式是CBO ALL_ROWS
Optimizer mode: ALL_ROWS
分析用户的ID
Parsing user id: 55 (HF)
下面是实际的执行路径。这个计划中的信息不是CBO根据表分析数据估算出的数值，而是SQL实际执行过程中消耗的资源信息。
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=692 pr=0 pw=0 time=11699 us)
49934 TABLE ACCESS FULL T2 (cr=692 pr=0 pw=0 time=89 us)
其中：
·Rows：当前操作返回的实际记录数。
·Row Source Operation：行源操作（表示当前操作的数据访问方式）。
·cr（consistent read）：一致性方式读取的数据块（相当于query列上fetch步骤的值）。
·pr（physical read）：物理读取的数据块（相当于disk列上的fetch步骤的值）。
·pw（physical write）：物理写。
·time：当前操作执行的时间。
下面是使用explain for方式生成的SQL执行计划：
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
49934 TABLE ACCESS (FULL) OF 'T2' (TABLE)
下面是对这个SQL_TRACE期间所有非递归SQL语句（NON-RECURSIVE STATEMENTS）的执行信息统计汇总。
... OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.01 0.00 0 1 0 0 Execute 5 0.00 0.00 0 0 0 2 Fetch 2 0.01 0.01 0 692 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.02 0.02 0 693 0 3 Misses in library cache during parse: 2
下面是所有递归的SQL语句的信息统计。递归语句是指执行一条SQL语句衍生出执行一些其他的SQL。比如读取数据字典表等。
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 17 0.00 0.00 0 77 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 0.01 0.01 0 77 0 14
Misses in library cache during parse: 1
Misses in library cache during execute: 1
6 user SQL statements in session.
3 internal SQL statements in session.
9 SQL statements in session.
1 statement EXPLAINed in this session.
下面是整个TRACE文件的概要说明，包括文件名、扩展参数、SQL语句数量等内容。
***************************************************************************
Trace file: testdb_ora_25750_hf.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
6 user SQL statements in trace file.
3 internal SQL statements in trace file.
9 SQL statements in trace file.
8 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
HF.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
112 lines in trace file.
18 elapsed seconds in trace file.
4.V$SQL和V$SQL_PLAN
使用数据字典视图也可以查看执行计划。因这种方式显示的执行计划不太直观，故不太常用。下面给出简单示例。
SQL> select /*hf*/ count(*) from t1;
COUNT(*)
----------
86262
SQL> col oper format a20
SQL> col options format a20
SQL> col object_name format a10
SQL> select lpad(' ',2*depth)||operation,options,object_name,cost
from v$sql_plan
where sql_id='dnfpxwhzrkpm7';
OPER OPTIONS OBJECT_NAM COST
-------------------- -------------------- ---------- ----------
SELECT STATEMENT 344
SORT AGGREGATE
TABLE ACCESS FULL T1 344
5.DBMS_XPLAN
DBMS_XPLAN是Oracle数据库内置的一个包，通过它可以查看存储在不同位置的执行计划，包括计划表、库缓存、自动负载信息库（AWR）和SQL调优集。笔者认为，它是在众多查看执行计划的方式中最好的一种，不仅支持从多种数据源显示执行计划，而且显示信息比较丰富。
根据不同的数据源，需要调用DBMS_XPLAN包的不同方法。不同方法对比如表3-2所示。
表3-2 不同数据源调用的不同方法
下面针对不同的方法，分别加以说明。
（1）DBMS_XPLAN.DISPLAY
DISPLAY函数返回存储在计划表中的执行计划，返回值为集合dbms_xplan_type_table实例。其中，集合里的元素是对象类型dbms_xplan_type的实例。此对象类型的唯一属性称为plan_table_output，类型为varchar2。注意：使用前把sqlplus的linesize参数调整到至少120。
语法：
select * from table(dbms_xplan.display('table_name','statement_id','format','filter_preds));
参数：
·table_name：指定计划表的名字，默认值为plan_table。
·statement_id：指定SQL语句的名字，当SQL语句explain plan执行时，此参数是可选的。默认值是null，在使用默认值的情况下，将显示最近插入计划表中的执行计划（如果没有指定filter_preds参数）。
·format：指定输出哪些内容。除了基本的basic、typical、serial、all和advanced，为了更好地控制，还有一些额外的修饰符可以添加在后面（alias、bytes等）。如果需要某些特殊的信息，就在修饰符前加一个“+”字符（比如basic+predicate）。如果不需要哪些信息，就在修饰符前加一个“-”字符（比如typical-bytes）。可以同时指定多个修饰符（typical+alias-bytes）。默认值为typical，同时基本值advanced和所有修饰符从10gR2开始可供使用。
·filter_preds：指定在查询计划表时添加的一个约束。约束的内容是基于计划表中某个字段一个寻常的SQL谓词（比如statement_id='xxx'），默认值是null。如果使用默认值，将显示最近一条插到计划表中的执行计划。这个参数从10gR2开始使用。
下面来重点介绍一个非常重要的参数——FORMAT参数。
基本值：
·basic：仅显示很少的信息。基本上只包含操作和操作的对象。
·typical：显示大部分相关内容，基本上包含除了别名、提纲和字段投影外的所有信息。
·serial：和typical类似，只是并行操作没有显示出来。
·all：显示除了提纲外的所有信息。
·advanced：显示所有信息。
修饰符：
·alias：控制包含查询块名和对象别名部分的显示。
·bytes：控制执行计划表中字段Bytes的显示。
·cost：控制执行计划表中字段Cost的显示。
·note：控制包含注意信息（note）部分的显示。
·outline：控制包含提纲（outline）部分的显示。
·parallel：控制并行处理信息的显示，尤其是执行计划表中字段TQ、IN-OUT和PQ Distrib的显示。
·partition：控制分区信息的显示，尤其是执行计划表中字段Pstart和Pstop的显示。
·peeked_binds：控制包含被窥视的绑定变量部分的显示。既然SQL语句explain plan的当前实现不执行绑定变量窥视，这部分内容就不会显示。
·predicate：控制包含谓词filter和access部分的显示。
·projection：控制包含字段投影信息部分的显示。
·remote：控制远程执行的SQL语句的显示。
·rows：控制执行计划表中字段Rows的显示。
示例：
SQL> explain plan for select count(*) from t1; Explained. SQL> select * from table(dbms_xplan.display(null,null,'basic')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3724264953 ----------------------------------- | Id | Operation | Name | ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T1 | -----------------------------------
（2）DBMS_XPLAN.DISPLAY_CURSOR
显示存储在库缓存中的执行计划。注意此函数从10g开始可供使用。返回值是集合dbms_xplan_type_table的一个实例。
语法：
select * from table(dbms_xplan.display_cursor('sql_id', cursor_child_no, 'format'));
参数：
·sql_id：指定被返回执行计划的SQL语句的父游标。默认值是null。如果使用了默认值，当前会话的最后一条SQL语句的执行计划将被返回。
·cursor_child_no：指定父游标下子游标的序号，即指定被返回执行计划的SQL语句的子游标，默认值是0。如果设定为null，sql_id所指父游标下所有子游标的执行计划都将被返回。
·format：指定要显示那些信息，默认值为typical。可用参数和display相同。此外，如果执行统计打开（参数statistics_level为all或SQL语句使用了提示gather_plan_statistics），则可以显示更多的信息。
下面来重点介绍一个重点参数——FORMAT参数。
修饰符
：
·allstats：这是iostats+memstats的快捷方式。
·iostats：控制I/O统计的显示。
·last：显示所有执行计算过的统计。如果指定了这个值，只显示最后一次执行的统计信息。
·memstats：控制PGA相关统计的显示。
·runstats_last：和iostats last相同，只能用于10gR1。
·runstats_tot：和iostats相同，只能用于10gR1。
示例：
SQL> select count(*) from t1;
COUNT(*)
----------
86262
SQL> select sql_id, address, hash_value ,plan_hash_value ,child_number
2 from v$sql
3 where sql_text like '%select count(*) from t1%' and sql_text not like '%v$sql%';
SQL_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER
------------- ---------------- ---------- --------------- ------------
5bc0v4my7dvr5 000000006AA92650 4235652837 3724264953 0
SQL> select * from table(dbms_xplan.display_cursor('5bc0v4my7dvr5',0,'advanced +allstats'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 3724264953
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 344 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 85773 | 344 (1)| 00:00:05 |
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
（3）DBMS_XPLAN.DISPLAY_AWR
DISPLAY_AWR函数返回存储在AWR中的执行计划，从10g起可用。返回值是集合dbms_xplan_type_table的一个实例。
语法：
select * from table(dbmx_xplan.display_awr('sql_id',plan_hash_value,db_id,'format'));
参数：
·sql_id：指定被返回执行计划的SQL语句的父游标。此参数没有默认值。
·plan_hash_value：指定被返回执行计划的SQL语句的哈希值，默认值为null。如果使用了默认值，与sql_id参数指定的父游标相关的所有执行计划都会返回。
·db_id：指定被返回执行计划的SQL语句所在的数据库，默认为null。如果使用了默认值，则数据库为当前库。
·format：指定要显示哪些信息。和display函数中的format有相同的参数，默认为typical。
（4）输出说明
无论使用哪种方法，其大致都包含相同的输出项。下面针对输出的各个部分进行详细说明。
第一部分：
sql_id xxxxxxxxxx,child number 0 ------------------------------------- select t2.* from t t1, t t2 where t1.n = t2.n and t1.id > 6 and t2.id between 6 and 19;
其中：
·sql_id：标识父游标。只有调用display_cursor或display_awr函数时才有此信息。
·child number：属于这个sql_id的子游标序号，可以标识出子游标。只有调用display_cursor函数时才会产生这个数据。
·select...：SQL语句内容。只有在调用display_cursor或display_awr函数时产生。
第二部分：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1338433605
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 7756 | 42 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 7756 | 42 (3)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID
| T | 14 | 7392 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 14 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T | 982 | 25532 | 36 (0)| 00:00:01 |
--------------------------------------------------------------------------------
包含执行计划的哈希值和以表格形式展现的执行计划本身。表格中提供了每一步操作的执行路径、对象、影响行数、成本等信息。此外，关于分区、并行处理、运行统计部分只有在存在时才会显示。
1）基本字段（总是可用的）：
·Id：执行计划中每一个操作（行）的标识符。如果数据前面带有星号，意味着将在随后提供这行包含的谓词信息。
·Operation：执行的操作，又称行源操作。
·Name：操作的对象。
·2）查询优化器评估：
·Rows(E-Rows)：评估中操作返回的记录条数。
·Bytes(E-Bytes)：评估中操作返回的记录字节数。
·TempSpc：评估中操作使用的临时空间大小。
·Cost(%CPU)：评估中操作的开销。在括号内列出了CPU开销的百分比。注意这些值是通过执行计划计算出来的。换句话说，父操作的开销包含子操作的开销。
·Time：评估中执行操作需要的时间（HH:MM:SS）。
3）分区：
·Pstart：访问的第一个分区。如果解析时不知道是哪个分区，就设为KEY、KEY(I)、KEY(MC)、KEY(OR)或KEY(SQ)。
·Pstop：访问的最后一个分区。如果解析时不知道是哪个分区，就设为KEY、KEY(I)、KEY(MC)、KEY(OR)或KEY(SQ)。
4）并行和分布式处理：
·Inst：在分布式操作中，指操作使用的数据库链的名字。
·TQ：在并行操作中，用于从属线程间通信的表队列。
·IN-OUT：并行或分布式操作间的关系。
·PQ Distrib：在并行操作中，生产者为发送数据给消费者进行的分配。
5）运行时统计（统计开启时可用）：
·Starts：指定操作执行的次数。
·Rows：操作返回的真实记录数。
·Time：操作执行的真实时间（HH:MM:SS.FF）
6）I/O统计（统计开启时可用）：
·Buffers：执行期间进行的逻辑读操作数量。
·Reads：执行期间进行的物理读操作数量。
·Writes：执行期间进行的物理写操作数量。
7）内存使用统计：
·0Mem：最优执行所需内存的评估值。
·1Mem：一次通过（one-pass）执行所需内存的评估值。
·0/1/M：最优/一次/多次通过（multipass）模式操作执行的次数。
·Used-Mem：最后一次执行时操作使用的内存量。
·Used-Tmp：最后一次执行时操作使用的临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致（比如，32KB意味着32MB）。
·Max-Tmp：操作使用的最大临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致（比如，32KB意味着32MB）。
第三部分：
Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T2@SEL$1 3 - SEL$1 / T2@SEL$1 4 - SEL$1 / T1@SEL$1
对执行计划中的每一步操作，都可以看到所涉及的查询块，同时可能看到相关的执行对象。这部分信息只从10g才可以使用。
第四部分：
Outline Data
----------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1"("T"."ID"))
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
仅在10gR2后才开始生效，显示强制产生一个特殊执行计划所必需的一组提示的集合。这些提示的集合称为提纲（outline）。
第五部分：
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."N"="T2"."N")
3 - access("T2"."ID">=6 AND "T2"."ID"<=19)
4 - filter("T1"."ID">6)
显示使用的谓词。对其中的每一个谓词，显示在第几行和以什么样的方式（访问或过滤）操作数据。
第六部分：
Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."PAD"[VARCHAR2, 1000] 2 - "T2"."ID"[NUMBER,22], "T2"."N"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000] 3 - "T2".ROWID[ROWID,10], "T2"."ID"[NUMBER,22] 4 - "T1"."N"[NUMBER,22]
这部分信息仅从10g开始可用，显示每一步操作执行时，那些字段作为输出返回。
第七部分：
Note ----- - dynamic sampling used for this statement
提供优化阶段、环境或SQL语句本身的一些注意和警告信息。此处，提醒查询优化器使用了动态采样来收集对象统计信息。在9iR2中，这部分内容是没有标题的，也没包含多少信息（例如，是否使用动态采样）。从10g开始，这部分内容就详细多了。
[1] 除了最后两个外，都要从10gR2 起可以使用。
3.3.2 固定执行计划
在实际工作中经常面临这样一个问题，就是SQL语句的执行计划因为各种原因产生变化导致执行效率低下。此时，就需要一种技术可以将SQL语句的执行计划固定下来。Oracle数据库本身提供了多种方法，可以固定执行计划，如表3-3所示。后面将对各种技术，进行简单说明。
表3-3 Oracle数据库提供的方法
1.提示
通过使用提示，强制SQL语句按照指定的方式执行。这是一种相对简单的处理方式，缺点是必须修改SQL语句，且有些情况下可能会出现忽略提示的现象。关于提示的使用方法，会在后面详细说明，这里指通过一个简单的示例说明一下它的用法。
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> alter table t1 add primary key(object_id);
Table altered.
SQL> exec sys.dbms_stats.gather_table_stats('hf','t1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL> select count(*) from t1;
COUNT(*)
----------
86267
Execution Plan
----------------------------------------------------------
Plan hash value: 716119113
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C0011089 | 86267 | 50 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL> select /*+ full(t1) */ count(*) from t1;
COUNT(*)
----------
86267
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 344 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 86267 | 344 (1)| 00:00:05 |
-------------------------------------------------------------------
在这个示例中，通过指定full这个提示，将原来的“INDEX FAST FULL SCAN”变成了“TABLE ACCESS FULL”。
2.存储概要
存储概要（stored outlines）被设计用来提供稳定的执行计划，以消除执行环境或对象统计信息的改变造成的影响。这个特性又称计划稳定性。计划稳定性阻止执行计划和应用程序性能因环境和配置的改变而改变。计划稳定性把执行计划保存在存储概要中。启用存储概要时，优化器从概要中产生相应的执行计划，从而使SQL语句的访问路径稳定下来。存储概要中的计划不会改变，即使数据库配置或Oracle版本发生变化。计划稳定性还可以在从基于规则优化器移植到基于成本优化器时和升级到新版本Oracle时稳定应用程序性能。要使用存储概要，执行的SQL语句应与存储的SQL语句完全匹配。执行计划的固定依赖于判定一个查询是否存在存储概要时查询语句是否完全一致，与判定shared pool里一个执行计划是否可以重用时的匹配方式是一致的。下面通过一个简单的示例说明具体用法。
SQL> create table t as select * from dba_objects;
Table created.
SQL> set autotrace on
SQL> select * from t where object_id=10;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2898 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 14 | 2898 | 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
SQL> create index idx_t on t(object_id);
Index created.
SQL> select * from t where object_id=10;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00|
| 1 | TABLE ACCESS BY INDEX ROWID
| T | 1 | 207 | 2 (0)| 00:00|
|* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00|
--------------------------------------------------------------------------------
在有索引的情况下，这条语句为“INDEX RANGE SCAN”；没有索引的情况下，这条语句为“TABLE ACCESS FULL”。
SQL> drop index idx_t;
Index dropped.
SQL> create or replace outline myoutline for category mycategory on
select * from t where object_id=10;
Outline created.
在没有索引的情况下，为这条语句生成一个存储概要。
SQL> create index idx_t on t(object_id);
Index created.
SQL> select * from t where object_id=10;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00|
|* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00|
--------------------------------------------------------------------------------
SQL> alter session set use_stored_outlines=mycategory;
Session altered.
SQL> select * from t where object_id=10;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1029 | 208K| 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 1029 | 208K| 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Note
-----
- outline "MYOUTLINE" used for this statement
在会话级使用先前定义的存储概要，在即使有索引的情况下，仍然执行的是“TABLE ACCESS FULL”。需要特别关注的是，后面的Note一节，明确说明了这个语句使用了名称为“MYOUTLINE”的存储概要。
3.SQL概要
SQL概要是一个对象，它包含了可以帮助查询优化器为一个特定的SQL找到高效执行计划的信息。这些信息包括执行环境、对象统计和对查询优化器所做评估的修正信息。它最大的优点是在不修改SQL和会话执行环境的情况下影响查询优化器的决定。下面通过一个简单示例说明一下它的用法。
下面的代码准备了一个数据环境。
SQL> create table t1 as select rownum as id,t.* from dba_objects t; Table created. SQL> create index idx_t1 on t1(id); Index created. SQL> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed.
按照下面的查询，检索一部分数据，执行计划走的是索引范围扫描。
SQL> set autotrace traceonly exp SQL> select count(*) from t1 where id<100; ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 99 | 495 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------
下面的代码使用了full提示，模拟了一个性能较差的SQL语句，使用了全表扫描的方式检索数据。下面尝试使用SQL概要的方式解决这个问题。
SQL> select /*+ full(t) */ count(*) from t1 t where id<100; --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 361 (1)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| T1 | 99 | 495 | 361 (1)| 00:00:05 | ---------------------------------------------------------------------------
使用下面的方法，可生成一个SQL概要。可以按照输出报告的方法，接受一个SQL概要。
SQL> set autotrace off
SQL> declare
2 v_task_name varchar2(30);
3 begin
4 v_task_name:=dbms_sqltune.create_tuning_task(sql_text=>'select /*+ full(t) */ count(*) from t1 t where id<100',task_name=>'task_t1');
5 dbms_sqltune.execute_tuning_task(v_task_name);
6 dbms_output.put_line(v_task_name);
7 end;
8 /
PL/SQL procedure successfully completed.
select dbms_sqltune.report_tuning_task('task_t1') from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : task_t1
Tuning Task Owner : HF
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 12/10/2010 17:18:03
Completed at : 12/10/2010 17:18:03
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: HF
SQL ID : 3k0zyq1s87nuc
SQL Text : select /*+ full(t) */ count(*) from t1 t where id<100
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
//找到一个SQL Profile，具体的执行计划在下面
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.78%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'task_t1', replace => TRUE);
//采用这个SQL Profile，只需要执行这个即可
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
//原始的执行计划
------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 165 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T1 | 96 | 480 | 165 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<100)
2- Using SQL Profile
//使用SQL Profile后的执行计划
--------------------
Plan hash value: 1970818898
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1 | 96 | 480 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<100)
-------------------------------------------------------------------------------
下面的操作接受了这个SQL概要。虽然在SQL代码中存在提示，但Oracle优化器还是会根据SQL概要选择更合适的执行计划。在执行计划的Note部分，标明使用了SQL概要。
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'task_t1', replace => TRUE); PL/SQL procedure successfully completed. SQL> set autotrace traceonly exp SQL> select /*+ full(t) */ count(*) from t1 t where id<100; ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 99 | 495 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- Note ----- - SQL profile "SYS_SQLPROF_01498bec08ca0000" used for this statement
4.SQL计划基线
从11g开始，存储概要被SQL计划基线取而代之，可以认为SQL计划基线是存储概要的一个改进版本。SQL计划基线是一个与SQL语句相关联的对象，它被设计用来影响查询优化器产生执行计划时的决定。具体来说，SQL计划基线主要是一个提示的集合。基本上，SQL计划基线就是用来迫使查询优化器给一条给定的SQL语句产生一个特定的、稳定的执行计划。SQL计划基线是存储在数据字典中的。
下面通过一个示例说明SQL计划基线的使用方法。
下面的代码准备了一个数据环境。
SQL> create table t1 as select rownum as id,t.* from dba_objects t; Table created. SQL> create index idx_t1 on t1(id); Index created. SQL> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed.
按照下面查询检索一部分数据，执行计划是索引范围扫描。
SQL> set autotrace on explain SQL> select count(*) from t1 where id between 100 and 200; ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| IDX_T1 | 102 | 510 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------
下面使用full提示，模拟了一个性能较差的SQL语句，使用了全表扫描的方式检索数据。下面尝试使用SQL计划基线的方式解决这个问题。
SQL> select /*+ full(t) */ count(*) from t1 t where id between 100 and 200; --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 361 (1)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| T1 | 102 | 510 | 361 (1)| 00:00:05 | ---------------------------------------------------------------------------
下面的操作接受了这个SQL计划基线。虽然在SQL代码中存在提示，但Oracle优化器还是会根据SQL计划基线选择更合适的执行计划。在执行计划的Note部分，标明使用了SQL计划基线。
SQL> set autotrace off SQL> alter session set optimizer_capture_sql_plan_baselines = true; Session altered. SQL> select /*+ full(t) */ count(*) from t1 t where id between 100 and 200; SQL> select /*+ full(t) */ count(*) from t1 t where id between 100 and 200; SQL> alter session set optimizer_capture_sql_plan_baselines=false; Session altered. SQL> select /*+ full(t) */ count(*) from t1 t where id between 100 and 200; SQL> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 361 (100)| | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| T1 | 102 | 510 | 361 (1)| 00:00:05 | --------------------------------------------------------------------------- Note ----- - SQL plan baseline SQL_PLAN_bgn8rnrj73kjc616acf47 used for this statement
3.3.3 修改执行计划
修改执行计划的方法很多，这里以SQL概要为例进行说明。其原理是构造一个与原始SQL在逻辑上、结构上完全相同的SQL。强制逻辑上和结构上相同，SQL解析的用户名、SQL中引用对象的用户名甚至是一些谓词条件都可以不同。当然能够完全一样会更省事。然后执行构造的SQL，并取得构造SQL的概要数据。使用原始SQL的文本和构造SQL的概要数据创建一个SQL Profile。下面通过一个示例说明这种方法。
下面的代码是一些数据准备工作。
SQL> create table t1 as select * from dba_objects; Table created. SQL> create table t2 as select * from dba_objects; Table created. SQL> create index idx_t2 on t2(object_id); Index created. SQL> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(user,'T2'); PL/SQL procedure successfully completed.
我们生成了一个“原始”SQL，它的执行计划是哈希连接。
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner from t1,t2
2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
SQL> select sql_id, child_number,plan_hash_value
2 from v$sql
3 where sql_text like '%orig_sql%' and sql_text not like '%v$sql%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
8s5ddx4hhwzwp 0 1838229974
SQL> select * from table(dbms_xplan.display_cursor('8s5ddx4hhwzwp',0));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 688 (100)| |
|* 1 | HASH JOIN | | 4314 | 459K| 688 (1)| 00:00:09 |
|* 2 | TABLE ACCESS FULL| T1 | 4314 | 412K| 344 (1)| 00:00:05 |
| 3 | TABLE ACCESS FULL| T2 | 86273 | 926K| 344 (1)| 00:00:05 |
---------------------------------------------------------------------------
下面的代码生成了一个“构造”SQL，它的执行计划是嵌套扫描。
SQL> select /*+ modify_sql index(t1) index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2
2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
SQL> select sql_id, child_number,plan_hash_value
2 from v$sql
3 where sql_text like '%modify_sql%' and sql_text not like '%v$sql%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
fyhbm2tj967wk 0 1054738919
SQL> select * from table(dbms_xplan.display_cursor('fyhbm2tj967wk',0));
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8974 (100)| |
| 1 | NESTED LOOPS | | 4314 | 459K| 8974 (1)| 00:01:48 |
| 2 | NESTED LOOPS | | 4314 | 459K| 8974 (1)| 00:01:48 |
|* 3 | TABLE ACCESS FULL | T1 | 4314 | 412K| 344 (1)| 00:00:05 |
|* 4 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID
| T2 | 1 | 11 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
下面的代码生成“构造”SQL的SQL Profile。方法是调用了一个脚本——coe_xfr_sql_profile.sql。附录部分会有这个脚本。
SQL> @ coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: fyhbm2tj967wk
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1054738919 .096
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1054738919
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "fyhbm2tj967wk"
PLAN_HASH_VALUE: "1054738919"
. . .
Execute coe_xfr_sql_profile_fyhbm2tj967wk_1054738919.sql
on TARGET system in order to create a custom SQL Profile
with plan 1054738919 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
下面的代码生成“原始”SQL的SQL Profile。方法是调用了一个脚本——coe_xfr_sql_profile.sql。
SQL>@ coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 8s5ddx4hhwzwp
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1838229974 .111
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1838229974
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "8s5ddx4hhwzwp"
PLAN_HASH_VALUE: "1838229974"
. . .
Execute coe_xfr_sql_profile_8s5ddx4hhwzwp_1838229974.sql
on TARGET system in order to create a custom SQL Profile
with plan 1838229974 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
下面的代码执行了“原始”SQL，其执行计划变成“构造”SQL的执行方式。这就达到了修改执行计划的目的。
vi coe_xfr_sql_profile_8s5ddx4hhwzwp_1838229974.sql
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T1"@"SEL$1")]',
q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
//将SYS.SQLPROF_ATTR部分修改为coe_xfr_sql_profile_fyhbm2tj967wk_1054738919.sql中的对应部分，具体参见上面
. . .
force_match => TRUE
//由false修改成true
SQL> @ coe_xfr_sql_profile_8s5ddx4hhwzwp_1838229974.sql
select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4314 | 459K| 8974 (1)| 00:01:48 |
| 1 | NESTED LOOPS | | 4314 | 459K| 8974 (1)| 00:01:48 |
| 2 | NESTED LOOPS | | 4314 | 459K| 8974 (1)| 00:01:48 |
|* 3 | TABLE ACCESS FULL | T1 | 4314 | 412K| 344 (1)| 00:00:05 |
|* 4 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID
| T2 | 1 | 11 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------Note
-----
- SQL profile "coe_8s5ddx4hhwzwp_1838229974" used for this statement