ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 聊聊Oracle Optimizer相关的几个参数(中)

聊聊Oracle Optimizer相关的几个参数(中)

原创 Linux操作系统 作者:realkid4 时间:2012-04-10 22:46:56 0 删除 编辑

 

我们继续来介绍可以影响Oracle Optimizer工作的若干参数。

 

4、优化器参数2——cursor_sharing

 

Cursor_sharing是Oracle优化器的另一个重要参数,可能也是我们日常讨论较多的一个参数。最开始的cursor_sharing提出,是建立在Oracle希望可以“一蹴而就”的解决hard parse,提高library cache中SQL执行计划重用概率的“美好愿望”下。在相当长的时间里,很多DBA和开发人员都将这个参数视为“金手指”,认为只要开启了这个参数,硬解析比例就会降低,性能瓶颈就会消除。

 

但是无论是从理论上,还是实际使用效果上,cursor_sharing都没有实现当时的目标。

 

笔者在之前的Blog中,对cursor_sharing参数的含义、取值和效果乃至评价都有比较具体的阐述。有兴趣的读者可以参见下面列表系列:

 

《浅谈cursor_sharing取值对SQL共享的影响》

http://space.itpub.net/17203031/viewspace-705196

http://space.itpub.net/17203031/viewspace-705289

 

 

SQL> show parameter cursor_shar

 

NAME                                 TYPE        VALUE

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

cursor_sharing                       string      EXACT

 

 

简单的说,cursor_sharing参数对使用绑定变量的SQL语句是没有效果和影响的。如果该参数设置为非EXACT,那些没有使用绑定变量的SQL语句的where后面取值都会被默认的替换,并且进行一定程度的执行计划共享。

 

但是,无论是cursor_sharing取值为familiar还是force,都存在其在游标共享问题的不合理性。特别是其对所有SQL执行计划处理的“一刀切”策略,是很有问题的。

 

在使用过程中,cursor_sharing也有各种问题,所以很快的大家就放弃了这种参数优化的思路。转回到书写绑定变量和避免bind peeking等方案上来。

 

进入11g之后,Adaptive Cursor Sharing技术的提出,意味着Oracle重新思考解决绑定变量的bind peeking问题。Cursor_sharing有传言在下一个版本中一些取值会去除。

 

笔者认为:cursor_sharing是一种不成熟的尝试。在一些特定场合下,可能存在使用的价值。但是作为数据架构师和开发人员,还是老老实实的写绑定变量稳妥。运维DBA在面对没有绑定变量的系统时候,也要慎用该参数。

 

5、优化参数3——db_file_multiblock_read_count

 

这个参数的作用,同Oracle进行物理IO的方式有关。Oracle进行的IO分为Logical IO和Physical IO两种。不严格的说:进行物理IO的次数比例越高,系统性能瓶颈问题出现的多。

 

但是进行IO也是有不同之处的。进行一次IO的数据块读取量不同,可能会影响到Optimizer特别是CBO的成本计算。

 

Oracle进行FTS(Full Table Scan)和Index Fast-Full Scan的时候,进行读取的数据块都是连续方式访问的。因为作为数据段Data Segment和索引段Index Segment,都是由一系列内部连续数据块组成的分区extent序列构成的。在进行这样的操作时,获取的数据块大都是相邻的。

 

如果我们进行一次物理IO时候,能够多获取到一些数据块,那么进行物理IO的绝对次数就会变少。这样相应的成本就会降低。

 

 

SQL> show parameter db_file_mu

 

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count        integer     16

 

 

SQL> select value, display_value from v$parameter where name='db_file_multiblock_read_count';

 

VALUE      DISPLAY_VA

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

16                                     16

 

 

默认情况下,笔者实验环境上的该参数为16,表示一次进行读取的时候最多读取到16个数据块。

 

我们首先回顾一下在默认情况下,进行FTS操作的成本计算。

 

 

 

 

