登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›性能调优：JPPD（连接谓词下推）客户案例模拟 ...

性能调优：JPPD（连接谓词下推）客户案例模拟

遗憩 2025-6-17 21:10:22
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
  1. 前面几天发了几篇JPPD文章，今天就来演示一下客户现场遇到真实案例的模拟，这个在客户现场优化SQL时遇到连接谓词推入不成功的案例，由于但是客户正式环境不允许操作，有没有测试环境，没有办法测试，下面是在自己的虚拟机里面演示一下，优化后能带来的性能提升。
复制代码
1，环境介绍

本次实验是基于11.2.0.4环境，其它环境在默认情况下可能结果会不一致。
  1. www.htz.pw > select * from v$version where rownum<4;
  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  8. www.htz.pw > !lsb_release -a
  9. Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
复制代码
2，参数配置

下面参数都为默认情况下的配置
  1. SELECT a.ksppinm AS parameter,">  2         b.ksppstvl AS session_value,
  2.   3         c.ksppstvl AS instance_value,
  3.   4         a.ksppdesc AS description
  4.   5  FROM   x$ksppi a,
  5.   6         x$ksppcv b,
  6.   7         x$ksppsv c
  7.   8  WHERE  a.indx = b.indx
  8.   9  AND    a.indx = c.indx
  9. 10  AND    a.ksppinm LIKE '/_%' ESCAPE '/'
  10. 11  AND    (a.ksppinm in ('_push_join_predicate','_optimizer_push_pred_cost_based','_optimizer_extend_jppd_view_types'))
  11. 12  ORDER BY a.ksppinm
  12. 13  /
  14. PARAMETER                           S_VALUE   D_VALUE  DESCRIPTION
  15. ----------------------------------- -------   -------- ----------------------------------------------------------------
  16. _optimizer_extend_jppd_view_types   TRUE      TRUE     join pred pushdown on group-by, distinct, semi-/anti-joined view
  17. _optimizer_push_pred_cost_based     TRUE      TRUE     use cost-based query transformation for push pred optimization
  18. _push_join_predicate                TRUE      TRUE     enable pushing join predicate inside a view
复制代码
3，创建测试表

这里都是基于dba_objects视图创建的。
  1. www.htz.pw > create table scott.htz1 as select * from dba_objects;
  2. www.htz.pw > create table scott.htz2 as select * from dba_objects;
  3. www.htz.pw > create table scott.htz3 as select * from dba_objects;
复制代码
4，语句一测试

下面这个SQL还回是0行，其实出view，SQL本来也就是还回0行，这个还回的行数直接影响到我们的测试结果。
  1.     FROM scott.htz1 a,
  2.          scott.htz2 b,
  3.          (  SELECT COUNT (*) COUNT, owner
  4.               FROM scott.htz3 c
  5.              WHERE c.object_id > 1000
  6.           GROUP BY owner) d
  7.    WHERE     a.owner = b.owner
  8.          AND a.object_id > b.object_id
  9.          AND b.CREATED > SYSDATE - 1
  10.          AND a.owner = d.owner;
