本文章为网络笔记,看了warehouse老师的视频受益匪浅,更是感觉自己技术太过初级,特写了本笔记,方便以后反复学习!
如有任何不妥,请发邮件至102448567@qq.com删除文章!
关于warehouse:
http://blog.itpub.net/19602/viewspace-1059211/
11gR2视频第四版 8_04_执行计划
执行计划:sql
语句执行的详细过程
有很多方法
在sqlplus
中默认是关闭的[]
为可选项,{}
为必选项,|
隔开的是可选项,只能选择其中的一个
SQL> show autotrace
autotrace OFF
有哪些选项
SQL> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> select count(1) from tt2 where object_id=2;
COUNT(1)
----------
16
Execution Plan --执行计划
----------------------------------------------------------
Plan hash value: 1151720287
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX_TT2 | 16 | 208 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
--执行计划结束,下面是统计信息
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
75 consistent gets
1 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 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 on stat
只要统计信息:执行这个sql
语句对资源的消耗汇总
set autotrace traceonly exp
只显示执行计划不显示sql
的结果
SQL> set autotrace on exp
SQL> select count(1) from tt2 where object_id=2;
COUNT(1)
----------
16
############
id:步骤id
Operation:操作的名字
Name:操作的对象的名字
Rows:每一步访问了多少行
Bytes:多少个字节
Cost:成本,一个没有单位的数值,越大消耗的资源越大,主要和IO和CPU有关
Time:消耗的时间
重点关注数值非常大的行
############
Execution Plan
----------------------------------------------------------
Plan hash value: 1151720287
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX_TT2 | 16 | 208 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
因为object_id
上有索引所以上面的执行计划最先执行id
是3
的步骤,因为不是唯一索引,找到object_id
就会找到rowid
,然后就确定了一行,对于上面的sql
来说执行完id3
之后就是id2
(找到了之后就count
聚合了)然后就是id1
(输出了)
改变一下sql
SQL> select count(object_name) from tt2 where object_id=2;
COUNT(OBJECT_NAME)
------------------
16
Execution Plan
----------------------------------------------------------
Plan hash value: 3949934342
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 18 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TT2 | 16 | 1264 | 18 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TT2 | 16 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information --谓词信息 (identified by operation id):
---------------------------------------------------
--相当于是告诉你在哪一步用到了where子句,也就是3前边有个*的原因
3 - access("OBJECT_ID"=2) --这里是access还有可能是filter
Note --注意
-----
--动态取样被这个语句使用,级别是2 啥意思?看后面
- dynamic sampling used for this statement (level=2)
上面的sql
执行顺序先是id3
(走索引范围扫描)然后是id2
(在表中通过index
的rowid
去访问)然后是id1
(count
聚合)最后是id0
(输出)
这里的id2
与上一个sql
不一样是因为这次count
的object_name
在index
的leaf
中没有所以需要到表中去找,而上一个是count(1)
直接在index
中就可以了
Note --注意
-----
--动态取样被这个语句使用,级别是2 啥意思?
- dynamic sampling used for this statement (level=2)
意思是Oracle
生成这个执行计划的时候要用到optimizer statistics
(优化器统计信息)
optimizer statistics分为三种
1.table
select * from dba_tabes where table_name = ‘TT2’;
其中的blocks:表示存放这个表里的数据用了多少个块
num_rows:表里一共有多少行数据
avg_row_len:平均每一行的长度是多少个字节
这三个信息能宏观的说明这个表大不大
2.index
3.column
正是因为tt2
这个表没有优化器统计信息,Oracle
生成执行计划的时候没有参考依据,所以Oracle
进行了dynamic sampling
(动态取样),动态取样受到了一个参数的限制
SQL> show parameter dynamic;
NAME TYPE VALUE
----------------------------- ----------- -------------
optimizer_dynamic_sampling integer 2
Dynamic Statistics Levels(11.2)
https://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94759
下面的参数是11204
的时候默认level
就是2
SQL> show parameter optimizer_features_enable;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 11.2.0.4
如果不让Oracle
动态取样就把optimizer_dynamic_sampling
设置成0
SQL> alter session set optimizer_dynamic_sampling = 0;
Session altered.
下面就已经没有了note
的提示信息
SQL> select count(object_name) from tt2 where object_id+0=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1424956034
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 878 (1)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
|* 2 | TABLE ACCESS FULL| TT2 | 2637 | 203K| 878 (1)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"+0=2)
把optimizer_dynamic_sampling
改回来,改成2
还可以在sql
语句中直接屏蔽掉提示动态取样
SQL> select /*+ dynamic_sampling(0) */count(object_name) from tt2 where object_id+0=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1424956034
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 878 (1)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
|* 2 | TABLE ACCESS FULL| TT2 | 2637 | 203K| 878 (1)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"+0=2)
或者要是这个表中有了统计信息Oracle
就不会提示动态取样了
SQL> exec dbms_stats.gather_table_stats('sys','tt2');
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,avg_row_len from dba_tables where table_name = 'TT2';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
247104 3229 89
SQL> select count(object_name) from tt2 where object_id+0=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1424956034
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 878 (1)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TT2 | 16 | 400 | 878 (1)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"+0=2)
什么是优化器统计信息呢?
为Oracle
生成执行计划的时候提供了参考的依据比如dba_tables
中的blocks、num_rows、avg_row_len
表如果很小Oracle
就会直接选择TABLE ACCESS FULL
,如果表很大就会评估是不是使用Index
效率会更高
改一下sql
让谓词信息中的access
变成filter
SQL> select count(object_name) from tt2 where object_id+0=2;
COUNT(OBJECT_NAME)
------------------
16
Execution Plan
----------------------------------------------------------
Plan hash value: 1424956034
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 878 (1)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
|* 2 | TABLE ACCESS FULL| TT2 | 36 | 2844 | 878 (1)| 00:00:11 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"+0=2)
Note
-----
- dynamic sampling used for this statement (level=2)
这个执行计划就没有走index
而且他的rows
不是16
因为他是估算的,不是真实的结果,通过index
的时候估算的是正确的,没有走index
估算的有偏差,cost
和bytes
也差了很多,显然不走index
效率很差
access和filter的区别
access代表的是访问的路径(object_id=2)
1.TABLE ACCESS FULL(直接在表里找)
2.INDEX RANGE SCAN(可以在index里找)
凡是access就说明where子句条件中是有索引的
filter表示就是过滤条件不影响结果
执行计划的顺序:
原则
1.缩进最多的先执行
2.缩进相同的先执行上面的
栗子:
下面的执行顺序就是6-8-9-7-5-11-12-10-4-14-16-15-13-3-2-1-0
SQL> select count(1) from dba_objects;
Execution Plan
----------------------------------------------------------
Plan hash value: 2509769952
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 53 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | DBA_OBJECTS | 15453 | | 53 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN | | 15452 | 1554K| 53 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | I_USER2 | 46 | 184 | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 15452 | 1493K| 52 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | I_USER2 | 46 | 1012 | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | OBJ$ | 15452 | 1161K| 51 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 11 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 17 | 0 (0)| 00:00:01 |
| 14 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS CLUSTER| USER$ | 1 | 4 | 0 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
"O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5
OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR
"O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR
"O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_na
me')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS
(SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE
"O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND
"U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_editio
n_id')))))
5 - access("O"."SPARE3"="U"."USER#")
7 - access("O"."OWNER#"="U"."USER#")
9 - filter("O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."TYPE#"<>10 AND "O"."LINKNAME"
IS NULL AND BITAND("O"."FLAGS",128)=0)
11 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','
current_edition_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','
current_edition_id')))
12 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
16 - access("L"."OWNER#"="U"."USER#")