第8章 调优技巧
8.1、查看真实的基数（Rows）
8.2、使用union代替OR
8.3、分页语句优化思路
8.3.1、单表分页优化思想
错误的分页（会产生严重的性能问题）
TEST01@testora>create table t_page as select * from dba_objects ;
Table created.
select *
from (select t.*, rownum rn
from (select * from t_page order by object_id) t)
where rn >= 1
5 and rn <= 10;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3603170480
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91112 | 19M| | 4444 (1)| 00:00:54 |
|* 1 | VIEW | | 91112 | 19M| | 4444 (1)| 00:00:54 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 91112 | 17M| | 4444 (1)| 00:00:54 |
| 4 | SORT ORDER BY | | 91112 | 17M| 21M| 4444 (1)| 00:00:54 |
| 5 | TABLE ACCESS FULL| T_PAGE | 91112 | 17M| | 344 (1)| 00:00:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=10 AND "RN">=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
1378 consistent gets
0 physical reads
0 redo size
2075 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
从执行计划中我们可以看到该SQL走了全表扫描，假如t_page有上亿条记录，会产生严重的性能问题。所以该SQL不能走全表扫描，必须走索引扫描。
TEST01@testora>create index idx_page on t_page(object_id ,0);
TEST01@testora>alter session set statistics_level = all ;
Session altered.
select *
from (select t.*, rownum rn
from (select /*+index(t_page idx_page)*/
*
from t_page
order by object_id) t)
where rn >= 1
8 and rn <= 10;
---省略输出---
TEST01@testora>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 6hy652qjmgujd, child number 1
-------------------------------------
select * from (select t.*, rownum rn from (select
/*+index(t_page idx_page)*/ * from
t_page order by object_id) t) where rn >= 1 and rn
<= 10
Plan hash value: 3119682446
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.13 | 1545 |
|* 1 | VIEW | | 1 | 91112 | 10 |00:00:00.13 | 1545 |
| 2 | COUNT | | 1 | | 86313 |00:00:00.11 | 1545 |
| 3 | VIEW | | 1 | 91112 | 86313 |00:00:00.09 | 1545 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_PAGE|1|91112|86313 |00:00:00.07 | 1545 |
| 5 | INDEX FULL SCAN | IDX_PAGE | 1 | 91112 | 86313 |00:00:00.02 | 217 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
1 - filter(("RN"<=10 AND "RN">=1))
Note
-----
- dynamic sampling used for this statement (level=2)
29 rows selected.
正确的分页
分页框架：
select *
from (select *
from (select a.*, rownum rn
from (需要分页的SQL) a)
where rownum <= 10)
where rn >= 1;
TEST01@testora>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 95puvbgwqw5mh, child number 0
-------------------------------------
select * from (select * from (select a.*, rownum rn
from (select /*+index(t_page idx_page)*/
* from t_page
order by object_id) a) where rownum <= 10) where rn >= 1
Plan hash value: 1201925926
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 | 91112 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 | 91112 | 10 |00:00:00.01 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_PAGE | 1 | 91112 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_PAGE | 1 | 91112 | 10 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.
分页语句优化思路：
要利用索引已经排序特性，将order by的列包含在索引中，同时也要利用rownum的COUNT STOPKEY特性来优化分页SQL。如果分页中没有排序，可以直接利用rownum的COUNT STOPKEY特性来优化分页SQL。
现在我们继续完善分页语句的优化思路：如果分页语句中有排序（order by），要利用索引已经排序特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。如果分页语句中有过滤条件，我们要注意过滤条件是否有等值过滤条件，如果有等值过滤条件，要将等值过滤条件优先组合在一起，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。如果分页语句中没有等值过滤条件，我们应该先将排序列放在索引前面，将非等值过滤列放后面，最后利用rownum的COUNT STOPKEY特性来优化分页SQL。如果分页中没有排序，可以直接利用rownum的COUNT STOPKEY特性来优化分页SQL。
select *
from (select *
from (select a.*, rownum rn
from (select /*+index(t_page idx_page)*/
*
from t_page
where owner = 'SYS'
order by object_id) a)
where rownum <= 10)
where rn >= 1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
由于
select *
from (select *
from (select a.*, rownum rn
from (select /*+index(t_page idx_page)*/
*
from t_page
where owner = 'SCOTT'
order by object_id) a)
where rownum <= 10)
where rn >= 1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
这是因为第一条SQL过滤条件是owner='SCOTT'，owner='SCOTT'在表中只有很少数据，通过扫描object_id列的索引，然后回表再去匹配owner='SCOTT'，因为owner='SCOTT'数据量少，要搜索大量数据才能匹配上。而第二条SQL的过滤条件是owner='SYS'，因为owner='SYS'数据量多，只需要搜索少量数据就能匹配上。
想要优化第一条SQL，就需要让其在索引扫描的时候读取少量数据块就取得10行数据，这就需要将过滤列（owner）包含在索引中，排序列是object_id，那么现在我们创建组合索引。
TEST01@testora>create index idx_page_ownerid on t_page(owner,object_id) ;
Index created.
begin
dbms_stats.gather_table_stats(ownname => 'TEST01',
tabname => 'T_PAGE',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => 1,
cascade => true);
end;
select *
from (select *
from (select a.*, rownum rn
from (select /*+index(t_page idx_page_ownerid)*/
*
from t_page
where owner = 'SYS'
order by object_id) a)
where rownum <= 10)
where rn >= 1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
由于
select *
from (select *
from (select a.*, rownum rn
from (select /*+index(t_page idx_page_ownerid)*/
*
from t_page
where owner = 'SCOTT'
order by object_id) a)
where rownum <= 10)
where rn >= 1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
8.3.2、多表关联分页优化思路
TEST01@testora>create table t_page2 as select * from dba_objects ;
Table created.
TEST01@testora>alter session set statistics_level = all ;
select *
from (select *
from (select a.owner,
a.object_id,
a.subobject_name,
a.object_name,
rownum rn
from (select t1.owner,
t1.object_id,
t1.subobject_name,
t2.object_name
from t_page t1, t_page2 t2
where t1.object_id = t2.object_id
order by t2.object_name) a)
where rownum <= 10)
where rn >= 1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
创建索引后可得到优化后的执行计划：
TEST01@testora>create index idx_page2_name on t_page2(object_name,0) ;
Index created.
select *
from (select *
from (select a.owner,
a.object_id,
a.subobject_name,
a.object_name,
rownum rn
from (select /*+ index(t2 idx_page2_name) leading(t2) use_nl(t2,t1) */ t1.owner,
t1.object_id,
t1.subobject_name,
t2.object_name
from t_page t1, t_page2 t2
where t1.object_id = t2.object_id
order by t2.object_name) a)
where rownum <= 10)
where rn >= 1;
以上排序列 t2.object_name 来自一个列，可以消除 SORT ORDER BY ;如果排序列来自两个表，则无法消除 SORT ORDER BY ，如：
select *
from (select *
from (select a.owner,
a.object_id,
a.subobject_name,
a.object_name,
rownum rn
from (select t1.owner,
t1.object_id,
t1.subobject_name,
t2.object_name
from t_page t1, t_page2 t2
where t1.object_id = t2.object_id
order by t2.object_name, t1.subobject_name) a)
where rownum <= 10)
where rn >= 1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
总结一下多表关联分页优化思路,多表关联分页语句，如果有排序，只能对其中一个表进行排序，让参与排序的表作为嵌套循环的驱动表，并且要控制驱动表返回的数据顺序与排序的顺序一致，其余表的连接列要创建好索引。如果有外连接，我们只能选择主表的列作为排序列，语句中不能有distinct、group by、max、min、avg、union、union all，执行计划中不能出现SORT ORDER BY。
8.4、使用分析函数优化自连接
ZIYOO0830@o11201g>set autotrace traceonly
select ename, deptno, sal
from scott.emp a
3 where sal = (select max(sal) from scott.emp b where a.deptno = b.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 2649664444
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 462 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 26 | | |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 26 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"= (SELECT MAX("SAL") FROM "SCOTT"."EMP" "B" WHERE
"B"."DEPTNO"=:B1))
4 - filter("B"."DEPTNO"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
以上SQL为查询每个部门工资最高的员工的所有信息，访问了EMP表两次。
使用分析函数优化SQL：
ZIYOO0830@o11201g>
select ename, deptno, sal
from (select a.*, max(sal) over(partition by deptno) max_sal
from scott.emp a)
4 where sal = max_sal;
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 | 462 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"="MAX_SAL")
Note
-----
- dynamic sampling used for this statement (level=2)
8.5、超大表与超小表关联优化方法
ZIYOO0830@o11201g>create table b as select * from dba_objects ;
ZIYOO0830@o11201g>create table b as select * from dba_objects ;
ZIYOO0830@o11201g>insert into b select * from b ;
ZIYOO0830@o11201g>insert into b select * from b ;
ZIYOO0830@o11201g>insert into b select * from b ;
ZIYOO0830@o11201g>insert into b select * from b ;
ZIYOO0830@o11201g>insert into b select * from b ;
72469 rows created.
select * from a,b where a.object_id = b.object_id ;
表a有30MB，表b有30GB，量表关联猴返回大量数据，应该走hash连接，a表为驱动表，b为被驱动表；
开启并行执行（小表作为广播表）：
explain plan for select /*+ parallel(6) use_hash(a,b) pq_distribute(a noe,broadcast) */ *
from a,b where a.object_Id = b.object_id ;
以上均可以使用set autotrace traceonly查看相应的执行计划；
8.6、超大表与超大表关联优化方法
ZIYOO0830@o11201g>create table c as select * from b;
ZIYOO0830@o11201g>create table c as select * from b;
ZIYOO0830@o11201g>insert into c select * from c ;
8.7、LIKE语句优化方法
8.8、DBLINK优化
hint：driving_site 改变传输表
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
