Sql性能調優

一·、前言：這篇博文內容非原創，是我們公司的架構師給我們做技術培訓的時候講的內容，我稍微整理了下，借花獻佛。這篇博文只是做一個大概的科普介紹，畢竟SQL優化的知識太大了，幾乎可以用一本書來介紹。另外，博主對SQL優化也是剛剛接觸，也有很多不了解的地方，說的不對的地方，還請大家指正，共勉！

二、oracle伺服器，所謂oracle伺服器指的是一個資料庫管理系統，它包括一個oracle實例（動態）和一個oracle資料庫（靜態）。

oracle實例是一個運行的概念，提供了一種訪問資料庫的方式，由SGA和一些後台服務進程組成，DBWn PMON CKPT LGWR SMON是必備的後台進程，而ad queue，rac，shared server，ad replication則是可選的。連接到oracle實例有三種途徑:

1、如果用戶登陸到運行oracle實例的操作系統上，則通過進程間通信進行訪問

2、C/S結構訪問

3、三層結構

oracle資料庫是一個被統一處理的數據的集合，從物理角度來說包括三類文件，數據文件、控制文件、重做日誌文件。

PMON監控其他後台進程，並且在伺服器進程或者轉發器進程異常終止之後執行恢復。pmon負責清理資料庫的buffer cache，並且釋放客戶端進程使用的資源。比如說pmon重置當前活動的事務表，釋放不需要的locks，清理進程id（隱式回滾）

SMON負責系統級別的清理工作

1.執行實例恢復。

2.恢復異常的transaction（實例恢復期間 file or tablespace被置為offline狀態），smon會在他們置為online的時候執行恢復。

3.清理不使用的臨時segments。比如當創建index的時候需要分配臨時extent，如果操作失敗，smon負責清理這些臨時空間。

4.在使用字典管理表空間的時候合併連續的空閑extent。smon為定期監控。其他進程如果需要的話也會通知smon。

Database Writer Process (DBWn)負責將更改的buffer 從db buffer cache中寫到datafile中去，通過一個dbwn進程（dbw0）就足夠了，但是也可以配置更多額外的dbwr進程，它可以提升頻繁更改的資料庫系統的性能。當然額外的dbw進程對於單處理器系統是沒有任何用處的。

Log Writer Process (LGWR)管理這redo log buffer。lgwr寫buffer中連續的部分到online redo log 中。因為分離了更改資料庫buffer的任務：dbwn散列寫buffer到disk中，執行快速的順序寫到redo，所以資料庫提升了性能。

1.用戶提交了一個事務。

2. redo log switch 發生

3. 從上一次lgwr寫操作開始已經過去了3秒

4. redo log buffer 三分之一滿或者已經存儲了1mb的數據量

5. dbwn必須寫更改的數據到磁碟上面。

CKPT更新控制文件以及數據文件頭部的檢查點信息，並且給dbwn信號去寫數據塊到磁碟上面。檢查點信息包括：檢查點位置，scn，恢復時開始的redo log 位置，類似這樣的信息。

Recoverer Process (RECO)在分散式資料庫中，reco進程自動的解決分散式事務發生錯誤的情況。

三、分析語句階段優化

硬解析:SQL語句從用戶進程提交到oracle，經過分析裝載到共享SQL區域（shared pool）。如果SQL語句不在shared pool，需要進行語句解析，即硬解析。

軟解析:如果SQL語句在shared pool，就可以直接進入執行階段。

優化技巧1:語法分析需要耗費很多資源，要盡量避免進行語法分析，即硬解析。

優化技巧2:即軟解析時，當Shared pool沒有空間時，oracle會根據LRU演算法(最近最少使用頁面置換演算法)更新SQL區域，所以適當增加shared_pool,可以存放更多解析後的SQL來提高效率。

Oracle Optimizer(查詢優化器)：是Oracle在執行SQL之前分析語句的工具,Oracle在執行一個SQL之前,首先要分析一下語句的執行計劃,然後再按執行計划去執行，主要有以下兩種方式:

