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

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

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

借助Oracle的10053事件event，我们可以监控到CBO对SQL进行成本计算和路径选择的过程和方法。本篇就通过实验来进行演示：

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

--对该SQL的CBO执行计划生成过程进行监控；

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

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

4、Library 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

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

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

| 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。

5、Trace 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博客

使用10053事件跟踪CBO优化器决策（上）

最新文章