資料庫hint

㈠ 資料庫的表如果沒有主鍵查詢速度會慢嗎

我們搭建一個 Mysql 5.7 的環境，此處省略搭建步驟。

寫個簡單的腳本，製造一批帶主鍵和不帶主鍵的表：

可以看到執行時間變成了 0.67s。

整理

我們診斷的關鍵點如下：

1. 對於 information_schema 中的元數據表，執行計劃不能提供有效信息。

2. 通過查看 MySQL 改寫後的 SQL，我們猜測了優化器發生了誤判。

3. 我們增加了 hint，指導 MySQL 正確進行優化判斷。

但目前我們的實驗僅限於猜測，猜中了萬事大吉，猜不中就無法做出好的診斷。

㈡ 如何提高sql語句的執行效率

1、使用ordered提示

Oracle必須花費大量的時間來剖析多表的合並，用以確定表合並的最佳順序。SQL表達式涉及七個乃至更多的表合並，那麼有時就會需要超過30分鍾的時間來剖析，Ordered這個提示(hint)和其他的提示一起使用能夠產生合適的合並順序。

2、使用ordered_predicates

ordered_predicates提示在查詢的WHERE子句里指定的，並被用來指定布爾判斷(Booleanpredicate)被評估的順序。在沒有ordered_predicates的情況下，Oracle會使用下面這些步驟來評估SQL判斷的順序：子查詢的評估先於外層WHERE子句里的Boolean條件。

所有沒有內置函數或者子查詢的布爾條件都按照其在WHERE子句里相反的順序進行評估，即最後一條判斷最先被評估。每個判斷都帶有內置函數的布爾判斷都依據其預計的評估值按遞增排列。

3、限製表格合並評估的數量

提高SQL剖析性能的最後一種方法是強製取代Oracle的一個參數，這個參數控制著在評估一個查詢的時候，基於消耗的優化器所評估的可能合並數量。

(2)資料庫hint擴展閱讀：

1、表設計的優化，數據行的長度不要超過8020位元組，如果超過這個長度的話在物理頁中這條數據會佔用兩行從而造成存儲碎片，降低查詢效率。

2、語句的查詢優化，保證在實現功能的基礎上，盡量減少對資料庫的訪問次數；

3、建立高效的索引創建索引一般有以下兩個目的：維護被索引列的唯一性和提供快速訪問表中數據的策略。

大型資料庫有兩種索引即簇索引和非簇索引，一個沒有簇索引的表是按堆結構存儲數據，所有的數據均添加在表的尾部，而建立了簇索引的表，其數據在物理上會按照簇索引鍵的順序存儲。個表只允許有一個簇索引。

4、強制查詢轉換，有時候oracle 的優化器未必能走正確的查詢路線，這個時候就需要添加一些hint 之類的來規定他的執行路線。當然了，這個未必是最好的處理方案。因為雖然現在走這個路線是對的，以為因為數據的變化到這這個HINT 變得不可取。

㈢ oracle 的hint有什麼用

