【学习笔记】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过滤时子查询执行的次数
973
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
