oracle 10053診斷事件
本文參考王海《ORACLE 診斷事件及深入解析10053 事件》概念來自於該文件
一、概念
Oracle 為RDBMS 提供了多種的診斷工具，診斷事件(Event)是其中一種常用、好用的
方法，它使DBA 可以方便的轉儲資料庫各種結構及跟蹤特定事件的發生.
1、 通常格式如下：
EVENT=""
2、 Event 分類
診斷事件大體上可以分為四類：
a． 轉儲類事件：它們主要用於轉儲Oracle 的一些結構，例如轉儲一下控制檔案、數
據檔案頭等內容。
b． 捕捉類事件：它們用於捕捉一些Error 事件的發生，例如捕捉一下ORA-04031 發
生時一些Rdbms 資訊，以判斷是Bug 還是其它原因引起的這方面的問題。
c． 改變執行途徑類事件：它們用於改主一些Oracle 內部程式碼的執行途徑，例如設定
10269 將會使Smon 程式不去合併那些Free 的空間。
d． 跟蹤類事件：這們用於獲取一些跟蹤資訊以用於Sql 調優等方面，最典型的便是
10046 了，將會對Sql 進行跟蹤。
3、設定診斷事件
a、init.ora
EVENT="\
10231 trace name context forever, level 10:\
10232 trace name context forever, level 10"
可以設定多個也可以設定一個
b、session/system
Alter session/system set events ‘immediate trace name controlf level 10;
4、DBMS_SYSTEM.SET_EV過程
a． 過和定義如下
DBMS_SYSTEM.SET_EV(
SI Binary_integer,
SE Binary_integer,
EV Binary_integer,
LE Binary_integer,
NM Binary_integer);
SI: 即v$session 中的sid
SE:即v$session 中的serial#
EV:要設定的事件
LE:要設定事件的級別
NM:名稱
b． 舉個例子，以10046 為例
SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'')；
王海講的很細。我主要是想測試一下10053事件，以及該事件的準備工作，就不細談了，前面只是基本的概念。其中需要注意的是：
要實現跟蹤必須滿足兩個條件：sql 語句必須被hardparse 並且必須使用CBO 最佳化器模式
二、準備工作
1、設定最佳化器及啟用關閉該事件
C:\Documents and Settings\zero>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 1月 25 14:34:28 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
連線到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> conn test/1
已連線。
SQL>
SQL> alter session set events '10053 trace name context forever ,level 1'
2 /
會話已更改。
SQL> alter session set optimizer_mode = all_rows;
會話已更改。
SQL> select count(1) from ele_enterprise ee ,ele_budget_subject bs,ele_payoff_kind pk,ele_manage_branch mb;
COUNT(1)
----------
139853952
SQL> alter session set events '10053 trace name context off';
會話已更改。
此時我本地的udump已經生成了一個trc檔案“zero_ora_2128.trc”
2、檔案逐步分析
a、語句
*** 2008-01-25 14:20:21.000
QUERY
select count(1) from ele_enterprise ee ,ele_budget_subject bs,ele_payoff_kind pk,ele_manage_branch mb
=====================
這個是我在該事件啟用的時候的一個sql查詢語句，寫的很爛，肯定會硬解析，我在上一步設定了cbo的最佳化模式，保證了該語句能夠被10053事件記錄
b、最佳化器資訊
記載了所有影響成本計算的引數
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = All_Rows
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 1048576
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 524288
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE
c、基本統計資訊
下一部分是所有表和索引的基本統計資訊
基本統計資訊包括
表:
Trace label dba_tables column
CDN NUM_ROWS 表記錄數
NBLKS BLOCKS 高水位以下的block 數
TABLE_SCAN_CST 全表掃描的I/O 成本
AVG_ROW_LEN AVG_ROW_LEN 平均行長
索引:
Trace label dba_indexes column
Index#, col# 索引號及表列號
LVLS BLEVEL BTREE 索引高度
#LB LEAF_BLOCKS 索引葉塊數
#DK DISTINCT_KEYS 不重複索引關鍵字
LB/K AVG_LEAF_BLOCKS_PER_KEY 葉塊/關鍵字
DB/K AVG_DATA_BLOCKS_PER_KEY 資料塊/關鍵字
CLUF CLUSTERING_FACTOR 索引聚合因子
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: ELE_MANAGE_BRANCH Alias: MB
TOTAL :: CDN: 16 NBLKS: 5 AVG_ROW_LEN: 287
-- Index stats
INDEX NAME: ELE_MANAGE_BRANCH_N1 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 1 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_MANAGE_BRANCH_N2 COL#: 3
TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_MANAGE_BRANCH_PK COL#: 2
TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats Table: ELE_PAYOFF_KIND Alias: PK
TOTAL :: CDN: 6 NBLKS: 5 AVG_ROW_LEN: 293
-- Index stats
INDEX NAME: ELE_PAYOFF_KIND_N1 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 1 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_PAYOFF_KIND_N2 COL#: 3
TOTAL :: LVLS: 0 #LB: 1 #DK: 6 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_PAYOFF_KIND_PK COL#: 2
TOTAL :: LVLS: 0 #LB: 1 #DK: 6 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats Table: ELE_BUDGET_SUBJECT Alias: BS
TOTAL :: CDN: 1451 NBLKS: 58 AVG_ROW_LEN: 312
-- Index stats
INDEX NAME: ELE_BUDGET_SUBJECT_N1 COL#: 1
TOTAL :: LVLS: 1 #LB: 4 #DK: 1 LB/K: 4 DB/K: 58 CLUF: 58
INDEX NAME: ELE_BUDGET_SUBJECT_N2 COL#: 3
TOTAL :: LVLS: 1 #LB: 4 #DK: 1451 LB/K: 1 DB/K: 1 CLUF: 1333
INDEX NAME: ELE_BUDGET_SUBJECT_PK COL#: 2
TOTAL :: LVLS: 1 #LB: 10 #DK: 1451 LB/K: 1 DB/K: 1 CLUF: 1423
***********************
Table stats Table: ELE_ENTERPRISE Alias: EE
TOTAL :: CDN: 832 NBLKS: 43 AVG_ROW_LEN: 358
-- Index stats
INDEX NAME: ELE_ENTERPRISE_N1 COL#: 1
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
INDEX NAME: ELE_ENTERPRISE_N2 COL#: 3
TOTAL :: LVLS: 1 #LB: 3 #DK: 832 LB/K: 1 DB/K: 1 CLUF: 382
INDEX NAME: ELE_ENTERPRISE_PK COL#: 2
TOTAL :: LVLS: 1 #LB: 6 #DK: 832 LB/K: 1 DB/K: 1 CLUF: 814
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_ENTERPRISE ORIG CDN: 832 ROUNDED CDN: 832 CMPTD CDN: 832
Access path: tsc Resc: 6 Resp: 6
Access path: index (iff)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 4 Resp: 4
Access path: index (iff)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 2.00 PATH: 23 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_BUDGET_SUBJECT ORIG CDN: 1451 ROUNDED CDN: 1451 CMPTD CDN: 1451
Access path: tsc Resc: 7 Resp: 7
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 3 Resp: 3
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 2.00 PATH: 23 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_PAYOFF_KIND ORIG CDN: 6 ROUNDED CDN: 6 CMPTD CDN: 6
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 1.00 PATH: 4 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_MANAGE_BRANCH ORIG CDN: 16 ROUNDED CDN: 16 CMPTD CDN: 16
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 1.00 PATH: 4 Degree: 1
d、綜合計劃：
這一部分開始是10053 最大的一部分，在這裡CBO 會評估各種JOIN 方式及順序的成
本。
Join order[1]: ELE_PAYOFF_KIND [PK] ELE_MANAGE_BRANCH [MB] ELE_ENTERPRISE [EE] ELE_BUDGET_SUBJECT [BS]
Now joining: ELE_MANAGE_BRANCH [MB] *******
NL Join
Outer table: cost: 1 cdn: 6 rcz: 0 resp: 1
Inner table: ELE_MANAGE_BRANCH
Access path: tsc Resc: 2
Join: Resc: 13 Resp: 13
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_MANAGE_BRANCH
Access path: iff Resc: 2
Join: Resc: 13 Resp: 13
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_MANAGE_BRANCH
Access path: iff Resc: 2
Join: Resc: 13 Resp: 13
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 7 resp: 7
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 7 resp: 7
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 96 = outer (6) * inner (16) * sel (1.0000e+000) [flag=0]
Best NL cost: 7 resp: 7
Join result: cost: 7 cdn: 96 rcz: 0
Now joining: ELE_ENTERPRISE [EE] *******
NL Join
Outer table: cost: 7 cdn: 96 rcz: 0 resp: 7
Inner table: ELE_ENTERPRISE
Access path: tsc Resc: 6
Join: Resc: 583 Resp: 583
Access path: index (iff)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_ENTERPRISE
Access path: iff Resc: 4
Join: Resc: 391 Resp: 391
Access path: index (iff)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_ENTERPRISE
Access path: iff Resc: 2
Join: Resc: 199 Resp: 199
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 2503 resp: 2503
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 679 resp: 679
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 79872 = outer (96) * inner (832) * sel (1.0000e+000) [flag=0]
Best NL cost: 199 resp: 199
Join result: cost: 199 cdn: 79872 rcz: 0
Now joining: ELE_BUDGET_SUBJECT [BS] *******
NL Join
Outer table: cost: 199 cdn: 79872 rcz: 0 resp: 199
Inner table: ELE_BUDGET_SUBJECT
Access path: tsc Resc: 7
Join: Resc: 559303 Resp: 559303
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_BUDGET_SUBJECT
Access path: iff Resc: 2
Join: Resc: 159943 Resp: 159943
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_BUDGET_SUBJECT
Access path: iff Resc: 3
Join: Resc: 239815 Resp: 239815
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 399559 resp: 399559
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 878791 resp: 878791
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 115894272 = outer (79872) * inner (1451) * sel (1.0000e+000) [flag=0]
Best NL cost: 159943 resp: 159943
Join result: cost: 159943 cdn: 115894272 rcz: 0
Best so far: TABLE#: 0 CST: 1 CDN: 6 BYTES: 0
Best so far: TABLE#: 1 CST: 7 CDN: 96 BYTES: 0
Best so far: TABLE#: 2 CST: 199 CDN: 79872 BYTES: 0
Best so far: TABLE#: 3 CST: 159943 CDN: 115894272 BYTES: 0
第1 行為JOIN 方式
第2 行為驅動表的成本，行數，行大小。這裡的行數為16，平均行長原本為20，但
是因為DEPT 表包含(DEPTNO, DEPT, and LOC)3 列但僅有DEPTNO,DEPT 等2 列需
要被join，所以計算後平均行長為16，所以在這裡也被稱為low row size.
第3 行到16 行透過NL JOIN 的成本計算公式，計算出幾種不同join 方法的成本。
所以在這裡HA JOIN 會被選做最最佳化的執行路徑，SQL 語句將會最終走HA JOIN.
多重JOIN:
如果出現大於兩個表進行JOIN 的情況，那麼會有更多的join 順序被考慮，4 個表join
的話會有24 種join 順序，5 個表的話會有120 個join 順序，n 個表會有n!個join 順
序。由於估算每種join 順序都會耗費cpu，所以oracle 用一個初始化引數
optimizer_max_permutations 來限制最大計算join 順序。
個人理解，在做多表的時候，每個表的組合越來越多，本部分的資訊就更多。
一、概念
Oracle 為RDBMS 提供了多種的診斷工具，診斷事件(Event)是其中一種常用、好用的
方法，它使DBA 可以方便的轉儲資料庫各種結構及跟蹤特定事件的發生.
1、 通常格式如下：
EVENT=""
2、 Event 分類
診斷事件大體上可以分為四類：
a． 轉儲類事件：它們主要用於轉儲Oracle 的一些結構，例如轉儲一下控制檔案、數
據檔案頭等內容。
b． 捕捉類事件：它們用於捕捉一些Error 事件的發生，例如捕捉一下ORA-04031 發
生時一些Rdbms 資訊，以判斷是Bug 還是其它原因引起的這方面的問題。
c． 改變執行途徑類事件：它們用於改主一些Oracle 內部程式碼的執行途徑，例如設定
10269 將會使Smon 程式不去合併那些Free 的空間。
d． 跟蹤類事件：這們用於獲取一些跟蹤資訊以用於Sql 調優等方面，最典型的便是
10046 了，將會對Sql 進行跟蹤。
3、設定診斷事件
a、init.ora
EVENT="\
10231 trace name context forever, level 10:\
10232 trace name context forever, level 10"
可以設定多個也可以設定一個
b、session/system
Alter session/system set events ‘immediate trace name controlf level 10;
4、DBMS_SYSTEM.SET_EV過程
a． 過和定義如下
DBMS_SYSTEM.SET_EV(
SI Binary_integer,
SE Binary_integer,
EV Binary_integer,
LE Binary_integer,
NM Binary_integer);
SI: 即v$session 中的sid
SE:即v$session 中的serial#
EV:要設定的事件
LE:要設定事件的級別
NM:名稱
b． 舉個例子，以10046 為例
SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'')；
王海講的很細。我主要是想測試一下10053事件，以及該事件的準備工作，就不細談了，前面只是基本的概念。其中需要注意的是：
要實現跟蹤必須滿足兩個條件：sql 語句必須被hardparse 並且必須使用CBO 最佳化器模式
二、準備工作
1、設定最佳化器及啟用關閉該事件
C:\Documents and Settings\zero>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 1月 25 14:34:28 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
連線到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> conn test/1
已連線。
SQL>
SQL> alter session set events '10053 trace name context forever ,level 1'
2 /
會話已更改。
SQL> alter session set optimizer_mode = all_rows;
會話已更改。
SQL> select count(1) from ele_enterprise ee ,ele_budget_subject bs,ele_payoff_kind pk,ele_manage_branch mb;
COUNT(1)
----------
139853952
SQL> alter session set events '10053 trace name context off';
會話已更改。
此時我本地的udump已經生成了一個trc檔案“zero_ora_2128.trc”
2、檔案逐步分析
a、語句
*** 2008-01-25 14:20:21.000
QUERY
select count(1) from ele_enterprise ee ,ele_budget_subject bs,ele_payoff_kind pk,ele_manage_branch mb
=====================
這個是我在該事件啟用的時候的一個sql查詢語句，寫的很爛，肯定會硬解析，我在上一步設定了cbo的最佳化模式，保證了該語句能夠被10053事件記錄
b、最佳化器資訊
記載了所有影響成本計算的引數
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = All_Rows
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 1048576
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 524288
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE
c、基本統計資訊
下一部分是所有表和索引的基本統計資訊
基本統計資訊包括
表:
Trace label dba_tables column
CDN NUM_ROWS 表記錄數
NBLKS BLOCKS 高水位以下的block 數
TABLE_SCAN_CST 全表掃描的I/O 成本
AVG_ROW_LEN AVG_ROW_LEN 平均行長
索引:
Trace label dba_indexes column
Index#, col# 索引號及表列號
LVLS BLEVEL BTREE 索引高度
#LB LEAF_BLOCKS 索引葉塊數
#DK DISTINCT_KEYS 不重複索引關鍵字
LB/K AVG_LEAF_BLOCKS_PER_KEY 葉塊/關鍵字
DB/K AVG_DATA_BLOCKS_PER_KEY 資料塊/關鍵字
CLUF CLUSTERING_FACTOR 索引聚合因子
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: ELE_MANAGE_BRANCH Alias: MB
TOTAL :: CDN: 16 NBLKS: 5 AVG_ROW_LEN: 287
-- Index stats
INDEX NAME: ELE_MANAGE_BRANCH_N1 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 1 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_MANAGE_BRANCH_N2 COL#: 3
TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_MANAGE_BRANCH_PK COL#: 2
TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats Table: ELE_PAYOFF_KIND Alias: PK
TOTAL :: CDN: 6 NBLKS: 5 AVG_ROW_LEN: 293
-- Index stats
INDEX NAME: ELE_PAYOFF_KIND_N1 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 1 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_PAYOFF_KIND_N2 COL#: 3
TOTAL :: LVLS: 0 #LB: 1 #DK: 6 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_PAYOFF_KIND_PK COL#: 2
TOTAL :: LVLS: 0 #LB: 1 #DK: 6 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats Table: ELE_BUDGET_SUBJECT Alias: BS
TOTAL :: CDN: 1451 NBLKS: 58 AVG_ROW_LEN: 312
-- Index stats
INDEX NAME: ELE_BUDGET_SUBJECT_N1 COL#: 1
TOTAL :: LVLS: 1 #LB: 4 #DK: 1 LB/K: 4 DB/K: 58 CLUF: 58
INDEX NAME: ELE_BUDGET_SUBJECT_N2 COL#: 3
TOTAL :: LVLS: 1 #LB: 4 #DK: 1451 LB/K: 1 DB/K: 1 CLUF: 1333
INDEX NAME: ELE_BUDGET_SUBJECT_PK COL#: 2
TOTAL :: LVLS: 1 #LB: 10 #DK: 1451 LB/K: 1 DB/K: 1 CLUF: 1423
***********************
Table stats Table: ELE_ENTERPRISE Alias: EE
TOTAL :: CDN: 832 NBLKS: 43 AVG_ROW_LEN: 358
-- Index stats
INDEX NAME: ELE_ENTERPRISE_N1 COL#: 1
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
INDEX NAME: ELE_ENTERPRISE_N2 COL#: 3
TOTAL :: LVLS: 1 #LB: 3 #DK: 832 LB/K: 1 DB/K: 1 CLUF: 382
INDEX NAME: ELE_ENTERPRISE_PK COL#: 2
TOTAL :: LVLS: 1 #LB: 6 #DK: 832 LB/K: 1 DB/K: 1 CLUF: 814
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_ENTERPRISE ORIG CDN: 832 ROUNDED CDN: 832 CMPTD CDN: 832
Access path: tsc Resc: 6 Resp: 6
Access path: index (iff)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 4 Resp: 4
Access path: index (iff)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 2.00 PATH: 23 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_BUDGET_SUBJECT ORIG CDN: 1451 ROUNDED CDN: 1451 CMPTD CDN: 1451
Access path: tsc Resc: 7 Resp: 7
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 3 Resp: 3
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 2.00 PATH: 23 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_PAYOFF_KIND ORIG CDN: 6 ROUNDED CDN: 6 CMPTD CDN: 6
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 1.00 PATH: 4 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_MANAGE_BRANCH ORIG CDN: 16 ROUNDED CDN: 16 CMPTD CDN: 16
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 1.00 PATH: 4 Degree: 1
d、綜合計劃：
這一部分開始是10053 最大的一部分，在這裡CBO 會評估各種JOIN 方式及順序的成
本。
Join order[1]: ELE_PAYOFF_KIND [PK] ELE_MANAGE_BRANCH [MB] ELE_ENTERPRISE [EE] ELE_BUDGET_SUBJECT [BS]
Now joining: ELE_MANAGE_BRANCH [MB] *******
NL Join
Outer table: cost: 1 cdn: 6 rcz: 0 resp: 1
Inner table: ELE_MANAGE_BRANCH
Access path: tsc Resc: 2
Join: Resc: 13 Resp: 13
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_MANAGE_BRANCH
Access path: iff Resc: 2
Join: Resc: 13 Resp: 13
Access path: index (iff)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_MANAGE_BRANCH
Access path: iff Resc: 2
Join: Resc: 13 Resp: 13
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 7 resp: 7
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 7 resp: 7
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 96 = outer (6) * inner (16) * sel (1.0000e+000) [flag=0]
Best NL cost: 7 resp: 7
Join result: cost: 7 cdn: 96 rcz: 0
Now joining: ELE_ENTERPRISE [EE] *******
NL Join
Outer table: cost: 7 cdn: 96 rcz: 0 resp: 7
Inner table: ELE_ENTERPRISE
Access path: tsc Resc: 6
Join: Resc: 583 Resp: 583
Access path: index (iff)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 4
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_ENTERPRISE
Access path: iff Resc: 4
Join: Resc: 391 Resp: 391
Access path: index (iff)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_ENTERPRISE
Access path: iff Resc: 2
Join: Resc: 199 Resp: 199
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 2503 resp: 2503
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 679 resp: 679
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
RSC_CPU: 0 RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 79872 = outer (96) * inner (832) * sel (1.0000e+000) [flag=0]
Best NL cost: 199 resp: 199
Join result: cost: 199 cdn: 79872 rcz: 0
Now joining: ELE_BUDGET_SUBJECT [BS] *******
NL Join
Outer table: cost: 199 cdn: 79872 rcz: 0 resp: 199
Inner table: ELE_BUDGET_SUBJECT
Access path: tsc Resc: 7
Join: Resc: 559303 Resp: 559303
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_BUDGET_SUBJECT
Access path: iff Resc: 2
Join: Resc: 159943 Resp: 159943
Access path: index (iff)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_BUDGET_SUBJECT
Access path: iff Resc: 3
Join: Resc: 239815 Resp: 239815
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 399559 resp: 399559
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join: resc: 878791 resp: 878791
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
RSC_CPU: 0 RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 115894272 = outer (79872) * inner (1451) * sel (1.0000e+000) [flag=0]
Best NL cost: 159943 resp: 159943
Join result: cost: 159943 cdn: 115894272 rcz: 0
Best so far: TABLE#: 0 CST: 1 CDN: 6 BYTES: 0
Best so far: TABLE#: 1 CST: 7 CDN: 96 BYTES: 0
Best so far: TABLE#: 2 CST: 199 CDN: 79872 BYTES: 0
Best so far: TABLE#: 3 CST: 159943 CDN: 115894272 BYTES: 0
第1 行為JOIN 方式
第2 行為驅動表的成本，行數，行大小。這裡的行數為16，平均行長原本為20，但
是因為DEPT 表包含(DEPTNO, DEPT, and LOC)3 列但僅有DEPTNO,DEPT 等2 列需
要被join，所以計算後平均行長為16，所以在這裡也被稱為low row size.
第3 行到16 行透過NL JOIN 的成本計算公式，計算出幾種不同join 方法的成本。
所以在這裡HA JOIN 會被選做最最佳化的執行路徑，SQL 語句將會最終走HA JOIN.
多重JOIN:
如果出現大於兩個表進行JOIN 的情況，那麼會有更多的join 順序被考慮，4 個表join
的話會有24 種join 順序，5 個表的話會有120 個join 順序，n 個表會有n!個join 順
序。由於估算每種join 順序都會耗費cpu，所以oracle 用一個初始化引數
optimizer_max_permutations 來限制最大計算join 順序。
個人理解，在做多表的時候，每個表的組合越來越多，本部分的資訊就更多。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/79499/viewspace-160114/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle診斷事件及深入解析10053事件Oracle事件
- ORACLE診斷事件Oracle事件
- Oracle診斷事件列表Oracle事件
- ORACLE診斷事件(zt)Oracle事件
- Oracle診斷事件列表(轉)Oracle事件
- oracle 事件診斷詳細Oracle事件
- Oracle所有診斷事件列表eventsOracle事件
- Oracle診斷事件例項（一）Oracle事件
- ORACLE診斷事件的總結Oracle事件
- Oracle 10053 事件Oracle事件
- Oracle 10053事件Oracle事件
- zt_oracle診斷事件event列表Oracle事件
- 診斷事件（1）事件
- ORACLE 深入解析10053事件Oracle事件
- 等待事件快速定位診斷事件
- 等待事件效能診斷方法事件
- oracle 10046事件故障診斷一例Oracle事件
- oracle中的10053事件命令Oracle事件
- ORACLE診斷案例Oracle
- Oracle故障診斷Oracle
- oracle小知識點16-診斷事件diagnostic eventsOracle事件
- 【原創】ORACLE 深入解析10053事件Oracle事件
- 10053事件事件
- oracle 12c 新增的診斷事件的初步嘗試Oracle事件
- 【event messages】使用PL/SQL獲取Oracle診斷事件列表SQLOracle事件
- oracle 效能診斷工具Oracle
- 基於等待事件的效能診斷事件
- 【10053 事件】10053事件的跟蹤檔案解析事件
- ORACLEE 10053 事件Oracle事件
- 解析10053事件事件
- oracle診斷工具-RDA使用Oracle
- oracle sqlt(sqltxplain) 診斷工具OracleSQLAI
- Oracle診斷工具RDA使用Oracle
- Oracle效能診斷藝術Oracle
- 基於等待事件的效能診斷(轉)事件
- 如何診斷等待事件 enq: HW - contention事件ENQ
- 轉_診斷latch:shared pool等待事件事件
- latch free 等待事件的診斷語句事件