在项目遇到一个谓词推入引起性能问题,使用no_push_pred Hint不能阻止其谓词推入,对此进行一个探究。 由于涉及保密,不能直接上原代码,原场景是谓词推入了一个视图,这个视图是一个在另一个视图的基础上建立, 这是不是导致no_push_pred无效的根本原因呢,先在官方文档查看了是否有关于谓词推入的限制,很遗憾并没有 找到,我们直接实验归纳下这种特殊情况。
官方文档是这样描述的:
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.
--创建三个测试表 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; /
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)进行了谓词推入。
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不生效。
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控制