Transformation之FPD(filter push-down)的transform【一】

哎呀我的天吶發表於2014-11-06
    使用者寫的sql,Oracle會進行等價改寫,即使是RBO優化模式,oracle也會給你做一些轉換,這些轉化都是基於一種固定的演算法,oracle稱這種轉換是“啟發式”。大多是有明顯的問題,比如我們寫in,oracle在有可能的情況下,會將in轉換成exist,然後用semi join的方式給你做join,在一般情況下exist比in要快。transformation是oracle必做的一個步驟,至少在8i 8.05版本之後transformation這個東西一直都存在。網上有很多優化法則,說用exits不讓用in,有些情況下是對的,但有些情況下是不對的,用in的時候,oracle能轉換的話都會給你轉換,這種轉換不是基於成本的而是“基於啟發的轉化”

    oracle有的時候不做transformation,這時候可能會引起效能的問題,此時你就得去找原因,oracle有時,會把transformation做錯。11.2.0.3有四個ora_600錯誤是由於transformation引起的bug,11.2.0.4有兩個ora_600錯誤是由於transformation引起。

    在開發團隊,我們建議儘量少寫一些複雜的view,你可以讓資料冗餘一些,在插入的時候,多插入一些資料做取代。或者用物化檢視解決這些問題,儘量不要寫常規的view,爛的view會引起oracle transformation錯誤,影響效能

join:
    inner-join:等值連線
    outer-join:外連線
    semi-join:exist,拿sql是寫不出來的,他其實是oracle內部演算法的一個名稱。oracle用一個列匹配另外的列,當只要有一個值匹配成功即結束不會遍歷所有的值
    anti-join:not exist(或者 not in)這種情況。

view:
    Simple:如果結果集中能拿到rowid,那麼就是一個簡單的view,結果集拿不到rowid就是複雜的view。
    Complex(複雜view):包含聚合函式 如distinct,group by ,connect by,只要有遞迴查詢這用view,我們就稱之為複雜的view。
    in-line view:相當於子查詢。有一個表join一個查詢,如下:

點選(此處)摺疊或開啟

  1. SQL> select a.* from emp a ,
  2.   2 (select * from dept where deptno > 20) b
  3.   3 where a.deptno = b.deptno;

  4.      EMPNO ENAME JOB             MGR HIREDATE       SAL      COMM  DEPTNO
  5. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
  6.       7499 YAOCHONG SALESMAN     7698 20-FEB-81     1600     300   30
  7.       7521 YAOCHONG SALESMAN     7698 22-FEB-81     1250     500   30
  8.       7654 YAOCHONG SALESMAN     7698 28-SEP-81     1250     1400  30
  9.       7698 YAOCHONG MANAGER      7839 01-MAY-81     2850           30
  10.       7844 YAOCHONG SALESMAN     7698 08-SEP-81     1500      0    30
  11.       7900 YAOCHONG CLERK        7698 03-DEC-81     950            30

  12. 6 rows selected.
Sub-quries:
    =
    in

點選(此處)摺疊或開啟

  1. SQL> select * from emp where deptno in
  2.   2 (select deptno from dept where deptno > 20);

  3.      EMPNO ENAME         JOB          MGR     HIREDATE      SAL   COMM    DEPTNO
  4. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
  5.       7499 YAOCHONG      SALESMAN     7698    20-FEB-81     1600  300     30
  6.       7521 YAOCHONG      SALESMAN     7698    22-FEB-81     1250  500     30
  7.       7654 YAOCHONG      SALESMAN     7698    28-SEP-81     1250  1400    30
  8.       7698 YAOCHONG      MANAGER      7839    01-MAY-81     2850          30
  9.       7844 YAOCHONG      SALESMAN     7698    08-SEP-81     1500    0     30
  10.       7900 YAOCHONG      CLERK        7698    03-DEC-81     950           30
    exists
    not in
    not exists
    any/some
    all
    Sing-column
    multi-column
    single-table
    multi-table
    
-----------------------------------------------------------------------------------------------------------------------------------------------------

點選(此處)摺疊或開啟

  1. SQL> explain plan for select * from (select * from emp);

  2. Explained.

  3. SQL> @?/rdbms/admin/utlxplp

  4. PLAN_TABLE_OUTPUT
  5. ---------------------------------------------------------------------------------------
  6. Plan hash value: 3956160932

  7. --------------------------------------------------------------------------
  8. | Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |
  9. --------------------------------------------------------------------------
  10. | 0  | SELECT STATEMENT |      | 14   | 574   | 3 (0)      | 00:00:01|
  11. | 1  | TABLE ACCESS FULL| EMP  | 14   | 574   | 3 (0)      | 00:00:01|
  12. --------------------------------------------------------------------------
oracle會自動transformation成select * from emp;執行計劃相同。

點選(此處)摺疊或開啟

  1. SQL> @?/rdbms/admin/utlxplp

  2. PLAN_TABLE_OUTPUT
  3. ----------------------------------------------------------------------------------------
  4. Plan hash value: 3956160932

  5. --------------------------------------------------------------------------
  6. | Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
  7. --------------------------------------------------------------------------
  8. | 0  | SELECT STATEMENT |      | 14   | 574   | 3 (0)      | 00:00:01 |
  9. | 1  | TABLE ACCESS FULL| EMP  | 14   | 574   | 3 (0)      | 00:00:01 |

以下三種oracle 也做了transformation

點選(此處)摺疊或開啟

  1. SQL> explain plan for select * from (select * from emp) where empno = 7788;

  2. Explained.

  3. SQL> @?/rdbms/admin/utlxplp

  4. PLAN_TABLE_OUTPUT
  5. -------------------------------------------------------------------------------------------
  6. Plan hash value: 2949544139

  7. --------------------------------------------------------------------------------------
  8. | Id | Operation                  | Name   | Rows  | Bytes  | Cost (%CPU)| Time     |
  9. --------------------------------------------------------------------------------------
  10. | 0  | SELECT STATEMENT           |        |     1 |     41 |     1 (0)  | 00:00:01 |
  11. | 1  | TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     41 |     1 (0)  | 00:00:01 |
  12. |* 2 | INDEX UNIQUE SCAN          | PK_EMP |     1 |        |     0 (0)  | 00:00:01 |
  13. --------------------------------------------------------------------------------------

  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------

  16.    2 - access("EMP"."EMPNO"=7788)

  17. 14 rows selected.

