08-优化学习-调优技巧

原创 ziyoo0830 2019-11-01

784

第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 改变传输表

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

学习笔记-ziyoo0830（共13篇）

个人学习

trwweiai

5年前

怎么没看到07

5年前

08-优化学习-调优技巧

第8章调优技巧

8.1、查看真实的基数（Rows）

8.2、使用union代替OR

8.3、分页语句优化思路

8.3.1、单表分页优化思想

错误的分页（会产生严重的性能问题）

正确的分页

8.3.2、多表关联分页优化思路

8.4、使用分析函数优化自连接

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

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

8.7、LIKE语句优化方法

8.8、DBLINK优化

文章被以下合辑收录

评论

相关阅读

08-优化学习-调优技巧

第8章 调优技巧

8.1、查看真实的基数（Rows）

8.2、使用union代替OR

8.3、分页语句优化思路

8.3.1、单表分页优化思想

错误的分页（会产生严重的性能问题）

正确的分页

8.3.2、多表关联分页优化思路

8.4、使用分析函数优化自连接

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

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

8.7、LIKE语句优化方法

8.8、DBLINK优化

文章被以下合辑收录

评论

相关阅读

第8章调优技巧