push_pred/no_push_pred无效探究

背景

在项目遇到一个谓词推入引起性能问题，使用no_push_pred Hint不能阻止其谓词推入，对此进行一个探究。
由于涉及保密，不能直接上原代码，原场景是谓词推入了一个视图，这个视图是一个在另一个视图的基础上建立，
这是不是导致no_push_pred无效的根本原因呢，先在官方文档查看了是否有关于谓词推入的限制，很遗憾并没有
找到，我们直接实验归纳下这种特殊情况。

谓词推入（Predicate Pushing）

官方文档是这样描述的：
In predicate pushing, the optimizer “pushes” the relevant predicates from the containing query block into the view query block. For views that are not merged, this technique improves the subplan of the unmerged view because the database can use the pushed-in predicates to access indexes or to use as filters.

实验

1.环境准备

--创建三个测试表
create table test1 as select * from dba_objects;
create table test2 as select * from dba_tables;
create table test3 as select * from dba_segments;
--在必要条件上建索引
create index idx_test1_n1 on test1(OBJECT_NAME);
create index idx_test2_n1 on test2(TABLE_NAME);
create index idx_test3_n1 on test3(segment_name);
--创建一个unmerged view
create OR REPLACE VIEW test_v as
SELECT t.owner, t.object_type, t.object_name, t.object_id
  FROM test1 t
 WHERE t.object_type = 'INDEX'
   AND t.owner = 'SYS'
UNION ALL
SELECT t.owner, 'TABLE', t.table_name, NULL
  FROM test2 t
 WHERE t.owner = 'SYS';
--创建以视图为基础
create or replace VIEW test_v1 as
SELECT t.* FROM test_v t;
--收集统计信息
begin
  dbms_stats.gather_schema_statS('FAN');
end;
/

2.模拟执行计划

SELECT a.*
  FROM test3 a, test_v1 b
 WHERE a.segment_name = b.object_name
   AND a.blocks = 48;
    Plan Hash Value  : 2626764003 

------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |   12 |  1836 |  185 | 00:00:03 |
|   1 |   NESTED LOOPS                   |              |   12 |  1836 |  185 | 00:00:03 |
| * 2 |    TABLE ACCESS FULL             | TEST3        |   25 |  3225 |   35 | 00:00:01 |
|   3 |    VIEW                          | TEST_V       |    1 |    24 |    6 | 00:00:01 |
|   4 |     UNION ALL PUSHED PREDICATE   |              |      |       |      |          |
| * 5 |      TABLE ACCESS BY INDEX ROWID | TEST1        |    1 |    40 |    4 | 00:00:01 |
| * 6 |       INDEX RANGE SCAN           | IDX_TEST1_N1 |    2 |       |    3 | 00:00:01 |
| * 7 |      TABLE ACCESS BY INDEX ROWID | TEST2        |    1 |    27 |    2 | 00:00:01 |
| * 8 |       INDEX RANGE SCAN           | IDX_TEST2_N1 |    1 |       |    1 | 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("A"."BLOCKS"=48)
* 5 - filter("T"."OBJECT_TYPE"='INDEX' AND "T"."OWNER"='SYS')
* 6 - access("T"."OBJECT_NAME"="A"."SEGMENT_NAME")
* 7 - filter("T"."OWNER"='SYS')
* 8 - access("T"."TABLE_NAME"="A"."SEGMENT_NAME")

分析：从执行计划来看，id4（UNION ALL PUSHED PREDICATE）进行了谓词推入。

3.使用no_push_pred能否阻止谓词推入？

SELECT/*+no_push_pred(b)*/ a.*
  FROM test3 a, test_v1 b
 WHERE a.segment_name = b.object_name
   AND a.blocks = 48;
Plan hash value: 2626764003
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |    12 |  1836 |   185   (0)| 00:00:03 |
|   1 |  NESTED LOOPS                  |              |    12 |  1836 |   185   (0)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL            | TEST3        |    25 |  3225 |    35   (0)| 00:00:01 |
|   3 |   VIEW                         | TEST_V       |     1 |    24 |     6   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE  |              |       |       |            |          |
|*  5 |     TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    40 |     4   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | IDX_TEST1_N1 |     2 |       |     3   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| TEST2        |     1 |    27 |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN          | IDX_TEST2_N1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"."BLOCKS"=48)
   5 - filter("T"."OBJECT_TYPE"='INDEX' AND "T"."OWNER"='SYS')
   6 - access("T"."OBJECT_NAME"="A"."SEGMENT_NAME")
   7 - filter("T"."OWNER"='SYS')
   8 - access("T"."TABLE_NAME"="A"."SEGMENT_NAME")

很遗憾，hint无效

分析：这个执行计划顺序是先与test_v1进行视图合并，然后谓词推入test_v。hint no_push_pred是针对 unmerged 
view，因为前面已经进行了视图合并，所以hint不生效。

4.使用use_hash或no_merge

SELECT/*+no_merge(b)*/ a.*
  FROM test3 a, test_v1 b
 WHERE a.segment_name = b.object_name
   AND a.blocks = 48;

SELECT/*+hash(b)*/ a.*
  FROM test3 a, test_v1 b
 WHERE a.segment_name = b.object_name
   AND a.blocks = 48;
   Plan hash value: 2728465010
 
-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |    40 |  7800 |   358   (1)| 00:00:05 |
|*  1 |  HASH JOIN           |        |    40 |  7800 |   358   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL  | TEST3  |    25 |  3225 |    35   (0)| 00:00:01 |
|   3 |   VIEW               | TEST_V |  2666 |   171K|   322   (1)| 00:00:04 |
|   4 |    UNION-ALL         |        |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| TEST1  |  1699 | 67960 |   291   (1)| 00:00:04 |
|*  6 |     TABLE ACCESS FULL| TEST2  |   967 | 26109 |    31   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."SEGMENT_NAME"="T"."OBJECT_NAME")
   2 - filter("A"."BLOCKS"=48)
   5 - filter("T"."OBJECT_TYPE"='INDEX' AND "T"."OWNER"='SYS')
   6 - filter("T"."OWNER"='SYS')

谓词推入消失，执行计划可控！！

分析：use_hash或no_merge阻止了test_v1视图合并，那么test_v就无法谓词推入

总结：

在关联嵌套视图情况下，要先分析最外层视图是否进行了视图合并。
视图合并：使用use_nl进行谓词推入，使用use_hash阻止谓词推入
未视图合并：使用merge/no_merge控制