Oracle Tuning (Oracle 效能調整)的一些總結(轉)2

●強迫開發人員調整自己的SQL
很多開發人員錯誤的認為，他們的任務就是編寫SQL語句然後從資料庫中獲得正確的資料。但是實際上編寫出SQL語句只是開發人員一半的工作，在一個成功的Oracle應用系統中，會要求開發人員的SQL語句採用最優化的方式訪問資料庫，並且保證SQL語句的執行計劃在新的SQL之間的可移植性。
令人驚訝的是，在許多Oracle應用系統中都不怎麼考慮具體SQL語句的執行計劃，認為CBO是很智慧的，無論如何都可以為我們提供最好的SQL語句執行計劃。
同一個查詢在SQL語句中可能有不同方式的寫法，而每一種寫法都可能有不同的執行計劃。觀察下面的例子，每一個查詢的結果都是一樣的，但是執行計劃卻相去甚遠。

-- 使用了不正確的子查詢
select
book_title
from
book
where
book_key not in (select book_key from sales);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=64)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=64)
3 1 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=25)


-- 使用了兩張表的外連線
select
book_title
from
book b,
sales s
where
b.book_key = s.book_key(+)
and
quantity is null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)

1 0 FILTER
2 1 FILTER
3 2 HASH JOIN (OUTER)
4 3 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=1280)
5 3 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=1800)


-- 使用了三個正確的子查詢
select
book_title
from
book
where
book_title not in (
select
distinct
book_title
from
book,
sales
where
book.book_key = sales.book_key
and
quantity > 0);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=59)
3 1 FILTER
4 3 NESTED LOOPS (Cost=6 Card=1 Bytes=82)
5 4 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=90)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1)
7 6 INDEX (UNIQUE SCAN) OF 'PK_BOOK' (UNIQUE)

我們看到，正確的SQL語句寫法產生的執行計劃是如此的不同。明智的開發人員知道如何去編寫能夠產生最好執行計劃的SQL語句，明智的Oracle應用系統也會主動訓練開發人員去編寫最有效的SQL語句。

下面是一些幫助開發人員優化SQL語句的技巧：
1、 使用 autotrace 和 TKPROF 功能去分析SQL語句的執行計劃；
2、 保證所有生產環境中的SQL語句都是在測試環境中經過優化的；
3、 制定一個效能優化的標準，而不是隻要求開發人員編寫出最快的SQL語句。根據這種標準，好的開發人員應該能夠寫出最有效的SQL語句。

●謹慎管理CBO統計資料
成功的Oracle系統會謹慎管理他們的CBO統計資料，以保證CBO在測試環境和生產環境中以同樣的方式工作。一個聰明的DBA會在得到高質量的CBO統計資料以後，把這些統計資料移植到測試環境中，這樣SQL語句的執行計劃在測試環境和生產環境中就是一樣的了。

對DBA來說，一個重要的工作就是收集和釋出CBO統計資料，並隨時保持一套當前執行環境的最精確的統計資料。在一些情況下，可能會有不止一套的優化統計資料。比如，對OLTP執行的最好的統計資料可能對資料倉儲執行卻不是最好的，在這種情況下，DBA就需要保持兩套統計資料，並根據不同的執行條件匯入系統。

可以使用 dbms_stats 包中的 export_system_stats 儲存過程來完成CBO統計資料的匯出。下面的例子中，我們把當前CBO統計資料匯出到一張名叫 stats_table_oltp的表中。
dbms_stats.export_system_stats(‘stats_table_oltp’)

匯出以後，我們就可以把這張表拷貝到別的例項中，當系統的執行模式改變以後，使用 dbms_stats 包中的 import_system_stats 儲存過程來完成CBO統計資料的匯入。
dbms_stats.import_system_stats(‘stats_table_oltp’)

●千萬不要隨便改動CBO引數的值
改動CBO相關引數的值是非常危險的，因為一個小小的改動可能就會對整個系統的執行效能帶來極大的負面影響，只有在經過嚴格的系統測試以後才能改動這些引數的值。可能帶來極大影響的引數值包括：optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching。而其他引數，比如 hash_area_size , sort_area_size，引數值的改變就不是那麼危險了，可以在會話級進行改變以幫助CBO優化查詢。

●保證靜態的執行計劃
成功的CBO應用會通過謹慎管理統計資料來鎖定SQL執行計劃，同時保證儲存的優化計劃的穩定性，或者在具體的SQL語句中加入一些細節上的提示。
記住：重新分析一個系統的統計資料，可能會導致成千上萬的SQL語句改變其執行計劃。許多Oracle應用系統要求所有的SQL語句在測試環境中經過驗證，保證在功能上和生產環境是一致的。


2.4.4 CBO的思考
儘管我們已經對CBO的不少細節有了瞭解，但是由於隨著Oracle新版本的不斷推出，CBO變得越來越強大，同時也越來越複雜，我們仍然有許多關於CBO的知識需要學習。
下面是一些關於CBO調整的提綱性的建議，供準備進行CBO調整的DBA們思考。

●DBA可以提供一些Oracle引數的配置對CBO進行控制，但是隻能在有限的環境下謹慎的改變這些引數；

●CBO依靠統計資料來產生SQL語句的優化的執行計劃，可以通過 dbms_stats 包來分析、產生統計資料；

●DBA們的一項重要任務就是收集、管理CBO統計資料，這些資料可以被收集、儲存，也可以在相關的例項中進行移植，以保證執行計劃的連貫性。

●在沒有使用 export_system_stats 儲存過程匯出原來的統計資料以前，重新對系統的統計資料進行分析是十分危險的，因為成千上萬的SQL語句的執行計劃將可能全部改變，而你卻不能恢復原來的 SQL效能。只有在系統的資料發生巨大變化時，才可能需要對整個系統的統計資料進行重新分析。


本小節是關於CBO的一些技術討論，原文來自 Donald K. Burleson 在OTN上的一篇文章，具體URL路徑是：
http://otn.oracle.com/oramag/webcolumns/2003/techarticles/burleson_cbo_pt1.html


注：以上內容來源http://www.cnblogs.com/binary/archive/2005/04/02/131033.html 葛巨集賓的專欄

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