复制代码
4.1 默认情况
  1.     FROM scott.htz1 a,
  2.          scott.htz2 b,
  3.          (  SELECT COUNT (*) COUNT, owner
  4.               FROM scott.htz3 c
  5.              WHERE c.object_id > 1000
  6.           GROUP BY owner) d
  7.    WHERE     a.owner = b.owner
  8.          AND a.object_id > b.object_id
  9.          AND b.CREATED > SYSDATE - 1
  10.          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----------------------------------------------------------          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 强制连接列谓词推入
  1. SELECT /*+ push_pred(d)*/">  2        *
  2.   3    FROM scott.htz1 a,
  3.   4         scott.htz2 b,
  4.   5         (  SELECT COUNT (*) COUNT, owner
  5.   6              FROM scott.htz3 c
  6.   7             WHERE c.object_id > 1000
  7.   8          GROUP BY owner) d
  8.   9   WHERE     a.owner = b.owner
  9. 10         AND a.object_id > b.object_id
  10. 11         AND b.CREATED > SYSDATE - 1
  11. 12         AND a.owner = d.owner;
  13. no rows selected
  16. Execution Plan
  17. ----------------------------------------------------------
  18. Plan hash value: 1009835727
  20. --------------------------------------------------------------------------------
  21. | Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  22. --------------------------------------------------------------------------------
  23. |   0 | SELECT STATEMENT        |      |    42M|    16G|  1990K  (1)| 06:38:11 |
  24. |   1 |  NESTED LOOPS           |      |    42M|    16G|  1990K  (1)| 06:38:11 |
  25. |*  2 |   HASH JOIN             |      |  5771 |  2333K|   692   (1)| 00:00:09 |
  26. |*  3 |    TABLE ACCESS FULL    | HTZ2 |    14 |  2898 |   346   (1)| 00:00:05 |
  27. |   4 |    TABLE ACCESS FULL    | HTZ1 | 99677 |    19M|   345   (1)| 00:00:05 |
  28. |   5 |   VIEW PUSHED PREDICATE |      |     1 |    13 |   345   (1)| 00:00:05 |
  29. |*  6 |    FILTER               |      |       |       |            |          |
  30. |   7 |     SORT AGGREGATE      |      |     1 |    30 |            |          |
  31. |*  8 |      TABLE ACCESS FULL  | HTZ3 |   881 | 26430 |   345   (1)| 00:00:05 |
  32. --------------------------------------------------------------------------------
  34. Predicate Information (identified by operation id):
  35. ---------------------------------------------------
  37.    2 - access("A"."OWNER"="B"."OWNER")
  38.        filter("A"."OBJECT_ID">"B"."OBJECT_ID")
  39.    3 - filter("B"."CREATED">SYSDATE@!-1)
  40.    6 - filter(COUNT(*)>0)
  41.    8 - filter("OWNER"="A"."OWNER" AND "C"."OBJECT_ID">1000)
  43. Note
  44. -----
  45.    - dynamic sampling used for this statement (level=2)
  48. Statistics
  49. ----------------------------------------------------------
  50.           0  recursive calls
  51.           0  db block gets
  52.        2480  consistent gets
  53.           0  physical reads
  54.           0  redo size
  55.        2554  bytes sent via SQL*Net to client
  56.         512  bytes received via SQL*Net from client
  57.           1  SQL*Net roundtrips to/from client
  58.           0  sorts (memory)
  59.           0  sorts (disk)
  60.           0  rows processed