• RBO(rule-base optimizer):優化器遵循Oracle內部預定的規則,句法驅動和數據字典驅動。
• CBO(cost-based optimizer):依據語句執行的代價，主要指對CPU和內存的佔用，優化器在判斷是否使用CBO時，要參照表和索引的統計信息統計表驅動，統計信息要在對錶做analyze後才會有。

優化技巧3:Oracle8及以後版本，推薦用CBO方式，Oracle10G此功能已經很強大。

Oracle優化器的優化模式主要有五種：

• Choose：默認模式。根據表或索引的統計信息，如果有統計信息，則使用CBO方式；如果沒有統計信息，相應列有索引，則使用RBO方式。
• Rule：基於規則優化，忽略任何統計信息
• First rows：與Choose類似。不同的是如果表有統計信息，它將以最快的方式返回查詢結果，以獲得最佳響應時間。
• First_rows_n：與Choose類似。不同的是如果表有統計信息，它將以最快的方式返回查詢的前幾行，以獲得最佳響應時間。
• All rows：完全基於CBO的模式。當一個表有統計信息時，以最快方式返回表所有行，以獲得最大吞吐量。沒有統計信息則使用RBO方式。

Oracle Optimizer 運行級別怎麼修改？

• Instance級:修改啟動參數在init<SID>.ora文件中設定OPTIMIZER_MODE，需要資料庫重啟
• Session級:（JDBC或者Hibernate或者一次連接），通過alter session set optimizer_mode = value修改，忽略instance級
• Statement級:通過在SQL語句中加如Hint(隱語)實現,表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,忽略instance級和session級

eg:SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO="SCOTT"; ……來設定

DBMS_STATS包工具做CBO代價分析

DBMS_STATS：dbms_stats包下面一共有40多個存儲過程. 對執行計劃的生成非常重要。常見的有：

分析資料庫（包括所有的用戶對象和系統對象）：gather_database_stats

分析用戶所有的對象（包括表、索引、簇）：gather_schema_stats

分析表：gather_table_stats

分析索引：gather_index_stats

刪除資料庫統計信息：delete_database_stats

刪除用戶方案統計信息：delete_schema_stats

刪除表統計信息：delete_table_stats

刪除索引統計信息：delete_index_stats

刪除列統計信息：delete_column_stats

設置表統計信息：set_table_stats

設置索引統計信息：set_index_stats

設置列統計信息：set_column_stats

可以查看錶 DBA_TABLES來查看錶是否與被分析過，如：

SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES

通常使用的比較多的主要是DBMS_STATS.GATHER_TABLE_STATS和 DBMS_STATS.GATHER_INDEX_STATS。

使用步驟:

1、首先創建一個分析表，該表是用來保存之前的分析值。

SQL> begin

2 dbms_stats.create_stat_table(ownname => "scott",stattab => "STAT_TABLE");

3 end;

4 /

2、分析表信息。可以參考這篇博客

exec dbms_stats.gather_schema_stats(

ownname => "SCOTT",

options => "GATHER AUTO",

estimate_percent => dbms_stats.auto_sample_size,

method_opt => "for all columns size repeat",

degree => 15

)

3、將執行計劃導入到STAT_TABLE中

exec dbms_stats.export_schema_stats(ownname => "scott",stattab => "stat_table") ;

4、查看執行計劃表

select * from stat_table;

四、執行計劃階段優化

全表掃描(Full Table Scans)

Oracle讀取表中所有的行，並檢查每一行是否滿足語句的WHERE限制條件,採用多塊讀的方式使一次I/O能讀取多塊數據塊，而不是只讀取一個數據塊，這極大的減少了I/O總次數，提高了系統的吞吐量，所以利用多塊讀的方法可以十分高效地實現全表掃描，注意,只有全表掃描才能使用多塊讀的操作。

優化技巧4:通過設置db_block_multiblock_read_count和db_block_size來適當增加一次I/O可讀的數據塊。

