朋友一篇《troubleshooting not JPPD cause View is a set query block》引起我的兴趣，想在测试环境尝试验证一下这个问题，除了统计信息外，这类问题可能是因为升级导致的或同版本的两个环境数据库执行计划不一致，可能因为DB参数不同或补丁修复相关，如何在那么多不同的配置中找它呢？在我们没有MOS或一些原厂内部资料时有没有一些小技巧? 之前有搞过一个pd_test.sql暴力尝试的方法，复现案例的过程往往有时也不易，这里从这个案例发现一些好玩的记录一下。
–环境oracle 19.3
创建测试环境
SQL> create table anbob.test as select * from dba_objects;
Table created.
SQL> create table anbob.test1 as select * from anbob.test;
Table created.
SQL> SQL> explain plan for
select t1.object_id,t1.object_name from anbob.test1 t1,
(select object_id,count(*)
from anbob.test
group by object_id
union all
select object_id,count(*)
from anbob.test
9 group by object_id) v1
10 where t1.owner='SYS' and t1.object_id(+)=v1.object_id;
Explained.
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4164 | 235K| | 1606 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 4164 | 235K| | 1606 (1)| 00:00:01 |
| 2 | JOIN FILTER CREATE | :BF0000 | 2082 | 93690 | | 386 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TEST1 | 2082 | 93690 | | 386 (1)| 00:00:01 |
| 4 | VIEW | | 145K| 1850K| | 1220 (1)| 00:00:01 |
| 5 | UNION-ALL | | | | | | |
| 6 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 7 | JOIN FILTER USE | :BF0000 | 72871 | 355K| | 386 (1)| 00:00:01 |
|* 8 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
| 9 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 10 | JOIN FILTER USE | :BF0000 | 72871 | 355K| | 386 (1)| 00:00:01 |
|* 11 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID")
3 - filter("T1"."OWNER"='SYS')
8 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"OBJECT_ID"))
11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"OBJECT_ID"))
26 rows selected.
Note:
这里出现了一个SYS_OP_BLOOM_FILTER 布隆过滤,
查看默认与bloom相关的参数
SQL> @pd bloom
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- -------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
1858 742 _instance_recovery_bloom_filter_size 83886080 Bloom filter size (in num of bits) used during claim phase
1859 743 _instance_recovery_bloom_filter_fprate 0 Allowable false positive percentage [0-100]
4017 FB1 _bloom_filter_enabled TRUE enables or disables bloom filter
4018 FB2 _bloom_filter_debug 0 debug level for bloom filtering
4019 FB3 _bloom_filter_size 0 bloom filter vector size (in KB)
4020 FB4 _bloom_predicate_enabled TRUE enables or disables bloom filter predicate pushdown
4021 FB5 _bloom_predicate_offload TRUE enables or disables bloom filter predicate offload to cells
4022 FB6 _bloom_folding_enabled TRUE Enable folding of bloom filter
4023 FB7 _bloom_folding_density 16 bloom filter folding density lower bound
4024 FB8 _bloom_folding_min 0 bloom filter folding size lower bound (in KB)
4025 FB9 _bloom_pushing_max 512 bloom filter pushing size upper bound (in KB)
4026 FBA _bloom_max_size 262144 maximum bloom filter size (in KB)
4027 FBB _bloom_pushing_total_max 262144 bloom filter combined pushing size upper bound (in KB)
4028 FBC _bloom_minmax_enabled TRUE enable or disable bloom min max filtering
4029 FBD _bloom_rm_filter FALSE remove bloom predicate in favor of zonemap join pruning predicate
4030 FBE _bloom_sm_enabled TRUE enable bloom filter optimization using slave mapping
4031 FBF _bloom_serial_filter ON enable serial bloom filter on exadata
4082 FF2 _bloom_pruning_enabled TRUE Enable partition pruning using bloom filtering
4362 110A _optimizer_inmemory_bloom_filter TRUE controls serial bloom filter for in-memory tables
4437 1155 _bloom_filter_ratio 35 bloom filter filtering ratio
4446 115E _bloom_max_wait_time 50 bloom filter wait time upper bound (in ms)
4447 115F _bloom_wait_on_rac FALSE enables bloom filter (with hash hash distribution) wait on RAC
4448 1160 _bloom_extent_size 0 bloom filter extent size in bytes
4452 1164 _bloom_pruning_setops_enabled TRUE Allow bloom pruning to be pushed through set operations
4453 1165 _bloom_filter_setops_enabled TRUE Allow bloom filter to be pushed through set operations
4464 1170 _bloom_use_shared_pool FALSE use shared pool for bloom filter
26 rows selected.
修改optimizer_features_enable为11.2.0.4
SQL> alter session set optimizer_features_enable='11.2.0.4';
Session altered.
SQL> explain plan for
select t1.object_id,t1.object_name from anbob.test1 t1,
(select object_id,count(*)
from anbob.test
group by object_id
union all
select object_id,count(*)
from anbob.test
group by object_id) v1
10 where t1.owner='SYS' and t1.object_id(+)=v1.object_id;
Explained.
Plan hash value: 96915498
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4164 | 235K| | 1606 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 4164 | 235K| | 1606 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST1 | 2082 | 93690 | | 386 (1)| 00:00:01 |
| 3 | VIEW | | 145K| 1850K| | 1220 (1)| 00:00:01 |
| 4 | UNION-ALL | | | | | | |
| 5 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
| 7 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 8 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID")
2 - filter("T1"."OWNER"='SYS')
21 rows selected.
Note:
与案例描述的一样，没有谓词推入，也没有布隆过滤后的推入。我们先还原案例现象。
对比19c与11g的参数差异
SQL> @cofep.sql 11.2.0.4 19.1.0.1
Compare Optimizer_Features_Enable Parameter differences
for values 11.2.0.4 and 19.1.0.1
PARAMETER '11.2.0.4' '19.1.0.1' DESCRIPTION
---------------------------------------- ------------------------------ ------------------------------ ----------------------------------------------------------------------
_adaptive_window_consolidator_enabled FALSE TRUE enable/disable adaptive window consolidator PX plan
_bloom_filter_ratio 30 35 bloom filter filtering ratio
_bloom_filter_setops_enabled FALSE TRUE Allow bloom filter to be pushed through set operations
_bloom_pruning_setops_enabled FALSE TRUE Allow bloom pruning to be pushed through set operations
_cell_offload_grand_total FALSE TRUE allow offload of grand total aggregations
_cell_offload_vector_groupby_fact_key FALSE TRUE enable cell offload of vector group by with fact grouping keys
_cell_offload_vector_groupby_withnojoin FALSE TRUE allow offload of vector group by without joins
_convert_set_to_join FALSE TRUE enables conversion of set operator to join
_distinct_agg_optimization_gsets OFF CHOOSE Use Distinct Aggregate Optimization for Grouping Sets
_ds_enable_view_sampling FALSE TRUE Use sampling for views in Dynamic Sampling
_ds_sampling_method NO_QUALITY_METRIC PROGRESSIVE Dynamic sampling method used
_ds_xt_split_count 0 1 Dynamic Sampling Service: split count for external tables
_gby_vector_aggregation_enabled FALSE TRUE enable group-by and aggregation using vector scheme
_hcs_enable_pred_push FALSE TRUE enable optimizer AV predicate pushing via reparse
_key_vector_create_pushdown_threshold 0 20000 minimum grouping keys for key vector create pushdown
_key_vector_double_enabled FALSE TRUE enables or disables key vector support for binary_double
_key_vector_join_pushdown_enabled FALSE TRUE enables or disables key vector join push down support
_key_vector_timestamp_enabled FALSE TRUE enables or disables key vector support for timestamp
_mv_access_compute_fresh_data OFF ON mv access compute fresh data
_optimizer_ads_use_partial_results FALSE TRUE Use partial results of ADS queries
_optimizer_ads_use_spd_cache FALSE TRUE use Sql Plan Directives for caching ADS queries
_optimizer_aggr_groupby_elim FALSE TRUE group-by and aggregation elimination
_optimizer_ansi_join_lateral_enhance FALSE TRUE optimization of left/full ansi-joins and lateral views
_optimizer_ansi_rearchitecture FALSE TRUE re-architecture of ANSI left, right, and full outer joins
_optimizer_band_join_aware FALSE TRUE enable the detection of band join by the optimizer
_optimizer_batch_table_access_by_rowid FALSE TRUE enable table access by ROWID IO batching
_optimizer_cbqt_or_expansion OFF ON enables cost based OR expansion
_optimizer_cluster_by_rowid FALSE TRUE enable/disable the cluster by rowid feature
_optimizer_cluster_by_rowid_batched FALSE TRUE enable/disable the cluster by rowid batching feature
_optimizer_cluster_by_rowid_control 3 129 internal control for cluster by rowid feature mode
_optimizer_control_shard_qry_processing 65529 65528 control shard query processing
_optimizer_cube_join_enabled FALSE TRUE enable cube join
_optimizer_eliminate_subquery FALSE TRUE consider elimination of subquery optimization
_optimizer_enable_plsql_stats FALSE TRUE Use statistics of plsql functions
_optimizer_enhanced_join_elimination FALSE TRUE Enhanced(12.2) join elimination
_optimizer_gather_stats_on_conventional_ 65535 0 settings for optimizer online stats gathering on conventional DML
config
_optimizer_gather_stats_on_conventional_ FALSE TRUE optimizer online stats gathering for conventional DML
dml
_optimizer_gather_stats_on_load FALSE TRUE enable/disable online statistics gathering
_optimizer_gather_stats_on_load_index FALSE TRUE enable/disable online index stats gathering for loads
_optimizer_hybrid_fpwj_enabled FALSE TRUE enable hybrid full partition-wise join when TRUE
_optimizer_inmemory_access_path FALSE TRUE optimizer access path costing for in-memory
_optimizer_inmemory_autodop FALSE TRUE optimizer autoDOP costing for in-memory
_optimizer_inmemory_bloom_filter FALSE TRUE controls serial bloom filter for in-memory tables
_optimizer_inmemory_cluster_aware_dop FALSE TRUE Affinitize DOP for inmemory objects
_optimizer_inmemory_gen_pushable_preds FALSE TRUE optimizer generate pushable predicates for in-memory
_optimizer_inmemory_minmax_pruning FALSE TRUE controls use of min/max pruning for costing in-memory tables
_optimizer_inmemory_table_expansion FALSE TRUE optimizer in-memory awareness for table expansion
_optimizer_inmemory_use_stored_stats NEVER AUTO optimizer use stored statistics for in-memory tables
_optimizer_interleave_or_expansion FALSE TRUE interleave OR Expansion during CBQT
_optimizer_key_vector_payload FALSE TRUE enables or disables dimension payloads in vector transform
_optimizer_key_vector_pruning_enabled FALSE TRUE enables or disables key vector partition pruning
_optimizer_multi_table_outerjoin FALSE TRUE allows multiple tables on the left of outerjoin
_optimizer_multicol_join_elimination FALSE TRUE eliminate multi-column key based joins
_optimizer_nlj_hj_adaptive_join FALSE TRUE allow adaptive NL Hash joins
_optimizer_null_accepting_semijoin FALSE TRUE enables null-accepting semijoin
_optimizer_partial_join_eval FALSE TRUE partial join evaluation parameter
_optimizer_proc_rate_level OFF BASIC control the level of processing rates
_optimizer_push_down_distinct 0 5 push down distinct from query block to table
_optimizer_quarantine_sql FALSE TRUE enable use of sql quarantine
_optimizer_reduce_groupby_key FALSE TRUE group-by key reduction
_optimizer_strans_adaptive_pruning FALSE TRUE allow adaptive pruning of star transformation bitmap trees
_optimizer_undo_cost_change 11.2.0.4 19.1.0.1 optimizer undo cost change
_optimizer_union_all_gsets FALSE TRUE Use Union All Optimization for Grouping Sets
_optimizer_unnest_scalar_sq FALSE TRUE enables unnesting of of scalar subquery
_optimizer_use_auto_indexes OFF AUTO Use Auto Index
_optimizer_use_gtt_session_stats FALSE TRUE use GTT session private statistics
_optimizer_use_stats_on_conventional_con 65535 0 settings for optimizer usage of online stats on conventional DML
fig
_optimizer_use_stats_on_conventional_dml FALSE TRUE use optimizer statistics gathered for conventional DML
_optimizer_use_table_scanrate OFF HADOOP_ONLY Use Table Specific Scan Rate
_optimizer_use_xt_rowid FALSE TRUE Use external table rowid
_optimizer_vector_base_dim_fact_factor 0 200 cost based vector transform base dimension to base fact ratio
_optimizer_vector_transformation FALSE TRUE perform vector transform
_pwise_distinct_enabled FALSE TRUE enable partition wise distinct
_px_adaptive_dist_method OFF CHOOSE determines the behavior of adaptive distribution methods
_px_concurrent FALSE TRUE enables pq with concurrent execution of serial inputs
_px_cpu_autodop_enabled FALSE TRUE enables or disables auto dop cpu computation
_px_dist_agg_partial_rollup_pushdown OFF ADAPTIVE perform distinct agg partial rollup pushdown for px execution
_px_dynamic_granules FALSE TRUE enable dynamic granule generation
_px_dynamic_granules_adjust 0 10 adjust dynamic granule regeneration
_px_external_table_default_stats FALSE TRUE the external table default stats collection enable/disable
_px_filter_parallelized FALSE TRUE enables or disables correlated filter parallelization
_px_filter_skew_handling FALSE TRUE enable correlated filter parallelization to handle skew
_px_groupby_pushdown CHOOSE FORCE perform group-by pushdown for parallel query
_px_hybrid_partition_execution_enabled FALSE TRUE enable parallel hybrid partition execution
_px_hybrid_partition_skew_threshold 255 10 partitions bigger than threshold times average size are skewed
_px_join_skew_handling FALSE TRUE enables skew handling for parallel joins
_px_join_skew_null_handling FALSE TRUE enables null skew handling improvement for parallel outer joins
_px_join_skew_sampling_time_limit 0 50 Sets execution time limit for skew handling sampling queries
_px_join_skew_use_histogram FALSE TRUE Enables retrieval of skewed values from histogram when possible
_px_nlj_bcast_rr_threshold 65535 10 threshold to use broadcast left random right distribution for NLJ
_px_object_sampling_enabled FALSE TRUE use base object sampling when possible for range distribution
_px_parallelize_expression FALSE TRUE enables or disables expression evaluation parallelization
_px_partial_rollup_pushdown OFF ADAPTIVE perform partial rollup pushdown for parallel execution
_px_partition_skew_threshold 0 80 percentage of table size considered as threshold for skew
determination
_px_pwise_wif_enabled FALSE TRUE enable parallel partition wise window function
_px_replication_enabled FALSE TRUE enables or disables replication of small table scans
_px_scalable_gby_invdist FALSE TRUE scalable PX plan for GBY w/ inverse distribution functions
_px_scalable_invdist_mcol FALSE TRUE enable/disable px plan for percentile functions on multiple columns
_px_shared_hash_join FALSE TRUE enable/disable shared hash join
_px_single_server_enabled FALSE TRUE allow single-slave dfo in parallel query
_px_wif_dfo_declumping OFF CHOOSE NDV-aware DFO clumping of multiple window sorts
_px_wif_extend_distribution_keys FALSE TRUE extend TQ data redistribution keys for window functions
_query_rewrite_use_on_query_computation FALSE TRUE query rewrite use on query computation
_recursive_with_branch_iterations 1 7 Expected number of iterations of the recurive branch of RW/CBY
_recursive_with_parallel FALSE TRUE Enable/disable parallelization of Recursive With
_sqlexec_hash_based_distagg_ssf_enabled FALSE TRUE enable hash based distinct aggregation for single set gby queries
_sqlexec_pwiseops_with_binds_enabled FALSE TRUE enable partition wise execution in the presence of bind variables in
inlist equality operator in where clause
_sqlexec_pwiseops_with_sqlfuncs_enabled FALSE TRUE enables partition wise operations even in the presence of functions
over table partition keys
_sqlexec_reorder_wif_enabled FALSE TRUE enable re-ordering of window functions
_subquery_pruning_mv_enabled FALSE TRUE enable the use of subquery predicates with MVs to perform pruning
_vector_encoding_mode OFF MANUAL enable vector encoding(OFF/MANUAL/AUTO)
_xt_sampling_scan_granules OFF ON Granule Sampling for Block Sampling of External Tables
optimizer_features_enable 11.2.0.4 19.1.0.1 optimizer plan compatibility parameter
113 rows selected.
19c禁用”_bloom_filter_setops_enabled”
SQL> alter session set "_bloom_filter_setops_enabled"=false;
Session altered.
SQL> explain plan for
select t1.object_id,t1.object_name from anbob.test1 t1,
(select object_id,count(*)
from anbob.test
group by object_id
union all
select object_id,count(*)
from anbob.test
group by object_id) v1
10 where t1.owner='SYS' and t1.object_id(+)=v1.object_id;
Explained.
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4164 | 235K| | 1606 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 4164 | 235K| | 1606 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST1 | 2082 | 93690 | | 386 (1)| 00:00:01 |
| 3 | VIEW | | 145K| 1850K| | 1220 (1)| 00:00:01 |
| 4 | UNION-ALL | | | | | | |
| 5 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
| 7 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 8 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID")
2 - filter("T1"."OWNER"='SYS')
21 rows selected.
NOTE：
这里没有推入，或许是因为cost问题. 案例中内部是有索引的。
SQL> create index anbob.idx_test_oid on anbob.test(object_id);
Index created.
SQL> alter session set "_bloom_filter_setops_enabled"=false;
Session altered.
SQL> explain plan for
2 select t1.object_id,t1.object_name from anbob.test1 t1,
(select object_id,count(*)
from anbob.test
group by object_id
union all
select object_id,count(*)
from anbob.test
group by object_id) v1
where t1.owner='SYS' and t1.object_id(+)=v1.object_id; 3 4 5 6 7 8 9 10
Explained.
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4164 | 235K| | 1606 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 4164 | 235K| | 1606 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST1 | 2082 | 93690 | | 386 (1)| 00:00:01 |
| 3 | VIEW | | 145K| 1850K| | 1220 (1)| 00:00:01 |
| 4 | UNION-ALL | | | | | | |
| 5 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
| 7 | HASH GROUP BY | | 72870 | 355K| 872K| 610 (1)| 00:00:01 |
| 8 | TABLE ACCESS FULL| TEST | 72871 | 355K| | 386 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID")
2 - filter("T1"."OWNER"='SYS')
Note：
why not use index? 这个原因比如列无not null
OBJECT_ID 为空的对象
SQL> alter table anbob.test modify object_id not null; alter table anbob.test modify object_id not null * ERROR at line 1: ORA-02296: cannot enable (ANBOB.) - null values found SQL> delete anbob.test where object_id is null; 1 row deleted. SQL> alter table anbob.test modify object_id not null; Table altered. SQL> select OWNER , OBJECT_NAME ,OBJECT_TYPE from anbob.test1 where object_id is null; OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ------------------------------ ---------- -------------- ----------------------- SYS SYS_HUB DATABASE LINK SQL> delete anbob.test1 where object_id is null; 1 row deleted.
Note:
在oracle中对象没有object_id以前没有注意，不过sys_hub老早倒知道就是内部通信的dblink。
什么是sys_hub
What Is The Use Of DataBase Link SYS.SYS_HUB And Can It Be Deleted (Doc ID 2413797.1)记载
sys_hub是12c中引入的用于RW与RO（read only）instance内部使用, SYS_HUB DBLINK 用于将只读实例上发出的 dml 操作路由到可以实际执行的读写实例。与是否使用CDB无关.
如果不使用RW（读写（RW）实例是可以进行读写操作的实例。选择/插入/更新/删除）/RO（Read Only实例只允许读操作。选择）实例配置，可以删除SYS_HUB，无副作用.data
关于READ ONLY实例在几年前我的另一篇BLOG 《Oracle 12c R2 – 19C Instance_mode read-only（不是雪中须送炭，聊装风景要诗来。）》
SQL> @dblinks
OWNER DB_LINK USERNAME HOST CREATED
-------------------- ---------------------------------------- -------------------- ---------------------------------------- ---------
SYS SYS_HUB SEEDDATA 17-APR-19
-- drop sys_hub
SQL> drop database link sys_hub;
Database link dropped.
SQL> select * from containers(t1001);
ID CON_ID
---------- ----------
2 4
-- recreate sys_hub
SQL> execute dbms_pq_internal.create_db_link_for_hub ;
PL/SQL procedure successfully completed.
SQL> @dblinks
OWNER DB_LINK USERNAME HOST CREATED
-------------------- ---------------------------------------- -------------------- ---------------------------------------- ---------
SYS SYS_HUB ANBOB 30-DEC-23
Note:
sys_hub如果没有使用read only instance RAC，可以删掉，也不会影响containers函数使用，可以使用dbms_pq_internal重建该link.
19c
SQL> alter session set "_bloom_filter_setops_enabled"=false;
Session altered.
SQL> exec dbms_stats.gather_table_stats('anbob','test1');
PL/SQL procedure successfully completed.
SQL> explain plan for
select t1.object_id,t1.object_name from anbob.test1 t1,
(select object_id,count(*)
from anbob.test
group by object_id
union all
select object_id,count(*)
from anbob.test
group by object_id) v1
10 where t1.owner='ANBOB' and t1.object_id(+)=v1.object_id;
Explained.
Plan hash value: 1405068673
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 64 | 3200 | 450 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 64 | 3200 | 450 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST1 | 32 | 1440 | 386 (1)| 00:00:01 |
| 3 | VIEW | | 1 | 5 | 2 (0)| 00:00:01 |
| 4 | UNION ALL PUSHED PREDICATE | | | | | |
| 5 | SORT GROUP BY | | 1 | 5 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_TEST_OID | 1 | 5 | 1 (0)| 00:00:01 |
| 7 | SORT GROUP BY | | 1 | 5 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_TEST_OID | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."OWNER"='ANBOB')
6 - access("OBJECT_ID"="T1"."OBJECT_ID")
8 - access("OBJECT_ID"="T1"."OBJECT_ID")
22 rows selected.
Note:
现在得到了那个19c中高效的执行计划。 #6 #8显示确实有谓词关联条件的推入.
10053 对比
Registered qb: SEL$33B88E6B 0xa2425a30 (OUTER-JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "T1"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$33B88E6B nbfros=2 flg=0
fro(0): flg=0 objn=81235 hint_alias="T1"@"SEL$1"
fro(1): flg=1 objn=0 hint_alias="V1"@"SEL$1"
# 11g
JPPD: Considering Cost-based predicate pushdown from query block SEL$33B88E6B (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$33B88E6B (#1)
kkqctdrvTD-start: :
call(in-use=6472, alloc=16344), compile(in-use=186608, alloc=218488), execution(in-use=253600, alloc=256912)
Check Basic Validity for Non-Union View for query block SET$1 (#2)
JPPD: JPPD bypassed: View is a set query block.
OJPPD: OJPPD bypassed: View contains a group by.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=6472, alloc=16344), compile(in-use=187448, alloc=218488), execution(in-use=253600, alloc=256912)
kkqctdrvTD-end:
call(in-use=6472, alloc=16344), compile(in-use=187968, alloc=218488), execution(in-use=253600, alloc=256912)
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$33B88E6B (#1)
JPPD: No valid views found to push predicate into.
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 711 | |
| 1 | HASH JOIN | | 64 | 3712 | 711 | 00:00:09 |
| 2 | TABLE ACCESS FULL | TEST1 | 32 | 1440 | 386 | 00:00:05 |
| 3 | VIEW | | 142K | 1850K | 325 | 00:00:04 |
| 4 | UNION-ALL | | | | | |
| 5 | HASH GROUP BY | | 71K | 356K | 163 | 00:00:02 |
| 6 | INDEX FULL SCAN | IDX_TEST_OID| 71K | 356K | 163 | 00:00:02 |
| 7 | HASH GROUP BY | | 71K | 356K | 163 | 00:00:02 |
| 8 | INDEX FULL SCAN | IDX_TEST_OID| 71K | 356K | 163 | 00:00:02 |
-------------------------------------------+-----------------------------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$33B88E6B
2 - SEL$33B88E6B / "T1"@"SEL$1"
3 - SET$1 / "V1"@"SEL$1"
4 - SET$1
5 - SEL$2
6 - SEL$2 / "TEST"@"SEL$2"
7 - SEL$3
8 - SEL$3 / "TEST"@"SEL$3"
------------------------------------------------------------
Predicate Information:
----------------------
1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID")
2 - filter("T1"."OWNER"='ANBOB')
# 19C
JPPD: Considering Cost-based predicate pushdown from query block SEL$33B88E6B (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$33B88E6B (#1)
kkqctdrvTD-start: :
call(in-use=6968, alloc=16344), compile(in-use=234776, alloc=266656), execution(in-use=302248, alloc=305560)
Check Basic Validity for Non-Union View for query block SET$1 (#2)
JPPD: JPPD bypassed: View is a set query block.
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$33B88E6B (#1)
JPPD: Checking validity of push-down from query block SEL$33B88E6B (#1) to query block SET$1 (#2)
JPPD: Passed validity checks
JPPD: JPPD: Pushdown from query block SEL$33B88E6B (#1) passed validity checks.
Join-Predicate push-down on query block SEL$33B88E6B (#1)
JPPD: Using search type: linear
JPPD: Considering join predicate push-down
JPPD: Starting iteration 1, state space = (2) : (0)
JPPD: Performing join predicate push-down (no transformation phase) from query block SEL$33B88E6B (#1) to query block SET$1 (#2)
============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 450 | |
| 1 | NESTED LOOPS | | 64 | 3200 | 450 | 00:00:06 |
| 2 | TABLE ACCESS FULL | TEST1 | 32 | 1440 | 386 | 00:00:05 |
| 3 | VIEW | | 1 | 5 | 2 | 00:00:01 |
| 4 | UNION ALL PUSHED PREDICATE | | | | | |
| 5 | SORT GROUP BY | | 1 | 5 | 1 | 00:00:01 |
| 6 | INDEX RANGE SCAN | IDX_TEST_OID| 1 | 5 | 1 | 00:00:01 |
| 7 | SORT GROUP BY | | 1 | 5 | 1 | 00:00:01 |
| 8 | INDEX RANGE SCAN | IDX_TEST_OID| 1 | 5 | 1 | 00:00:01 |
-----------------------------------------------------+-----------------------------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$33B88E6B
2 - SEL$33B88E6B / "T1"@"SEL$1"
3 - SET$5715CE2E / "V1"@"SEL$1"
4 - SET$5715CE2E
5 - SEL$639F1A6F
6 - SEL$639F1A6F / "TEST"@"SEL$2"
7 - SEL$B01C6807
8 - SEL$B01C6807 / "TEST"@"SEL$3"
------------------------------------------------------------
Predicate Information:
----------------------
2 - filter("T1"."OWNER"='ANBOB')
6 - access("OBJECT_ID"="T1"."OBJECT_ID")
8 - access("OBJECT_ID"="T1"."OBJECT_ID")
查找bugfix对比
1，数据参数
2，补丁修复
参数我们上面有列表可以确认没有JPPD相关不同的参数，下面我们确认补丁
SQL> @fix_control "jppd%group" %
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_ EVENT IS_DEFAULT CON_ID
--------- ----- ----------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
21099502 1 QKSFM_JPPD_21099502 Enable extended JPPD for UNION[ALL] views having group by 12.2.0.1 0 1 1
SQL> l
1 select *
2 from v$system_fix_control
3 where( lower(description) like lower('%&1%')
4 or EVENT like '&1'
5 or bugno like '&1'
6 or lower(SQL_FEATURE ) like lower('&1')
7 or OPTIMIZER_FEATURE_ENABLE like lower('&1'))
8 AND nvl(optimizer_feature_enable,'null') = DECODE('&2', '%', nvl(optimizer_feature_enable,'null'), '&2')
9 order by
10* OPTIMIZER_FEATURE_ENABLE, bugno
SQL>
SQL> show parameter optimizer_features
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
optimizer_features_enable string 19.1.0
SQL> alter session set "_bloom_filter_setops_enabled"=false;
Session altered.
SQL> alter session set "_fix_control"='21099502:OFF';
Session altered.
SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 614710582
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 64 | 3712 | | 711 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 64 | 3712 | | 711 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST1 | 32 | 1440 | | 386 (1)| 00:00:01 |
| 3 | VIEW | | 145K| 1850K| | 325 (1)| 00:00:01 |
| 4 | UNION-ALL | | | | | | |
| 5 | HASH GROUP BY | | 72870 | 355K| 872K| 163 (1)| 00:00:01 |
| 6 | INDEX FULL SCAN| IDX_TEST_OID | 72871 | 355K| | 163 (1)| 00:00:01 |
| 7 | HASH GROUP BY | | 72870 | 355K| 872K| 163 (1)| 00:00:01 |
| 8 | INDEX FULL SCAN| IDX_TEST_OID | 72871 | 355K| | 163 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID")
2 - filter("T1"."OWNER"='ANBOB')
Note:
通过禁用19c的bug 修复，复现了问题，确认是该bug.
MOS描述
Bug 21099502 – Join Predicates not pushed into UNION ALL view having group by and aggregates (Doc ID 21099502.8)
| Range of versions believed to be affected | Versions BELOW 12.2 |
| Versions confirmed as being affected |
Summary:
当两个版本或环境出现执行计划不一致时，如果数据和统计信息相同，不该先确认一下两个版本相关的参数差异，然后再确认之间的版本BUG 修复，也可以尝试使用遍历所有参数的方法检查执行计划的变化。
— OVER —