oracle性能参数(optimizer_mode)测试一

最新推荐文章于 2021-04-05 16:07:50 发布

cijieran2214

最新推荐文章于 2021-04-05 16:07:50 发布

阅读量240

点赞数

（一）参数基础知识

1、optimizer_mode参数定义：

当SQL语句执行时，此参数定义数据库选择那种优化方法。查询优化器会依据它的值决定哪个是最高效的执行计划。

此参数对于需要全部执行的SQL语句是没有意义的，比如insert 语句。

不同oracle版本可选值：

10.2 { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows } default: all_rows

10.1 { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows } default: all_rows

09.2 { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule} default: choose

2、参数值的意义:

rule 基于规则的优化法则生成SQL执行计划。

choose 依据是否有对象统计信息来决定采用哪种优化模式，当至少有一个对象有统计信息时，

采用all_rows的参数值；当没有统计信息时，采用rule的参数值。

first_rows_n (where n=1,10,100,1000)

采用基于代价的优化模式，以最快响应时间反回前n行作为SQL执行计划高效的目标。

first_rows 使用最小的代价查询出前几行最为高效SQL执行计划的选择目标。

all_rows 使用最少的资源消耗为目标，来选择高效的SQL执行计划。

3、版本的变化：

参数可选值choose和rule已经不再被oracle10及更高版本支持。

4、在哪个级别设定优化模式：

A、Instance级别我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS。或

者执行语句ALTER SYSTEM SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS scope=both来设定。

B、Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。

C、语句级别用Hint（/*+ ... */）来设定。

（二）下面是试验部分：

1、测试的环境：

OS： AIX Version 5.3

ORACLE: Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi

2、影响测试的前提参数，这几个参数值保持不变：

optimizer_index_cost_adj=10

db_file_multiblock_read_count=64

cpu_count=18

optimizer_index_caching=90

sga_max_size=34G

sga_target=34G

pga_aggregate_target=20G

3、创建测试需要的两个表：

--drop table t;

--drop table d;

----创建t表有500万条数据

SQL> CREATE TABLE t AS SELECT rownum AS id, rpad('addfa',100,'kj#dff214123dopio[p248jfkfk') AS pad FROM dual CONNECT BY level <= 5000000;

----创建d表有5万条数据

SQL> CREATE TABLE d AS SELECT 200000+rownum AS id, rpad('addfa',100,'kj#dff214123dopio[p248jfkfk')

AS pad FROM dual CONNECT BY level <= 50000;

----给两表加索引

SQL> create index ind_t_id on t(id);

SQL> create index ind_d_id on d(id);

----收集对象统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'t');

SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'d');

SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';

SQL> col table_name format a5;

SQL> col num_rows format 999999999999

SQL> col last_analyzed format A20;

SQL> select table_name,num_rows,last_analyzed from user_tables;

TABLE NUM_ROWS LAST_ANALYZED

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

D 50000 20110108 19:32:49

T 4996653 20110108 19:32:40

4、当从500万中查询出5万数据时（查询结果集小于总数据量的10%，这个10%由参数值optimizer_index_cost_adj决定），参数值all_rows，first_rows_n会有不一样的执行计划和查询结果：

1）先看all_rows优化原则下，sql的执行计划：

SQL> select /*+all_rows*/* from t where exists (select id from d where t.id=d.id);

50000 rows selected.

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

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

| 0 | SELECT STATEMENT | | 50000 | 5419K| 2519 (1)| 00:00:31 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 106 | 1 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 50000 | 5419K| 2519 (1)| 00:00:31 |

| 3 | SORT UNIQUE | | 50000 | 244K| 11 (0)| 00:00:01 |

| 4 | INDEX FULL SCAN | IND_D_ID | 50000 | 244K| 11 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | IND_T_ID | 1 | | 1 (0)| 00:00:01 |

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

执行计划注解：

一个大表和一个小表查询，先用小表d中的索引数据放到大表t中，走t表索引查询显示出来，嵌套循环连接，这个执行计划可以有最少的资源消耗得到所有结果。

2）下面是first_rows_n原则下sql的执行计划：

SQL> select /*+first_rows(1)*/* from t where exists (select id from d where t.id=d.id);

50000 rows selected.

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

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

| 0 | SELECT STATEMENT | | 1 | 111 | 3 (0) | 00:00:01 |

| 1 | NESTED LOOPS SEMI | | 1 | 111 | 3 (0) | 00:00:01 |

| 2 | TABLE ACCESS FULL | T | 4996K| 505M| 2 (0) | 00:00:01 |

|* 3 | INDEX RANGE SCAN | IND_D_ID | 500 | 2500 | 1 (0) | 00:00:01 |

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

执行计划注解：

T表的第一条数据取出，放到d表中走d表索引查询是否存在，存在则显示t表此条数据，这样的执行计划被认为获得第一条数据最快。

两个执行计划：

大表和小表连接，all_rows优化原则时，资源占用少，sql执行时间短

大表和小表连接，first_rows_n优化原则时，资源占用多，sql执行时间长

