oralce的plan和优化

最新推荐文章于 2022-02-17 17:22:22 发布

旅行的青蛙~~

最新推荐文章于 2022-02-17 17:22:22 发布

阅读量124

点赞数

分类专栏： oracle

oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

如果要分析某条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 SQLNet to client
　　416 bytes received via SQLNet from client
　　2 SQLNet 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’));
　　或者：
　　SQL> select * from table(dbms_xplan.display);
　　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 SQLNet to client
　　416 bytes received via SQLNet from client
　　2 SQLNet 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.csdn.net/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.csdn.net/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 SQLNet to client
　　416 bytes received via SQLNet 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) ：在磁盘上执行的排序量
　　Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增加相关的索引，优化SQL语句来解决。
　　关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：
　　数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。
　　用以下语句可以查看数据缓冲区的命中率：
　　SQL>SELECT name, value FROM v$sysstat WHERE name IN (‘db block gets’, ‘consistent gets’,‘physical reads’);
　　查询出来的结果Buffer Cache的命中率应该在90％以上，否则需要增加数据缓冲区的大小。