浅析CPU因素对Oracle cbo优化器的影响

我们知道影响Oracle cbo优化器做出正确的执行计划影响因素有很多，如CPU，I/O cost等。在Oracle的执行计划中也将CPU,IO,TEMP的COST列入其中。通过下图就可以清晰的看到影响CBO的各种因素。

[img]http://dl.iteye.com/upload/attachment/234748/aca100e0-62ce-3ad4-ada3-74e7673e6601.jpg[/img]
为获得CBO的一些统计值，Oracle 9i需要手工执行dbms_stat.gather_system_stats，在Oracle 10g中，采取默认收集。
从Oracle 9i开始我们可以通过隐含参数_optimizer_cost_model来设置CBO选择CPU COST优先还是IO优先。
[quote]alter session set "_optimizer_cost_model"=choose; -- default value

alter session set "_optimizer_cost_model"=io;

alter session set "_optimizer_cost_model"=cpu; [/quote]

设置CBO CPU COST方法如下：
[quote]alter session set "_optimizer_cost_model"=cpu;[/quote]
如果是Oracle 9i设置参数之前还需手动统计系统信息dbms_stat.gather_system_stats。

关闭CBO CPU COST方法如下：
1、在SQL中加 "no_cpu_costing" hint

2、alter session set "_optimizer_cost_model"=io;

3、在 init.ora 中加参数_optimizer_cost_model=io
在这里需要注意optimizer_index_cost_adj应设为默认值 (Oracle9i bug 2820066)
[quote]Notes on Bug 2820066:

CPU cost is computed when optimizer_index_cost_adj is set to a non-default value.

Range of versions believed to be affected: Versions < 10.1.0.2

Platforms affected: Generic (all / most platforms affected)

This issue is fixed in 9.2.0.6 (Server Patch Set) and 10.1.0.2

Bug description: If optimizer_index_cost_adj is set to a non-default value CPU costs are calculated regardless of the optimizer cost model used. If you have optimizer_index_cost_adj set and you are not using the optimizer CPU cost model, but explain plan shows that for queries not using domain indexes CPU costs are being calculated, you are probably hitting this bug.

In sum, CPU cost is always computed regardless of optimizer mode when optimizer_index_cost_adj is set in un-patched Oracle versions less than 10.1.0.2.[/quote]
通过以上基本知识，我们再通过一个详细的案例来进一步说明_optimizer_cost_model参数的作用。
测试版本为

[quote]SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production[/quote]
创建测试表格，并分析测试表格

[quote]SQL> create table dba_t as select * from dba_objects;

Table created.

SQL> select count(*) from dba_t;

COUNT(*)
----------
33017

SQL> create table user_t as select * from user_objects;

Table created.

SQL> select count(*) from user_t;

COUNT(*)
----------
8

SQL> create unique index idx_USER_T on USER_T(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'DBA_T' ,CASCADE=>TRUE);

PL/SQL procedure successfully completed.


SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'USER_T',CASCADE=>TRUE);

PL/SQL procedure successfully completed.[/quote]

查看其执行计划，发现走的是NESTED LOOPS
[quote]SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);

7 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=8 Bytes=792)
1 0 NESTED LOOPS (Cost=45 Card=8 Bytes=792)
2 1 TABLE ACCESS (FULL) OF 'DBA_T' (Cost=45 Card=33017 Bytes
=3103598)

3 1 INDEX (UNIQUE SCAN) OF 'IDX_USER_T' (UNIQUE)


Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
471 consistent gets
0 physical reads
0 redo size
1391 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
7 rows processed[/quote]
如果我们启用CPU COST，就走HASH JOIN了，这主要的原因是Oracle 10g以前，CBO优化器的缺省是IO COST优先的因此在内表很小，并且可以通过索引访问，外表较大情况下，优先选择NESTED LOOP。
[quote]SQL> alter session set "_optimizer_cost_model" = "cpu";

Session altered.

SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);

7 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=8 Bytes=792
)

1 0 HASH JOIN (Cost=120 Card=8 Bytes=792)
2 1 INDEX (FULL SCAN) OF 'IDX_USER_T' (UNIQUE) (Cost=2 Card=
8 Bytes=40)