相對而言ORACLE優化器已經非常智能，產生的執行計劃也是最優的，但是ORACLE還是提供了一些hint供用戶顯示的控制執行計劃，因為大千世界各種各樣的業務都有，ORACLE優化器生成的執行計劃並不一定適用於所有業務，同時hint對於DBA而言更是一件利器，我們可以通過hint看到優化後的執行計劃，總結一下常用hint。1.全表掃描hintfull(table_name)相對而言，全表掃描hint使用場合較少，但是要知道，全表掃描並不一定比索引效率低，特別是查詢表中80%以上的資料庫，全表掃描的效率要高於索引掃描。2.索引hintindex(table_nameindex_name)這兩種hint一個是強制使用索引，另一個是強制執行計劃不要走索引，什麼用呢？常用於SQL調優過程中對比索引和非索引掃描。3.索引快速掃描hintindex_ffs(table_nameindex_name)這種索引稱之為索引快速掃描，常用於統計索引列鍵值的個數，如count(object_id)，跟全表掃描很像，但效率要比全表掃描要高很多，也就是執行計劃中看到的FASTFULLSCN。4.索引跳躍掃描hintindex_ss(table_nameindex_name)該hint在執行計劃中就是傳說中的INDEXSKIPSCAN這個對新手而言不太好理解，舉個例子索引有兩個列（A,B)類型組合索引，但是查詢中where條件只有B沒有Aselect*fromwhereb=1，此時ORACLE優化器走的索引就是所謂的索引跳躍掃描，只在CBO下適用，在RBO不適用。5.表關聯hintuser_nl(table_name1table_name2)此hint是表之間關聯效率最高的一種，通常用於一大一小兩表之間進行關聯查詢，小表作驅動表進行全表掃描，大表上要求有索引，走索引掃描，代價最低。6.表關聯hintuse_hash(table_name1table_name2)如果兩個表一大一小，但是大表沒有索引就會選擇HASH，如果兩個結果集比較小還可以承受，但是如果兩個較大的表HASH的話，會直接將資料庫HANG住，最好避免這種演算法7.表關聯hintuser_merge(table_name1table_name2)兩個表進行關聯，分別對全個表進行全表掃描後排序然後進行合並，排序既消耗內存又消耗CPU，總之代價比較大，常通過在兩個表上創建索引避免此類連接的發生。因此對比後發現，只有nested_loop方式進行關聯是最優的。8.表順序hintleading(table_name1table_name2)在RBO模式下，我們常常通過考慮from後面表的先後順序來進行SQL優化，但是此方法對RBO模式不再適用，CBO模式下按照順序選擇驅動表9.數據載入hintappend()直接路徑載入，對於大表操作極為有用，原理是什麼呢？打個比方，好比兩個超市理貨員，一人一箱貨需要上架到貨架上，一個人去找貨架中空閑位置去放，可能需要找N個空閑位，另一個人找一個空的貨架直接放上去，那個效果最高？當然是第二個，此hint的作用就是讓ORACLE找一個大空親塊直接存放新數據，而不是擠空閑位置去放新數據，如果此hint同時加上nologing聯合使用效果更高，常用於數據遷移項目中。10.dblink處理端hintdriving_site(table_name)此hint常用於通過dblink連接處理數據的業務，它的作用是將本地表推送到遠端資料庫進行關聯然後將結果返回，常用於本地表較小，遠端表較大的情況，效果很是不錯。11.數據返回模式hintfirst_rows該hint是影響數據返回模式hint，添加後ORACLE將邊處理邊返回，數據倉庫中用的比較多，但是在OLTP系統中也常見，上次系統優化就因為一兄弟在添加hint時，添加後發現執行計劃沒變，於是將原有的hintfirst_rows然後添加hintdriving_site()，執行計劃是變了，變化是因去去掉first_rows引起的，並且通過dblink遠端資料庫執行時查詢全變成的全表掃描，導致兩個業務大表hash，業務高峰直接將資料庫宕機，因此該hint添加或刪除一定要看遠端執行計劃有無發生變化，否則後果不開設想（切記）。

㈣ 怎樣保持Oracle資料庫SQL性能的穩定性

有客戶遇到SQL性能不穩定 突然變差導致系統性能出現嚴重問題的情況 對於大型的系統來說 SQL性能不穩定 有時突然變差 這是常常遇到的問題 這也是一些DBA的挑戰

對於使用Oracle資料庫的應用系統 有時會出現運行得好好的SQL 性能突然變差 特別是對於OLTP類型系統執行頻繁的核心SQL 如果出現性能問題 通常會影響整個資料庫的性能 進而影響整個系統的正常運行 對於個別的SQL 比如較少使用的查詢報表之類的SQL 如果出現問題 通常隻影響少部分功能模塊 而不會影響整個系統

那麼應該怎麼樣保持SQL性能的穩定性？

SQL的性能變差 通常是在SQL語句重新進行了解析 解析時使用了錯誤的執行計劃出現的 下列情況是SQL會重新解析的原因

SQL語句沒有使用綁定變數 這樣SQL每次執行都要解析

SQL長時間沒有執行 被刷出SHARED POOL 再次執行時需要重新解析

在SQL引用的對象（表 視圖等）上執行了DDL操作 甚至是結構發生了變化 比如建了一個索引

對SQL引用的對象進行了許可權更改森拿

重新分析（收集統計信息）了SQL引用的表和索引 或者表和索引統計信息被刪除

修改了與性能相關的部分參數

刷新了共享池

當然重啟資料庫也會使所有SQL全部重新解析

SQL重新解析後 跟以前相比 性能突然變差 通常是下列原因

表和索引的優化統計信息被刪除 或者重新收集後統計信息不準確 重新收集統計信息通常是由於收集策略（方法）不正確引起 比如對分區表使用 *** yze命令而不是用dbms_stats包 收集統計信息時采樣比例過小等等 Oracle優化器嚴重依賴於統計信息 如果統計信息有問題 則很容易導致SQL不能使用正確的執行計劃

SQL綁定變數窺螞祥探(bind peeking) 同時綁定變數對應的列上有直方圖 或者綁定變數的值變化范圍過大 分區數據分布極不均勻