優化技巧5:避免使用select * from 減少物理讀，邏輯讀（* 要走系統字典表，查看這張表有哪些欄位）,最好制定需要返回的欄位。

優化技巧6:較小的表使用全表掃描，效率更高；較大的表應避免全表掃描，除非涉及全表記錄10%以上的查詢；避免給記錄數少的表建立索引，避免索引開銷。

優化技巧7:指定過濾謂詞 where，儘可能縮小查詢範圍（能過濾掉大部分記錄的欄位應該放在右邊,因為sql語句是從右至左執行的）。

通過ROWID的表存取(Table Access by ROWID)

ROWID記錄了記錄行所在的數據文件、數據塊以及行在該塊中的位置，所以通過ROWID來存取數據可以快速定位到目標數據上，可以說是整個資料庫都在用的索引，是Oracle存取單行數據的最快方法。這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個數據塊。

索引掃描(Index Scan)

通過index查找到數據對應的rowid值(對於非唯一索引可能返回多個rowid值)，然後根據rowid直接從表中得到具體的數據，這種查找方式稱為索引掃描或索引查找(index lookup)。

索引唯一掃描(index unique scan)

通過唯一索引查找一個數值經常返回單個ROWID,如果存在UNIQUE 或PRIMARY KEY 約束（約束只有一行記錄匹配），Oracle實現索引唯一性掃描。

索引範圍掃描(index range scan)

使用一個索引存取多行數據，在唯一索引上使用索引範圍掃描的典型情況下是在謂詞(where限制條件)中使用了範圍操作符，有以下三種情況會導致引起索引範圍掃描:

• 在唯一索引列上使用了range操作符(> < <> >= <= between)
• 在組合索引上，只使用部分列進行查詢，導致查詢出多行
• 對非唯一索引列上進行的任何查詢。

索引全掃描(index full scan)

什麼時候會引起索引全掃描呢?當不使用謂詞邏輯where;所有查詢結果數據都必須從索引中可以直接得到;需要排序操作，比如order by。

索引快速掃描(index fast full scan)

與索引全掃描很相似，只是不涉及排序動作。

優化技巧7:對於只從表中查詢出總行數的2%到4%行的表時，可以考慮創建索引。

優化技巧8:不要將那些頻繁修改的列作為索引列，頻繁修改會導致不必要的索引開銷。

優化技巧9:不要使用包含函數或操作符放入WHERE從句中的關鍵字作為索引，會導致索引失效，可以考慮使用函數索引。

優化技巧10:在組合索引中，沒有按照建立時的索引關鍵字順序描述，比如xyz變成了yxz，也會導致索引失效。

優化技巧11:如果在表中要建立索引的一列或多列上使用了函數或表達式，則創建的是基於函數的索引。基於函數的索引預先計算函數或表達式的值，並將結果存儲在索引中。B樹索引和bitmap索引也是函數索引

優化技巧12:排序動作能不做就不做，增加系統開銷的同時還會使快速索引失效。

五、多表關聯查詢操作

任何N（N大於2）張表之間的操作都將轉化為兩張表之間的關聯操作，查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數據的集合，也可以是表的部分行數據的集合，或者說集合篩選後的集合都成為row source。無論連接操作符如何，典型的連接類型共有3種：

排序合併連接(Sort Merge Join (SMJ))

select aa.CREATEPERSONNAME, bb.CREATEPERSON

from tbl_comm_commonticket aa, tbl_ybgz_ticket bb

where aa.CREATEPERSONNAME = bb.CREATEPERSON

order by aa.CREATEPERSONNAME, bb.CREATEPERSON

排序屬於代價很高的操作，特別對於大表。因此經常避免使用排序合併連接方法，但是如果2個row source都已經預先排序（比如primary Key索引），則這種連接方法可以選用。

嵌套循環(Nested Loops (NL))

分為驅動表(OUTER TABLE)和內層表(INNER TABLE)。因為嵌套循環，所以外層循環的次數越少越好，因此一般將數據量較小表或滿足條件的row source較小的表作為驅動表(用於外層循環)的理論依據。

