Oracle性能优化：子查询中有主表过滤条件，子查询是否unnest
本实验测试的是子查询中包括主表的过滤条件时，子查询是否会unnest，通常情况下，unnest的效率会更高，但是如果遇到子查询只还回几行的时候，其实我们可以把子查询当成一个查询结果用于做驱动表，从10.2.0.5以后的版本，子查询中包括 主表的过滤条件时，也会自动unnset。下面测试的版本，包括 10.2.0.1/10.2.0.4/10.2.0.5，最后测试了如果不自动unnest，怎么优化这样的SQL
1，10.2.0.1版本
2，10.2.0.4版本
3，10.2.0.5版本
4，10.2.0.1版本的优化技巧
创建测试表
SQL> drop table scott.test;
Table dropped.
SQL> drop table scott.test1;
Table dropped.
SQL> create table scott.test as select * from dba_objects;
Table created.
SQL> create table scott.test1 as select * from dba_objects where rownum<1000;
Table created.1，10.2.0.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
SQL> set lines 200
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan
———————————————————-
Plan hash value: 353051353
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 96 | 85477 (1)| 00:17:06 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST | 44716 | 4192K| 158 (1)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE
:B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))
3 – filter(:B1=’SYS’)
4 – filter(“B”.”OBJECT_ID”=:B1)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
339145 consistent gets
0 physical reads
0 redo size
29653 bytes sent via SQL*Net to client
1152 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
913 rows processed这里发现，子查询根本没有unnest
下面通过提示要强制unnest
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select /*+ unnest */ 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan
———————————————————-
Plan hash value: 353051353
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 96 | 114K (1)| 00:22:49 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST | 58924 | 5524K| 157 (2)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 |
—————————————————————————–
仍然无效，说明根本不能进行unnest2,10.2.0.4版本
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
SQL> set lines 200
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan
———————————————————-
Plan hash value: 353051353
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 96 | 85477 (1)| 00:17:06 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST | 44716 | 4192K| 158 (1)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE
:B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))
3 – filter(:B1=’SYS’)
4 – filter(“B”.”OBJECT_ID”=:B1)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
339145 consistent gets
0 physical reads
0 redo size
29653 bytes sent via SQL*Net to client
1152 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
913 rows processed下面通过提示来强制
SQL> set autotrace traceonly;
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select /*+ unnest */ 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan
———————————————————-
Plan hash value: 353051353
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 96 | 85477 (1)| 00:17:06 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST | 44716 | 4192K| 158 (1)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter( EXISTS (SELECT /*+ UNNEST */ 0 FROM “SCOTT”.”TEST1″ “B”
WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))
3 – filter(:B1=’SYS’)
4 – filter(“B”.”OBJECT_ID”=:B1)
Note
—–
– dynamic sampling used for this statement
提示仍然无效3.10.2.0.5
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi
PL/SQL Release 10.2.0.5.0 – Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 – Production
NLSRTL Version 10.2.0.5.0 – Production
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2133522026
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 999 | 106K| 165 (2)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI| | 999 | 106K| 165 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL | TEST1 | 999 | 12987 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TEST | 26856 | 2517K| 159 (2)| 00:00:02 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
3 – filter(“A”.”OWNER”=’SYS’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
528 recursive calls
0 db block gets
923 consistent gets
712 physical reads
332 redo size
29656 bytes sent via SQL*Net to client
1152 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
913 rows processed这里发生了Unnest
下面是测试unnest与no_unnest的效率
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 2133522026
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 999 | 106K| 165 (2)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI| | 999 | 106K| 165 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL | TEST1 | 999 | 12987 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TEST | 26856 | 2517K| 159 (2)| 00:00:02 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
3 – filter(“A”.”OWNER”=’SYS’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
770 consistent gets
0 physical reads
0 redo size
29656 bytes sent via SQL*Net to client
1152 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
913 rows processed
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select /*+ no_unnest */ 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.94
Execution Plan
———————————————————-
Plan hash value: 353051353
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 96 | 107K (1)| 00:21:28 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST | 55572 | 5209K| 159 (2)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM “SCOTT”.”TEST1″
“B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))
3 – filter(:B1=’SYS’)
4 – filter(“B”.”OBJECT_ID”=:B1)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
15 recursive calls
0 db block gets
340705 consistent gets
0 physical reads
0 redo size
29656 bytes sent via SQL*Net to client
1152 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
913 rows processed4，10.2.0.1版本中怎么优化
SQL> set timing on;
SQL> select a.object_name, owner
2 from scott.test a
3 where exists (select 1
4 from scott.test1 b
5 where a.object_id = b.object_id
6 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.91
这里发现使用时间是00.91
Execution Plan
———————————————————-
Plan hash value: 353051353
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 96 | 114K (1)| 00:22:49 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TEST | 58924 | 5524K| 157 (2)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE
:B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))
3 – filter(:B1=’SYS’)
4 – filter(“B”.”OBJECT_ID”=:B1)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
338788 consistent gets
0 physical reads
0 redo size
29654 bytes sent via SQL*Net to client
1129 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
913 rows processed下面是优化后的效果
SQL> with b as
2 (select /*+ materialize */
3 object_id
4 from scott.test1)
5 select a.object_name, a.owner
6 from scott.test a
7 where exists (select 1
8 from b
9 where a.object_id = b.object_id
10 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.82这里的效果，时间减少不是很多，因为子查询中的表本来就不大，
Execution Plan
———————————————————-
Plan hash value: 1200970861
——————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 96 | 114K (1)| 00:22:59 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | TEST | | | | |
| 3 | TABLE ACCESS FULL | TEST1 | 999 | 12987 | 5 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL | TEST | 58924 | 5524K| 157 (2)| 00:00:02 |
|* 6 | FILTER | | | | | |
|* 7 | VIEW | | 999 | 12987 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6604_876E8 | 999 | 12987 | 2 (0)| 00:00:01 |
——————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
4 – filter( EXISTS (SELECT /*+ */ 0 FROM (SELECT /*+ CACHE_TEMP_TABLE (“T1”) */ “C0”
“OBJECT_ID” FROM “SYS”.”SYS_TEMP_0FD9D6604_876E8″ “T1”) “B” WHERE :B1=’SYS’ AND
“B”.”OBJECT_ID”=:B2))
6 – filter(:B1=’SYS’)
7 – filter(“B”.”OBJECT_ID”=:B1)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
2 recursive calls
9 db block gets
91726 consistent gets 这里发现逻辑读减少很多的。
2 physical reads
604 redo size
29654 bytes sent via SQL*Net to client
1129 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
913 rows processed------------------作者介绍-----------------------
姓名：黄廷忠