复制代码
这里消耗的逻辑读也是2480，但是tempspc为0.但是这里ORACLE自己估算出来谓词推入成本更高。
4.3 连接列创建索引
  1. SELECT /*+ push_pred(d)*/">  2        *
  2.   3    FROM scott.htz1 a,
  3.   4         scott.htz2 b,
  4.   5         (  SELECT COUNT (*) COUNT, owner
  5.   6              FROM scott.htz3 c
  6.   7             WHERE c.object_id > 1000
  7.   8          GROUP BY owner) d
  8.   9   WHERE     a.owner = b.owner
  9. 10         AND a.object_id > b.object_id
  10. 11         AND b.CREATED > SYSDATE - 1
  11. 12         AND a.owner = d.owner;
  13. no rows selected
  16. Execution Plan
  17. ----------------------------------------------------------
  18. Plan hash value: 3435586372
  20. ----------------------------------------------------------------------------------------------
  21. | Id  | Operation                       | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
  22. ----------------------------------------------------------------------------------------------
  23. |   0 | SELECT STATEMENT                |            |    42M|    16G|   104K  (1)| 00:20:56 |
  24. |   1 |  NESTED LOOPS                   |            |    42M|    16G|   104K  (1)| 00:20:56 |
  25. |*  2 |   HASH JOIN                     |            |  5771 |  2333K|   692   (1)| 00:00:09 |
  26. |*  3 |    TABLE ACCESS FULL            | HTZ2       |    14 |  2898 |   346   (1)| 00:00:05 |
  27. |   4 |    TABLE ACCESS FULL            | HTZ1       | 99677 |    19M|   345   (1)| 00:00:05 |
  28. |   5 |   VIEW PUSHED PREDICATE         |            |     1 |    13 |    18   (0)| 00:00:01 |
  29. |*  6 |    FILTER                       |            |       |       |            |          |
  30. |   7 |     SORT AGGREGATE              |            |     1 |    30 |            |          |
  31. |*  8 |      TABLE ACCESS BY INDEX ROWID| HTZ3       |   881 | 26430 |    18   (0)| 00:00:01 |
  32. |*  9 |       INDEX RANGE SCAN          | IND_HTZ3_1 |   352 |       |     8   (0)| 00:00:01 |
  33. ----------------------------------------------------------------------------------------------
  35. Predicate Information (identified by operation id):
  36. ---------------------------------------------------
  38.    2 - access("A"."OWNER"="B"."OWNER")
  39.        filter("A"."OBJECT_ID">"B"."OBJECT_ID")
  40.    3 - filter("B"."CREATED">SYSDATE@!-1)
  41.    6 - filter(COUNT(*)>0)
  42.    8 - filter("C"."OBJECT_ID">1000)
  43.    9 - access("OWNER"="A"."OWNER")
  45. Note
  46. -----
  47.    - dynamic sampling used for this statement (level=2)
  50. Statistics
  51. ----------------------------------------------------------
  52.           0  recursive calls
  53.           0  db block gets
  54.        2480  consistent gets
  55.           0  physical reads
  56.           0  redo size
  57.        2554  bytes sent via SQL*Net to client
  58.         512  bytes received via SQL*Net from client
  59.           1  SQL*Net roundtrips to/from client
  60.           0  sorts (memory)
  61.           0  sorts (disk)
  62.           0  rows processed
复制代码
这里可以看到，逻辑读变，但是ORACLE的估算成本在这里下将了。
4.4 总结

上面结果是SQL不包括视图时执行还回的结果为0，几种方式的逻辑读基本没有发生变化。
5，语句二测试

下面这个SQL是有结果集还回的
  1.    FROM scott.htz1 a,
  2.         scott.htz2 b,
  3.         (  SELECT COUNT (*) COUNT, owner
  4.              FROM scott.htz3 c
  5.             WHERE c.object_id > 1000
  6.          GROUP BY owner) d
  7.   WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
  9. 18 rows selected.
复制代码
可以看到还回了18行记录
5.1 默认情况
  1.    FROM scott.htz1 a,
  2.         scott.htz2 b,
  3.         (  SELECT COUNT (*) COUNT, owner
  4.              FROM scott.htz3 c
  5.             WHERE c.object_id > 1000
  6.          GROUP BY owner) d
  7.   WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
  9. 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----------------------------------------------------------         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 连接列创建索引

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 强制谓词推入
  1. SELECT /*+ push_pred(d)*/">  2        *
  2.   3    FROM scott.htz1 a,
  3.   4         scott.htz2 b,
  4.   5         (  SELECT COUNT (*) COUNT, owner
  5.   6              FROM scott.htz3 c
  6.   7             WHERE c.object_id > 1000
  7.   8          GROUP BY owner) d
  8.   9   WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
  9.   FROM scott.htz1 a,
  10.              *
  11. ERROR at line 3:
  12. ORA-01013: user requested cancel of current operation
  15. Elapsed: 00:05:19.68
