sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle 如何判断分页SQL语句是否最优化状态

时间:2016-10-31 21:59   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 分享一篇关于Oracle SQL优化的笔记,详细介绍如何判断分页SQL语句是否最优化状态,以及其实可以简单的看执行计划是否包含SORT ORDER BY STOPKEY这一列。
关于分页语句。下面在自己的平台简单的测试一把。判断分页语句是否最优化,其实可以简单的看执行计划是否包含SORT ORDER BY STOPKEY这一列,如果有这一列,意味着查询完所有的数据,在对数据进行排序的时,根据rownum的值来终止排序过程。

分页语句其实不仅仅要实现功能上的分页功能,也要使用利用rownum来终止对数据的访问

1,环境介绍

oracleplus.net> host uname -a
windows32 luoping 2.6.1 7601 i686-pc Intel unknown MinGW

oracleplus.net> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

2,创建测试数据

利用dba_objects表来创建测试表
oracleplus.net> create user htz identified by oracle;
User created.
oracleplus.net> grant dba to htz;
Grant succeeded.
oracleplus.net> create table htz.page1 as select * from dba_objects where rownum<20;
Table created.
oracleplus.net> create table htz.pages2 as select * from dba_objects;
Table created.
oracleplus.net> create index htz.ind_pages2_1 on htz.pages2(object_id,owner);
Index created.

3,配置参数

请注意是在会话级别修改参数为all,用于收集执行计划每一步实际还回的行数。也可以通过增加提示来实现。在优化OLTP环境SQL时候,如果不知道执行计划中那一步消耗大量的资源,也可以通过配置此参数,来收集详细的信息。
Oracleoracleplus.net
oracleplus.net> alter session set statistics_level=all;

Session altered.

4,分页语句执行

这里按a.object_id排序,取第5行到第10行之间的数据

4.1 只实现分页功能
select owner, object_id, object_type, object_name, rn
from (select owner, object_id, object_type, object_name, rownum rn
from (select /*+ use_nl(a b)*/
a.owner, a.object_id, a.object_type, b.object_name
from htz.page1 a, htz.pages2 b
where a.owner = b.owner
and b.object_id > 50000
order by a.object_id)
where rownum < 10)
where rn > 5

SQL> @plan_by_last.sql
SQL> set echo off
Enter value for sqlid:
old 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(‘&sqlid’,null),null,’ALLSTATS LAST’))
new 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(”,null),null,’ALLSTATS LAST’))
Plan hash value: 1735455317

————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.13 |
|* 1 | VIEW | | 1 | 9 | 4 |00:00:00.13 |
|* 2 | COUNT STOPKEY | | 1 | | 9 |00:00:00.13 |
| 3 | VIEW | | 1 | 208K| 9 |00:00:00.13 |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 208K| 9 |00:00:00.13 |
| 5 | NESTED LOOPS | | 1 | | 79933 |00:00:00.12 |
| 6 | NESTED LOOPS | | 1 | 208K| 79933 |00:00:00.05 |
| 7 | TABLE ACCESS FULL | PAGE1 | 1 | 19 | 19 |00:00:00.01 |
|* 8 | INDEX RANGE SCAN | IND_PAGES2_1 | 19 | 1051 | 79933 |00:00:00.04 |
| 9 | TABLE ACCESS BY INDEX ROWID| PAGES2 | 79933 | 10981 | 79933 |00:00:00.03 |
————————————————————————————————-

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

1 – filter("RN">5)
2 – filter(ROWNUM<10)
4 – filter(ROWNUM<10)
8 – access("B"."OBJECT_ID">50000 AND "A"."OWNER"="B"."OWNER" AND "B"."OBJECT_ID" IS NOT NULL)
filter("A"."OWNER"="B"."OWNER")
通过执行计划可以到,还回4条记录,但是访问了所有的数据。IND_PAGES2_1被执行了19次,等于PAGE1的总行数。PAGES2回表79933次。

