ANBOB™

Optimizer_mode是告诉query optimizer默认的优化途径，这个参数有三个参数，all_rows,first_rows_n,first_rows

• all_rows: The query optimizer uses a cost-based approach for all SQL statements and optimizes with a goal of best throughput (minimum resource cost to complete the entire statement).
• first_rows_n: The query optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, or 1000).
• first_rows: The query optimizer uses a mix of costs and heuristics to find the best plan for quickly returning the first few rows.

all_rows是默认参数，first_rows只是为了向前兼容，ORACLE建议你平时用时使用first_rows_N,N的值越大就越像ALL_ROWS靠近。

你可以在分别instance,session,sql级指定优化级别，sql级使用hint，optimizer_mode不同也会生成不同的执行计划，有时对相同的sql为什么有的用索引有的不用，为什么有的是NL JOIN,为什么有的是HA JOIN

看个测试

create table bigtab pctfree 0 as 
   select rownum id,rpad('anbob'||rownum,4000,'0') rname ,lpad('anbob'||rownum,4000,'0') lname from dual connect by ;
rownum<100000;

anbob@ANBOB> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS
	  
anbob@ANBOB> select * from bigtab
  2  left join obj on bigtab.id=obj.object_id
  3  where obj.object_id between 1000 and 2000;

984 rows selected.
Elapsed: 00:00:20.96

Execution Plan
----------------------------------------------------------
Plan hash value: 2217950213

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   940 |  7439K| 56128   (1)| 00:11:14 |
|   1 |  NESTED LOOPS                |            |       |       |            |          |
|   2 |   NESTED LOOPS               |            |   940 |  7439K| 56128   (1)| 00:11:14 |
|*  3 |    TABLE ACCESS FULL         | BIGTAB     |  1002 |  7834K| 54399   (1)| 00:10:53 |
|*  4 |    INDEX RANGE SCAN          | IDX_OBJ_ID |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| OBJ        |     1 |    97 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
anbob@ANBOB> alter session set optimizer_mode='ALL_ROWS';
Session altered.

anbob@ANBOB> show parameter optimizer_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

anbob@ANBOB> l
  1  select * from bigtab
  2  left join obj on bigtab.id=obj.object_id
  3* where obj.object_id between 1000 and 2000
anbob@ANBOB> /

984 rows selected.

Elapsed: 00:00:20.98

Execution Plan
----------------------------------------------------------
Plan hash value: 126369464

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   940 |  7439K| 54425   (1)| 00:10:54 |
|*  1 |  HASH JOIN                   |            |   940 |  7439K| 54425   (1)| 00:10:54 |
|   2 |   TABLE ACCESS BY INDEX ROWID| OBJ        |   941 | 91277 |    26   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_ID |   941 |       |     4   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | BIGTAB     |  1002 |  7834K| 54399   (1)| 00:10:53 |
-------------------------------------------------------------------------------------------

anbob@ANBOB> alter session set optimizer_mode='FIRST_ROWS';
Session altered.

anbob@ANBOB> select /*+leading(bigtab)*/* from bigtab
  2  left join obj on bigtab.id=obj.object_id
  3  where obj.object_id between 1000 and 2000;

984 rows selected.

Elapsed: 00:00:20.87

Execution Plan
----------------------------------------------------------
Plan hash value: 2217950213

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   940 |  7439K| 56128   (1)| 00:11:14 |
|   1 |  NESTED LOOPS                |            |       |       |            |          |
|   2 |   NESTED LOOPS               |            |   940 |  7439K| 56128   (1)| 00:11:14 |
|*  3 |    TABLE ACCESS FULL         | BIGTAB     |  1002 |  7834K| 54399   (1)| 00:10:53 |
|*  4 |    INDEX RANGE SCAN          | IDX_OBJ_ID |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| OBJ        |     1 |    97 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

即使改变了join table order，也一样first_rows更倾向与NL JOIN;

anbob@ANBOB> alter session set optimizer_mode='FIRST_ROWS_100';
Session altered.

anbob@ANBOB> select * from bigtab
  2  left join obj on bigtab.id=obj.object_id
  3  where obj.object_id between 1000 and 2000 and rownum<10;

9 rows selected.

Elapsed: 00:00:00.13

Execution Plan
----------------------------------------------------------
Plan hash value: 1237295455

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     9 |   141K|  5891   (1)| 00:01:11 |
|*  1 |  COUNT STOPKEY                |            |       |       |            |          |
|*  2 |   HASH JOIN                   |            |   101 |  1589K|  5891   (1)| 00:01:11 |
|   3 |    TABLE ACCESS BY INDEX ROWID| OBJ        |   941 | 91277 |    26   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_OBJ_ID |   941 |       |     4   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | BIGTAB     |   110 |   860K|  5865   (1)| 00:01:11 |
--------------------------------------------------------------------------------------------
anbob@ANBOB> alter session set optimizer_mode='FIRST_ROWS_1';
Session altered.

anbob@ANBOB> /
9 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 3152508579

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     2 | 16208 |   117   (1)| 00:00:02 |
|*  1 |  COUNT STOPKEY                |            |       |       |            |          |
|   2 |   NESTED LOOPS                |            |       |       |            |          |
|   3 |    NESTED LOOPS               |            |     2 | 16208 |   117   (1)| 00:00:02 |
|*  4 |     TABLE ACCESS FULL         | BIGTAB     |     4 | 32028 |   110   (0)| 00:00:02 |
|*  5 |     INDEX RANGE SCAN          | IDX_OBJ_ID |     1 |       |     1   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| OBJ        |     1 |    97 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

FIRST_ROWS修改cbo，只是一种理想，返回第一行越快越好(ASAP),对于C/S环境，每次查询只返回几行来说它是不错的选择，比如TOAD,和SQLPLUS,同样一个查询你可能发现在TOAD运行很快就返回了记录（准确说是部分记录）其实它也是一直在fetch数据只不过in background，但SQLPLUS还在一直的滚动,first_rows_n，更倾向与index scan 和NS JOIN,适合用于OLTP。

ALL_ROWS对于INDEX FULL SCAN,和FULL TABLE SCAN来说是不错的，它是利用最大吞吐量返回更多的数据，在大数据集操作时更喜欢HA JOIN,更适合用于OLAP.

打赏

微信扫一扫，打赏作者吧～