本文详细介绍了如何分析SQL性能问题，通过查看执行计划来定位性能瓶颈，并提供了三种查看执行计划的方法。同时，文章解释了执行计划中的关键概念，如Cardinality（基数）、谓词说明、统计信息等，帮助理解SQL性能优化的原理。
如果要分析某条 SQL 的性能问题，通常我们要先看 SQL 的执行计划，看看 SQL 的每一步执行是否存在问题。 如果一条 SQL 平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。
看懂执行计划也就成了 SQL 优化的先决条件。 这里的 SQL 优化指的是 SQL 性能问题的定位，定位后就可以解决问题。
一． 查看执行计划的三种方法
1.1 设置 autotrace
| 序号 | 命令 | 解释 |
| 1 | SET AUTOTRACE OFF | 此为默认值，即关闭 Autotrace |
| 2 | SET AUTOTRACE ON EXPLAIN | 只显示执行计划 |
| 3 | SET AUTOTRACE ON STATISTICS | 只显示执行的统计信息 |
| 4 | SET AUTOTRACE ON | 包含 2,3 两项内容 |
| 5 | SET AUTOTRACE TRACEONLY | 与 ON 相似，但不显示语句的执行结果 |
SQL> set autotrace on
SQL> select * from dave;
ID NAME
---------- ----------
8 安庆
1 dave
2 bl
1 bl
2 dave
3 dba
4 sf-express
5 dmm
已选择 8 行。
执行计划
----------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
609 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)
8 rows processed
SQL>
1.2 使用 SQL
SQL>EXPLAIN PLAN FOR sql 语句 ;
SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
示例：
SQL> EXPLAIN PLAN FOR SELECT * FROM DAVE;
已解释。
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
已选择 8 行。
执行计划
----------------------------------------------------------
Plan hash value: 2137789089
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
25 recursive calls
12 db block gets
168 consistent gets
0 physical reads
0 redo size
974 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed
SQL>
1.3 使用 Toad,PL/SQL Developer 工具
二． Cardinality （基数） / rows
Cardinality 值表示 CBO 预期从一个行源（ row source ）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。 在 Oracle 9i 中的执行计划中， Cardinality 缩写成 Card 。 在 10g 中， Card 值被 rows 替换。
这是 9i 的一个执行计划，我们可以看到关键字 Card ：
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=402)
1 0 TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2 Card=1 Bytes=402)
Oracle 10g 的执行计划，关键字换成了 rows ：
执行计划
----------------------------------------------------------
Plan hash value: 2137789089
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Cardinality 的值对于 CBO 做出正确的执行计划来说至关重要。 如果 CBO 获得的 Cardinality 值不够准确（通常是没有做分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致 CBO 错误的制定出执行计划。
在多表关联查询或者 SQL 中有子查询时，每个关联表或子查询的 Cardinality 的值对主查询的影响都非常大，甚至可以说， CBO 就是依赖于各个关联表或者子查询 Cardinality 值计算出最后的执行计划。
对于多表查询， CBO 使用每个关联表返回的行数（ Cardinality ）决定用什么样的访问方式来做表关联（如 Nested loops Join 或 hash Join ）。
多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP
http://blog.youkuaiyun.com/tianlesoftware/archive/2010/08/20/5826546.aspx
对于子查询，它的 Cardinality 将决定子查询是使用索引还是使用全表扫描的方式访问数据。
三． SQL 的执行计划
生成 SQL 的执行计划是 Oracle 在对 SQL 做硬解析时的一个非常重要的步骤，它制定出一个方案告诉 Oracle 在执行这条 SQL 时以什么样的方式访问数据：索引还是全表扫描，是 Hash Join 还是 Nested loops Join 等。 比如说某条 SQL 通过使用索引的方式访问数据是最节省资源的，结果 CBO 作出的执行计划是全表扫描，那么这条 SQL 的性能必然是比较差的。
Oracle SQL 的硬解析和软解析
http://blog.youkuaiyun.com/tianlesoftware/archive/2010/04/08/5458896.aspx
示例：
SQL> SET AUTOTRACE TRACEONLY; -- 只显示执行计划，不显示结果集
SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;
已选择 13 行。
执行计划
----------------------------------------------------------
Plan hash value: 992080948
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 988 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 988 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 494 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 494 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."EMPNO"="B"."MGR")
filter("A"."EMPNO"="B"."MGR")
5 - filter("B"."MGR" IS NOT NULL)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
2091 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed
SQL>
图片是 Toad 工具查看的执行计划。 在 Toad 里面，很清楚的显示了执行的顺序。 但是如果在 SQLPLUS 里面就不是那么直接。 但我们也可以判断： 一般按缩进长度来判断，缩进最大的最先执行，如果有 2 行缩进一样，那么就先执行上面的。
3.1 执行计划中字段解释：
ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。
Operation ： 当前操作的内容。
Rows ： 当前操作的 Cardinality ， Oracle 估计当前操作的返回结果集。
Cost （ CPU ）： Oracle 计算出来的一个数值（代价），用于说明 SQL 执行的代价。
Time ： Oracle 估计当前操作的时间。
3.2 谓词说明：
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access ("A"."EMPNO"="B"."MGR")
filter("A"."EMPNO"="B"."MGR")
5 - filter ("B"."MGR" IS NOT NULL)
Access: 表示这个谓词条件的值将会影响数据的访问路径（表还是索引）。
Filter ：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。
在谓词中主要注意 access ，要考虑谓词的条件，使用的访问路径是否正确。
3.3 统计信息说明：
db block gets ： 从 buffer cache 中读取的 block 的数量
consistent gets ： 从 buffer cache 中读取的 undo 数据的 block 的数量
physical reads ： 从磁盘读取的 block 的数量
redo size ： DML 生成的 redo 的大小
sorts (memory) ： 在内存执行的排序量
sorts (disk) ： 在磁盘上执行的排序量
Recursive Calls ： Number of recursive calls generated at both the user and system level.
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls 。
DB Block Gets ： Number of times a CURRENT block was requested.
Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them. (DB Block Gets: 请求的数据块在 buffer 能满足的个数 )
Consistent Gets ： Number of times a consistent read was requested for a block.
This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification. (Consistent Gets: 数据请求总数在回滚段 Buffer 中 )
Physical Reads ： Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads: 实例启动后，从磁盘读到 Buffer Cache 数据块数量 )
Sorts (disk) ： Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.
更多内容参考 Oracle 联机文档：
Statistics Descriptions
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/stats002.htm#i375475
3.4 动态分析
如果在执行计划中有如下提示：
Note
------------
-dynamic sampling used for the statement
这提示用户 CBO 当前使用的技术，需要用户在分析计划时考虑到这些因素。 当出现这个提示，说明当前表使用了动态采样。 我们从而推断这个表可能没有做过分析。
这里会出现两种情况：
（1） 如果表没有做过分析 ，那么 CBO 可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。
（2） 如果表分析过 ，但是分析信息过旧，这时 CBO 就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。
总结：
在看执行计划的时候，除了看执行计划本身，还需要看谓词和提示信息。 通过整体信息来判断 SQL 效率。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
