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一個查詢,如下:
Sub-quries:
=
in
exists
not in
not exists
any/some
all
Sing-column
multi-column
single-table
multi-table
-----------------------------------------------------------------------------------------------------------------------------------------------------
oracle會自動transformation成select * from emp;執行計劃相同。
以下三種oracle 也做了transformation
一個查詢發起的時候,首先要做啟發式的transformation,轉化好之後再做優化,最後生成執行計劃。

看10053,首先出現的是transformation,然後收集統計資訊,之後cost比較,最後執行計劃。
啟發式transformation(所謂啟發式就是固定的,硬程式碼寫的,先於cost評估,oracle轉化成語義相等的sql,然後再算cost)
啟發式transformation包含的方法

實驗:
10053事件的使用方法和10046是一樣的,首先給事件設定一個級別level 1,然後執行sql(或者直接使用explain plan的方式產生執行計劃),最後終止事件。10053事件產生的trace檔案不能用tkprof工具格式話,tkprof工具只能格式化sql_trace和10046事件產生的trace檔案,直接閱讀這個原始檔案就可以。
接下來就是真正的做transformation,前面是告訴你現有的環境,接下來是真正的工作
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時候,就過濾很多了,減少匹配
後面的是重新提了一下
fix 11738631 = enabled這個數其實是一個bug,去MOS上一查就是到他修復了一個什麼問題。有些時候會把bug修壞、把他關掉就是說這個fix就不起作用了。
query block 他會把sql語句拆成幾塊 如果有子查詢,子查詢就是一個query block,oracle做優化其實是對每一個query block做優化的。
10053對於解決問題有時候很重要,雖然不是解決sql問題首先要看的~
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一個查詢,如下:
點選(此處)摺疊或開啟
-
SQL> select a.* from emp a ,
-
2 (select * from dept where deptno > 20) b
-
3 where a.deptno = b.deptno;
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
-
7499 YAOCHONG SALESMAN 7698 20-FEB-81 1600 300 30
-
7521 YAOCHONG SALESMAN 7698 22-FEB-81 1250 500 30
-
7654 YAOCHONG SALESMAN 7698 28-SEP-81 1250 1400 30
-
7698 YAOCHONG MANAGER 7839 01-MAY-81 2850 30
-
7844 YAOCHONG SALESMAN 7698 08-SEP-81 1500 0 30
-
7900 YAOCHONG CLERK 7698 03-DEC-81 950 30
-
- 6 rows selected.
=
in
點選(此處)摺疊或開啟
-
SQL> select * from emp where deptno in
-
2 (select deptno from dept where deptno > 20);
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
-
7499 YAOCHONG SALESMAN 7698 20-FEB-81 1600 300 30
-
7521 YAOCHONG SALESMAN 7698 22-FEB-81 1250 500 30
-
7654 YAOCHONG SALESMAN 7698 28-SEP-81 1250 1400 30
-
7698 YAOCHONG MANAGER 7839 01-MAY-81 2850 30
-
7844 YAOCHONG SALESMAN 7698 08-SEP-81 1500 0 30
- 7900 YAOCHONG CLERK 7698 03-DEC-81 950 30
not in
not exists
any/some
all
Sing-column
multi-column
single-table
multi-table
-----------------------------------------------------------------------------------------------------------------------------------------------------
點選(此處)摺疊或開啟
-
SQL> explain plan for select * from (select * from emp);
-
-
Explained.
-
-
SQL> @?/rdbms/admin/utlxplp
-
-
PLAN_TABLE_OUTPUT
-
---------------------------------------------------------------------------------------
-
Plan hash value: 3956160932
-
-
--------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 14 | 574 | 3 (0) | 00:00:01|
-
| 1 | TABLE ACCESS FULL| EMP | 14 | 574 | 3 (0) | 00:00:01|
- --------------------------------------------------------------------------
點選(此處)摺疊或開啟
-
SQL> @?/rdbms/admin/utlxplp
-
-
PLAN_TABLE_OUTPUT
-
----------------------------------------------------------------------------------------
-
Plan hash value: 3956160932
-
-
--------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 14 | 574 | 3 (0) | 00:00:01 |
-
| 1 | TABLE ACCESS FULL| EMP | 14 | 574 | 3 (0) | 00:00:01 |
以下三種oracle 也做了transformation
點選(此處)摺疊或開啟
-
SQL> explain plan for select * from (select * from emp) where empno = 7788;
-
-
Explained.
-
-
SQL> @?/rdbms/admin/utlxplp
-
-
PLAN_TABLE_OUTPUT
-
-------------------------------------------------------------------------------------------
-
Plan hash value: 2949544139
-
-
--------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 41 | 1 (0) | 00:00:01 |
-
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 41 | 1 (0) | 00:00:01 |
-
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 |
-
--------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
2 - access("EMP"."EMPNO"=7788)
-
- 14 rows selected.
點選(此處)摺疊或開啟
-
SQL> explain plan for select * from ( select * from emp where empno = 7788);
-
-
Explained.
-
-
SQL> @?/rdbms/admin/utlxplp
-
-
PLAN_TABLE_OUTPUT
-
-------------------------------------------------------------------------------------------
-
Plan hash value: 2949544139
-
-
--------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 41 | 1 (0) | 00:00:01 |
-
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 41 | 1 (0) | 00:00:01 |
-
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 |
-
--------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
2 - access(\"EMPNO\"=7788)
-
- 14 rows selected.
點選(此處)摺疊或開啟
-
SQL> explain plan for select * from emp where empno = 7788;
-
-
Explained.
-
-
SQL> @?/rdbms/admin/utlxplp
-
-
PLAN_TABLE_OUTPUT
-
------------------------------------------------------------------------------------------
-
Plan hash value: 2949544139
-
-
--------------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 41 | 1 (0) | 00:00:01 |
-
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 41 | 1 (0) | 00:00:01 |
-
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 |
-
--------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
-
2 - access(\"EMPNO\"=7788)
-
- 14 rows selected.

看10053,首先出現的是transformation,然後收集統計資訊,之後cost比較,最後執行計劃。
啟發式transformation(所謂啟發式就是固定的,硬程式碼寫的,先於cost評估,oracle轉化成語義相等的sql,然後再算cost)
- 先於cost評估
- 剔除多餘的操作
- 最小化quary block
- 在多個可能的transformation路徑中選擇----cost的過程中產生的(區別於啟發式之後再cost)。
- 從多個transformation的結果中,找到最好的plan
啟發式transformation包含的方法

實驗:
點選(此處)摺疊或開啟
-
set line 200
-
set pages 200
-
@10053.sql 即:alter session set events '10053 trace name context forever, level 1';
-
select /*sample 1*/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;
-
@10053d.sql 即:alter session set events '10053 trace name context off';
set line 2000
set pages 200 - select TRACEFILE from v$process where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1));
-
TRACEFILE
------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_22134.trc -
-
select * from table(dbms_xplan.display_cursor(null,null,'last'));
-
-
/*
-
FPD: transitive predicates are generated in query block SEL$1 (#0)
-
"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
- */
- SQL> !vi /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_27067.trc
點選(此處)摺疊或開啟
-
Trace file /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_22134.trc
-
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production ------------版本是什麼
-
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, -----是rac的
-
Data Mining and Real Application Testing options
-
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
-
System name: Linux ---------平臺
-
Node name: node1
-
Release: 2.6.18-194.el5
-
Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010
-
Machine: i686
-
Instance name: prod1
-
Redo thread mounted by this instance: 1
-
Oracle process number: 34
-
Unix process pid: 22134, image: oracle@node1 (TNS V1-V3) -------以上資訊對於support來說很重要,我們關注下版本是什麼
-
-
-
*** 2014-11-01 01:37:48.076
-
*** SESSION ID:(59.486) 2014-11-01 01:37:48.076
-
*** CLIENT ID:() 2014-11-01 01:37:48.076
-
*** SERVICE NAME:(SYS$USERS) 2014-11-01 01:37:48.076
-
*** MODULE NAME:(SQL*Plus) 2014-11-01 01:37:48.076
-
*** ACTION NAME:() 2014-11-01 01:37:48.076
-
-
Registered qb: SEL$1 0x708b6c (PARSER)
-
---------------------
-
QUERY BLOCK SIGNATURE ----10053最先做的是query block的註冊
-
---------------------
- signature (): qb_name=SEL$1 nbfros=2 flg=0 -----先註冊一個quary block 名字是SEL$1,如果語句多會分成多個query block,
- query block可以用hint qb_name指定
-
fro(0): flg=4 objn=73181 hint_alias="E"@"SEL$1"
-
fro(1): flg=4 objn=73181 hint_alias="M"@"SEL$1" ------10053第一次做quary block的註冊。
-
-
SPM: statement not found in SMB
-
-
**************************
-
Automatic degree of parallelism (ADOP)
-
**************************
-
Automatic degree of parallelism is disabled: Parameter.
-
-
PM: Considering predicate move-around in query block SEL$1 (#0) ----第二步做predicate move-around考慮能不能講位詞給祛除掉
-
**************************
-
Predicate Move-Around (PM)
-
**************************
-
OPTIMIZER INFORMATION
-
-
******************************************
-
----- Current SQL Statement for this session (sql_id=4usswnngj0jdx) -----sql id也告訴你了
-
select /*sample 1*/e.ename -----然後告訴你當前的sql是什麼樣子的
-
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
-
******************************************* -------這以下都是transformation的方法,解釋一些縮寫的含義
-
Legend
-
The following abbreviations are used by optimizer trace.
-
CBQT - cost-based query transformation
-
JPPD - join predicate push-down
-
OJPPD - old-style (non-cost-based) JPPD ---不考慮成本的cost
-
FPD - filter push-down
-
PM - predicate move-around
-
CVM - complex view merging
-
SPJ - select-project-join
-
SJC - set join conversion
-
SU - subquery unnesting
-
OBYE - order by elimination
-
OST - old style star transformation
-
ST - new (cbqt) star transformation
-
CNT - count(col) to count(*) transformation
-
JE - Join Elimination
-
JF - join factorization
-
SLP - select list pruning
-
DP - distinct placement
-
qb - query block
-
LB - leaf blocks
-
DK - distinct keys
-
LB/K - average number of leaf blocks per key
-
DB/K - average number of data blocks per key
-
CLUF - clustering factor
-
NDV - number of distinct values
-
Resp - response cost
-
Card - cardinality
-
Resc - resource cost
-
NL - nested loops (join)
-
SM - sort merge (join)
-
HA - hash (join)
-
CPUSPEED - CPU Speed
-
IOTFRSPEED - I/O transfer speed
-
IOSEEKTIM - I/O seek time
-
SREADTIM - average single block read time
-
MREADTIM - average multiblock read time
-
MBRC - average multiblock read count
-
MAXTHR - maximum I/O system throughput
- SLAVETHR - average slave I/O throughput
-
dmeth - distribution method
-
1: no partitioning required
-
2: value partitioned
-
4: right is random (round-robin)
-
128: left is random (round-robin)
-
8: broadcast right and partition left
-
16: broadcast left and partition right
-
32: partition left using partitioning of right
-
64: partition right using partitioning of left
-
256: run the join in serial
-
0: invalid distribution method
-
sel - selectivity
-
ptn - partition
- 這裡是修復bug的資訊
-
***************************************
-
PARAMETERS USED BY THE OPTIMIZER -----這裡告訴你系統的統計資訊
-
********************************
-
*************************************
-
PARAMETERS WITH ALTERED VALUES
-
******************************
-
Compilation Environment Dump ---這個下面這一塊是必看的一個章節,這個下面有什麼說明,改過的cbo引數一定要看的。是不是改過
-
parallel_max_degree = 4
-
Bug Fix Control Environment
-
-
-
************************************* 和cbo有關的引數都列在這裡,這些引數可以在session級別關閉
-
PARAMETERS WITH DEFAULT VALUES
-
******************************
-
Compilation Environment Dump
-
optimizer_mode_hinted = false
-
optimizer_features_hinted = 0.0.0
-
parallel_execution_enabled = true
-
parallel_query_forced_dop = 0
-
parallel_dml_forced_dop = 0
-
parallel_ddl_forced_degree = 0
-
parallel_ddl_forced_instances = 0
-
_query_rewrite_fudge = 90
-
optimizer_features_enable = 11.2.0.1
-
_optimizer_search_limit = 5
-
cpu_count = 1
-
active_instance_count = 2
-
parallel_threads_per_cpu = 2
-
hash_area_size = 131072
-
bitmap_merge_area_size = 1048576
-
sort_area_size = 65536
-
sort_area_retained_size = 0
-
_sort_elimination_cost_ratio = 0
-
_optimizer_block_size = 8192
-
_sort_multiblock_read_count = 2
-
_hash_multiblock_io_count = 0
-
_db_file_optimizer_read_count = 8
-
_optimizer_max_permutations = 2000
-
pga_aggregate_target = 339968 KB
-
_pga_max_size = 204800 KB
-
_query_rewrite_maxdisjunct = 257
-
_smm_auto_min_io_size = 56 KB
-
_smm_auto_max_io_size = 248 KB
-
_smm_min_size = 339 KB
-
_smm_max_size = 67993 KB
-
_smm_px_max_size = 169984 KB
-
_cpu_to_io = 0
-
_optimizer_undo_cost_change = 11.2.0.1
-
parallel_query_mode = enabled
-
parallel_dml_mode = disabled
-
parallel_ddl_mode = enabled
-
optimizer_mode = all_rows
-
sqlstat_enabled = false
-
_optimizer_percent_parallel = 101
-
_always_anti_join = choose
-
_always_semi_join = choose
-
_optimizer_mode_force = true
-
_partition_view_enabled = true
-
_always_star_transformation = false
-
_query_rewrite_or_error = false
-
_hash_join_enabled = true
-
cursor_sharing = exact
-
_b_tree_bitmap_plans = true
-
star_transformation_enabled = false
-
_optimizer_cost_model = choose
-
_new_sort_cost_estimate = true
-
_complex_view_merging = true
-
_unnest_subquery = true
-
_eliminate_common_subexpr = true
-
_pred_move_around = true
-
_convert_set_to_join = false
-
_push_join_predicate = true
-
_push_join_union_view = true
-
_fast_full_scan_enabled = true
-
_optim_enhance_nnull_detection = true
-
_parallel_broadcast_enabled = true
-
_px_broadcast_fudge_factor = 100
-
_ordered_nested_loop = true
-
_no_or_expansion = false
-
optimizer_index_cost_adj = 100
-
optimizer_index_caching = 0
-
_system_index_caching = 0
-
_disable_datalayer_sampling = false
-
query_rewrite_enabled = true
-
query_rewrite_integrity = enforced
-
_query_cost_rewrite = true
-
_query_rewrite_2 = true
-
_query_rewrite_1 = true
-
_query_rewrite_expression = true
-
_query_rewrite_jgmigrate = true
-
_query_rewrite_fpc = true
-
_query_rewrite_drj = true
-
_full_pwise_join_enabled = true
-
_partial_pwise_join_enabled = true
-
_left_nested_loops_random = true
-
_improved_row_length_enabled = true
-
_index_join_enabled = true
-
_enable_type_dep_selectivity = true
-
_improved_outerjoin_card = true
-
_optimizer_adjust_for_nulls = true
-
_optimizer_degree = 0
-
_use_column_stats_for_function = true
-
_subquery_pruning_enabled = true
-
_subquery_pruning_mv_enabled = false
-
_or_expand_nvl_predicate = true
-
_like_with_bind_as_equality = false
-
_table_scan_cost_plus_one = true
-
_cost_equality_semi_join = true
-
_default_non_equality_sel_check = true
-
_new_initial_join_orders = true
-
_oneside_colstat_for_equijoins = true
-
_optim_peek_user_binds = true
-
_minimal_stats_aggregation = true
-
_force_temptables_for_gsets = false
-
workarea_size_policy = auto
-
_smm_auto_cost_enabled = true
-
_gs_anti_semi_join_allowed = true
-
_optim_new_default_join_sel = true
-
optimizer_dynamic_sampling = 2
-
_pre_rewrite_push_pred = true
-
_optimizer_new_join_card_computation = true
-
_union_rewrite_for_gs = yes_gset_mvs
-
_generalized_pruning_enabled = true
-
_optim_adjust_for_part_skews = true
-
_force_datefold_trunc = false
-
statistics_level = typical
-
_optimizer_system_stats_usage = true
-
skip_unusable_indexes = true
-
_remove_aggr_subquery = true
-
_optimizer_push_down_distinct = 0
-
_dml_monitoring_enabled = true
-
_optimizer_undo_changes = false
-
_predicate_elimination_enabled = true
-
_nested_loop_fudge = 100
-
_project_view_columns = true
-
_local_communication_costing_enabled = true
-
_local_communication_ratio = 50
-
_query_rewrite_vop_cleanup = true
-
_slave_mapping_enabled = true
-
_optimizer_cost_based_transformation = linear
-
_optimizer_mjc_enabled = true
-
_right_outer_hash_enable = true
-
_spr_push_pred_refspr = true
-
_optimizer_cache_stats = false
-
_optimizer_cbqt_factor = 50
-
_optimizer_squ_bottomup = true
-
_fic_area_size = 131072
-
_optimizer_skip_scan_enabled = true
-
_optimizer_cost_filter_pred = false
-
_optimizer_sortmerge_join_enabled = true
-
_optimizer_join_sel_sanity_check = true
-
_mmv_query_rewrite_enabled = true
-
_bt_mmv_query_rewrite_enabled = true
-
_add_stale_mv_to_dependency_list = true
-
_distinct_view_unnesting = false
-
_optimizer_dim_subq_join_sel = true
-
_optimizer_disable_strans_sanity_checks = 0
-
_optimizer_compute_index_stats = true
-
_push_join_union_view2 = true
-
_optimizer_ignore_hints = false
-
_optimizer_random_plan = 0
-
_query_rewrite_setopgrw_enable = true
-
_optimizer_correct_sq_selectivity = true
-
_disable_function_based_index = false
-
_optimizer_join_order_control = 3
-
_optimizer_cartesian_enabled = true
-
_optimizer_starplan_enabled = true
-
_extended_pruning_enabled = true
-
_optimizer_push_pred_cost_based = true
-
_optimizer_null_aware_antijoin = true
-
_optimizer_extend_jppd_view_types = true
-
_sql_model_unfold_forloops = run_time
-
_enable_dml_lock_escalation = false
-
_bloom_filter_enabled = true
-
_update_bji_ipdml_enabled = 0
-
_optimizer_extended_cursor_sharing = udo
-
_dm_max_shared_pool_pct = 1
-
_optimizer_cost_hjsmj_multimatch = true
-
_optimizer_transitivity_retain = true
-
_px_pwg_enabled = true
-
optimizer_secure_view_merging = true
-
_optimizer_join_elimination_enabled = true
-
flashback_table_rpi = non_fbt
-
_optimizer_cbqt_no_size_restriction = true
-
_optimizer_enhanced_filter_push = true
-
_optimizer_filter_pred_pullup = true
-
_rowsrc_trace_level = 0
-
_simple_view_merging = true
-
_optimizer_rownum_pred_based_fkr = true
-
_optimizer_better_inlist_costing = all
-
_optimizer_self_induced_cache_cost = false
-
_optimizer_min_cache_blocks = 10
-
_optimizer_or_expansion = depth
-
_optimizer_order_by_elimination_enabled = true
-
_optimizer_outer_to_anti_enabled = true
-
_selfjoin_mv_duplicates = true
-
_dimension_skip_null = true
-
_force_rewrite_enable = false
-
_optimizer_star_tran_in_with_clause = true
-
_optimizer_complex_pred_selectivity = true
-
_optimizer_connect_by_cost_based = true
-
_gby_hash_aggregation_enabled = true
-
_globalindex_pnum_filter_enabled = true
-
_px_minus_intersect = true
-
_fix_control_key = 0
-
_force_slave_mapping_intra_part_loads = false
-
_force_tmp_segment_loads = false
-
_query_mmvrewrite_maxpreds = 10
-
_query_mmvrewrite_maxintervals = 5
-
_query_mmvrewrite_maxinlists = 5
-
_query_mmvrewrite_maxdmaps = 10
-
_query_mmvrewrite_maxcmaps = 20
-
_query_mmvrewrite_maxregperm = 512
-
_query_mmvrewrite_maxmergedcmaps = 50
-
_query_mmvrewrite_maxqryinlistvals = 500
-
_disable_parallel_conventional_load = false
-
_trace_virtual_columns = false
-
_replace_virtual_columns = true
-
_virtual_column_overload_allowed = true
-
_kdt_buffering = true
-
_first_k_rows_dynamic_proration = true
-
_optimizer_sortmerge_join_inequality = true
-
_optimizer_aw_stats_enabled = true
-
_bloom_pruning_enabled = true
-
result_cache_mode = MANUAL
-
_px_ual_serial_input = true
-
_optimizer_skip_scan_guess = false
-
_enable_row_shipping = true
-
_row_shipping_threshold = 80
-
_row_shipping_explain = false
-
transaction_isolation_level = read_commited
-
_optimizer_distinct_elimination = true
-
_optimizer_multi_level_push_pred = true
-
_optimizer_group_by_placement = true
-
_optimizer_rownum_bind_default = 10
-
_enable_query_rewrite_on_remote_objs = true
-
_optimizer_extended_cursor_sharing_rel = simple
-
_optimizer_adaptive_cursor_sharing = true
-
_direct_path_insert_features = 0
-
_optimizer_improve_selectivity = true
-
optimizer_use_pending_statistics = false
-
_optimizer_enable_density_improvements = true
-
_optimizer_aw_join_push_enabled = true
-
_optimizer_connect_by_combine_sw = true
-
_enable_pmo_ctas = 0
-
_optimizer_native_full_outer_join = force
-
_bloom_predicate_enabled = true
-
_optimizer_enable_extended_stats = true
-
_is_lock_table_for_ddl_wait_lock = 0
-
_pivot_implementation_method = choose
-
optimizer_capture_sql_plan_baselines = false
-
optimizer_use_sql_plan_baselines = true
-
_optimizer_star_trans_min_cost = 0
-
_optimizer_star_trans_min_ratio = 0
-
_with_subquery = OPTIMIZER
-
_optimizer_fkr_index_cost_bias = 10
-
_optimizer_use_subheap = true
-
parallel_degree_policy = manual
-
parallel_degree = 0
-
parallel_min_time_threshold = 10
-
_parallel_time_unit = 10
-
_optimizer_or_expansion_subheap = true
-
_optimizer_free_transformation_heap = true
-
_optimizer_reuse_cost_annotations = true
-
_result_cache_auto_size_threshold = 100
-
_result_cache_auto_time_threshold = 1000
-
_optimizer_nested_rollup_for_gset = 100
-
_nlj_batching_enabled = 1
-
parallel_query_default_dop = 0
-
is_recur_flags = 0
-
optimizer_use_invisible_indexes = false
-
flashback_data_archive_internal_cursor = 0
-
_optimizer_extended_stats_usage_control = 224
-
_parallel_syspls_obey_force = true
-
cell_offload_processing = true
-
_rdbms_internal_fplib_enabled = false
-
db_file_multiblock_read_count = 79
-
_bloom_folding_enabled = true
-
_mv_generalized_oj_refresh_opt = true
-
cell_offload_compaction = ADAPTIVE
-
parallel_degree_limit = 65535
-
parallel_force_local = false
-
total_cpu_count = 2
-
cell_offload_plan_display = AUTO
-
_optimizer_coalesce_subqueries = true
-
_optimizer_fast_pred_transitivity = true
-
_optimizer_fast_access_pred_analysis = true
-
_optimizer_unnest_disjunctive_subq = true
-
_optimizer_unnest_corr_set_subq = true
-
_optimizer_distinct_agg_transform = true
-
_aggregation_optimization_settings = 0
-
_optimizer_connect_by_elim_dups = true
-
_optimizer_eliminate_filtering_join = true
-
_connect_by_use_union_all = true
-
dst_upgrade_insert_conv = true
-
advanced_queuing_internal_cursor = 0
-
_optimizer_unnest_all_subqueries = true
-
_bloom_predicate_pushdown_to_storage = true
-
_bloom_vector_elements = 0
-
_bloom_pushing_max = 524288
-
parallel_autodop = 0
-
parallel_ddldml = 0
-
_parallel_cluster_cache_policy = adaptive
-
_parallel_scalability = 50
-
iot_internal_cursor = 0
-
_optimizer_instance_count = 0
-
_optimizer_connect_by_cb_whr_only = false
-
_suppress_scn_chk_for_cqn = nosuppress_1466
-
_optimizer_join_factorization = true
-
_optimizer_use_cbqt_star_transformation = true
-
_optimizer_table_expansion = true
-
_and_pruning_enabled = true
-
_deferred_constant_folding_mode = DEFAULT
-
_optimizer_distinct_placement = true
-
partition_pruning_internal_cursor = 0
-
parallel_hinted = none
-
_sql_compatibility = 0
-
_optimizer_use_feedback = true
-
_optimizer_try_st_before_jppd = true
- Bug Fix Control Environment ---有的時候的查詢和bug fix有關係,他為了改一個bug會產生另外的問題。這時候你可以把這個bug fix disable掉,再去跑一下看看能不能好,如果好了的話,你會得到一個好的執行計劃。
-
fix 3834770 = 1
-
fix 3746511 = enabled
-
fix 4519016 = enabled
-
fix 3118776 = enabled
-
fix 4488689 = enabled
-
fix 2194204 = disabled
-
fix 2660592 = enabled
-
fix 2320291 = enabled
-
fix 2324795 = enabled
-
fix 4308414 = enabled
-
fix 3499674 = disabled
-
fix 4569940 = enabled
-
fix 4631959 = enabled
-
fix 4519340 = enabled
-
fix 4550003 = enabled
-
fix 1403283 = enabled
-
fix 4554846 = enabled
-
fix 4602374 = enabled
-
fix 4584065 = enabled
-
fix 4545833 = enabled
-
fix 4611850 = enabled
-
fix 4663698 = enabled
-
fix 4663804 = enabled
-
fix 4666174 = enabled
-
fix 4567767 = enabled
-
fix 4556762 = 15
-
fix 4728348 = enabled
-
fix 4708389 = enabled
-
fix 4175830 = enabled
-
fix 4752814 = enabled
-
fix 4583239 = enabled
-
fix 4386734 = enabled
-
fix 4887636 = enabled
-
fix 4483240 = enabled
-
fix 4872602 = disabled
-
fix 4711525 = enabled
-
fix 4545802 = enabled
-
fix 4605810 = enabled
-
fix 4704779 = enabled
-
fix 4900129 = enabled
-
fix 4924149 = enabled
-
fix 4663702 = enabled
-
fix 4878299 = enabled
-
fix 4658342 = enabled
-
fix 4881533 = enabled
-
fix 4676955 = enabled
-
fix 4273361 = enabled
-
fix 4967068 = enabled
-
fix 4969880 = disabled
-
fix 5005866 = enabled
-
fix 5015557 = enabled
-
fix 4705343 = enabled
-
fix 4904838 = enabled
-
fix 4716096 = enabled
-
fix 4483286 = disabled
-
fix 4722900 = enabled
-
fix 4615392 = enabled
-
fix 5096560 = enabled
-
fix 5029464 = enabled
-
fix 4134994 = enabled
-
fix 4904890 = enabled
-
fix 5104624 = enabled
-
fix 5014836 = enabled
-
fix 4768040 = enabled
-
fix 4600710 = enabled
-
fix 5129233 = enabled
-
fix 4595987 = enabled
-
fix 4908162 = enabled
-
fix 5139520 = enabled
-
fix 5084239 = enabled
-
fix 5143477 = disabled
-
fix 2663857 = enabled
-
fix 4717546 = enabled
-
fix 5240264 = disabled
-
fix 5099909 = enabled
-
fix 5240607 = enabled
-
fix 5195882 = enabled
-
fix 5220356 = enabled
-
fix 5263572 = enabled
-
fix 5385629 = enabled
-
fix 5302124 = enabled
-
fix 5391942 = enabled
-
fix 5384335 = enabled
-
fix 5482831 = enabled
-
fix 4158812 = enabled
-
fix 5387148 = enabled
-
fix 5383891 = enabled
-
fix 5466973 = enabled
-
fix 5396162 = enabled
-
fix 5394888 = enabled
-
fix 5395291 = enabled
-
fix 5236908 = enabled
-
fix 5509293 = enabled
-
fix 5449488 = enabled
-
fix 5567933 = enabled
-
fix 5570494 = enabled
-
fix 5288623 = enabled
-
fix 5505995 = enabled
-
fix 5505157 = enabled
-
fix 5112460 = enabled
-
fix 5554865 = enabled
-
fix 5112260 = enabled
-
fix 5112352 = enabled
-
fix 5547058 = enabled
-
fix 5618040 = enabled
-
fix 5585313 = enabled
-
fix 5547895 = enabled
-
fix 5634346 = enabled
-
fix 5620485 = enabled
-
fix 5483301 = enabled
-
fix 5657044 = enabled
-
fix 5694984 = enabled
-
fix 5868490 = enabled
-
fix 5650477 = enabled
-
fix 5611962 = enabled
-
fix 4279274 = enabled
-
fix 5741121 = enabled
-
fix 5714944 = enabled
-
fix 5391505 = enabled
-
fix 5762598 = enabled
-
fix 5578791 = enabled
-
fix 5259048 = enabled
-
fix 5882954 = enabled
-
fix 2492766 = enabled
-
fix 5707608 = enabled
-
fix 5891471 = enabled
-
fix 5884780 = enabled
-
fix 5680702 = enabled
-
fix 5371452 = enabled
-
fix 5838613 = enabled
-
fix 5949981 = enabled
-
fix 5624216 = enabled
-
fix 5741044 = enabled
-
fix 5976822 = enabled
-
fix 6006457 = enabled
-
fix 5872956 = enabled
-
fix 5923644 = enabled
-
fix 5943234 = enabled
-
fix 5844495 = enabled
-
fix 4168080 = enabled
-
fix 6020579 = enabled
-
fix 5842686 = disabled
-
fix 5996801 = enabled
-
fix 5593639 = enabled
-
fix 6133948 = enabled
-
fix 3151991 = enabled
-
fix 6146906 = enabled
-
fix 6239909 = enabled
-
fix 6267621 = enabled
-
fix 5909305 = enabled
-
fix 6279918 = enabled
-
fix 6141818 = enabled
-
fix 6151963 = enabled
-
fix 6251917 = enabled
-
fix 6282093 = enabled
-
fix 6119510 = enabled
-
fix 6119382 = enabled
-
fix 3801750 = enabled
-
fix 5705630 = disabled
-
fix 5944076 = enabled
-
fix 5406763 = enabled
-
fix 6070954 = enabled
-
fix 6282944 = enabled
-
fix 6138746 = enabled
-
fix 6082745 = enabled
-
fix 3426050 = enabled
-
fix 599680 = enabled
-
fix 6062266 = enabled
-
fix 6087237 = enabled
-
fix 6122894 = enabled
-
fix 6377505 = enabled
-
fix 5893768 = enabled
-
fix 6163564 = enabled
-
fix 6073325 = enabled
-
fix 6188881 = enabled
-
fix 6007259 = enabled
-
fix 6239971 = enabled
-
fix 5284200 = disabled
-
fix 6042205 = enabled
-
fix 6051211 = enabled
-
fix 6434668 = enabled
-
fix 6438752 = enabled
-
fix 5936366 = enabled
-
fix 6439032 = enabled
-
fix 6438892 = enabled
-
fix 6006300 = enabled
-
fix 5947231 = enabled
-
fix 5416118 = 1
-
fix 6365442 = 1
-
fix 6239039 = enabled
-
fix 6502845 = enabled
-
fix 6913094 = enabled
-
fix 6029469 = enabled
-
fix 5919513 = enabled
-
fix 6057611 = enabled
-
fix 6469667 = enabled
-
fix 6608941 = disabled
-
fix 6368066 = enabled
-
fix 6329318 = enabled
-
fix 6656356 = enabled
-
fix 4507997 = enabled
-
fix 6671155 = enabled
-
fix 6694548 = enabled
-
fix 6688200 = enabled
-
fix 6612471 = enabled
-
fix 6708183 = disabled
-
fix 6326934 = enabled
-
fix 6520717 = disabled
-
fix 6714199 = enabled
-
fix 6681545 = enabled
-
fix 6748058 = enabled
-
fix 6167716 = enabled
-
fix 6674254 = enabled
-
fix 6468287 = enabled
-
fix 6503543 = enabled
-
fix 6808773 = disabled
-
fix 6766962 = enabled
-
fix 6120483 = enabled
-
fix 6670551 = enabled
-
fix 6771838 = enabled
-
fix 6626018 = disabled
-
fix 6530596 = enabled
-
fix 6778642 = enabled
-
fix 6699059 = enabled
-
fix 6376551 = enabled
-
fix 6429113 = enabled
-
fix 6782437 = enabled
-
fix 6776808 = enabled
-
fix 6765823 = enabled
-
fix 6768660 = enabled
-
fix 6782665 = enabled
-
fix 6610822 = enabled
-
fix 6514189 = enabled
-
fix 6818410 = enabled
-
fix 6827696 = enabled
-
fix 6773613 = enabled
-
fix 5902962 = enabled
-
fix 6956212 = enabled
-
fix 3056297 = enabled
-
fix 6440977 = disabled
-
fix 6972291 = disabled
-
fix 6904146 = enabled
-
fix 8580883 = enabled
-
fix 5892599 = disabled
-
fix 8609714 = enabled
-
fix 8514561 = enabled
- fix 8619631 = disabled
點選(此處)摺疊或開啟
-
***************************************
-
PARAMETERS IN OPT_PARAM HINT
-
****************************
-
***************************************
-
Column Usage Monitoring is ON: tracking level = 1
-
***************************************
-
-
Considering Query Transformations on query block SEL$1 (#0)
-
**************************
-
Query transformations (QT)
-
**************************
-
JF: Checking validity of join factorization for query block SEL$1 (#0) ------發現沒什麼可以做的就過去了。
-
JF: Bypassed: not a UNION or UNION-ALL query block. 沒有union all的東西所以跳過
-
ST: not valid since star transformation parameter is FALSE -----這個也沒什麼必要做
-
TE: Checking validity of table expansion for query block SEL$1 (#0)
-
TE: Bypassed: No partitioned table in query block. ----根本就沒有paratition 表所以也沒做。
-
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries. 不是複雜view 所以也沒做
-
CBQT: Validity checks failed for 4usswnngj0jdx.
-
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
-
*************************
-
Common Subexpression elimination (CSE)
-
*************************
-
CSE: CSE not performed on query block SEL$1 (#0).
-
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
-
***************************
-
Order-by elimination (OBYE)
-
***************************
-
OBYE: OBYE bypassed: no order by to eliminate.
-
JE: Considering Join Elimination on query block SEL$1 (#0)
-
*************************
-
Join Elimination (JE) ----這一步看看能不能把一些join消除掉,也沒有能消除的join
-
*************************
-
SQL:******* UNPARSED QUERY IS *******
-
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"
-
JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
-
JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
-
JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
-
SQL:******* UNPARSED QUERY IS *******
-
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"
-
Query block SEL$1 (#0) unchanged
-
CVM: Considering view merge in query block SEL$1 (#0)
-
JE: Considering Join Elimination on query block SEL$1 (#0)
-
*************************
-
Join Elimination (JE)
-
*************************
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
-
JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
-
JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
Query block SEL$1 (#0) unchanged
-
query block SEL$1 (#0) unchanged
-
Considering Query Transformations on query block SEL$1 (#0)
-
**************************
-
Query transformations (QT)
-
**************************
-
JF: Checking validity of join factorization for query block SEL$1 (#0)
-
JF: Bypassed: not a UNION or UNION-ALL query block.
-
ST: not valid since star transformation parameter is FALSE
-
TE: Checking validity of table expansion for query block SEL$1 (#0)
-
TE: Bypassed: No partitioned table in query block.
-
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
-
CBQT: Validity checks failed for 4usswnngj0jdx.
-
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
-
*************************
-
Common Subexpression elimination (CSE)
-
*************************
-
CSE: CSE not performed on query block SEL$1 (#0).
-
SU: Considering subquery unnesting in query block SEL$1 (#0)
-
********************
-
Subquery Unnest (SU)
-
********************
-
SJC: Considering set-join conversion in query block SEL$1 (#0)
-
*************************
-
Set-Join Conversion (SJC)
-
*************************
-
SJC: not performed
-
JE: Considering Join Elimination on query block SEL$1 (#0)
-
*************************
-
Join Elimination (JE)
-
*************************
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
-
JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
-
JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
Query block SEL$1 (#0) unchanged
-
PM: Considering predicate move-around in query block SEL$1 (#0)
-
**************************
-
Predicate Move-Around (PM)
-
**************************
-
PM: PM bypassed: Outer query contains no views.
-
PM: PM bypassed: Outer query contains no views.
-
query block SEL$1 (#0) unchanged
-
FPD: Considering simple filter push in query block SEL$1 (#0) --------------FPD這裡做了一些事情,產生了一個可以傳遞的謂詞
-
E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
-
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
-
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
-
-
FPD: transitive predicates are generated in query block SEL$1 (#0)
-
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
- apadrv-start sqlid=5576332259131147709
-
:
-
call(in-use=2208, alloc=16360), compile(in-use=61336, alloc=63432), execution(in-use=2088, alloc=4060)
-
-
*******************************************
-
Peeked values of the binds in SQL statement
- ******************************************* ------------最後把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時候,就過濾很多了,減少匹配
-
Final query after transformations:******* UNPARSED QUERY IS *******
-
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
-
kkoqbc: optimizing query block SEL$1 (#0)
-
-
:
-
call(in-use=2436, alloc=16360), compile(in-use=62156, alloc=63432), execution(in-use=2088, alloc=4060)
-
kkoqbc-subheap (create addr=0x705628)
-
****************
-
QUERY BLOCK TEXT
-
****************
-
select /*sample 1*/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
-
---------------------
-
QUERY BLOCK SIGNATURE
-
---------------------
-
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
-
fro(0): flg=0 objn=73181 hint_alias=E@SEL$1
-
fro(1): flg=0 objn=73181 hint_alias=M@SEL$1
-
-
-----------------------------
-
SYSTEM STATISTICS INFORMATION
-
-----------------------------
-
Using NOWORKLOAD Stats
-
CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
-
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
-
IOSEEKTIM: 10 milliseconds (default is 10)
-
MBRC: -1 blocks (default is 8)
-
-
***************************************
-
BASE STATISTICAL INFORMATION -----做cost,把表的統計資訊拿出來,考慮各種路徑
-
***********************
-
Table Stats::
-
Table: EMP Alias: M
-
#Rows: 14 #Blks: 5 AvgRowLen: 41.00
-
Index Stats::
-
Index: PK_EMP Col#: 1
-
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
-
***********************
-
Table Stats::
-
Table: EMP Alias: E
-
#Rows: 14 #Blks: 5 AvgRowLen: 41.00
-
Index Stats::
-
Index: PK_EMP Col#: 1
-
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
-
Access path analysis for EMP
-
***************************************
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for EMP[E] --------訪問單表的時候,我怎麼訪問是最快的。
-
-
Table: EMP Alias: E
-
Card: Original: 14.000000 Rounded: 4 Computed: 3.74 Non Adjusted: 3.74
-
Access Path: TableScan -------因為沒有索引嗎,他考慮的唯一方式就是全表掃描
-
Cost: 3.00 Resp: 3.00 Degree: 0
-
Cost_io: 3.00 Cost_cpu: 40677
-
Resp_io: 3.00 Resp_cpu: 40677
-
Best:: AccessPath: TableScan
-
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 3.74 Bytes: 0
-
-
Access path analysis for EMP
-
***************************************
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for EMP[M]
-
-
Table: EMP Alias: M
-
Card: Original: 14.000000 Rounded: 4 Computed: 4.03 Non Adjusted: 4.03
-
Access Path: TableScan
-
Cost: 3.00 Resp: 3.00 Degree: 0
-
Cost_io: 3.00 Cost_cpu: 40601
-
Resp_io: 3.00 Resp_cpu: 40601
-
Best:: AccessPath: TableScan
-
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.03 Bytes: 0
-
-
***************************************
-
-
-
OPTIMIZER STATISTICS AND COMPUTATIONS
-
***************************************
-
GENERAL PLANS
-
***************************************
-
Considering cardinality-based initial join order. -----然後考慮怎麼join,怎麼join最好呢
-
Permutations for Starting Table :0
-
Join order[1]: EMP[E]#0 EMP[M]#1
-
-
***************
-
Now joining: EMP[M]#1
-
***************
-
NL Join ---------考慮nest loop花銷,這種join會考慮用哪個表做驅動表,因為他需要把小表作為驅動表的。
-
Outer table: Card: 3.74 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
-
Access path analysis for EMP
-
Inner table: EMP Alias: M ---inner table 是M這張表
-
Access Path: TableScan
-
NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
-
Cost_io: 10.00 Cost_cpu: 203079
-
Resp_io: 10.00 Resp_cpu: 203079
-
Access Path: index (UniqueScan)
-
Index: PK_EMP
-
resc_io: 1.00 resc_cpu: 8528
-
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
-
NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
-
Cost_io: 7.00 Cost_cpu: 74790
-
Resp_io: 7.00 Resp_cpu: 74790
-
Access Path: index (AllEqUnique)
-
Index: PK_EMP
-
resc_io: 1.00 resc_cpu: 8528
-
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
-
NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
-
Cost_io: 7.00 Cost_cpu: 74790
-
Resp_io: 7.00 Resp_cpu: 74790
-
-
Best NL cost: 7.00
-
resc: 7.00 resc_io: 7.00 resc_cpu: 74790
-
resp: 7.00 resp_io: 7.00 resc_cpu: 74790
-
Join Card: 1.144145 = = outer (3.744856) * inner (4.032922) * sel (0.075758)
-
Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 3.744856, inner: 4.032922, sel: 0.075758
-
Join Card - Rounded: 1 Computed: 1.14
-
Outer table: EMP Alias: E
-
resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
-
Inner table: EMP Alias: M
-
resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 32 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 23 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
- SM Join -----------sort merge join
-
SM cost: 8.00
-
resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
-
resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
-
Outer table: EMP Alias: E
-
resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
-
Inner table: EMP Alias: M
-
resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
Cost per ptn: 0.50 #ptns: 1
-
hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
-
Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
-
HA Join ------------hash join最好cost為6.50
-
HA cost: 6.50
-
resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
-
resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
-
Best:: JoinMethod: Hash
-
Cost: 6.50 Degree: 1 Resp: 6.50 Card: 1.14 Bytes: 31
-
***********************
-
Best so far: Table#: 0 cost: 3.0013 card: 3.7449 bytes: 80
-
Table#: 1 cost: 6.5025 card: 1.1441 bytes: 31
-
***********************
-
Join order[2]: EMP[M]#1 EMP[E]#0
-
-
***************
-
Now joining: EMP[E]#0
-
***************
-
-
NL Join
-
Outer table: Card: 4.03 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 11
-
Access path analysis for EMP
-
Inner table: EMP Alias: E
-
Access Path: TableScan
-
NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
-
Cost_io: 10.00 Cost_cpu: 203325
-
Resp_io: 10.00 Resp_cpu: 203325
-
-
Best NL cost: 10.01
-
resc: 10.01 resc_io: 10.00 resc_cpu: 203325
-
resp: 10.01 resp_io: 10.00 resc_cpu: 203325
-
Join Card: 1.144145 = = outer (4.032922) * inner (3.744856) * sel (0.075758)
-
Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 4.032922, inner: 3.744856, sel: 0.075758
-
Join Card - Rounded: 1 Computed: 1.14
-
Outer table: EMP Alias: M
-
resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
-
Inner table: EMP Alias: E
-
resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 23 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 32 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
-
SM Join
-
SM cost: 8.00
-
resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
-
resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
-
Outer table: EMP Alias: M
-
resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
-
Inner table: EMP Alias: E
-
resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
Cost per ptn: 0.50 #ptns: 1
-
hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
-
Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
-
HA Join
-
HA cost: 6.50
-
resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
-
resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
-
Join order aborted: cost > best plan cost
-
***********************
-
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
-
-
*********************************
-
Number of join permutations tried: 2
-
*********************************
-
Consider using bloom filter between E[EMP] and M[EMP]
-
kkoBloomFilter: join (lcdn:4 rcdn:4 jcdn:1 limit:8)
-
Computing bloom ndv for creator:E[EMP] ccdn:3.7 and user:M[EMP] ucdn:4.0
-
kkopqComputeBloomNdv: predicate (bndv:6 ndv:6) and (bndv:14 ndv:5)
-
kkopqComputeBloomNdv: predicate (bndv:3 ndv:3) and (bndv:3 ndv:3)
-
kkopqComputeBloomNdv: predicate (bndv:12 ndv:3) and (bndv:12 ndv:4)
-
kkopqComputeBloomNdv: pred cnt:6 ndv:4 reduction:1
-
kkoBloomFilter: join ndv:0 reduction:0.928571 (limit:0.500000) rejected because distinct value ratio
-
(newjo-save) [1 0 ]
-
Trying or-Expansion on query block SEL$1 (#0)
-
Transfer Optimizer annotations for query block SEL$1 (#0)
-
id=0 frofand predicate=E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500
-
id=0 frofkksm[i] (sort-merge/hash) predicate=E.MGR=M.EMPNO
-
id=0 frofkksm[i] (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO
-
id=0 frosand (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO AND E.MGR=M.EMPNO
-
id=0 frofand predicate=M.DEPTNO=10 AND M.SAL>1500
-
id=0 frojand (sort-merge/hash) predicate=M.SAL>E.SAL
-
Final cost for query block SEL$1 (#0) - All Rows Plan: ------最後給你的統計
-
Best join order: 1
-
Cost: 6.5025 Degree: 1 Card: 1.0000 Bytes: 31
-
Resc: 6.5025 Resc_io: 6.0000 Resc_cpu: 16258612
-
Resp: 6.5025 Resp_io: 6.0000 Resc_cpu: 16258612
-
kkoqbc-subheap (delete addr=0x705628, in-use=24852, alloc=25480)
-
kkoqbc-end:
-
:
-
call(in-use=13332, alloc=49168), compile(in-use=63592, alloc=67556), execution(in-use=2088, alloc=4060)
-
-
kkoqbc: finish optimizing query block SEL$1 (#0)
-
apadrv-end
-
:
-
call(in-use=13332, alloc=49168), compile(in-use=64228, alloc=67556), execution(in-use=2088, alloc=4060)
-
-
-
Starting SQL statement dump
-
-
user_id=84 user_name=SCOTT module=SQL*Plus action=
-
sql_id=4usswnngj0jdx plan_hash_value=-656709420 problem_type=3
-
----- Current SQL Statement for this session (sql_id=4usswnngj0jdx) -----
-
select /*sample 1*/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
-
sql_text_length=138
-
sql=select /*sample 1*/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
-
----- Explain Plan Dump -----
-
----- Plan Table ----- -------給你執行計劃
-
-
============
-
Plan Table
-
============
-
--------------------------------------+-----------------------------------+
-
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-
--------------------------------------+-----------------------------------+
-
| 0 | SELECT STATEMENT | | | | 7 | |
-
| 1 | HASH JOIN | | 1 | 31 | 7 | 00:00:01 |
-
| 2 | TABLE ACCESS FULL | EMP | 4 | 80 | 3 | 00:00:01 |
-
| 3 | TABLE ACCESS FULL | EMP | 4 | 44 | 3 | 00:00:01 |
-
--------------------------------------+-----------------------------------+
-
Predicate Information:
-
----------------------
-
1 - access(E.MGR=M.EMPNO AND E.DEPTNO=M.DEPTNO)
-
1 - filter(M.SAL>E.SAL)
-
2 - filter((E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500))
-
3 - filter((M.DEPTNO=10 AND M.SAL>1500))
-
-
Content of other_xml column
-
===========================
-
db_version : 11.2.0.1
-
parse_schema : SCOTT
-
plan_hash : 3638257876
-
plan_hash_2 : 4140062955
-
Outline Data: 這是outline data,一般情況可以用outline data 固定執行計劃
-
/*+
-
BEGIN_OUTLINE_DATA
-
IGNORE_OPTIM_EMBEDDED_HINTS
-
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
-
DB_VERSION('11.2.0.1')
-
ALL_ROWS
-
OUTLINE_LEAF(@"SEL$1")
-
FULL(@"SEL$1" "E"@"SEL$1") ---full table scan
-
FULL(@"SEL$1" "M"@"SEL$1") -----這裡就是這個query block別名M的full table scan
-
LEADING(@"SEL$1" "E"@"SEL$1" "M"@"SEL$1") -----這個是哪個放在前面,哪個放在後面。
-
USE_HASH(@"SEL$1" "M"@"SEL$1")
-
END_OUTLINE_DATA
-
*/
後面的是重新提了一下
-
-
*** 2014-11-01 01:51:05.586
-
Registered qb: SEL$1 0x708b6c (PARSER)
-
---------------------
-
QUERY BLOCK SIGNATURE
-
---------------------
-
signature (): qb_name=SEL$1 nbfros=2 flg=0
-
fro(0): flg=4 objn=73181 hint_alias="E"@"SEL$1"
-
fro(1): flg=4 objn=73181 hint_alias="M"@"SEL$1"
-
-
SPM: statement not found in SMB
-
-
**************************
-
Automatic degree of parallelism (ADOP)
-
**************************
-
Automatic degree of parallelism is disabled: Parameter.
-
-
PM: Considering predicate move-around in query block SEL$1 (#0)
-
**************************
-
Predicate Move-Around (PM)
-
**************************
-
OPTIMIZER INFORMATION
-
-
******************************************
-
----- Current SQL Statement for this session (sql_id=g126tztfnzvrd) -----
-
select /*sample 11*/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
-
*******************************************
-
Legend
-
The following abbreviations are used by optimizer trace.
-
CBQT - cost-based query transformation
-
JPPD - join predicate push-down
-
OJPPD - old-style (non-cost-based) JPPD
-
FPD - filter push-down
-
PM - predicate move-around
-
CVM - complex view merging
-
SPJ - select-project-join
-
SJC - set join conversion
-
SU - subquery unnesting
-
OBYE - order by elimination
-
OST - old style star transformation
-
ST - new (cbqt) star transformation
-
CNT - count(col) to count(*) transformation
-
JE - Join Elimination
-
JF - join factorization
-
SLP - select list pruning
-
DP - distinct placement
-
qb - query block
-
LB - leaf blocks
-
DK - distinct keys
-
LB/K - average number of leaf blocks per key
-
DB/K - average number of data blocks per key
-
CLUF - clustering factor
-
NDV - number of distinct values
-
Resp - response cost
-
Card - cardinality
-
Resc - resource cost
-
NL - nested loops (join)
-
SM - sort merge (join)
-
HA - hash (join)
-
CPUSPEED - CPU Speed
-
IOTFRSPEED - I/O transfer speed
-
IOSEEKTIM - I/O seek time
-
SREADTIM - average single block read time
-
MREADTIM - average multiblock read time
-
MBRC - average multiblock read count
-
MAXTHR - maximum I/O system throughput
-
SLAVETHR - average slave I/O throughput
-
dmeth - distribution method
-
1: no partitioning required
-
2: value partitioned
-
4: right is random (round-robin)
-
128: left is random (round-robin)
-
8: broadcast right and partition left
-
16: broadcast left and partition right
-
32: partition left using partitioning of right
-
64: partition right using partitioning of left
-
256: run the join in serial
-
0: invalid distribution method
-
sel - selectivity
-
ptn - partition
-
***************************************
-
PARAMETERS USED BY THE OPTIMIZER
-
********************************
-
*************************************
-
PARAMETERS WITH ALTERED VALUES
-
******************************
-
Compilation Environment Dump
-
parallel_max_degree = 4
-
-
-
***************************************
-
PARAMETERS IN OPT_PARAM HINT
-
****************************
-
***************************************
-
Column Usage Monitoring is ON: tracking level = 1
-
***************************************
-
-
Considering Query Transformations on query block SEL$1 (#0)
-
**************************
-
Query transformations (QT)
-
**************************
-
JF: Checking validity of join factorization for query block SEL$1 (#0)
-
JF: Bypassed: not a UNION or UNION-ALL query block.
-
ST: not valid since star transformation parameter is FALSE
-
TE: Checking validity of table expansion for query block SEL$1 (#0)
-
TE: Bypassed: No partitioned table in query block.
-
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
-
CBQT: Validity checks failed for g126tztfnzvrd.
-
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
-
*************************
-
Common Subexpression elimination (CSE)
-
*************************
-
CSE: CSE not performed on query block SEL$1 (#0).
-
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
-
***************************
-
Order-by elimination (OBYE)
-
***************************
-
OBYE: OBYE bypassed: no order by to eliminate.
-
JE: Considering Join Elimination on query block SEL$1 (#0)
-
*************************
-
Join Elimination (JE)
-
*************************
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
-
JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
-
JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
Query block SEL$1 (#0) unchanged
-
CVM: Considering view merge in query block SEL$1 (#0)
-
JE: Considering Join Elimination on query block SEL$1 (#0)
-
*************************
-
Join Elimination (JE)
-
*************************
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
-
JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
-
JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
Query block SEL$1 (#0) unchanged
-
query block SEL$1 (#0) unchanged
-
Considering Query Transformations on query block SEL$1 (#0)
-
**************************
-
Query transformations (QT)
-
**************************
-
JF: Checking validity of join factorization for query block SEL$1 (#0)
-
JF: Bypassed: not a UNION or UNION-ALL query block.
-
ST: not valid since star transformation parameter is FALSE
-
TE: Checking validity of table expansion for query block SEL$1 (#0)
-
TE: Bypassed: No partitioned table in query block.
-
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
-
CBQT: Validity checks failed for g126tztfnzvrd.
-
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
-
*************************
-
Common Subexpression elimination (CSE)
-
*************************
-
CSE: CSE not performed on query block SEL$1 (#0).
-
SU: Considering subquery unnesting in query block SEL$1 (#0)
-
********************
-
Subquery Unnest (SU)
-
********************
-
SJC: Considering set-join conversion in query block SEL$1 (#0)
-
*************************
-
Set-Join Conversion (SJC)
-
*************************
-
SJC: not performed
-
JE: Considering Join Elimination on query block SEL$1 (#0)
-
*************************
-
Join Elimination (JE)
-
*************************
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
JE: cfro: EMP objn:73181 col#:4 dfro:EMP dcol#:4
-
JE: cfro: EMP objn:73181 col#:1 dfro:EMP dcol#:1
-
JE: cfro: EMP objn:73181 col#:8 dfro:EMP dcol#:8
-
SQL:******* UNPARSED QUERY IS *******
-
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
-
Query block SEL$1 (#0) unchanged
-
PM: Considering predicate move-around in query block SEL$1 (#0)
-
**************************
-
Predicate Move-Around (PM)
-
**************************
-
PM: PM bypassed: Outer query contains no views.
-
PM: PM bypassed: Outer query contains no views.
-
query block SEL$1 (#0) unchanged
-
FPD: Considering simple filter push in query block SEL$1 (#0)
-
E.MGR=M.EMPNO AND M.DEPTNO=10 AND E.DEPTNO=M.DEPTNO AND E.SAL>1500 AND M.SAL>E.SAL
-
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
-
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
-
-
FPD: transitive predicates are generated in query block SEL$1 (#0)
-
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
-
apadrv-start sqlid=17332342852445794029
-
:
-
call(in-use=2208, alloc=16360), compile(in-use=61336, alloc=63432), execution(in-use=2088, alloc=4060)
-
-
*******************************************
-
Peeked values of the binds in SQL statement
-
*******************************************
-
-
Final query after transformations:******* UNPARSED QUERY IS *******
-
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
-
kkoqbc: optimizing query block SEL$1 (#0)
-
-
:
-
call(in-use=2436, alloc=16360), compile(in-use=62156, alloc=63432), execution(in-use=2088, alloc=4060)
-
-
kkoqbc-subheap (create addr=0x705628)
-
****************
-
QUERY BLOCK TEXT
-
****************
-
select /*sample 11*/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
-
---------------------
-
QUERY BLOCK SIGNATURE
-
---------------------
-
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
-
fro(0): flg=0 objn=73181 hint_alias=E@SEL$1
-
fro(1): flg=0 objn=73181 hint_alias=M@SEL$1
-
-
-----------------------------
-
SYSTEM STATISTICS INFORMATION
-
-----------------------------
-
Using NOWORKLOAD Stats
-
CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
-
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
-
IOSEEKTIM: 10 milliseconds (default is 10)
-
MBRC: -1 blocks (default is 8)
-
-
***************************************
-
BASE STATISTICAL INFORMATION
-
***********************
-
Table Stats::
-
Table: EMP Alias: M
-
#Rows: 14 #Blks: 5 AvgRowLen: 41.00
-
Index Stats::
-
Index: PK_EMP Col#: 1
-
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
-
***********************
-
Table Stats::
-
Table: EMP Alias: E
-
#Rows: 14 #Blks: 5 AvgRowLen: 41.00
-
Index Stats::
-
Index: PK_EMP Col#: 1
-
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
-
Access path analysis for EMP
-
***************************************
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for EMP[E]
-
-
Table: EMP Alias: E
-
Card: Original: 14.000000 Rounded: 4 Computed: 3.74 Non Adjusted: 3.74
-
Access Path: TableScan
-
Cost: 3.00 Resp: 3.00 Degree: 0
-
Cost_io: 3.00 Cost_cpu: 40677
-
Resp_io: 3.00 Resp_cpu: 40677
-
Best:: AccessPath: TableScan
-
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 3.74 Bytes: 0
-
-
Access path analysis for EMP
-
***************************************
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for EMP[M]
-
-
Table: EMP Alias: M
-
Card: Original: 14.000000 Rounded: 4 Computed: 4.03 Non Adjusted: 4.03
-
Access Path: TableScan
-
Cost: 3.00 Resp: 3.00 Degree: 0
-
Cost_io: 3.00 Cost_cpu: 40601
-
Resp_io: 3.00 Resp_cpu: 40601
-
Best:: AccessPath: TableScan
-
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.03 Bytes: 0
-
-
***************************************
-
-
-
OPTIMIZER STATISTICS AND COMPUTATIONS
-
***************************************
-
GENERAL PLANS
-
***************************************
-
Considering cardinality-based initial join order.
-
Permutations for Starting Table :0
-
Join order[1]: EMP[E]#0 EMP[M]#1
-
-
***************
-
Now joining: EMP[M]#1
-
***************
-
NL Join
-
Outer table: Card: 3.74 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
-
Access path analysis for EMP
-
Inner table: EMP Alias: M
-
Access Path: TableScan
-
NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
-
Cost_io: 10.00 Cost_cpu: 203079
-
Resp_io: 10.00 Resp_cpu: 203079
-
Access Path: index (UniqueScan)
-
Index: PK_EMP
-
resc_io: 1.00 resc_cpu: 8528
-
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
-
NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
-
Cost_io: 7.00 Cost_cpu: 74790
-
Resp_io: 7.00 Resp_cpu: 74790
-
Access Path: index (AllEqUnique)
-
Index: PK_EMP
-
resc_io: 1.00 resc_cpu: 8528
-
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
-
NL Join : Cost: 7.00 Resp: 7.00 Degree: 1
-
Cost_io: 7.00 Cost_cpu: 74790
-
Resp_io: 7.00 Resp_cpu: 74790
-
-
Best NL cost: 7.00
-
resc: 7.00 resc_io: 7.00 resc_cpu: 74790
-
resp: 7.00 resp_io: 7.00 resc_cpu: 74790
-
Join Card: 1.144145 = = outer (3.744856) * inner (4.032922) * sel (0.075758)
-
Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 3.744856, inner: 4.032922, sel: 0.075758
-
Join Card - Rounded: 1 Computed: 1.14
-
Outer table: EMP Alias: E
-
resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
-
Inner table: EMP Alias: M
-
resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 32 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 23 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
-
SM Join
-
SM cost: 8.00
-
resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
-
resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
-
Outer table: EMP Alias: E
-
resc: 3.00 card 3.74 bytes: 20 deg: 1 resp: 3.00
-
Inner table: EMP Alias: M
-
resc: 3.00 card: 4.03 bytes: 11 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
Cost per ptn: 0.50 #ptns: 1
-
hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
-
Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
-
HA Join
-
HA cost: 6.50
-
resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
-
resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
-
Best:: JoinMethod: Hash
-
Cost: 6.50 Degree: 1 Resp: 6.50 Card: 1.14 Bytes: 31
-
***********************
-
Best so far: Table#: 0 cost: 3.0013 card: 3.7449 bytes: 80
-
Table#: 1 cost: 6.5025 card: 1.1441 bytes: 31
-
***********************
-
Join order[2]: EMP[M]#1 EMP[E]#0
-
-
***************
-
Now joining: EMP[E]#0
-
***************
-
NL Join
-
Outer table: Card: 4.03 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 11
-
Access path analysis for EMP
-
Inner table: EMP Alias: E
-
Access Path: TableScan
-
NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
-
Cost_io: 10.00 Cost_cpu: 203325
-
Resp_io: 10.00 Resp_cpu: 203325
-
-
Best NL cost: 10.01
-
resc: 10.01 resc_io: 10.00 resc_cpu: 203325
-
resp: 10.01 resp_io: 10.00 resc_cpu: 203325
-
Join Card: 1.144145 = = outer (4.032922) * inner (3.744856) * sel (0.075758)
-
Join cardinality for HJ/SMJ (no post filters): 2.517119, outer: 4.032922, inner: 3.744856, sel: 0.075758
-
Join Card - Rounded: 1 Computed: 1.14
-
Outer table: EMP Alias: M
-
resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
-
Inner table: EMP Alias: E
-
resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 23 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SORT ressource Sort statistics
-
Sort width: 396 Area size: 347136 Max Area size: 69624832
-
Degree: 1
-
Blocks to Sort: 1 Row size: 32 Total Rows: 4
-
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
-
Total IO sort cost: 0 Total CPU sort cost: 32353029
-
Total Temp space used: 0
-
SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
-
SM Join
-
SM cost: 8.00
-
resc: 8.00 resc_io: 6.00 resc_cpu: 64787335
-
resp: 8.00 resp_io: 6.00 resp_cpu: 64787335
-
Outer table: EMP Alias: M
-
resc: 3.00 card 4.03 bytes: 11 deg: 1 resp: 3.00
-
Inner table: EMP Alias: E
-
resc: 3.00 card: 3.74 bytes: 20 deg: 1 resp: 3.00
-
using dmeth: 2 #groups: 1
-
Cost per ptn: 0.50 #ptns: 1
-
hash_area: 124 (max=16999) buildfrag: 1 probefrag: 1 ppasses: 1
-
Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
-
HA Join
-
HA cost: 6.50
-
resc: 6.50 resc_io: 6.00 resc_cpu: 16258612
-
resp: 6.50 resp_io: 6.00 resp_cpu: 16258612
-
Join order aborted: cost > best plan cost
-
***********************
-
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
-
-
*********************************
-
Number of join permutations tried: 2
-
*********************************
-
Consider using bloom filter between E[EMP] and M[EMP]
-
kkoBloomFilter: join (lcdn:4 rcdn:4 jcdn:1 limit:8)
-
Computing bloom ndv for creator:E[EMP] ccdn:3.7 and user:M[EMP] ucdn:4.0
-
kkopqComputeBloomNdv: predicate (bndv:6 ndv:6) and (bndv:14 ndv:5)
-
kkopqComputeBloomNdv: predicate (bndv:3 ndv:3) and (bndv:3 ndv:3)
-
kkopqComputeBloomNdv: predicate (bndv:12 ndv:3) and (bndv:12 ndv:4)
-
kkopqComputeBloomNdv: pred cnt:6 ndv:4 reduction:1
-
kkoBloomFilter: join ndv:0 reduction:0.928571 (limit:0.500000) rejected because distinct value ratio
-
(newjo-save) [1 0 ]
-
Trying or-Expansion on query block SEL$1 (#0)
-
Transfer Optimizer annotations for query block SEL$1 (#0)
-
id=0 frofand predicate=E.DEPTNO=10 AND E.MGR IS NOT NULL AND E.SAL>1500
-
id=0 frofkksm[i] (sort-merge/hash) predicate=E.MGR=M.EMPNO
-
id=0 frofkksm[i] (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO
-
id=0 frosand (sort-merge/hash) predicate=E.DEPTNO=M.DEPTNO AND E.MGR=M.EMPNO
-
id=0 frofand predicate=M.DEPTNO=10 AND M.SAL>1500
-
id=0 frojand (sort-merge/hash) predicate=M.SAL>E.SAL
-
Final cost for query block SEL$1 (#0) - All Rows Plan:
-
Best join order: 1
-
Cost: 6.5025 Degree: 1 Card: 1.0000 Bytes: 31
-
Resc: 6.5025 Resc_io: 6.0000 Resc_cpu: 16258612
-
Resp: 6.5025 Resp_io: 6.0000 Resc_cpu: 16258612
-
kkoqbc-subheap (delete addr=0x705628, in-use=24852, alloc=25480)
-
kkoqbc-end:
-
:
-
call(in-use=13332, alloc=49168), compile(in-use=63592, alloc=67556), execution(in-use=2088, alloc=4060)
-
-
kkoqbc: finish optimizing query block SEL$1 (#0)
-
apadrv-end
-
:
-
call(in-use=13332, alloc=49168), compile(in-use=64228, alloc=67556), execution(in-use=2088, alloc=4060)
-
-
-
Starting SQL statement dump
-
-
user_id=84 user_name=SCOTT module=SQL*Plus action=
-
sql_id=g126tztfnzvrd plan_hash_value=-656709420 problem_type=3
-
----- Current SQL Statement for this session (sql_id=g126tztfnzvrd) -----
-
select /*sample 11*/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
-
sql_text_length=139
-
sql=select /*sample 11*/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
-
----- Explain Plan Dump -----
-
----- Plan Table -----
-
-
============
- 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Affine TransformationORM
- AngularJS之FilterAngularJSFilter
- CSS3 之 transform & transition & animationCSSS3ORM
- flutter系列之:flutter中的變形金剛TransformFlutterORM
- My suggestions on SAP ABAP transformationORM
- spring cloud gateway之filter篇SpringCloudGatewayFilter
- Tomcat Filter之動態注入TomcatFilter
- JavaWeb開發之Filter中的dispatcher標籤JavaWebFilter
- 線段樹 transformation——hdu 4578ORM
- 一個 Chrome XSS Filter Bypass 的分析ChromeFilter
- Elasticsearch中關於transform的一個問題分析ElasticsearchORM
- Census TransformORM
- canvas transform()CanvasORM
- flink:StreamExecutionEnvironment、DataStream和Transformation與StreamOperatorASTORM
- Elasticsearch 之 Filter 與 Query 有啥不同?ElasticsearchFilter
- animation、transition、transform的區別ORM
- Css3中的TransformCSSS3ORM
- transition、transform、animate的區別?ORM
- transform Vs UdfORM
- HBase Filter 過濾器之 ValueFilter 詳解Filter過濾器
- Java安全之Filter許可權繞過JavaFilter
- 數字轉型Digital Transformation簡介GitORM
- Java安全之基於Tomcat的Filter型記憶體馬JavaTomcatFilter記憶體
- [elixir! #0083] Stream.transform 的用法ORM
- 理解Swift高階函式之map, filter, reduceSwift函式Filter
- Spring Cloud Gateway實戰之五:內建filterSpringCloudGatewayFilter
- Transformation HDU - 4578線段樹綜合操作ORM
- Android Transform + ASM 初探AndroidORMASM
- CSS transform-originCSSORM
- 深入理解TransformORM
- CSS transform 屬性CSSORM
- 4、Flutter Widget - Transform;FlutterORM
- Filter+Redis解決專案之間呼叫的冪等性FilterRedis
- 藍芽的基本widgets教程-Transform篇藍芽ORM
- css3有趣的transform形變CSSS3ORM
- Tied Block Convolution:一種共享filter的卷積形態BloCFilter卷積
- canvas transform引數解析CanvasORM
- Games101-2 transformGAMORM
- Laravel Transform 轉換器LaravelORM