使用10053事件跟蹤CBO優化器決策（上）

查詢優化器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部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-706290/，如需轉載，請註明出處，否則將追究法律責任。

使用10053事件跟蹤CBO優化器決策（上）

相關文章