`
itspace
  • 浏览: 978452 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

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

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


为获得CBO的一些统计值，Oracle 9i需要手工执行dbms_stat.gather_system_stats，在Oracle 10g中，采取默认收集。
从Oracle 9i开始我们可以通过隐含参数_optimizer_cost_model来设置CBO选择CPU COST优先还是IO优先。
引用
alter session set "_optimizer_cost_model"=choose; -- default value 

alter session set "_optimizer_cost_model"=io; 

alter session set "_optimizer_cost_model"=cpu;


设置CBO CPU COST方法如下：
引用
alter session set "_optimizer_cost_model"=cpu;

如果是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)
引用
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.

通过以上基本知识，我们再通过一个详细的案例来进一步说明_optimizer_cost_model参数的作用。
测试版本为

引用
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

创建测试表格，并分析测试表格

引用
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.


查看其执行计划，发现走的是NESTED LOOPS
引用
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

如果我们启用CPU COST，就走HASH JOIN了，这主要的原因是Oracle 10g以前，CBO优化器的缺省是IO COST优先的因此在内表很小，并且可以通过索引访问，外表较大情况下，优先选择NESTED LOOP。
引用
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


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

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod


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

引用
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


我们可以在进一步探讨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                  CPUSPEEDNW                       1104.475


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
引用
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)


加大cpuspeednw速度，观察执行计划，可以看到执行计划走nested loop，CPU的影响可见一斑啊
引用
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 |  NESTED LOOPS      |            |     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


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

Session altered.

跟踪文件显示：
引用
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 8000 millions instruction/sec
  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
  • 大小: 99.2 KB
0
顶
0
踩
分享到：
| 探讨Oracle 事务并行恢复
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘_0321.pdf

    ### Oracle CBO优化器与直方图(Histogram)解析 #### 概述 在数据库管理领域，Oracle CBO（Cost-Based Optimizer）成本基于优化器是Oracle数据库管理系统中用于确定查询执行计划的一种重要机制。通过分析SQL语句，...

    ORACLE CBO RBO 优化

    ORACLE CBO RBO 优化

    Oracle优化器介绍

    Oracle 优化器可以分为两大类：基于规则的优化器（RBO）和基于成本的优化器（CBO）。 基于规则的优化器（RBO） RBO 是一种 Based on Rule 的优化器，根据可用的访问路径和访问路径的等级来选择执行计划。RBO 访问...

    Oracle的优化器(Optimizer)

    #### 一、Oracle优化器概述 在Oracle数据库中，每当执行SQL语句时，系统首先需要确定最佳的执行路径或计划，这一过程由**优化器(Optimizer)**负责。优化器的主要任务是评估SQL语句的不同执行计划，并从中选择一个最...

    oracle9i优化器介绍

    影响Oracle9i优化器目标选择的主要因素包括： 1. **OPTIMIZER_MODE初始化参数**：这是决定实例默认优化方法的关键参数。它可以设置为CHOOSE、ALL_ROWS、FIRST_ROWS_n、FIRST_ROWS和RULE。其中，CHOOSE根据数据字典...

    Oracle9i优化器介绍

    在Oracle数据库系统中，优化器的作用不可忽视，它直接影响到数据库的性能和响应时间。下面将对Oracle9i优化器进行详细的介绍。 1. 优化器类型： Oracle9i包含两种主要的优化器：成本基于（Cost-Based Optimizer, ...

    Oracle数据库优化详解

    从Oracle 10g版本开始，原有的基于规则的优化器（RBO）不再受到支持，因此所有老版本Oracle数据库在升级过程中都需要将优化器从RBO迁移到CBO。 在Oracle数据库优化过程中，以下几个方面是重点需要关注的： 1. CBO...

    Oracle语句优化53个规则详解

    #### 一、选用适合的Oracle优化器 在Oracle数据库中，优化器的选择对于SQL语句的执行效率至关重要。Oracle提供了三种优化器模式： 1. **基于规则的优化器（RULE）**：这是一种较老的优化器类型，它根据一系列固定...

    小菜鸟系列-Oracle的优化器与hint

    Oracle提供了多种类型的优化器，包括基于规则的优化器（RBO）和基于成本的优化器（CBO）。然而，CBO是现代Oracle版本中的默认选择，因为它能根据统计信息和实际系统资源消耗来评估执行计划的成本。 CBO依赖于统计...

    oracle cbo

    cbooracle cbo

    如何选择Oracle优化器使你事半功倍

    为了使CBO更准确，你需要定期运行ANALYZE命令，收集对象统计信息，这些信息会影响优化器的成本估算。 3. CHOOSE优化器则根据是否运行过ANALYZE命令来决定使用RULE还是COST。如果已分析，将使用CBO；否则，会采用...

    Oracle优化器介绍(精简完善版).doc

    Oracle优化器经历了从基于规则的优化器(RBO)到基于成本的优化器(CBO)的演变，这一转变显著提升了性能优化能力。 #### RBO与CBO对比 **RBO (Rule-Based Optimizer)**是一种早期的优化技术，它依据预定义的规则集来...

    Oracle优化器案例与原理分析（盖国强）

    在实际应用中，数据库管理员需要对Oracle优化器有深入的理解，以便在遇到性能问题时，可以快速定位并解决问题。通过不断学习和实践，可以更好地掌握优化器的工作机制，实现更高效的数据库管理和查询优化。

    【oracle】oracle查询优化改写

    Oracle有多种优化器，如RBO（Rule-Based Optimizer，基于规则的优化器）和CBO（Cost-Based Optimizer，基于成本的优化器）。CBO是现代Oracle数据库的默认优化器，它通过估算不同执行计划的成本来选择最优路径。这个...

    oracle-优化器详解.docx

    影响优化器优化目标的因素主要有三个：OPTIMIZER_MODE 初始化参数、数据字典中的 CBO 统计数据、用来改变 CBO 优化目标的 Hints。 OPTIMIZER_MODE 初始化参数 ------------------------- OPTIMIZER_MODE 初始化...

    Oracle实战优化器

    ### Oracle实战优化器详解 #### 一、优化器概述 在Oracle数据库中，优化器扮演着至关重要的角色，它的主要任务是为SQL语句选择最优的执行路径，从而提高SQL语句的执行效率。优化器根据不同的策略可以分为两种类型...

    基于成本的ORACLE优化法则 英文原版

    8. **优化器模式和提示**：有时候，CBO的决策可能不理想，此时可以使用优化器模式（如Rule-Based Optimizer, RBO）切换或SQL提示来干预执行计划的选择。 9. **资源管理**：Oracle的资源管理功能允许DBA设定不同用户...

Magicbox
Global site tag (gtag.js) - Google Analytics