复制代码
运行很久没有结果
  1. @sql_monitor_by_sqlid.sql">Enter value for sqlid: fkkvkv27pgqbx
  2. SQL Monitoring Report
  4. SQL Text
  5. ------------------------------
  6. 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
  7. E - 1 AND a.owner = d.owner
  9. Global Information
  10. ------------------------------
  11. Status              :  EXECUTING
  12. Instance ID         :  1
  13. Session             :  SYS (26:7)
  14. SQL ID              :  fkkvkv27pgqbx
  15. SQL Execution ID    :  16777218
  16. Execution Started   :  04/20/2015 22:06:30
  17. First Refresh Time  :  04/20/2015 22:06:36
  18. Last Refresh Time   :  04/20/2015 22:08:19
  19. Duration            :  110s
  20. Module/Action       :  sqlplus@orcl9i (TNS V1-V3)/-
  21. Service             :  SYS$USERS
  22. Program             :  sqlplus@orcl9i (TNS V1-V3)
  24. Global Stats
  25. ===================================================================
  26. | Elapsed |   Cpu   |    IO    |  Other   | Buffer | Read | Read  |
  27. | Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes |
  28. ===================================================================
  29. |     108 |     108 |     0.00 |     0.51 |    14M |    2 | 49152 |
  30. ===================================================================
  32. SQL Plan Monitoring Details (Plan Hash Value=258155078)
  33. ======================================================================================================================================================================
  34. | Id   |              Operation              |    Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Mem | Activity | Activity Detail |
  35. |      |                                     |            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |     |   (%)    |   (# samples)   |
  36. ======================================================================================================================================================================
  37. |    0 | SELECT STATEMENT                    |            |         |      |           |        |     1 |          |      |       |     |          |                 |
  38. |    1 |   MERGE JOIN                        |            |     20M |  10M |           |        |     1 |          |      |       |     |          |                 |
  39. | -> 2 |    SORT JOIN                        |            |    702K |  10M |       104 |     +6 |     1 |        0 |      |       |  2M |          |                 |
  40. | -> 3 |     NESTED LOOPS                    |            |    702K |  10M |       104 |     +6 |     1 |    14028 |      |       |     |          |                 |
  41. | -> 4 |      TABLE ACCESS FULL              | HTZ1       |   86448 |  345 |       104 |     +6 |     1 |    14028 |      |       |     |          |                 |
  42. | -> 5 |      VIEW PUSHED PREDICATE          |            |       1 |  110 |       104 |     +6 | 14029 |    14028 |      |       |     |          |                 |
  43. | -> 6 |       FILTER                        |            |         |      |       104 |     +6 | 14029 |    14028 |      |       |     |          |                 |
  44. |    7 |        SORT AGGREGATE               |            |       1 |      |       104 |     +6 | 14029 |    14028 |      |       |     |     1.82 | Cpu (2)         |
  45. |    8 |         TABLE ACCESS BY INDEX ROWID | HTZ3       |    3716 |  110 |       109 |     +1 | 14029 |     470M |      |       |     |    69.09 | Cpu (76)        |
  46. | -> 9 |          INDEX RANGE SCAN           | IND_HTZ3_1 |    3759 |    9 |       105 |     +6 | 14029 |     480M |    2 | 49152 |     |    29.09 | Cpu (32)        |
  47. |   10 |    SORT JOIN                        |            |      80 |  347 |           |        |       |          |      |       |     |          |                 |
  48. |   11 |     TABLE ACCESS FULL               | HTZ2       |      80 |  346 |           |        |       |          |      |       |     |          |                 |
  49. ======================================================================================================================================================================
复制代码
这里可以看到SQL的表间接顺序走错了，我们希望的是a,b做HASH，结果集与D走NL的方式
  1. 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)*/
  2.   3        *
  3.   4    FROM scott.htz1 a,
  4.   5         scott.htz2 b,
  5.   6         (  SELECT COUNT (*) COUNT, owner
  6.   7              FROM scott.htz3 c
  7.   8             WHERE c.object_id > 1000
  8.   9          GROUP BY owner) d
  9. 10   WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
  11. Explained.
  13. Elapsed: 00:00:00.00
  14. www.htz.pw > @plan_by_explain.sql
  15. www.htz.pw > set lines 170
  16. www.htz.pw > set pages 1000
  17. www.htz.pw > select * from table(dbms_xplan.display);
  19. PLAN_TABLE_OUTPUT
  20. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  21. Plan hash value: 2320111649
  23. ------------------------------------------------------------------------------------------------------
  24. | Id  | Operation                       | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  25. ------------------------------------------------------------------------------------------------------
  26. |   0 | SELECT STATEMENT                |            |    19M|  3953M|       |   268M  (1)|896:07:11 |
  27. |   1 |  NESTED LOOPS                   |            |    19M|  3953M|       |   268M  (1)|896:07:11 |
  28. |*  2 |   HASH JOIN                     |            |  2442K|   456M|  9288K|  1149   (1)| 00:00:14 |
  29. |   3 |    TABLE ACCESS FULL            | HTZ1       | 86448 |  8273K|       |   345   (1)| 00:00:05 |
  30. |*  4 |    TABLE ACCESS FULL            | HTZ2       |    80 |  7840 |       |   346   (1)| 00:00:05 |
  31. |   5 |   VIEW PUSHED PREDICATE         |            |     1 |    13 |       |   110   (0)| 00:00:02 |
  32. |*  6 |    FILTER                       |            |       |       |       |            |          |
  33. |   7 |     SORT AGGREGATE              |            |     1 |    11 |       |            |          |
  34. |*  8 |      TABLE ACCESS BY INDEX ROWID| HTZ3       |  3716 | 40876 |       |   110   (0)| 00:00:02 |
  35. |*  9 |       INDEX RANGE SCAN          | IND_HTZ3_1 |  3759 |       |       |     9   (0)| 00:00:01 |
  36. ------------------------------------------------------------------------------------------------------
  38. Predicate Information (identified by operation id):
  39. ---------------------------------------------------
  41.    2 - access("A"."OWNER"="B"."OWNER")
  42.    4 - filter("B"."CREATED">SYSDATE@!-1)
  43.    6 - filter(COUNT(*)>0)
  44.    8 - filter("C"."OBJECT_ID">1000)
  45.    9 - access("OWNER"="A"."OWNER")
  47. 25 rows selected.
  49. Elapsed: 00:00:00.01
  53. SELECT /*+ leading(a b d) SWAP_JOIN_INPUTS(a) no_swap_join_inputs(d) use_hash(a b d)  push_pred(d)*/
  54.       *
  55.   3    FROM scott.htz1 a,
  56.   4         scott.htz2 b,
  57.   5         (  SELECT COUNT (*) COUNT, owner
  58.   6              FROM scott.htz3 c
  59.   7             WHERE c.object_id > 1000
  60.   8          GROUP BY owner) d
  61.   9   WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
  63. 18 rows selected.
  65. Elapsed: 00:00:00.05
  67. Execution Plan
  68. ----------------------------------------------------------
  69. Plan hash value: 2320111649
  71. ------------------------------------------------------------------------------------------------------
  72. | Id  | Operation                       | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  73. ------------------------------------------------------------------------------------------------------
  74. |   0 | SELECT STATEMENT                |            |    19M|  3953M|       |   268M  (1)|896:07:11 |
  75. |   1 |  NESTED LOOPS                   |            |    19M|  3953M|       |   268M  (1)|896:07:11 |
  76. |*  2 |   HASH JOIN                     |            |  2442K|   456M|  9288K|  1149   (1)| 00:00:14 |
  77. |   3 |    TABLE ACCESS FULL            | HTZ1       | 86448 |  8273K|       |   345   (1)| 00:00:05 |
  78. |*  4 |    TABLE ACCESS FULL            | HTZ2       |    80 |  7840 |       |   346   (1)| 00:00:05 |
  79. |   5 |   VIEW PUSHED PREDICATE         |            |     1 |    13 |       |   110   (0)| 00:00:02 |
  80. |*  6 |    FILTER                       |            |       |       |       |            |          |
  81. |   7 |     SORT AGGREGATE              |            |     1 |    11 |       |            |          |
  82. |*  8 |      TABLE ACCESS BY INDEX ROWID| HTZ3       |  3716 | 40876 |       |   110   (0)| 00:00:02 |
  83. |*  9 |       INDEX RANGE SCAN          | IND_HTZ3_1 |  3759 |       |       |     9   (0)| 00:00:01 |
  84. ------------------------------------------------------------------------------------------------------
  86. Predicate Information (identified by operation id):
  87. ---------------------------------------------------
  89.    2 - access("A"."OWNER"="B"."OWNER")
  90.    4 - filter("B"."CREATED">SYSDATE@!-1)
  91.    6 - filter(COUNT(*)>0)
  92.    8 - filter("C"."OBJECT_ID">1000)
  93.    9 - access("OWNER"="A"."OWNER")
  96. Statistics
  97. ----------------------------------------------------------
  98.           0  recursive calls
  99.           0  db block gets
  100.        2543  consistent gets
  101.           0  physical reads
  102.           0  redo size
  103.        4047  bytes sent via SQL*Net to client
  104.         534  bytes received via SQL*Net from client
  105.           3  SQL*Net roundtrips to/from client
  106.           0  sorts (memory)
  107.           0  sorts (disk)
  108.          18  rows processed
