10053内容详解(如何看懂10053)

最新推荐文章于 2024-06-05 07:30:00 发布

深圳gg

最新推荐文章于 2024-06-05 07:30:00 发布

阅读量1.7k

点赞数

分类专栏： Oracle SQL优化

本文链接：https://blog.csdn.net/stevendbaguo/article/details/8131962

版权

Oracle SQL优化专栏收录该内容

130 篇文章 7 订阅

订阅专栏

比较长，要有点耐心看，做个实验。

SQL> create table t_objects as select * from user_objects;

SQL> create table t_tables as select * from user_tables;

SQL> create index ind_objects on T_OBJECTS (object_name);

SQL> create index ind_tables on T_TABLES (table_name);

SQL>exec dbms_stats.gather_table_stats(user,'T_OBJECTS',cascade => true);
SQL>exec dbms_stats.gather_table_stats(user,'T_TABLES',cascade => true);

SQL> alter session set events '10053 trace name context forever,level 1';

SQL> explain plan for select * from t_objects o, t_tables t

2 where t.table_name = o.object_name;

SQL> alter session set events '10053 trace name context off';

一、下面的部分CBO的主要工作是对SQL语句的谓词进行分析、重写，把它改为最符合逻辑的SQL语句

**************************

Predicate Move-Around (PM)

**************************

select * from t_objects o, t_tables t

where t.table_name = o.object_name;

改为了：

SELECT "O"."OBJECT_NAME" "OBJECT_NAME",

"O"."SUBOBJECT_NAME" "SUBOBJECT_NAME",

"O"."OBJECT_ID" "OBJECT_ID",

"O"."DATA_OBJECT_ID" "DATA_OBJECT_ID",

"O"."OBJECT_TYPE" "OBJECT_TYPE",

"O"."CREATED" "CREATED",

"O"."LAST_DDL_TIME" "LAST_DDL_TIME",

"O"."TIMESTAMP" "TIMESTAMP",

"O"."STATUS" "STATUS",

"O"."TEMPORARY" "TEMPORARY",

"O"."GENERATED" "GENERATED",

"O"."SECONDARY" "SECONDARY",

"T"."TABLE_NAME" "TABLE_NAME",

"T"."TABLESPACE_NAME" "TABLESPACE_NAME",

"T"."CLUSTER_NAME" "CLUSTER_NAME",

"T"."IOT_NAME" "IOT_NAME",

"T"."STATUS" "STATUS",

"T"."PCT_FREE" "PCT_FREE",

"T"."PCT_USED" "PCT_USED",

"T"."INI_TRANS" "INI_TRANS",

"T"."MAX_TRANS" "MAX_TRANS",

"T"."INITIAL_EXTENT" "INITIAL_EXTENT",

"T"."NEXT_EXTENT" "NEXT_EXTENT",

"T"."MIN_EXTENTS" "MIN_EXTENTS",

"T"."MAX_EXTENTS" "MAX_EXTENTS",

"T"."PCT_INCREASE" "PCT_INCREASE",

"T"."FREELISTS" "FREELISTS",

"T"."FREELIST_GROUPS" "FREELIST_GROUPS",

"T"."LOGGING" "LOGGING",

"T"."BACKED_UP" "BACKED_UP",

"T"."NUM_ROWS" "NUM_ROWS",

"T"."BLOCKS" "BLOCKS",

"T"."EMPTY_BLOCKS" "EMPTY_BLOCKS",

"T"."AVG_SPACE" "AVG_SPACE",

"T"."CHAIN_CNT" "CHAIN_CNT",

"T"."AVG_ROW_LEN" "AVG_ROW_LEN",

"T"."AVG_SPACE_FREELIST_BLOCKS" "AVG_SPACE_FREELIST_BLOCKS",

"T"."NUM_FREELIST_BLOCKS" "NUM_FREELIST_BLOCKS",

"T"."DEGREE" "DEGREE",

"T"."INSTANCES" "INSTANCES",

"T"."CACHE" "CACHE",

"T"."TABLE_LOCK" "TABLE_LOCK",

"T"."SAMPLE_SIZE" "SAMPLE_SIZE",

"T"."LAST_ANALYZED" "LAST_ANALYZED",

"T"."PARTITIONED" "PARTITIONED",

"T"."IOT_TYPE" "IOT_TYPE",

