要进行SQL的优化，必须要学会读执行计划。 1. autotrace 命令 SQL show autotrace; autotrace OFF SQL set autotrace; Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] set autotrace on 表示输出SQL的结果，以及执行计划和统计
要进行SQL的优化，必须要学会读执行计划。
1. autotrace 命令
SQL> show autotrace;
autotrace OFF
SQL> set autotrace;
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
set autotrace on 表示输出SQL的结果，以及执行计划和统计信息
set autotrace traceonly 表示不输出SQL的结果；但是输出执行计划和统计信息
set autotrace traceonly explain 表示进输出执行计划
set autotrace traceonly statistics 表示仅输出统计信息
所以一般用：set autotrace traceonly
2. 示例一：
SQL> set autotrace on;
SQL> show autotrace;
autotrace ON EXPLAIN STATISTICS
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from test_index;
COUNT(*)
----------
50319
Execution Plan <<=====>> 执行计划 EXP[LAIN]
----------------------------------------------------------
Plan hash value: 4201568295 <<=====>> 执行计划hash值
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 155 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_INDEX | 50319 | 155 (2)| 00:00:02 |
-------------------------------------------------------------------------
Statistics<<=====>> 统计信息 STAT[ISTICS]
----------------------------------------------------------
171 recursive calls <<===>> 递归调用：即解析该SQL语句调用了多少次函数来访问row cache
0 db block gets <<===>> 缓存读
719 consistent gets <<===>> 一致性读，是SQL优化的目标(属于缓存读)
0 physical reads <<===>> 物理读
0 redo size <<===>> 产生了多少日志
413 bytes sent via SQL*Net to client <<===>>服务端发送多少字节
385 bytes received via SQL*Net from client <<===>>客户端发送了多少字节
2 SQL*Net roundtrips to/from client <<===>> 客户端/服务器来回次数
3 sorts (memory) <<===>> 内存排序
0 sorts (disk) <<===>> 磁盘排序
1 rows processed <<===>> 处理/产生了多少行结果
SQL> select count(*) from test_index;
COUNT(*)
----------
50319
Execution Plan
----------------------------------------------------------
Plan hash value: 4201568295
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 155 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_INDEX | 50319 | 155 (2)| 00:00:02 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
696 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
分析：
因为我们执行alter system flush shared_pool，所以library cache为空；所以第一次执行select count(*) from test_index; 会导致了硬解析，所以会导致 171 recursive calls，而第二次执行该SQL语句时，会走软解析，所以第二次执行时 0 recursive calls。
3.几个重要的概要：
1)逻辑读：也叫缓存读/内存读，buffer gets
2)物理读：也叫磁盘读，physical reads
3)一致性读：consistent gets are query mode gets. This might entail a reconstruction of the block with the undo (rollback) mechanism.
普 通的select语句属于query mode，都会产生一致性读，一致性读可能涉及到cr块的构造。当一个事务正在update一个数据块时，其它的session来了一个针对同一个数据块 的select，那么因为事务的“读一致性要求”，第二个session不能够看到第一个session未提交的事务做的修改，所以会利用undo段来构 造出一个数据块，该数据块与被update之前的一模一样，select返回的值就是该构造出来的数据块中的值。注意select没有构造cr块，也叫做 consistent gets.
4)db block gets: 是dml语句导致的逻辑读。dml语句的修改操作的过程是：先判断要修改的数据块是否在buffer cache中，如果在就直接读取修改，如果不在buffer cache中，那么先读取到内存中，然后再修改。所以不管dml操作是否发生物理读，但一定会发生逻辑读。而db block gets就是dml语句导致的逻辑读。
5)buffer gets = db block gets + consistent gets
current mode: 一般而言 dml 语句都属于 current mode.
4. 阅读执行计划：
SQL> select count(*) from dba_objects;
Execution Plan
----------------------------------------------------------
Plan hash value: 2598313856
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 144 (5)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | DBA_OBJECTS | 48374 | | 144 (5)| 00:00:02 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN | | 51423 | 4117K| 143 (5)| 00:00:02 |
| 6 | TABLE ACCESS FULL | USER$ | 62 | 186 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | OBJ$ | 51423 | 3967K| 140 (4)| 00:00:02 |
|* 8 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 16 | 1 (0)| 00:00:01 |
| 11 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7
OR "I"."TYPE#"=9))=1)
5 - access("O"."OWNER#"="U"."USER#")
7 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_'
AND "O"."LINKNAME" IS NULL)
8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
9 - access("I"."OBJ#"=:B1)
13 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5322 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们看到上面的执行计划表格中，列出了14条Operation，那么这些Operation的执行顺序是怎样的呢？关键在于我们要理解执行计划——执行计划其实是一棵二叉树：
我们利用plan_hash_value来查询一下执行计划中的信息
SQL> select id,parent_id,depth,sql_id from v$sql_plan where plan_hash_value='2598313856';
ID PARENT_ID DEPTH SQL_ID
---------- ---------- ---------- -------------
0 0 g4pkmrqrgxg3b
1 0 1 g4pkmrqrgxg3b
2 1 2 g4pkmrqrgxg3b
3 2 3 g4pkmrqrgxg3b
4 3 4 g4pkmrqrgxg3b
5 4 5 g4pkmrqrgxg3b
6 5 6 g4pkmrqrgxg3b
7 5 6 g4pkmrqrgxg3b
8 4 5 g4pkmrqrgxg3b
9 8 6 g4pkmrqrgxg3b
10 3 4 g4pkmrqrgxg3b
11 10 5 g4pkmrqrgxg3b
12 10 5 g4pkmrqrgxg3b
13 12 6 g4pkmrqrgxg3b
14 rows selected.
我们根据ID和PARENT_ID列就可以构造出一棵二叉树出来，然后按照深度优先遍历这棵二叉树就可以得到这些Operation的执行的先后顺序。当我们熟练之后，可以直接就写出他们的执行顺序来：6,7,5,9,8,4,11,13,12,10,3,2,1,0。
670
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
