Transformation之JE（join Elimination）合并同类项【二】

  • 哎呀我的天呐

    2014-11-07 13:20:21
  • Oracle
  • 原创

10053只有在硬解析的时候生成。 

set line 200
set pages 200
@10053.sql 
select /* Sample 2^^*/ a.ename from emp a, dept b
where (a.deptno = b.deptno
and a.sal>1500
and b.dname='SALES')
or(
a.deptno = b.deptno
and a.sal> 1500
and b.dname = 'RESEARCH'
);
select * from table(dbms_xplan.display_cursor(null,null,'last'));
@10053d.sql

oracle会把以上sql transformation成


select /* Sample2^^*/ a .ename from emp a , dept b

where a .deptno = b .deptno

and a . sal > 1500

and (b .dname = 'SALES' or b .dname = 'RESEARCH' ) ;

10053


  1. Trace file /u01 /app /oracle /diag /rdbms /prod /prod1 /trace /prod1_ora_4890 .trc

  2. Oracle Database 11g Enterprise Edition Release 11 .2 . .1 .0 - Production

  3. With the Partitioning , Real Application Clusters , Automatic Storage Management , OLAP ,

  4. Data Mining and Real Application Testing options

  5. ORACLE_HOME = /u01 /app /oracle /product /11 .2 . /db_1

  6. System name :    Linux

  7. Node name :    node1

  8. Release :    2 .6 .18 -194 .el5

  9. Version :    #1 SMP Tue Mar 16 21 :52 :43 EDT 2010

  10. Machine :    i686

  11. Instance name : prod1

  12. Redo thread mounted by this instance : 1

  13. Oracle process number : 33

  14. Unix process pid : 4890 , image : oracle @node1 (TNS V1 -V3 )



  17. * * * 2014 -11 -01 05 :01 :29 .664

  18. * * * SESSION ID : (59 .615 ) 2014 -11 -01 05 :01 :29 .664

  19. * * * CLIENT ID : ( ) 2014 -11 -01 05 :01 :29 .664

  20. * * * SERVICE NAME : (SYS$USERS ) 2014 -11 -01 05 :01 :29 .664

  21. * * * MODULE NAME : (SQL *Plus ) 2014 -11 -01 05 :01 :29 .664

  22. * * * ACTION NAME : ( ) 2014 -11 -01 05 :01 :29 .664

  23.  

  24. Registered qb : SEL$1 0x883604 (PARSER )

  25. ---------------------

  26. QUERY BLOCK SIGNATURE

  27. ---------------------

  28.   signature ( ) : qb_name =SEL$1 nbfros =2 flg =

  29.     fro ( ) : flg =4 objn =73181 hint_alias = "A" @ "SEL$1"

  30.     fro (1 ) : flg =4 objn =73179 hint_alias = "B" @ "SEL$1"


  32. SPM : statement not found in SMB


  34. * * * * * * * * * * * * * * * * * * * * * * * * * *

  35. Automatic degree of parallelism (ADOP )

  36. * * * * * * * * * * * * * * * * * * * * * * * * * *

  37. Automatic degree of parallelism is disabled : Parameter .


  39. PM : Considering predicate move -around in query block SEL$1 ( # )

  40. * * * * * * * * * * * * * * * * * * * * * * * * * *

  41. Predicate Move -Around (PM )

  42. * * * * * * * * * * * * * * * * * * * * * * * * * *

  43. OPTIMIZER INFORMATION


  45. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  46. ----- Current SQL Statement for this session (sql_id=5z7wqfry426ch) -----

  47. select /* Sample 2^^*/ a .ename from emp a , dept b

  48. where (a .deptno = b .deptno

  49. and a .sal >1500

  50. and b .dname = 'SALES' )

  51. or (

  52. a .deptno = b .deptno

  53. and a .sal > 1500

  54. and b .dname = 'RESEARCH'

  55. )

  56. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  57. Legend

  58. The following abbreviations are used by optimizer trace .

  59. CBQT - cost -based query transformation

  60. JPPD - join predicate push -down

  61. OJPPD - old -style (non -cost -based ) JPPD

  62. FPD - filter push -down

  63. PM - predicate move -around

  64. CVM - complex view merging

  65. SPJ - select -project -join

  66. SJC - set join conversion

  67. SU - subquery unnesting

  68. OBYE - order by elimination

  69. OST - old style star transformation

  70. ST - new (cbqt ) star transformation

  71. CNT - count ( col ) to count ( * ) transformation

  72. JE - Join Elimination

  73. JF - join factorization

  74. SLP - select list pruning

  75. DP - distinct placement

  76. qb - query block

  77. LB - leaf blocks

  78. DK - distinct keys

  79. LB /K - average number of leaf blocks per key

  80. DB /K - average number of data blocks per key

  81. CLUF - clustering factor

  82. NDV - number of distinct values

  83. Resp - response cost

  84. Card - cardinality

  85. Resc - resource cost

  86. NL - nested loops (join )

  87. SM - sort merge (join )

  88. HA - hash (join )

  89. CPUSPEED - CPU Speed

  90. IOTFRSPEED - I /O transfer speed

  91. IOSEEKTIM - I /O seek time

  92. SREADTIM - average single block read time

  93. MREADTIM - average multiblock read time

  94. MBRC - average multiblock read count

  95. MAXTHR - maximum I /O system throughput

  96. SLAVETHR - average slave I /O throughput

  97. dmeth - distribution method

  98.   1 : no partitioning required

  99.   2 : value partitioned

  100.   4 : right is random ( round -robin )

  101.   128 : left is random ( round -robin )

  102.   8 : broadcast right and partition left

  103.   16 : broadcast left and partition right

  104.   32 : partition left using partitioning of right

  105.   64 : partition right using partitioning of left

  106.   256 : run the join in serial

  107.   0 : invalid distribution method

  108. sel - selectivity

  109. ptn - partition

  110. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  111. PARAMETERS USED BY THE OPTIMIZER

  112. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  113.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  114.   PARAMETERS WITH ALTERED VALUES

  115.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  116. Compilation Environment Dump

  117. parallel_max_degree = 4

  118. Bug Fix Control Environment



  121.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  122.   PARAMETERS WITH DEFAULT VALUES

  123.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  124. Compilation Environment Dump

  125. optimizer_mode_hinted = false

  126. optimizer_features_hinted = 0 . .

  127. parallel_execution_enabled = true

  128. parallel_query_forced_dop = 0

  129. parallel_dml_forced_dop = 0

  130. parallel_ddl_forced_degree = 0

  131. parallel_ddl_forced_instances = 0

  132. _query_rewrite_fudge = 90

  133. optimizer_features_enable = 11 .2 . .1

  134. _optimizer_search_limit = 5

  135. cpu_count = 1

  136. active_instance_count = 2

  137. parallel_threads_per_cpu = 2

  138. hash_area_size = 131072

  139. bitmap_merge_area_size = 1048576

  140. sort_area_size = 65536

  141. sort_area_retained_size = 0

  142. _sort_elimination_cost_ratio = 0

  143. _optimizer_block_size = 8192

  144. _sort_multiblock_read_count = 2

  145. _hash_multiblock_io_count = 0

  146. _db_file_optimizer_read_count = 8

  147. _optimizer_max_permutations = 2000

  148. pga_aggregate_target = 339968 KB

  149. _pga_max_size = 204800 KB

  150. _query_rewrite_maxdisjunct = 257

  151. _smm_auto_min_io_size = 56 KB

  152. _smm_auto_max_io_size = 248 KB

  153. _smm_min_size = 339 KB

  154. _smm_max_size = 67993 KB

  155. _smm_px_max_size = 169984 KB

  156. _cpu_to_io = 0

  157. _optimizer_undo_cost_change = 11 .2 . .1

  158. parallel_query_mode = enabled

  159. parallel_dml_mode = disabled

  160. parallel_ddl_mode = enabled

  161. optimizer_mode = all_rows

  162. sqlstat_enabled = false

  163. _optimizer_percent_parallel = 101

  164. _always_anti_join = choose

  165. _always_semi_join = choose

  166. _optimizer_mode_force = true

  167. _partition_view_enabled = true

  168. _always_star_transformation = false

  169. _query_rewrite_or_error = false

  170. _hash_join_enabled = true

  171. cursor_sharing = exact

  172. _b_tree_bitmap_plans = true

  173. star_transformation_enabled = false

  174. _optimizer_cost_model = choose

  175. _new_sort_cost_estimate = true

  176. _complex_view_merging = true

  177. _unnest_subquery = true

  178. _eliminate_common_subexpr = true

  179. _pred_move_around = true

  180. _convert_set_to_join = false

  181. _push_join_predicate = true

  182. _push_join_union_view = true

  183. _fast_full_scan_enabled = true

  184. _optim_enhance_nnull_detection = true

  185. _parallel_broadcast_enabled = true

  186. _px_broadcast_fudge_factor = 100

  187. _ordered_nested_loop = true

  188. _no_or_expansion = false

  189. optimizer_index_cost_adj = 100

  190. optimizer_index_caching = 0

  191. _system_index_caching = 0

  192. _disable_datalayer_sampling = false

  193. query_rewrite_enabled = true

  194. query_rewrite_integrity = enforced

  195. _query_cost_rewrite = true

  196. _query_rewrite_2 = true

  197. _query_rewrite_1 = true

  198. _query_rewrite_expression = true

  199. _query_rewrite_jgmigrate = true

  200. _query_rewrite_fpc = true

  201. _query_rewrite_drj = true

  202. _full_pwise_join_enabled = true

  203. _partial_pwise_join_enabled = true

  204. _left_nested_loops_random = true

  205. _improved_row_length_enabled = true

  206. _index_join_enabled = true

  207. _enable_type_dep_selectivity = true

  208. _improved_outerjoin_card = true

  209. _optimizer_adjust_for_nulls = true

  210. _optimizer_degree = 0

  211. _use_column_stats_for_function = true

  212. _subquery_pruning_enabled = true

  213. _subquery_pruning_mv_enabled = false

  214. _or_expand_nvl_predicate = true

  215. _like_with_bind_as_equality = false

  216. _table_scan_cost_plus_one = true

  217. _cost_equality_semi_join = true

  218. _default_non_equality_sel_check = true

  219. _new_initial_join_orders = true

  220. _oneside_colstat_for_equijoins = true

  221. _optim_peek_user_binds = true

  222. _minimal_stats_aggregation = true

  223. _force_temptables_for_gsets = false

  224. workarea_size_policy = auto

  225. _smm_auto_cost_enabled = true

  226. _gs_anti_semi_join_allowed = true

  227. _optim_new_default_join_sel = true

  228. optimizer_dynamic_sampling = 2

  229. _pre_rewrite_push_pred = true

  230. _optimizer_new_join_card_computation = true

  231. _union_rewrite_for_gs = yes_gset_mvs

  232. _generalized_pruning_enabled = true

  233. _optim_adjust_for_part_skews = true

  234. _force_datefold_trunc = false

  235. statistics_level = typical

  236. _optimizer_system_stats_usage = true

  237. skip_unusable_indexes = true

  238. _remove_aggr_subquery = true

  239. _optimizer_push_down_distinct = 0

  240. _dml_monitoring_enabled = true

  241. _optimizer_undo_changes = false

  242. _predicate_elimination_enabled = true

  243. _nested_loop_fudge = 100

  244. _project_view_columns = true

  245. _local_communication_costing_enabled = true

  246. _local_communication_ratio = 50

  247. _query_rewrite_vop_cleanup = true

  248. _slave_mapping_enabled = true

  249. _optimizer_cost_based_transformation = linear

  250. _optimizer_mjc_enabled = true

  251. _right_outer_hash_enable = true

  252. _spr_push_pred_refspr = true

  253. _optimizer_cache_stats = false

  254. _optimizer_cbqt_factor = 50

  255. _optimizer_squ_bottomup = true

  256. _fic_area_size = 131072

  257. _optimizer_skip_scan_enabled = true

  258. _optimizer_cost_filter_pred = false

  259. _optimizer_sortmerge_join_enabled = true

  260. _optimizer_join_sel_sanity_check = true

  261. _mmv_query_rewrite_enabled = true

  262. _bt_mmv_query_rewrite_enabled = true

  263. _add_stale_mv_to_dependency_list = true

  264. _distinct_view_unnesting = false

  265. _optimizer_dim_subq_join_sel = true

  266. _optimizer_disable_strans_sanity_checks = 0

  267. _optimizer_compute_index_stats = true

  268. _push_join_union_view2 = true

  269. _optimizer_ignore_hints = false

  270. _optimizer_random_plan = 0

  271. _query_rewrite_setopgrw_enable = true

  272. _optimizer_correct_sq_selectivity = true

  273. _disable_function_based_index = false

  274. _optimizer_join_order_control = 3

  275. _optimizer_cartesian_enabled = true

  276. _optimizer_starplan_enabled = true

  277. _extended_pruning_enabled = true

  278. _optimizer_push_pred_cost_based = true

  279. _optimizer_null_aware_antijoin = true

  280. _optimizer_extend_jppd_view_types = true

  281. _sql_model_unfold_forloops = run_time

  282. _enable_dml_lock_escalation = false

  283. _bloom_filter_enabled = true

  284. _update_bji_ipdml_enabled = 0

  285. _optimizer_extended_cursor_sharing = udo

  286. _dm_max_shared_pool_pct = 1

  287. _optimizer_cost_hjsmj_multimatch = true

  288. _optimizer_transitivity_retain = true

  289. _px_pwg_enabled = true

  290. optimizer_secure_view_merging = true

  291. _optimizer_join_elimination_enabled = true

  292. flashback_table_rpi = non_fbt

  293. _optimizer_cbqt_no_size_restriction = true

  294. _optimizer_enhanced_filter_push = true

  295. _optimizer_filter_pred_pullup = true

  296. _rowsrc_trace_level = 0

  297. _simple_view_merging = true

  298. _optimizer_rownum_pred_based_fkr = true

  299. _optimizer_better_inlist_costing = all

  300. _optimizer_self_induced_cache_cost = false

  301. _optimizer_min_cache_blocks = 10

  302. _optimizer_or_expansion = depth

  303. _optimizer_order_by_elimination_enabled = true

  304. _optimizer_outer_to_anti_enabled = true

  305. _selfjoin_mv_duplicates = true

  306. _dimension_skip_null = true

  307. _force_rewrite_enable = false

  308. _optimizer_star_tran_in_with_clause = true

  309. _optimizer_complex_pred_selectivity = true

  310. _optimizer_connect_by_cost_based = true

  311. _gby_hash_aggregation_enabled = true

  312. _globalindex_pnum_filter_enabled = true

  313. _px_minus_intersect = true

  314. _fix_control_key = 0

  315. _force_slave_mapping_intra_part_loads = false

  316. _force_tmp_segment_loads = false

  317. _query_mmvrewrite_maxpreds = 10

  318. _query_mmvrewrite_maxintervals = 5

  319. _query_mmvrewrite_maxinlists = 5

  320. _query_mmvrewrite_maxdmaps = 10

  321. _query_mmvrewrite_maxcmaps = 20

  322. _query_mmvrewrite_maxregperm = 512

  323. _query_mmvrewrite_maxmergedcmaps = 50

  324. _query_mmvrewrite_maxqryinlistvals = 500

  325. _disable_parallel_conventional_load = false

  326. _trace_virtual_columns = false

  327. _replace_virtual_columns = true

  328. _virtual_column_overload_allowed = true

  329. _kdt_buffering = true

  330. _first_k_rows_dynamic_proration = true

  331. _optimizer_sortmerge_join_inequality = true

  332. _optimizer_aw_stats_enabled = true

  333. _bloom_pruning_enabled = true

  334. result_cache_mode = MANUAL

  335. _px_ual_serial_input = true

  336. _optimizer_skip_scan_guess = false

  337. _enable_row_shipping = true

  338. _row_shipping_threshold = 80

  339. _row_shipping_explain = false

  340. transaction_isolation_level = read_commited

  341. _optimizer_distinct_elimination = true

  342. _optimizer_multi_level_push_pred = true

  343. _optimizer_group_by_placement = true

  344. _optimizer_rownum_bind_default = 10

  345. _enable_query_rewrite_on_remote_objs = true

  346. _optimizer_extended_cursor_sharing_rel = simple

  347. _optimizer_adaptive_cursor_sharing = true

  348. _direct_path_insert_features = 0

  349. _optimizer_improve_selectivity = true

  350. optimizer_use_pending_statistics = false

  351. _optimizer_enable_density_improvements = true

  352. _optimizer_aw_join_push_enabled = true

  353. _optimizer_connect_by_combine_sw = true

  354. _enable_pmo_ctas = 0

  355. _optimizer_native_full_outer_join = force

  356. _bloom_predicate_enabled = true

  357. _optimizer_enable_extended_stats = true

  358. _is_lock_table_for_ddl_wait_lock = 0

  359. _pivot_implementation_method = choose

  360. optimizer_capture_sql_plan_baselines = false

  361. optimizer_use_sql_plan_baselines = true

  362. _optimizer_star_trans_min_cost = 0

  363. _optimizer_star_trans_min_ratio = 0

  364. _with_subquery = OPTIMIZER

  365. _optimizer_fkr_index_cost_bias = 10

  366. _optimizer_use_subheap = true

  367. parallel_degree_policy = manual

  368. parallel_degree = 0

  369. parallel_min_time_threshold = 10

  370. _parallel_time_unit = 10

  371. _optimizer_or_expansion_subheap = true

  372. _optimizer_free_transformation_heap = true

  373. _optimizer_reuse_cost_annotations = true

  374. _result_cache_auto_size_threshold = 100

  375. _result_cache_auto_time_threshold = 1000

  376. _optimizer_nested_rollup_for_gset = 100

  377. _nlj_batching_enabled = 1

  378. parallel_query_default_dop = 0

  379. is_recur_flags = 0

  380. optimizer_use_invisible_indexes = false

  381. flashback_data_archive_internal_cursor = 0

  382. _optimizer_extended_stats_usage_control = 224

  383. _parallel_syspls_obey_force = true

  384. cell_offload_processing = true

  385. _rdbms_internal_fplib_enabled = false

  386. db_file_multiblock_read_count = 79

  387. _bloom_folding_enabled = true

  388. _mv_generalized_oj_refresh_opt = true

  389. cell_offload_compaction = ADAPTIVE

  390. parallel_degree_limit = 65535

  391. parallel_force_local = false

  392. total_cpu_count = 2

  393. cell_offload_plan_display = AUTO

  394. _optimizer_coalesce_subqueries = true

  395. _optimizer_fast_pred_transitivity = true

  396. _optimizer_fast_access_pred_analysis = true

  397. _optimizer_unnest_disjunctive_subq = true

  398. _optimizer_unnest_corr_set_subq = true

  399. _optimizer_distinct_agg_transform = true

  400. _aggregation_optimization_settings = 0

  401. _optimizer_connect_by_elim_dups = true

  402. _optimizer_eliminate_filtering_join = true

  403. _connect_by_use_union_all = true

  404. dst_upgrade_insert_conv = true

  405. advanced_queuing_internal_cursor = 0

  406. _optimizer_unnest_all_subqueries = true

  407. _bloom_predicate_pushdown_to_storage = true

  408. _bloom_vector_elements = 0

  409. _bloom_pushing_max = 524288

  410. parallel_autodop = 0

  411. parallel_ddldml = 0

  412. _parallel_cluster_cache_policy = adaptive

  413. _parallel_scalability = 50

  414. iot_internal_cursor = 0

  415. _optimizer_instance_count = 0

  416. _optimizer_connect_by_cb_whr_only = false

  417. _suppress_scn_chk_for_cqn = nosuppress_1466

  418. _optimizer_join_factorization = true

  419. _optimizer_use_cbqt_star_transformation = true

  420. _optimizer_table_expansion = true

  421. _and_pruning_enabled = true

  422. _deferred_constant_folding_mode = DEFAULT

  423. _optimizer_distinct_placement = true

  424. partition_pruning_internal_cursor = 0

  425. parallel_hinted = none

  426. _sql_compatibility = 0

  427. _optimizer_use_feedback = true

  428. _optimizer_try_st_before_jppd = true

  429. Bug Fix Control Environment

  430.     fix  3834770 = 1

  431.     fix  3746511 = enabled


  433.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  434.   PARAMETERS IN OPT_PARAM HINT

  435.    * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  436. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  437. Column Usage Monitoring is ON : tracking level = 1

  438. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


  440. Considering Query Transformations on query block SEL$1 ( # )

  441. * * * * * * * * * * * * * * * * * * * * * * * * * *

  442. Query transformations ( QT )

  443. * * * * * * * * * * * * * * * * * * * * * * * * * *

  444. JF : Checking validity of join factorization for query block SEL$1 ( # )     ---没什么可做的

  445. JF : Bypassed : not a UNION or UNION - ALL query block .

  446. ST : not valid since star transformation parameter is FALSE

  447. TE : Checking validity of table expansion for query block SEL$1 ( # )

  448. TE : Bypassed : No partitioned table in query block .

  449. CBQT bypassed for query block SEL$1 ( # ) : no complex view , sub -queries or UNION ( ALL ) queries .

  450. CBQT : Validity checks failed for 5z7wqfry426ch .

  451. CSE : Considering common sub -expression elimination in query block SEL$1 ( # )

  452. * * * * * * * * * * * * * * * * * * * * * * * * *

  453. Common Subexpression elimination (CSE )

  454. * * * * * * * * * * * * * * * * * * * * * * * * *

  455. CSE : CSE performed on query block SEL$1 ( # ) .

  456. OBYE : Considering Order - by Elimination from view SEL$1 ( # )

  457. * * * * * * * * * * * * * * * * * * * * * * * * * * *

  458. Order - by elimination (OBYE )

  459. * * * * * * * * * * * * * * * * * * * * * * * * * * *

  460. OBYE : OBYE bypassed : no order by to eliminate .

  461. JE : Considering Join Elimination on query block SEL$1 ( # )

  462. * * * * * * * * * * * * * * * * * * * * * * * * *

  463. Join Elimination (JE )

  464. * * * * * * * * * * * * * * * * * * * * * * * * *

  465. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *

  466. SELECT A . ENAME  ENAME  FROM SCOTT . EMP  A , SCOTT . DEPT  B  WHERE A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = SALES  OR B . DNAME = 'RESEARCH' )

  467. JE : cfro : EMP objn :73179 col # :8 dfro :DEPT dcol# :8

  468. JE : cfro : EMP objn :73179 col # :8 dfro :DEPT dcol# :8

  469. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *

  470. SELECT A . ENAME  ENAME  FROM SCOTT . EMP  A , SCOTT . DEPT  B  WHERE A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = 'SALES' OR B . DNAME = 'RESEARCH' )

  471. Query block SEL$1 ( # ) unchanged

  472. CVM : Considering view merge in query block SEL$1 ( # )

  473. JE : Considering Join Elimination on query block SEL$1 ( # )

  474. * * * * * * * * * * * * * * * * * * * * * * * * *

  475. Join Elimination (JE )

  476. * * * * * * * * * * * * * * * * * * * * * * * * *

  477. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *

  478. SELECT A . ENAME  ENAME  FROM SCOTT . EMP  A , SCOTT . DEPT  B  WHERE A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = 'SALES' OR B . DNAME = 'RESEARCH' )

  479. JE : cfro : EMP objn :73179 col # :8 dfro :DEPT dcol# :8

  480. JE : cfro : EMP objn :73179 col # :8 dfro :DEPT dcol# :8

  481. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *

  482. SELECT A . ENAME  ENAME  FROM SCOTT . EMP  A , SCOTT . DEPT  B  WHERE A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME =' SALES'  OR B . DNAME = 'RESEARCH' )

  483. Query block SEL$1 ( # ) unchanged

  484. query block SEL$1 ( # ) unchanged

  485. Considering Query Transformations on query block SEL$1 ( # )

  486. * * * * * * * * * * * * * * * * * * * * * * * * * *

  487. Query transformations (QT )

  488. * * * * * * * * * * * * * * * * * * * * * * * * * *

  489. JF : Checking validity of join factorization for query block SEL$1 ( # )

  490. JF : Bypassed : not a UNION or UNION - ALL query block .

  491. ST : not valid since star transformation parameter is FALSE

  492. TE : Checking validity of table expansion for query block SEL$1 ( # )

  493. TE : Bypassed : No partitioned table in query block .

  494. CBQT bypassed for query block SEL$1 ( # ) : no complex view , sub -queries or UNION ( ALL ) queries .

  495. CBQT : Validity checks failed for 5z7wqfry426ch .

  496. CSE : Considering common sub -expression elimination in query block SEL$1 ( # )

  497. * * * * * * * * * * * * * * * * * * * * * * * * *

  498. Common Subexpression elimination (CSE )

  499. * * * * * * * * * * * * * * * * * * * * * * * * *

  500. CSE : CSE not performed on query block SEL$1 ( # ) .

  501. SU : Considering subquery unnesting in query block SEL$1 ( # )

  502. * * * * * * * * * * * * * * * * * * * *

  503. Subquery Unnest (SU )

  504. * * * * * * * * * * * * * * * * * * * *

  505. SJC : Considering set -join conversion in query block SEL$1 ( # )

  506. * * * * * * * * * * * * * * * * * * * * * * * * *

  507. Set -Join Conversion (SJC )

  508. * * * * * * * * * * * * * * * * * * * * * * * * *

  509. SJC : not performed

  510. JE : Considering Join Elimination on query block SEL$1 ( # )

  511. * * * * * * * * * * * * * * * * * * * * * * * * *

  512. Join Elimination (JE )

  513. * * * * * * * * * * * * * * * * * * * * * * * * *

  514. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *   -----这里说的是没变异之前的sql，但是现实的是他已经给你做完了合并了，这里看不到合并的那一步，"合并"的transformation，可能在sql解析器中就做了合并了，反正最后合并了...

  515.        有很多transformation是在10053中看不到的。 10053是个外国老头做的，他是个开 发，10053没那么严格              

  516. SELECT A . ENAME  ENAME  FROM SCOTT . EMP  A , SCOTT . DEPT  B  WHERE A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = 'SALES' OR B . DNAME = 'RESEARCH' )

  517. JE : cfro : EMP objn :73179 col # :8 dfro :DEPT dcol# :8

  518. JE : cfro : EMP objn :73179 col # :8 dfro :DEPT dcol# :8

  519. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *

  520. SELECT A . ENAME  ENAME  FROM SCOTT . EMP  A , SCOTT . DEPT  B  WHERE A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = 'SALES'  OR B . DNAME = 'RESEARCH' )

  521. Query block SEL$1 ( # ) unchanged

  522. PM : Considering predicate move -around in query block SEL$1 ( # )

  523. * * * * * * * * * * * * * * * * * * * * * * * * * *

  524. Predicate Move -Around (PM )

  525. * * * * * * * * * * * * * * * * * * * * * * * * * *

  526. PM : PM bypassed : Outer query contains no views .

  527. PM : PM bypassed : Outer query contains no views .

  528. query block SEL$1 ( # ) unchanged

  529. FPD : Considering simple filter push in query block SEL$1 ( # )

  530. A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = 'SALES' OR B . DNAME = 'RESEARCH' )

  531. try to generate transitive predicate from check constraints for query block SEL$1 ( # )

  532. finally : A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = SALES  OR B . DNAME = 'RESEARCH' )


  534. apadrv - start sqlid =6890391388387613072

  535.    :

  536.     call ( in -use =2116 , alloc =16360 ) , compile ( in -use =55108 , alloc =59416 ) , execution ( in -use =2124 , alloc =4060 )


  538. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  539. Peeked values of the binds in SQL statement

  540. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


  542. Final query after transformations : * * * * * * * UNPARSED QUERY IS * * * * * * *

  543. SELECT A . ENAME  ENAME  FROM SCOTT . EMP  A , SCOTT . DEPT  B  WHERE A . SAL >1500 AND A . DEPTNO = B . DEPTNO  AND ( B . DNAME = 'SALES' OR B . DNAME = 'RESEARCH' )

  544. kkoqbc : optimizing query block SEL$1 ( # )

  545.         

  546.          :

  547.     call ( in -use =2288 , alloc =16360 ) , compile ( in -use =55928 , alloc =59416 ) , execution ( in -use =2124 , alloc =4060 )


  549. kkoqbc -subheap ( create addr =0x88fb0c )

  550. * * * * * * * * * * * * * * * *

  551. QUERY BLOCK TEXT

  552. * * * * * * * * * * * * * * * *

  553. select /* Sample 2^^*/ a .ename from emp a , dept b

  554. where (a .deptno = b .deptno

  555. and a .sal >1500

  556. and b .dname = 'SALES' )

  557. or (

  558. a .deptno = b .deptno

  559. and a .sal > 1500

  560. and b .dname = 'RESEARCH'

  561. )

  562. ---------------------

  563. QUERY BLOCK SIGNATURE

  564. ---------------------

  565. signature (optimizer ) : qb_name =SEL$1 nbfros =2 flg =

  566.   fro ( ) : flg =0 objn =73181 hint_alias = "A" @ "SEL$1"

  567.   fro (1 ) : flg =0 objn =73179 hint_alias = "B" @ "SEL$1"


  569. -----------------------------

  570. SYSTEM STATISTICS INFORMATION

  571. -----------------------------

  572.    Using NOWORKLOAD Stats

  573.   CPUSPEEDNW : 2696 millions instructions /sec ( default is 100 )

  574.   IOTFRSPEED : 4096 bytes per millisecond ( default is 4096 )

  575.   IOSEEKTIM : 10 milliseconds ( default is 10 )

  576.   MBRC : -1 blocks ( default is 8 )


  578. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  579. BASE STATISTICAL INFORMATION

  580. * * * * * * * * * * * * * * * * * * * * * * *

  581. Table Stats : :

  582.    Table : DEPT  Alias : B

  583.     # Rows : 4  #Blks : 5  AvgRowLen : 20 .00

  584. Index Stats : :

  585.    Index : PK_DEPT Col # : 1

  586.     LVLS : 0  #LB : 1  #DK : 4  LB /K : 1 .00  DB /K : 1 .00  CLUF : 1 .00

  587. * * * * * * * * * * * * * * * * * * * * * * *

  588. Table Stats : :

  589.    Table : EMP  Alias : A

  590.     # Rows : 14  #Blks : 5  AvgRowLen : 41 .00

  591. Index Stats : :

  592.    Index : PK_EMP Col # : 1

  593.     LVLS : 0  #LB : 1  #DK : 14  LB /K : 1 .00  DB /K : 1 .00  CLUF : 1 .00

  594. Access path analysis for EMP

  595. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  596. SINGLE TABLE ACCESS PATH

  597.   Single Table Cardinality Estimation for EMP [A ]

  598.    Table : EMP  Alias : A

  599.     Card : Original : 14 .000000  Rounded : 12  Computed : 12 .10  Non Adjusted : 12 .10

  600.    Access Path : TableScan

  601.     Cost : 3 .00  Resp : 3 .00  Degree : 0

  602.       Cost_io : 3 .00  Cost_cpu : 40287

  603.       Resp_io : 3 .00  Resp_cpu : 40287

  604.    * * * * * * trying bitmap /domain indexes * * * * * *

  605.    Access Path : index (FullScan )

  606.      Index : PK_EMP

  607.     resc_io : 1 .00  resc_cpu : 9921

  608.     ix_sel : 1 .000000  ix_sel_with_filters : 1 .000000

  609.     Cost : 1 .00  Resp : 1 .00  Degree : 0

  610.    * * * * * * finished trying bitmap /domain indexes * * * * * *

  611.   Best : : AccessPath : TableScan

  612.          Cost : 3 .00  Degree : 1  Resp : 3 .00  Card : 12 .10  Bytes : 0


  614. Access path analysis for DEPT

  615. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  616. SINGLE TABLE ACCESS PATH

  617.   Single Table Cardinality Estimation for DEPT [B ]

  618.    Table : DEPT  Alias : B

  619.     Card : Original : 4 .000000  Rounded : 2  Computed : 2 .00  Non Adjusted : 2 .00

  620.    Access Path : TableScan

  621.     Cost : 3 .00  Resp : 3 .00  Degree : 0

  622.       Cost_io : 3 .00  Cost_cpu : 36637

  623.       Resp_io : 3 .00  Resp_cpu : 36637

  624.    * * * * * * trying bitmap /domain indexes * * * * * *

  625.    Access Path : index (FullScan )

  626.      Index : PK_DEPT

  627.     resc_io : 1 .00  resc_cpu : 7921

  628.     ix_sel : 1 .000000  ix_sel_with_filters : 1 .000000

  629.     Cost : 1 .00  Resp : 1 .00  Degree : 0

  630.    * * * * * * finished trying bitmap /domain indexes * * * * * *

  631.   Best : : AccessPath : TableScan

  632.          Cost : 3 .00  Degree : 1  Resp : 3 .00  Card : 2 .00  Bytes : 0


  634. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *



  637. OPTIMIZER STATISTICS AND COMPUTATIONS

  638. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  639. GENERAL PLANS

  640. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  641. Considering cardinality -based initial join order .

  642. Permutations for Starting Table :

  643. Join order [1 ] : DEPT [B ] #0  EMP [A ] #1


  645. * * * * * * * * * * * * * * *

  646. Now joining : EMP [A ] #1

  647. * * * * * * * * * * * * * * *

  648. NL Join

  649.   Outer table : Card : 2 .00  Cost : 3 .00  Resp : 3 .00  Degree : 1  Bytes : 13

  650. Access path analysis for EMP

  651.   Inner table : EMP  Alias : A

  652.    Access Path : TableScan

  653.     NL Join : Cost : 7 .00  Resp : 7 .00  Degree : 1

  654.       Cost_io : 7 .00  Cost_cpu : 117372

  655.       Resp_io : 7 .00  Resp_cpu : 117372

  656.    * * * * * * trying bitmap /domain indexes * * * * * *

  657.    Access Path : index (FullScan )

  658.      Index : PK_EMP

  659.     resc_io : 1 .00  resc_cpu : 9921

  660.     ix_sel : 1 .000000  ix_sel_with_filters : 1 .000000

  661.     Cost : 1 .00  Resp : 1 .00  Degree : 0

  662.    * * * * * * finished trying bitmap /domain indexes * * * * * *


  664.    Best NL cost : 7 . 00

  665.           resc : 7 .00  resc_io : 7 .00  resc_cpu : 117372

  666.           resp : 7 .00  resp_io : 7 .00  resc_cpu : 117372

  667. Join Card : 8 .065844 = = outer (2 .000000 ) * inner (12 .098765 ) * sel ( .333333 )

  668. Join Card - Rounded : 8 Computed : 8 .07

  669.   Outer table : DEPT  Alias : B

  670.     resc : 3 .00  card 2 .00  bytes : 13  deg : 1  resp : 3 .00

  671.   Inner table : EMP  Alias : A

  672.     resc : 3 .00  card : 12 .10  bytes : 16  deg : 1  resp : 3 .00

  673.      using dmeth : 2  #groups : 1

  674.     SORT ressource         Sort statistics

  675.       Sort width : 396 Area size : 347136 Max Area size : 69624832

  676.       Degree : 1

  677.       Blocks to Sort : 1 Row size : 25 Total Rows : 2

  678.        Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  679.       Total IO sort cost : 0      Total CPU sort cost : 32352758

  680.       Total Temp space used : 0

  681.     SORT ressource         Sort statistics

  682.       Sort width : 396 Area size : 347136 Max Area size : 69624832

  683.       Degree : 1

  684.       Blocks to Sort : 1 Row size : 28 Total Rows : 12

  685.        Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  686.       Total IO sort cost : 0      Total CPU sort cost : 32354606

  687.       Total Temp space used : 0

  688.   SM join : Resc : 8 .00  Resp : 8 .00 [multiMatchCost = .00 ]

  689. SM Join

  690.    SM cost : 8 . 00

  691.      resc : 8 .00 resc_io : 6 .00 resc_cpu : 64784289

  692.      resp : 8 .00 resp_io : 6 .00 resp_cpu : 64784289

  693. SM Join ( with index on outer )

  694.    Access Path : index (FullScan )

  695.      Index : PK_DEPT

  696.     resc_io : 2 .00  resc_cpu : 16073

  697.     ix_sel : 1 .000000  ix_sel_with_filters : 1 .000000

  698.     Cost : 2 .00  Resp : 2 .00  Degree : 1

  699.   Outer table : DEPT  Alias : B

  700.     resc : 2 .00  card 2 .00  bytes : 13  deg : 1  resp : 2 .00

  701.   Inner table : EMP  Alias : A

  702.     resc : 3 .00  card : 12 .10  bytes : 16  deg : 1  resp : 3 .00

  703.      using dmeth : 2  #groups : 1

  704.     SORT ressource         Sort statistics

  705.       Sort width : 396 Area size : 347136 Max Area size : 69624832

  706.       Degree : 1

  707.       Blocks to Sort : 1 Row size : 28 Total Rows : 12

  708.        Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  709.       Total IO sort cost : 0      Total CPU sort cost : 32354606

  710.       Total Temp space used : 0

  711.   SM join : Resc : 6 .00  Resp : 6 .00 [multiMatchCost = .00 ]

  712.   Outer table : DEPT  Alias : B

  713.     resc : 3 .00  card 2 .00  bytes : 13  deg : 1  resp : 3 .00

  714.   Inner table : EMP  Alias : A

  715.     resc : 3 .00  card : 12 .10  bytes : 16  deg : 1  resp : 3 .00

  716.      using dmeth : 2  #groups : 1

  717.     Cost per ptn : 0 .50  #ptns : 1

  718.     hash_area : 124 ( max =16999 ) buildfrag : 1  probefrag : 1  ppasses : 1

  719.   Hash join : Resc : 6 .50  Resp : 6 .50 [multiMatchCost = .00 ]

  720. HA Join

  721.    HA cost : 6 . 50

  722.      resc : 6 .50 resc_io : 6 .00 resc_cpu : 16254759

  723.      resp : 6 .50 resp_io : 6 .00 resp_cpu : 16254759

  724. Best : : JoinMethod : SortMerge

  725.        Cost : 6 .00  Degree : 1  Resp : 6 .00  Card : 8 .07 Bytes : 29

  726. * * * * * * * * * * * * * * * * * * * * * * *

  727. Best so far : Table # : 0  cost : 2 .0005  card : 2 .0000  bytes : 26

  728.                Table # : 1  cost : 6 .0018  card : 8 .0658  bytes : 232

  729. * * * * * * * * * * * * * * * * * * * * * * *

  730. Join order [2 ] : EMP [A ] #1  DEPT [B ] #


  732. * * * * * * * * * * * * * * *

  733. Now joining : DEPT [B ] #

  734. * * * * * * * * * * * * * * *

  735. NL Join

  736.   Outer table : Card : 12 .10  Cost : 3 .00  Resp : 3 .00  Degree : 1  Bytes : 16

  737. Access path analysis for DEPT

  738.   Inner table : DEPT  Alias : B

  739.    Access Path : TableScan

  740.     NL Join : Cost : 21 .01  Resp : 21 .01  Degree : 1

  741.       Cost_io : 21 .00  Cost_cpu : 479934

  742.       Resp_io : 21 .00  Resp_cpu : 479934

  743.    Access Path : index (UniqueScan )

  744.      Index : PK_DEPT

  745.     resc_io : 1 .00  resc_cpu : 8429

  746.     ix_sel : 0 .250000  ix_sel_with_filters : 0 .250000

  747.     NL Join : Cost : 15 .00  Resp : 15 .00  Degree : 1

  748.       Cost_io : 15 .00  Cost_cpu : 141434

  749.       Resp_io : 15 .00  Resp_cpu : 141434

  750.    Access Path : index (AllEqUnique )

  751.      Index : PK_DEPT

  752.     resc_io : 1 .00  resc_cpu : 8429

  753.     ix_sel : 0 .250000  ix_sel_with_filters : 0 .250000

  754.     NL Join : Cost : 15 .00  Resp : 15 .00  Degree : 1

  755.       Cost_io : 15 .00  Cost_cpu : 141434

  756.       Resp_io : 15 .00  Resp_cpu : 141434

  757.    * * * * * * trying bitmap /domain indexes * * * * * *

  758.    * * * * * * finished trying bitmap /domain indexes * * * * * *


  760.   Best NL cost : 15 .00

  761.           resc : 15 .00  resc_io : 15 .00  resc_cpu : 141434

  762.           resp : 15 .00  resp_io : 15 .00  resc_cpu : 141434

  763. Join Card : 8 .065844 = = outer (12 .098765 ) * inner (2 .000000 ) * sel ( .333333 )

  764. Join Card - Rounded : 8 Computed : 8 .07

  765.   Outer table : EMP  Alias : A

  766.     resc : 3 .00  card 12 .10  bytes : 16  deg : 1  resp : 3 .00

  767.   Inner table : DEPT  Alias : B

  768.     resc : 3 .00  card : 2 .00  bytes : 13  deg : 1  resp : 3 .00

  769.      using dmeth : 2  #groups : 1

  770.     SORT ressource         Sort statistics

  771.       Sort width : 396 Area size : 347136 Max Area size : 69624832

  772.       Degree : 1

  773.       Blocks to Sort : 1 Row size : 28 Total Rows : 12

  774.        Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  775.       Total IO sort cost : 0      Total CPU sort cost : 32354606

  776.       Total Temp space used : 0

  777.     SORT ressource         Sort statistics

  778.       Sort width : 396 Area size : 347136 Max Area size : 69624832

  779.       Degree : 1

  780.       Blocks to Sort : 1 Row size : 25 Total Rows : 2

  781.        Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  782.       Total IO sort cost : 0      Total CPU sort cost : 32352758

  783.       Total Temp space used : 0

  784.   SM join : Resc : 8 .00  Resp : 8 .00 [multiMatchCost = .00 ]

  785. SM Join

  786.   SM cost : 8 .00

  787.      resc : 8 .00 resc_io : 6 .00 resc_cpu : 64784289

  788.      resp : 8 .00 resp_io : 6 .00 resp_cpu : 64784289

  789.   Outer table : EMP  Alias : A

  790.     resc : 3 .00  card 12 .10  bytes : 16  deg : 1  resp : 3 .00

  791.   Inner table : DEPT  Alias : B

  792.     resc : 3 .00  card : 2 .00  bytes : 13  deg : 1  resp : 3 .00

  793.      using dmeth : 2  #groups : 1

  794.     Cost per ptn : 0 .50  #ptns : 1

  795.     hash_area : 124 ( max =16999 ) buildfrag : 1  probefrag : 1  ppasses : 1

  796.   Hash join : Resc : 6 .50  Resp : 6 .50 [multiMatchCost = .00 ]

  797. HA Join

  798.   HA cost : 6 .50

  799.      resc : 6 .50 resc_io : 6 .00 resc_cpu : 16255859

  800.      resp : 6 .50 resp_io : 6 .00 resp_cpu : 16255859

  801. Join order aborted : cost > best plan cost

  802. * * * * * * * * * * * * * * * * * * * * * * *

  803. (newjo -stop -1 ) k : , spcnt : , perm :2 , maxperm :2000


  805. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  806. Number of join permutations tried : 2

  807. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  808. Consider using bloom filter between B [DEPT ] and A [EMP ]

  809. kkoBloomFilter : join (lcdn :2 rcdn :12 jcdn :8 limit :12 )

  810. Computing bloom ndv for creator :B [DEPT ] ccdn :2 .0 and user :A [EMP ] ucdn :12 .1

  811. kkopqComputeBloomNdv : predicate (bndv :3 ndv :3 ) and (bndv :4 ndv :2 )

  812. kkopqComputeBloomNdv : pred cnt :2 ndv :2 reduction :0

  813. kkoBloomFilter : join ndv :2 reduction : .165306 (limit : .500000 ) accepted invalidated

  814. (newjo - save ) [0 1 ]

  815. Trying or -Expansion on query block SEL$1 ( # )

  816. Transfer Optimizer annotations for query block SEL$1 ( # )

  817. id =0 frofand predicate = B . DNAME = 'RESEARCH' OR B . DNAME = 'SALES'

  818. id =0 frofkksm [i ] (sort -merge /hash ) predicate = "A" . "DEPTNO" = "B" . "DEPTNO"

  819. id =0 frosand (sort -merge /hash ) predicate = "A" . "DEPTNO" = "B" . "DEPTNO"

  820. id =0 frofand predicate = "A" . "SAL" >1500

  821. Final cost for query block SEL$1 ( # ) - All Rows Plan :

  822.   Best join order : 1

  823.   Cost : 6 .0018  Degree : 1  Card : 8 .0000  Bytes : 232

  824.   Resc : 6 .0018  Resc_io : 5 .0000  Resc_cpu : 32410967

  825.   Resp : 6 .0018  Resp_io : 5 .0000  Resc_cpu : 32410967

  826. kkoqbc -subheap ( delete addr =0x88fb0c , in -use =58204 , alloc =75528 )

  827. kkoqbc - end :

  828.          :

  829.     call ( in -use =14320 , alloc =101236 ) , compile ( in -use =57944 , alloc =59416 ) , execution ( in -use =2124 , alloc =4060 )


  831. kkoqbc : finish optimizing query block SEL$1 ( # )

  832. apadrv - end

  833.            :

  834.     call ( in -use =14320 , alloc =101236 ) , compile ( in -use =58580 , alloc =59416 ) , execution ( in -use =2124 , alloc =4060 )



  837. Starting SQL statement dump


  839. user_id =84 user_name =SCOTT module =SQL *Plus action =

  840. sql_id =5z7wqfry426ch plan_hash_value =844388907 problem_type =3

  841. ----- Current SQL Statement for this session (sql_id=5z7wqfry426ch) -----

  842. select /* Sample 2^^*/ a .ename from emp a , dept b

  843. where (a .deptno = b .deptno

  844. and a .sal >1500

  845. and b .dname = 'SALES' )

  846. or (

  847. a .deptno = b .deptno

  848. and a .sal > 1500

  849. and b .dname = 'RESEARCH'

  850. )

  851. sql_text_length =180

  852. sql = select /* Sample 2^^*/ a .ename from emp a , dept b

  853. where (a .deptno = b .deptno

  854. and a .sal >1500

  855. and b .dname = 'SALES' )

  856. or (

  857. a .deptno = b .deptno

  858. and a .sal > 1500

  859. and b .dname = 'RESEARCH'

  860. )

  861. ----- Explain Plan Dump -----

  862. ----- Plan Table -----

  863.  

  864. = = = = = = = = = = = =

  865. Plan Table

  866. = = = = = = = = = = = =

  867. ------------------------------------------------+-----------------------------------+

  868. | Id | Operation                       | Name     | Rows | Bytes | Cost |     Time |

  869. ------------------------------------------------+-----------------------------------+

  870. | 0   | SELECT STATEMENT                 |          |       |        |    6 |           |

  871. | 1   |   MERGE JOIN                     |         |    8 |   232 |    6 | 00 :00 :01 | 

  872. | 2   |      TABLE ACCESS BY INDEX ROWID | DEPT     |    2 |    26 |    2 | 00 :00 :01 |

  873. | 3   |       INDEX FULL SCAN            | PK_DEPT |    4 |        |    1 | 00 :00 :01 |

  874. | 4   |     SORT JOIN                    |          |   12 |   192 |    4 | 00 :00 :01 |

  875. | 5   |        TABLE ACCESS FULL          | EMP      |   12 |   192 |    3 | 00 :00 :01 |

  876. ------------------------------------------------+-----------------------------------+

  877. Predicate Information :

  878. ----------------------

  879. 2 - filter ( ( B . DNAME = RESEARCH  OR B . DNAME = 'SALES' ) )

  880. 4 - access ( A . DEPTNO = B . DEPTNO )                                   ----table access full 没有顺序所以 sort join 排序这个步骤在内存中执行  

  881. 4 - filter ( A . DEPTNO = B . DEPTNO )

  882. 5 - filter ( A . SAL >1500 )

  883.  

  884. Content of other_xml column

  885. = = = = = = = = = = = = = = = = = = = = = = = = = = =

  886.   db_version : 11 .2 . .1

  887.   parse_schema : SCOTT

  888.   plan_hash : 844388907

  889.   plan_hash_2 : 1522826797

  890.   Outline Data :

  891.    /*+

  892.     BEGIN_OUTLINE_DATA

  893.       IGNORE_OPTIM_EMBEDDED_HINTS

  894.       OPTIMIZER_FEATURES_ENABLE(\'11.2.0.1\')

  895.       DB_VERSION(\'11.2.0.1\')

  896.       ALL_ROWS

  897.       OUTLINE_LEAF(@\"SEL$1\")

  898.       INDEX(@\"SEL$1\" \"B\"@\"SEL$1\" (\"DEPT\".\"DEPTNO\"))

  899.       FULL(@\"SEL$1\" \"A\"@\"SEL$1\")

  900.       LEADING(@\"SEL$1\" \"B\"@\"SEL$1\" \"A\"@\"SEL$1\")

  901.       USE_MERGE(@\"SEL$1\" \"A\"@\"SEL$1\")

  902.       PX_JOIN_FILTER(@\"SEL$1\" \"A\"@\"SEL$1\")

  903.     END_OUTLINE_DATA

  904.   */

  905.  

  906. Optimizer state dump :

  907. Compilation Environment Dump

  908. optimizer_mode_hinted = false

  909. optimizer_features_hinted = 0 . .

  910. parallel_execution_enabled = true

  911. parallel_query_forced_dop = 0

  912. parallel_dml_forced_dop = 0

  913. parallel_ddl_forced_degree = 0

  914. parallel_ddl_forced_instances = 0

  915. _query_rewrite_fudge = 90

  916. optimizer_features_enable = 11 .2 . .1

  917. _optimizer_search_limit = 5

  918. cpu_count = 1

  919. active_instance_count = 2

  920. parallel_threads_per_cpu = 2

  921. hash_area_size = 131072

  922. bitmap_merge_area_size = 1048576

  923. sort_area_size = 65536

  924. sort_area_retained_size = 0

  925. _sort_elimination_cost_ratio = 0

  926. _optimizer_block_size = 8192

  927. _sort_multiblock_read_count = 2

  928. _hash_multiblock_io_count = 0

  929. _db_file_optimizer_read_count = 8

  930. _optimizer_max_permutations = 2000

  931. pga_aggregate_target = 339968 KB

  932. _pga_max_size = 204800 KB

  933. _query_rewrite_maxdisjunct = 257

  934. _smm_auto_min_io_size = 56 KB

  935. _smm_auto_max_io_size = 248 KB

  936. _smm_min_size = 339 KB

  937. _smm_max_size = 67993 KB

  938. _smm_px_max_size = 169984 KB

  939. _cpu_to_io = 0

  940. _optimizer_undo_cost_change = 11 .2 . .1

  941. parallel_query_mode = enabled

  942. parallel_dml_mode = disabled

  943. parallel_ddl_mode = enabled

  944. optimizer_mode = all_rows

  945. sqlstat_enabled = false

  946. _optimizer_percent_parallel = 101

  947. _always_anti_join = choose

  948. _always_semi_join = choose

  949. _optimizer_mode_force = true

  950. _partition_view_enabled = true

  951. _always_star_transformation = false

  952. _query_rewrite_or_error = false

  953. _hash_join_enabled = true

  954. cursor_sharing = exact

  955. _b_tree_bitmap_plans = true

  956. star_transformation_enabled = false

  957. _optimizer_cost_model = choose

  958. _new_sort_cost_estimate = true

  959. _complex_view_merging = true

  960. _unnest_subquery = true

  961. _eliminate_common_subexpr = true

  962. _pred_move_around = true

  963. _convert_set_to_join = false

  964. _push_join_predicate = true

  965. _push_join_union_view = true

  966. _fast_full_scan_enabled = true

  967. _optim_enhance_nnull_detection = true

  968. _parallel_broadcast_enabled = true

  969. _px_broadcast_fudge_factor = 100

  970. _ordered_nested_loop = true

  971. _no_or_expansion = false

  972. optimizer_index_cost_adj = 100

  973. optimizer_index_caching = 0

  974. _system_index_caching = 0

  975. _disable_datalayer_sampling = false

  976. query_rewrite_enabled = true

  977. query_rewrite_integrity = enforced

  978. _query_cost_rewrite = true

  979. _query_rewrite_2 = true

  980. _query_rewrite_1 = true

  981. _query_rewrite_expression = true

  982. _query_rewrite_jgmigrate = true

  983. _query_rewrite_fpc = true

  984. _query_rewrite_drj = true

  985. _full_pwise_join_enabled = true

  986. _partial_pwise_join_enabled = true

  987. _left_nested_loops_random = true

  988. _improved_row_length_enabled = true

  989. _index_join_enabled = true

  990. _enable_type_dep_selectivity = true

  991. _improved_outerjoin_card = true

  992. _optimizer_adjust_for_nulls = true

  993. _optimizer_degree = 0

  994. _use_column_stats_for_function = true

  995. _subquery_pruning_enabled = true

  996. _subquery_pruning_mv_enabled = false

  997. _or_expand_nvl_predicate = true

  998. _like_with_bind_as_equality = false

  999. _table_scan_cost_plus_one = true

  1000. _cost_equality_semi_join = true

  1001. _default_non_equality_sel_check = true

  1002. _new_initial_join_orders = true

  1003. _oneside_colstat_for_equijoins = true

  1004. _optim_peek_user_binds = true

  1005. _minimal_stats_aggregation = true

  1006. _force_temptables_for_gsets = false

  1007. workarea_size_policy = auto

  1008. _smm_auto_cost_enabled = true

  1009. _gs_anti_semi_join_allowed = true

  1010. _optim_new_default_join_sel = true

  1011. optimizer_dynamic_sampling = 2

  1012. _pre_rewrite_push_pred = true

  1013. _optimizer_new_join_card_computation = true

  1014. _union_rewrite_for_gs = yes_gset_mvs

  1015. _generalized_pruning_enabled = true

  1016. _optim_adjust_for_part_skews = true

  1017. _force_datefold_trunc = false

  1018. statistics_level = typical

  1019. _optimizer_system_stats_usage = true

  1020. skip_unusable_indexes = true

  1021. _remove_aggr_subquery = true

  1022. _optimizer_push_down_distinct = 0

  1023. _dml_monitoring_enabled = true

  1024. _optimizer_undo_changes = false

  1025. _predicate_elimination_enabled = true

  1026. _nested_loop_fudge = 100

  1027. _project_view_columns = true

  1028. _local_communication_costing_enabled = true

  1029. _local_communication_ratio = 50

  1030. _query_rewrite_vop_cleanup = true

  1031. _slave_mapping_enabled = true

  1032. _optimizer_cost_based_transformation = linear

  1033. _optimizer_mjc_enabled = true

  1034. _right_outer_hash_enable = true

  1035. _spr_push_pred_refspr = true

  1036. _optimizer_cache_stats = false

  1037. _optimizer_cbqt_factor = 50

  1038. _optimizer_squ_bottomup = true

  1039. _fic_area_size = 131072

  1040. _optimizer_skip_scan_enabled = true

  1041. _optimizer_cost_filter_pred = false

  1042. _optimizer_sortmerge_join_enabled = true

  1043. _optimizer_join_sel_sanity_check = true

  1044. _mmv_query_rewrite_enabled = true

  1045. _bt_mmv_query_rewrite_enabled = true

  1046. _add_stale_mv_to_dependency_list = true

  1047. _distinct_view_unnesting = false

  1048. _optimizer_dim_subq_join_sel = true

  1049. _optimizer_disable_strans_sanity_checks = 0

  1050. _optimizer_compute_index_stats = true

  1051. _push_join_union_view2 = true

  1052. _optimizer_ignore_hints = false

  1053. _optimizer_random_plan = 0

  1054. _query_rewrite_setopgrw_enable = true

  1055. _optimizer_correct_sq_selectivity = true

  1056. _disable_function_based_index = false

  1057. _optimizer_join_order_control = 3

  1058. _optimizer_cartesian_enabled = true

  1059. _optimizer_starplan_enabled = true

  1060. _extended_pruning_enabled = true

  1061. _optimizer_push_pred_cost_based = true

  1062. _optimizer_null_aware_antijoin = true

  1063. _optimizer_extend_jppd_view_types = true

  1064. _sql_model_unfold_forloops = run_time

  1065. _enable_dml_lock_escalation = false

  1066. _bloom_filter_enabled = true

  1067. _update_bji_ipdml_enabled = 0

  1068. _optimizer_extended_cursor_sharing = udo

  1069. _dm_max_shared_pool_pct = 1

  1070. _optimizer_cost_hjsmj_multimatch = true

  1071. _optimizer_transitivity_retain = true

  1072. _px_pwg_enabled = true

  1073. optimizer_secure_view_merging = true

  1074. _optimizer_join_elimination_enabled = true

  1075. flashback_table_rpi = non_fbt

  1076. _optimizer_cbqt_no_size_restriction = true

  1077. _optimizer_enhanced_filter_push = true

  1078. _optimizer_filter_pred_pullup = true

  1079. _rowsrc_trace_level = 0

  1080. _simple_view_merging = true

  1081. _optimizer_rownum_pred_based_fkr = true

  1082. _optimizer_better_inlist_costing = all

  1083. _optimizer_self_induced_cache_cost = false

  1084. _optimizer_min_cache_blocks = 10

  1085. _optimizer_or_expansion = depth

  1086. _optimizer_order_by_elimination_enabled = true

  1087. _optimizer_outer_to_anti_enabled = true

  1088. _selfjoin_mv_duplicates = true

  1089. _dimension_skip_null = true

  1090. _force_rewrite_enable = false

  1091. _optimizer_star_tran_in_with_clause = true

  1092. _optimizer_complex_pred_selectivity = true

  1093. _optimizer_connect_by_cost_based = true

  1094. _gby_hash_aggregation_enabled = true

  1095. _globalindex_pnum_filter_enabled = true

  1096. _px_minus_intersect = true

  1097. _fix_control_key = 0

  1098. _force_slave_mapping_intra_part_loads = false

  1099. _force_tmp_segment_loads = false

  1100. _query_mmvrewrite_maxpreds = 10

  1101. _query_mmvrewrite_maxintervals = 5

  1102. _query_mmvrewrite_maxinlists = 5

  1103. _query_mmvrewrite_maxdmaps = 10

  1104. _query_mmvrewrite_maxcmaps = 20

  1105. _query_mmvrewrite_maxregperm = 512

  1106. _query_mmvrewrite_maxmergedcmaps = 50

  1107. _query_mmvrewrite_maxqryinlistvals = 500

  1108. _disable_parallel_conventional_load = false

  1109. _trace_virtual_columns = false

  1110. _replace_virtual_columns = true

  1111. _virtual_column_overload_allowed = true

  1112. _kdt_buffering = true

  1113. _first_k_rows_dynamic_proration = true

  1114. _optimizer_sortmerge_join_inequality = true

  1115. _optimizer_aw_stats_enabled = true

  1116. _bloom_pruning_enabled = true

  1117. result_cache_mode = MANUAL

  1118. _px_ual_serial_input = true

  1119. _optimizer_skip_scan_guess = false

  1120. _enable_row_shipping = true

  1121. _row_shipping_threshold = 80

  1122. _row_shipping_explain = false

  1123. transaction_isolation_level = read_commited

  1124. _optimizer_distinct_elimination = true

  1125. _optimizer_multi_level_push_pred = true

  1126. _optimizer_group_by_placement = true

  1127. _optimizer_rownum_bind_default = 10

  1128. _enable_query_rewrite_on_remote_objs = true

  1129. _optimizer_extended_cursor_sharing_rel = simple

  1130. _optimizer_adaptive_cursor_sharing = true

  1131. _direct_path_insert_features = 0

  1132. _optimizer_improve_selectivity = true

  1133. optimizer_use_pending_statistics = false

  1134. _optimizer_enable_density_improvements = true

  1135. _optimizer_aw_join_push_enabled = true

  1136. _optimizer_connect_by_combine_sw = true

  1137. _enable_pmo_ctas = 0

  1138. _optimizer_native_full_outer_join = force

  1139. _bloom_predicate_enabled = true

  1140. _optimizer_enable_extended_stats = true

  1141. _is_lock_table_for_ddl_wait_lock = 0

  1142. _pivot_implementation_method = choose

  1143. optimizer_capture_sql_plan_baselines = false

  1144. optimizer_use_sql_plan_baselines = true

  1145. _optimizer_star_trans_min_cost = 0

  1146. _optimizer_star_trans_min_ratio = 0

  1147. _with_subquery = OPTIMIZER

  1148. _optimizer_fkr_index_cost_bias = 10

  1149. _optimizer_use_subheap = true

  1150. parallel_degree_policy = manual

  1151. parallel_degree = 0

  1152. parallel_min_time_threshold = 10

  1153. _parallel_time_unit = 10

  1154. _optimizer_or_expansion_subheap = true

  1155. _optimizer_free_transformation_heap = true

  1156. _optimizer_reuse_cost_annotations = true

  1157. _result_cache_auto_size_threshold = 100

  1158. _result_cache_auto_time_threshold = 1000

  1159. _optimizer_nested_rollup_for_gset = 100

  1160. _nlj_batching_enabled = 1

  1161. parallel_query_default_dop = 0

  1162. is_recur_flags = 0

  1163. optimizer_use_invisible_indexes = false

  1164. flashback_data_archive_internal_cursor = 0

  1165. _optimizer_extended_stats_usage_control = 224

  1166. _parallel_syspls_obey_force = true

  1167. cell_offload_processing = true

  1168. _rdbms_internal_fplib_enabled = false

  1169. db_file_multiblock_read_count = 79

  1170. _bloom_folding_enabled = true

  1171. _mv_generalized_oj_refresh_opt = true

  1172. cell_offload_compaction = ADAPTIVE

  1173. parallel_degree_limit = 65535

  1174. parallel_force_local = false

  1175. parallel_max_degree = 4

  1176. total_cpu_count = 2

  1177. cell_offload_plan_display = AUTO

  1178. _optimizer_coalesce_subqueries = true

  1179. _optimizer_fast_pred_transitivity = true

  1180. _optimizer_fast_access_pred_analysis = true

  1181. _optimizer_unnest_disjunctive_subq = true

  1182. _optimizer_unnest_corr_set_subq = true

  1183. _optimizer_distinct_agg_transform = true

  1184. _aggregation_optimization_settings = 0

  1185. _optimizer_connect_by_elim_dups = true

  1186. _optimizer_eliminate_filtering_join = true

  1187. _connect_by_use_union_all = true

  1188. dst_upgrade_insert_conv = true

  1189. advanced_queuing_internal_cursor = 0

  1190. _optimizer_unnest_all_subqueries = true

  1191. _bloom_predicate_pushdown_to_storage = true

  1192. _bloom_vector_elements = 0

  1193. _bloom_pushing_max = 524288

  1194. parallel_autodop = 0

  1195. parallel_ddldml = 0

  1196. _parallel_cluster_cache_policy = adaptive

  1197. _parallel_scalability = 50

  1198. iot_internal_cursor = 0

  1199. _optimizer_instance_count = 0

  1200. _optimizer_connect_by_cb_whr_only = false

  1201. _suppress_scn_chk_for_cqn = nosuppress_1466

  1202. _optimizer_join_factorization = true

  1203. _optimizer_use_cbqt_star_transformation = true

  1204. _optimizer_table_expansion = true

  1205. _and_pruning_enabled = true

  1206. _deferred_constant_folding_mode = DEFAULT

  1207. _optimizer_distinct_placement = true

  1208. partition_pruning_internal_cursor = 0

  1209. parallel_hinted = none

  1210. _sql_compatibility = 0

  1211. _optimizer_use_feedback = true

  1212. _optimizer_try_st_before_jppd = true

  1213. Bug Fix Control Environment

  1214.     fix  3834770 = 1

  1215.     fix  3746511 = enabled

  1216.     fix  4519016 = enabled 

  1217.     fix  2320291 = enabled 

  1218.     fix  4666174 = enabled

  1219.     fix  4567767 = enabled

  1220.     fix  4556762 = 15   


  1222. Query Block Registry :

  1223. SEL$1 0x883604 (PARSER ) [ FINAL ]


  1225. :

  1226.     call ( in -use =21064 , alloc =101236 ) , compile ( in -use =80828 , alloc =141580 ) , execution ( in -use =8172 , alloc =12212 )


  1228. End of Optimizer State Dump

  1229. Dumping Hints

  1230. = = = = = = = = = = = = =

  1231. = = = = = = = = = = = = = = = = = = = = = = END SQL Statement Dump = = = = = = = = = = = = = = = = = = = = = =

  1232. ------------------------------------------------+-----------------------------------+

  1233. | Id  | Operation                        | Name     |  Rows  | Bytes  | Cost  |     Time  |

  1234. ------------------------------------------------+-----------------------------------+

  1235. | 0   |  SELECT STATEMENT                 |          |       |        |    6  |           |

  1236. | 1   |   MERGE JOIN                     |          |    8  |   232  |    6  | 00 :00 :01  | 

  1237. | 2   |      TABLE  ACCESS  BY  INDEX  ROWID  | DEPT     |    2  |    26  |    2  | 00 :00 :01  |

  1238. | 3   |        INDEX FULL SCAN            | PK_DEPT  |    4  |        |    1  | 00 :00 :01  |

  1239. | 4   |     SORT JOIN                    |          |   12  |   192  |    4  | 00 :00 :01  |

  1240. | 5   |        TABLE  ACCESS FULL          | EMP      |   12  |   192  |    3  | 00 :00 :01  |

  1241. ------------------------------------------------+-----------------------------------+

               Predicate Information:
             ----------------------
             2 - filter(("B"."DNAME"='RESEARCH' OR "B"."DNAME"='SALES'))
             4 - access("A"."DEPTNO"="B"."DEPTNO")
             4 - filter("A"."DEPTNO"="B"."DEPTNO")
             5 - filter("A"."SAL">1500)

语句的执行计划走的是sort，
但是11.2.0.3走的是hash，不同版本解析器执行计划可能不同
bloom一个行，一个位。
0101000100010000000000010001000010000010000111
0110000010010000000010000000010000000000000110
符合条件的变成1，把两个字节序做个and，都是1说明能连取出来，这个动作叫boom





_bloom_filter_enabled 


请使用浏览器的分享功能分享到微信等