3 1 TABLE ACCESS (FULL) OF 'DBA_T' (Cost=115 Card=33017 Byte
s=3103598)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
462 consistent gets
0 physical reads
0 redo size
1391 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
[/quote]

再进一步同样的例子在Oracle 10g中测试
[quote]SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod[/quote]

可以发现Oracle 10g已经默认启用 cpu cost。

[quote]SQL> create table dba_t as select * from dba_objects;

Table created.

SQL> select count(*) from dba_t;

COUNT(*)
----------
63713

SQL> create table user_t as select * from user_objects;

Table created.

SQL> select count(*) from user_t;

COUNT(*)
----------
8

SQL> create unique index idx_USER_T on USER_T(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'DBA_T' ,CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'USER_T',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);

7 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3073270263

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

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

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

| 0 | SELECT STATEMENT | | 8 | 800 | 185 (2)| 00:00:03
|

|* 1 | HASH JOIN | | 8 | 800 | 185 (2)| 00:00:03
|

| 2 | INDEX FULL SCAN | IDX_USER_T | 8 | 40 | 1 (0)| 00:00:01
|

| 3 | TABLE ACCESS FULL| DBA_T | 63713 | 5910K| 183 (2)| 00:00:03
|

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="OBJECT_ID")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed[/quote]

我们可以在进一步探讨CPU因素对COST的影响。
SQL> select * from aux_stats$;

SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO STATUS
COMPLETED

SYSSTATS_INFO DSTART
05-30-2009 03:11

SYSSTATS_INFO DSTOP
05-30-2009 03:11


SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO FLAGS 1


SYSSTATS_MAIN [color=red]CPUSPEEDNW 1104.475[/color]


SYSSTATS_MAIN IOSEEKTIM 12.006


SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN IOTFRSPEED 4096


SYSSTATS_MAIN SREADTIM


SYSSTATS_MAIN MREADTIM


SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN CPUSPEED


SYSSTATS_MAIN MBRC


SYSSTATS_MAIN MAXTHR


SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
PVAL2
--------------------------------------------------------------------------------
SYSSTATS_MAIN SLAVETHR


13 rows selected.
附各字段意思，在这里我们使用的是No Workload
[quote]No Workload (NW) stats:
CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds

Workload-related stats:
SREADTIM - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)[/quote]

加大cpuspeednw速度，观察执行计划，可以看到执行计划走nested loop，CPU的影响可见一斑啊
[quote]SQL> execute DBMS_STATS.SET_SYSTEM_STATS (pname => 'cpuspeednw', pvalue => 8000);

PL/SQL procedure successfully completed.

SQL> set AUTOT TRACEONLY EXP STAT
SQL> SELECT * FROM dba_t WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM user_t);

7 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1130854696

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

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

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

| 0 | SELECT STATEMENT | | 8 | 800 | 182 (1)| 00:00:03
|

| 1 | [color=red]NESTED LOOPS [/color] | | 8 | 800 | 182 (1)| 00:00:03
|

| 2 | TABLE ACCESS FULL| DBA_T | 63713 | 5910K| 181 (0)| 00:00:03
|

|* 3 | INDEX UNIQUE SCAN| IDX_USER_T | 1 | 5 | 0 (0)| 00:00:01
|

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


Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID"="OBJECT_ID")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
900 consistent gets
0 physical reads
0 redo size
1459 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed[/quote]

再一进步我们可以通过10053事件获得跟踪
[quote]SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 2';

Session altered.[/quote]
跟踪文件显示：
[quote]*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
[color=red]CPUSPEED: 8000 millions instruction/sec[/color]
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 12 milliseconds (default is 10)
...
Best:: JoinMethod: Hash
Cost: 182.84 Degree: 1 Resp: 182.84 Card: 8.00 Bytes: 100
...
Best:: JoinMethod: NestedLoop
Cost: 181.88 Degree: 1 Resp: 181.88 Card: 8.00 Bytes: 100 [/quote]