ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用10053事件跟踪CBO优化器决策(下)

使用10053事件跟踪CBO优化器决策(下)

原创 Linux操作系统 作者:realkid4 时间:2011-09-04 17:03:06 0 删除 编辑

 

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

 

6Trace 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 NameModel NameService name以及Session ID分别表示使用跟踪会话SQL的背景信息。从中我们可以看到该SQL是使用PL/SQL DeveloperCommand Window功能,连接的Service NameOTS。当前会话编号是142.14

 

另一部分是对SQL查询本身的一个解析,qbQuery Block)对应的SEL$1的解析编号为0x796af14Objn表示进行查询操作的数据对象编号。

 

 

ü        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 StatisticsSchema StatisticsData Dictionary StatisticsDatabase 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 statisticsschema statistics两部分的说明。System statistics中的内容表示计算执行计划时使用的系统状态参数,是满负荷下的系统统计量还是空负荷的。之后是CPUIO相关的时间和速度信息。

 

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的所谓成本,就是将CPUIO结合的一个指标。在计算访问路径中,我们可以看到这种选择过程。如我们的实例中,如果直接使用Table Scan,计算出的成本约为164。而之后使用的Index Fast Full ScanFFS)路径,成本有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 OptimizerOracle中较为复杂的逻辑组件,也是Oracle DBMS的核心技术。作为行业领军的数据库产品,Oracle Query Optimizer属于关键技术。CBO如何选择执行计划,是每个希望深入内核的研究者追寻的目标。

 

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

 

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

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-706625/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7752628