"T"."TEMPORARY" "TEMPORARY",

"T"."SECONDARY" "SECONDARY",

"T"."NESTED" "NESTED",

"T"."BUFFER_POOL" "BUFFER_POOL",

"T"."ROW_MOVEMENT" "ROW_MOVEMENT",

"T"."GLOBAL_STATS" "GLOBAL_STATS",

"T"."USER_STATS" "USER_STATS",

"T"."DURATION" "DURATION",

"T"."SKIP_CORRUPT" "SKIP_CORRUPT",

"T"."MONITORING" "MONITORING",

"T"."CLUSTER_OWNER" "CLUSTER_OWNER",

"T"."DEPENDENCIES" "DEPENDENCIES",

"T"."COMPRESSION" "COMPRESSION",

"T"."DROPPED" "DROPPED"

FROM "TEST"."T_OBJECTS" "O", "TEST"."T_TABLES" "T"

WHERE "T"."TABLE_NAME" = "O"."OBJECT_NAME"

AND "T"."TABLE_NAME" ='T_OBJECTS'

二、下面是解释trace文件用到的一些缩写的指标定义

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

SPJ - select-project-join

SJC - set join conversion

SU - subquery unnesting

OBYE - order by elimination

ST - star transformation

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)

CPUCSPEED - 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)

512: 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

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

三、绑定变量的描述，由于这里没有使用绑定变量，所以没有信息

*******************************************

Peeked values of the binds in SQL statement

*******************************************

四、一些修复的bug信息

*********************************

Bug Fix Control Environment

*********************************

fix 4611850 = enabled

fix 4663804 = enabled

fix 4663698 = enabled

fix 4545833 = enabled

fix 3499674 = disabled

fix 4584065 = enabled

…………………………………省略…………………………

五、性能相关的初始化参数

*************************************

PARAMETERS WITH DEFAULT VALUES

******************************

optimizer_mode_hinted = false

parallel_execution_enabled = true

optimizer_features_enable = 10.2.0.4

cpu_count = 2

hash_area_size = 131072

bitmap_merge_area_size = 1048576

sort_area_size = 65536

pga_aggregate_target = 198656 KB

_db_file_optimizer_read_count = 16

…………………………………省略…………………………

六、SQL引用对象的基本信息(具体的意思可以看标题二的内容)

下列的信息在CBO做执行计划代价的计算上都要作为输入的值。

***************************************

BASE STATISTICAL INFORMATION

**************************************

Table Stats::

Table: T_TABLES Alias: T

#Rows: 41 #Blks: 5 AvgRowLen: 192.00

