上篇中，我们主要介绍了10053事件的作用和跟踪文件生成规则。本篇中，我们将使用上篇生成的跟踪文件ots_ora_2968.trc，从结构上分析CBO在选择一个执行计划的步骤和方法。

 

6、Trace File结果综析

 

Trace文件是Oracle预留的用于对系统内部行为进行监控的日志性质文件。10053的跟踪文件具体可以分为如下组成部分：

 

ü        缩略语信息说明

 

在Oracle中，有很多内部使用的缩略语。掌握这些习惯性缩写，可以大大提高我们对Internal理解的能力。在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

（篇幅原因，有省略……）

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

 

 

这些缩略语都会在跟踪文件中出现。

 

ü        系统环境基本信息

 

如同标签一样，在Trace文件都包括一个trace文件头。其中包括了当前系统的基本信息和环境信息。如下：

 

 

Dump file c:\tool\oracle\oracle\product\10.2.0\admin\ots\udump\ots_ora_2968.trc

Tue Aug 30 10:29:57 2011

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Windows Server 2003 Version V5.2 Service Pack 1

CPU                 : 1 - type 586, 1 Physical Cores

Process Affinity    : 0x00000000

Memory (Avail/Total): Ph:133M/1015M, Ph+PgF:1472M/2453M, VA:1460M/2047M

Instance name: ots

 

Redo thread mounted by this instance: 1

Oracle process number: 22

 

Windows thread id: 2968, image: ORACLE.EXE (SHAD)

 

 

上面的信息主要表示的数据库实例的基本信息，包括内存、CPU和一些OS的版本信息和实例名称信息。Oracle DB的行为，与当前实例所在的软硬件环境是密切相关的，所以在跟踪文件的文件头，首先列出了实例基本信息。

 

 

 

ü        Query Block基本信息

 

在实例基本信息之后，就是关于进行跟踪SQL的基本信息情况。

 

 

*** 2011-08-30 10:29:57.703

*** ACTION NAME:(Command Window - New) 2011-08-30 10:29:57.656

*** MODULE NAME:(PL/SQL Developer) 2011-08-30 10:29:57.656

*** SERVICE NAME:(OTS) 2011-08-30 10:29:57.656

*** SESSION ID:(142.14) 2011-08-30 10:29:57.656

Registered qb: SEL$1 0x796af14 (PARSER)

  signature (): qb_name=SEL$1 nbfros=1 flg=0

    fro(0): flg=4 bjn=101798 hint_alias="T"@"SEL$1"

 

 

其中的Action Name、Model Name、Service name以及Session ID分别表示使用跟踪会话SQL的背景信息。从中我们可以看到该SQL是使用PL/SQL Developer的Command Window功能，连接的Service Name是OTS。当前会话编号是142.14。

 

另一部分是对SQL查询本身的一个解析，qb（Query Block）对应的SEL$1的解析编号为0x796af14。Objn表示进行查询操作的数据对象编号。

 

 

ü        SQL改写Transformation过程

 

对一般成熟的DBMS Query Optimizer而言，都会在parse前试图对输入SQL进行改写（Query Transformation）。这个过程的目的是让SQL语句更容易生成高效的执行计划。常见的改写手段有：谓词推进（Predicate Move-Around）、视图合并（View Merging）和子查询嵌套展开（Subquery Unnesting）等。

 

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

Predicate Move-Around (PM)

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

