【案例】Oracle优化之优化SQL时遇到连接谓词推入不成功分析
时间:2016-11-04 19:31 来源:Oracle研究中心 作者:HTZ 点击:
次
天萃荷净
Oracle研究中心案例分析：运维DBA反映在做Oracle SQL优化时遇到连接谓词推入不成功情况，在测试环境中进行判断。
今天在客户现场优化SQL时遇到连接谓词推入不成功，由于但是客户正式环境不允许操作，测试环境又是10G环境，没有办法测试，下面是在自己的VM里面测试。
1，环境介绍
本次实验是基于11.2.0.4环境，其它环境在默认情况下可能结果会不一致。
oracleplus.net> select * from v$version where rownum<4;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
Elapsed: 00:00:00.00
oracleplus.net> set timing off
oracleplus.net> select * from v$version where rownum<4;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
oracleplus.net> !lsb_release -a
LSB Version: :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: RedHatEnterpriseAS
Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Release: 4
Codename: NahantUpdate8
2，参数配置
下面参数都为默认情况下的配置
oracleplus.net> SELECT a.ksppinm AS parameter,
2 b.ksppstvl AS session_value,
3 c.ksppstvl AS instance_value,
4 a.ksppdesc AS description
5 FROM x$ksppi a,
6 x$ksppcv b,
7 x$ksppsv c
8 WHERE a.indx = b.indx
9 AND a.indx = c.indx
10 AND a.ksppinm LIKE '/_%' ESCAPE '/'
11 AND (a.ksppinm in ('_push_join_predicate','_optimizer_push_pred_cost_based','_optimizer_extend_jppd_view_types'))
12 ORDER BY a.ksppinm
13 /
PARAMETER S_VALUE D_VALUE DESCRIPTION
----------------------------------- ------- -------- ----------------------------------------------------------------
_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 view
3，创建测试表
这里都是基于dba_objects视图创建的。
oracleplus.net> create table scott.htz1 as select * from dba_objects;
oracleplus.net> create table scott.htz2 as select * from dba_objects;
oracleplus.net> create table scott.htz3 as select * from dba_objects;
4，语句一测试
下面这个SQL还回是0行，其实出view，SQL本来也就是还回0行，这个还回的行数直接影响到我们的测试结果。
SELECT *
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 默认情况
oracleplus.net> SELECT *
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 selected
Execution 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
----------------------------------------------------------
0 recursive calls
0 db block gets
2480 consistent gets
0 physical reads
0 redo size
2554 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
这里看到整个SQL逻辑读是2480，也消耗了TEMPSPC为2408K.
4.2 强制连接列谓词推入
oracleplus.net> 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
----------------------------------------------------------
0 recursive calls
0 db block gets
2480 consistent gets
0 physical reads
0 redo size
2554 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
这里消耗的逻辑读也是2480，但是tempspc为0.但是这里ORACLE自己估算出来谓词推入成本更高。
4.3 连接列创建索引
oracleplus.net> 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
----------------------------------------------------------
0 recursive calls
0 db block gets
2480 consistent gets
0 physical reads
0 redo size
2554 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
这里可以看到，逻辑读变，但是ORACLE的估算成本在这里下将了。
4.4 总结
上面结果是SQL不包括视图时执行还回的结果为0，几种方式的逻辑读基本没有发生变化。
5，语句二测试
下面这个SQL是有结果集还回的
SELECT *
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 默认情况
oracleplus.net> SELECT *
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.05
Execution 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
----------------------------------------------------------
28 recursive calls
0 db block gets
4385 consistent gets
0 physical reads
0 redo size
4536 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
18 rows processed
这里看到SQL的逻辑读是4385，TEMP消耗3616.
5.2 连接列创建索引
oracleplus.net> create index scott.ind_htz3_1 on scott.htz3(owner);
Index created.
Elapsed: 00:00:00.08
oracleplus.net> 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
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19M| 4274M| 1095 (6)| 00:00:14 |
|* 1 | HASH JOIN | | 19M| 4274M| 1095 (6)| 00:00:14 |
|* 2 | TABLE ACCESS FULL | HTZ2 | 80 | 7840 | 346 (1)| 00:00:05 |
|* 3 | HASH JOIN | | 701K| 85M| 695 (2)| 00:00:09 |
| 4 | VIEW | | 23 | 690 | 348 (2)| 00:00:05 |
| 5 | HASH GROUP BY | | 23 | 253 | 348 (2)| 00:00:05 |
|* 6 | TABLE ACCESS FULL| HTZ3 | 85470 | 918K| 345 (1)| 00:00:05 |
| 7 | TABLE ACCESS FULL | HTZ1 | 86448 | 8273K| 345 (1)| 00:00:05 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OWNER"="B"."OWNER")
2 - filter("B"."CREATED">SYSDATE@!-1)
3 - access("A"."OWNER"="D"."OWNER")
6 - filter("C"."OBJECT_ID">1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3719 consistent gets
0 physical reads
0 redo size
4536 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
这里看到逻辑读降到3719，COST也降到1095了。
5.3 强制谓词推入
oracleplus.net> 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
运行很久没有结果
oracleplus.net> @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 | | (%) | (# http://www.oracleplus.netsamples) |
======================================================================================================================================================================
| 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的方式
oracleplus.net> 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
oracleplus.net> @plan_by_explain.sql
oracleplus.net> set lines 170
oracleplus.net> set pages 1000
oracleplus.net> 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
----------------------------------------------------------
0 recursive calls
0 db block gets
2543 consistent gets
0 physical reads
0 redo size
4047 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
这里看到逻辑读下降到2543，但是消耗了temspc空间，cost也是很多的。
其实VIEW中条件越多，创建组合索引效果会更好
oracleplus.net> 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
----------------------------------------------------------
0 recursive calls
0 db block gets
2489 consistent gets 这里减少了54个BLOCK基本就是一下回表的操作
0 physical reads
0 redo size
4047 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
6，禁用推入
oracleplus.net> alter system set "_optimizer_extend_jppd_view_types"=false;
System altered.
Elapsed: 00:00:00.01
禁用_optimizer_extend_jppd_view_types参数，这里可以看到走HASH，消耗大量TEMPSPC
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.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
----------------------------------------------------------
1 recursive calls
0 db block gets
2746 consistent gets
0 physical reads
0 redo size
4080 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
测试结束
MOS中已经有很多文档对连接列谓词推入做了详细说明
本文固定链接: http://www.htz.pw/2015/04/21/optimizer-join-push-predicate.html | 认真就输
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【案例】Oracle优化之优化SQL时遇到连接谓词推入不成功分析
205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