4.2 利用rownum终止对数据的访问
利用rownum终止对数据的访问有几个前提条件:1,order by后面的列,只能是驱动表中的列。2,执行计划要走NL的方式,3,order by后面列所在的表为驱动表。4,SQL语句中不能包括minus,union,union all等待关键字。5,驱动表在where中的所有列需要都在索引中,select不要求。
创建索引
oracleplus.net> create index htz.ind_page1_2 on htz.page1(object_id,owner);

Index created.
oracleplus.net> select owner, object_id, object_type, object_name, rn
2 from (select owner, object_id, object_type, object_name, rownum rn
3 from (select /*+ use_nl(a b) index(a ind_page1_2)*/
4 a.owner, a.object_id, a.object_type, b.object_name
5 from htz.page1 a, htz.pages2 b
6 where a.owner = b.owner
7 and b.object_id > 50000
8 order by a.object_id)
9 where rownum < 10)
10 where rn > 5
11 ;
oracleplus.net> @plan_by_last.sql
oracleplus.net> set echo off
Enter value for sqlid:
old 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(‘&sqlid’,null),null,’ALLSTATS LAST’))
new 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(”,null),null,’ALLSTATS LAST’))

PLAN_TABLE_OUTPUT
————————————————————————————————————————–SQL_ID 1nvjmktk0rpqm, child number 0
————————————-
select owner, object_id, object_type, object_name, rn from (select
owner, object_id, object_type, object_name, rownum rn from
(select /*+ use_nl(a b) index(a ind_page1_2)*/
a.owner, a.object_id, a.object_type, b.object_name
from htz.page1 a, htz.pages2 b where a.owner = b.owner
and b.object_id > 50000 order by
a.object_id) where rownum < 10) where rn > 5

Plan hash value: 2290584084

———————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 7 |
|* 1 | VIEW | | 1 | 9 | 4 |00:00:00.01 | 7 |
|* 2 | COUNT STOPKEY | | 1 | | 9 |00:00:00.01 | 7 |
| 3 | VIEW | | 1 | 30014 | 9 |00:00:00.01 | 7 |
| 4 | NESTED LOOPS | | 1 | | 9 |00:00:00.01 | 7 |
| 5 | NESTED LOOPS | | 1 | 30014 | 9 |00:00:00.01 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| PAGE1 | 1 | 19 | 1 |00:00:00.01 | 2 |
| 7 | INDEX FULL SCAN | IND_PAGE1_2 | 1 | 19 | 1 |00:00:00.01 | 1 |
|* 8 | INDEX RANGE SCAN | IND_PAGES2_1 | 1 | 1 | 9 |00:00:00.01 | 3 |
| 9 | TABLE ACCESS BY INDEX ROWID | PAGES2 | 9 | 1580 | 9 |00:00:00.01 | 2 |
———————————————————————————————————–

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

1 – filter("RN">5)
2 – filter(ROWNUM<10)
8 – access("B"."OBJECT_ID">50000 AND "A"."OWNER"="B"."OWNER" AND "B"."OBJECT_ID" IS NOT NULL)
filter("A"."OWNER"="B"."OWNER")

Note
—–
– dynamic sampling used for this statement (level=2)


39 rows selected.
这里已经没有看到SORT ORDER BY STOPKEY这一行,并且被驱动表执行次数为rownum指定的还回的行数。说明通过rownum已经实现终止对更多数据的访问。
需要注意的表连接方法走的是NL,如果rownum的取值大于驱动表还回的总行数的1/2的时候,我们需要更改一下SQL,可以走索引的desc。如果rownum在1/2左右的时候,此方法的效率可能还没有走HASH的效率高。

本文固定链接: http://www.htz.pw/2014/09/21/%e5%88%86%e9%a1%b5%e8%af%ad%e5%8f%a5%e7%9a%84%e7%ae%80%e5%8d%95%e6%b5%8b%e8%af%95.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 如何判断分页SQL语句是否最优化状态

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1125.html

Oracle研究中心

关键词:

SQL分页语句的简单测试

优化分页SQL语句的笔记

分页语句包含SORT ORDER BY STOPKEY的优化笔记