Transformation之FPD（filter push-down）的transform【一】

    使用者寫的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. 
   
5.      EMPNO ENAME JOB             MGR HIREDATE       SAL      COMM  DEPTNO
   
6. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
   
7.       7499 YAOCHONG SALESMAN     7698 20-FEB-81     1600     300   30
   
8.       7521 YAOCHONG SALESMAN     7698 22-FEB-81     1250     500   30
   
9.       7654 YAOCHONG SALESMAN     7698 28-SEP-81     1250     1400  30
   
10.       7698 YAOCHONG MANAGER      7839 01-MAY-81     2850           30
    
11.       7844 YAOCHONG SALESMAN     7698 08-SEP-81     1500      0    30
    
12.       7900 YAOCHONG CLERK        7698 03-DEC-81     950            30
    
13. 
    
14. 6 rows selected.

Sub-quries：
    =
    in

點選(此處)摺疊或開啟

1. SQL> select * from emp where deptno in
   
2.   2 (select deptno from dept where deptno > 20);
   
3. 
   
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

    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. 
   
3. Explained.
   
4. 
   
5. SQL> @?/rdbms/admin/utlxplp
   
6. 
   
7. PLAN_TABLE_OUTPUT
   
8. ---------------------------------------------------------------------------------------
   
9. Plan hash value: 3956160932
   
10. 
    
11. --------------------------------------------------------------------------
    
12. | Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |
    
13. --------------------------------------------------------------------------
    
14. | 0  | SELECT STATEMENT |      | 14   | 574   | 3 (0)      | 00:00:01|
    
15. | 1  | TABLE ACCESS FULL| EMP  | 14   | 574   | 3 (0)      | 00:00:01|
    
16. --------------------------------------------------------------------------

oracle會自動transformation成select * from emp;執行計劃相同。

點選(此處)摺疊或開啟

1. SQL> @?/rdbms/admin/utlxplp
   
2. 
   
3. PLAN_TABLE_OUTPUT
   
4. ----------------------------------------------------------------------------------------
   
5. Plan hash value: 3956160932
   
6. 
   
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 |
    

以下三種oracle 也做了transformation

點選(此處)摺疊或開啟

1. SQL> explain plan for select * from (select * from emp) where empno = 7788;
   
2. 
   
3. Explained.
   
4. 
   
5. SQL> @?/rdbms/admin/utlxplp
   
6. 
   
7. PLAN_TABLE_OUTPUT
   
8. -------------------------------------------------------------------------------------------
   
9. Plan hash value: 2949544139
   
10. 
    
11. --------------------------------------------------------------------------------------
    
12. | Id | Operation                  | Name   | Rows  | Bytes  | Cost (%CPU)| Time     |
    
13. --------------------------------------------------------------------------------------
    
14. | 0  | SELECT STATEMENT           |        |     1 |     41 |     1 (0)  | 00:00:01 |
    
15. | 1  | TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     41 |     1 (0)  | 00:00:01 |
    
16. |* 2 | INDEX UNIQUE SCAN          | PK_EMP |     1 |        |     0 (0)  | 00:00:01 |
    
17. --------------------------------------------------------------------------------------
    
18. 
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
    
21. 
    
22.    2 - access("EMP"."EMPNO"=7788)
    
23. 
    
24. 14 rows selected.

點選(此處)摺疊或開啟

1. SQL> explain plan for select * from ( select * from emp where empno = 7788);
   
2. 
   
3. Explained.
   
4. 
   
5. SQL> @?/rdbms/admin/utlxplp
   
6. 
   
7. PLAN_TABLE_OUTPUT
   
8. -------------------------------------------------------------------------------------------
   
9. Plan hash value: 2949544139
   
10. 
    
11. --------------------------------------------------------------------------------------
    
12. | Id | Operation                 | Name   | Rows  | Bytes  | Cost (%CPU)| Time     |
    
13. --------------------------------------------------------------------------------------
    
14. | 0 | SELECT STATEMENT           |        |     1 |     41 |     1 (0)  | 00:00:01 |
    
15. | 1 | TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     41 |     1 (0)  | 00:00:01 |
    
16. |* 2 | INDEX UNIQUE SCAN         | PK_EMP |     1 |        |     0 (0)  | 00:00:01 |
    
17. --------------------------------------------------------------------------------------
    
18. 
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
    
21. 
    
