[Oracle]获取执行计划的各方法总结 - 数据库编程

T | | 2 | 8138 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 | | 3 | INLIST ITERATOR | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T1"."N"=18 OR "T1"."N"=19) 6 - access("T1"."ID"="T2"."T1_ID") Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 12 consistent gets 0 physical reads 0 redo size 1032 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed

优点：1.可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
2.虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出。

缺陷：1.必须要等到语句真正执行完毕后，才可以出结果；
2.无法看到表被访问了多少次。

方法3（statistics level=all的方式)

步骤1：alter session set statistics_level=all ;
步骤2：在此处执行你的SQL
步骤3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

另注：

1. 如果你用 /*+ gather_plan_statistics */的方法，可以省略步骤1，直接步骤2,3。
2. 关键字解读（其中OMem、1Mem和User-Mem在后续的课程中会陆续见到）：
Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。

A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
Buffers为每一步实际执行的逻辑读或一致性读。
Reads为物理读。
OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，
这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的
1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，
就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存
大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,
大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)
OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。
0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存

set autotrace off          
alter session set statistics_level=all ;
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  1a914ws3ggfsn, child number 0
-------------------------------------
SELECT  * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)

Plan hash value: 3532430033
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |      2 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                  |          |      1 |        |      2 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                 |          |      1 |      2 |      2 |00:00:00.01 |      10 |
|   3 |    INLIST ITERATOR             |          |      1 |        |      2 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1       |      2 |      2 |      2 |00:00:00.01 |       5 |
|*  5 |      INDEX RANGE SCAN          | T1_N     |      2 |      1 |      2 |00:00:00.01 |       3 |
|*  6 |    INDEX RANGE SCAN            | T2_T1_ID |      2 |      1 |      2 |00:00:00.01 |       5 |
|   7

[Oracle]获取执行计划的各方法总结(二)

方法3（statistics level=all的方式)