） 綁定變數的列上有悶春搏直方圖

假如表orders存儲所有的訂單 state列有 種不同的值 表示未處理 表示處理成功完成 表示處理失敗 State列上有一個索引 表中絕大部分數據的state列為 和 佔少數 有下面的SQL

select * from orders where state=:b

這里:b 是變數 在大多數情況下這個值為 則應該使用索引 但是如果SQL被重新解析 而第一次執行時應用傳給變數b 值為 則不會使用索引 採用全表掃描的方式來訪問表 對於綁定變數的SQL 只在第一次執行時才會進行綁定變數窺探 並以此確定執行計劃 該SQL後續執行時全部按這個執行計劃 這樣在後續執行時 b 變數傳入的值為 的時候 仍然是第一次執行時產生的執行計劃 即使用的是全表掃描 這樣會導致性能很差

） 綁定變數的值變化范圍過大

同樣假如orders表有一列created_date表示一筆訂單的下單時間 orders表裡面存儲了最近 年的數據 有如下的SQL

Select * from orders where created_date >=:b ;

假如大多數情況下 應用傳入的b 變數值為最近幾天內的日期值 那麼SQL使用的是created_date列上的索引 而如果b 變數值為 個月之前的一個值 那麼就會使用全表掃描 與上面描述的直方圖引起的問題一樣 如果SQL第 次執行時傳入的變數值引起的是全表掃描 那麼將該SQL後續執行時都使用了全表掃描 從而影響了性能

） 分區數據量不均勻

對於范圍和列表分區 可能存在各個分區之間數據量極不均勻的情況下 比如分區表orders按地區area進行了分區 P 分區只有幾千行 而P 分區有 萬行數據 同時假如有一列proct_id 其上有一個本地分區索引 有如下的SQL

select * from orders where area=:b and proct_id =:b

這條SQL由於有area條件 因此會使用分區排除 如果第 次執行時應用傳給b 變數的值正好落在P 分區上 很可能導致SQL採用全表掃描訪問 如前面所描述的 導致SQL後續執行時全部使用了全表掃描

其他原因 比如表做了類似於MOVE操作之後 索引不可用 對索引進行了更改 當然這種情況是屬於維護不當引起的問題 不在本文討論的范圍

綜上所述 SQL語句性能突然變差 主要是因為綁定變數和統計信息的原因 注意這里只討論了突然變差的情況 而對於由於數據量和業務量的增加性能逐步變差的情況不討論

為保持SQL性能或者說是執行計劃的穩定性 需要從以下幾個方面著手

規劃好優化統計信息的收集策略 對於Oracle g來說 默認的策略能夠滿足大部分需求 但是默認的收集策略會過多地收集列上的直方圖 由於綁定變數與直方圖固有的矛盾 為保持性能穩定 對使用綁定變數的列 不收集列上的直方圖 對的確需要收集直方圖的列 在SQL中該列上的條件就不要用綁定變數 統計信息收集策略 可以考慮對大部分表 使用系統默認的收集策略 而對於有問題的 可以用DBMS_STATS LOCK_STATS鎖定表的統計信息 避免系統自動收集該表的統計信息 然後編寫腳本來定製地收集表的統計信息 腳本中類似如下

exec dbms_stats unlock_table_stats…

exec dbms_stats gather_table_stats…

exec dbms_stats lock_table_stats…

修改SQL語句 使用HINT 使SQL語句按HINT指定的執行計劃進行執行 這需要修改應用 同時需要逐條SQL語句進行 加上測試和發布 時間較長 成本較高 風險也較大

修改隱含參數 _optim_peek_user_binds 為FALSE 修改這個參數可能會引起性能問題（這里討論的是穩定性問題）

使用OUTLINE 對於曾經出現過執行計劃突然變差的SQL語句 可以使用OUTLINE來加固其執行計劃 在 g中DBMS_OUTLN CREATE_OUTLINE可以根據已有的執行正常的SQL游標來創建OUTLINE 如果事先對所有頻繁執行的核心SQL使用OUTLINE加固執行計劃 將最大可能地避免SQL語句性能突然變差

注 DBMS_OUTLN可以通過$ORACLE_HOME/rdbms/admin/dbmsol sql腳本來安裝

使用SQL Profile SQL Profile是Oracle g之後的新功能 此處不再介紹 請參考相應的文檔

除此之外 可以調整一些參數避免潛在的問題 比如將 _btree_bitmap_plans 參數設置為FALSE（這個參數請參考互聯網上的文章或Oracle文檔）

lishixin/Article/program/Oracle/201311/18054