当前位置: 首页 > 调优 > 正文

Optimizer Join Push Predicate

    今天在客户现场优化SQL时遇到连接谓词推入不成功,由于但是客户正式环境不允许操作,测试环境又是10G环境,没有办法测试,下面是在自己的VM里面测试。

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
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production

Elapsed: 00:00:00.00
www.htz.pw > set timing off
www.htz.pw > 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

www.htz.pw > !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,参数配置

下面参数都为默认情况下的配置

www.htz.pw > 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视图创建的。

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行,其实出viewSQL本来也就是还回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 默认情况

www.htz.pw > 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,也消耗了TEMPSPC2408K.

4.2 强制连接列谓词推入

www.htz.pw > 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,但是tempspc0.但是这里ORACLE自己估算出来谓词推入成本更高。

4.3 连接列创建索引

www.htz.pw > 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 默认情况

www.htz.pw > 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的逻辑读是4385TEMP消耗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

------------------------------------------------------------------------------
| 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

这里看到逻辑读降到3719COST也降到1095了。

5.3 强制谓词推入

www.htz.pw > 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

运行很久没有结果

www.htz.pw > @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的方式
www.htz.pw > 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
----------------------------------------------------------
          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中条件越多,创建组合索引效果会更好

www.htz.pw > 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,禁用推入

www.htz.pw > 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 | 认真就输

该日志由 huangtingzhong 于2015年04月21日发表在 调优 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: Optimizer Join Push Predicate | 认真就输
关键字: