查看Oracle执行计划的几种常用方法-系列3，oracle执行计划
续上篇：http://blog.csdn.net/bisal/article/details/39225373
4. 10046事件
通过10046事件也可以查看目标SQL的执行计划。像10046这种事件，都不是Oracle官方文档中可以查询到的，这些事件一般用于调试目的，因此往往可以使用他们找到问题更详细的信息。
10046事件和之前的explain plan、DBMS_XPLAN包以及AUTOTRACE开关的区别在于，10046事件产生的trc文件中明确显示了目标SQL实际执行计划中每一步所消耗的逻辑读、物理读和花费的时间，执行计划的成本分析，进而可以看出为什么Oracle对于SQL选择了这样的执行计划，而不是那样的执行计划，之所以说是实际的执行计划，从10046事件执行的过程就可以看出来：
(a) 在当前session激活10046事件。
(b) 在此session中执行SQL。
(c) 关闭此session的10046事件。
真正执行的SQL，对应的执行计划可以在trc文件中找到。这个trc文件会记录SQL的执行计划和资源消耗，命名格式“实例名_ora_当前session的spid.trc”。
(1). 激活10046事件：
有两种方法：
(a) alter session set events '10046 trace name context forever, level 12';
(b)
oradebug setmypid/oradebug setospid SPID;
oradebug event 10046 trace name context forever, level 12;
(2). 查看10046产生的trc文件名和路径的方法：
(a) show parameter USER_DUMP_DEST显示trc文件存储的路径 -> 查找对应当前session的trc文件(若当前是单用户，则是最新产生的文件)。
实验：
(b) 使用上述(b)的ordebug产生trc文件，可以用oradebug tracefile_name得到trc文件名和路径。
oradebug有很多需要说的，首先这是sqlplus特有的命令，在PLSQL Developer中执行会提示无效的SQL语句，例如：
其次它是sysdba角色的命令，使用非sysdba执行会提示ORA-01031权限不足，例如：
使用sysdba登录后，可以查看oradebug的帮助：
尽管oradebug用的时候需要使用sysdba登录，看似有些麻烦，但和第一种alter session的方法相比，最大的好处就是alter session只能针对当前会话或系统级，即alter session或alter system设置，如果设置非本会话的跟踪，此时就可以用oradebug了，(据说dbms_system、dbms_monitor和dbms_support也可以实现相同的需求，但没有试过)。
使用oradebug设置10046事件之前需要首先设置待跟踪的会话：
(a) 跟踪本会话，使用：oradebug setmypid即可。
(b) 跟踪非本会话，使用：oradebug setospid SPID(来自v$process)。
查找SPID的方法：
(a) select * from v$session a where audsid = userenv('sessionid');返回SID值。
(b)
select s.USERNAME,
s.OSUSER,
s.SID,
s.PADDR,
s.PROCESS,
p.spid os_process_id,
p.pid oracle_process_id
from v$session s, v$process p
where s.paddr = p.addr
and s.username = upper('待跟踪session用户名')
and s.SID = (a)返回的SID;
例如：
其中：
v$process中的SPID是指操作系统的进程，即操作系统的PID。
v$session中的pid, serial#是oracle分配的PID。
此时如果需要跟踪24061这个session执行的SQL，可以用oradebug setospid 24061，然后oradebug
event 10046 trace name context forever, level 12;就打开了10046事件。
接着可以通过oradebug tracefile_name查看trace文件名和路径，例如：
看下petest_ora_22756.trc的内容：
除了机器、实例、进程等基本信息外，真正写入的内容：
Received ORADEBUG command 'tracefile_name' from process Unix process pid: 22235, image:
表示接收到来自Unix的操作系统进程PID是22235的ORADEBUG命令，参数tracefile_name。
显然22235这个进程是sqlplus登陆后执行ORADEBUG的客户端，例如：
(3).
关闭10046的方法：
(a) alter session set events '10046 trace name context off';
(b) oradebug event 10046 trace name context off;分别对应两种打开10046事件的方法。
(4). 再说说oradebug和alter session打开10046事件产生trace文件的区别：
(a) 使用alter session打开10046事件时，如果未执行SQL，则不会产生trace文件。
(b) 使用oradebug event 10046 trace name context forever, level 12;打开10046事件，此时就已经产生trace文件，除基本信息外，主要是一行：
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1379395297285576
当使用oradebug event 10046 trace name context off;关闭10046事件，会写入一行：
WAIT #0: nam='SQL*Net message from client' ela= 30946429 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1379395328232564
(5). trace跟踪文件：
上面说了打开和关闭10046的两种常用方法，下面简单看看trace文件都包含了什么，为什么说10046这种事件是用于调试的，我现在不能精通所有内容，大概谈谈理解。
首先执行命令打开10046事件、执行SQL、关闭10046事件，例如：
查看产生的trace文件：
两个"============"之前的内容，是执行SQL产生的信息，之前和之后的内容，应该是打开和关闭10046事件的信息。
红线框内的是这条SQL用的执行计划，从文件中看，一共有5步，括号内的是相关消耗：
执行计划第一步：MERGE JOIN，逻辑读(cr)是11，物理读(pr)是0，时间(time)是378微秒。
执行计划第二步：TABLE ACCESS BY INDEX ROWID TEST1，逻辑读(cr)是4，物理读(pr)是0，时间(time)是139微秒。
执行计划第三步：INDEX FULL SCAN SYS_C0016790，逻辑读(cr)是2，物理读(pr)是0，时间(time)是86微秒。
执行计划第四步：SORT JOIN，逻辑读(cr)是7，物理读(pr)是0，时间(time)是266微秒。
执行计划第五步：TABLE ACCESS FULL TEST2，逻辑读(cr)是7，物理读(pr)是0，时间(time)是128微秒。
这里trc文件是一种裸trace文件，内容可看，但不是那么直观，可以使用tkprof命令翻译trc文件。例如：
查看生成的tkprof文件：
从这里可以更清楚地看到每步执行计划返回的行数，以及顺序关系，按照@dbsnake的执行计划读取口诀： “先从最开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行”。简单分析下：
(a) INDEX FULL ...
先使用SYS_C0016790主键索引进行索引快速全扫描，这里SYS_C0016790是TEST1的主键，即t1id列。
(b) TABLE ACCESS FULL ...
全表扫描TEST2表。
(c) TABLE ACCESS BY ...
根据TEST1主键索引返回的ROWID，查询对应数据项。产生结果集1。
(d) SORT JOIN
按照TEST2的t2id列排序。产生结果集2。
(e) MERGE JOIN
遍历结果集1，即取出结果集1的第1条记录，和结果集2中按照t1.t1id=t2.t2id的条件判断是否存在匹配记录，再取出结果集1的第2条记录继续判断，直到遍历完成结果集1。
这里用到的是“排序合并连接”，执行计划中对应的关键字是“MERGE JOIN”和“SORT JOIN”，正常来讲，两个表第二步都应该是SORT JOIN，但这里表TEST1却是TABLE ACCESS BY INDEX ROWID TEST1，我想原因应该是：
(a)
对TEST2表的扫描使用的是INDEX FULL SCAN SYS_C0016790，即使用的索引快速全扫描，扫描t1id的主键索引数据块。
(b) 索引都是有序的，因此INDEX FULL SCAN SYS_C0016790的结果也是相当于排序的。
(c) 既然之前已经是排序的结果，那么按照有序索引对应的ROWID，找到对应的记录也是有序的，TABLE ACCESS BY INDEX ROWID TEST1，所以不用显示SORT JOIN再次排序了。
未完待续。。。
To Be Continued ...
在日常基于数据库应用的开发过程中，我们经常需要对多个表或者数据源进行关联查询而得出我们需要的结果集。那么Oracle到底存在着哪几种连接方式？优化器内部又是怎样处理这些连接的？哪种连接方式又是适合哪种查询需求的？只有对这些问题有了清晰的理解后，我们才能针对特定的查询需求选择合适的连接方式，开发出健壮的数据库应用程序。选择合适的表连接方法对SQL语句运行的性能有着至关重要的影响。下面我们就Oracle常用的一些连接方法及适用情景做一个简单的介绍。
3.1 嵌套循环连接(nested loop)
嵌套循环连接的工作方式是这样的：
1、 Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。
2、 提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。
3、 嵌套循环连接的过程如下所示：
Nested loop
Outer loop
Inner loop
我们可以看出这里面存在着两个循环，一个是外部循环，提取驱动表中符合条件的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接。
嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。
嵌套循环连接驱动表的选择也是连接中需要着重注意的一点，有一个常见的误区是驱动表要选择小表，其实这是不对的。假如有两张表A、B关联查询，A表有1000000条记录，B表有10000条记录，但是A表过滤出来的记录只有10条，这时候显然用A表当做驱动表是比较合适的。因此驱动表是由过滤条件限制返回记录最少的那张表，而不是根据表的大小来选择的。
在外连接查询中，如果走嵌套循环连接的话，那么驱动表必然是没有符合条件关联的那张表，也就是后面不加(+)的那张表。这是由于外连接需要提取可能另一张表没符合条件的记录，因此驱动表需要是那张我们要返回所有符合条件记录的表。比如下面这个查询，
嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。在这种情况下，终端用户将会快速地得到返回的首批记录，且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多，或者被驱动表的连接列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的
-- 删除原表
drop table t1;
-- 建立测试表
create table t1(
f1 varchar2(10),
f2 varc......余下全文>>
Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。
1.1 、安 装
要使用EXPLAIN首先要执行相应的脚本，创建出Explain_plan表。
具体脚本执行如下：
$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX) 该脚本后会生成一个表这个程序会创建一个名为plan_table的表。
1.2 、使用
常规使用语法：
explainPLAN[SETSTATEMENT_ID[=]<stringliteral][INTO<table_name]
FOR<sql_statement 其中： STATEMENT_ID：是一个唯一的字符串，把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。
TABLE_NAME：是plan表名，它结构如前所示，你可以任意设定这个名称。
SQL_STATEMENT：是真正的SQL语句。
比如： SQLexplainplansetstatement_id='T_TEST'forselect*fromt_test; SQL Explained
执行下面语句可以查询到执行计划