[20210111]優化模式optimizer_mode.txt
[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.總結
--//總之這個細節需要注意.一般會在使用者登入時通過觸發器修改這個引數設定.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2748884/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 效能優化模式優化模式
- [譯] 優化 Go 的模式優化Go模式
- 【譯】優化Go的模式優化Go模式
- 淺談optimizer_mode優化器模式優化模式
- 如何利用策略模式優化表單驗證模式優化
- 利用策略模式優化過多 if else 程式碼模式優化
- 中介模型以及優化查詢以及CBV模式模型優化模式
- 【優化】ALL_ROWS模式和FIRST_ROWS模式的適用場景優化模式
- 前端效能優化（JS/CSS優化，SEO優化）前端優化JSCSS
- 使用策略模式和簡單工廠模式重寫支付模組(二)-優化$request模式優化
- 「GAN優化」什麼是模式崩潰，以及如何從優化目標上解決這個問題優化模式
- 效能優化案例-SQL優化優化SQL
- MSSQL優化之索引優化SQL優化索引
- CUDA優化之指令優化優化
- Oracle9i, 10g 優化模式 OPTIMIZER_MODEOracle優化模式
- SpringBoot自定義初始化Bean+HashMap優化策略模式實踐Spring BootBeanHashMap優化模式
- Android效能優化----卡頓優化Android優化
- 資料庫優化 - SQL優化資料庫優化SQL
- 【前端效能優化】vue效能優化前端優化Vue
- 前端效能優化 --- 圖片優化前端優化
- sql優化之邏輯優化SQL優化
- [效能優化]DateFormatter深度優化探索優化ORM
- SQL優化：limit分頁優化SQL優化MIT
- (mysql優化-3) 系統優化MySql優化
- MySQL 效能優化之索引優化MySql優化索引
- MySQL優化-安裝配置優化MySql優化
- Web效能優化：圖片優化Web優化
- MySQL 效能優化之SQL優化MySql優化
- 記一次使用策略模式優化程式碼的經歷模式優化
- 為SSD程式設計（5）：訪問模式和系統優化程式設計模式優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- Android 效能優化 ---- 啟動優化Android優化
- 效能優化(二) UI 繪製優化優化UI
- hive優化-資料傾斜優化Hive優化
- 效能優化｜Tomcat 服務優化優化Tomcat