一、导读
一个好的运维团队，应该首先是一批好的开发工程师，当软件系统变得越来越复杂时 ，主动承担起架构调整，系统调优，自动化等工作来帮助降低系统复杂度，优化系统 架构，提高系统可用性，下图为Oracle优化的进化论。
二、优化常识
2.1.显示执行计划方法
SYS@ORCL> SET AUTOTRACE TRACEONLY：只显示统计结果，不显示返回的结果集合 • 1
2.2.SQL执行计划
执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述。 即就是对一个查询任务，做出一份怎样去完成任务的详细方案。 如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划， 看看SQL的每一步执行是否存在问题。 看懂执行计划也就成了SQL优化的先决条件。 通过执行计划定位性能问题，定位后就通过建立索引、修改sql等解决问题。 2.2.1 执行顺序的原则 执行顺序的原则是：由上至下，从右向左 由上至下：在执行计划中一般含有多个节点， 相同级别(或并列)的节点，靠上的优先执行，靠下的后执行 从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。 2.2.2 执行计划中字段解释 ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。 Operation： 当前操作的内容。 Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。 Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。 Time：Oracle 估计当前操作的时间。 在看执行计划的时候，除了看执行计划本身， 还需要看谓词和统计信息。 通过整体信息来判断SQL效率。 2.2.3 谓词说明 Access : * 通过某种方式定位了需要的数据，然后读取出这些结果集，叫做Access。 * 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。 Filter： * 把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。 * 表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。 在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。
2.3 Statistics统计信息
CBO包含以下组件：查询转换器、评估器、计划生成器 recursive calls：产生的递归sql调用的条数。 Db block gets:从buffer cache中读取的block的数量 consistent gets：从buffer cache中读取的undo数据的block的数量 physical reads：从磁盘读取的block的数量 redo size：DML生成的redo的大小 bytes sent via SQL*Net to client： 数据库服务器通过SQL*Net向查询客户端发送的查询结果字节数 bytes received via SQL*Net from client： 通过SQL*Net接受的来自客户端的数据字节数 SQL*Net roundtrips to/from client： 服务器和客户端来回往返通信的Oracle Net messages条数 sorts (memory)：在内存执行的排序量 sorts (disk)：在磁盘上执行的排序量 rows processed：处理的数据的行数
2.4 表连接方式
在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式 多表之间的连接有三种方式： Nested Loops，Hash Join 和 Sort Merge Join. 具体适用哪种类型的连接取决于： * 当前的优化器模式 （ALL_ROWS 和 RULE） * 取决于表大小 * 取决于连接列是否有索引 * 取决于连接列是否排序 2.4.1 hash join 使用情况：Hash join在两个表的数据量差别很大的时候.（小表一般会丢在内存中） 2.4.2 merge join Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据， 到另一个排序表中做匹配。 适用情况： 1.RBO模式 2.不等价关联(>,<,>=,<=,<>) 3.HASH_JOIN_ENABLED=false 4. 用在没有索引，并且数据已经排序的情况. 2.4.3 nested loop Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)， 然后访问另一张表（被查找表 inner table,通常有索引）。 驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。 适用情况： 适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index）， 并且索引选择性较好的时候. JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。
2.4.执行计划的获取
2.4.1 dbms_xplan.display_cursor获取 select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到，如果SQL已被age out出share pool，则查找不到 select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取(直接在命令窗口执行，可对比执行计划，真实的计划) 2.4.2 Explain Plan（预估执行计划） SYS@ORCL> explain plan for select * from scott.emp; SYS@ORCL> select * from table(dbms_xplan.display); 注：Explain plan只生成执行计划，并不会真正执行SQL语句， 因此产生的执行计划有可能不准
三、优化案例展示
3.1 未走索引
创建测试表： SQL> create table system.test_objects as select * from dba_objects; SYS@ORCL> insert into system.test_objects select * from system.test_objects; --数据量达到1200多万即可 收集统计信息： SQL> EXEC DBMS_STATS.gather_table_stats('SYSTEM','TEST_OBJECTS'); SQL> set autotrace on SQL> set linesize 200 SYS@ORCL> set time on timing on SQL> select count(*) from system.test_objects A where object_name='OBJ$'; COUNT(*) ---------- 256 Elapsed: 00:00:03.08 Execution Plan ---------------------------------------------------------- Plan hash value: 3799704240 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 19506 (2)| 00:03:55 | | 1 | SORT AGGREGATE | | 1 | 25 | | | |* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 226 | 5650 | 19506 (2)| 00:03:55 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='OBJ$') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 176155 consistent gets 149912 physical reads 0 redo size 516 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SYS@ORCL> set autotrace off SYS@ORCL> select count(*) from system.test_objects;
3.2 如何优化
如前所示，原SQL是要统计表system.test_objects中，object_name列中， 值为'OBJ$'的记录的行数。而表system.test_objects中共有记录600余万行。 该SQL共执行了3秒，进行了约17万个逻辑读（从内存中读取数据） 和几乎同样数量的物理读（从外部存储上读取数据，其处理速度比内存的处理速度慢千倍以上）。 但实际上，只有256行记录是满足检索条件的，其相对于600余万行的总记录数而言，是非常少的。 同时，也说明OBJECT_NAME列上选择性是比较好的。 综上，我们可以考虑在列OBJECT_NAME列上创建索引,这样，当进行同样的SQL时: 第一，我们可以通过该索引快速定位到满足条件的行， 从而避免了从头扫到尾的全表扫描的处理方式。 第二，由于是统计满足相关条件的记录数量， 所以，并不需要返回除检索条件列之外的其它列，只是计数罢了， 所以，仅仅访问相关的索引就足够了，而不需要再从索引回表，去获取其它列。 ##优化效果 在列object_name创建索引后，执行时间为，由之前的03.08表降为0.01秒， 辑读和物理读分别为5和4 创建索引 SQL> create index ind_test_objects_object_name on system.TEST_OBJECTS(Object_Name); SQL> select count(*) from system.test_objects A where object_name='OBJ$'; COUNT(*) ---------- 256 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 251147837 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 6 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 25 | | | |* 2 | INDEX RANGE SCAN| IND_TEST_OBJECTS_OBJECT_NAME | 226 | 5650 | 6 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME"='OBJ$') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 3 physical reads 0 redo size 516 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
