性能调优：JPPD（连接谓词下推）客户案例模拟
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！由于博客中有大量代码，通过页面浏览效果更佳。
前面几天发了几篇JPPD文章，今天就来演示一下客户现场遇到真实案例的模拟，这个在客户现场优化SQL时遇到连接谓词推入不成功的案例，由于但是客户正式环境不允许操作，有没有测试环境，没有办法测试，下面是在自己的虚拟机里面演示一下，优化后能带来的性能提升。1，环境介绍
本次实验是基于11.2.0.4环境，其它环境在默认情况下可能结果会不一致。
www.htz.pw > select * from v$version where rownum<4;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
www.htz.pw > !lsb_release -a
Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)2，参数配置
下面参数都为默认情况下的配置
SELECT a.ksppinm AS parameter,">2 b.ksppstvl AS session_value,
3 c.ksppstvl AS instance_value,
4 a.ksppdesc AS description
5FROM x$ksppi a,
6 x$ksppcv b,
7 x$ksppsv c
8WHEREa.indx = b.indx
9AND a.indx = c.indx
10AND a.ksppinm LIKE '/_%' ESCAPE '/'
11AND (a.ksppinm in ('_push_join_predicate','_optimizer_push_pred_cost_based','_optimizer_extend_jppd_view_types'))
12ORDER BY a.ksppinm
13/
PARAMETER S_VALUE D_VALUEDESCRIPTION
----------------------------------- ------- -------- ----------------------------------------------------------------
_optimizer_extend_jppd_view_types TRUE TRUE join pred pushdown on group-by, distinct, semi-/anti-joined view
_optimizer_push_pred_cost_based TRUE TRUE use cost-based query transformation for push pred optimization
_push_join_predicate TRUE TRUE enable pushing join predicate inside a view3，创建测试表
这里都是基于dba_objects视图创建的。
www.htz.pw > create table scott.htz1 as select * from dba_objects;
www.htz.pw > create table scott.htz2 as select * from dba_objects;
www.htz.pw > create table scott.htz3 as select * from dba_objects;4，语句一测试
下面这个SQL还回是0行，其实出view，SQL本来也就是还回0行，这个还回的行数直接影响到我们的测试结果。
FROM scott.htz1 a,
scott.htz2 b,
(SELECT COUNT (*) COUNT, owner
FROM scott.htz3 c
WHERE c.object_id > 1000
GROUP BY owner) d
WHERE a.owner = b.owner
AND a.object_id > b.object_id
AND b.CREATED > SYSDATE - 1
AND a.owner = d.owner;4.1 默认情况
FROM scott.htz1 a,
scott.htz2 b,
(SELECT COUNT (*) COUNT, owner
FROM scott.htz3 c
WHERE c.object_id > 1000
GROUP BY owner) d
WHERE a.owner = b.owner
AND a.object_id > b.object_id
AND b.CREATED > SYSDATE - 1
AND a.owner = d.owner;no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 2675592091-------------------------------------------------------------------------------------| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 42M| 17G| |1447 (9)| 00:00:18 ||*1 |HASH JOIN | | 42M| 17G|2408K|1447 (9)| 00:00:18 ||*2 | HASH JOIN | |5771 |2333K| | 692 (1)| 00:00:09 ||*3 | TABLE ACCESS FULL | HTZ2 | 14 |2898 | | 346 (1)| 00:00:05 || 4 | TABLE ACCESS FULL | HTZ1 | 99677 | 19M| | 345 (1)| 00:00:05 || 5 | VIEW | | 88109 |2581K| | 348 (2)| 00:00:05 || 6 | HASH GROUP BY | | 88109 |2581K| | 348 (2)| 00:00:05 ||*7 | TABLE ACCESS FULL| HTZ3 | 88109 |2581K| | 345 (1)| 00:00:05 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("A"."OWNER"="D"."OWNER") 2 - access("A"."OWNER"="B"."OWNER") filter("A"."OBJECT_ID">"B"."OBJECT_ID") 3 - filter("B"."CREATED">SYSDATE@!-1) 7 - filter("C"."OBJECT_ID">1000)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 0recursive calls 0db block gets 2480consistent gets 0physical reads 0redo size 2554bytes sent via SQL*Net to client 512bytes received via SQL*Net from client 1SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 0rows processed这里看到整个SQL逻辑读是2480，也消耗了TEMPSPC为2408K.
4.2 强制连接列谓词推入
SELECT /*+ push_pred(d)*/">2 *
3 FROM scott.htz1 a,
4 scott.htz2 b,
5 (SELECT COUNT (*) COUNT, owner
6 FROM scott.htz3 c
7 WHERE c.object_id > 1000
8 GROUP BY owner) d
9 WHERE a.owner = b.owner
10 AND a.object_id > b.object_id
11 AND b.CREATED > SYSDATE - 1
12 AND a.owner = d.owner;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1009835727
--------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 42M| 16G|1990K(1)| 06:38:11 |
| 1 |NESTED LOOPS | | 42M| 16G|1990K(1)| 06:38:11 |
|*2 | HASH JOIN | |5771 |2333K| 692 (1)| 00:00:09 |
|*3 | TABLE ACCESS FULL | HTZ2 | 14 |2898 | 346 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL | HTZ1 | 99677 | 19M| 345 (1)| 00:00:05 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 13 | 345 (1)| 00:00:05 |
|*6 | FILTER | | | | | |
| 7 | SORT AGGREGATE | | 1 | 30 | | |
|*8 | TABLE ACCESS FULL| HTZ3 | 881 | 26430 | 345 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="B"."OWNER")
filter("A"."OBJECT_ID">"B"."OBJECT_ID")
3 - filter("B"."CREATED">SYSDATE@!-1)
6 - filter(COUNT(*)>0)
8 - filter("OWNER"="A"."OWNER" AND "C"."OBJECT_ID">1000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
2480consistent gets
0physical reads
0redo size
2554bytes sent via SQL*Net to client
512bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed这里消耗的逻辑读也是2480，但是tempspc为0.但是这里ORACLE自己估算出来谓词推入成本更高。
4.3 连接列创建索引
SELECT /*+ push_pred(d)*/">2 *
3 FROM scott.htz1 a,
4 scott.htz2 b,
5 (SELECT COUNT (*) COUNT, owner
6 FROM scott.htz3 c
7 WHERE c.object_id > 1000
8 GROUP BY owner) d
9 WHERE a.owner = b.owner
10 AND a.object_id > b.object_id
11 AND b.CREATED > SYSDATE - 1
12 AND a.owner = d.owner;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3435586372
----------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 42M| 16G| 104K(1)| 00:20:56 |
| 1 |NESTED LOOPS | | 42M| 16G| 104K(1)| 00:20:56 |
|*2 | HASH JOIN | |5771 |2333K| 692 (1)| 00:00:09 |
|*3 | TABLE ACCESS FULL | HTZ2 | 14 |2898 | 346 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL | HTZ1 | 99677 | 19M| 345 (1)| 00:00:05 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 13 | 18 (0)| 00:00:01 |
|*6 | FILTER | | | | | |
| 7 | SORT AGGREGATE | | 1 | 30 | | |
|*8 | TABLE ACCESS BY INDEX ROWID| HTZ3 | 881 | 26430 | 18 (0)| 00:00:01 |
|*9 | INDEX RANGE SCAN | IND_HTZ3_1 | 352 | | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="B"."OWNER")
filter("A"."OBJECT_ID">"B"."OBJECT_ID")
3 - filter("B"."CREATED">SYSDATE@!-1)
6 - filter(COUNT(*)>0)
8 - filter("C"."OBJECT_ID">1000)
9 - access("OWNER"="A"."OWNER")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
2480consistent gets
0physical reads
0redo size
2554bytes sent via SQL*Net to client
512bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed这里可以看到，逻辑读变，但是ORACLE的估算成本在这里下将了。
4.4 总结
上面结果是SQL不包括视图时执行还回的结果为0，几种方式的逻辑读基本没有发生变化。
5，语句二测试
下面这个SQL是有结果集还回的
FROM scott.htz1 a,
scott.htz2 b,
(SELECT COUNT (*) COUNT, owner
FROM scott.htz3 c
WHERE c.object_id > 1000
GROUP BY owner) d
WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
18 rows selected.可以看到还回了18行记录
5.1 默认情况
FROM scott.htz1 a,
scott.htz2 b,
(SELECT COUNT (*) COUNT, owner
FROM scott.htz3 c
WHERE c.object_id > 1000
GROUP BY owner) d
WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
18 rows selected.Elapsed: 00:00:00.05Execution Plan----------------------------------------------------------Plan hash value: 1337491101-------------------------------------------------------------------------------------| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 847M| 350G| |5837(40)| 00:01:11 ||*1 |HASH JOIN | | 847M| 350G|3616K|5837(40)| 00:01:11 || 2 | VIEW | | 88109 |2581K| | 348 (2)| 00:00:05 || 3 | HASH GROUP BY | | 88109 |2581K| | 348 (2)| 00:00:05 ||*4 | TABLE ACCESS FULL| HTZ3 | 88109 |2581K| | 345 (1)| 00:00:05 ||*5 | HASH JOIN | | 115K| 45M| | 692 (1)| 00:00:09 ||*6 | TABLE ACCESS FULL | HTZ2 | 14 |2898 | | 346 (1)| 00:00:05 || 7 | TABLE ACCESS FULL | HTZ1 | 99677 | 19M| | 345 (1)| 00:00:05 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("A"."OWNER"="D"."OWNER") 4 - filter("C"."OBJECT_ID">1000) 5 - access("A"."OWNER"="B"."OWNER") 6 - filter("B"."CREATED">SYSDATE@!-1)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 28recursive calls 0db block gets 4385consistent gets 0physical reads 0redo size 4536bytes sent via SQL*Net to client 534bytes received via SQL*Net from client 3SQL*Net roundtrips to/from client 7sorts (memory) 0sorts (disk) 18rows processed这里看到SQL的逻辑读是4385，TEMP消耗3616.
5.2 连接列创建索引
www.htz.pw >create index scott.ind_htz3_1 on scott.htz3(owner);
Index created.
Elapsed: 00:00:00.08
www.htz.pw > SELECT *
2 FROM scott.htz1 a,
3 scott.htz2 b,
4 (SELECT COUNT (*) COUNT, owner
5 FROM scott.htz3 c
6 WHERE c.object_id > 1000
7 GROUP BY owner) d
8 WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
18 rows selected.
Elapsed: 00:00:00.03
Execution Plan
Plan hash value: 3445613030
ç
这里看到逻辑读降到3719，COST也降到1095了。
5.3 强制谓词推入
SELECT /*+ push_pred(d)*/">2 *
3 FROM scott.htz1 a,
4 scott.htz2 b,
5 (SELECT COUNT (*) COUNT, owner
6 FROM scott.htz3 c
7 WHERE c.object_id > 1000
8 GROUP BY owner) d
9 WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
FROM scott.htz1 a,
*
ERROR at line 3:
ORA-01013: user requested cancel of current operation
Elapsed: 00:05:19.68运行很久没有结果
@sql_monitor_by_sqlid.sql">Enter value for sqlid: fkkvkv27pgqbx
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ push_pred(d)*/ * FROM scott.htz1 a, scott.htz2 b, ( SELECT COUNT (*) COUNT, owner FROM scott.htz3 c WHERE c.object_id > 1000 GROUP BY owner) d WHERE a.owner = b.owner AND b.CREATED > SYSDAT
E - 1 AND a.owner = d.owner
Global Information
------------------------------
Status :EXECUTING
Instance ID :1
Session :SYS (26:7)
SQL ID :fkkvkv27pgqbx
SQL Execution ID :16777218
Execution Started :04/20/2015 22:06:30
First Refresh Time:04/20/2015 22:06:36
Last Refresh Time :04/20/2015 22:08:19
Duration :110s
Module/Action :sqlplus@orcl9i (TNS V1-V3)/-
Service :SYS$USERS
Program :sqlplus@orcl9i (TNS V1-V3)
Global Stats
===================================================================
| Elapsed | Cpu | IO |Other | Buffer | Read | Read|
| Time(s) | Time(s) | Waits(s) | Waits(s) |Gets| Reqs | Bytes |
===================================================================
| 108 | 108 | 0.00 | 0.51 | 14M | 2 | 49152 |
===================================================================
SQL Plan Monitoring Details (Plan Hash Value=258155078)
======================================================================================================================================================================
| Id | Operation | Name |Rows | Cost | Time | Start| Execs | Rows | Read | Read| Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
======================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | |
| 1 | MERGE JOIN | | 20M |10M | | | 1 | | | | | | |
| -> 2 | SORT JOIN | | 702K |10M | 104 | +6 | 1 | 0 | | |2M | | |
| -> 3 | NESTED LOOPS | | 702K |10M | 104 | +6 | 1 | 14028 | | | | | |
| -> 4 | TABLE ACCESS FULL | HTZ1 | 86448 |345 | 104 | +6 | 1 | 14028 | | | | | |
| -> 5 | VIEW PUSHED PREDICATE | | 1 |110 | 104 | +6 | 14029 | 14028 | | | | | |
| -> 6 | FILTER | | | | 104 | +6 | 14029 | 14028 | | | | | |
| 7 | SORT AGGREGATE | | 1 | | 104 | +6 | 14029 | 14028 | | | | 1.82 | Cpu (2) |
| 8 | TABLE ACCESS BY INDEX ROWID | HTZ3 | 3716 |110 | 109 | +1 | 14029 | 470M | | | | 69.09 | Cpu (76) |
| -> 9 | INDEX RANGE SCAN | IND_HTZ3_1 | 3759 | 9 | 105 | +6 | 14029 | 480M | 2 | 49152 | | 29.09 | Cpu (32) |
| 10 | SORT JOIN | | 80 |347 | | | | | | | | | |
| 11 | TABLE ACCESS FULL | HTZ2 | 80 |346 | | | | | | | | | |
======================================================================================================================================================================这里可以看到SQL的表间接顺序走错了，我们希望的是a,b做HASH，结果集与D走NL的方式
explain plan for">SELECT /*+ leading(a b d) SWAP_JOIN_INPUTS(a) no_swap_join_inputs(d) use_hash(a b d)push_pred(d)*/
3 *
4 FROM scott.htz1 a,
5 scott.htz2 b,
6 (SELECT COUNT (*) COUNT, owner
7 FROM scott.htz3 c
8 WHERE c.object_id > 1000
9 GROUP BY owner) d
10 WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
Explained.
Elapsed: 00:00:00.00
www.htz.pw > @plan_by_explain.sql
www.htz.pw > set lines 170
www.htz.pw > set pages 1000
www.htz.pw > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2320111649
------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19M|3953M| | 268M(1)|896:07:11 |
| 1 |NESTED LOOPS | | 19M|3953M| | 268M(1)|896:07:11 |
|*2 | HASH JOIN | |2442K| 456M|9288K|1149 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | HTZ1 | 86448 |8273K| | 345 (1)| 00:00:05 |
|*4 | TABLE ACCESS FULL | HTZ2 | 80 |7840 | | 346 (1)| 00:00:05 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 13 | | 110 (0)| 00:00:02 |
|*6 | FILTER | | | | | | |
| 7 | SORT AGGREGATE | | 1 | 11 | | | |
|*8 | TABLE ACCESS BY INDEX ROWID| HTZ3 |3716 | 40876 | | 110 (0)| 00:00:02 |
|*9 | INDEX RANGE SCAN | IND_HTZ3_1 |3759 | | | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="B"."OWNER")
4 - filter("B"."CREATED">SYSDATE@!-1)
6 - filter(COUNT(*)>0)
8 - filter("C"."OBJECT_ID">1000)
9 - access("OWNER"="A"."OWNER")
25 rows selected.
Elapsed: 00:00:00.01
SELECT /*+ leading(a b d) SWAP_JOIN_INPUTS(a) no_swap_join_inputs(d) use_hash(a b d)push_pred(d)*/
*
3 FROM scott.htz1 a,
4 scott.htz2 b,
5 (SELECT COUNT (*) COUNT, owner
6 FROM scott.htz3 c
7 WHERE c.object_id > 1000
8 GROUP BY owner) d
9 WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
18 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 2320111649
------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19M|3953M| | 268M(1)|896:07:11 |
| 1 |NESTED LOOPS | | 19M|3953M| | 268M(1)|896:07:11 |
|*2 | HASH JOIN | |2442K| 456M|9288K|1149 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | HTZ1 | 86448 |8273K| | 345 (1)| 00:00:05 |
|*4 | TABLE ACCESS FULL | HTZ2 | 80 |7840 | | 346 (1)| 00:00:05 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 13 | | 110 (0)| 00:00:02 |
|*6 | FILTER | | | | | | |
| 7 | SORT AGGREGATE | | 1 | 11 | | | |
|*8 | TABLE ACCESS BY INDEX ROWID| HTZ3 |3716 | 40876 | | 110 (0)| 00:00:02 |
|*9 | INDEX RANGE SCAN | IND_HTZ3_1 |3759 | | | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="B"."OWNER")
4 - filter("B"."CREATED">SYSDATE@!-1)
6 - filter(COUNT(*)>0)
8 - filter("C"."OBJECT_ID">1000)
9 - access("OWNER"="A"."OWNER")
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
2543consistent gets
0physical reads
0redo size
4047bytes sent via SQL*Net to client
534bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
18rows processed这里看到逻辑读下降到2543，但是消耗了temspc空间，cost也是很多的。
其实VIEW中条件越多，创建组合索引效果会更好
create index scott.ind_htz3_2 on scott.htz3(owner,object_id);">
Index created.
Elapsed: 00:00:00.10
SELECT /*+ leading(a b d) SWAP_JOIN_INPUTS(a) no_swap_join_inputs(d) use_hash(a b d)push_pred(d)*/
2 *
3 FROM scott.htz1 a,
4 scott.htz2 b,
5 (SELECT COUNT (*) COUNT, owner
6 FROM scott.htz3 c
7 WHERE c.object_id > 1000
8 GROUP BY owner) d
9 WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
18 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1875749008
----------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19M|3953M| | 29M(1)| 97:53:05 |
| 1 |NESTED LOOPS | | 19M|3953M| | 29M(1)| 97:53:05 |
|*2 | HASH JOIN | |2442K| 456M|9288K|1149 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | HTZ1 | 86448 |8273K| | 345 (1)| 00:00:05 |
|*4 | TABLE ACCESS FULL | HTZ2 | 80 |7840 | | 346 (1)| 00:00:05 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 13 | | 12 (0)| 00:00:01 |
|*6 | FILTER | | | | | | |
| 7 | SORT AGGREGATE | | 1 | 11 | | | |
|*8 | INDEX RANGE SCAN | IND_HTZ3_2 |3716 | 40876 | | 12 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="B"."OWNER")
4 - filter("B"."CREATED">SYSDATE@!-1)
6 - filter(COUNT(*)>0)
8 - access("OWNER"="A"."OWNER" AND "C"."OBJECT_ID">1000 AND "C"."OBJECT_ID" IS NOT
NULL)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
2489consistent gets这里减少了54个BLOCK基本就是一下回表的操作
0physical reads
0redo size
4047bytes sent via SQL*Net to client
534bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
18rows processed6，禁用推入
alter system set "_optimizer_extend_jppd_view_types"=false;">
System altered.
Elapsed: 00:00:00.01禁用_optimizer_extend_jppd_view_types参数，这里可以看到走HASH，消耗大量TEMPSPC
*
3 FROM scott.htz1 a,
4 scott.htz2 b,
5 (SELECT COUNT (*) COUNT, owner
6 FROM scott.htz3 c
7 WHERE c.object_id > 1000
8 GROUP BY owner) d
9 WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
18 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2571011931
----------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19M|4274M| | 25302 (1)| 00:05:04 |
|*1 |HASH JOIN | | 19M|4274M| 484M| 25302 (1)| 00:05:04 |
|*2 | HASH JOIN | |2442K| 456M|9288K|1149 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | HTZ1 | 86448 |8273K| | 345 (1)| 00:00:05 |
|*4 | TABLE ACCESS FULL | HTZ2 | 80 |7840 | | 346 (1)| 00:00:05 |
| 5 | VIEW | | 23 | 690 | | 75 (4)| 00:00:01 |
| 6 | HASH GROUP BY | | 23 | 253 | | 75 (4)| 00:00:01 |
|*7 | INDEX FAST FULL SCAN| IND_HTZ3_2 | 85470 | 918K| | 72 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OWNER"="D"."OWNER")
2 - access("A"."OWNER"="B"."OWNER")
4 - filter("B"."CREATED">SYSDATE@!-1)
7 - filter("C"."OBJECT_ID">1000)
Statistics
----------------------------------------------------------
1recursive calls
0db block gets
2746consistent gets
0physical reads
0redo size
4080bytes sent via SQL*Net to client
534bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
18rows processed测试结束
MOS中已经有很多文档对连接列谓词推入做了详细说明
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]