sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【学习笔记】Oracle性能优化 sql走filter过滤时子查询执行的次数

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

天萃荷净 分享一篇关于Oracle SQL语句性能优化的子查询执行次数的案例,filter 的性能实际上跟列值distinct数有关,oracle在执行的时候实际上做了很大优化,最坏情况下才会出现对外表每一行执行一次filter操作。


关于sql走filter过滤的时候,子查询执行的次数。因为我们知道子查询执行的次数,决定了我们SQL的性能

1,数据库的版本

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production

2,创建测试表与索引

SQL> create table scott.htz1 as select * from dba_objects;

Table created.

SQL> create table scott.htz2 as select * from dba_objects;

Table created.

SQL> create table scott.htz3 as select * from dba_objects;


Table created.

SQL>
SQL> create index scott.ind_htz3_object_owner on scott.htz3(object_id,owner);

Index created.

3,驱动表无重复值

SQL> select *
2 from scott.htz1 a, scott.htz2 b
3 where a.object_id = b.object_id
4 and a.object_id in (select object_id
5 from scott.htz3 c
6 where c.owner = ‘SCOTT’
7 and a.owner = ‘SYS’)
8 ;

no rows selected

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_TABLE_OUTPUT
—————————————————————————————-
select * from scott.htz1 a, scott.htz2 b where a.object_id = b.object_id and a.object_id in (select object_id
from scott.htz3 c where c.owner = ‘SCOTT’ and
a.owner = ‘SYS’)

Plan hash value: 286543034

———————————————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
———————————————————————————————————————————-
|* 1 | FILTER | | 1 | | 0 |00:00:00.96 | 47310 | | | |
|* 2 | HASH JOIN | | 1 | 38536 | 50068 |00:00:00.60 | 1386 | 7548K| 2031K| 8473K (0)|
| 3 | TABLE ACCESS FULL| HTZ1 | 1 | 38535 | 50068 |00:00:00.05 | 693 | | | |
| 4 | TABLE ACCESS FULL| HTZ2 | 1 | 50965 | 50069 |00:00:00.10 | 693 | | | |
|* 5 | FILTER | | 50068 | | 0 |00:00:00.23 | 45924 | | | |
|* 6 | INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER | 22962 | 1 | 0 |00:00:00.10 | 45924 | | | |
———————————————————————————————————————————-

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

1 – filter( IS NOT NULL)
2 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
5 – filter(:B1=’SYS’)
6 – access(“OBJECT_ID”=:B1Oracle oracleplus.net AND “C”.”OWNER”=’SCOTT’)

Note
—–
– dynamic sampling used for this statement

这里filter执行了50068次,而INDEX只执行了22962次,这里是因为有主表查询,把主表的过滤列推过来了
31 rows selected.

SQL>
SQL> select count(*)
2 from scott.htz1 a, scott.htz2 b
3 where a.object_id = b.object_id;

COUNT(*)
———-
50068

4,有重复的值

SQL> insert into scott.htz1 select * from scott.htz1;

50068 rows created.

SQL> commit;

Commit complete.


SQL> select count(*)
2 from scott.htz1 a, scott.htz2 b
3 where a.object_id = b.object_id;

COUNT(*)
———-
100136

这里看到两个表连接后还回了100316行记录
SQL> select count(distinct a.object_id) count_distinct
2 from scott.htz1 a, scott.htz2 b
3 where a.object_id = b.object_id;

COUNT_DISTINCT
————–
50068
这里看到表还回的不同值为50068个

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_TABLE_OUTPUT
————————————————–
SQL_ID 1gktx239rcv6f, child number 0
————————————-
select * from scott.htz1 a, scott.htz2 b where a.object_id = b.object_id and a.object_id in (select object_id
from scott.htz3 c where c.owner = ‘SCOTT’ and a.owner = ‘SYS’)

Plan hash value: 286543034

