不得不知的调优技巧

1.查看真实的基数（Rows）

执行计划中的Rows是假的，是CBO根据统计信息和数学公式估算出来的，在看执行计划的时候，一定要注意嵌套循环驱动表的Rows是否估算准确，同时也要注意执行计划的入口Rows是否算错。

2.使用UNION 代替OR

将OR改写为union之后，可以消除Filter，若无法进行更改，SQL只能走FIlter的情况下，需在连接列建立索引。

3.分页语句优化思路

如果分页语句中有排序（order by）,要利用索引已经排序特性，将order by 的列包含在索引中，同时也要利用rownum的COUNT STOPKEY特性来优化分页SQL。如果分页中没有排序，可以直接利用rownum的COUNT STOPKEY特性来优化分页SQL.

4.使用分析函数优化自连接

scott@orclpdb1:orclcdb> set autot on
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> select ename, deptno, sal
  2    from emp a
  3   where sal = (select max(sal) from emp b where a.deptno = b.deptno);

ENAME          DEPTNO        SAL
---------- ---------- ----------
BLAKE              30       2850
SCOTT              20       3000
KING               10       5000
FORD               20       3000

4 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1245077725

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     3 |   117 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     3 |   117 |     7  (15)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SAL"="MAX(SAL)" AND "A"."DEPTNO"="ITEM_1")


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        801  bytes sent via SQL*Net to client
        468  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

scott@orclpdb1:orclcdb> 

select ename, deptno, sal
  from emp a
 where sal = (select max(sal) from emp b where a.deptno = b.deptno);

我们可以利用分析函数对上面SQL进行等价改写，使EMP只访问一次。

分析函数的写法如下：

select ename, deptno, sal
  from (select a.*, max(sal) over(partition by deptno) max_sal from emp a)
 where sal = max_sal;

scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> select ename, deptno, sal
  2    from (select a.*, max(sal) over(partition by deptno) max_sal from emp a)
  3   where sal = max_sal;

ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
FORD               20       3000
SCOTT              20       3000
BLAKE              30       2850

4 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4130734685

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   644 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |   644 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   182 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL"="MAX_SAL")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        795  bytes sent via SQL*Net to client
        704  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

scott@orclpdb1:orclcdb> 

使用 分析函数改写之后，减少了表扫描次数，EMP表越大，性能提升越明显。

超大表与超小表关联优化方法

走hash join

use_hash(a,b)

a 有 20 MB  b 有 30GB

超大表与超大表关联优化方法

走hash join

/* parallel(6) use_hash(a,b) pq_distribute(b hash,hash) */

SQL三段分拆法

select 与 from 之间最好不要有标量子查询，也不要有自定义函数。因为有标量子查询或者是自定义函数，会导致子查询或者函数中的表被反复扫描。

from 与where 之间要关注大表，大表会产生性能问题，同时要注意子查询和视图。如果有子查询或视图，要单独运行。看运行的快或慢。如果运行慢需要单独优化，另外要注意子查询/视图是否可以谓词推入，是否会视图合并，最后还要留意表与表之间的内连接还是外连接。因为外连接会导致嵌套循环无法驱动表。

where 后面需要特别注意子查询，要能判断各种子查询写法是否可以展开（unnest）,同时也要注意where过滤条件，尽量不要在where过滤列上使用函数，这样会导致列不走索引。