PM: Considering predicate move-around in SEL$1 (#0).

PM:   Checking validity of predicate move-around in SEL$1 (#0).

CBQT: Validity checks failed for an041xjjnbr8q.

CVM: Considering view merge in query block SEL$1 (#0)

CBQT: Validity checks failed for an041xjjnbr8q.

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

Subquery Unnest

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

SU: Considering subquery unnesting in query block SEL$1 (#0)

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

Set-Join Conversion (SJC)

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

SJC: Considering set-join conversion in SEL$1 (#0).

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

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

         apadrv-start: call(in-use=172, alloc=0), compile(in-use=31672, alloc=0)

kkoqbc-start

: call(in-use=172, alloc=0), compile(in-use=32276, alloc=0)

 

 

可以看出，Query Optimizer使用各种改写手段对an041xjjnbr8q进行改写尝试。注意，这种尝试对一些简单SQL没有过多的意义。

 

此处出现的Transformation过程，也只是优化器对于我们SQL的一种处理流程。

 

ü        优化器参数设置

 

优化器的工作原理是受到系统参数（显示、隐式）、数据库对象统计量。其中，系统参数常常起到优化器工作参数和路径方法选择的作用。在10053事件的优化跟踪文件中，很大的篇幅就是记录着当前使用的优化器参数。

 

 

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

PARAMETERS USED BY THE OPTIMIZER

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

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

  PARAMETERS WITH ALTERED VALUES

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

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

  PARAMETERS WITH DEFAULT VALUES

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

  optimizer_mode_hinted               = false

  _query_rewrite_fudge                = 90

  optimizer_features_enable           = 10.2.0.1

（篇幅原因，有省略……）

  _force_rewrite_enable               = false

  _optimizer_star_tran_in_with_clause = true

  _optimizer_complex_pred_selectivity = true

  _gby_hash_aggregation_enabled       = true

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

  PARAMETERS IN OPT_PARAM HINT

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

 

 

注意，使用参数是按照默认值和改写值进行组织的。影响Oracle行为的参数包括显示参数和隐式参数两个部分。有时候，会根据系统需要对系统参数进行调节，来控制影响优化器计算成本乃至生成执行计划的行为。

 

在Trace文件中，对参数的显示也是根据默认参数和修改参数进行组织的。

 

 

ü        系统system和数据对象参数

 

我们经常说的Oracle统计量，是分为若干类型的。比较常见的有System Statistics、Schema Statistics、Data Dictionary Statistics和Database Statistics。在CBO生成执行计划的时候，这些统计量都会作为CBO优化器的参考信息计入到成本计算公式中。

 

 

 

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

SYSTEM STATISTICS INFORMATION

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

  Using NOWORKLOAD Stats

  CPUSPEED: 485 millions instruction/sec

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table:  T  Alias:  T

    #Rows: 53308  #Blks:  735  AvgRowLen:  93.00

Index Stats::

  Index: IDX_T_OBJECT_ID  Col#: 4

LVLS: 1  #LB: 118  #DK: 53308  LB/K: 1.00  DB/K: 1.00  CLUF: 850.00

 

 

在trace文件中，我们看到了system statistics和schema statistics两部分的说明。System statistics中的内容表示计算执行计划时使用的系统状态参数，是满负荷下的系统统计量还是空负荷的。之后是CPU、IO相关的时间和速度信息。

 

Schema统计量中的内容就比较熟悉了，分别是SQL相关的数据表和索引对应的统计量。在上篇中，我们已经看到过这些数据。相当于计算执行计划的过程是使用这些参数的。

 

 

ü        访问路径成本cost计算比较

 

CBO的工作原理，就是一个执行路径试探性生成和比较的过程。其中，访问方式table access path是一个重要的成本计算点。从跟踪文件中，我们可以看到这种路径生成的比较。

 

 

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

SINGLE TABLE ACCESS PATH

  Table:  T  Alias: T    

    Card: Original: 53308  Rounded: 53308  Computed: 53308.00  Non Adjusted: 53308.00

  Access Path: TableScan

    Cost:  164.27  Resp: 164.27  Degree: 0

      Cost_io: 162.00  Cost_cpu: 13230458

      Resp_io: 162.00  Resp_cpu: 13230458

  Access Path: index (index (FFS))

    Index: IDX_T_OBJECT_ID

    resc_io: 28.00  resc_cpu: 7237290

    ix_sel: 0.0000e+000  ix_sel_with_filters: 1

  Access Path: index (FFS)

    Cost:  29.24  Resp: 29.24  Degree: 1

      Cost_io: 28.00  Cost_cpu: 7237290

      Resp_io: 28.00  Resp_cpu: 7237290

  Access Path: index (FullScan)

    Index: IDX_T_OBJECT_ID

    resc_io: 119.00  resc_cpu: 11509051

    ix_sel: 1  ix_sel_with_filters: 1

    Cost: 120.98  Resp: 120.98  Degree: 1

  Best:: AccessPath: IndexFFS  Index: IDX_T_OBJECT_ID

         Cost: 29.24  Degree: 1  Resp: 29.24  Card: 53308.00  Bytes: 0

 

 

Oracle CBO的所谓成本，就是将CPU与IO结合的一个指标。在计算访问路径中，我们可以看到这种选择过程。如我们的实例中，如果直接使用Table Scan，计算出的成本约为164。而之后使用的Index Fast Full Scan（FFS）路径，成本有29.24。对应的Index Full Scan，成本为120。

 

最后，CBO给予了一个BEST路径选择，使用Index Fast Full Scan方式，读取数据索引idx_t_object_id，不进行回表操作。

 

 

ü        生成执行计划

 

经过比较cost过程，是确定执行计划的一种过程。之后就是生成执行计划的结果部分。

 

 

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

OPTIMIZER STATISTICS AND COMPUTATIONS

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

GENERAL PLANS

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

Considering cardinality-based initial join order.

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

Join order[1]:   T[T]#0

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

Best so far: Table#: 0  cost: 29.2436  card: 53308.0000  bytes: 0

(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000

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

Number of join permutations tried: 1

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

Final - All Rows Plan:  Best join order: 1

  Cost: 29.2436  Degree: 1  Card: 53308.0000  Bytes: 0

  Resc: 29.2436  Resc_io: 28.0000  Resc_cpu: 7237290

  Resp: 29.2436  Resp_io: 28.0000  Resc_cpu: 7237290

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SYS"."T" "T"

kkoqbc-end

          : call(in-use=13248, alloc=0), compile(in-use=32844, alloc=0)

apadrv-end: call(in-use=13248, alloc=0), compile(in-use=33392, alloc=0)

 

sql_id=an041xjjnbr8q.

Current SQL statement for this session:

select /*+ 10053 trace */count(*) from t

 

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

Plan Table

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

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

| Id  | Operation              | Name           | Rows  | Bytes | Cost  | Time      |

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

| 0   | SELECT STATEMENT       |                |       |       |    29 |           |

| 1   |  SORT AGGREGATE        |                |     1 |       |       |           |

| 2   |   INDEX FAST FULL SCAN | IDX_T_OBJECT_ID|   52K |       |    29 |  00:00:01 |

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

Predicate Information:

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

 

Content of other_xml column

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

  db_version     : 10.2.0.1

  parse_schema   : SYS

  plan_hash      : 1572773910

  Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))

    END_OUTLINE_DATA

  */

 

 

 

7、结论

 

Oracle Query Optimizer是Oracle中较为复杂的逻辑组件，也是Oracle DBMS的核心技术。作为行业领军的数据库产品，Oracle Query Optimizer属于关键技术。CBO如何选择执行计划，是每个希望深入内核的研究者追寻的目标。

 

使用10053事件，我们可以较为深入的接近CBO的判断轨迹和方式。在出现一些执行计划问题的时候，我们也可以看到CBO为什么没有按照希望的方式运行。

 

10053事件跟踪文件包括很多重要的信息和内容。由于篇幅的原因，只寻找到一个简单的SQL进行trace。