點選(此處)摺疊或開啟

  1. SQL> explain plan for select * from ( select * from emp where empno = 7788);

  2. Explained.

  3. SQL> @?/rdbms/admin/utlxplp

  4. PLAN_TABLE_OUTPUT
  5. -------------------------------------------------------------------------------------------
  6. Plan hash value: 2949544139

  7. --------------------------------------------------------------------------------------
  8. | Id | Operation                 | Name   | Rows  | Bytes  | Cost (%CPU)| Time     |
  9. --------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT           |        |     1 |     41 |     1 (0)  | 00:00:01 |
  11. | 1 | TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     41 |     1 (0)  | 00:00:01 |
  12. |* 2 | INDEX UNIQUE SCAN         | PK_EMP |     1 |        |     0 (0)  | 00:00:01 |
  13. --------------------------------------------------------------------------------------

  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------

  16.    2 - access(\"EMPNO\"=7788)

  17. 14 rows selected.

點選(此處)摺疊或開啟

  1. SQL> explain plan for select * from emp where empno = 7788;

  2. Explained.

  3. SQL> @?/rdbms/admin/utlxplp

  4. PLAN_TABLE_OUTPUT
  5. ------------------------------------------------------------------------------------------
  6. Plan hash value: 2949544139

  7. --------------------------------------------------------------------------------------
  8. | Id | Operation                 | Name   | Rows  | Bytes  | Cost (%CPU)| Time     |
  9. --------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT           |        |     1 |     41 |     1 (0)  | 00:00:01 |
  11. | 1 | TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     41 |     1 (0)  | 00:00:01 |
  12. |* 2 | INDEX UNIQUE SCAN         | PK_EMP |     1 |        |     0 (0)  | 00:00:01 |
  13. --------------------------------------------------------------------------------------

  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------

  16.    2 - access(\"EMPNO\"=7788)

  17. 14 rows selected.
一個查詢發起的時候,首先要做啟發式的transformation,轉化好之後再做優化,最後生成執行計劃。

看10053,首先出現的是transformation,然後收集統計資訊,之後cost比較,最後執行計劃。

啟發式transformation(所謂啟發式就是固定的,硬程式碼寫的,先於cost評估,oracle轉化成語義相等的sql,然後再算cost)
    
  • 先於cost評估
  • 剔除多餘的操作
  • 最小化quary block
基於成本的transformation(高版本帶的基於成本的transformation
  • 在多個可能的transformation路徑中選擇----cost的過程中產生的(區別於啟發式之後再cost)
  • 從多個transformation的結果中,找到最好的plan

啟發式transformation包含的方法


實驗:

點選(此處)摺疊或開啟

  1. set line 200
  2. set pages 200
  3. @10053.sql  即:alter session set events '10053 trace name context forever, level 1';
  4. select /*sample 1*/e.ename
  5. from emp e, emp m
  6. where e.mgr=m.empno
  7. and m.deptno = 10
  8. and e.deptno = m.deptno
  9. and e.sal>1500
  10. and m.sal>e.sal;
  11. @10053d.sql 即:alter session set events '10053 trace name context off';
                    set line 2000
                    set pages 200
  12. select TRACEFILE from v$process where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1));
  13. TRACEFILE
    ------------------------------------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_22134.trc

  14. select * from table(dbms_xplan.display_cursor(null,null,'last'));

  15. /*
  16. FPD: transitive predicates are generated in query block SEL$1 (#0)
  17. "E"."MGR"="M"."EMPNO" AND "M"."DEPTNO"=10 AND "E"."DEPTNO"="M"."DEPTNO" AND "E"."SAL">1500 AND "M"."SAL">"E"."SAL" AND "M"."SAL">1500 AND "E"."DEPTNO"=1
  18. */
10053事件的使用方法和10046是一樣的,首先給事件設定一個級別level 1,然後執行sql(或者直接使用explain plan的方式產生執行計劃),最後終止事件。10053事件產生的trace檔案不能用tkprof工具格式話,tkprof工具只能格式化sql_trace和10046事件產生的trace檔案,直接閱讀這個原始檔案就可以。
  1. SQL> !vi /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_27067.trc

點選(此處)摺疊或開啟

  1. Trace file /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_22134.trc
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production   ------------版本是什麼
  3. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,             -----是rac的
  4. Data Mining and Real Application Testing options
  5. ORACLE_HOME = /u01/app/oracle/product/11.2.0/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: 34
  14. Unix process pid: 22134, image: oracle@node1 (TNS V1-V3)             -------以上資訊對於support來說很重要,我們關注下版本是什麼


  15. *** 2014-11-01 01:37:48.076
  16. *** SESSION ID:(59.486) 2014-11-01 01:37:48.076
  17. *** CLIENT ID:() 2014-11-01 01:37:48.076
  18. *** SERVICE NAME:(SYS$USERS) 2014-11-01 01:37:48.076
  19. *** MODULE NAME:(SQL*Plus) 2014-11-01 01:37:48.076
  20. *** ACTION NAME:() 2014-11-01 01:37:48.076

  21. Registered qb: SEL$1 0x708b6c (PARSER)
  22. ---------------------
  23. QUERY BLOCK SIGNATURE                                ----10053最先做的是query block的註冊
  24. ---------------------
  25.   signature (): qb_name=SEL$1 nbfros=2 flg=0       -----先註冊一個quary block 名字是SEL$1,如果語句多會分成多個query block,
  26.                                                     query block可以用hint qb_name指定 
  27.     fro(0): flg=4 objn=73181 hint_alias="E"@"SEL$1"
  28.     fro(1): flg=4 objn=73181 hint_alias="M"@"SEL$1"                     ------10053第一次做quary block的註冊。

  29. SPM: statement not found in SMB

  30. **************************
  31. Automatic degree of parallelism (ADOP)
  32. **************************
  33. Automatic degree of parallelism is disabled: Parameter.

  34. PM: Considering predicate move-around in query block SEL$1 (#0)       ----第二步predicate move-around考慮能不能講位詞給祛除掉
  35. **************************
  36. Predicate Move-Around (PM)
  37. **************************
  38. OPTIMIZER INFORMATION

  39. ******************************************
  40. ----- Current SQL Statement for this session (sql_id=4usswnngj0jdx)    -----sql id也告訴你了
  41. select /*sample 1*/e.ename                                             -----然後告訴你當前的sql是什麼樣子的
  42. from emp e, emp m
  43. where e.mgr=m.empno
  44. and m.deptno = 10
  45. and e.deptno = m.deptno
  46. and e.sal>1500
  47. and m.sal>e.sal
  48. *******************************************                            -------這以下都是transformation的方法,解釋一些縮寫的含義
  49. Legend
  50. The following abbreviations are used by optimizer trace.
  51. CBQT - cost-based query transformation
  52. JPPD - join predicate push-down
  53. OJPPD - old-style (non-cost-based) JPPD    ---不考慮成本的cost
  54. FPD - filter push-down
  55. PM - predicate move-around
  56. CVM - complex view merging
  57. SPJ - select-project-join
  58. SJC - set join conversion
  59. SU - subquery unnesting
  60. OBYE - order by elimination
  61. OST - old style star transformation
  62. ST - new (cbqt) star transformation
  63. CNT - count(col) to count(*) transformation
  64. JE - Join Elimination
  65. JF - join factorization
  66. SLP - select list pruning
  67. DP - distinct placement
  68. qb - query block
  69. LB - leaf blocks
  70. DK - distinct keys
  71. LB/K - average number of leaf blocks per key
  72. DB/K - average number of data blocks per key
  73. CLUF - clustering factor
  74. NDV - number of distinct values
  75. Resp - response cost
  76. Card - cardinality
  77. Resc - resource cost
  78. NL - nested loops (join)
  79. SM - sort merge (join)
  80. HA - hash (join)

  1. CPUSPEED - CPU Speed                            
  2. IOTFRSPEED - I/O transfer speed
  3. IOSEEKTIM - I/O seek time
  4. SREADTIM - average single block read time
  5. MREADTIM - average multiblock read time
  6. MBRC - average multiblock read count
  7. MAXTHR - maximum I/O system throughput
  8. SLAVETHR - average slave I/O throughput


  1. dmeth - distribution method
  2.   1: no partitioning required
  3.   2: value partitioned
  4.   4: right is random (round-robin)
  5.   128: left is random (round-robin)
  6.   8: broadcast right and partition left
  7.   16: broadcast left and partition right
  8.   32: partition left using partitioning of right
  9.   64: partition right using partitioning of left
  10.   256: run the join in serial
  11.   0: invalid distribution method
  12. sel - selectivity
  13. ptn - partition
  14.                                               這裡是修復bug的資訊
  15. ***************************************
  16. PARAMETERS USED BY THE OPTIMIZER             -----這裡告訴你系統的統計資訊 
  17. ********************************
  18. *************************************
  19. PARAMETERS WITH ALTERED VALUES           
  20. ******************************
  21. Compilation Environment Dump      ---這個下面這一塊是必看的一個章節,這個下面有什麼說明,改過的cbo引數一定要看的。是不是改過 
  22. parallel_max_degree = 4
  23. Bug Fix Control Environment


  24.   *************************************          和cbo有關的引數都列在這裡,這些引數可以在session級別關閉
  25.   PARAMETERS WITH DEFAULT VALUES
  26.   ******************************
  27. Compilation Environment Dump
  28. optimizer_mode_hinted = false
  29. optimizer_features_hinted = 0.0.0
  30. parallel_execution_enabled = true
  31. parallel_query_forced_dop = 0
  32. parallel_dml_forced_dop = 0
  33. parallel_ddl_forced_degree = 0
  34. parallel_ddl_forced_instances = 0
  35. _query_rewrite_fudge = 90
  36. optimizer_features_enable = 11.2.0.1
  37. _optimizer_search_limit = 5
  38. cpu_count = 1
  39. active_instance_count = 2
  40. parallel_threads_per_cpu = 2
  41. hash_area_size = 131072
  42. bitmap_merge_area_size = 1048576
  43. sort_area_size = 65536
  44. sort_area_retained_size = 0
  45. _sort_elimination_cost_ratio = 0
  46. _optimizer_block_size = 8192
  47. _sort_multiblock_read_count = 2
  48. _hash_multiblock_io_count = 0
  49. _db_file_optimizer_read_count = 8
  50. _optimizer_max_permutations = 2000
  51. pga_aggregate_target = 339968 KB
  52. _pga_max_size = 204800 KB
  53. _query_rewrite_maxdisjunct = 257
  54. _smm_auto_min_io_size = 56 KB
  55. _smm_auto_max_io_size = 248 KB
  56. _smm_min_size = 339 KB
  57. _smm_max_size = 67993 KB
  58. _smm_px_max_size = 169984 KB
  59. _cpu_to_io = 0
  60. _optimizer_undo_cost_change = 11.2.0.1
  61. parallel_query_mode = enabled
  62. parallel_dml_mode = disabled
  63. parallel_ddl_mode = enabled
  64. optimizer_mode = all_rows
  65. sqlstat_enabled = false
  66. _optimizer_percent_parallel = 101
  67. _always_anti_join = choose
  68. _always_semi_join = choose
  69. _optimizer_mode_force = true
  70. _partition_view_enabled = true
  71. _always_star_transformation = false
  72. _query_rewrite_or_error = false
  73. _hash_join_enabled = true
  74. cursor_sharing = exact
  75. _b_tree_bitmap_plans = true
  76. star_transformation_enabled = false
  77. _optimizer_cost_model = choose
  78. _new_sort_cost_estimate = true
  79. _complex_view_merging = true
  80. _unnest_subquery = true
  81. _eliminate_common_subexpr = true
  82. _pred_move_around = true
  83. _convert_set_to_join = false
  84. _push_join_predicate = true
  85. _push_join_union_view = true
  86. _fast_full_scan_enabled = true
  87. _optim_enhance_nnull_detection = true
  88. _parallel_broadcast_enabled = true
  89. _px_broadcast_fudge_factor = 100
  90. _ordered_nested_loop = true
  91. _no_or_expansion = false
  92. optimizer_index_cost_adj = 100
  93. optimizer_index_caching = 0
  94. _system_index_caching = 0
  95. _disable_datalayer_sampling = false
  96. query_rewrite_enabled = true
  97. query_rewrite_integrity = enforced
  98. _query_cost_rewrite = true
  99. _query_rewrite_2 = true
  100. _query_rewrite_1 = true
  101. _query_rewrite_expression = true
  102. _query_rewrite_jgmigrate = true
  103. _query_rewrite_fpc = true
  104. _query_rewrite_drj = true
  105. _full_pwise_join_enabled = true
  106. _partial_pwise_join_enabled = true
  107. _left_nested_loops_random = true
  108. _improved_row_length_enabled = true
  109. _index_join_enabled = true
  110. _enable_type_dep_selectivity = true
  111. _improved_outerjoin_card = true
  112. _optimizer_adjust_for_nulls = true
  113. _optimizer_degree = 0
  114. _use_column_stats_for_function = true
  115. _subquery_pruning_enabled = true
  116. _subquery_pruning_mv_enabled = false
  117. _or_expand_nvl_predicate = true
  118. _like_with_bind_as_equality = false
  119. _table_scan_cost_plus_one = true
  120. _cost_equality_semi_join = true
  121. _default_non_equality_sel_check = true
  122. _new_initial_join_orders = true
  123. _oneside_colstat_for_equijoins = true
  124. _optim_peek_user_binds = true
  125. _minimal_stats_aggregation = true
  126. _force_temptables_for_gsets = false
  127. workarea_size_policy = auto
  128. _smm_auto_cost_enabled = true
  129. _gs_anti_semi_join_allowed = true
  130. _optim_new_default_join_sel = true
  131. optimizer_dynamic_sampling = 2
  132. _pre_rewrite_push_pred = true
  133. _optimizer_new_join_card_computation = true
  134. _union_rewrite_for_gs = yes_gset_mvs
  135. _generalized_pruning_enabled = true
  136. _optim_adjust_for_part_skews = true
  137. _force_datefold_trunc = false
  138. statistics_level = typical
  139. _optimizer_system_stats_usage = true
  140. skip_unusable_indexes = true
  141. _remove_aggr_subquery = true
  142. _optimizer_push_down_distinct = 0
  143. _dml_monitoring_enabled = true
  144. _optimizer_undo_changes = false
  145. _predicate_elimination_enabled = true
  146. _nested_loop_fudge = 100
  147. _project_view_columns = true
  148. _local_communication_costing_enabled = true
  149. _local_communication_ratio = 50
  150. _query_rewrite_vop_cleanup = true
  151. _slave_mapping_enabled = true
  152. _optimizer_cost_based_transformation = linear
  153. _optimizer_mjc_enabled = true
  154. _right_outer_hash_enable = true
  155. _spr_push_pred_refspr = true
  156. _optimizer_cache_stats = false
  157. _optimizer_cbqt_factor = 50
  158. _optimizer_squ_bottomup = true
  159. _fic_area_size = 131072
  160. _optimizer_skip_scan_enabled = true
  161. _optimizer_cost_filter_pred = false
  162. _optimizer_sortmerge_join_enabled = true
  163. _optimizer_join_sel_sanity_check = true
  164. _mmv_query_rewrite_enabled = true
  165. _bt_mmv_query_rewrite_enabled = true
  166. _add_stale_mv_to_dependency_list = true
  167. _distinct_view_unnesting = false
  168. _optimizer_dim_subq_join_sel = true
  169. _optimizer_disable_strans_sanity_checks = 0
  170. _optimizer_compute_index_stats = true
  171. _push_join_union_view2 = true
  172. _optimizer_ignore_hints = false
  173. _optimizer_random_plan = 0
  174. _query_rewrite_setopgrw_enable = true
  175. _optimizer_correct_sq_selectivity = true
  176. _disable_function_based_index = false
  177. _optimizer_join_order_control = 3
  178. _optimizer_cartesian_enabled = true
  179. _optimizer_starplan_enabled = true
  180. _extended_pruning_enabled = true
  181. _optimizer_push_pred_cost_based = true
  182. _optimizer_null_aware_antijoin = true
  183. _optimizer_extend_jppd_view_types = true
  184. _sql_model_unfold_forloops = run_time
  185. _enable_dml_lock_escalation = false
  186. _bloom_filter_enabled = true
  187. _update_bji_ipdml_enabled = 0
  188. _optimizer_extended_cursor_sharing = udo
  189. _dm_max_shared_pool_pct = 1
  190. _optimizer_cost_hjsmj_multimatch = true
  191. _optimizer_transitivity_retain = true
  192. _px_pwg_enabled = true
  193. optimizer_secure_view_merging = true
  194. _optimizer_join_elimination_enabled = true
  195. flashback_table_rpi = non_fbt
  196. _optimizer_cbqt_no_size_restriction = true
  197. _optimizer_enhanced_filter_push = true
  198. _optimizer_filter_pred_pullup = true
  199. _rowsrc_trace_level = 0
  200. _simple_view_merging = true
  201. _optimizer_rownum_pred_based_fkr = true
  202. _optimizer_better_inlist_costing = all
  203. _optimizer_self_induced_cache_cost = false
  204. _optimizer_min_cache_blocks = 10
  205. _optimizer_or_expansion = depth
  206. _optimizer_order_by_elimination_enabled = true
  207. _optimizer_outer_to_anti_enabled = true
  208. _selfjoin_mv_duplicates = true
  209. _dimension_skip_null = true
  210. _force_rewrite_enable = false
  211. _optimizer_star_tran_in_with_clause = true
  212. _optimizer_complex_pred_selectivity = true
  213. _optimizer_connect_by_cost_based = true
  214. _gby_hash_aggregation_enabled = true
  215. _globalindex_pnum_filter_enabled = true
  216. _px_minus_intersect = true
  217. _fix_control_key = 0
  218. _force_slave_mapping_intra_part_loads = false
  219. _force_tmp_segment_loads = false
  220. _query_mmvrewrite_maxpreds = 10
  221. _query_mmvrewrite_maxintervals = 5
  222. _query_mmvrewrite_maxinlists = 5
  223. _query_mmvrewrite_maxdmaps = 10
  224. _query_mmvrewrite_maxcmaps = 20
  225. _query_mmvrewrite_maxregperm = 512
  226. _query_mmvrewrite_maxmergedcmaps = 50
  227. _query_mmvrewrite_maxqryinlistvals = 500
  228. _disable_parallel_conventional_load = false
  229. _trace_virtual_columns = false
  230. _replace_virtual_columns = true
  231. _virtual_column_overload_allowed = true
  232. _kdt_buffering = true
  233. _first_k_rows_dynamic_proration = true
  234. _optimizer_sortmerge_join_inequality = true
  235. _optimizer_aw_stats_enabled = true
  236. _bloom_pruning_enabled = true
  237. result_cache_mode = MANUAL
  238. _px_ual_serial_input = true
  239. _optimizer_skip_scan_guess = false
  240. _enable_row_shipping = true
  241. _row_shipping_threshold = 80
  242. _row_shipping_explain = false
  243. transaction_isolation_level = read_commited
  244. _optimizer_distinct_elimination = true
  245. _optimizer_multi_level_push_pred = true
  246. _optimizer_group_by_placement = true
  247. _optimizer_rownum_bind_default = 10
  248. _enable_query_rewrite_on_remote_objs = true
  249. _optimizer_extended_cursor_sharing_rel = simple
  250. _optimizer_adaptive_cursor_sharing = true
  251. _direct_path_insert_features = 0
  252. _optimizer_improve_selectivity = true
  253. optimizer_use_pending_statistics = false
  254. _optimizer_enable_density_improvements = true
  255. _optimizer_aw_join_push_enabled = true
  256. _optimizer_connect_by_combine_sw = true
  257. _enable_pmo_ctas = 0
  258. _optimizer_native_full_outer_join = force
  259. _bloom_predicate_enabled = true
  260. _optimizer_enable_extended_stats = true
  261. _is_lock_table_for_ddl_wait_lock = 0
  262. _pivot_implementation_method = choose
  263. optimizer_capture_sql_plan_baselines = false
  264. optimizer_use_sql_plan_baselines = true
  265. _optimizer_star_trans_min_cost = 0
  266. _optimizer_star_trans_min_ratio = 0
  267. _with_subquery = OPTIMIZER
  268. _optimizer_fkr_index_cost_bias = 10
  269. _optimizer_use_subheap = true
  270. parallel_degree_policy = manual
  271. parallel_degree = 0
  272. parallel_min_time_threshold = 10
  273. _parallel_time_unit = 10
  274. _optimizer_or_expansion_subheap = true
  275. _optimizer_free_transformation_heap = true
  276. _optimizer_reuse_cost_annotations = true
  277. _result_cache_auto_size_threshold = 100
  278. _result_cache_auto_time_threshold = 1000
  279. _optimizer_nested_rollup_for_gset = 100
  280. _nlj_batching_enabled = 1
  281. parallel_query_default_dop = 0
  282. is_recur_flags = 0
  283. optimizer_use_invisible_indexes = false
  284. flashback_data_archive_internal_cursor = 0
  285. _optimizer_extended_stats_usage_control = 224
  286. _parallel_syspls_obey_force = true
  287. cell_offload_processing = true
  288. _rdbms_internal_fplib_enabled = false
  289. db_file_multiblock_read_count = 79
  290. _bloom_folding_enabled = true
  291. _mv_generalized_oj_refresh_opt = true
  292. cell_offload_compaction = ADAPTIVE
  293. parallel_degree_limit = 65535
  294. parallel_force_local = false
  295. total_cpu_count = 2
  296. cell_offload_plan_display = AUTO
  297. _optimizer_coalesce_subqueries = true
  298. _optimizer_fast_pred_transitivity = true
  299. _optimizer_fast_access_pred_analysis = true
  300. _optimizer_unnest_disjunctive_subq = true
  301. _optimizer_unnest_corr_set_subq = true
  302. _optimizer_distinct_agg_transform = true
  303. _aggregation_optimization_settings = 0
  304. _optimizer_connect_by_elim_dups = true
  305. _optimizer_eliminate_filtering_join = true
  306. _connect_by_use_union_all = true
  307. dst_upgrade_insert_conv = true
  308. advanced_queuing_internal_cursor = 0
  309. _optimizer_unnest_all_subqueries = true
  310. _bloom_predicate_pushdown_to_storage = true
  311. _bloom_vector_elements = 0
  312. _bloom_pushing_max = 524288
  313. parallel_autodop = 0
  314. parallel_ddldml = 0
  315. _parallel_cluster_cache_policy = adaptive
  316. _parallel_scalability = 50
  317. iot_internal_cursor = 0
  318. _optimizer_instance_count = 0
  319. _optimizer_connect_by_cb_whr_only = false
  320. _suppress_scn_chk_for_cqn = nosuppress_1466
  321. _optimizer_join_factorization = true
  322. _optimizer_use_cbqt_star_transformation = true
  323. _optimizer_table_expansion = true
  324. _and_pruning_enabled = true
  325. _deferred_constant_folding_mode = DEFAULT
  326. _optimizer_distinct_placement = true
  327. partition_pruning_internal_cursor = 0
  328. parallel_hinted = none
  329. _sql_compatibility = 0
  330. _optimizer_use_feedback = true
  331. _optimizer_try_st_before_jppd = true


  1. Bug Fix Control Environment       ---有的時候的查詢和bug fix有關係,他為了改一個bug會產生另外的問題。這時候你可以把這個bug fix disable掉,再去跑一下看看能不能好,如果好了的話,你會得到一個好的執行計劃。
  1.     fix 3834770 = 1
  2.     fix 3746511 = enabled
  3.     fix 4519016 = enabled
  4.     fix 3118776 = enabled
  5.     fix 4488689 = enabled
  6.     fix 2194204 = disabled
  7.     fix 2660592 = enabled
  8.     fix 2320291 = enabled
  9.     fix 2324795 = enabled
  10.     fix 4308414 = enabled
  11.     fix 3499674 = disabled
  12.     fix 4569940 = enabled
  13.     fix 4631959 = enabled
  14.     fix 4519340 = enabled
  15.     fix 4550003 = enabled
  16.     fix 1403283 = enabled
  17.     fix 4554846 = enabled
  18.     fix 4602374 = enabled
  19.     fix 4584065 = enabled
  20.     fix 4545833 = enabled
  21.     fix 4611850 = enabled
  22.     fix 4663698 = enabled
  23.     fix 4663804 = enabled
  24.     fix 4666174 = enabled
  25.     fix 4567767 = enabled
  26.     fix 4556762 = 15
  27.     fix 4728348 = enabled
  28.     fix 4708389 = enabled
  29.     fix 4175830 = enabled
  30.     fix 4752814 = enabled
  31.     fix 4583239 = enabled
  32.     fix 4386734 = enabled
  33.     fix 4887636 = enabled
  34.     fix 4483240 = enabled
  35.     fix 4872602 = disabled
  36.     fix 4711525 = enabled
  37.     fix 4545802 = enabled
  38.     fix 4605810 = enabled
  39.     fix 4704779 = enabled
  40.     fix 4900129 = enabled
  41.     fix 4924149 = enabled
  42.     fix 4663702 = enabled
  43.     fix 4878299 = enabled
  44.     fix 4658342 = enabled
  45.     fix 4881533 = enabled
  46.     fix 4676955 = enabled
  47.     fix 4273361 = enabled
  48.     fix 4967068 = enabled
  49.     fix 4969880 = disabled
  50.     fix 5005866 = enabled
  51.     fix 5015557 = enabled
  52.     fix 4705343 = enabled
  53.     fix 4904838 = enabled
  54.     fix 4716096 = enabled
  55.     fix 4483286 = disabled
  56.     fix 4722900 = enabled
  57.     fix 4615392 = enabled
  58.     fix 5096560 = enabled
  59.     fix 5029464 = enabled
  60.     fix 4134994 = enabled
  61.     fix 4904890 = enabled
  62.     fix 5104624 = enabled
  63.     fix 5014836 = enabled
  64.     fix 4768040 = enabled
  65.     fix 4600710 = enabled
  66.     fix 5129233 = enabled
  67.     fix 4595987 = enabled
  68.     fix 4908162 = enabled
  69.     fix 5139520 = enabled
  70.     fix 5084239 = enabled
  71.     fix 5143477 = disabled
  72.     fix 2663857 = enabled
  73.     fix 4717546 = enabled
  74.     fix 5240264 = disabled
  75.     fix 5099909 = enabled
  76.     fix 5240607 = enabled
  77.     fix 5195882 = enabled
  78.     fix 5220356 = enabled
  79.     fix 5263572 = enabled
  80.     fix 5385629 = enabled
  81.     fix 5302124 = enabled
  82.     fix 5391942 = enabled
  83.     fix 5384335 = enabled
  84.     fix 5482831 = enabled
  85.     fix 4158812 = enabled
  86.     fix 5387148 = enabled
  87.     fix 5383891 = enabled
  88.     fix 5466973 = enabled
  89.     fix 5396162 = enabled
  90.     fix 5394888 = enabled
  91.     fix 5395291 = enabled
  92.     fix 5236908 = enabled
  93.     fix 5509293 = enabled
  94.     fix 5449488 = enabled
  95.     fix 5567933 = enabled
  96.     fix 5570494 = enabled
  97.     fix 5288623 = enabled
  98.     fix 5505995 = enabled
  99.     fix 5505157 = enabled
  100.     fix 5112460 = enabled
  101.     fix 5554865 = enabled
  102.     fix 5112260 = enabled
  103.     fix 5112352 = enabled
  104.     fix 5547058 = enabled
  105.     fix 5618040 = enabled
  106.     fix 5585313 = enabled
  107.     fix 5547895 = enabled
  108.     fix 5634346 = enabled
  109.     fix 5620485 = enabled
  110.     fix 5483301 = enabled
  111.     fix 5657044 = enabled
  112.     fix 5694984 = enabled
  113.     fix 5868490 = enabled
  114.     fix 5650477 = enabled
  115.     fix 5611962 = enabled
  116.     fix 4279274 = enabled
  117.     fix 5741121 = enabled
  118.     fix 5714944 = enabled
  119.     fix 5391505 = enabled
  120.     fix 5762598 = enabled
  121.     fix 5578791 = enabled
  122.     fix 5259048 = enabled
  123.     fix 5882954 = enabled
  124.     fix 2492766 = enabled
  125.     fix 5707608 = enabled
  126.     fix 5891471 = enabled
  127.     fix 5884780 = enabled
  128.     fix 5680702 = enabled
  129.     fix 5371452 = enabled
  130.     fix 5838613 = enabled
  131.     fix 5949981 = enabled
  132.     fix 5624216 = enabled
  133.     fix 5741044 = enabled
  134.     fix 5976822 = enabled
  135.     fix 6006457 = enabled
  136.     fix 5872956 = enabled
  137.     fix 5923644 = enabled
  138.     fix 5943234 = enabled
  139.     fix 5844495 = enabled
  140.     fix 4168080 = enabled
  141.     fix 6020579 = enabled
  142.     fix 5842686 = disabled
  143.     fix 5996801 = enabled
  144.     fix 5593639 = enabled
  145.     fix 6133948 = enabled
  146.     fix 3151991 = enabled
  147.     fix 6146906 = enabled
  148.     fix 6239909 = enabled
  149.     fix 6267621 = enabled
  150.     fix 5909305 = enabled
  151.     fix 6279918 = enabled
  152.     fix 6141818 = enabled
  153.     fix 6151963 = enabled
  154.     fix 6251917 = enabled
  155.     fix 6282093 = enabled
  156.     fix 6119510 = enabled
  157.     fix 6119382 = enabled
  158.     fix 3801750 = enabled
  159.     fix 5705630 = disabled
  160.     fix 5944076 = enabled
  161.     fix 5406763 = enabled
  162.     fix 6070954 = enabled
  163.     fix 6282944 = enabled
  164.     fix 6138746 = enabled
  165.     fix 6082745 = enabled
  166.     fix 3426050 = enabled
  167.     fix 599680 = enabled
  168.     fix 6062266 = enabled
  169.     fix 6087237 = enabled
  170.     fix 6122894 = enabled
  171.     fix 6377505 = enabled
  172.     fix 5893768 = enabled
  173.     fix 6163564 = enabled
  174.     fix 6073325 = enabled
  175.     fix 6188881 = enabled
  176.     fix 6007259 = enabled
  177.     fix 6239971 = enabled
  178.     fix 5284200 = disabled
  179.     fix 6042205 = enabled
  180.     fix 6051211 = enabled
  181.     fix 6434668 = enabled
  182.     fix 6438752 = enabled
  183.     fix 5936366 = enabled
  184.     fix 6439032 = enabled
  185.     fix 6438892 = enabled
  186.     fix 6006300 = enabled
  187.     fix 5947231 = enabled
  188.     fix 5416118 = 1
  189.     fix 6365442 = 1
  190.     fix 6239039 = enabled
  191.     fix 6502845 = enabled
  192.     fix 6913094 = enabled
  193.     fix 6029469 = enabled
  194.     fix 5919513 = enabled
  195.     fix 6057611 = enabled
  196.     fix 6469667 = enabled
  197.     fix 6608941 = disabled
  198.     fix 6368066 = enabled
  199.     fix 6329318 = enabled
  200.     fix 6656356 = enabled
  201.     fix 4507997 = enabled
  202.     fix 6671155 = enabled
  203.     fix 6694548 = enabled
  204.     fix 6688200 = enabled
  205.     fix 6612471 = enabled
  206.     fix 6708183 = disabled
  207.     fix 6326934 = enabled
  208.     fix 6520717 = disabled
  209.     fix 6714199 = enabled
  210.     fix 6681545 = enabled
  211.     fix 6748058 = enabled
  212.     fix 6167716 = enabled
  213.     fix 6674254 = enabled
  214.     fix 6468287 = enabled
  215.     fix 6503543 = enabled
  216.     fix 6808773 = disabled
  217.     fix 6766962 = enabled
  218.     fix 6120483 = enabled
  219.     fix 6670551 = enabled
  220.     fix 6771838 = enabled
  221.     fix 6626018 = disabled
  222.     fix 6530596 = enabled
  223.     fix 6778642 = enabled
  224.     fix 6699059 = enabled
  225.     fix 6376551 = enabled
  226.     fix 6429113 = enabled
  227.     fix 6782437 = enabled
  228.     fix 6776808 = enabled
  229.     fix 6765823 = enabled
  230.     fix 6768660 = enabled
  231.     fix 6782665 = enabled
  232.     fix 6610822 = enabled
  233.     fix 6514189 = enabled
  234.     fix 6818410 = enabled
  235.     fix 6827696 = enabled
  236.     fix 6773613 = enabled
  237.     fix 5902962 = enabled
  238.     fix 6956212 = enabled
  239.     fix 3056297 = enabled
  240.     fix 6440977 = disabled
  241.     fix 6972291 = disabled
  242.     fix 6904146 = enabled
  243.     fix 8580883 = enabled
  244.     fix 5892599 = disabled
  245.     fix 8609714 = enabled
  246.     fix 8514561 = enabled
  247.     fix 8619631 = disabled
接下來就是真正的做transformation,前面是告訴你現有的環境,接下來是真正的工作

點選(此處)摺疊或開啟

  1. ***************************************
  2.   PARAMETERS IN OPT_PARAM HINT
  3.   ****************************
  4. ***************************************
  5. Column Usage Monitoring is ON: tracking level = 1
  6. ***************************************

  7. Considering Query Transformations on query block SEL$1 (#0)
  8. **************************
  9. Query transformations (QT)
  10. **************************
  11. JF: Checking validity of join factorization for query block SEL$1 (#0)     ------發現沒什麼可以做的就過去了。
  12. JF: Bypassed: not a UNION or UNION-ALL query block.                           沒有union all的東西所以跳過
  13. ST: not valid since star transformation parameter is FALSE               -----這個也沒什麼必要做
  14. TE: Checking validity of table expansion for query block SEL$1 (#0)
  15. TE: Bypassed: No partitioned table in query block.                      ----根本就沒有paratition 表所以也沒做。
  16. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.  不是複雜view 所以也沒做
  17. CBQT: Validity checks failed for 4usswnngj0jdx.
  18. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  19. *************************
  20. Common Subexpression elimination (CSE)
  21. *************************
  22. CSE: CSE not performed on query block SEL$1 (#0).
  23. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
  24. ***************************
  25. Order-by elimination (OBYE)
  26. ***************************
  27. OBYE: OBYE bypassed: no order by to eliminate.
  28. JE: Considering Join Elimination on query block SEL$1 (#0)
  29. *************************
  30. Join Elimination (JE)               ----這一步看看能不能把一些join消除掉,也沒有能消除的join
  31. *************************
  32. SQL:******* UNPARSED QUERY IS *******
  33. SELECT "E"."ENAME" "ENAME" FROM "SCOTT"."EMP" "E","SCOTT"."EMP" "M" WHERE "E"."MGR"="M"."EMPNO" AND "M"."DEPTNO"=10 AND "E"."DEPTNO"="M"."DEPTNO" AND "E"."SAL">1500 AND "M"."SAL">"E"."SAL"
  34. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
  35. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
  36. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
  37. SQL:******* UNPARSED QUERY IS *******
  38. SELECT "E"."ENAME" "ENAME" FROM "SCOTT"."EMP" "E","SCOTT"."EMP" "M" WHERE "E"."MGR"="M"."EMPNO" AND "M"."DEPTNO"=10 AND "E"."DEPTNO"="M"."DEPTNO" AND "E"."SAL">1500 AND "M"."SAL">"E"."SAL"
  39. Query block SEL$1 (#0) unchanged
  40. CVM: Considering view merge in query block SEL$1 (#0)
  41. JE: Considering Join Elimination on query block SEL$1 (#0)
  42. *************************
  43. Join Elimination (JE)
  44. *************************
  45. SQL:******* UNPARSED QUERY IS *******
  46. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  47. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
  48. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
  49. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
  50. SQL:******* UNPARSED QUERY IS *******
  51. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  52. Query block SEL$1 (#0) unchanged
  53. query block SEL$1 (#0) unchanged
  54. Considering Query Transformations on query block SEL$1 (#0)
  55. **************************
  56. Query transformations (QT)
  57. **************************
  58. JF: Checking validity of join factorization for query block SEL$1 (#0)
  59. JF: Bypassed: not a UNION or UNION-ALL query block.
  60. ST: not valid since star transformation parameter is FALSE
  61. TE: Checking validity of table expansion for query block SEL$1 (#0)
  62. TE: Bypassed: No partitioned table in query block.
  63. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
  64. CBQT: Validity checks failed for 4usswnngj0jdx.
  65. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  66. *************************
  67. Common Subexpression elimination (CSE)
  68. *************************
  69. CSE: CSE not performed on query block SEL$1 (#0).
  70. SU: Considering subquery unnesting in query block SEL$1 (#0)
  71. ********************
  72. Subquery Unnest (SU)
  73. ********************
  74. SJC: Considering set-join conversion in query block SEL$1 (#0)
  75. *************************
  76. Set-Join Conversion (SJC)
  77. *************************
  78. SJC: not performed
  79. JE: Considering Join Elimination on query block SEL$1 (#0)
  80. *************************
  81. Join Elimination (JE)
  82. *************************
  83. SQL:******* UNPARSED QUERY IS *******
  84. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  85. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
  86. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
  87. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
  88. SQL:******* UNPARSED QUERY IS *******
  89. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  90. Query block SEL$1 (#0) unchanged
  91. PM: Considering predicate move-around in query block SEL$1 (#0)
  92. **************************
  93. Predicate Move-Around (PM)
  94. **************************
  95. PM: PM bypassed: Outer query contains no views.
  96. PM: PM bypassed: Outer query contains no views.
  97. query block SEL$1 (#0) unchanged
  98. FPD: Considering simple filter push in query block SEL$1 (#0)                       --------------FPD這裡做了一些事情,產生了一個可以傳遞的謂詞
  99. E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  100. try to generate transitive predicate from check constraints for query block SEL$1 (#0)
  101. finally: E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL AND M.SAL>1500 AND E.DEPTNO=10

  102. FPD: transitive predicates are generated in query block SEL$1 (#0)
  103. E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL AND M.SAL>1500 AND E.DEPTNO=10
  104. apadrv-start sqlid=5576332259131147709
  105.   :
  106.     call(in-use=2208, alloc=16360), compile(in-use=61336, alloc=63432), execution(in-use=2088, alloc=4060)

  107. *******************************************
  108. Peeked values of the binds in SQL statement
  109. *******************************************                                                         ------------最後把sql變成這樣了
                    


SELECT E.ENAME FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO 
AND M.DEPTNO=10 
AND E.DEPTNO=M.DEPTNO 
AND E.SAL >1500 
AND M.SAL>E.SAL 
AND M.SAL>1500 
AND E.DEPTNO=10;    這個是oracle給你加上去的。這樣>1500時候,就過濾很多了,減少匹配


  1. Final query after transformations:******* UNPARSED QUERY IS *******
  2. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL AND M.SAL>1500 AND E.DEPTNO=10
  3. kkoqbc: optimizing query block SEL$1 (#0)
  4.         
  5.         :
  6.     call(in-use=2436, alloc=16360), compile(in-use=62156, alloc=63432), execution(in-use=2088, alloc=4060)


  1. kkoqbc-subheap (create addr=0x705628)
  2. ****************
  3. QUERY BLOCK TEXT
  4. ****************
  5. select /*sample 1*/e.ename
  6. from emp e, emp m
  7. where e.mgr=m.empno
  8. and m.deptno = 10
  9. and e.deptno = m.deptno
  10. and e.sal>1500
  11. and m.sal>e.sal
  12. ---------------------
  13. QUERY BLOCK SIGNATURE
  14. ---------------------
  15. signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
  16.   fro(0): flg=0 objn=73181 hint_alias=E@SEL$1
  17.   fro(1): flg=0 objn=73181 hint_alias=M@SEL$1

  18. -----------------------------
  19. SYSTEM STATISTICS INFORMATION
  20. -----------------------------
  21.   Using NOWORKLOAD Stats
  22.   CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
  23.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  24.   IOSEEKTIM: 10 milliseconds (default is 10)
  25.   MBRC: -1 blocks (default is 8)

  26. ***************************************
  27. BASE STATISTICAL INFORMATION                                                -----做cost,把表的統計資訊拿出來,考慮各種路徑
  28. ***********************
  29. Table Stats::                                                 
  30.   Table: EMP Alias: M
  31.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
  32. Index Stats::
  33.   Index: PK_EMP Col#: 1
  34.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  35. ***********************
  36. Table Stats::
  37.   Table: EMP Alias: E
  38.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
  39. Index Stats::
  40.   Index: PK_EMP Col#: 1
  41.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  42. Access path analysis for EMP
  43. ***************************************
  44. SINGLE TABLE ACCESS PATH
  45.   Single Table Cardinality Estimation for EMP[E]                           --------訪問單表的時候,我怎麼訪問是最快的。

  46.   Table: EMP Alias: E
  47.     Card: Original: 14.000000 Rounded: 4 Computed: 3.74 Non Adjusted: 3.74
  48.   Access Path: TableScan                                                  -------因為沒有索引嗎,他考慮的唯一方式就是全表掃描
  49.     Cost: 3.00 Resp: 3.00 Degree: 0
  50.       Cost_io: 3.00 Cost_cpu: 40677
  51.       Resp_io: 3.00 Resp_cpu: 40677
  52.   Best:: AccessPath: TableScan
  53.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 3.74 Bytes: 0

  54. Access path analysis for EMP
  55. ***************************************
  56. SINGLE TABLE ACCESS PATH
  57.   Single Table Cardinality Estimation for EMP[M]

  58.   Table: EMP Alias: M
  59.     Card: Original: 14.000000 Rounded: 4 Computed: 4.03 Non Adjusted: 4.03
  60.   Access Path: TableScan
  61.     Cost: 3.00 Resp: 3.00 Degree: 0
  62.       Cost_io: 3.00 Cost_cpu: 40601
  63.       Resp_io: 3.00 Resp_cpu: 40601
  64.   Best:: AccessPath: TableScan
  65.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.03 Bytes: 0

  66. ***************************************


  67. OPTIMIZER STATISTICS AND COMPUTATIONS
  68. ***************************************
  69. GENERAL PLANS
  70. ***************************************


  1. Considering cardinality-based initial join order.                        -----然後考慮怎麼join,怎麼join最好呢
  2. Permutations for Starting Table :0
  3. Join order[1]: EMP[E]#0 EMP[M]#1

  4. ***************
  5. Now joining: EMP[M]#1
  6. ***************
  7. NL Join                            ---------考慮nest loop花銷,這種join會考慮用哪個表做驅動表,因為他需要把小表作為驅動表的。
  8.   Outer table: Card: 3.74 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
  9. Access path analysis for EMP
  10.   Inner table: EMP Alias: M      ---inner table 是M這張表
  11.   Access Path: TableScan
  12.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
  13.       Cost_io: 10.00 Cost_cpu: 203079
  14.       Resp_io: 10.00 Resp_cpu: 203079
  15.   Access Path: index (UniqueScan)
  16.     Index: PK_EMP
  17.     resc_io: 1.00 resc_cpu: 8528
  18.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
  19.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
  20.       Cost_io: 7.00 Cost_cpu: 74790
  21.       Resp_io: 7.00 Resp_cpu: 74790
  22.   Access Path: index (AllEqUnique)
  23.     Index: PK_EMP
  24.     resc_io: 1.00 resc_cpu: 8528
  25.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
  26.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
  27.       Cost_io: 7.00 Cost_cpu: 74790
  28.       Resp_io: 7.00 Resp_cpu: 74790

  29.   Best NL cost: 7.00
  30.           resc: 7.00 resc_io: 7.00 resc_cpu: 74790
  31.           resp: 7.00 resp_io: 7.00 resc_cpu: 74790
  32. Join Card: 1.144145 = = outer (3.744856) * inner (4.032922) * sel (0.075758)
  33. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 3.744856, inner: 4.032922, sel: 0.075758
  34. Join Card - Rounded: 1 Computed: 1.14
  35.   Outer table: EMP Alias: E
  36.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
  37.   Inner table: EMP Alias: M
  38.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
  39.     using dmeth: 2 #groups: 1
  40.     SORT ressource Sort statistics
  41.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  42.       Degree: 1
  43.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
  44.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  45.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  46.       Total Temp space used: 0
  47.     SORT ressource Sort statistics
  48.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  49.       Degree: 1
  50.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
  51.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  52.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  53.       Total Temp space used: 0
  54.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
  55. SM Join                                                  -----------sort merge join
  56.   SM cost: 8.00
  57.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
  58.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
  59.   Outer table: EMP Alias: E
  60.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
  61.   Inner table: EMP Alias: M
  62.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
  63.     using dmeth: 2 #groups: 1
  64.     Cost per ptn: 0.50 #ptns: 1
  65.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
  66.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
  67. HA Join                                                        ------------hash join最好cost為6.50
  68.   HA cost: 6.50
  69.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
  70.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
  71. Best:: JoinMethod: Hash
  72.        Cost: 6.50 Degree: 1 Resp: 6.50 Card: 1.14 Bytes: 31
  73. ***********************
  74. Best so far: Table#: 0 cost: 3.0013 card: 3.7449 bytes: 80
  75.               Table#: 1 cost: 6.5025 card: 1.1441 bytes: 31
  76. ***********************
  77. Join order[2]: EMP[M]#1 EMP[E]#0

  78. ***************
  79. Now joining: EMP[E]#0
  80. ***************

  81. NL Join
  82.   Outer table: Card: 4.03 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 11
  83. Access path analysis for EMP
  84.   Inner table: EMP Alias: E
  85.   Access Path: TableScan
  86.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
  87.       Cost_io: 10.00 Cost_cpu: 203325
  88.       Resp_io: 10.00 Resp_cpu: 203325

  89.   Best NL cost: 10.01
  90.           resc: 10.01 resc_io: 10.00 resc_cpu: 203325
  91.           resp: 10.01 resp_io: 10.00 resc_cpu: 203325
  92. Join Card: 1.144145 = = outer (4.032922) * inner (3.744856) * sel (0.075758)
  93. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 4.032922, inner: 3.744856, sel: 0.075758
  94. Join Card - Rounded: 1 Computed: 1.14
  95.   Outer table: EMP Alias: M
  96.     resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
  97.   Inner table: EMP Alias: E
  98.     resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
  99.     using dmeth: 2 #groups: 1
  100.     SORT ressource Sort statistics
  101.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  102.       Degree: 1
  103.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
  104.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  105.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  106.       Total Temp space used: 0
  107.     SORT ressource Sort statistics
  108.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  109.       Degree: 1
  110.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
  111.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  112.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  113.       Total Temp space used: 0
  114.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
  115. SM Join
  116.   SM cost: 8.00
  117.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
  118.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
  119.   Outer table: EMP Alias: M
  120.     resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
  121.   Inner table: EMP Alias: E
  122.     resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
  123.     using dmeth: 2 #groups: 1
  124.     Cost per ptn: 0.50 #ptns: 1
  125.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
  126.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
  127. HA Join
  128.   HA cost: 6.50
  129.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
  130.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
  131. Join order aborted: cost > best plan cost
  132. ***********************
  133. (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000

  134. *********************************
  135. Number of join permutations tried: 2
  136. *********************************
  137. Consider using bloom filter between E[EMP] and M[EMP]
  138. kkoBloomFilter: join (lcdn:4 rcdn:4 jcdn:1 limit:8)
  139. Computing bloom ndv for creator:E[EMP] ccdn:3.7 and user:M[EMP] ucdn:4.0
  140. kkopqComputeBloomNdv: predicate (bndv:6 ndv:6) and (bndv:14 ndv:5)
  141. kkopqComputeBloomNdv: predicate (bndv:3 ndv:3) and (bndv:3 ndv:3)
  142. kkopqComputeBloomNdv: predicate (bndv:12 ndv:3) and (bndv:12 ndv:4)
  143. kkopqComputeBloomNdv: pred cnt:6 ndv:4 reduction:1
  144. kkoBloomFilter: join ndv:0 reduction:0.928571 (limit:0.500000) rejected because distinct value ratio
  145. (newjo-save) [1 0 ]
  146. Trying or-Expansion on query block SEL$1 (#0)
  147. Transfer Optimizer annotations for query block SEL$1 (#0)
  148. id=0 frofand predicate=E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500
  149. id=0 frofkksm[i] (sort-merge/hash) predicate=E.MGR=M.EMPNO
  150. id=0 frofkksm[i] (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO
  151. id=0 frosand (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO AND E.MGR=M.EMPNO
  152. id=0 frofand predicate=M.DEPTNO=10 AND M.SAL>1500
  153. id=0 frojand (sort-merge/hash) predicate=M.SAL>E.SAL
  154. Final cost for query block SEL$1 (#0) - All Rows Plan:                              ------最後給你的統計
  155.   Best join order: 1
  156.   Cost: 6.5025 Degree: 1 Card: 1.0000 Bytes: 31
  157.   Resc: 6.5025 Resc_io: 6.0000 Resc_cpu: 16258612
  158.   Resp: 6.5025 Resp_io: 6.0000 Resc_cpu: 16258612
  159. kkoqbc-subheap (delete addr=0x705628, in-use=24852, alloc=25480)
  160. kkoqbc-end:
  161.         :
  162.     call(in-use=13332, alloc=49168), compile(in-use=63592, alloc=67556), execution(in-use=2088, alloc=4060)

  163. kkoqbc: finish optimizing query block SEL$1 (#0)
  164. apadrv-end
  165.           :
  166.     call(in-use=13332, alloc=49168), compile(in-use=64228, alloc=67556), execution(in-use=2088, alloc=4060)


  167. Starting SQL statement dump

  168. user_id=84 user_name=SCOTT module=SQL*Plus action=
  169. sql_id=4usswnngj0jdx plan_hash_value=-656709420 problem_type=3
  170. ----- Current SQL Statement for this session (sql_id=4usswnngj0jdx) -----
  171. select /*sample 1*/e.ename
  172. from emp e, emp m
  173. where e.mgr=m.empno
  174. and m.deptno = 10
  175. and e.deptno = m.deptno
  176. and e.sal>1500
  177. and m.sal>e.sal
  178. sql_text_length=138
  179. sql=select /*sample 1*/e.ename
  180. from emp e, emp m
  181. where e.mgr=m.empno
  182. and m.deptno = 10
  183. and e.deptno = m.deptno
  184. and e.sal>1500
  185. and m.sal>e.sal
  186. ----- Explain Plan Dump -----
  187. ----- Plan Table -----                                                -------給你執行計劃
  188.  
  189. ============
  190. Plan Table
  191. ============
  192. --------------------------------------+-----------------------------------+
  193. | Id | Operation | Name | Rows | Bytes | Cost | Time |
  194. --------------------------------------+-----------------------------------+
  195. | 0 | SELECT STATEMENT | | | | 7 | |
  196. | 1 | HASH JOIN | | 1 | 31 | 7 | 00:00:01 |
  197. | 2 | TABLE ACCESS FULL | EMP | 4 | 80 | 3 | 00:00:01 |
  198. | 3 | TABLE ACCESS FULL | EMP | 4 | 44 | 3 | 00:00:01 |
  199. --------------------------------------+-----------------------------------+
  200. Predicate Information:
  201. ----------------------
  202. 1 - access(E.MGR=M.EMPNO AND E.DEPTNO=M.DEPTNO)
  203. 1 - filter(M.SAL>E.SAL)
  204. 2 - filter((E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500))
  205. 3 - filter((M.DEPTNO=10 AND M.SAL>1500))
  206.  
  207. Content of other_xml column
  208. ===========================
  209.   db_version : 11.2.0.1
  210.   parse_schema : SCOTT
  211.   plan_hash : 3638257876
  212.   plan_hash_2 : 4140062955
  213.   Outline Data:                                 這是outline data,一般情況可以用outline data 固定執行計劃
  214.   /*+
  215.     BEGIN_OUTLINE_DATA
  216.       IGNORE_OPTIM_EMBEDDED_HINTS
  217.       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
  218.       DB_VERSION('11.2.0.1')
  219.       ALL_ROWS
  220.       OUTLINE_LEAF(@"SEL$1")
  221.       FULL(@"SEL$1" "E"@"SEL$1")               ---full table scan
  222.       FULL(@"SEL$1" "M"@"SEL$1")                    -----這裡就是這個query block別名M的full table scan
  223.       LEADING(@"SEL$1" "E"@"SEL$1" "M"@"SEL$1") -----這個是哪個放在前面,哪個放在後面。
  224.       USE_HASH(@"SEL$1" "M"@"SEL$1")
  225.     END_OUTLINE_DATA
  226.   */
  227.  



後面的是重新提了一下

  1. *** 2014-11-01 01:51:05.586
  2. Registered qb: SEL$1 0x708b6c (PARSER)
  3. ---------------------
  4. QUERY BLOCK SIGNATURE
  5. ---------------------
  6.   signature (): qb_name=SEL$1 nbfros=2 flg=0
  7.     fro(0): flg=4 objn=73181 hint_alias="E"@"SEL$1"
  8.     fro(1): flg=4 objn=73181 hint_alias="M"@"SEL$1"

  9. SPM: statement not found in SMB

  10. **************************
  11. Automatic degree of parallelism (ADOP)
  12. **************************
  13. Automatic degree of parallelism is disabled: Parameter.

  14. PM: Considering predicate move-around in query block SEL$1 (#0)
  15. **************************
  16. Predicate Move-Around (PM)
  17. **************************
  18. OPTIMIZER INFORMATION

  19. ******************************************
  20. ----- Current SQL Statement for this session (sql_id=g126tztfnzvrd) -----
  21. select /*sample 11*/e.ename
  22. from emp e, emp m
  23. where e.mgr=m.empno
  24. and m.deptno = 10
  25. and e.deptno = m.deptno
  26. and e.sal>1500
  27. and m.sal>e.sal
  28. *******************************************
  29. Legend
  30. The following abbreviations are used by optimizer trace.
  31. CBQT - cost-based query transformation
  32. JPPD - join predicate push-down
  33. OJPPD - old-style (non-cost-based) JPPD
  34. FPD - filter push-down
  35. PM - predicate move-around
  36. CVM - complex view merging
  37. SPJ - select-project-join
  38. SJC - set join conversion
  39. SU - subquery unnesting
  40. OBYE - order by elimination
  41. OST - old style star transformation
  42. ST - new (cbqt) star transformation
  43. CNT - count(col) to count(*) transformation
  44. JE - Join Elimination
  45. JF - join factorization
  46. SLP - select list pruning
  47. DP - distinct placement
  48. qb - query block
  49. LB - leaf blocks
  50. DK - distinct keys
  51. LB/K - average number of leaf blocks per key
  52. DB/K - average number of data blocks per key
  53. CLUF - clustering factor
  54. NDV - number of distinct values
  55. Resp - response cost
  56. Card - cardinality
  57. Resc - resource cost
  58. NL - nested loops (join)
  59. SM - sort merge (join)
  60. HA - hash (join)
  61. CPUSPEED - CPU Speed
  62. IOTFRSPEED - I/O transfer speed
  63. IOSEEKTIM - I/O seek time
  64. SREADTIM - average single block read time
  65. MREADTIM - average multiblock read time
  66. MBRC - average multiblock read count
  67. MAXTHR - maximum I/O system throughput
  68. SLAVETHR - average slave I/O throughput
  69. dmeth - distribution method
  70.   1: no partitioning required
  71.   2: value partitioned
  72.   4: right is random (round-robin)
  73.   128: left is random (round-robin)
  74.   8: broadcast right and partition left
  75.   16: broadcast left and partition right
  76.   32: partition left using partitioning of right
  77.   64: partition right using partitioning of left
  78.   256: run the join in serial
  79.   0: invalid distribution method
  80. sel - selectivity
  81. ptn - partition
  82. ***************************************
  83. PARAMETERS USED BY THE OPTIMIZER
  84. ********************************
  85.   *************************************
  86.   PARAMETERS WITH ALTERED VALUES
  87.   ******************************
  88. Compilation Environment Dump
  89. parallel_max_degree = 4


  90.   ***************************************
  91.   PARAMETERS IN OPT_PARAM HINT
  92.   ****************************
  93. ***************************************
  94. Column Usage Monitoring is ON: tracking level = 1
  95. ***************************************

  96. Considering Query Transformations on query block SEL$1 (#0)
  97. **************************
  98. Query transformations (QT)
  99. **************************
  100. JF: Checking validity of join factorization for query block SEL$1 (#0)
  101. JF: Bypassed: not a UNION or UNION-ALL query block.
  102. ST: not valid since star transformation parameter is FALSE
  103. TE: Checking validity of table expansion for query block SEL$1 (#0)
  104. TE: Bypassed: No partitioned table in query block.
  105. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
  106. CBQT: Validity checks failed for g126tztfnzvrd.
  107. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  108. *************************
  109. Common Subexpression elimination (CSE)
  110. *************************
  111. CSE: CSE not performed on query block SEL$1 (#0).
  112. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
  113. ***************************
  114. Order-by elimination (OBYE)
  115. ***************************
  116. OBYE: OBYE bypassed: no order by to eliminate.
  117. JE: Considering Join Elimination on query block SEL$1 (#0)
  118. *************************
  119. Join Elimination (JE)
  120. *************************
  121. SQL:******* UNPARSED QUERY IS *******
  122. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  123. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
  124. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
  125. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
  126. SQL:******* UNPARSED QUERY IS *******
  127. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  128. Query block SEL$1 (#0) unchanged
  129. CVM: Considering view merge in query block SEL$1 (#0)
  130. JE: Considering Join Elimination on query block SEL$1 (#0)
  131. *************************
  132. Join Elimination (JE)
  133. *************************
  134. SQL:******* UNPARSED QUERY IS *******
  135. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  136. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
  137. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
  138. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
  139. SQL:******* UNPARSED QUERY IS *******
  140. SELECT \"E\".ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  141. Query block SEL$1 (#0) unchanged
  142. query block SEL$1 (#0) unchanged
  143. Considering Query Transformations on query block SEL$1 (#0)
  144. **************************
  145. Query transformations (QT)
  146. **************************
  147. JF: Checking validity of join factorization for query block SEL$1 (#0)
  148. JF: Bypassed: not a UNION or UNION-ALL query block.
  149. ST: not valid since star transformation parameter is FALSE
  150. TE: Checking validity of table expansion for query block SEL$1 (#0)
  151. TE: Bypassed: No partitioned table in query block.
  152. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
  153. CBQT: Validity checks failed for g126tztfnzvrd.
  154. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  155. *************************
  156. Common Subexpression elimination (CSE)
  157. *************************
  158. CSE: CSE not performed on query block SEL$1 (#0).
  159. SU: Considering subquery unnesting in query block SEL$1 (#0)
  160. ********************
  161. Subquery Unnest (SU)
  162. ********************
  163. SJC: Considering set-join conversion in query block SEL$1 (#0)
  164. *************************
  165. Set-Join Conversion (SJC)
  166. *************************
  167. SJC: not performed
  168. JE: Considering Join Elimination on query block SEL$1 (#0)
  169. *************************
  170. Join Elimination (JE)
  171. *************************
  172. SQL:******* UNPARSED QUERY IS *******
  173. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  174. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
  175. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
  176. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
  177. SQL:******* UNPARSED QUERY IS *******
  178. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  179. Query block SEL$1 (#0) unchanged
  180. PM: Considering predicate move-around in query block SEL$1 (#0)
  181. **************************
  182. Predicate Move-Around (PM)
  183. **************************
  184. PM: PM bypassed: Outer query contains no views.
  185. PM: PM bypassed: Outer query contains no views.
  186. query block SEL$1 (#0) unchanged
  187. FPD: Considering simple filter push in query block SEL$1 (#0)
  188. E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
  189. try to generate transitive predicate from check constraints for query block SEL$1 (#0)
  190. finally: E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL AND M.SAL>1500 AND E.DEPTNO=10

  191. FPD: transitive predicates are generated in query block SEL$1 (#0)
  192. E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL AND M.SAL>1500 AND E.DEPTNO=10
  193. apadrv-start sqlid=17332342852445794029
  194.   :
  195.     call(in-use=2208, alloc=16360), compile(in-use=61336, alloc=63432), execution(in-use=2088, alloc=4060)

  196. *******************************************
  197. Peeked values of the binds in SQL statement
  198. *******************************************

  199. Final query after transformations:******* UNPARSED QUERY IS *******
  200. SELECT E.ENAME ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL AND M.SAL>1500 AND E.DEPTNO=10
  201. kkoqbc: optimizing query block SEL$1 (#0)
  202.         
  203.         :
  204.     call(in-use=2436, alloc=16360), compile(in-use=62156, alloc=63432), execution(in-use=2088, alloc=4060)

  205. kkoqbc-subheap (create addr=0x705628)
  206. ****************
  207. QUERY BLOCK TEXT
  208. ****************
  209. select /*sample 11*/e.ename
  210. from emp e, emp m
  211. where e.mgr=m.empno
  212. and m.deptno = 10
  213. and e.deptno = m.deptno
  214. and e.sal>1500
  215. and m.sal>e.sal
  216. ---------------------
  217. QUERY BLOCK SIGNATURE
  218. ---------------------
  219. signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
  220.   fro(0): flg=0 objn=73181 hint_alias=E@SEL$1
  221.   fro(1): flg=0 objn=73181 hint_alias=M@SEL$1

  222. -----------------------------
  223. SYSTEM STATISTICS INFORMATION
  224. -----------------------------
  225.   Using NOWORKLOAD Stats
  226.   CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
  227.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  228.   IOSEEKTIM: 10 milliseconds (default is 10)
  229.   MBRC: -1 blocks (default is 8)

  230. ***************************************
  231. BASE STATISTICAL INFORMATION
  232. ***********************
  233. Table Stats::
  234.   Table: EMP Alias: M
  235.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
  236. Index Stats::
  237.   Index: PK_EMP Col#: 1
  238.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  239. ***********************
  240. Table Stats::
  241.   Table: EMP Alias: E
  242.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
  243. Index Stats::
  244.   Index: PK_EMP Col#: 1
  245.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  246. Access path analysis for EMP
  247. ***************************************
  248. SINGLE TABLE ACCESS PATH
  249.   Single Table Cardinality Estimation for EMP[E]

  250.   Table: EMP Alias: E
  251.     Card: Original: 14.000000 Rounded: 4 Computed: 3.74 Non Adjusted: 3.74
  252.   Access Path: TableScan
  253.     Cost: 3.00 Resp: 3.00 Degree: 0
  254.       Cost_io: 3.00 Cost_cpu: 40677
  255.       Resp_io: 3.00 Resp_cpu: 40677
  256.   Best:: AccessPath: TableScan
  257.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 3.74 Bytes: 0

  258. Access path analysis for EMP
  259. ***************************************
  260. SINGLE TABLE ACCESS PATH
  261.   Single Table Cardinality Estimation for EMP[M]

  262.   Table: EMP Alias: M
  263.     Card: Original: 14.000000 Rounded: 4 Computed: 4.03 Non Adjusted: 4.03
  264.   Access Path: TableScan
  265.     Cost: 3.00 Resp: 3.00 Degree: 0
  266.       Cost_io: 3.00 Cost_cpu: 40601
  267.       Resp_io: 3.00 Resp_cpu: 40601
  268.   Best:: AccessPath: TableScan
  269.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.03 Bytes: 0

  270. ***************************************


  271. OPTIMIZER STATISTICS AND COMPUTATIONS
  272. ***************************************
  273. GENERAL PLANS
  274. ***************************************
  275. Considering cardinality-based initial join order.
  276. Permutations for Starting Table :0
  277. Join order[1]: EMP[E]#0 EMP[M]#1

  278. ***************
  279. Now joining: EMP[M]#1
  280. ***************
  281. NL Join
  282.   Outer table: Card: 3.74 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
  283. Access path analysis for EMP
  284.   Inner table: EMP Alias: M
  285.   Access Path: TableScan
  286.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
  287.       Cost_io: 10.00 Cost_cpu: 203079
  288.       Resp_io: 10.00 Resp_cpu: 203079
  289.   Access Path: index (UniqueScan)
  290.     Index: PK_EMP
  291.     resc_io: 1.00 resc_cpu: 8528
  292.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
  293.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
  294.       Cost_io: 7.00 Cost_cpu: 74790
  295.       Resp_io: 7.00 Resp_cpu: 74790
  296.   Access Path: index (AllEqUnique)
  297.     Index: PK_EMP
  298.     resc_io: 1.00 resc_cpu: 8528
  299.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
  300.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
  301.       Cost_io: 7.00 Cost_cpu: 74790
  302.       Resp_io: 7.00 Resp_cpu: 74790

  303.   Best NL cost: 7.00
  304.           resc: 7.00 resc_io: 7.00 resc_cpu: 74790
  305.           resp: 7.00 resp_io: 7.00 resc_cpu: 74790
  306. Join Card: 1.144145 = = outer (3.744856) * inner (4.032922) * sel (0.075758)
  307. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 3.744856, inner: 4.032922, sel: 0.075758
  308. Join Card - Rounded: 1 Computed: 1.14
  309.   Outer table: EMP Alias: E
  310.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
  311.   Inner table: EMP Alias: M
  312.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
  313.     using dmeth: 2 #groups: 1
  314.     SORT ressource Sort statistics
  315.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  316.       Degree: 1
  317.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
  318.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  319.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  320.       Total Temp space used: 0
  321.     SORT ressource Sort statistics
  322.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  323.       Degree: 1
  324.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
  325.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  326.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  327.       Total Temp space used: 0
  328.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
  329. SM Join
  330.   SM cost: 8.00
  331.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
  332.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
  333.   Outer table: EMP Alias: E
  334.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
  335.   Inner table: EMP Alias: M
  336.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
  337.     using dmeth: 2 #groups: 1
  338.     Cost per ptn: 0.50 #ptns: 1
  339.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
  340.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
  341. HA Join
  342.   HA cost: 6.50
  343.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
  344.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
  345. Best:: JoinMethod: Hash
  346.        Cost: 6.50 Degree: 1 Resp: 6.50 Card: 1.14 Bytes: 31
  347. ***********************
  348. Best so far: Table#: 0 cost: 3.0013 card: 3.7449 bytes: 80
  349.               Table#: 1 cost: 6.5025 card: 1.1441 bytes: 31
  350. ***********************
  351. Join order[2]: EMP[M]#1 EMP[E]#0

  352. ***************
  353. Now joining: EMP[E]#0
  354. ***************
  355. NL Join
  356.   Outer table: Card: 4.03 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 11
  357. Access path analysis for EMP
  358.   Inner table: EMP Alias: E
  359.   Access Path: TableScan
  360.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
  361.       Cost_io: 10.00 Cost_cpu: 203325
  362.       Resp_io: 10.00 Resp_cpu: 203325

  363.   Best NL cost: 10.01
  364.           resc: 10.01 resc_io: 10.00 resc_cpu: 203325
  365.           resp: 10.01 resp_io: 10.00 resc_cpu: 203325
  366. Join Card: 1.144145 = = outer (4.032922) * inner (3.744856) * sel (0.075758)
  367. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 4.032922, inner: 3.744856, sel: 0.075758
  368. Join Card - Rounded: 1 Computed: 1.14
  369.   Outer table: EMP Alias: M
  370.     resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
  371.   Inner table: EMP Alias: E
  372.     resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
  373.     using dmeth: 2 #groups: 1
  374.     SORT ressource Sort statistics
  375.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  376.       Degree: 1
  377.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
  378.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  379.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  380.       Total Temp space used: 0
  381.     SORT ressource Sort statistics
  382.       Sort width: 396 Area size: 347136 Max Area size: 69624832
  383.       Degree: 1
  384.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
  385.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  386.       Total IO sort cost: 0 Total CPU sort cost: 32353029
  387.       Total Temp space used: 0
  388.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
  389. SM Join
  390.   SM cost: 8.00
  391.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
  392.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
  393.   Outer table: EMP Alias: M
  394.     resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
  395.   Inner table: EMP Alias: E
  396.     resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
  397.     using dmeth: 2 #groups: 1
  398.     Cost per ptn: 0.50 #ptns: 1
  399.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
  400.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
  401. HA Join
  402.   HA cost: 6.50
  403.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
  404.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
  405. Join order aborted: cost > best plan cost
  406. ***********************
  407. (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000

  408. *********************************
  409. Number of join permutations tried: 2
  410. *********************************
  411. Consider using bloom filter between E[EMP] and M[EMP]
  412. kkoBloomFilter: join (lcdn:4 rcdn:4 jcdn:1 limit:8)
  413. Computing bloom ndv for creator:E[EMP] ccdn:3.7 and user:M[EMP] ucdn:4.0
  414. kkopqComputeBloomNdv: predicate (bndv:6 ndv:6) and (bndv:14 ndv:5)
  415. kkopqComputeBloomNdv: predicate (bndv:3 ndv:3) and (bndv:3 ndv:3)
  416. kkopqComputeBloomNdv: predicate (bndv:12 ndv:3) and (bndv:12 ndv:4)
  417. kkopqComputeBloomNdv: pred cnt:6 ndv:4 reduction:1
  418. kkoBloomFilter: join ndv:0 reduction:0.928571 (limit:0.500000) rejected because distinct value ratio
  419. (newjo-save) [1 0 ]
  420. Trying or-Expansion on query block SEL$1 (#0)
  421. Transfer Optimizer annotations for query block SEL$1 (#0)
  422. id=0 frofand predicate=E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500
  423. id=0 frofkksm[i] (sort-merge/hash) predicate=E.MGR=M.EMPNO
  424. id=0 frofkksm[i] (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO
  425. id=0 frosand (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO AND E.MGR=M.EMPNO
  426. id=0 frofand predicate=M.DEPTNO=10 AND M.SAL>1500
  427. id=0 frojand (sort-merge/hash) predicate=M.SAL>E.SAL
  428. Final cost for query block SEL$1 (#0) - All Rows Plan:
  429.   Best join order: 1
  430.   Cost: 6.5025 Degree: 1 Card: 1.0000 Bytes: 31
  431.   Resc: 6.5025 Resc_io: 6.0000 Resc_cpu: 16258612
  432.   Resp: 6.5025 Resp_io: 6.0000 Resc_cpu: 16258612
  433. kkoqbc-subheap (delete addr=0x705628, in-use=24852, alloc=25480)
  434. kkoqbc-end:
  435.         :
  436.     call(in-use=13332, alloc=49168), compile(in-use=63592, alloc=67556), execution(in-use=2088, alloc=4060)

  437. kkoqbc: finish optimizing query block SEL$1 (#0)
  438. apadrv-end
  439.           :
  440.     call(in-use=13332, alloc=49168), compile(in-use=64228, alloc=67556), execution(in-use=2088, alloc=4060)


  441. Starting SQL statement dump

  442. user_id=84 user_name=SCOTT module=SQL*Plus action=
  443. sql_id=g126tztfnzvrd plan_hash_value=-656709420 problem_type=3
  444. ----- Current SQL Statement for this session (sql_id=g126tztfnzvrd) -----
  445. select /*sample 11*/e.ename
  446. from emp e, emp m
  447. where e.mgr=m.empno
  448. and m.deptno = 10
  449. and e.deptno = m.deptno
  450. and e.sal>1500
  451. and m.sal>e.sal
  452. sql_text_length=139
  453. sql=select /*sample 11*/e.ename
  454. from emp e, emp m
  455. where e.mgr=m.empno
  456. and m.deptno = 10
  457. and e.deptno = m.deptno
  458. and e.sal>1500
  459. and m.sal>e.sal
  460. ----- Explain Plan Dump -----
  461. ----- Plan Table -----
  462.  
  463. ============
  464. Plan Table

fix 11738631 = enabled這個數其實是一個bug,去MOS上一查就是到他修復了一個什麼問題。有些時候會把bug修壞、把他關掉就是說這個fix就不起作用了。
    
query block 他會把sql語句拆成幾塊  如果有子查詢,子查詢就是一個query block,oracle做優化其實是對每一個query block做優化的。

10053對於解決問題有時候很重要,雖然不是解決sql問題首先要看的~

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29990276/viewspace-1323258/,如需轉載,請註明出處,否則將追究法律責任。

相關文章