[20210111]优化模式optimizer_mode.txt

--//昨天终于看完崔华<基于Oracle的SQL优化>.里面提到optimizer_mode设置FIRST_ROWS_N要特别注意.
--//通过例子说明:

1.环境:
SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
SYS@book> set verify off
SYS@book> @ tpt/pvalid.sql optimizer_mode
Display valid values for multioption parameters matching "optimizer_mode"...
  PAR# PARAMETER       ORD VALUE                          DEFAULT
------ --------------- --- ------------------------------ -------
  2027 optimizer_mode    1 RULE
       optimizer_mode    2 CHOOSE
       optimizer_mode    3 ALL_ROWS
       optimizer_mode    4 FIRST_ROWS
       optimizer_mode    5 FIRST_ROWS_1
       optimizer_mode    6 FIRST_ROWS_10
       optimizer_mode    7 FIRST_ROWS_100
       optimizer_mode    8 FIRST_ROWS_1000
8 rows selected.

SCOTT@book> show parameter optimizer_mode
NAME           TYPE    VALUE
-------------- ------- --------
optimizer_mode string  ALL_ROWS

SCOTT@book> alter session set optimizer_mode=FIRST_ROWS_1;
Session altered.

SCOTT@book> alter session set statistics_level = all;
Session altered.

SCOTT@book> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a2dk8bdn0ujx7, child number 1
-------------------------------------
select * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     2 (100)|          |     14 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |    38 |     2   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1

--//你可以发现 E-Rows=1,这样导致多表连接判断错误,选择不合理的执行计划.

SCOTT@book> alter session set optimizer_mode=ALL_ROWS;
Session altered.

SCOTT@book> select * from emp;
...


SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a2dk8bdn0ujx7, child number 2
-------------------------------------
select * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |     14 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1

--//E-rows=14. 这样判断就是准确的.

3.继续测试:
select * from dept,emp where dept.deptno=emp.deptno;

--//optimizer_mode=FIRST_ROWS_1看到的执行计划:
Plan hash value: 3625962092
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     3 (100)|          |     14 |00:00:00.01 |      25 |
|   1 |  NESTED LOOPS                |         |      1 |      1 |    58 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |      25 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |    58 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |      11 |
|   3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |    38 |     2   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     14 |      1 |       |     0   (0)|          |     14 |00:00:00.01 |       4 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     14 |      1 |    20 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |      14 |
----------------------------------------------------------------------------------------------------------------------------------

--//optimizer_mode=ALL_ROWS看到的执行计划:
Plan hash value: 844388907
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      10 |      1 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |     14 |   812 |     6  (17)| 00:00:01 |     14 |00:00:00.01 |      10 |      1 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |    80 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       4 |      1 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |      1 |       |       |          |
|*  4 |   SORT JOIN                  |         |      4 |     14 |   532 |     4  (25)| 00:00:01 |     14 |00:00:00.01 |       6 |      0 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//你可以发现optimizer_mode=FIRST_ROWS_N的情况,更加趋向于nested loop.

--//在遇到一些特殊情况下看执行计划的outlin.
>@ dpc '' outline
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      FIRST_ROWS(1)
      ~~~~~~~~~~~~~~~
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "EMP"@"SEL$1" "DEPT"@"SEL$1")
      USE_NL(@"SEL$1" "DEPT"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "DEPT"@"SEL$1")
      END_OUTLINE_DATA
  */

4.总结
--//总之这个细节需要注意.一般会在用户登录时通过触发器修改这个参数设置.