复制代码
这里看到逻辑读下降到2543，但是消耗了temspc空间，cost也是很多的。
其实VIEW中条件越多，创建组合索引效果会更好
  1. create index scott.ind_htz3_2 on scott.htz3(owner,object_id);">
  2. Index created.
  4. Elapsed: 00:00:00.10
  7. SELECT /*+ leading(a b d) SWAP_JOIN_INPUTS(a) no_swap_join_inputs(d) use_hash(a b d)  push_pred(d)*/
  8.   2        *
  9.   3    FROM scott.htz1 a,
  10.   4         scott.htz2 b,
  11.   5         (  SELECT COUNT (*) COUNT, owner
  12.   6              FROM scott.htz3 c
  13.   7             WHERE c.object_id > 1000
  14.   8          GROUP BY owner) d
  15.   9   WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
  17. 18 rows selected.
  19. Elapsed: 00:00:00.04
  21. Execution Plan
  22. ----------------------------------------------------------
  23. Plan hash value: 1875749008
  25. ----------------------------------------------------------------------------------------------
  26. | Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  27. ----------------------------------------------------------------------------------------------
  28. |   0 | SELECT STATEMENT        |            |    19M|  3953M|       |    29M  (1)| 97:53:05 |
  29. |   1 |  NESTED LOOPS           |            |    19M|  3953M|       |    29M  (1)| 97:53:05 |
  30. |*  2 |   HASH JOIN             |            |  2442K|   456M|  9288K|  1149   (1)| 00:00:14 |
  31. |   3 |    TABLE ACCESS FULL    | HTZ1       | 86448 |  8273K|       |   345   (1)| 00:00:05 |
  32. |*  4 |    TABLE ACCESS FULL    | HTZ2       |    80 |  7840 |       |   346   (1)| 00:00:05 |
  33. |   5 |   VIEW PUSHED PREDICATE |            |     1 |    13 |       |    12   (0)| 00:00:01 |
  34. |*  6 |    FILTER               |            |       |       |       |            |          |
  35. |   7 |     SORT AGGREGATE      |            |     1 |    11 |       |            |          |
  36. |*  8 |      INDEX RANGE SCAN   | IND_HTZ3_2 |  3716 | 40876 |       |    12   (0)| 00:00:01 |
  37. ----------------------------------------------------------------------------------------------
  39. Predicate Information (identified by operation id):
  40. ---------------------------------------------------
  42.    2 - access("A"."OWNER"="B"."OWNER")
  43.    4 - filter("B"."CREATED">SYSDATE@!-1)
  44.    6 - filter(COUNT(*)>0)
  45.    8 - access("OWNER"="A"."OWNER" AND "C"."OBJECT_ID">1000 AND "C"."OBJECT_ID" IS NOT
  46.               NULL)
  49. Statistics
  50. ----------------------------------------------------------
  51.           0  recursive calls
  52.           0  db block gets
  53.        2489  consistent gets  这里减少了54个BLOCK基本就是一下回表的操作
  54.           0  physical reads
  55.           0  redo size
  56.        4047  bytes sent via SQL*Net to client
  57.         534  bytes received via SQL*Net from client
  58.           3  SQL*Net roundtrips to/from client
  59.           0  sorts (memory)
  60.           0  sorts (disk)
  61.          18  rows processed
