ITPub博客

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

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

原创 Linux操作系统 作者:realkid4 时间:2011-08-30 22:44:20 0 删除 编辑

 

查询优化器Query OptimizerOracle数据库的一个核心技术,作用是将输入SQL转化为最优的执行计划。由于SQL语句本身是一种描述特性的语言,Query Optimizer生成最优的执行计划,就是各品种、各版本DBMS的追求目标。

 

 

Oracle Query Optimizer而言,经历了RBOCBO两个时代。在RBORule-Based Optimizer)时代,Oracle是依据一系列固化的执行路径选择规则来生成执行计划。这种方式的优点是简单,但是缺点在于不能应付复杂数据条件和过于僵化。

 

 

CBOCost-Based Optimizer)称为基于成本的优化器。CBO的工作完全依赖于对数据库对象的统计量,如数据表、索引等对象的统计信息。此外,还有一些内部的系统参数和内部计算公式。CBO将系统I/OCPU转化为统一的成本度量,比较多条可能的执行路径成本差额,最后将成本cost最少的一个作为实际生成的执行计划。

 

借助Oracle10053事件event,我们可以监控到CBOSQL进行成本计算和路径选择的过程和方法。本篇就通过实验来进行演示:

 

 

1、  实验环境准备

 

我们在Oracle10g下构建实验环境。

 

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE     10.2.0.1.0       Production

 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

 

 

构建数据表T,其中的数据列object_id建立索引,而且设置为非空属性。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> create index idx_t_object_id on t(object_id);

Index created

 

SQL> alter table t modify object_id not null;

Table altered

 

 

之后,我们手工的对数据表T进行统计量收集。

 

 

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

PL/SQL procedure successfully completed

 

 

2、统计量展示

 

统计量在Oracle优化器中扮演着极其重要的作用。可以说如果没有统计量信息,Oracle CBO优化器就没有生成执行计划的依据。统计量简单的来说就是对查询相关对象,如数据表、索引、各类型约束的汇总信息,通常是针对取值方面和段segment大小方面。

 

Oracle统计量是随着对象进行保存,保存在数据库的数据字典中的。

 

 

--存储数据段角度的统计信息

SQL> select segment_name, segment_type, bytes, bytes, extents, blocks from user_segments where segment_name='T';

 

SEGMENT_NA SEGMENT_TYPE            BYTES      BYTES    EXTENTS     BLOCKS

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

T          TABLE                 6291456    6291456         21        768

 

SQL> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN, DEGREE ,SAMPLE_SIZE from dba_tables where table_name='T' and wner='SYS';

 

  NUM_ROWS     BLOCKS EMPTY_BLOCKS   AVG_ROW_LEN  DEGREE               SAMPLE_SIZE

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

     53308        735            0           93             1                 53308

 

 

此外,数据表中的索引和数据列也有对应的统计信息。

 

 

SQL> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN, DEGREE ,SAMPLE_SIZE from dba_tables where table_name='T' and wner='SYS';

 

  NUM_ROWS     BLOCKS EMPTY_BLOCKS   AVG_ROW_LEN  DEGREE               SAMPLE_SIZE

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

     53308        735            0           93             1                 53308

 

 

SQL> select column_name, num_distinct,DENSITY,  NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, HISTOGRAM  from dba_tab_col_statistics where table_name='T' and wner='SYS';

 

COLUMN_NAME          NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM

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

OWNER                          25       0.04          0           1        3756 NONE

OBJECT_NAME                 31510 3.17359568          0           1       53308 NONE

SUBOBJECT_NAME                199 0.00502512      52862           1         446 NONE

OBJECT_ID                   53308 1.87589104          0           1       53308 NONE

(篇幅原因,有省略……

 

13 rows selected

 

 

统计量信息主要集中在两个部分环节:段(数据段、索引段)的空间描述信息、数据值列的形态描述信息。统计量的作用就是将这两个部分内容加以量化处理,用于CBO生成执行计划的成本试算。

 

3、使用事件监控获取trace文件

 

下面就可以使用10053事件的跟踪优化器功能。

 

--解除跟踪dump文件大小控制;

SQL> alter session set max_dump_file_size = unlimited;

Session altered

 

--开启10053事件监控

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Session altered

 

--对该SQLCBO执行计划生成过程进行监控;

SQL> select /*+ 10053 trace */count(*) from t;

  COUNT(*)

----------

     53308

 

SQL> exit;

 

 

之后,我们需要获取生成trace文件的具体名称路径。这里的方法很多,比如使用SQL语句、oradebug tracefile_name或者11g下的v$diag_info等等。本篇中笔者使用自定义的方法获取到。

 

 

 

SQL> select f_get_trace_name from dual;

 

F_GET_TRACE_NAME

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

C:\TOOL\ORACLE\ORACLE\PRODUCT\10.2.0\ADMIN\OTS\UDUMP\ots_ora_2968.trc

 

 

在指定的目录下,我们是可以获取到对应文件名的跟踪文件。

 

 

4Library Cache中的shared cursor信息

 

在检查trace file信息之前,我们先检查一下library cache中生成的shared cursor信息,以及对应的执行计划。这里我们先从v$sqlarea中寻找父游标信息。

 

 

SQL> select sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ 10053 trace */%';

 

SQL_ID        VERSION_COUNT EXECUTIONS

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

an041xjjnbr8q             1          1

 

 

SQL> select sql_id, child_number, executions from v$sql where sql_text like 'select /*+ 10053 trace */%';

 

SQL_ID        CHILD_NUMBER EXECUTIONS

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

an041xjjnbr8q            0          1

 

 

一个父游标下对应一个子游标,使用dbms_xplan抽取出执行计划查看。

 

 

SQL> select * from table(dbms_xplan.display_cursor('an041xjjnbr8q',0,'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  an041xjjnbr8q, child number 0

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

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

Plan hash value: 1572773910

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

| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time

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

|   0 | SELECT STATEMENT      |                 |       |    29 (100)|

|   1 |  SORT AGGREGATE       |                 |     1 |            |

|   2 |   INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 53308 |    29   (4)| 00:00:01

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1

   2 - SEL$1 / T@SEL$1

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

  */

Column Projection Information (identified by operation id):

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

   1 - (#keys=0) COUNT(*)[22]

 

38 rows selected

 

 

执行计划中使用了Index Fast Full Scan路径,Cost成本为29

 

 

5Trace File结果初探

 

打开trace file,可以看到很多关于CBO计算执行计划的参数和基础信息,最值得关注的部分如下:

 

 

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

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在不断的比较若干种访问方式的成本计算,最后发现Best的结果路径是最好的是Index Fast Full Scan路径,成本值为29.24。这个与我们从Library Cache中抽取出的结果一致。

 

Trace File中包括的内容很多,值得我们仔细研究。我们将在下篇中着重对此进行说明分析。

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

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7690294