Oracle学习笔记读懂执行计划（十八）
获取执行计划的方法：
统计信息：
/*
1. 11g默认启动了统计信息收集的任务，默认运行时间是周一到周五晚上10点和周6，周天的早上6点
2. 你也可以关闭自动统计新收集任务，选择手工收集的方式，但是一般不建议这样操作。
*/
select window_name,
window_next_time,
autotask_status,
optimizer_stats
from DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME AUTOTASK OPTIMIZE
------------------- ------------------------------------------------ -------- -----
MONDAY_WINDOW 13-1月 -14 10.00.00.000000 下午 +08:00 ENABLED ENABLED
TUESDAY_WINDOW 14-1月 -14 10.00.00.000000 下午 +08:00 ENABLED ENABLED
WEDNESDAY_WINDOW 15-1月 -14 10.00.00.000000 下午 +08:00 ENABLED ENABLED
THURSDAY_WINDOW 16-1月 -14 10.00.00.000000 下午 +08:00 ENABLED ENABLED
FRIDAY_WINDOW 17-1月 -14 10.00.00.000000 下午 +08:00 ENABLED ENABLED
SATURDAY_WINDOW 11-1月 -14 06.00.00.000000 上午 +08:00 ENABLED ENABLED
SUNDAY_WINDOW 12-1月 -14 06.00.00.000000 上午 +08:00 ENABLED ENABLED
select client_name,status from dba_autotask_client
where client_name='auto optimizer stats collection';
CLIENT_NAME STATUS
--------------------------------------------
auto optimizer stats collection ENABLED
/*
禁用这个任务的sql如下：（其实默认是有auto space advisor段指导、sql tuning advisor自动SQL优化和收集统计信息3个任务。
不过那2个基本没啥用，可以禁掉！)：
*/
BEGIN
dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>NULL);
END;
--启用
begin
dbms_auto_task_admin.enable;
dbms_auto_task_admin.enable('auto optimizer stats collection', null,null);
end;
begin
dbms_auto_task_admin.enable('auto optimizer stats collection', null,'WEDNESDAY_WINDOW');
dbms_auto_task_admin.enable('auto optimizer stats collection', null,'FRIDAY_WINDOW');
dbms_auto_task_admin.enable('auto optimizer stats collection', null,'SATURDAY_WINDOW');
dbms_auto_task_admin.enable('auto optimizer stats collection', null,'THURSDAY_WINDOW');
dbms_auto_task_admin.enable('auto optimizer stats collection', null,'TUESDAY_WINDOW');
dbms_auto_task_admin.enable('auto optimizer stats collection', null,'SUNDAY_WINDOW');
dbms_auto_task_admin.enable('auto optimizer stats collection', null,'MONDAY_WINDOW');
end;
--配置维护窗口：
--可是使用DBMS_SCHEDULER 包来修改窗口属性。
--1：修改维护窗口
--先禁用维护窗口
BEGIN
dbms_scheduler.disable(
name => 'SATURDAY_WINDOW');
--修改维护窗口属性：
dbms_scheduler.set_attribute(
name => 'SATURDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(4, 'hour'));
--启用维护窗口
dbms_scheduler.enable(
name => 'SATURDAY_WINDOW');
END;
/
--对于当前打开的窗口，你需要首先禁用，然后修改再启用，配置立即生效，如果你不通过这三个过程来修改属性，属性是不会生效的，直到下一次窗口打开。
--2：创建新窗口：
BEGIN
dbms_scheduler.create_window(
window_name => 'EARLY_MORNING_WINDOW',
duration => numtodsinterval(1, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_window_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
--3:删除窗口：
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
--跟踪job运行情况：
--可以通过查询视图DBA_AUTOTASK_HISTORY追踪job运行情况：
select client_name,job_name,job_start_time
from dba_autotask_job_history where client_name='auto optimizer stats collection'
order by job_start_time desc;
动态采样
/*
结论：
1. 统计信息默认情况下是每天晚上10点半后收集，如果新建对象还没来得级收集统计信息，就采用动态采样的方式。
2. 具体在set autotrace 跟踪的执行计划中，可以看到类似：- dynamic sampling used for this statement (level=2)
3. 除非你用类似/*+dynamic_sampling(t 0) */的HINT关闭这个动态采样。
4. 在收集过统计信息后，Oracle就不会采用动态采样。
注：建索引过程中，默认会收集索引相关的统计信息。
*/
set autotrace off
set linesize 1000
drop table t_sample purge;
create table t_sample as select * from dba_objects;
create index idx_t_sample_objid on t_sample(object_id);
select num_rows, blocks, last_analyzed
from user_tables
where table_name = 'T_SAMPLE';
NUM_ROWS BLOCKS LAST_ANALYZED
----------------------------------
--建索引后，自动收集统计信息。
select index_name,
num_rows,
leaf_blocks,
distinct_keys,
last_analyzed
from user_indexes
where table_name = 'T_SAMPLE';
INDEX_NAME NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
------------------------------ ---------- ----------- ------------- --------------
IDX_T_SAMPLE_OBJID 73159 162 73159 11-1月 -14
set autotrace traceonly
set linesize 1000
select * from t_sample where object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 1453182238
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_SAMPLE | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--以下方法是要取消动态采样。
select /*+dynamic_sampling(t 0) */ * from t_sample t where object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 1453182238
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 872 | 176K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_SAMPLE | 872 | 176K| 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 349 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T_SAMPLE',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set autotrace off
select num_rows, blocks, last_analyzed
from user_tables
where table_name = 'T_SAMPLE';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- --------------
73630 1068 12-1月 -14
set autotrace traceonly
select * from t_sample where object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 1453182238
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_SAMPLE | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--表统计信息收集后，是这样的。
set autotrace off
select num_rows, blocks, last_analyzed
from user_tables
where table_name = 'T';
NUM_ROWS BLOCKS LAST_ANALYZED
--------- ---------- --------------
73118 1068 11-1月 -14
表访问次数：
/*
总的结论：
一.获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了）：
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql
二.适用场合分析
1.如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1；
2.跟踪某条SQL最简单的方法是方法1，其次就是方法2；
3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；
4.如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5；
5.要想确保看到真实的执行计划，不能用方法1和方法2；
6.要想获取表被访问的次数，只能使用方法3；
*/
/*
分结论1（表访问次数）：
二. 执行计划中"表访问次数” 是关键指标，这只能靠方法3的方式获取。
隆重推出本次明星：方法3的statistics_level=all;
*/
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 1000
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
CREATE INDEX t1_n ON t1 (n);
CREATE INDEX t2_t1_id ON t2(t1_id);
/*
下面我们将会用多种方法来查看如下语句的执行计划
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
*/
----方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)
/*
步骤1：explain plan for "你的SQL"
步骤2：select * from table(dbms_xplan.display());
*/
set linesize 1000
set pagesize 2000
explain plan for
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 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)
已选择24行。
/*
优点： 1.无需真正执行，快捷方便
缺陷： 1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
2.无法判断是处理了多少行；
3.无法判断表被访问了多少次。
确实啊，这毕竟都没有真正执行又如何得知真实运行产生的统计信息。
*/
----方法2(set autotrace on 方式)
/*
步骤1：set autotrace on
步骤2：在此处执行你的SQL即可，后续自然会有结果输出
另，有如下几种方式：
set autotrace on (得到执行计划，输出运行结果）
set autotrace traceonly (得到执行计划，不输出运行结果）
set autotrace traceonly explain (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分）
set autotrace traceonl statistics(不输出运行结果和执行计划部分，仅展现统计信息部分）
*/
set autotrace on
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
执行计划
----------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 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. 关键字解读：
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 | TABLE ACCESS BY INDEX ROWID | T2 | 2 | 1 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------
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)
已选择29行。
/*
--优点：1.可以清晰的从STARTS得出表被访问多少。
2.可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。
3.虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的多少
--缺陷：1.必须要等到语句真正执行完毕后，才可以出结果。
2.无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。
3.看不出递归调用的次数，看不出物理读的多少（不过逻辑读才是重点）
*/
----方法4(知道sql_id后，直接带入的方式，简单，就步骤1）
/*
步骤1： select * from table(dbms_xplan.display_cursor('&sq_id')); （该方法是从共享池里得到）
注：
1. 还有一个方法，select * from table(dbms_xplan.display_awr('&sq_id'));（这是awr性能视图里获取到的）
2. 如果有多执行计划，可以用类似方法查出
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));
*/
select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn'));
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 | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 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)
/*
--优点：1.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；
2.可以得到真实的执行计划。（停，等等，啥真实的，刚才这几个套路中，还有假的执行计划的吗？)
--缺陷 1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
2.无法判断是处理了多少行；
3.无法判断表被访问了多少次。
*/
----方法5(10046TRACE）
/*
步骤1：alter session set events '10046 trace name context forever,level 12'; （开启跟踪）
步骤2：执行你的语句
步骤3：alter session set events '10046 trace name context off'; （关闭跟踪）
步骤4：找到跟踪后产生的文件
步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela （格式化命令）
*/
set autotace off
alter session set statistics_level=typical;
alter session set events '10046 trace name context forever,level 12';
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
alter session set events '10046 trace name context off';
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
exit
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc d:\10046.txt sys=no sort=prsela,exeela,fchela
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 12 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 12 0 2
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 94
Rows Row Source Operation
------- ---------------------------------------------------
2 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
2 NESTED LOOPS (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2)
2 INLIST ITERATOR (cr=5 pr=0 pw=0 time=16 us)
2 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2)
2 INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621)
2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622)
2 TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 1.31 1.31
/*
--优点：1.可以看出SQL语句对应的等待事件
2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。
3.可以方便的看出处理的行数，产生的物理逻辑读。
4.可以方便的看出解析时间和执行时间。
5.可以跟踪整个程序包
--缺陷: 1.步骤繁琐，比较麻烦
2.无法判断表被访问了多少次。
3.执行计划中的谓词部分不能清晰的展现出来。
*/
6. awrsqrpt.sql
/*
步骤1：@?/rdbms/admin/awrsqrpt.sql
步骤2：选择你要的断点（begin snap 和end snap)
步骤3：输入你的sql_id
*/
递归调用：
/*
分结论2（递归的调用）：
一. 关于获取执行计划的6种方法和各自区别。
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql
二. 执行计划中"递归调用” 是关键指标，这只能靠方法2和方法5了。
方法2的set autotrace on ;和方法5的10046 trace跟踪
*/
drop table people purge;
create table people (first_name varchar2(200),last_name varchar2(200),sex_id number);
create table sex (name varchar2(20), sex_id number);
insert into people (first_name,last_name,sex_id) select object_name,object_type,1 from dba_objects;
insert into sex (name,sex_id) values ('男',1);
insert into sex (name,sex_id) values ('女',2);
insert into sex (name,sex_id) values ('不详',3);
commit;
create or replace function get_sex_name(p_id sex.sex_id%type) return sex.name%type is
v_name sex.name%type;
begin
select name
into v_name
from sex
where sex_id=p_id;
return v_name;
end;
/
/*
下面我们将会用多种方法来查看如下语句的执行计划
select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people;
*/
----方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)
set linesize 1000
set pagesize 2000
explain plan for
select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people;
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 2528372185
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80635 | 16M| 137 (1)| 00:00:02 |
| 1 | TABLE ACCESS FULL| PEOPLE | 80635 | 16M| 137 (1)| 00:00:02 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择12行。
----方法2(set autotrace on 方式)
/*set autotrace on
set autotrace traceonly
set autotrace traceonly explain
set autotrace traceonl statistics
*/
set autotrace traceonly
select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people;
执行计划
----------------------------------------------------------
Plan hash value: 2528372185
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80635 | 16M| 137 (1)| 00:00:02 |
| 1 | TABLE ACCESS FULL| PEOPLE | 80635 | 16M| 137 (1)| 00:00:02 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
73121 recursive calls
0 db block gets
517142 consistent gets
0 physical reads
0 redo size
3382143 bytes sent via SQL*Net to client
54029 bytes received via SQL*Net from client
4876 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73121 rows processed
----方法3（statistics level=all的方式)
set autotrace off
alter session set statistics_level=all ;
select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID f4hgrku2d4usc, child number 1
-------------------------------------
select sex_id, first_name||' '||last_name full_name,
get_sex_name(sex_id) gender from people
Plan hash value: 2528372185
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 73121 |00:00:00.14 | 5295 |
| 1 | TABLE ACCESS FULL| PEOPLE | 1 | 80635 | 73121 |00:00:00.14 | 5295 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择18行。
----方法4(知道sql_id后，直接带入的方式）
select * from table(dbms_xplan.display_cursor('f4hgrku2d4usc'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID f4hgrku2d4usc, child number 0
-------------------------------------
select sex_id, first_name||' '||last_name full_name,
get_sex_name(sex_id) gender from people
Plan hash value: 2528372185
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 137 (100)| |
| 1 | TABLE ACCESS FULL| PEOPLE | 80635 | 16M| 137 (1)| 00:00:02 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择18行。
----方法5(10046TRACE）
set autotace off
alter session set statistics_level=typical;
alter session set events '10046 trace name context forever,level 12';
select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people;
alter session set events '10046 trace name context off';
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
exit
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_4824.trc d:\10046_2.txt sys=no sort=prsela,exeela,fchela
--结果发现，其实是SQL_ID=dyspxnp5ndqj1语句成漏网之鱼了。
SQL ID: dyspxnp5ndqj1
Plan Hash: 3096199304
SELECT NAME
FROM
SEX WHERE SEX_ID=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 73121 9.48 9.66 0 0 0 0
Fetch 73121 1.54 1.88 0 511847 0 73121
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 146242 11.02 11.55 0 511847 0 73121
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 94 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 71 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4876 8.68 8.68 0 5302 0 73121
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4878 8.68 8.69 0 5373 0 73121
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 94
Rows Row Source Operation
------- ---------------------------------------------------
73121 TABLE ACCESS FULL PEOPLE (cr=5295 pr=0 pw=0 time=142014 us cost=137 size=17497795 card=80635)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4876 0.00 0.02
SQL*Net message from client 4876 0.11 50.97
********************************************************************************
不真实的执行计划：
/*
一.关于获取执行计划的6种方法和各自区别。
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql
二. 执行计划中"真实执行计划” 是一个很重要的常识，这也就是方法1 和方法2 的最大缺陷了。
方法1的explain plan for和方法2的set autotrace on
例子主要是针对：绑定变量窥视与直方图
*/
---构建T表，数据，及主键
DROP TABLE t;
CREATE TABLE t
AS
SELECT rownum AS id, rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level <= 1000;
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
---收集统计信息
BEGIN
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns size 254'
);
END;
/
/*
下面我们将会用多种方法来查看如下语句的执行计划
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;
*/
----方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)
set linesize 1000
set pagesize 2000
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
explain plan for
SELECT count(pad) FROM t WHERE id < :id;
select * from table(dbms_xplan.display());
--明明应该是走全表扫描合适，居然走了索引读，原因是，这个执行计划是假的。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 50 | 5250 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<TO_NUMBER(:ID))
已选择15行。
----方法2(set autotrace on 方式)
/*set autotrace on
set autotrace traceonly
set autotrace traceonly explain
set autotrace traceonl statistics
*/
set autotrace traceonly
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
执行计划
----------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 50 | 5250 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<TO_NUMBER(:ID))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
----方法3（statistics level=all的方式)
set autotrace off
alter session set statistics_level=all ;
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 2 | TABLE ACCESS FULL| T | 1 | 988 | 989 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:ID)
已选择19行。
----方法4(知道sql_id后，直接带入的方式）
select * from table(dbms_xplan.display_cursor('asth1mx10aygn'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID asth1mx10aygn, child number 0
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
|* 2 | TABLE ACCESS FULL| T | 988 | 101K| 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:ID)
已选择19行。
----方法5(10046TRACE）
set autotace off
alter session set statistics_level=typical;
alter session set events '10046 trace name context forever,level 12';
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
alter session set events '10046 trace name context off';
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
exit
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_3144.trc d:\10046_3.txt sys=no sort=prsela,exeela,fchela
--观察发现，也是真实的执行计划，全表扫描！
SQL ID: asth1mx10aygn
Plan Hash: 2966233522
SELECT count(pad)
FROM
t WHERE id < :id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 19 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 19 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 94
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=19 pr=0 pw=0 time=0 us)
989 TABLE ACCESS FULL T (cr=19 pr=0 pw=0 time=494 us cost=7 size=103740 card=988)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 8.06 8.06
********************************************************************************
多执行计划：
/*
分结论4（一条SQL对应多个执行计划）：
一. 关于获取执行计划的6种方法和各自区别。
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql
二. 执行计划中"一条SQL对应多个计划” 也是一个很重要的常识，这只能靠方法4和方法6了。
：方法4的dbms_xplan.display_cursor+sql_id和方法6的awrsqrpt.sql。
*/
---构建T表，数据，及主键
sqlplus ljb/ljb
DROP TABLE t;
CREATE TABLE t AS SELECT * FROM DBA_OBJECTS where object_id is not null;
create index idx_object_id on t(object_id);
alter table T modify object_id not null;
set autotrace off
set linesize 1000
set pagesize 2000
alter session set statistics_level=all ;
select count(*) from t;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID cyzznbykb509s, child number 0
-------------------------------------
select count(*) from t
Plan hash value: 1131838604
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.10 | 170 | 162 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.10 | 170 | 162 |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 1 | 65318 | 73118 |00:00:00.09 | 170 | 162 |
----------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择18行。
connect yxl/yxl
drop table t purge;
CREATE TABLE t AS SELECT rownum id ,rownum+1 n FROM DBA_OBJECTS ;
set autotrace off
set linesize 1000
set pagesize 2000
alter session set statistics_level=all ;
select count(*) from t;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID cyzznbykb509s, child number 1
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 153 | 41 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 153 | 41 |
| 2 | TABLE ACCESS FULL| T | 1 | 62936 | 73120 |00:00:00.02 | 153 | 41 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择18行。
----(知道sql_id后，直接带入的方式）
select sql_id, child_number from v$sql where sql_id='cyzznbykb509s';
SQL_ID CHILD_NUMBER
------------- ------------
cyzznbykb509s 0
cyzznbykb509s 1
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cyzznbykb509s, child number 0
-------------------------------------
select count(*) from t
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 49 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 65318 | 49 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择18行。
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID cyzznbykb509s, child number 1
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 46 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 62936 | 46 (3)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择18行。
/*
第6种获取执行计划的方法awrsqrpt.sql同样也可以获取到多条执行计划
这个方法当一条SQL有多个执行计划的时候，可以在报表里输出。但是要确保在AWR的采集周期内的生成报表。
*/
获取执行计划的6种方法
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql
6种方法各自适用场合
1.如果某SQL执行很长时间才出结果或返回不了结果，这时就只能用方法1；
2.跟踪某条SQL最简单的方法是方法1，其次就是方法2；
3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；
4.如果SQL中含有函数，函数中套有SQL等多层调用，想准确分析只能使用方法5；
5.要想确保看到真实的执行计划，不能用方法1和方法2；
6.要想获取表被访问的次数，只能使用方法3；
单独型：
DROP TABLE bonus cascade constraints PURGE;
DROP TABLE emp cascade constraints PURGE;
DROP TABLE dept cascade constraints PURGE;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
execute dbms_stats.gather_table_stats(user, 'dept')
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17-04-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-05-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22-06-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('2-07-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-08-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('1-09-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('9-10-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('09-02-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, to_date('17-03-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, to_date('8-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('12-08-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('3-11-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, to_date('23-10-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
CREATE INDEX emp_job_i ON emp (job);
CREATE INDEX emp_mgr_i ON emp (mgr);
execute dbms_stats.gather_table_stats(user, 'emp')
CREATE TABLE bonus
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER,
comm NUMBER);
execute dbms_stats.gather_table_stats(user, 'bonus');
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
REM
--单独形
REM
SELECT deptno, count(*)
FROM emp
WHERE job = 'CLERK' AND sal < 1200
GROUP BY deptno;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5k3rhmx9hprh7, child number 1
-------------------------------------
SELECT deptno, count(*) FROM emp WHERE job = 'CLERK' AND sal < 1200
GROUP BY deptno
Plan hash value: 3067371962
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 2 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 2 |00:00:00.01 | 2 | 888K| 888K| 539K (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 3 | 3 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | EMP_JOB_I | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL"<1200)
3 - access("JOB"='CLERK')
Note
-----
- cardinality feedback used for this statement
已选择26行。
---也可试set autotrace 的获取方法
set autotrace traceonly
set linesize 1000
set pagesize 2000
SELECT deptno, count(*)
FROM emp
WHERE job = 'CLERK' AND sal < 1200
GROUP BY deptno;
执行计划
----------------------------------------------------------
Plan hash value: 3067371962
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 15 | 3 (34)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_I | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL"<1200)
3 - access("JOB"='CLERK')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
527 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
联合型(非关联)
/*
我们把有多个孩子的操作定义为联合型操作，其中孩子的操作互相独立，我们定义为非关联联合型操作
*/
DROP TABLE bonus cascade constraints PURGE;
DROP TABLE emp cascade constraints PURGE;
DROP TABLE dept cascade constraints PURGE;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
execute dbms_stats.gather_table_stats(user, 'dept')
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17-04-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-05-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22-06-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('2-07-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-08-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('1-09-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('9-10-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('09-02-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, to_date('17-03-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, to_date('8-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('12-08-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('3-11-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, to_date('23-10-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
CREATE INDEX emp_job_i ON emp (job);
CREATE INDEX emp_mgr_i ON emp (mgr);
execute dbms_stats.gather_table_stats(user, 'emp')
CREATE TABLE bonus
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER,
comm NUMBER);
execute dbms_stats.gather_table_stats(user, 'bonus');
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
PAUSe
REM
REM Unrelated-Combine Operations
--非关联联合型
REM
SELECT ename FROM emp
UNION ALL
SELECT dname FROM dept
UNION ALL
SELECT '%' FROM dual;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 781xq971h0y2p, child number 0
-------------------------------------
SELECT ename FROM emp UNION ALL SELECT dname FROM dept UNION ALL SELECT
'%' FROM dual
Plan hash value: 4181933179
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 19 |00:00:00.01 | 16 |
| 1 | UNION-ALL | | 1 | | 19 |00:00:00.01 | 16 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 8 |
| 4 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------
已选择17行。
联合型(相关联)01_nl：
/*
我们把有多个孩子的操作定义为联合型操作，其中一个孩子控制其他孩子的操作我们定义为关联联合型操作
让我们来看相关联联合型中的NL连接吧。
先访问的表返回多少条记录，后访问的表就被访问多少次，请注意和FLITER的差异。
*/
DROP TABLE bonus cascade constraints PURGE;
DROP TABLE emp cascade constraints PURGE;
DROP TABLE dept cascade constraints PURGE;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
execute dbms_stats.gather_table_stats(user, 'dept')
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17-04-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-05-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22-06-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('2-07-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-08-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('1-09-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('9-10-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('09-02-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, to_date('17-03-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, to_date('8-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('12-08-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('3-11-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, to_date('23-10-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
CREATE INDEX emp_job_i ON emp (job);
CREATE INDEX emp_mgr_i ON emp (mgr);
execute dbms_stats.gather_table_stats(user, 'emp')
CREATE TABLE bonus
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER,
comm NUMBER);
execute dbms_stats.gather_table_stats(user, 'bonus');
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
PAUSE
REM
REM Related-Combine Operations
--关联联合型
REM
REM Operation NESTED LOOPS
SELECT /*+ ordered use_nl(dept) index(dept) */ *
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.comm IS NULL
AND dept.dname != 'SALES';
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 3ux4g340c933p, child number 0
-------------------------------------
SELECT /*+ ordered use_nl(dept) index(dept) */ * FROM emp, dept WHERE
emp.deptno = dept.deptno AND emp.comm IS NULL AND dept.dname != 'SALES'
--11g执行计划---
Plan hash value: 2694310824
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 |00:00:00.01 | 22 |
| 1 | NESTED LOOPS | | 1 | | 8 |00:00:00.01 | 22 |
| 2 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 12 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 10 | 10 |00:00:00.01 | 8 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 10 | 1 | 10 |00:00:00.01 | 4 |
|* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 1 | 8 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."COMM" IS NULL)
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
5 - filter("DEPT"."DNAME"<>'SALES')
已选择25行。
--请注意在Oracle10g的执行计划：
SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 3ux4g340c933p, child number 0
-------------------------------------
SELECT /*+ ordered use_nl(dept) index(dept) */ * FROM emp, dept WHERE emp.deptno =
dept.deptno AND emp.comm IS NULL AND dept.dname != 'SALES'
--10g执行计划---
Plan hash value: 3487251775
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 10 | 8 |00:00:00.01 | 20 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 10 | 10 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 1 | 8 |00:00:00.01 | 12 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 10 | 1 | 10 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."COMM" IS NULL)
3 - filter("DEPT"."DNAME"<>'SALES')
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
23 rows selected.
11g中的rowid的访问优化提升分析比较：
/*
结论：用rowid读取同一个block的30行，在11g中仅仅耗费了3个逻辑读，而在10g中耗费30个逻辑读。
说明11g引入了对rowid读的新特性， 那就是，如果rowid是来自一个block，那么是可以重用的！
*/
---11g的情况
drop table t purge;
create table t as select * from dba_objects;
SELECT DBMS_ROWID.rowid_relative_fno (ROWID) REL_FNO,
DBMS_ROWID.rowid_block_number (ROWID) BLOCKNO,
DBMS_ROWID.rowid_row_number (ROWID) ROWNO,
ROWID
FROM T WHERE ROWNUM<=30;
REL_FNO BLOCKNO ROWNO ROWID
--------- ---------- ---------- ------------------
11 284259 0 AAAak/AALAABFZjAAA
11 284259 1 AAAak/AALAABFZjAAB
11 284259 2 AAAak/AALAABFZjAAC
11 284259 3 AAAak/AALAABFZjAAD
11 284259 4 AAAak/AALAABFZjAAE
11 284259 5 AAAak/AALAABFZjAAF
11 284259 6 AAAak/AALAABFZjAAG
11 284259 7 AAAak/AALAABFZjAAH
11 284259 8 AAAak/AALAABFZjAAI
11 284259 9 AAAak/AALAABFZjAAJ
11 284259 10 AAAak/AALAABFZjAAK
11 284259 11 AAAak/AALAABFZjAAL
11 284259 12 AAAak/AALAABFZjAAM
11 284259 13 AAAak/AALAABFZjAAN
11 284259 14 AAAak/AALAABFZjAAO
11 284259 15 AAAak/AALAABFZjAAP
11 284259 16 AAAak/AALAABFZjAAQ
11 284259 17 AAAak/AALAABFZjAAR
11 284259 18 AAAak/AALAABFZjAAS
11 284259 19 AAAak/AALAABFZjAAT
11 284259 20 AAAak/AALAABFZjAAU
11 284259 21 AAAak/AALAABFZjAAV
11 284259 22 AAAak/AALAABFZjAAW
11 284259 23 AAAak/AALAABFZjAAX
11 284259 24 AAAak/AALAABFZjAAY
11 284259 25 AAAak/AALAABFZjAAZ
11 284259 26 AAAak/AALAABFZjAAa
11 284259 27 AAAak/AALAABFZjAAb
11 284259 28 AAAak/AALAABFZjAAc
11 284259 29 AAAak/AALAABFZjAAd
已选择30行。
set linesize 1000
set pagesize 2000
set autotrace traceonly
select * from t
where rowid in ('AAAak/AALAABFZjAAA',
'AAAak/AALAABFZjAAB',
'AAAak/AALAABFZjAAC',
'AAAak/AALAABFZjAAD',
'AAAak/AALAABFZjAAE',
'AAAak/AALAABFZjAAF',
'AAAak/AALAABFZjAAG',
'AAAak/AALAABFZjAAH',
'AAAak/AALAABFZjAAI',
'AAAak/AALAABFZjAAJ',
'AAAak/AALAABFZjAAK',
'AAAak/AALAABFZjAAL',
'AAAak/AALAABFZjAAM',
'AAAak/AALAABFZjAAN',
'AAAak/AALAABFZjAAO',
'AAAak/AALAABFZjAAP',
'AAAak/AALAABFZjAAQ',
'AAAak/AALAABFZjAAR',
'AAAak/AALAABFZjAAS',
'AAAak/AALAABFZjAAT',
'AAAak/AALAABFZjAAU',
'AAAak/AALAABFZjAAV',
'AAAak/AALAABFZjAAW',
'AAAak/AALAABFZjAAX',
'AAAak/AALAABFZjAAY',
'AAAak/AALAABFZjAAZ',
'AAAak/AALAABFZjAAa',
'AAAak/AALAABFZjAAb',
'AAAak/AALAABFZjAAc',
'AAAak/AALAABFZjAAd');
执行计划
----------------------------------------------------------
Plan hash value: 749296034
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T | 1 | 219 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
2381 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
--研究10g的情况
SELECT DBMS_ROWID.rowid_relative_fno (ROWID) REL_FNO,
DBMS_ROWID.rowid_block_number (ROWID) BLOCKNO,
DBMS_ROWID.rowid_row_number (ROWID) ROWNO,
ROWID
FROM T WHERE ROWNUM<=30;
REL_FNO BLOCKNO ROWNO ROWID
---------- ---------- ---------- ------------------
4 212 0 AACSVaAAEAAAADUAAA
4 212 1 AACSVaAAEAAAADUAAB
4 212 2 AACSVaAAEAAAADUAAC
4 212 3 AACSVaAAEAAAADUAAD
4 212 4 AACSVaAAEAAAADUAAE
4 212 5 AACSVaAAEAAAADUAAF
4 212 6 AACSVaAAEAAAADUAAG
4 212 7 AACSVaAAEAAAADUAAH
4 212 8 AACSVaAAEAAAADUAAI
4 212 9 AACSVaAAEAAAADUAAJ
4 212 10 AACSVaAAEAAAADUAAK
4 212 11 AACSVaAAEAAAADUAAL
4 212 12 AACSVaAAEAAAADUAAM
4 212 13 AACSVaAAEAAAADUAAN
4 212 14 AACSVaAAEAAAADUAAO
4 212 15 AACSVaAAEAAAADUAAP
4 212 16 AACSVaAAEAAAADUAAQ
4 212 17 AACSVaAAEAAAADUAAR
4 212 18 AACSVaAAEAAAADUAAS
4 212 19 AACSVaAAEAAAADUAAT
4 212 20 AACSVaAAEAAAADUAAU
4 212 21 AACSVaAAEAAAADUAAV
4 212 22 AACSVaAAEAAAADUAAW
4 212 23 AACSVaAAEAAAADUAAX
4 212 24 AACSVaAAEAAAADUAAY
4 212 25 AACSVaAAEAAAADUAAZ
4 212 26 AACSVaAAEAAAADUAAa
4 212 27 AACSVaAAEAAAADUAAb
4 212 28 AACSVaAAEAAAADUAAc
4 212 29 AACSVaAAEAAAADUAAd
30 rows selected.
set linesize 1000
set pagesize 2000
set autotrace traceonly
select * from t
where rowid in ('AACSVaAAEAAAADUAAA',
'AACSVaAAEAAAADUAAB',
'AACSVaAAEAAAADUAAC',
'AACSVaAAEAAAADUAAD',
'AACSVaAAEAAAADUAAE',
'AACSVaAAEAAAADUAAF',
'AACSVaAAEAAAADUAAG',
'AACSVaAAEAAAADUAAH',
'AACSVaAAEAAAADUAAI',
'AACSVaAAEAAAADUAAJ',
'AACSVaAAEAAAADUAAK',
'AACSVaAAEAAAADUAAL',
'AACSVaAAEAAAADUAAM',
'AACSVaAAEAAAADUAAN',
'AACSVaAAEAAAADUAAO',
'AACSVaAAEAAAADUAAP',
'AACSVaAAEAAAADUAAQ',
'AACSVaAAEAAAADUAAR',
'AACSVaAAEAAAADUAAS',
'AACSVaAAEAAAADUAAT',
'AACSVaAAEAAAADUAAU',
'AACSVaAAEAAAADUAAV',
'AACSVaAAEAAAADUAAW',
'AACSVaAAEAAAADUAAX',
'AACSVaAAEAAAADUAAY',
'AACSVaAAEAAAADUAAZ',
'AACSVaAAEAAAADUAAa',
'AACSVaAAEAAAADUAAb',
'AACSVaAAEAAAADUAAc',
'AACSVaAAEAAAADUAAd' );
Execution Plan
----------------------------------------------------------
Plan hash value: 749296034
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 189 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T | 1 | 189 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
2386 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
11g中的rowid优化引发的执行计划优化：
--环境构造
--研究Nested Loops Join访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
);
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
);
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
CREATE INDEX t1_n ON t1 (n);
CREATE INDEX t2_t1_id ON t2(t1_id);
--11g环境
set linesize 1000
set pagesize 2000
alter session set statistics_level=all ;
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 7xxxx7qhk4wvx, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n = 19
Plan hash value: 1300700041
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads|
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 | 4 |
| 1 | NESTED LOOPS | | 1 | | 1 |00:00:00.01 | 7 | 4 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
|* 4 | INDEX RANGE SCAN | T1_N | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 5 | INDEX RANGE SCAN | T2_T1_ID | 1 | 1 | 1 |00:00:00.01 | 3 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |00:00:00.01 | 1 | 0 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
已选择28行。
--10g环境
set linesize 1000
set pagesize 2000
alter session set statistics_level=all ;
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 7xxxx7qhk4wvx, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n = 19
Plan hash value: 2669480776
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |00:00:00.01 | 7 | 1 |
| 2 | NESTED LOOPS | | 1 | 1 | 3 |00:00:00.01 | 6 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
|* 4 | INDEX RANGE SCAN | T1_N | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 5 | INDEX RANGE SCAN | T2_T1_ID | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement
26 rows selected.
联合型(相关联)02——filter：
/*
我们把有多个孩子的操作定义为联合型操作，其中一个孩子控制其他孩子的操作我们定义为关联联合型操作
先访问的表返回多少不重复记录，后访问的表就访问多少次，请注意这个不重复的关键字，明确和NL的差异。
*/
DROP TABLE bonus cascade constraints PURGE;
DROP TABLE emp cascade constraints PURGE;
DROP TABLE dept cascade constraints PURGE;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
execute dbms_stats.gather_table_stats(user, 'dept')
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17-04-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-05-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22-06-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('2-07-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-08-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('1-09-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('9-10-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('09-02-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, to_date('17-03-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, to_date('8-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('12-08-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('3-11-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, to_date('23-10-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
CREATE INDEX emp_job_i ON emp (job);
CREATE INDEX emp_mgr_i ON emp (mgr);
execute dbms_stats.gather_table_stats(user, 'emp')
CREATE TABLE bonus
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER,
comm NUMBER);
execute dbms_stats.gather_table_stats(user, 'bonus');
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
SELECT *
FROM emp
WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM dept
WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
AND NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM bonus
WHERE bonus.ename = emp.ename);
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID 143p2rxtjxj0m, child number 0
-------------------------------------
SELECT * FROM emp WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM dept WHERE dept.dname = 'SALES' AND
dept.deptno = emp.deptno) AND NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM bonus WHERE bonus.ename = emp.ename)
Plan hash value: 2272441335
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 |00:00:00.01 | 14 |
|* 1 | FILTER | | 1 | | 8 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 | 1 |00:00:00.01 | 6 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 3 | 1 | 3 |00:00:00.01 | 3 |
|* 5 | TABLE ACCESS FULL | BONUS | 8 | 1 | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL AND IS NULL))
3 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("BONUS"."ENAME"=:B1)
已选择28行。
--原因分析：
--为什么执行计划中ID=3的地方STARTS 3次，因为虽然有8条记录，但是不重复的只有3个（ACCOUNTING、RESEARCH、 SALES)
SELECT dname, count(*)
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname;
DNAME COUNT(*)
-------------- ----------
ACCOUNTING 3
RESEARCH 5
SALES 6
--接下来的为什么执行计划中ID=5的地方是STARTS 8次，因为返回8条
SELECT ename
FROM emp
WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM dept
WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno);
ENAME
----------
SMITH
JONES
CLARK
SCOTT
KING
ADAMS
FORD
MILLER
已选择8行。
--做一些试验看看
update emp set deptno=40 where deptno=20 and rownum=1;
commit;
SELECT dname, count(*)
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname;
DNAME COUNT(*)
-------------- ----------
ACCOUNTING 3
OPERATIONS 1
RESEARCH 4
SALES 6
SELECT *
FROM emp
WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM dept
WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
AND NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM bonus
WHERE bonus.ename = emp.ename);
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 143p2rxtjxj0m, child number 0
-------------------------------------
SELECT * FROM emp WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM dept WHERE dept.dname = 'SALES' AND
dept.deptno = emp.deptno) AND NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM bonus WHERE bonus.ename = emp.ename)
Plan hash value: 2272441335
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 |00:00:00.01 | 16 |
|* 1 | FILTER | | 1 | | 8 |00:00:00.01 | 16 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 1 | 1 |00:00:00.01 | 8 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 4 | 1 | 4 |00:00:00.01 | 4 |
|* 5 | TABLE ACCESS FULL | BONUS | 8 | 1 | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL AND IS NULL))
3 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("BONUS"."ENAME"=:B1)
已选择28行。
--有变化！执行计划中ID=3的地方STARTS从3次变为4次！不过ID=5的地方的STARTS依然是8次。
原因很简单，如下：
ENAME
----------
SMITH
JONES
CLARK
SCOTT
KING
ADAMS
FORD
MILLER
--继续做试验，删除SMITH的记录。
delete from emp where ename='SMITH';
commit;
--然后再执行观察执行计划。
SELECT *
FROM emp
WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM dept
WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
AND NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM bonus
WHERE bonus.ename = emp.ename);
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 143p2rxtjxj0m, child number 0
-------------------------------------
SELECT * FROM emp WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM dept WHERE dept.dname = 'SALES' AND
dept.deptno = emp.deptno) AND NOT EXISTS (SELECT /*+ no_unnest */ 0
FROM bonus WHERE bonus.ename = emp.ename)
Plan hash value: 2272441335
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 14 |
|* 1 | FILTER | | 1 | | 7 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 13 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 | 1 |00:00:00.01 | 6 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 3 | 1 | 3 |00:00:00.01 | 3 |
|* 5 | TABLE ACCESS FULL | BONUS | 7 | 1 | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL AND IS NULL))
3 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("BONUS"."ENAME"=:B1)
已选择28行。
--这下ID=5处的STARTS从原来的8变成7了。
联合型(相关联)---update
/*
我们把有多个孩子的操作定义为联合型操作，其中一个孩子控制其他孩子的操作我们定义为关联联合型操作
*/
DROP TABLE bonus cascade constraints PURGE;
DROP TABLE emp cascade constraints PURGE;
DROP TABLE dept cascade constraints PURGE;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
execute dbms_stats.gather_table_stats(user, 'dept')
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17-04-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-05-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22-06-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('2-07-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-08-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('1-09-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('9-10-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('09-02-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, to_date('17-03-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, to_date('8-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('12-08-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('3-11-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, to_date('23-10-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
CREATE INDEX emp_job_i ON emp (job);
CREATE INDEX emp_mgr_i ON emp (mgr);
execute dbms_stats.gather_table_stats(user, 'emp')
CREATE TABLE bonus
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER,
comm NUMBER);
execute dbms_stats.gather_table_stats(user, 'bonus');
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
REM Operation UPDATE
UPDATE emp e1
SET sal = (SELECT avg(sal) FROM emp e2 WHERE e2.deptno = e1.deptno),
comm = (SELECT avg(comm) FROM emp e3);
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID aj9bzs3ptc6wn, child number 0
-------------------------------------
UPDATE emp e1 SET sal = (SELECT avg(sal) FROM emp e2 WHERE e2.deptno =
e1.deptno), comm = (SELECT avg(comm) FROM emp e3)
Plan hash value: 1690508028
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 65 |
| 1 | UPDATE | EMP | 1 | | 0 |00:00:00.01 | 65 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 7 |
| 3 | SORT AGGREGATE | | 3 | 1 | 3 |00:00:00.01 | 21 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 5 | 14 |00:00:00.01 | 21 |
| 5 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 6 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E2"."DEPTNO"=:B1)
已选择24行。
rollback;
联合型(相关联)04_树形
/*
相关联联合型中的CONNECT BY WITH FLITERING操作吧。
*/
DROP TABLE bonus cascade constraints PURGE;
DROP TABLE emp cascade constraints PURGE;
DROP TABLE dept cascade constraints PURGE;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
execute dbms_stats.gather_table_stats(user, 'dept')
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17-04-1980','DD-MM-YYYY'), 800, NULL,20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-05-1981','DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22-06-1981','DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('2-07-1981', 'DD-MM-YYYY'), 2975, NULL,20);
INSERT INTO emp VALUES (7654, 'MARTIN','SALESMAN', 7698, to_date('28-08-1981','DD-MM-YYYY'), 1250, 1400,30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('1-09-1981', 'DD-MM-YYYY'), 2850, NULL,30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('9-10-1981', 'DD-MM-YYYY'), 2450, NULL,10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('09-02-1982','DD-MM-YYYY'), 3000, NULL,20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL,to_date('17-03-1981','DD-MM-YYYY'), 5000, NULL,10);
INSERT INTO emp VALUES (7844, 'TURNER','SALESMAN', 7698, to_date('8-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('12-08-1983','DD-MM-YYYY'), 1100, NULL,20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('3-11-1981', 'DD-MM-YYYY'), 950, NULL,30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981', 'DD-MM-YYYY'), 3000, NULL,20);
INSERT INTO emp VALUES (7934, 'MILLER','CLERK', 7782, to_date('23-10-1982','DD-MM-YYYY'), 1300, NULL,10);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
CREATE INDEX emp_job_i ON emp (job);
CREATE INDEX emp_mgr_i ON emp (mgr);
execute dbms_stats.gather_table_stats(user, 'emp')
CREATE TABLE bonus
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER,
comm NUMBER);
execute dbms_stats.gather_table_stats(user, 'bonus');
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
PAUSE
REM Operation CONNECT BY WITH FILTERING
COLUMN ename FORMAT A10
COLUMN manager FORMAT A10
SELECT /*+ connect_by_filtering */ level, rpad('-',level-1,'-')||ename AS ename, prior ename AS manager
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID 62wv394wc9zqa, child number 0
-------------------------------------
SELECT /*+ connect_by_filtering */ level, rpad('-',level-1,'-')||ename
AS ename, prior ename AS manager FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
Plan hash value: 1519159851
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 15 | | | |
|* 1 | CONNECT BY WITH FILTERING | | 1 | | 14 |00:00:00.01 | 15 | 2048 | 2048 | 2048 (0)|
|* 2 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 3 | NESTED LOOPS | | 4 | 2 | 13 |00:00:00.01 | 8 | | | |
| 4 | CONNECT BY PUMP | | 4 | | 14 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 2 | 13 |00:00:00.01 | 8 | | | |
|* 6 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 2 | 13 |00:00:00.01 | 5 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MGR"=PRIOR NULL)
2 - filter("MGR" IS NULL)
6 - access("connect$_by$_pump$_002"."PRIOR empno "="MGR")
filter("MGR" IS NOT NULL)
已选择28行。
原理分析：
SELECT /*+ connect_by_filtering */ level, rpad('-',level-1,'-')||ename AS ename, prior ename AS manager
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
LEVEL ENAME MANAGER
---------- ---------- ----------
1 KING
2 -JONES KING
3 --SCOTT JONES
4 ---ADAMS SCOTT
3 --FORD JONES
4 ---SMITH FORD
2 -BLAKE KING
3 --ALLEN BLAKE
3 --WARD BLAKE
3 --MARTIN BLAKE
3 --TURNER BLAKE
3 --JAMES BLAKE
2 -CLARK KING
3 --MILLER CLARK
已选择14行。
/*
--为什么执行计划中ID=4的地方STARTS 4次，因为完成4次执行
第1次得到KING
第2次得到 JONES、BLAKE、CLARK
第3次得到 SCOTT、 FORD、 ALLEN、 WARD、 MARTIN、 TURNER、 JAMES、 MILLER
第4次得到 ADAMS、 SMITH
为什么执行计划中ID=6的部分是执行14次，因为返回14条。
*/
SELECT /*+ no_connect_by_filtering full(emp) */ level, emp.*
FROM emp
START WITH mgr = 7839
CONNECT BY PRIOR empno = mgr;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
