【優化】ALL_ROWS模式和FIRST_ROWS模式的適用場景

victorymoshui發表於2011-04-27
在CBO的優化模式下,我們可以使用optimizer_mode引數控制優化模式。主要有兩種模式,一種是ALL_ROWS模式,另外一種是FIRST_ROWS模式。

ALL_ROWS模式適用場景:希望優化程式給出一種儘快得到全部記錄的執行計劃,目標是增加系統的吞吐量。
FIRST_ROWS模式使用場景:希望優化程式給出一種可以迅速的得到第一行的執行計劃,目標是減少系統的響應時間。

兩種模式需要具體場景具體分析,比如常見的Web應用,很少有一次性得到全部記錄的情況,都是分多頁互動的響應操作者,因此預設的ALL_ROWS模式就不太適合了,應該考慮使用FIRST_ROWS模式進行優化。
又如,我們想要生成全部資料的報表,那麼預設的ALL_ROWS模式就比較的適合。

通過一個實驗看一下兩種優化模式下的執行計劃的不同之處。
1.預設情況下,資料庫採用ALL_ROWS模式。
sec@ora10g> show parameter optimizer_mode

NAME                TYPE                 VALUE
------------------- -------------------- -----------------
optimizer_mode      string               ALL_ROWS

2.建立千萬級別的測試表t,開啟autotrace,檢視一下預設ALL_ROWS模式下的執行計劃。
sec@ora10g> set autot trace explain
sec@ora10g> select t1.x, t2.x from t t1, t t2 where t1.x = t2.x and t1.owner='SEC';

Execution Plan
----------------------------------------------------------
Plan hash value: 2371815244

--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 99695 |  2823K|       | 43627   (1)| 00:08:44 |
|*  1 |  HASH JOIN            |      | 99695 |  2823K|  3408K| 43627   (1)| 00:08:44 |
|*  2 |   TABLE ACCESS FULL   | T    | 99695 |  2239K|       | 29985   (1)| 00:06:00 |
|   3 |   INDEX FAST FULL SCAN| PK_T |  9969K|    57M|       |  4871   (2)| 00:00:59 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."X"="T2"."X")
   2 - filter("T1"."OWNER"='SEC')

優化程式給出了一個快速獲得t表全部記錄的執行計劃,使用到了索引快速全掃描的方式執行,總的執行時間較快。

3.修改優化模式為FIRST_ROWS模式後,再次查詢其執行計劃。
sec@ora10g> alter session set optimizer_mode =first_rows;

Session altered.

sec@ora10g> select t1.x, t2.x from t t1, t t2 where t1.x = t2.x and t1.owner='SEC';

Execution Plan
----------------------------------------------------------
Plan hash value: 217223811

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 99695 |  2823K|   129K  (1)| 00:25:57 |
|   1 |  NESTED LOOPS      |      | 99695 |  2823K|   129K  (1)| 00:25:57 |
|*  2 |   TABLE ACCESS FULL| T    | 99695 |  2239K| 29985   (1)| 00:06:00 |
|*  3 |   INDEX UNIQUE SCAN| PK_T |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."OWNER"='SEC')
   3 - access("T1"."X"="T2"."X")

優化程式給出了一種快速獲得t表第一條記錄的執行計劃,使用到了索引唯一性掃描的方式執行,總的執行時間相對ALL_ROWS模式就長了許多。

4.參考一下Oracle 10g官方文件關於optimizer_mode引數的描述

OPTIMIZER_MODE

Property Description
Parameter type String
Syntax OPTIMIZER_MODE =

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

Default value all_rows
Modifiable ALTER SESSION,ALTER SYSTEM

OPTIMIZER_MODEestablishes the default behavior. for choosing an optimization approach for the instance.

Values:

  • first_rows_n

    The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the firstnrows (wheren= 1, 10, 100, 1000).

  • first_rows

    The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

  • all_rows

    The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).


5.小結

這種優化手段給我們的啟示是什麼?Oracle預設的優化模式並不一定是我們想要的,必須根據自己的系統特點細心的定製。
Oracle的自動化程式越來越快,這就給一些DBA一種普遍的誤解,認為在資料庫層面上基本上不用做過多的優化調整,只要按照Oracle的自動化策略走就可以了。這種想法是不正確的。越是自動化,其優化細節就隱藏的越深,越是要靜下心來深入的探索和調整。

Good luck.

-- The End --

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9390331/viewspace-693831/,如需轉載,請註明出處,否則將追究法律責任。

相關文章