select aa.processname,bb.processname

from tbl_comm_deal bb,tbl_comm_commonticket aa

where bb.processname = aa.processname

嵌套循環返回已經連接的行，而不必等待所有的連接操作處理完才返回數據 ，所以提高了響應速度。如果OUTER TABLE比較小，並且在INNER TABLE上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。另外，這種連接方式，是在RBO優化器中。

哈希連接(Hash Join)

散列基本原理是：使用一個下標範圍比較大的數組來存儲元素。可以設計一個函數（哈希函數，也叫做散列函數），使得每個元素的關鍵字都與一個函數值（即數組下標，hash值）相對應，於是用這個數組單元來存儲這個元素；但是，不能夠保證每個元素的關鍵字與函數值是一一對應的，因此極有可能出現對於不同的元素，卻計算出了相同的函數值，這樣就產生了「衝突」，換句話說，就是把不同的元素分在了相同的「類」之中。 總的來說，「直接定址」與「解決衝突」是哈希表的兩大特點。

散列連接是CBO 做大數據集連接時的常用方式，優化器使用兩個表中較小的表（或數據源）利用連接鍵在內存中建立散列表，然後掃描較大的表並探測散列表，找出與散列表匹配的行。

hash join只有在CBO方式下可以使用；Oracle初始化參數HASH_JOIN_ENABLED決定是否啟用hash join；pga_aggregate_target指定散列連接可用的內存大小；盡量使內層表生成的散列表最小，最好能夠全部載入內存；主要用於等值連接。

六、其他

優化技巧13:避免使用不確定操作符，因為會引起全表掃描; <> ,!=可以等價轉化為 < or > 代替。

優化技巧14:Where子句中出現IS NULL或者IS NOT NULL時，Oracle會停止使用索引而執行全表掃描。可以在設計表時，對索引列設置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。

優化技巧15:當通配符「%」或者「_」作為查詢字元串的第一個字元時，索引不會被使用 。

優化技巧16:對數據類型不同的列進行比較時，會使索引失效。

優化技巧17:UNION操作符會對結果進行篩選，消除重複，數據量大的情況下可能會引起磁碟排序。如果不需要刪除重複記錄，應該使用UNION ALL。

優化技巧18:Order By語句中的非索引列會降低性能，可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式。

優化技巧19:相同的Sql語句，要保證查詢字元完全相同，大小寫，空格位置，利用shared_pool，防止相同的Sql語句被多次分析，使用變數綁定。

優化技巧20:調整SQL語句的目的是為了在執行中使資源的使用減少到最小。除了選擇使用不同的SQL語法來優化執行代價，還可以通過調整執行順序優化SQL。

優化技巧21:Oracle在執行IN子查詢時，首先執行子查詢，將查詢結果放入臨時表再執行主查詢。而EXIST則是首先檢查主查詢，然後運行子查詢直到找到第一個匹配項。因此NOT EXISTS比NOT IN效率稍高，相應更快。但是(NOT) EXISTS 不等於(NOT) IN。

優化技巧22:可以多使用視圖進行軟解析，視圖只是把你要用的sql進行保存而已，你需要擔心的是視圖中的sql會不會效率太低，而不用擔心視圖的耗時。

優化技巧23:適當的時候強制使用rule會獲得更高效率；調試SQL時關注執行計劃和執行代價。

優化技巧24:避免視圖嵌套使用，尤其是針對視圖排序，篩選等操作。

優化技巧25:不同版本資料庫的執行計劃差別可能很大。

優化技巧26:不是只有select..是查詢，所有的DML操作都含有查詢過程。

七、SQL分析工具

EXPLAIN PLAN

使用步驟：

1、SQL> explain plan for select * from emp,dept where emp.deptno=dept.deptno;

2、select * from table(dbms_xplan.display);

3、

AUTOTRACE

使用步驟:

1、set autotrace on (可能會報Cannot SET AUTOTRACE的錯誤，參考這篇博客解決)

2、select * from emp,dept where emp.deptno=dept.deptno;

3、

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！