复制代码
6，禁用推入
  1. alter system set "_optimizer_extend_jppd_view_types"=false;">
  2. System altered.
  4. Elapsed: 00:00:00.01
复制代码
禁用_optimizer_extend_jppd_view_types参数，这里可以看到走HASH，消耗大量TEMPSPC
  1.           *
  2.   3        FROM scott.htz1 a,
  3.   4             scott.htz2 b,
  4.   5             (  SELECT COUNT (*) COUNT, owner
  5.   6                  FROM scott.htz3 c
  6.   7                 WHERE c.object_id > 1000
  7.   8              GROUP BY owner) d
  8.   9       WHERE a.owner = b.owner AND b.CREATED > SYSDATE - 1 AND a.owner = d.owner;
  10. 18 rows selected.
  12. Elapsed: 00:00:00.06
  14. Execution Plan
  15. ----------------------------------------------------------
  16. Plan hash value: 2571011931
  18. ----------------------------------------------------------------------------------------------
  19. | Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  20. ----------------------------------------------------------------------------------------------
  21. |   0 | SELECT STATEMENT        |            |    19M|  4274M|       | 25302   (1)| 00:05:04 |
  22. |*  1 |  HASH JOIN              |            |    19M|  4274M|   484M| 25302   (1)| 00:05:04 |
  23. |*  2 |   HASH JOIN             |            |  2442K|   456M|  9288K|  1149   (1)| 00:00:14 |
  24. |   3 |    TABLE ACCESS FULL    | HTZ1       | 86448 |  8273K|       |   345   (1)| 00:00:05 |
  25. |*  4 |    TABLE ACCESS FULL    | HTZ2       |    80 |  7840 |       |   346   (1)| 00:00:05 |
  26. |   5 |   VIEW                  |            |    23 |   690 |       |    75   (4)| 00:00:01 |
  27. |   6 |    HASH GROUP BY        |            |    23 |   253 |       |    75   (4)| 00:00:01 |
  28. |*  7 |     INDEX FAST FULL SCAN| IND_HTZ3_2 | 85470 |   918K|       |    72   (0)| 00:00:01 |
  29. ----------------------------------------------------------------------------------------------
  31. Predicate Information (identified by operation id):
  32. ---------------------------------------------------
  34.    1 - access("A"."OWNER"="D"."OWNER")
  35.    2 - access("A"."OWNER"="B"."OWNER")
  36.    4 - filter("B"."CREATED">SYSDATE@!-1)
  37.    7 - filter("C"."OBJECT_ID">1000)
  40. Statistics
  41. ----------------------------------------------------------
  42.           1  recursive calls
  43.           0  db block gets
  44.        2746  consistent gets
  45.           0  physical reads
  46.           0  redo size
  47.        4080  bytes sent via SQL*Net to client
  48.         534  bytes received via SQL*Net from client
  49.           3  SQL*Net roundtrips to/from client
  50.           0  sorts (memory)
  51.           0  sorts (disk)
  52.          18  rows processed
复制代码
测试结束
MOS中已经有很多文档对连接列谓词推入做了详细说明
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
1.png


来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
遗憩
2025-6-17 21:10:22

0

粉丝关注

9

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