[20210111]优化模式optimizer_mode.txt

来源：这里教程网时间：2026-03-03 16:21:45 作者：

[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.总结 --//总之这个细节需要注意.一般会在用户登录时通过触发器修改这个参数设置.