Column (#1): TABLE_NAME(VARCHAR2)

AvgLen: 22.00 NDV: 41 Nulls: 0 Density: 0.02439

Index Stats::

Index: IND_TABLES Col#: 1

LVLS: 0 #LB: 1 #DK: 41 LB/K: 1.00 DB/K: 1.00 CLUF: 8.00

***********************

Table Stats::

Table: T_OBJECTS Alias: O

#Rows: 373 #Blks: 8 AvgRowLen: 83.00

Column (#1): OBJECT_NAME(VARCHAR2)

AvgLen: 20.00 NDV: 351 Nulls: 0 Density: 0.0026895

Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242

Index Stats::

Index: IND_OBJECTS Col#: 1

LVLS: 1 #LB: 2 #DK: 351 LB/K: 1.00 DB/K: 1.00 CLUF: 133.00

************************************************************

七、CBO计算每个对象单独访问的代价

Card: Original: 373 原纪录数 Rounded: 1 输出纪录数，可以看到两张表的访问索引的访问方式cost低，所以都走索引。

T_OBJECTS的访问评估：

SINGLE TABLE ACCESS PATH

-----------------------------------------

BEGIN Single Table Cardinality Estimation

-----------------------------------------

Using density: 0.0026895 of col #1 as selectivity of unpopular value pred

Table: T_OBJECTS Alias: O

Card: Original: 373 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00

-----------------------------------------

END Single Table Cardinality Estimation

-----------------------------------------

Access Path: TableScan

Cost: 3.02 Resp: 3.02 Degree: 0

Cost_io: 3.00 Cost_cpu: 131792

Resp_io: 3.00 Resp_cpu: 131792

Using density: 0.0026895 of col #1 as selectivity of unpopular value pred

Access Path: index (AllEqRange)

Index: IND_OBJECTS

resc_io: 2.00 resc_cpu: 16233

ix_sel: 0.0026895 ix_sel_with_filters: 0.0026895

Cost: 2.00 Resp: 2.00 Degree: 1

Best:: AccessPath: IndexRange Index: IND_OBJECTS

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0

T_TABLES的访问评估：

SINGLE TABLE ACCESS PATH

-----------------------------------------

BEGIN Single Table Cardinality Estimation

-----------------------------------------

Table: T_TABLES Alias: T

Card: Original: 41 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00

-----------------------------------------

END Single Table Cardinality Estimation

-----------------------------------------

Access Path: TableScan

Cost: 3.01 Resp: 3.01 Degree: 0

Cost_io: 3.00 Cost_cpu: 44747

Resp_io: 3.00 Resp_cpu: 44747

Access Path: index (AllEqRange)

Index: IND_TABLES

resc_io: 2.00 resc_cpu: 15533

ix_sel: 0.02439 ix_sel_with_filters: 0.02439

Cost: 2.00 Resp: 2.00 Degree: 1

Best:: AccessPath: IndexRange Index: IND_TABLES

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0

***************************************

八、CBO计算列出两个表关联方式，并计算出每一种关联方式的代价，最终选择最小的cost

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]: T_TABLES[T]#0 T_OBJECTS[O]#1—连接1

***************

Now joining: T_OBJECTS[O]#1

***************

NL Join

Outer table: Card: 1.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 192

Inner table: T_OBJECTS Alias: O

Access Path: TableScan

NL Join: Cost: 5.03 Resp: 5.03 Degree: 1

Cost_io: 5.00 Cost_cpu: 147324

Resp_io: 5.00 Resp_cpu: 147324

kkofmx: index filter:"T"."TABLE_NAME"="O"."OBJECT_NAME" AND "T"."TABLE_NAME"='T_OBJECTS' AND "O"."OBJECT_NAME"='T_OBJECTS'

Using density: 0.0026895 of col #1 as selectivity of unpopular value pred

Access Path: index (AllEqJoin)

Index: IND_OBJECTS

resc_io: 2.00 resc_cpu: 16233

ix_sel: 0.0026895 ix_sel_with_filters: 0.0026895

NL Join: Cost: 4.01 Resp: 4.01 Degree: 1

Cost_io: 4.00 Cost_cpu: 31766

Resp_io: 4.00 Resp_cpu: 31766

Best NL cost: 4.01

resc: 4.01 resc_io: 4.00 resc_cpu: 31766

resp: 4.01 resp_io: 4.00 resp_cpu: 31766

Join Card: 1.00 = outer (1.00) * inner (1.00) * sel (1)

Join Card - Rounded: 1 Computed: 1.00

SM Join

Outer table:

resc: 2.00 card 1.00 bytes: 192 deg: 1 resp: 2.00

Inner table: T_OBJECTS Alias: O

resc: 2.00 card: 1.00 bytes: 83 deg: 1 resp: 2.00

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 231 Area size: 202752 Max Area size: 40684544

Degree: 1

Blocks to Sort: 1 Row size: 102 Total Rows: 1

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 5819699

Total Temp space used: 0

SM join: Resc: 5.01 Resp: 5.01 [multiMatchCost=0.00]

SM cost: 5.01

resc: 5.01 resc_io: 4.00 resc_cpu: 5851465

resp: 5.01 resp_io: 4.00 resp_cpu: 5851465

HA Join

Outer table:

resc: 2.00 card 1.00 bytes: 192 deg: 1 resp: 2.00

Inner table: T_OBJECTS Alias: O

resc: 2.00 card: 1.00 bytes: 83 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: 4.51 Resp: 4.51 [multiMatchCost=0.00]

HA cost: 4.51

resc: 4.51 resc_io: 4.00 resc_cpu: 2941866

resp: 4.51 resp_io: 4.00 resp_cpu: 2941866

Best:: JoinMethod: NestedLoop--消耗最少的

Cost: 4.01 Degree: 1 Resp: 4.01 Card: 1.00 Bytes: 275

***********************

Best so far: Table#: 0 cost: 2.0027 card: 1.0000 bytes: 192

Table#: 1 cost: 4.0055 card: 1.0032 bytes: 275

***********************

Join order[2]: T_OBJECTS[O]#1 T_TABLES[T]#0--连接2

***************

Now joining: T_TABLES[T]#0

***************

NL Join

Outer table: Card: 1.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 83

Inner table: T_TABLES Alias: T

Access Path: TableScan

NL Join: Cost: 5.01 Resp: 5.01 Degree: 1

Cost_io: 5.00 Cost_cpu: 60980

Resp_io: 5.00 Resp_cpu: 60980

kkofmx: index filter:"T"."TABLE_NAME"="O"."OBJECT_NAME" AND "T"."TABLE_NAME"='T_OBJECTS' AND "O"."OBJECT_NAME"='T_OBJECTS'

Access Path: index (AllEqJoin)

Index: IND_TABLES

resc_io: 1.00 resc_cpu: 9261

ix_sel: 0.02439 ix_sel_with_filters: 0.02439

NL Join: Cost: 3.00 Resp: 3.00 Degree: 1

Cost_io: 3.00 Cost_cpu: 25494

Resp_io: 3.00 Resp_cpu: 25494

Best NL cost: 3.00

resc: 3.00 resc_io: 3.00 resc_cpu: 25494

resp: 3.00 resp_io: 3.00 resp_cpu: 25494

Join Card: 1.00 = outer (1.00) * inner (1.00) * sel (1)

Join Card - Rounded: 1 Computed: 1.00

SM Join

Outer table:

resc: 2.00 card 1.00 bytes: 83 deg: 1 resp: 2.00

Inner table: T_TABLES Alias: T

resc: 2.00 card: 1.00 bytes: 192 deg: 1 resp: 2.00

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 231 Area size: 202752 Max Area size: 40684544

Degree:1 Blocks to Sort:1 Row size: 222 Total Rows:1

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 5819699

Total Temp space used: 0

SM join: Resc: 5.01 Resp: 5.01 [multiMatchCost=0.00]

SM cost: 5.01

resc: 5.01 resc_io: 4.00 resc_cpu: 5851465

resp: 5.01 resp_io: 4.00 resp_cpu: 5851465

HA Join

Outer table:

resc: 2.00 card 1.00 bytes: 83 deg: 1 resp: 2.00

Inner table: T_TABLES Alias: T

resc: 2.00 card: 1.00 bytes: 192 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: 4.51 Resp: 4.51 [multiMatchCost=0.00]

HA cost: 4.51

resc: 4.51 resc_io: 4.00 resc_cpu: 2941866

resp: 4.51 resp_io: 4.00 resp_cpu: 2941866

Best:: JoinMethod: NestedLoop—消耗最少的

Cost: 3.00 Degree: 1 Resp: 3.00 Card: 1.00 Bytes: 275

***********************

Best so far: Table#: 1 cost: 2.0028 card: 1.0032 bytes: 83

Table#: 0 cost: 3.0044 card: 1.0032 bytes: 275

(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: 2—连接1最小cost为4.01，连接2最小cost为3.00，所以最终选择了连接2。

Cost: 3.0044 Degree: 1 Card: 1.0000 Bytes: 275

Resc: 3.0044 Resc_io: 3.0000 Resc_cpu: 25494

Resp: 3.0044 Resp_io: 3.0000 Resc_cpu: 25494

九、执行计划

============

Plan Table

============

----------------------------------------------------+-----------------------------------+

----------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 3 | |

| 1 | MERGE JOIN CARTESIAN | | 1 | 275 | 3 | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 1 | 83 | 2 | 00:00:01 |

| 3 | INDEX RANGE SCAN | IND_OBJECTS| 1 | | 1 | 00:00:01 |

| 4 | BUFFER SORT | | 1 | 192 | 1 | 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID | T_TABLES | 1 | 192 | 1 | 00:00:01 |

| 6 | INDEX RANGE SCAN | IND_TABLES | 1 | | 0 | |

----------------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

3 - access("O"."OBJECT_NAME"='T_OBJECTS')

6 - access("T"."TABLE_NAME"='T_OBJECTS')

可以看到最终CBO选择的是nestloop,这与执行计划的 MERGE JOIN CARTESIAN 对应，笛卡儿算法：是每个集合的任务一个成员都要与其他集合的每个成员进行匹配。

深圳gg

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录