——————————————————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
——————————————————————————————————————————————-
|* 1 | FILTER | | 1 | | 0 |00:00:01.29 | 48000 | 68 | | | |
|* 2 | HASH JOIN | | 1 | 38536 | 100K|00:00:00.81 | 2076 | 68 | 13M| 2031K| 17M (0)|
| 3 | TABLE ACCESS FULL| HTZ1 | 1 | 38535 | 100K|00:00:00.10 | 1383 | 0 | | | |
| 4 | TABLE ACCESS FULL| HTZ2 | 1 | 50965 | 50069 |00:00:00.10 | 693 | 68 | | | |
|* 5 | FILTER | | 50068 | | 0 |00:00:00.24 | 45924 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER | 22962 | 1 | 0 |00:00:00.10 | 45924 | 0 | | | |
——————————————————————————————————————————————-

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

1 – filter( IS NOT NULL)
2 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
5 – filter(:B1=’SYS’)
6 – access(“OBJECT_ID”=:B1 AND “C”.”OWNER”=’SCOTT’)

Note
—–
– dynamic sampling used for this statement

通过执行计划,我们可以看到FILTER这里仍然50068,说明是DISCOUNT的值的总行数,而不是主表还回的行数
30 rows selected.

5,连接列包括多个空值的情况

SQL> update scott.htz1 set object_id=” where rownum<10000;

9999 rows updated.

SQL> commit;

Commit complete.


SQL> update scott.htz2 set object_id=” where rownum<10000;

9999 rows updated.

SQL> commit;

Commit complete.



SQL> select count(distinct a.object_id) count_distinct
2 from scott.htz1 a, scott.htz2 b
3 where a.object_id = b.object_id;

COUNT_DISTINCT
————–
40069

连接列还回的DISCOUNT的值

SQL> select *
2 from scott.htz1 a, scott.htz2 b
3 where a.object_id = b.object_id
4 and a.object_id in (select object_id
5 from scott.htz3 c
6 where c.owner = ‘SCOTT’
7 and a.owner = ‘SYS’)
8 ;

no rows selected

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_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID 1gktx239rcv6f, child number 0
————————————-
select * from scott.htz1 a, scott.htz2 b where a.object_id = b.object_id and a.object_id in (select object_id
from scott.htz3 c where c.owner = ‘SCOTT’ and a.owner = ‘SYS’)

Plan hash value: 286543034

——————————————————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
——————————————————————————————————————————————-
|* 1 | FILTER | | 1 | | 0 |00:00:01.15 | 34646 | 19 | | | |
|* 2 | HASH JOIN | | 1 | 38536 | 80138 |00:00:00.86 | 2076 | 19 | 12M| 2029K| 15M (0)|
| 3 | TABLE ACCESS FULL| HTZ1 | 1 | 38535 | 100K|00:00:00.10 | 1383 | 19 | | | |
| 4 | TABLE ACCESS FULL| HTZ2 | 1 | 50965 | 50069 |00:00:00.09 | 693 | 0 | | | |
|* 5 | FILTER | | 40069 | | 0 |00:00:00.18 | 32570 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER | 16285 | 1 | 0 |00:00:00.07 | 32570 | 0 | | | |
——————————————————————————————————————————————-

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

1 – filter( IS NOT NULL)
2 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
5 – filter(:B1=’SYS’)
6 – access(“OBJECT_ID”=:B1 AND “C”.”OWNER”=’SCOTT’)

Note
—–
– dynamic sampling used for this statement


30 rows selected.

6,总结

通过上面的测试,我们可以看到FILTER过滤的时候,子查询执行的次数为count(DISCOUNT 主键连接列)


本文固定链接: http://www.htz.pw/2014/04/28/filter%e4%b8%ad%ef%bc%8c%e5%ad%90%e6%9f%a5%e8%af%a2%e6%89%a7%e8%a1%8c%e7%9a%84%e6%ac%a1%e6%95%b0.html | 认真就输

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

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle性能优化 sql走filter过滤时子查询执行的次数

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

Oracle研究中心

关键词:

Oracle性能优化之filter子查询执行次数优化

Oracle SQL执行计划中的filter