SQL> explain plan for select * from t where wner='SYS';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 43272 |  3887K|   158   (3)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 43272 |  3887K|   158   (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OWNER"='SYS')

 

SQL> explain plan for select * from t where object_id between 10 and 50000;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 44865 |  4030K|   159   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 44865 |  4030K|   159   (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"<=50000 AND "OBJECT_ID">=10)

 

 

 

注意,两个进行FTS的执行计划,Oracle优化器“估算出”的成本值为158和159。如果我们调整db_file_multiblock_read_count参数,观察一下两个SQL的执行计划变化。

 

 

SQL> alter session set db_file_multiblock_read_count=300;

Session altered

 

SQL> select value, display_value from v$parameter where name='db_file_multiblock_read_count';

 

VALUE      DISPLAY_VA

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

128                                  128

 

 

我们尝试将这个参数设置为300,但是最后还是调整为最大值128。这个参数虽然可以设置,但是最后还要受到服务器体系结构和物理配置的影响。

 

此时两个SQL的执行计划如下。

 

 

SQL> explain plan for select * from t where wner='SYS';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 43300 |  3890K|   126   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 43300 |  3890K|   126   (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OWNER"='SYS')

 

SQL> explain plan for select * from t where object_id between 10 and 50000;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 44849 |  4029K|   127   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 44849 |  4029K|   127   (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"<=50000 AND "OBJECT_ID">=10)

 

13 rows selected

 

 

当我们调整该参数之后,进行FTS操作计算出的成本变小。说明在计算执行计划的过程中,如果db_file_multiblock_read_count参数设置变大,成本公式中成本值就会变小。

 

可以看出,该参数设置的较大,可以让FTS操作成本估算的结果变小。进而,最终的生成执行计划更倾向于FTS操作。

 

 

6、优化参数4——optimizer_mode

 

optimizer_mode也是Oracle早期使用的一个参数,主要是针对SQL执行计划生成的目标导向。默认情况下,该参数取值为all_rows,表明执行计划的生成是以最小成本消耗和吞吐量为标准。在all_rows的取值情况下,我们通常是对所有的结果集合进行一次性的处理操作。

 

all_rows相对应的optimizer_mode参数是first_rows系列取值。该取值要求优化器以快速响应fast response作为执行计划的生成依据。执行SQL的时候,Oracle会争取将结果集合先返回给前端。

 

在实际使用中,单表操作情况下不同的optimizer_mode取值在执行计划上没有什么差异。

 

我们首先准备实验数据环境。

 

 

SQL> create table t_obj as select * from dba_objects;

Table created

 

SQL> create table t_tables as select * from dba_tables;

Table created

 

SQL> create index idx_t_obj_cmp on t_obj(owner,object_name);

Index created

 

SQL> create index idx_t_tables_cmp on t_tables(owner,table_name);

Index created

 

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

PL/SQL procedure successfully completed

 

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

PL/SQL procedure successfully completed

 

 

当进行单表操作的时候,first_rows与all_rows取值差异不是很大。

 

--设置optimizer_mode参数为all_rows

SQL> alter session set optimizer_mode='all_rows';

Session altered

 

SQL> select value from v$parameter where name='optimizer_mode';

VALUE

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

ALL_ROWS

 

SQL> explain plan for select * from t_obj;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

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

|   0 | SELECT STATEMENT  |       | 50387 |  4576K|   157   (3)| 00:00:02 |

|   1 |  TABLE ACCESS FULL| T_OBJ | 50387 |  4576K|   157   (3)| 00:00:02 |

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

8 rows selected

 

--设置参数取值为optimizer_mode为first_rows

SQL> alter session set optimizer_mode='first_rows';

Session altered

 

SQL> select value from v$parameter where name='optimizer_mode';

VALUE

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

FIRST_ROWS

 

SQL> explain plan for select * from t_obj;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

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

|   0 | SELECT STATEMENT  |       | 50387 |  4576K|   157   (3)| 00:00:02 |

|   1 |  TABLE ACCESS FULL| T_OBJ | 50387 |  4576K|   157   (3)| 00:00:02 |

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

 

8 rows selected

 

 

单表操作下,执行计划是没有什么差异的。但是,在进行表连接操作的情况下,执行计划有很大差异。

 

当参数为all_rows的时候,一般连接Oracle都尽量使用hash join连接方式,不使用index连接键。

 

 

SQL> alter session set optimizer_mode='all_rows';

Session altered

 

SQL> explain plan for select * from t_tables a, t_obj b where a.owner=b.owner and a.table_name=b.object_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2309375240

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

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

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

|   0 | SELECT STATEMENT   |          |  1606 |   467K|   171   (4)| 00:00:03 |

|*  1 |  HASH JOIN         |          |  1606 |   467K|   171   (4)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T_TABLES |  1606 |   321K|    12   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T_OBJ    | 50387 |  4576K|   157   (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - access("A"."OWNER"="B"."OWNER" AND

              "A"."TABLE_NAME"="B"."OBJECT_NAME")

 

16 rows selected

 

 

虽然在连接键上有索引,Oracle使用hash join连接方式。Hash Join方式往往会忽视索引路径。

 

如果我们将参数设置为first_rows,也就是尽量将先获取的结果返回。Oracle会更加倾向与嵌套循环Nested Loop和索引连接Index Join。

 

 

SQL> alter session set optimizer_mode='first_rows';

Session altered

 

SQL> select value from v$parameter where name='optimizer_mode';

VALUE

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

FIRST_ROWS

 

SQL> explain plan for select * from t_tables a, t_obj b where a.owner=b.owner and a.table_name=b.object_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2368859676

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

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

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

|   0 | SELECT STATEMENT            |               |  1606 |   467K|  3229   (1

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ         |     1 |    93 |     2   (0

|   2 |   NESTED LOOPS              |               |  1606 |   467K|  3229   (1

|   3 |    TABLE ACCESS FULL        | T_TABLES      |  1606 |   321K|    12   (0

|*  4 |    INDEX RANGE SCAN         | IDX_T_OBJ_CMP |     1 |       |     1   (0

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

Predicate Information (identified by operation id):

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

   4 - access("A"."OWNER"="B"."OWNER" AND "A"."TABLE_NAME"="B"."OBJECT_NAME")

 

16 rows selected

 

 

根据原始的设计,all_rows取值适合于批作业、计算、选择等常规业务场景。而first_rows主要适合在Web/Windows页面分页显示场景。

 

 

在我们使用SQL的时候,默认还是将这个参数设置为all_rows,这样适应性更好,执行计划获取更好。如果一些个别需要要求first_rows,可以使用hint进行特殊设置。

 

 

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

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7646420