作者
digoal
日期
2021-09-04
标签
PostgreSQL , Oracle
原文:
http://blog.itpub.net/26736162/viewspace-2136332/
https://www.cnblogs.com/HondaHsu/p/3533411.html
当一个SQL出现性能问题的时候，可以使用SQL_TRACE 或者 10046事件来跟踪SQL. 通过生成的trace来了解SQL的执行过程。 我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。 如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。无法进行分析判断。
而10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。
10053事件生成trace文件目录和SQL_TRACE一样。
在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.
在Oracle 11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下
对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。
10053事件有两个级别：
Level 2：2级是1级的一个子集，它包含以下内容：
- Column statistics
- Single Access Paths
- Join Costs
- Table Joins Considered
- Join Methods Considered (NL/MS/HA)
Level 1：1级比2级更详细，它包含2级的所有内容，在加如下内容：
- Parameters used by the optimizer
- Index statistics
1.1启用10053事件：
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
1.2关闭10053事件：
ALTER SESSION SET EVENTS '10053 trace name context off';
说明：
（1）sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。
（2）10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。
1.设定当前的trace 文件
1.1 设定trace 文件名称
SQL> alter session set tracefile_identifier='10053事件';
会话已更改。
设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。
1.2直接用如下SQL直接查出，当前的trace文件名。
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc'
AS "trace_file_name"
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
2.启动10053事件
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
3.执行事务
SQL> select * from pub_user u, pub_department dept
where u.department_id = dept.department_id;
SQL>Explain plan for select * from pub_user u, pub_department dept
where u.department_id = dept.department_id;
4.关闭10053事件
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
在此之前设置了标识，所以直接进入trace目录，找到含有 ‘10053事件’标识的trace 文件。
Trace file D:\oracle\product\10.2.0\admin\dw\udump/10053事件.trc
1. Predicate Move-Around (PM)(对SQL语句的谓词进行分析、重写，把它改为最符合逻辑的SQL语句)
2. 解释trace文件用到的一些缩写的指标定义
3. Peeked values of the binds in SQL statement(绑定变量的描述)
4. Bug Fix Control Environment(一些修复的bug信息)
5. PARAMETERS WITH DEFAULT VALUES(性能相关的初始化参数)
6. BASE STATISTICAL INFORMATION(SQL引用对象的基本信息)
7. CBO计算每个对象单独访问的代价
8. CBO计算列出两个表关联方式，并计算出每一种关联方式的代价，最终选择最小的cost
在我们写sql时，一条明显可以查询出来数据的语句，为什么我们写完之后却不返回数据？这时，10053可以解答我们的疑问。
1.见如下order by 查不出数据实验：
---10.2.0.1版本加了order by查不出数据实验
Drop table test1 purge;
Drop table test2 purge;
create table test1 (id number(20),name varchar2(20));
insert into test1 values (1,'A');
insert into test1 values (2,'A');
insert into test1 values (3,'A');
insert into test1 values (4,'A');
insert into test1 values (5,'B');
insert into test1 values (6,'B');
insert into test1 values (7,'C');
insert into test1 values (8,'C');
insert into test1 values (9,'C');
insert into test1 values (10,'C');
create table test2 (id number(20),name varchar2(20));
insert into test2 values (1,'A');
insert into test2 values (2,'A');
insert into test2 values (3,'A');
insert into test2 values (4,'A');
insert into test2 values (5,'A');
insert into test2 values (6,'A');
insert into test2 values (7,'A');
insert into test2 values (8,'B');
insert into test2 values (9,'C');
insert into test2 values (10,'C');
2.查询SQL
SELECT *
FROM (SELECT *
FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (select test2.*
from test2,
(SELECT t.id, t.name
FROM test T
WHERE T.id in (SELECT MAX(T1.id)
FROM test T1
group by name)) test1
where test2.name = test1.name
order by test2.name) INNER_TABLE)
WHERE OUTER_TABLE_ROWNUM <= 18) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM > 0;
10053和10046事件的用法是一样的，需要注意的是这个trace文件不能用tkprof工具处理，tkprof工具只能处理10046和sql_trace文件，下面是刚才生成的trace文件内容
这里从BASE STATISTICAL INFORMATION开始看
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T1 Alias: T1 (NOT ANALYZED)
#Rows: 164 #Blks: 2 AvgRowLen: 100.00
***********************
Table Stats::
Table: T Alias: T
#Rows: 73924 #Blks: 113 AvgRowLen: 5.00
Index Stats::
Index: IDX_T Col#: 1
LVLS: 1 #LB: 164 #DK: 73924 LB/K: 1.00 DB/K: 1.00 CLUF: 113.00
Access path analysis for T
***************************************
这一部分是sql语句中用到的对象基本信息，包括关联表和索引信息，我们看到这里列出了关于这个查询的三个对象信息，表T,T1和索引IDX_T，表信息包含了表的行数（#Rows），数据块数（#Blks），平均行长（AvgRowLen）索引项部分列出了索引叶块数（#LB），每个索引键值占据的数据块数（LB/K），每个索引键值对应的表中数据块数（DB/K）索引的聚合因子（CLUF）,当索引的键值对应的数据库越大，索引的聚合因子就越大，越不利用索引的使用。
接下来的部分是CBO计算的每个对象单独访问的代价
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 73924.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
Access Path: TableScan
Cost: 32.49 Resp: 32.49 Degree: 0
Cost_io: 32.00 Cost_cpu: 15589523
Resp_io: 32.00 Resp_cpu: 15589523
Access Path: index (index (FFS))
Index: IDX_T
resc_io: 46.00 resc_cpu: 13734996
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 46.43 Resp: 46.43 Degree: 1
Cost_io: 46.00 Cost_cpu: 13734996
Resp_io: 46.00 Resp_cpu: 13734996
Access Path: index (IndexOnly)
Index: IDX_T
resc_io: 2.00 resc_cpu: 34243
ix_sel: 0.001339 ix_sel_with_filters: 0.001339
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_T
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0
Access path analysis for T1
***************************************
这里有两个指标对于我们分析执行计划比较重要：
Card: Original: 73924.000000
原纪录数，也就是操作数据原的输入记录数，在这里就是表的实际行数73294
Card: Rounded： 99
输出的记录数，CBO计算出通过条件过滤，预计得到的行数99
通过这里我们可以看出对于T表CBO给我们计算出了3种访问方式
全表扫描
Access Path: TableScan 开销：Cost: 32.49
索引快速扫描
Access Path: index (index (FFS)) 开销：Cost: 46.43
单独访问索引
Access Path: index (IndexOnly) 开销：Cost: 2.00
可以看出，单独访问索引的方式是代价最低的，因此CBO得出下来结论
Best:: AccessPath: IndexRange
Index: IDX_T
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0
这里我不明白上面写的是IndexOnly，为什么最后结论改写成IndexRange。为何oracle不直接将Access Path写成IndexRange
下面是对T1表访问方式的描述
Table: T1 Alias: T1
Card: Original: 999.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
Access Path: TableScan
Cost: 2.01 Resp: 2.01 Degree: 0
Cost_io: 2.00 Cost_cpu: 216023
Resp_io: 2.00 Resp_cpu: 216023
Best:: AccessPath: TableScan
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 99.00 Bytes: 0
由于我们没有在T1上创建索引因此对于T1表的访问只有TableScan全表扫描一种方式
下面是T与T1表的关联统计最终分析出关联最小的开销作为最终的执行计划
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T1[T1]#0 T[T]#1
***************
Now joining: T[T]#1
***************
NL Join （一）
Outer table: Card: 99.00 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 17
Access path analysis for T
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 3082.41 Resp: 3082.41 Degree: 1
Cost_io: 3034.00 Cost_cpu: 1543578772
Resp_io: 3034.00 Resp_cpu: 1543578772
Access Path: index (index (FFS))
Index: IDX_T
resc_io: 44.43 resc_cpu: 13734996
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: T Alias: T
Access Path: index (FFS)
NL Join: Cost: 4443.65 Resp: 4443.65 Degree: 1
Cost_io: 4401.00 Cost_cpu: 1359980643
Resp_io: 4401.00 Resp_cpu: 1359980643
kkofmx: index filter:"T"."ID"<100
Access Path: index (AllEqJoinGuess)
Index: IDX_T
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.000014 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
***** End Logdef Adjustment ******
NL Join : Cost: 101.03 Resp: 101.03 Degree: 1
Cost_io: 101.00 Cost_cpu: 1029945
Resp_io: 101.00 Resp_cpu: 1029945
Best NL cost: 101.03 --nested loops join 代价是101.03
resc: 101.03 resc_io: 101.00 resc_cpu: 1029945
resp: 101.03 resp_io: 101.00 resc_cpu: 1029945
Join Card: 98.011326 = = outer (99.000000) * inner (99.001339) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: T1 Alias: T1
resc: 2.01 card 99.00 bytes: 17 deg: 1 resp: 2.01
Inner table: T Alias: T
resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 179 Area size: 157696 Max Area size: 31666176
Degree: 1
Blocks to Sort: 1 Row size: 29 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 31913716
Total Temp space used: 0
SORT ressource Sort statistics
Sort width: 179 Area size: 157696 Max Area size: 31666176
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 31913716
Total Temp space used: 0
SM join: Resc: 6.01 Resp: 6.01 [multiMatchCost=0.00]
SM Join （二）
SM cost: 6.01 --Sort merge join 的代价是6.01
resc: 6.01 resc_io: 4.00 resc_cpu: 64077698
resp: 6.01 resp_io: 4.00 resp_cpu: 64077698
Outer table: T1 Alias: T1
resc: 2.01 card 99.00 bytes: 17 deg: 1 resp: 2.01
Inner table: T Alias: T
resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1
hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 4.51 Resp: 4.51 [multiMatchCost=0.00]
HA Join （三）
HA cost: 4.51 --Hash join的代价是4.51
resc: 4.51 resc_io: 4.00 resc_cpu: 16217089
resp: 4.51 resp_io: 4.00 resp_cpu: 16217089
Best:: JoinMethod: Hash --第一种关联花费最小的是Hash join
Cost: 4.51 Degree: 1 Resp: 4.51 Card: 98.01 Bytes: 22
***********************
Best so far: Table#: 0 cost: 2.0068 card: 99.0000 bytes: 1683
Table#: 1 cost: 4.5086 card: 98.0113 bytes: 2156
***********************
Join order[2]: T[T]#1 T1[T1]#0
***************
Now joining: T1[T1]#0
***************
NL Join （一）
Outer table: Card: 99.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 5
Access path analysis for T1
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 57.67 Resp: 57.67 Degree: 1
Cost_io: 57.00 Cost_cpu: 21420508
Resp_io: 57.00 Resp_cpu: 21420508
Best NL cost: 57.67
resc: 57.67 resc_io: 57.00 resc_cpu: 21420508
resp: 57.67 resp_io: 57.00 resc_cpu: 21420508
Join Card: 98.011326 = = outer (99.001339) * inner (99.000000) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: T Alias: T
resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 2.01 card: 99.00 bytes: 17 deg: 1 resp: 2.01
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 179 Area size: 157696 Max Area size: 31666176
Degree: 1
Blocks to Sort: 1 Row size: 29 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 31913716
Total Temp space used: 0
SM join: Resc: 5.01 Resp: 5.01 [multiMatchCost=0.00]
SM Join （二）
SM cost: 5.01
resc: 5.01 resc_io: 4.00 resc_cpu: 32163982
resp: 5.01 resp_io: 4.00 resp_cpu: 32163982
Outer table: T Alias: T
resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 2.01 card: 99.00 bytes: 17 deg: 1 resp: 2.01
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 4.51 Resp: 4.51 [multiMatchCost=0.00]
HA Join （三）
HA cost: 4.51
resc: 4.51 resc_io: 4.00 resc_cpu: 16217089
resp: 4.51 resp_io: 4.00 resp_cpu: 16217089
Join order aborted: cost > best plan cost
***********************
2中关联方式统计如下：
T1关联T
nested join： 101.03 resc_cpu: 1029945
sort merge join：6.01 resc_cpu: 64077698
Hash join：4.51 resc_cpu: 16217089
T关联T1
nested join：57.67resc_cpu: 21420508
nested join：5.01resc_cpu: 32163982
nested join：4.51resc_cpu: 16217089
下面是最终关联计算开销的结果Best join order: 1，从上面的结果看出2种关联中最优的都是Hash join，碰巧这个两个hash join开销一样，oracle选择第一种关联方式
Number of join permutations tried: 2
*********************************
Consider using bloom filter between T1[T1] and T[T]
kkoBloomFilter: join (lcdn:99 rcdn:99 jcdn:98 limit:4901)
Computing bloom ndv for creator:T1[T1] ccdn:99.0 and user:T[T] ucdn:99.0
kkopqComputeBloomNdv: predicate (bndv:73924 ndv:100) and (bndv:999 ndv:99)
kkopqComputeBloomNdv: pred cnt:2 ndv:99 reduction:1
kkoBloomFilter: join ndv:0 reduction:0.999986 (limit:0.500000) rejected because distinct value ratio
(newjo-save) [0 1 ]
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofand predicate="T1"."ID"<100
id=0 frofkksm[i] (sort-merge/hash) predicate="T"."ID"="T1"."ID"
id=0 frosand (sort-merge/hash) predicate="T"."ID"="T1"."ID"
id=0 frofkke[i] (index stop key) predicate="T"."ID"<100
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 4.5086 Degree: 1 Card: 98.0000 Bytes: 2156
Resc: 4.5086 Resc_io: 4.0000 Resc_cpu: 16217089
Resp: 4.5086 Resp_io: 4.0000 Resc_cpu: 16217089
kkoqbc-subheap (delete addr=0x7ff58e2dfa50, in-use=29224, alloc=41296)
kkoqbc-end:
:
call(in-use=19752, alloc=82024), compile(in-use=67328, alloc=68488), execution(in-use=89616, alloc=93504)
kkoqbc: finish optimizing query block SEL$1 (#0)
下面是最终算出的执行计划结果
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 5 | |
| 1 | HASH JOIN | | 98 | 2156 | 5 | 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 99 | 1683 | 2 | 00:00:01 |
| 3 | INDEX RANGE SCAN | IDX_T | 99 | 495 | 2 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T"."ID"="T1"."ID")
2 - filter("T1"."ID"<100)
3 - access("T"."ID"<100)
注：trace文件头部和尾部省略
trace文件如下,篇幅原因有省略
/u01/app/admin/orcl/udump/orcl_ora_2590.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle
System name: Linux
Node name: rac1
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 2590, image: oracle@rac1 (TNS V1-V3)
trace文件通用，包含了操作系统、数据库和会话的信息，这里不再累述。
*** 2012-04-25 10:53:00.982
*** ACTION NAME:() 2012-04-25 10:53:00.981
*** MODULE NAME:(SQL*Plus) 2012-04-25 10:53:00.981
*** SERVICE NAME:(SYS$USERS) 2012-04-25 10:53:00.981
*** SESSION ID:(159.5) 2012-04-25 10:53:00.981
Registered qb: SEL$1 0x2db12034 (PARSER)
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=4 objn=53393 hint_alias="T"@"SEL$1"
fro(1): flg=4 objn=53395 hint_alias="T1"@"SEL$1"
下面是10053 trace信息
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1 标识10053事件用的时level1级别
***************************************
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
PM: PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in SEL$1 (#0)
FPD: Current where clause predicates in SEL$1 (#0) :
"T"."X"<:B1 AND "T"."X"="T1"."ID" #最初的谓词条件
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "T"."X"<:B1 AND "T"."X"="T1"."ID" AND "T1"."ID"<:B2
after transitive predicate generation: "T"."X"<:B1 AND "T"."X"="T1"."ID" AND "T1"."ID"<:B2
finally: "T"."X"<:B1 AND "T"."X"="T1"."ID" AND "T1"."ID"<:B2 #最终的谓词条件
可以看出，从逻辑上这两个谓词条件是等价的，CBO只所以进行改写，是为了方便计算每一步的成本和估算Cardinality
FPD: Following transitive predicates are generated in SEL$1 (#0) :
"T1"."ID"<:B1
apadrv-start: call(in-use=340, alloc=16360), compile(in-use=34068, alloc=37692)
kkoqbc-start
: call(in-use=344, alloc=16360), compile(in-use=34824, alloc=37692)
******************************************
Current SQL statement for this session:
select t1.* from t1,t where t.x<:c and t.x=t1.id
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
。。。。。省略若干行。。。。。。
128: use hash partitioning dimension
256: use range partitioning dimension
2048: use list partitioning dimension
1024: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
****************
QUERY BLOCK TEXT
****************
select t1.* from t1,t where t.x<:c and t.x=t1.id
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=74723 hint_alias="T"@"SEL$1"
fro(1): flg=0 objn=74725 hint_alias="T1"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
***************************************
Peeked values of the binds in SQL statement
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7b1efb8 bln=22 avl=00 flg=05
BIND ：Variables bound to a cursor，游标号
bind N ：The bind position being bound，绑定游标的位置，从0开始，0是第一个游标
dty ：Data type，数据类型
mxl ：Maximum length of the bind variable (private max len in paren)，绑定变量的最大长度
mal ：Array length，最大数组长度(当用到绑定变量数组或批量操作时)
scl ：Scale，比例
pre ：Precision，精度
oacflg ：Special flag indicating bind options，内部标记，若是奇数，则绑定变量为空值，允许有空值。
oacflg2 ：Continuation of oacflg，内部标记的扩展
size ：Amount of memory to be allocated for this chunk，缓冲区的大小
offset ：Offset into this chunk for this bind buffer，缓冲区的chunk大小
bfp ：Bind address，绑定变量地址
bln ：Bind buffer length，绑定变量缓冲区长度
avl ：Actual value length (array length too)，实际值的长度
flg ：Special flag indicating bind status，内部标记
value ：The actual value of the bind variable，绑定变量的实际值，有可能是16进制转储
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_b_tree_bitmap_plans = false
optimizer_dynamic_sampling = 3
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
性能相关的初始化参数值
。。。。。省略若干行。。。。。。
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select t1.* from t1,t where t.x<100 and t.x=t1.id
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=53393 hint_alias="T"@"SEL$1"
fro(1): flg=0 objn=53395 hint_alias="T1"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats 基于非工作量统计模式
CPUSPEEDNW: 2696 millions instructions/sec (default is 100) 非工作量统计模式下CPU主频
IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IO传输速率（字节/毫秒）
IOSEEKTIM: 10 milliseconds (default is 10) IO寻址时间（毫秒）
MBRC: -1 blocks (default is 8) 一次多块读可以读几个数据块
***************************************
BASE STATISTICAL INFORMATION
这一部分是sql中应用到的对象基本信息，包括表关联和各自索引的信息，这些信息都可以在相关视图中找到，如user_indexes,user_tables等
***********************
Table Stats::
Table: T Alias: T
#Rows: 50701 #Blks: 86 AvgRowLen: 4.00
Column (#1): X(NUMBER)
AvgLen: 5.00 NDV: 50701 Nulls: 0 Density: 1.9723e-05 Min: 6 Max: 50700
Index Stats::
Index: T_IDX Col#: 1
LVLS: 1 #LB: 112 #DK: 50701 LB/K: 1.00 DB/K: 1.00 CLUF: 78.00
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 50701 #Blks: 251 AvgRowLen: 29.00
Column (#1): ID(NUMBER)
AvgLen: 5.00 NDV: 50701 Nulls: 0 Density: 1.9723e-05 Min: 8 Max: 53394
Index Stats::
Index: T1_IDX Col#: 1
LVLS: 1 #LB: 112 #DK: 50701 LB/K: 1.00 DB/K: 1.00 CLUF: 393.00
表信息的部分中包括了表的行数、数据块数、平均行数。对于字段，只列出了谓词条件中包含的字段。对于在谓词中没有出现的字段，因为它不影响执行计划的选择，所以以CBO不需要将他考虑到代价中，我们看到，这里列出的是X字段，因为它既是两表关联的字段，同时自身也是一个谓词条件，X列的信息包括了它的类型、平均长度、非重复的值、空值、密度以及列的最大最小值，这些信息在CBO做执行计划代价的计算上都要作为输入的值。
索引项部分中列出了所以的高度，索引页块数（LB，Leaf Blocks），每个索引占据的数据块数（LB/K Leaf Blocks/Key），每个索引键值对应的表中数据块（DB/K，Data Blocks/Key），索引的聚合因子（CLUF，Clustering Factor）。集合因子CLUF（索引聚合因子），它表示索引中的键值和元表中的数据分布的一种关系，当索引键值和表中数据的排列顺序大致相同时，它意味着键值指向的数据块越多时（数据排序和索引相差越大）时，这个因子就越大，越不利于索引的使用。了解这个指标对于我们分析sql的执行计划很有用处，比如我们发现SQL执行计划异常，可是从cardinality上无法解释，也许应该考虑一下是否是CLUF的影响导致的。关于CLUF可以参加如下文章：
http://czmmiao.iteye.com/blog/1481957
***************************************
SINGLE TABLE ACCESS PATH
*** 2012-04-25 10:53:00.998
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T1 Alias: T1
Card: Original: 50701 Rounded: 87 Computed: 87.37 Non Adjusted: 87.37
原始行数 近似值 精确值 非修正值
Access Path: TableScan
Cost: 58.69 Resp: 58.69 Degree: 0 --Cost:总代价
Cost_io: 57.00 Cost_cpu: 11929421 --Cost:总代价=IO代价 + CPU代价
Resp_io: 57.00 Resp_cpu: 11929421 --并行访问代价
Access Path: index (RangeScan)
Index: T1_IDX
resc_io: 3.00 resc_cpu: 53924 --串行访问代价
ix_sel: 0.0017233 ix_sel_with_filters: 0.0017233
索引选择率 带过滤条件索引选择率
Cost: 3.01 Resp: 3.01 Degree: 1
Best:: AccessPath: IndexRange Index: T1_IDX
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 87.37 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
*** 2012-04-25 10:53:00.998
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T Alias: T
Card: Original: 50701 Rounded: 94 Computed: 94.01 Non Adjusted: 94.01
Access Path: TableScan
Cost: 22.53 Resp: 22.53 Degree: 0
Cost_io: 21.00 Cost_cpu: 10752644
Resp_io: 21.00 Resp_cpu: 10752644
Access Path: index (index (FFS))
Index: T_IDX
resc_io: 26.00 resc_cpu: 9416771
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 27.34 Resp: 27.34 Degree: 1
Cost_io: 26.00 Cost_cpu: 9416771
Resp_io: 26.00 Resp_cpu: 9416771
Access Path: index (IndexOnly)
Index: T_IDX
resc_io: 2.00 resc_cpu: 33243
ix_sel: 0.0018543 ix_sel_with_filters: 0.0018543
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: T_IDX
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 94.01 Bytes: 0
这部分展示了CBO计算的每个对象单独访问的代价。CBO要计算出每个对象单独访问时的代价，通过比较所有的数据访问的代价，选择出代价最小的一种访问方式。以T表为例我们比较关心如下两个指标
Card:Original:50741
原纪录数，也就是操作数据源的数据纪录数，在这里就是表的实际纪录50741
Card：Rounded：94
输出的纪录数，CBO计算出通过条件过滤，预计得到的纪录数。我们知道T安装条件小于100的纪录数是94条，这里估算出是96条，比较接近实际值。
通过这一部分的信息我们看到，对于T表，CBO人为可能使用下面几种方式来访问数据。
全表扫描
Access Path: TableScan
索引快速扫描
Access Path: index (index (FFS))
单独访问索引
Access Path: index (IndexOnly)
因为在结果集里面是T1表的信息，所以对于T表，只需要访问索引做关联条件查询，不需要访问表，所以单独访问索引也是可行的。
CBO计算出三种方式产生的代价分别是：
TableScan: 22.53
index (FFS) 26
index (IndexOnly) 2.00
很显然，单独访问索引的方式是代价最低的，所以CBO得出的结论，对于T表上的查询，选择使用单独访问索引的方式。
Best:: AccessPath: IndexRange Index: T_IDX
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 94.01 Bytes: 0
T1表的分析方法雷同，这里不再赘述。 这一部分，CBO计算了每个表单独进行数据访问代价最小的方式，为下一步表关联查询提供了代价计算的数据依据
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: T1[T1]#0 T[T]#1 #T1关联T
***************
Now joining: T[T]#1
***************
NL Join #NESTED LOOPS JOIN
Outer table: Card: 87.37 Cost: 3.01 Resp: 3.01 Degree: 1 Bytes: 29
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 1773.79 Resp: 1773.79 Degree: 0
Cost_io: 1641.00 Cost_cpu: 935533938
Resp_io: 1641.00 Resp_cpu: 935533938
Access Path: index (index (FFS))
Index: T_IDX
resc_io: 24.52 resc_cpu: 9416771
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: T Alias: T
Access Path: index (FFS)
NL Join: Cost: 2252.29 Resp: 2252.29 Degree: 0
Cost_io: 2136.00 Cost_cpu: 819313026
Resp_io: 2136.00 Resp_cpu: 819313026
kkofmx: index filter:"T"."X"<100 AND "T"."X"="T1"."ID" AND "T1"."ID"<100
Access Path: index (AllEqJoinGuess)
Index: T_IDX
resc_io: 1.00 resc_cpu: 8171
ix_sel: 1.9723e-05 ix_sel_with_filters: 3.6573e-08
NL Join (ordered): Cost: 90.11 Resp: 90.11 Degree: 1
Cost_io: 90.00 Cost_cpu: 769190
Resp_io: 90.00 Resp_cpu: 769190
Best NL cost: 90.11 #最好的nested loops join方式，代价为90.11
resc: 90.11 resc_io: 90.00 resc_cpu: 769190
resp: 90.11 resp_io: 90.00 resp_cpu: 769190
Join Card: 86.47 = outer (87.37) * inner (94.01) * sel (0.010526)
Join Card - Rounded: 86 Computed: 86.47
SM Join #SORT MERGE JOIN
Outer table:
resc: 3.01 card 87.37 bytes: 29 deg: 1 resp: 3.01
Inner table: T Alias: T
resc: 2.00 card: 94.01 bytes: 4 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 106 Area size: 131072 Max Area size: 18874368
Degree: 1
Blocks to Sort: 1 Row size: 15 Total Rows: 94
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 7073149
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost=0.00]
SM cost: 6.02 #Sort merge join的代价为6.02
resc: 6.02 resc_io: 5.00 resc_cpu: 7160316
resp: 6.02 resp_io: 5.00 resp_cpu: 7160316
HA Join
Outer table:
resc: 3.01 card 87.37 bytes: 29 deg: 1 resp: 3.01
Inner table: T Alias: T
resc: 2.00 card: 94.01 bytes: 4 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 5.52 Resp: 5.52 [multiMatchCost=0.00]
HA cost: 5.52 #hash join的代价为5.52
resc: 5.52 resc_io: 5.00 resc_cpu: 3632312
resp: 5.52 resp_io: 5.00 resp_cpu: 3632312
Best:: JoinMethod: Hash
Cost: 5.52 Degree: 1 Resp: 5.52 Card: 86.47 Bytes: 33
***********************
Best so far: Table#: 0 cost: 3.0077 card: 87.3729 bytes: 2523
Table#: 1 cost: 5.5156 card: 86.4652 bytes: 2838
#CBO得出结论，T1表关联T表代价最下的join方式为hash join的代价为5.52
***********************
Join order[2]: T[T]#1 T1[T1]#0 #T表关联T1表
***************
Now joining: T1[T1]#0
***************
NL Join #NESTED LOOPS JOIN
Outer table: Card: 94.01 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 4
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 5324.17 Resp: 5324.17 Degree: 0
Cost_io: 5165.00 Cost_cpu: 1121398858
Resp_io: 5165.00 Resp_cpu: 1121398858
kkofmx: index filter:"T1"."ID"<100
Access Path: index (AllEqJoinGuess)
Index: T1_IDX
resc_io: 2.00 resc_cpu: 15463
ix_sel: 1.9723e-05 ix_sel_with_filters: 3.3989e-08
NL Join (ordered): Cost: 190.21 Resp: 190.21 Degree: 1
Cost_io: 190.00 Cost_cpu: 1491454
Resp_io: 190.00 Resp_cpu: 1491454
Best NL cost: 190.21 #最好的nested loops join的代价为190.21
resc: 190.21 resc_io: 190.00 resc_cpu: 1491454
resp: 190.21 resp_io: 190.00 resp_cpu: 1491454
Join Card: 86.47 = outer (94.01) * inner (87.37) * sel (0.010526)
Join Card - Rounded: 86 Computed: 86.47
SM Join #Sort merge join
Outer table:
resc: 2.00 card 94.01 bytes: 4 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 3.01 card: 87.37 bytes: 29 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 106 Area size: 131072 Max Area size: 18874368
Degree: 1
Blocks to Sort: 1 Row size: 42 Total Rows: 87
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 7070644
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost=0.00]
SM cost: 6.02 #Sort merge join的代价为6.02
resc: 6.02 resc_io: 5.00 resc_cpu: 7157811
resp: 6.02 resp_io: 5.00 resp_cpu: 7157811
HA Join #hash join
Outer table:
resc: 2.00 card 94.01 bytes: 4 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 3.01 card: 87.37 bytes: 29 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 5.52 Resp: 5.52 [multiMatchCost=0.00]
HA cost: 5.52 #hash join的代价为5.52，这里计算出来的代价值和上面T1关联T表的代价值相等，那么CBO会继续比较串行执行和并行执行的IO和CPU代价
resc: 5.52 resc_io: 5.00 resc_cpu: 3632662 #串行执行的CPU代价为3632662大于上面计算出来的3632312
resp: 5.52 resp_io: 5.00 resp_cpu: 3632662 #并行执行的CPU代价为3632662大于上面计算出来的3632312
Join order aborted: cost > best plan cost # 废弃该join方式
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [1 0 ]
Final - All Rows Plan: Best join order: 1 # 得出结论，采用T1表hash joinT表的方式
Cost: 5.5156 Degree: 1 Card: 86.0000 Bytes: 2838 # 具体「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
