首页 » ORACLE 9i-23ai » Oracle不同版本SQL执行计划差异的排查方法

Oracle不同版本SQL执行计划差异的排查方法

朋友一篇《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 —

Related Posts:

打赏

, ,

对不起,这篇文章暂时关闭评论。