5、当从500万中查询出100万数据时（查询结果集大于总数据量的10%，这个10%由参数值optimizer_index_cost_adj决定），参数值all_rows，first_rows_n也会有不一样的执行计划和查询结果：

1）给b表增加数据到100万条：

SQL> insert into d SELECT 250000+rownum AS id, rpad('addfa',100,'kj#dff214123dopio[p248jfkfk') AS pad FROM dual CONNECT BY level <= 950000;

SQL> commit;

SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'d'); --表分析

SQL> select table_name,num_rows,last_analyzed from user_tables;

TABLE NUM_ROWS LAST_ANALYZED

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

D 1001846 20110108 20:07:37

T 4996653 20110108 19:32:40

1）先看all_rows优化原则下，sql的执行计划：

SQL> select /*+all_rows*/* from t where exists (select id from d where t.id=d.id);

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

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

| 0 | SELECT STATEMENT | | 999K| 106M| |13972 (1)| 00:02:48 |

| 1 | MERGE JOIN SEMI | | 999K| 106M| |13972 (1)| 00:02:48 |

| 2 | TABLE ACCESS BY INDEX ROWID| T |4996K| 505M| |10446 (1)| 00:02:06 |

| 3 | INDEX FULL SCAN |IND_T_ID|5219K| | | 1235 (1) | 00:00:15 |

|* 4 | SORT UNIQUE | |1001K| 5870K|23M| 3527 (3) | 00:00:43 |

| 5 | INDEX FULL SCAN |IND_D_ID|1001K| 5870K| | 206 (1) | 00:00:03 |

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

执行计划注解：

一个大表和一个中等表连接查询，中表d中的索引数据排序替重（只获取了索引块），t表索引中的数据直接拿出（只获取了索引块），两表的索引数据做“排序合并”连接，把满足条件的数据通过t表索引取出。这个执行计划可以有最少的资源消耗得到所有结果。

2）下面是first_rows_n原则下sql的执行计划：执行计划没有变化。

SQL> select /*+first_rows(1)*/* from t where exists (select id from d where t.id=d.id);

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

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

| 0 | SELECT STATEMENT | | 2 | 224 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS SEMI | | 2 | 224 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | T | 4996K| 505M| 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IND_D_ID | 200K| 1174K| 1 (0)| 00:00:01 |

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

两个执行计划：

大表和中等表连接，all_rows优化原则时，资源占用少，sql执行时间短

大表和小表连接，first_rows_n优化原则时，资源占用多，sql执行时间长

6、当从500万中查询出400万数据时（查询结果集大于总数据量的10%，这个10%由参数值optimizer_index_cost_adj决定），参数值all_rows，first_rows_n也会有不一样的执行计划和查询结果：

1）给b表增加数据到300万条：

SQL> insert into d SELECT 120000+rownum AS id, rpad('addfa',100,'kj#dff214123dopio[p248jfkfk') AS pad FROM dual CONNECT BY level <= 3000000;

SQL> commit;

SQL> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'d');

SQL> select table_name,num_rows,last_analyzed from user_tables;

TABLE NUM_ROWS LAST_ANALYZED

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

D 4000000 20110108 20:43:28

T 4996653 20110108 19:32:40

2）先看all_rows优化原则下，sql的执行计划：这个执行计划是和first_rows_n相同的的。

SQL> select /*+all_rows*/* from t where exists (select id from d where t.id=d.id);

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

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

| 0 | SELECT STATEMENT | | 2998K| 320M| 14288 (2) | 00:02:52 |

| 1 | NESTED LOOPS SEMI | | 2998K| 320M| 14288 (2) | 00:02:52 |

| 2 | TABLE ACCESS FULL| T | 4996K| 505M| 14220 (2) | 00:02:51 |

|* 3 | INDEX RANGE SCAN | IND_D_ID | 2400K | 13M| 1 (0) | 00:00:01 |

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

2）下面是first_rows_n原则下sql的执行计划：执行计划没有变化。

SQL> select /*+first_rows(1)*/* from t where exists (select id from d where t.id=d.id);

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

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

| 0 | SELECT STATEMENT | | 2 | 224 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS SEMI | | 2 | 224 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | T | 4441K| 448M| 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IND_D_ID | 2000K| 11 M | 1 (0)| 00:00:01 |

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

两个执行计划：

大表和中等表连接，all_rows优化原则时，资源占用少，sql执行时间短

大表和小表连接，first_rows_n优化原则时，资源占用多，sql执行时间长

以上试验说明，无论在什么情况下，优化原则first_rows_n的资源耗用高于 all_rows。

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

转载于:http://blog.itpub.net/23652549/viewspace-683666/

cijieran2214

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle性能参数(optimizer_mode)测试一

（一）参数基础知识 1、optimizer_mode参数定义：当SQL语句执行时，此参数定义数据库选择那种优化方法。查询优化器会依据它的值决定哪个是最高效的执行计划。此参数对于需要全部执行的SQ...
复制链接

扫一扫