22.    2 - access(\"EMPNO\"=7788)
    
23. 
    
24. 14 rows selected.

點選(此處)摺疊或開啟

1. SQL> explain plan for select * from emp where empno = 7788;
   
2. 
   
3. Explained.
   
4. 
   
5. SQL> @?/rdbms/admin/utlxplp
   
6. 
   
7. PLAN_TABLE_OUTPUT
   
8. ------------------------------------------------------------------------------------------
   
9. Plan hash value: 2949544139
   
10. 
    
11. --------------------------------------------------------------------------------------
    
12. | Id | Operation                 | Name   | Rows  | Bytes  | Cost (%CPU)| Time     |
    
13. --------------------------------------------------------------------------------------
    
14. | 0 | SELECT STATEMENT           |        |     1 |     41 |     1 (0)  | 00:00:01 |
    
15. | 1 | TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     41 |     1 (0)  | 00:00:01 |
    
16. |* 2 | INDEX UNIQUE SCAN         | PK_EMP |     1 |        |     0 (0)  | 00:00:01 |
    
17. --------------------------------------------------------------------------------------
    
18. 
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
    
21. 
    
22.    2 - access(\"EMPNO\"=7788)
    
23. 
    
24. 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. 
    
15. select * from table(dbms_xplan.display_cursor(null,null,'last'));
    
16. 
    
17. /*
    
18. FPD: transitive predicates are generated in query block SEL$1 (#0)
    
19. "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
    
20. */

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. 
    
16. 
    
17. *** 2014-11-01 01:37:48.076
    
18. *** SESSION ID:(59.486) 2014-11-01 01:37:48.076
    
19. *** CLIENT ID:() 2014-11-01 01:37:48.076
    
20. *** SERVICE NAME:(SYS$USERS) 2014-11-01 01:37:48.076
    
21. *** MODULE NAME:(SQL*Plus) 2014-11-01 01:37:48.076
    
22. *** ACTION NAME:() 2014-11-01 01:37:48.076
    
23. 
    
24. Registered qb: SEL$1 0x708b6c (PARSER)
    
25. ---------------------
    
26. QUERY BLOCK SIGNATURE                                ----10053最先做的是query block的註冊
    
27. ---------------------
    
28.   signature (): qb_name=SEL$1 nbfros=2 flg=0       -----先註冊一個quary block 名字是SEL$1,如果語句多會分成多個query block，
29.                                                     query block可以用hint qb_name指定 
30.     fro(0): flg=4 objn=73181 hint_alias="E"@"SEL$1"
    
31.     fro(1): flg=4 objn=73181 hint_alias="M"@"SEL$1"                     ------10053第一次做quary block的註冊。
    
32. 
    
33. SPM: statement not found in SMB
    
34. 
    
35. **************************
    
36. Automatic degree of parallelism (ADOP)
    
37. **************************
    
38. Automatic degree of parallelism is disabled: Parameter.
    
39. 
    
40. PM: Considering predicate move-around in query block SEL$1 (#0)       ----第二步做predicate move-around考慮能不能講位詞給祛除掉
    
41. **************************
    
42. Predicate Move-Around (PM)
    
43. **************************
    
44. OPTIMIZER INFORMATION
    
45. 
    
46. ******************************************
    
47. ----- Current SQL Statement for this session (sql_id=4usswnngj0jdx)    -----sql id也告訴你了
    
48. select /*sample 1*/e.ename                                             -----然後告訴你當前的sql是什麼樣子的
    
49. from emp e, emp m
    
50. where e.mgr=m.empno
    
51. and m.deptno = 10
    
52. and e.deptno = m.deptno
    
53. and e.sal>1500
    
54. and m.sal>e.sal
    
55. *******************************************                            -------這以下都是transformation的方法，解釋一些縮寫的含義
    
56. Legend
    
57. The following abbreviations are used by optimizer trace.
    
58. CBQT - cost-based query transformation
    
59. JPPD - join predicate push-down
    
60. OJPPD - old-style (non-cost-based) JPPD    ---不考慮成本的cost
    
61. FPD - filter push-down
    
62. PM - predicate move-around
    
63. CVM - complex view merging
    
64. SPJ - select-project-join
    
65. SJC - set join conversion
    
66. SU - subquery unnesting
    
67. OBYE - order by elimination
    
68. OST - old style star transformation
    
69. ST - new (cbqt) star transformation
    
70. CNT - count(col) to count(*) transformation
    
71. JE - Join Elimination
    
72. JF - join factorization
    
73. SLP - select list pruning
    
74. DP - distinct placement
    
75. qb - query block
    
76. LB - leaf blocks
    
77. DK - distinct keys
    
78. LB/K - average number of leaf blocks per key
    
79. DB/K - average number of data blocks per key
    
80. CLUF - clustering factor
    
81. NDV - number of distinct values
    
82. Resp - response cost
    
83. Card - cardinality
    
84. Resc - resource cost
    
85. NL - nested loops (join)
    
86. SM - sort merge (join)
    
87. 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. 
    
25. 
    
26.   *************************************          和cbo有關的引數都列在這裡，這些引數可以在session級別關閉
    
27.   PARAMETERS WITH DEFAULT VALUES
    
28.   ******************************
    
29. Compilation Environment Dump
    
30. optimizer_mode_hinted = false
    
31. optimizer_features_hinted = 0.0.0
    
32. parallel_execution_enabled = true
    
33. parallel_query_forced_dop = 0
    
34. parallel_dml_forced_dop = 0
    
35. parallel_ddl_forced_degree = 0
    
36. parallel_ddl_forced_instances = 0
    
37. _query_rewrite_fudge = 90
    
38. optimizer_features_enable = 11.2.0.1
    
39. _optimizer_search_limit = 5
    
40. cpu_count = 1
    
41. active_instance_count = 2
    
42. parallel_threads_per_cpu = 2
    
43. hash_area_size = 131072
    
44. bitmap_merge_area_size = 1048576
    
45. sort_area_size = 65536
    
46. sort_area_retained_size = 0
    
47. _sort_elimination_cost_ratio = 0
    
48. _optimizer_block_size = 8192
    
49. _sort_multiblock_read_count = 2
    
50. _hash_multiblock_io_count = 0
    
51. _db_file_optimizer_read_count = 8
    
52. _optimizer_max_permutations = 2000
    
53. pga_aggregate_target = 339968 KB
    
54. _pga_max_size = 204800 KB
    
55. _query_rewrite_maxdisjunct = 257
    
56. _smm_auto_min_io_size = 56 KB
    
57. _smm_auto_max_io_size = 248 KB
    
58. _smm_min_size = 339 KB
    
59. _smm_max_size = 67993 KB
    
60. _smm_px_max_size = 169984 KB
    
61. _cpu_to_io = 0
    
62. _optimizer_undo_cost_change = 11.2.0.1
    
63. parallel_query_mode = enabled
    
64. parallel_dml_mode = disabled
    
65. parallel_ddl_mode = enabled
    
66. optimizer_mode = all_rows
    
67. sqlstat_enabled = false
    
68. _optimizer_percent_parallel = 101
    
69. _always_anti_join = choose
    
70. _always_semi_join = choose
    
71. _optimizer_mode_force = true
    
72. _partition_view_enabled = true
    
73. _always_star_transformation = false
    
74. _query_rewrite_or_error = false
    
75. _hash_join_enabled = true
    
76. cursor_sharing = exact
    
77. _b_tree_bitmap_plans = true
    
78. star_transformation_enabled = false
    
79. _optimizer_cost_model = choose
    
80. _new_sort_cost_estimate = true
    
81. _complex_view_merging = true
    
82. _unnest_subquery = true
    
83. _eliminate_common_subexpr = true
    
84. _pred_move_around = true
    
85. _convert_set_to_join = false
    
86. _push_join_predicate = true
    
87. _push_join_union_view = true
    
88. _fast_full_scan_enabled = true
    
89. _optim_enhance_nnull_detection = true
    
90. _parallel_broadcast_enabled = true
    
91. _px_broadcast_fudge_factor = 100
    
92. _ordered_nested_loop = true
    
93. _no_or_expansion = false
    
94. optimizer_index_cost_adj = 100
    
95. optimizer_index_caching = 0
    
96. _system_index_caching = 0
    
97. _disable_datalayer_sampling = false
    
98. query_rewrite_enabled = true
    
99. query_rewrite_integrity = enforced
    
100. _query_cost_rewrite = true
     
101. _query_rewrite_2 = true
     
102. _query_rewrite_1 = true
     
103. _query_rewrite_expression = true
     
104. _query_rewrite_jgmigrate = true
     
105. _query_rewrite_fpc = true
     
106. _query_rewrite_drj = true
     
107. _full_pwise_join_enabled = true
     
108. _partial_pwise_join_enabled = true
     
109. _left_nested_loops_random = true
     
110. _improved_row_length_enabled = true
     
111. _index_join_enabled = true
     
112. _enable_type_dep_selectivity = true
     
113. _improved_outerjoin_card = true
     
114. _optimizer_adjust_for_nulls = true
     
115. _optimizer_degree = 0
     
116. _use_column_stats_for_function = true
     
117. _subquery_pruning_enabled = true
     
118. _subquery_pruning_mv_enabled = false
     
119. _or_expand_nvl_predicate = true
     
120. _like_with_bind_as_equality = false
     
121. _table_scan_cost_plus_one = true
     
122. _cost_equality_semi_join = true
     
123. _default_non_equality_sel_check = true
     
124. _new_initial_join_orders = true
     
125. _oneside_colstat_for_equijoins = true
     
126. _optim_peek_user_binds = true
     
127. _minimal_stats_aggregation = true
     
128. _force_temptables_for_gsets = false
     
129. workarea_size_policy = auto
     
130. _smm_auto_cost_enabled = true
     
131. _gs_anti_semi_join_allowed = true
     
132. _optim_new_default_join_sel = true
     
133. optimizer_dynamic_sampling = 2
     
134. _pre_rewrite_push_pred = true
     
135. _optimizer_new_join_card_computation = true
     
136. _union_rewrite_for_gs = yes_gset_mvs
     
137. _generalized_pruning_enabled = true
     
138. _optim_adjust_for_part_skews = true
     
139. _force_datefold_trunc = false
     
140. statistics_level = typical
     
141. _optimizer_system_stats_usage = true
     
142. skip_unusable_indexes = true
     
143. _remove_aggr_subquery = true
     
144. _optimizer_push_down_distinct = 0
     
145. _dml_monitoring_enabled = true
     
146. _optimizer_undo_changes = false
     
147. _predicate_elimination_enabled = true
     
148. _nested_loop_fudge = 100
     
149. _project_view_columns = true
     
150. _local_communication_costing_enabled = true
     
151. _local_communication_ratio = 50
     
152. _query_rewrite_vop_cleanup = true
     
153. _slave_mapping_enabled = true
     
154. _optimizer_cost_based_transformation = linear
     
155. _optimizer_mjc_enabled = true
     
156. _right_outer_hash_enable = true
     
157. _spr_push_pred_refspr = true
     
158. _optimizer_cache_stats = false
     
159. _optimizer_cbqt_factor = 50
     
160. _optimizer_squ_bottomup = true
     
161. _fic_area_size = 131072
     
162. _optimizer_skip_scan_enabled = true
     
163. _optimizer_cost_filter_pred = false
     
164. _optimizer_sortmerge_join_enabled = true
     
165. _optimizer_join_sel_sanity_check = true
     
166. _mmv_query_rewrite_enabled = true
     
167. _bt_mmv_query_rewrite_enabled = true
     
168. _add_stale_mv_to_dependency_list = true
     
169. _distinct_view_unnesting = false
     
170. _optimizer_dim_subq_join_sel = true
     
171. _optimizer_disable_strans_sanity_checks = 0
     
172. _optimizer_compute_index_stats = true
     
173. _push_join_union_view2 = true
     
174. _optimizer_ignore_hints = false
     
175. _optimizer_random_plan = 0
     
176. _query_rewrite_setopgrw_enable = true
     
177. _optimizer_correct_sq_selectivity = true
     
178. _disable_function_based_index = false
     
179. _optimizer_join_order_control = 3
     
180. _optimizer_cartesian_enabled = true
     
181. _optimizer_starplan_enabled = true
     
182. _extended_pruning_enabled = true
     
183. _optimizer_push_pred_cost_based = true
     
184. _optimizer_null_aware_antijoin = true
     
185. _optimizer_extend_jppd_view_types = true
     
186. _sql_model_unfold_forloops = run_time
     
187. _enable_dml_lock_escalation = false
     
188. _bloom_filter_enabled = true
     
189. _update_bji_ipdml_enabled = 0
     
190. _optimizer_extended_cursor_sharing = udo
     
191. _dm_max_shared_pool_pct = 1
     
192. _optimizer_cost_hjsmj_multimatch = true
     
193. _optimizer_transitivity_retain = true
     
194. _px_pwg_enabled = true
     
195. optimizer_secure_view_merging = true
     
196. _optimizer_join_elimination_enabled = true
     
197. flashback_table_rpi = non_fbt
     
198. _optimizer_cbqt_no_size_restriction = true
     
199. _optimizer_enhanced_filter_push = true
     
200. _optimizer_filter_pred_pullup = true
     
201. _rowsrc_trace_level = 0
     
202. _simple_view_merging = true
     
203. _optimizer_rownum_pred_based_fkr = true
     
204. _optimizer_better_inlist_costing = all
     
205. _optimizer_self_induced_cache_cost = false
     
206. _optimizer_min_cache_blocks = 10
     
207. _optimizer_or_expansion = depth
     
208. _optimizer_order_by_elimination_enabled = true
     
209. _optimizer_outer_to_anti_enabled = true
     
210. _selfjoin_mv_duplicates = true
     
211. _dimension_skip_null = true
     
212. _force_rewrite_enable = false
     
213. _optimizer_star_tran_in_with_clause = true
     
214. _optimizer_complex_pred_selectivity = true
     
215. _optimizer_connect_by_cost_based = true
     
216. _gby_hash_aggregation_enabled = true
     
217. _globalindex_pnum_filter_enabled = true
     
218. _px_minus_intersect = true
     
219. _fix_control_key = 0
     
220. _force_slave_mapping_intra_part_loads = false
     
221. _force_tmp_segment_loads = false
     
222. _query_mmvrewrite_maxpreds = 10
     
223. _query_mmvrewrite_maxintervals = 5
     
224. _query_mmvrewrite_maxinlists = 5
     
225. _query_mmvrewrite_maxdmaps = 10
     
226. _query_mmvrewrite_maxcmaps = 20
     
227. _query_mmvrewrite_maxregperm = 512
     
228. _query_mmvrewrite_maxmergedcmaps = 50
     
229. _query_mmvrewrite_maxqryinlistvals = 500
     
230. _disable_parallel_conventional_load = false
     
231. _trace_virtual_columns = false
     
232. _replace_virtual_columns = true
     
233. _virtual_column_overload_allowed = true
     
234. _kdt_buffering = true
     
235. _first_k_rows_dynamic_proration = true
     
236. _optimizer_sortmerge_join_inequality = true
     
237. _optimizer_aw_stats_enabled = true
     
238. _bloom_pruning_enabled = true
     
239. result_cache_mode = MANUAL
     
240. _px_ual_serial_input = true
     
241. _optimizer_skip_scan_guess = false
     
242. _enable_row_shipping = true
     
243. _row_shipping_threshold = 80
     
244. _row_shipping_explain = false
     
245. transaction_isolation_level = read_commited
     
246. _optimizer_distinct_elimination = true
     
247. _optimizer_multi_level_push_pred = true
     
248. _optimizer_group_by_placement = true
     
249. _optimizer_rownum_bind_default = 10
     
250. _enable_query_rewrite_on_remote_objs = true
     
251. _optimizer_extended_cursor_sharing_rel = simple
     
252. _optimizer_adaptive_cursor_sharing = true
     
253. _direct_path_insert_features = 0
     
254. _optimizer_improve_selectivity = true
     
255. optimizer_use_pending_statistics = false
     
256. _optimizer_enable_density_improvements = true
     
257. _optimizer_aw_join_push_enabled = true
     
258. _optimizer_connect_by_combine_sw = true
     
259. _enable_pmo_ctas = 0
     
260. _optimizer_native_full_outer_join = force
     
261. _bloom_predicate_enabled = true
     
262. _optimizer_enable_extended_stats = true
     
263. _is_lock_table_for_ddl_wait_lock = 0
     
264. _pivot_implementation_method = choose
     
265. optimizer_capture_sql_plan_baselines = false
     
266. optimizer_use_sql_plan_baselines = true
     
267. _optimizer_star_trans_min_cost = 0
     
268. _optimizer_star_trans_min_ratio = 0
     
269. _with_subquery = OPTIMIZER
     
270. _optimizer_fkr_index_cost_bias = 10
     
271. _optimizer_use_subheap = true
     
272. parallel_degree_policy = manual
     
273. parallel_degree = 0
     
274. parallel_min_time_threshold = 10
     
275. _parallel_time_unit = 10
     
276. _optimizer_or_expansion_subheap = true
     
277. _optimizer_free_transformation_heap = true
     
278. _optimizer_reuse_cost_annotations = true
     
279. _result_cache_auto_size_threshold = 100
     
280. _result_cache_auto_time_threshold = 1000
     
281. _optimizer_nested_rollup_for_gset = 100
     
282. _nlj_batching_enabled = 1
     
283. parallel_query_default_dop = 0
     
284. is_recur_flags = 0
     
285. optimizer_use_invisible_indexes = false
     
286. flashback_data_archive_internal_cursor = 0
     
287. _optimizer_extended_stats_usage_control = 224
     
288. _parallel_syspls_obey_force = true
     
289. cell_offload_processing = true
     
290. _rdbms_internal_fplib_enabled = false
     
291. db_file_multiblock_read_count = 79
     
292. _bloom_folding_enabled = true
     
293. _mv_generalized_oj_refresh_opt = true
     
294. cell_offload_compaction = ADAPTIVE
     
295. parallel_degree_limit = 65535
     
296. parallel_force_local = false
     
297. total_cpu_count = 2
     
298. cell_offload_plan_display = AUTO
     
299. _optimizer_coalesce_subqueries = true
     
300. _optimizer_fast_pred_transitivity = true
     
301. _optimizer_fast_access_pred_analysis = true
     
302. _optimizer_unnest_disjunctive_subq = true
     
303. _optimizer_unnest_corr_set_subq = true
     
304. _optimizer_distinct_agg_transform = true
     
305. _aggregation_optimization_settings = 0
     
306. _optimizer_connect_by_elim_dups = true
     
307. _optimizer_eliminate_filtering_join = true
     
308. _connect_by_use_union_all = true
     
309. dst_upgrade_insert_conv = true
     
310. advanced_queuing_internal_cursor = 0
     
311. _optimizer_unnest_all_subqueries = true
     
312. _bloom_predicate_pushdown_to_storage = true
     
313. _bloom_vector_elements = 0
     
314. _bloom_pushing_max = 524288
     
315. parallel_autodop = 0
     
316. parallel_ddldml = 0
     
317. _parallel_cluster_cache_policy = adaptive
     
318. _parallel_scalability = 50
     
319. iot_internal_cursor = 0
     
320. _optimizer_instance_count = 0
     
321. _optimizer_connect_by_cb_whr_only = false
     
322. _suppress_scn_chk_for_cqn = nosuppress_1466
     
323. _optimizer_join_factorization = true
     
324. _optimizer_use_cbqt_star_transformation = true
     
325. _optimizer_table_expansion = true
     
326. _and_pruning_enabled = true
     
327. _deferred_constant_folding_mode = DEFAULT
     
328. _optimizer_distinct_placement = true
     
329. partition_pruning_internal_cursor = 0
     
330. parallel_hinted = none
     
331. _sql_compatibility = 0
     
332. _optimizer_use_feedback = true
     
333. _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. 
   
8. Considering Query Transformations on query block SEL$1 (#0)
   
9. **************************
   
10. Query transformations (QT)
    
11. **************************
    
12. JF: Checking validity of join factorization for query block SEL$1 (#0)     ------發現沒什麼可以做的就過去了。
    
13. JF: Bypassed: not a UNION or UNION-ALL query block.                           沒有union all的東西所以跳過
    
14. ST: not valid since star transformation parameter is FALSE               -----這個也沒什麼必要做
    
15. TE: Checking validity of table expansion for query block SEL$1 (#0)
    
16. TE: Bypassed: No partitioned table in query block.                      ----根本就沒有paratition 表所以也沒做。
    
17. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.  不是複雜view 所以也沒做
    
18. CBQT: Validity checks failed for 4usswnngj0jdx.
    
19. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
    
20. *************************
    
21. Common Subexpression elimination (CSE)
    
22. *************************
    
23. CSE: CSE not performed on query block SEL$1 (#0).
    
24. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
    
25. ***************************
    
26. Order-by elimination (OBYE)
    
27. ***************************
    
28. OBYE: OBYE bypassed: no order by to eliminate.
    
29. JE: Considering Join Elimination on query block SEL$1 (#0)
    
30. *************************
    
31. Join Elimination (JE)               ----這一步看看能不能把一些join消除掉，也沒有能消除的join
    
32. *************************
    
33. SQL:******* UNPARSED QUERY IS *******
    
34. 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"
    
35. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
    
36. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
    
37. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
    
38. SQL:******* UNPARSED QUERY IS *******
    
39. 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"
    
40. Query block SEL$1 (#0) unchanged
    
41. CVM: Considering view merge in query block SEL$1 (#0)
    
42. JE: Considering Join Elimination on query block SEL$1 (#0)
    
43. *************************
    
44. Join Elimination (JE)
    
45. *************************
    
46. SQL:******* UNPARSED QUERY IS *******
    
47. 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
    
48. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
    
49. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
    
50. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
    
51. SQL:******* UNPARSED QUERY IS *******
    
52. 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
    
53. Query block SEL$1 (#0) unchanged
    
54. query block SEL$1 (#0) unchanged
    
55. Considering Query Transformations on query block SEL$1 (#0)
    
56. **************************
    
57. Query transformations (QT)
    
58. **************************
    
59. JF: Checking validity of join factorization for query block SEL$1 (#0)
    
60. JF: Bypassed: not a UNION or UNION-ALL query block.
    
61. ST: not valid since star transformation parameter is FALSE
    
62. TE: Checking validity of table expansion for query block SEL$1 (#0)
    
63. TE: Bypassed: No partitioned table in query block.
    
64. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
    
65. CBQT: Validity checks failed for 4usswnngj0jdx.
    
66. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
    
67. *************************
    
68. Common Subexpression elimination (CSE)
    
69. *************************
    
70. CSE: CSE not performed on query block SEL$1 (#0).
    
71. SU: Considering subquery unnesting in query block SEL$1 (#0)
    
72. ********************
    
73. Subquery Unnest (SU)
    
74. ********************
    
75. SJC: Considering set-join conversion in query block SEL$1 (#0)
    
76. *************************
    
77. Set-Join Conversion (SJC)
    
78. *************************
    
79. SJC: not performed
    
80. JE: Considering Join Elimination on query block SEL$1 (#0)
    
81. *************************
    
82. Join Elimination (JE)
    
83. *************************
    
84. SQL:******* UNPARSED QUERY IS *******
    
85. 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
    
86. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
    
87. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
    
88. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
    
89. SQL:******* UNPARSED QUERY IS *******
    
90. 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
    
91. Query block SEL$1 (#0) unchanged
    
92. PM: Considering predicate move-around in query block SEL$1 (#0)
    
93. **************************
    
94. Predicate Move-Around (PM)
    
95. **************************
    
96. PM: PM bypassed: Outer query contains no views.
    
97. PM: PM bypassed: Outer query contains no views.
    
98. query block SEL$1 (#0) unchanged
    
99. FPD: Considering simple filter push in query block SEL$1 (#0)                       --------------FPD這裡做了一些事情，產生了一個可以傳遞的謂詞
    
100. E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
     
101. try to generate transitive predicate from check constraints for query block SEL$1 (#0)
     
102. 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
     
103. 
     
104. FPD: transitive predicates are generated in query block SEL$1 (#0)
     
105. 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
     
106. apadrv-start sqlid=5576332259131147709
107.   :
     
108.     call(in-use=2208, alloc=16360), compile(in-use=61336, alloc=63432), execution(in-use=2088, alloc=4060)
     
109. 
     
110. *******************************************
     
111. Peeked values of the binds in SQL statement
     
112. *******************************************                                                         ------------最後把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. -----------------------------
    
20. SYSTEM STATISTICS INFORMATION
    
21. -----------------------------
    
22.   Using NOWORKLOAD Stats
    
23.   CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
    
24.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
    
25.   IOSEEKTIM: 10 milliseconds (default is 10)
    
26.   MBRC: -1 blocks (default is 8)
    
27. 
    
28. ***************************************
    
29. BASE STATISTICAL INFORMATION                                                -----做cost，把表的統計資訊拿出來，考慮各種路徑
    
30. ***********************
    
31. Table Stats::                                                 
    
32.   Table: EMP Alias: M
    
33.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
    
34. Index Stats::
    
35.   Index: PK_EMP Col#: 1
    
36.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
    
37. ***********************
    
38. Table Stats::
    
39.   Table: EMP Alias: E
    
40.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
    
41. Index Stats::
    
42.   Index: PK_EMP Col#: 1
    
43.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
    
44. Access path analysis for EMP
    
45. ***************************************
    
46. SINGLE TABLE ACCESS PATH
    
47.   Single Table Cardinality Estimation for EMP[E]                           --------訪問單表的時候，我怎麼訪問是最快的。
    
48. 
    
49.   Table: EMP Alias: E
    
50.     Card: Original: 14.000000 Rounded: 4 Computed: 3.74 Non Adjusted: 3.74
    
51.   Access Path: TableScan                                                  -------因為沒有索引嗎，他考慮的唯一方式就是全表掃描
    
52.     Cost: 3.00 Resp: 3.00 Degree: 0
    
53.       Cost_io: 3.00 Cost_cpu: 40677
    
54.       Resp_io: 3.00 Resp_cpu: 40677
    
55.   Best:: AccessPath: TableScan
    
56.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 3.74 Bytes: 0
    
57. 
    
58. Access path analysis for EMP
    
59. ***************************************
    
60. SINGLE TABLE ACCESS PATH
    
61.   Single Table Cardinality Estimation for EMP[M]
    
62. 
    
63.   Table: EMP Alias: M
    
64.     Card: Original: 14.000000 Rounded: 4 Computed: 4.03 Non Adjusted: 4.03
    
65.   Access Path: TableScan
    
66.     Cost: 3.00 Resp: 3.00 Degree: 0
    
67.       Cost_io: 3.00 Cost_cpu: 40601
    
68.       Resp_io: 3.00 Resp_cpu: 40601
    
69.   Best:: AccessPath: TableScan
    
70.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.03 Bytes: 0
    
71. 
    
72. ***************************************
    
73. 
    
74. 
    
75. OPTIMIZER STATISTICS AND COMPUTATIONS
    
76. ***************************************
    
77. GENERAL PLANS
    
78. ***************************************
    

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. ***************
   
6. Now joining: EMP[M]#1
   
7. ***************
   
8. NL Join                            ---------考慮nest loop花銷，這種join會考慮用哪個表做驅動表，因為他需要把小表作為驅動表的。
   
9.   Outer table: Card: 3.74 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
   
10. Access path analysis for EMP
    
11.   Inner table: EMP Alias: M      ---inner table 是M這張表
    
12.   Access Path: TableScan
    
13.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
    
14.       Cost_io: 10.00 Cost_cpu: 203079
    
15.       Resp_io: 10.00 Resp_cpu: 203079
    
16.   Access Path: index (UniqueScan)
    
17.     Index: PK_EMP
    
18.     resc_io: 1.00 resc_cpu: 8528
    
19.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
    
20.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
    
21.       Cost_io: 7.00 Cost_cpu: 74790
    
22.       Resp_io: 7.00 Resp_cpu: 74790
    
23.   Access Path: index (AllEqUnique)
    
24.     Index: PK_EMP
    
25.     resc_io: 1.00 resc_cpu: 8528
    
26.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
    
27.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
    
28.       Cost_io: 7.00 Cost_cpu: 74790
    
29.       Resp_io: 7.00 Resp_cpu: 74790
    
30. 
    
31.   Best NL cost: 7.00
    
32.           resc: 7.00 resc_io: 7.00 resc_cpu: 74790
    
33.           resp: 7.00 resp_io: 7.00 resc_cpu: 74790
    
34. Join Card: 1.144145 = = outer (3.744856) * inner (4.032922) * sel (0.075758)
    
35. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 3.744856, inner: 4.032922, sel: 0.075758
    
36. Join Card - Rounded: 1 Computed: 1.14
    
37.   Outer table: EMP Alias: E
    
38.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
    
39.   Inner table: EMP Alias: M
    
40.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
    
41.     using dmeth: 2 #groups: 1
    
42.     SORT ressource Sort statistics
    
43.       Sort width: 396 Area size: 347136 Max Area size: 69624832
    
44.       Degree: 1
    
45.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
    
46.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
    
47.       Total IO sort cost: 0 Total CPU sort cost: 32353029
    
48.       Total Temp space used: 0
    
49.     SORT ressource Sort statistics
    
50.       Sort width: 396 Area size: 347136 Max Area size: 69624832
    
51.       Degree: 1
    
52.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
    
53.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
    
54.       Total IO sort cost: 0 Total CPU sort cost: 32353029
    
55.       Total Temp space used: 0
    
56.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
    
57. SM Join                                                  -----------sort merge join
58.   SM cost: 8.00
    
59.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
    
60.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
    
61.   Outer table: EMP Alias: E
    
62.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
    
63.   Inner table: EMP Alias: M
    
64.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
    
65.     using dmeth: 2 #groups: 1
    
66.     Cost per ptn: 0.50 #ptns: 1
    
67.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
    
68.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
    
69. HA Join                                                        ------------hash join最好cost為6.50
    
70.   HA cost: 6.50
    
71.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
    
72.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
    
73. Best:: JoinMethod: Hash
    
74.        Cost: 6.50 Degree: 1 Resp: 6.50 Card: 1.14 Bytes: 31
    
75. ***********************
    
76. Best so far: Table#: 0 cost: 3.0013 card: 3.7449 bytes: 80
    
77.               Table#: 1 cost: 6.5025 card: 1.1441 bytes: 31
    
78. ***********************
    
79. Join order[2]: EMP[M]#1 EMP[E]#0
    
80. 
    
81. ***************
    
82. Now joining: EMP[E]#0
    
83. ***************
    
84. 
    
85. NL Join
    
86.   Outer table: Card: 4.03 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 11
    
87. Access path analysis for EMP
    
88.   Inner table: EMP Alias: E
    
89.   Access Path: TableScan
    
90.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
    
91.       Cost_io: 10.00 Cost_cpu: 203325
    
92.       Resp_io: 10.00 Resp_cpu: 203325
    
93. 
    
94.   Best NL cost: 10.01
    
95.           resc: 10.01 resc_io: 10.00 resc_cpu: 203325
    
96.           resp: 10.01 resp_io: 10.00 resc_cpu: 203325
    
97. Join Card: 1.144145 = = outer (4.032922) * inner (3.744856) * sel (0.075758)
    
98. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 4.032922, inner: 3.744856, sel: 0.075758
    
99. Join Card - Rounded: 1 Computed: 1.14
    
100.   Outer table: EMP Alias: M
     
101.     resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
     
102.   Inner table: EMP Alias: E
     
103.     resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
     
104.     using dmeth: 2 #groups: 1
     
105.     SORT ressource Sort statistics
     
106.       Sort width: 396 Area size: 347136 Max Area size: 69624832
     
107.       Degree: 1
     
108.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
     
109.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
     
110.       Total IO sort cost: 0 Total CPU sort cost: 32353029
     
111.       Total Temp space used: 0
     
112.     SORT ressource Sort statistics
     
113.       Sort width: 396 Area size: 347136 Max Area size: 69624832
     
114.       Degree: 1
     
115.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
     
116.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
     
117.       Total IO sort cost: 0 Total CPU sort cost: 32353029
     
118.       Total Temp space used: 0
     
119.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
     
120. SM Join
     
121.   SM cost: 8.00
     
122.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
     
123.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
     
124.   Outer table: EMP Alias: M
     
125.     resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
     
126.   Inner table: EMP Alias: E
     
127.     resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
     
128.     using dmeth: 2 #groups: 1
     
129.     Cost per ptn: 0.50 #ptns: 1
     
130.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
     
131.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
     
132. HA Join
     
133.   HA cost: 6.50
     
134.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
     
135.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
     
136. Join order aborted: cost > best plan cost
     
137. ***********************
     
138. (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
     
139. 
     
140. *********************************
     
141. Number of join permutations tried: 2
     
142. *********************************
     
143. Consider using bloom filter between E[EMP] and M[EMP]
     
144. kkoBloomFilter: join (lcdn:4 rcdn:4 jcdn:1 limit:8)
     
145. Computing bloom ndv for creator:E[EMP] ccdn:3.7 and user:M[EMP] ucdn:4.0
     
146. kkopqComputeBloomNdv: predicate (bndv:6 ndv:6) and (bndv:14 ndv:5)
     
147. kkopqComputeBloomNdv: predicate (bndv:3 ndv:3) and (bndv:3 ndv:3)
     
148. kkopqComputeBloomNdv: predicate (bndv:12 ndv:3) and (bndv:12 ndv:4)
     
149. kkopqComputeBloomNdv: pred cnt:6 ndv:4 reduction:1
     
150. kkoBloomFilter: join ndv:0 reduction:0.928571 (limit:0.500000) rejected because distinct value ratio
     
151. (newjo-save) [1 0 ]
     
152. Trying or-Expansion on query block SEL$1 (#0)
     
153. Transfer Optimizer annotations for query block SEL$1 (#0)
     
154. id=0 frofand predicate=E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500
     
155. id=0 frofkksm[i] (sort-merge/hash) predicate=E.MGR=M.EMPNO
     
156. id=0 frofkksm[i] (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO
     
157. id=0 frosand (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO AND E.MGR=M.EMPNO
     
158. id=0 frofand predicate=M.DEPTNO=10 AND M.SAL>1500
     
159. id=0 frojand (sort-merge/hash) predicate=M.SAL>E.SAL
     
160. Final cost for query block SEL$1 (#0) - All Rows Plan:                              ------最後給你的統計
     
161.   Best join order: 1
     
162.   Cost: 6.5025 Degree: 1 Card: 1.0000 Bytes: 31
     
163.   Resc: 6.5025 Resc_io: 6.0000 Resc_cpu: 16258612
     
164.   Resp: 6.5025 Resp_io: 6.0000 Resc_cpu: 16258612
     
165. kkoqbc-subheap (delete addr=0x705628, in-use=24852, alloc=25480)
     
166. kkoqbc-end:
     
167.         :
     
168.     call(in-use=13332, alloc=49168), compile(in-use=63592, alloc=67556), execution(in-use=2088, alloc=4060)
     
169. 
     
170. kkoqbc: finish optimizing query block SEL$1 (#0)
     
171. apadrv-end
     
172.           :
     
173.     call(in-use=13332, alloc=49168), compile(in-use=64228, alloc=67556), execution(in-use=2088, alloc=4060)
     
174. 
     
175. 
     
176. Starting SQL statement dump
     
177. 
     
178. user_id=84 user_name=SCOTT module=SQL*Plus action=
     
179. sql_id=4usswnngj0jdx plan_hash_value=-656709420 problem_type=3
     
180. ----- Current SQL Statement for this session (sql_id=4usswnngj0jdx) -----
     
181. select /*sample 1*/e.ename
     
182. from emp e, emp m
     
183. where e.mgr=m.empno
     
184. and m.deptno = 10
     
185. and e.deptno = m.deptno
     
186. and e.sal>1500
     
187. and m.sal>e.sal
     
188. sql_text_length=138
     
189. sql=select /*sample 1*/e.ename
     
190. from emp e, emp m
     
191. where e.mgr=m.empno
     
192. and m.deptno = 10
     
193. and e.deptno = m.deptno
     
194. and e.sal>1500
     
195. and m.sal>e.sal
     
196. ----- Explain Plan Dump -----
     
197. ----- Plan Table -----                                                -------給你執行計劃
     
198.  
     
199. ============
     
200. Plan Table
     
201. ============
     
202. --------------------------------------+-----------------------------------+
     
203. | Id | Operation | Name | Rows | Bytes | Cost | Time |
     
204. --------------------------------------+-----------------------------------+
     
205. | 0 | SELECT STATEMENT | | | | 7 | |
     
206. | 1 | HASH JOIN | | 1 | 31 | 7 | 00:00:01 |
     
207. | 2 | TABLE ACCESS FULL | EMP | 4 | 80 | 3 | 00:00:01 |
     
208. | 3 | TABLE ACCESS FULL | EMP | 4 | 44 | 3 | 00:00:01 |
     
209. --------------------------------------+-----------------------------------+
     
210. Predicate Information:
     
211. ----------------------
     
212. 1 - access(E.MGR=M.EMPNO AND E.DEPTNO=M.DEPTNO)
     
213. 1 - filter(M.SAL>E.SAL)
     
214. 2 - filter((E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500))
     
215. 3 - filter((M.DEPTNO=10 AND M.SAL>1500))
     
216.  
     
217. Content of other_xml column
     
218. ===========================
     
219.   db_version : 11.2.0.1
     
220.   parse_schema : SCOTT
     
221.   plan_hash : 3638257876
     
222.   plan_hash_2 : 4140062955
     
223.   Outline Data:                                 這是outline data，一般情況可以用outline data 固定執行計劃
     
224.   /*+
     
225.     BEGIN_OUTLINE_DATA
     
226.       IGNORE_OPTIM_EMBEDDED_HINTS
     
227.       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
     
228.       DB_VERSION('11.2.0.1')
     
229.       ALL_ROWS
     
230.       OUTLINE_LEAF(@"SEL$1")
     
231.       FULL(@"SEL$1" "E"@"SEL$1")               ---full table scan
     
232.       FULL(@"SEL$1" "M"@"SEL$1")                    -----這裡就是這個query block別名M的full table scan
     
233.       LEADING(@"SEL$1" "E"@"SEL$1" "M"@"SEL$1") -----這個是哪個放在前面，哪個放在後面。
     
234.       USE_HASH(@"SEL$1" "M"@"SEL$1")
     
235.     END_OUTLINE_DATA
     
236.   */
     
237.  

後面的是重新提了一下

1. 
   
2. *** 2014-11-01 01:51:05.586
   
3. Registered qb: SEL$1 0x708b6c (PARSER)
   
4. ---------------------
   
5. QUERY BLOCK SIGNATURE
   
6. ---------------------
   
7.   signature (): qb_name=SEL$1 nbfros=2 flg=0
   
8.     fro(0): flg=4 objn=73181 hint_alias="E"@"SEL$1"
   
9.     fro(1): flg=4 objn=73181 hint_alias="M"@"SEL$1"
   
10. 
    
11. SPM: statement not found in SMB
    
12. 
    
13. **************************
    
14. Automatic degree of parallelism (ADOP)
    
15. **************************
    
16. Automatic degree of parallelism is disabled: Parameter.
    
17. 
    
18. PM: Considering predicate move-around in query block SEL$1 (#0)
    
19. **************************
    
20. Predicate Move-Around (PM)
    
21. **************************
    
22. OPTIMIZER INFORMATION
    
23. 
    
24. ******************************************
    
25. ----- Current SQL Statement for this session (sql_id=g126tztfnzvrd) -----
    
26. select /*sample 11*/e.ename
    
27. from emp e, emp m
    
28. where e.mgr=m.empno
    
29. and m.deptno = 10
    
30. and e.deptno = m.deptno
    
31. and e.sal>1500
    
32. and m.sal>e.sal
    
33. *******************************************
    
34. Legend
    
35. The following abbreviations are used by optimizer trace.
    
36. CBQT - cost-based query transformation
    
37. JPPD - join predicate push-down
    
38. OJPPD - old-style (non-cost-based) JPPD
    
39. FPD - filter push-down
    
40. PM - predicate move-around
    
41. CVM - complex view merging
    
42. SPJ - select-project-join
    
43. SJC - set join conversion
    
44. SU - subquery unnesting
    
45. OBYE - order by elimination
    
46. OST - old style star transformation
    
47. ST - new (cbqt) star transformation
    
48. CNT - count(col) to count(*) transformation
    
49. JE - Join Elimination
    
50. JF - join factorization
    
51. SLP - select list pruning
    
52. DP - distinct placement
    
53. qb - query block
    
54. LB - leaf blocks
    
55. DK - distinct keys
    
56. LB/K - average number of leaf blocks per key
    
57. DB/K - average number of data blocks per key
    
58. CLUF - clustering factor
    
59. NDV - number of distinct values
    
60. Resp - response cost
    
61. Card - cardinality
    
62. Resc - resource cost
    
63. NL - nested loops (join)
    
64. SM - sort merge (join)
    
65. HA - hash (join)
    
66. CPUSPEED - CPU Speed
    
67. IOTFRSPEED - I/O transfer speed
    
68. IOSEEKTIM - I/O seek time
    
69. SREADTIM - average single block read time
    
70. MREADTIM - average multiblock read time
    
71. MBRC - average multiblock read count
    
72. MAXTHR - maximum I/O system throughput
    
73. SLAVETHR - average slave I/O throughput
    
74. dmeth - distribution method
    
75.   1: no partitioning required
    
76.   2: value partitioned
    
77.   4: right is random (round-robin)
    
78.   128: left is random (round-robin)
    
79.   8: broadcast right and partition left
    
80.   16: broadcast left and partition right
    
81.   32: partition left using partitioning of right
    
82.   64: partition right using partitioning of left
    
83.   256: run the join in serial
    
84.   0: invalid distribution method
    
85. sel - selectivity
    
86. ptn - partition
    
87. ***************************************
    
88. PARAMETERS USED BY THE OPTIMIZER
    
89. ********************************
    
90.   *************************************
    
91.   PARAMETERS WITH ALTERED VALUES
    
92.   ******************************
    
93. Compilation Environment Dump
    
94. parallel_max_degree = 4
    
95. 
    
96. 
    
97.   ***************************************
    
98.   PARAMETERS IN OPT_PARAM HINT
    
99.   ****************************
    
100. ***************************************
     
101. Column Usage Monitoring is ON: tracking level = 1
     
102. ***************************************
     
103. 
     
104. Considering Query Transformations on query block SEL$1 (#0)
     
105. **************************
     
106. Query transformations (QT)
     
107. **************************
     
108. JF: Checking validity of join factorization for query block SEL$1 (#0)
     
109. JF: Bypassed: not a UNION or UNION-ALL query block.
     
110. ST: not valid since star transformation parameter is FALSE
     
111. TE: Checking validity of table expansion for query block SEL$1 (#0)
     
112. TE: Bypassed: No partitioned table in query block.
     
113. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
     
114. CBQT: Validity checks failed for g126tztfnzvrd.
     
115. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
     
116. *************************
     
117. Common Subexpression elimination (CSE)
     
118. *************************
     
119. CSE: CSE not performed on query block SEL$1 (#0).
     
120. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
     
121. ***************************
     
122. Order-by elimination (OBYE)
     
123. ***************************
     
124. OBYE: OBYE bypassed: no order by to eliminate.
     
125. JE: Considering Join Elimination on query block SEL$1 (#0)
     
126. *************************
     
127. Join Elimination (JE)
     
128. *************************
     
129. SQL:******* UNPARSED QUERY IS *******
     
130. 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
     
131. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
     
132. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
     
133. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
     
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. Query block SEL$1 (#0) unchanged
     
137. CVM: Considering view merge in query block SEL$1 (#0)
     
138. JE: Considering Join Elimination on query block SEL$1 (#0)
     
139. *************************
     
140. Join Elimination (JE)
     
141. *************************
     
142. SQL:******* UNPARSED QUERY IS *******
     
143. 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
     
144. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
     
145. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
     
146. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
     
147. SQL:******* UNPARSED QUERY IS *******
     
148. 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
     
149. Query block SEL$1 (#0) unchanged
     
150. query block SEL$1 (#0) unchanged
     
151. Considering Query Transformations on query block SEL$1 (#0)
     
152. **************************
     
153. Query transformations (QT)
     
154. **************************
     
155. JF: Checking validity of join factorization for query block SEL$1 (#0)
     
156. JF: Bypassed: not a UNION or UNION-ALL query block.
     
157. ST: not valid since star transformation parameter is FALSE
     
158. TE: Checking validity of table expansion for query block SEL$1 (#0)
     
159. TE: Bypassed: No partitioned table in query block.
     
160. CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
     
161. CBQT: Validity checks failed for g126tztfnzvrd.
     
162. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
     
163. *************************
     
164. Common Subexpression elimination (CSE)
     
165. *************************
     
166. CSE: CSE not performed on query block SEL$1 (#0).
     
167. SU: Considering subquery unnesting in query block SEL$1 (#0)
     
168. ********************
     
169. Subquery Unnest (SU)
     
170. ********************
     
171. SJC: Considering set-join conversion in query block SEL$1 (#0)
     
172. *************************
     
173. Set-Join Conversion (SJC)
     
174. *************************
     
175. SJC: not performed
     
176. JE: Considering Join Elimination on query block SEL$1 (#0)
     
177. *************************
     
178. Join Elimination (JE)
     
179. *************************
     
180. SQL:******* UNPARSED QUERY IS *******
     
181. 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
     
182. JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
     
183. JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
     
184. JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
     
185. SQL:******* UNPARSED QUERY IS *******
     
186. 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
     
187. Query block SEL$1 (#0) unchanged
     
188. PM: Considering predicate move-around in query block SEL$1 (#0)
     
189. **************************
     
190. Predicate Move-Around (PM)
     
191. **************************
     
192. PM: PM bypassed: Outer query contains no views.
     
193. PM: PM bypassed: Outer query contains no views.
     
194. query block SEL$1 (#0) unchanged
     
195. FPD: Considering simple filter push in query block SEL$1 (#0)
     
196. E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
     
197. try to generate transitive predicate from check constraints for query block SEL$1 (#0)
     
198. 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
     
199. 
     
200. FPD: transitive predicates are generated in query block SEL$1 (#0)
     
201. 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
     
202. apadrv-start sqlid=17332342852445794029
     
203.   :
     
204.     call(in-use=2208, alloc=16360), compile(in-use=61336, alloc=63432), execution(in-use=2088, alloc=4060)
     
205. 
     
206. *******************************************
     
207. Peeked values of the binds in SQL statement
     
208. *******************************************
     
209. 
     
210. Final query after transformations:******* UNPARSED QUERY IS *******
     
211. 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
     
212. kkoqbc: optimizing query block SEL$1 (#0)
     
213.         
     
214.         :
     
215.     call(in-use=2436, alloc=16360), compile(in-use=62156, alloc=63432), execution(in-use=2088, alloc=4060)
     
216. 
     
217. kkoqbc-subheap (create addr=0x705628)
     
218. ****************
     
219. QUERY BLOCK TEXT
     
220. ****************
     
221. select /*sample 11*/e.ename
     
222. from emp e, emp m
     
223. where e.mgr=m.empno
     
224. and m.deptno = 10
     
225. and e.deptno = m.deptno
     
226. and e.sal>1500
     
227. and m.sal>e.sal
     
228. ---------------------
     
229. QUERY BLOCK SIGNATURE
     
230. ---------------------
     
231. signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
     
232.   fro(0): flg=0 objn=73181 hint_alias=E@SEL$1
     
233.   fro(1): flg=0 objn=73181 hint_alias=M@SEL$1
     
234. 
     
235. -----------------------------
     
236. SYSTEM STATISTICS INFORMATION
     
237. -----------------------------
     
238.   Using NOWORKLOAD Stats
     
239.   CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
     
240.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
     
241.   IOSEEKTIM: 10 milliseconds (default is 10)
     
242.   MBRC: -1 blocks (default is 8)
     
243. 
     
244. ***************************************
     
245. BASE STATISTICAL INFORMATION
     
246. ***********************
     
247. Table Stats::
     
248.   Table: EMP Alias: M
     
249.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
     
250. Index Stats::
     
251.   Index: PK_EMP Col#: 1
     
252.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
     
253. ***********************
     
254. Table Stats::
     
255.   Table: EMP Alias: E
     
256.     #Rows: 14 #Blks: 5 AvgRowLen: 41.00
     
257. Index Stats::
     
258.   Index: PK_EMP Col#: 1
     
259.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
     
260. Access path analysis for EMP
     
261. ***************************************
     
262. SINGLE TABLE ACCESS PATH
     
263.   Single Table Cardinality Estimation for EMP[E]
     
264. 
     
265.   Table: EMP Alias: E
     
266.     Card: Original: 14.000000 Rounded: 4 Computed: 3.74 Non Adjusted: 3.74
     
267.   Access Path: TableScan
     
268.     Cost: 3.00 Resp: 3.00 Degree: 0
     
269.       Cost_io: 3.00 Cost_cpu: 40677
     
270.       Resp_io: 3.00 Resp_cpu: 40677
     
271.   Best:: AccessPath: TableScan
     
272.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 3.74 Bytes: 0
     
273. 
     
274. Access path analysis for EMP
     
275. ***************************************
     
276. SINGLE TABLE ACCESS PATH
     
277.   Single Table Cardinality Estimation for EMP[M]
     
278. 
     
279.   Table: EMP Alias: M
     
280.     Card: Original: 14.000000 Rounded: 4 Computed: 4.03 Non Adjusted: 4.03
     
281.   Access Path: TableScan
     
282.     Cost: 3.00 Resp: 3.00 Degree: 0
     
283.       Cost_io: 3.00 Cost_cpu: 40601
     
284.       Resp_io: 3.00 Resp_cpu: 40601
     
285.   Best:: AccessPath: TableScan
     
286.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.03 Bytes: 0
     
287. 
     
288. ***************************************
     
289. 
     
290. 
     
291. OPTIMIZER STATISTICS AND COMPUTATIONS
     
292. ***************************************
     
293. GENERAL PLANS
     
294. ***************************************
     
295. Considering cardinality-based initial join order.
     
296. Permutations for Starting Table :0
     
297. Join order[1]: EMP[E]#0 EMP[M]#1
     
298. 
     
299. ***************
     
300. Now joining: EMP[M]#1
     
301. ***************
     
302. NL Join
     
303.   Outer table: Card: 3.74 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
     
304. Access path analysis for EMP
     
305.   Inner table: EMP Alias: M
     
306.   Access Path: TableScan
     
307.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
     
308.       Cost_io: 10.00 Cost_cpu: 203079
     
309.       Resp_io: 10.00 Resp_cpu: 203079
     
310.   Access Path: index (UniqueScan)
     
311.     Index: PK_EMP
     
312.     resc_io: 1.00 resc_cpu: 8528
     
313.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
     
314.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
     
315.       Cost_io: 7.00 Cost_cpu: 74790
     
316.       Resp_io: 7.00 Resp_cpu: 74790
     
317.   Access Path: index (AllEqUnique)
     
318.     Index: PK_EMP
     
319.     resc_io: 1.00 resc_cpu: 8528
     
320.     ix_sel: 0.071429 ix_sel_with_filters: 0.071429
     
321.     NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
     
322.       Cost_io: 7.00 Cost_cpu: 74790
     
323.       Resp_io: 7.00 Resp_cpu: 74790
     
324. 
     
325.   Best NL cost: 7.00
     
326.           resc: 7.00 resc_io: 7.00 resc_cpu: 74790
     
327.           resp: 7.00 resp_io: 7.00 resc_cpu: 74790
     
328. Join Card: 1.144145 = = outer (3.744856) * inner (4.032922) * sel (0.075758)
     
329. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 3.744856, inner: 4.032922, sel: 0.075758
     
330. Join Card - Rounded: 1 Computed: 1.14
     
331.   Outer table: EMP Alias: E
     
332.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
     
333.   Inner table: EMP Alias: M
     
334.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
     
335.     using dmeth: 2 #groups: 1
     
336.     SORT ressource Sort statistics
     
337.       Sort width: 396 Area size: 347136 Max Area size: 69624832
     
338.       Degree: 1
     
339.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
     
340.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
     
341.       Total IO sort cost: 0 Total CPU sort cost: 32353029
     
342.       Total Temp space used: 0
     
343.     SORT ressource Sort statistics
     
344.       Sort width: 396 Area size: 347136 Max Area size: 69624832
     
345.       Degree: 1
     
346.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
     
347.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
     
348.       Total IO sort cost: 0 Total CPU sort cost: 32353029
     
349.       Total Temp space used: 0
     
350.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
     
351. SM Join
     
352.   SM cost: 8.00
     
353.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
     
354.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
     
355.   Outer table: EMP Alias: E
     
356.     resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
     
357.   Inner table: EMP Alias: M
     
358.     resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
     
359.     using dmeth: 2 #groups: 1
     
360.     Cost per ptn: 0.50 #ptns: 1
     
361.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
     
362.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
     
363. HA Join
     
364.   HA cost: 6.50
     
365.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
     
366.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
     
367. Best:: JoinMethod: Hash
     
368.        Cost: 6.50 Degree: 1 Resp: 6.50 Card: 1.14 Bytes: 31
     
369. ***********************
     
370. Best so far: Table#: 0 cost: 3.0013 card: 3.7449 bytes: 80
     
371.               Table#: 1 cost: 6.5025 card: 1.1441 bytes: 31
     
372. ***********************
     
373. Join order[2]: EMP[M]#1 EMP[E]#0
     
374. 
     
375. ***************
     
376. Now joining: EMP[E]#0
     
377. ***************
     
378. NL Join
     
379.   Outer table: Card: 4.03 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 11
     
380. Access path analysis for EMP
     
381.   Inner table: EMP Alias: E
     
382.   Access Path: TableScan
     
383.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
     
384.       Cost_io: 10.00 Cost_cpu: 203325
     
385.       Resp_io: 10.00 Resp_cpu: 203325
     
386. 
     
387.   Best NL cost: 10.01
     
388.           resc: 10.01 resc_io: 10.00 resc_cpu: 203325
     
389.           resp: 10.01 resp_io: 10.00 resc_cpu: 203325
     
390. Join Card: 1.144145 = = outer (4.032922) * inner (3.744856) * sel (0.075758)
     
391. Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 4.032922, inner: 3.744856, sel: 0.075758
     
392. Join Card - Rounded: 1 Computed: 1.14
     
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.     SORT ressource Sort statistics
     
399.       Sort width: 396 Area size: 347136 Max Area size: 69624832
     
400.       Degree: 1
     
401.       Blocks to Sort: 1 Row size: 23 Total Rows: 4
     
402.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
     
403.       Total IO sort cost: 0 Total CPU sort cost: 32353029
     
404.       Total Temp space used: 0
     
405.     SORT ressource Sort statistics
     
406.       Sort width: 396 Area size: 347136 Max Area size: 69624832
     
407.       Degree: 1
     
408.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
     
409.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
     
410.       Total IO sort cost: 0 Total CPU sort cost: 32353029
     
411.       Total Temp space used: 0
     
412.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
     
413. SM Join
     
414.   SM cost: 8.00
     
415.      resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
     
416.      resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
     
417.   Outer table: EMP Alias: M
     
418.     resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
     
419.   Inner table: EMP Alias: E
     
420.     resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
     
421.     using dmeth: 2 #groups: 1
     
422.     Cost per ptn: 0.50 #ptns: 1
     
423.     hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
     
424.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
     
425. HA Join
     
426.   HA cost: 6.50
     
427.      resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
     
428.      resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
     
429. Join order aborted: cost > best plan cost
     
430. ***********************
     
431. (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
     
432. 
     
433. *********************************
     
434. Number of join permutations tried: 2
     
435. *********************************
     
436. Consider using bloom filter between E[EMP] and M[EMP]
     
437. kkoBloomFilter: join (lcdn:4 rcdn:4 jcdn:1 limit:8)
     
438. Computing bloom ndv for creator:E[EMP] ccdn:3.7 and user:M[EMP] ucdn:4.0
     
439. kkopqComputeBloomNdv: predicate (bndv:6 ndv:6) and (bndv:14 ndv:5)
     
440. kkopqComputeBloomNdv: predicate (bndv:3 ndv:3) and (bndv:3 ndv:3)
     
441. kkopqComputeBloomNdv: predicate (bndv:12 ndv:3) and (bndv:12 ndv:4)
     
442. kkopqComputeBloomNdv: pred cnt:6 ndv:4 reduction:1
     
443. kkoBloomFilter: join ndv:0 reduction:0.928571 (limit:0.500000) rejected because distinct value ratio
     
444. (newjo-save) [1 0 ]
     
445. Trying or-Expansion on query block SEL$1 (#0)
     
446. Transfer Optimizer annotations for query block SEL$1 (#0)
     
447. id=0 frofand predicate=E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500
     
448. id=0 frofkksm[i] (sort-merge/hash) predicate=E.MGR=M.EMPNO
     
449. id=0 frofkksm[i] (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO
     
450. id=0 frosand (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO AND E.MGR=M.EMPNO
     
451. id=0 frofand predicate=M.DEPTNO=10 AND M.SAL>1500
     
452. id=0 frojand (sort-merge/hash) predicate=M.SAL>E.SAL
     
453. Final cost for query block SEL$1 (#0) - All Rows Plan:
     
454.   Best join order: 1
     
455.   Cost: 6.5025 Degree: 1 Card: 1.0000 Bytes: 31
     
456.   Resc: 6.5025 Resc_io: 6.0000 Resc_cpu: 16258612
     
457.   Resp: 6.5025 Resp_io: 6.0000 Resc_cpu: 16258612
     
458. kkoqbc-subheap (delete addr=0x705628, in-use=24852, alloc=25480)
     
459. kkoqbc-end:
     
460.         :
     
461.     call(in-use=13332, alloc=49168), compile(in-use=63592, alloc=67556), execution(in-use=2088, alloc=4060)
     
462. 
     
463. kkoqbc: finish optimizing query block SEL$1 (#0)
     
464. apadrv-end
     
465.           :
     
466.     call(in-use=13332, alloc=49168), compile(in-use=64228, alloc=67556), execution(in-use=2088, alloc=4060)
     
467. 
     
468. 
     
469. Starting SQL statement dump
     
470. 
     
471. user_id=84 user_name=SCOTT module=SQL*Plus action=
     
472. sql_id=g126tztfnzvrd plan_hash_value=-656709420 problem_type=3
     
473. ----- Current SQL Statement for this session (sql_id=g126tztfnzvrd) -----
     
474. select /*sample 11*/e.ename
     
475. from emp e, emp m
     
476. where e.mgr=m.empno
     
477. and m.deptno = 10
     
478. and e.deptno = m.deptno
     
479. and e.sal>1500
     
480. and m.sal>e.sal
     
481. sql_text_length=139
     
482. sql=select /*sample 11*/e.ename
     
483. from emp e, emp m
     
484. where e.mgr=m.empno
     
485. and m.deptno = 10
     
486. and e.deptno = m.deptno
     
487. and e.sal>1500
     
488. and m.sal>e.sal
     
489. ----- Explain Plan Dump -----
     
490. ----- Plan Table -----
     
491.  
     
492. ============
     
493. 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/，如需轉載，請註明出處，否則將追究法律責任。