oracle高效能sql調整-筆記1 (轉發）

oracle是rdbms關係型資料庫 關係型資料庫的設計宗旨就是為了減少資料冗餘。
sql調整的意義：對於那些每天都要執行上千次的查詢來說，sql調整會大大提高資料庫的效能，同時可以延長資料庫伺服器的硬體壽命。
sql調整的目標：
1、確保所有的sql語句在執行之前得到認可
  因為書寫sql語句的程式設計師的目標和進行sql調整的操作者目標有很大的不同。程式設計師考慮以最快的速度得到查詢的正確結果，而sql最佳化的dba目標可能是要達到sql語句消耗資源的最最佳化。
2 建立有益於所有的sql操作的oracle統計資料和索引
  因為oracle得最佳化器cbo是基於統計資訊的。rbo是基於資料字典的。
3 為所有的sql鎖定執行計劃。
   因為新增索引可能會造成一系列的sql語句執行計劃的更改。sql調整也要保證這些已經解析的執行計劃能夠持久化

oracle最佳化的全域性步驟，層次結構 從上到下一個一個按照順序來：
+環境調整 伺服器 網路 磁碟
++oracle例項調整 SGA後臺處理
+++oracle物件調整 表 索引
++++oracle sql調整
原始資料庫結構的設計也是影響sql速度的重要的因素之一
--oracle表中資料標準化的程度
--oracle計劃冗餘度的多少（透過向表中新增冗餘的欄位 反標準化，可以避免昂貴的sql連結，提高效能）
但是 一旦應用程式進入到生產環境的話，資料庫的表設計是不可能再更改的。

sql調整的障礙
1、定位不友好的sql語句  我們需要定位這些語句來保持執行計劃的持久化，sql原始碼可能存在於不同的位置
2、來自管理方面的地址 sql最佳化 耗時而且昂貴 dba必須進行成本收益分析來證明在硬體上的節省能夠抵消在資料庫進行sql調整帶來的費用。
3、對特定的sql生成器的調優
例如sap應用程式的產品 可以動態的生成一些sql 而對這些sql的調整是不可能的。
4、來自sql程式設計師的抵制
  很多程式設計師不願意承認他們的sql不是未達標準的sql語句
5、調整不可再用的sql語句  很多第三方應用程式 嵌入直接量 會產生很多獨一無二的執行計劃 對資源造成浪費
6、逐漸減少的邊緣收益 dba需要確定並調整高頻使用的sql語句   定位這些語句變的很困難，可能會存在一些不經常使用的sql語句，但是他們被調整後可以獲得很大的收益，這樣定位它們就特別的困難，因為他們在程式庫快取中只是偶爾出現 到最後能得到的收益遠遠小於dba為此付出的努力。

sql調整過程
--定位高頻使用的sql語句
--調整sql語句
--新增索引
--更改最佳化器模式
--新增提示
--將調整持久化

如何定位使用頻繁的sql語句？
--使用statspack  使用stats$sql_summary表來捕捉sql語句
--過濾程式庫快取  透過使用工具對當前庫快取中已經存在的所有sql語句進行解釋
-----------------------statpack捕捉sql？-----------------------------
當sql語句的一些行為超過閾值的時候，oracle會向stats$sql_summary中新增記錄，當閾值很低的時候資料庫會更加繁忙，而且每次statspack請求快照的時候也都是這樣，因此如果sql調整不再使用這些內容，那麼dba從這個檢視中刪除它們是非常重要的。向這個檢視中插入資料的條件，閾值是stats$sql_parameter表中儲存的引數。
execution_th   執行次數的閾值
disk_read_th   磁碟讀的閾值
parse_call_th  解析的閾值
buffer_gets_th 記憶體讀的閾值
任何一個閾值被超出都會在stats$sql_summary檢視中新增一條記錄。每小時進行一次statspack取樣。
缺點是 需要根據sql的調整隨時調整閾值。想要調整的時候楊堤閾值才能捕捉到不經常執行的sql，但是降低閾值又會導致向該檢視新增更多的記錄，statspack空間很快被填滿。所以調整完後要及時刪除該檢視中的記錄。


----------------------------過濾程式庫快取--------------------------
第三方工具
sqlplus指令碼

--------------------調整sql的操作-----------------------------
更改最佳化器模式
新增索引
新增提示

---------------------------調整持久化----------------------------
最佳化器計劃穩定性
改變sql來源
1、最佳化器計劃穩定性
最佳化器計劃穩定性可以為相同的sql語句保留相同的執行計劃，不用考慮資料庫的變化。
如何使用？ 從$ORACLE_HOME/rdbms/admin 下執行dbmsol指令碼。 建立一個OUTLN的使用者 擁有dba許可權，也會安裝outln_pkg的軟體包，為管理儲存框架提供過程。
2 改變sql來源

----------------sql 調整的一些簡單目標---------------------------
去掉不必要的大型表的全表掃描--》轉化成索引掃描
快取小型表的全表掃描--》常駐記憶體 到keep cache
檢驗最佳化索引的使用--》 使用正確的索引
檢驗最佳化的連結技術--》nest loop、hash

---------------sql 調整的工具箱----------------------------------
access.sql        解釋程式快取中的所有sql語句 建立一系列的報告
access_report.sql 總結sql的不同行為的報告
get_SQL.SQL       列出快取中所有匹配的sql
plan.sql   顯示任何sql語句的執行計劃
根據這些報告我們能得到哪些有價值的資訊呢？
確定高頻使用的表和索引
確定要快取的表  keep pool中
確定要進行記錄重新排序的表 對於高頻索引範圍掃描的大型表 進行記錄重新排序減少輸入輸出
刪除未被使用的索引
透過新增新索引禁止全表掃描




在擁有orader by的子句中經常看到全索引掃描oracle最佳化器經常使用全索引掃描來避免排序。
怎樣用全索引掃描來避免排序呢？
比如 select * from emp order by empno；
方法一：可以執行全表掃描，然後對結果集進行排序，那就需要先得到全表的結果集，然後再對結果集在temp表空間進行排序。這個全表掃描可以透過使用db_file_multiblock_read_count引數很快進行多塊讀，或者使用並行的提示來多表進行並行化。
方法二：透過索引，按照empno的順序讀取記錄，從而避免了排序。
dba_data_file   dba_free_space兩個檢視 集合查詢oracle表空間中可用空間和已用空間的大小
如果沒有建立域內建函式匹配的基於函式的索引，那麼這些函式通常會導致sql最佳化器執行全表掃描，即會出現索引失效的情況。所以，對於sql查詢中使用內建函式的情況，dba需要對於表建立相應的基於函式的索引。

access.sql指令碼 可以用來解釋所有存在於程式庫快取中的sql語句。
透過這個報告，我們可以看到某個表一共有多少塊，經歷過多少次的全表掃描，這些全表掃描是否合法？這個查詢速度是否可以透過使用基於函式的索引來提高？
get-sql。sql指令碼是用來得到 某個sql語句的文字。
為了達到這個目的，我們可以執行get-sql.sql指令碼，檢查執行次數為全表掃描的次數的sql語句
getsql。sql指令碼其實是查詢sqlarea表
set lines 2000;
select
sql_text,
disk_reads,
executions,
parse_calls
from
v$sqlarea
where
lower(sql_text) like '%tablename%'

order by
disk_read desc
   
;
得到該sql以後就可以使用指令碼plan。sql獲得執行計劃。
所以在表中新增索引是非常危險的操作，因為這會導致許多查詢執行計劃的變更。但是我們使用基於函式的索引就不會產生這樣的問題，因為oracle只有在查詢使用了匹配的內建函式的時候 才會使用這種型別的索引。一個匹配的基於函式的索引能夠改變執行計劃。  
內建函式的最重要的sql調整規則：無論什麼時候只要在sql語句中使用了內建函式，那麼就必須建立基於函式的索引

query_rewrite_enabled 這個初始化引數指導oracle對資料庫查詢進行重新書寫，以防止對大量資料進行重新求和。
cursor_sharing  當這個初始化引數設定為force時，它指導oracle在失去了、語句中使用主機變數替代直接量
SQL> show parameter rewrite;
NAME                                 TYPE
------------------------------------ -----------
VALUE
------------------------------
query_rewrite_enabled                string
TRUE
query_rewrite_integrity              string
enforced


關於物化檢視：
我們可以對於經常訪問的基礎表的求和建立一個物化檢視sum_sal，比如，對emp表的sal列進行求和，然後當sql訪問該求和的時候，select sum(sal) from emp;我們可以設定rewrite的引數為true，那麼最佳化器會對該sql查詢進行強制重寫，從物化檢視中讀取資料，而避免了對emp表的資料進行大量的重新求和的過程，但是如果我的sql語句採用了繫結變數的方法，就沒有辦法用物化檢視進行重新書寫了。

cursor_sharing 引數
force  強制使那些 除了變數以外完全相同的語句共享一個指標 這個功能可以透過系統生成的捆綁變數替代直接量的方法實現 這樣的替代可以增加對直接量sql的共享。
exact 使得完全相同的sql共享一個指標

但是如果設定cursor_sharing=force也是存在一些風險的，如果他們使用同一個執行計劃。比如走索引。那麼舉個例子說 假設一張銷售表在local列上有4個值，east，west，north，sourth。其中west的值佔了90%的比例，那麼當然是where條件以west進行篩選的話，走全表掃描是最優的，但是由於進行了sql共享，使用了同一個走索引的執行計劃，那麼就會降低sql的效能了，所以在9i以後 oracle做了改進，cbo在對指標的第一次呼叫時 窺視使用者自定義的捆綁變數值，這使得最佳化器會對where子句操作器再進行選擇，那麼就會在south出現的時候更改執行計劃。
在這種非常不平衡的欄位中使用捆綁變數時 會大大增強cursor sharing的效能。

------------------------減少sql 解析的技術-----------------------
sql調整的目標之一是確保所有經過預解析的sql語句是可以多次使用的。這要求輸入的sql是完全匹配的，一個微小的變化都會導致oracle對sql語句進行重新解析。
v$sql 檢視的 execution欄位可以檢視sql語句被重新使用的次數。
1、講所有的sql語句防止在儲存過程中
2、在sql語句中避免使用直接量

---------------------生成執行計劃-----------------------------
oracle最佳化器的只能是決定最快最有效的方法為查詢服務
對於oracle來說 查詢速度和查詢效率是完全不同的兩個概念
oracle的兩個最佳化器目標：
-----最大速度  以最短的時間返回結果集 適用於OLTP 線上事務處理系統 透過使用oracle的first rows最佳化器模式實現（一遍將已經得到的結果返回給客戶一遍進行其餘結果的搜尋）
-----最小的資源佔用  使用最少的機器和磁碟資源 它適用於查詢實施的速度不作為主要考慮的那些面向批處理的 oracle資料庫   這個目標使用oracle的all rows最佳化器模式實現

最佳化器預設值引數是 optimizer_mode
SQL> show parameter optimizer_mode
NAME                                 TYPE
------------------------------------ ------------
VALUE
------------------------------
optimizer_mode                       string
ALL_ROWS

oracle使用兩種型別的最佳化器
基於規則的最佳化器 RBO 使用資料字典中索引的資訊
基於成本的最佳化器 CBO     使用analyze和dbms_gather包收集出的統計資訊
oracle預設的最佳化器模式是choose
如果沒有統計資料 oracle選擇RBO最佳化器模式
如果有統計資料 oracle選擇CBO
在複雜的查詢中 三表做關聯的情況下，choose非常危險，因為如果只有其中一個表有統計資訊，oracle也會選擇CBO，並在執行的時候 對於沒有統計資訊的表進行抽樣估計，這是會檢查dba_table檢視的num-rows欄位決定的，num-rows空就會進行抽樣，非空說明有統計資訊。在執行過程中對錶做分析抽樣估計的動作是非常消耗資源的，會造成單個查詢效能的大大降低。

-----------------------表的訪問方式---------------------
全表掃描
雜湊獲取
rowid訪問
1、全表掃描
對錶中所有塊都進行讀取，被刪除的空塊也會進行掃描；
通用的原則：sql查詢要求返回表中大多數的資料塊，否則應避免使用全表掃描。
特殊情況：
使用oracle並行查詢 P00n 對第n部分掃描 同時進行
多cpu的資料庫伺服器上使用db-file-multiblock-read-count
全表掃描的條件：
--表沒有索引
--查詢中沒有where條件
--四種情況導致的索引失效 函式 資料型別不一致 範圍 不等條件
--查詢使用like運算子 引數以% 開始時候
--使用基於成本的最佳化器而且表中的記錄很少的時候，小表全表掃描優
--在初始化檔案中存在optimizer-mode=all-rows時候

如果一個表經過大量刪除，中間有很多空塊，那麼進行全表掃描，掃描空塊沒有意義，浪費資源，那麼就需要京杭對錶進行重新組織。


2、雜湊訪問
主要是針對 雜湊聚簇表來說的。透過一個雜湊關鍵字和一個雜湊函式來做運算直接找到儲存資料的資料塊和行，所以說可以經過一次io獲得資料。
事實上在雜湊聚簇中資料就是索引，因為資料決定行的物理位置。雜湊聚簇表中ORACLE根據行的碼值，利用內部函式或提供的函
數對聚簇碼值進行運算，以決定資料的物理儲存位置。雜湊聚簇
通常意味著如果透過聚碼訪問的話，一個IO就能夠提取到所需的
資料。
雜湊聚簇要點：
1、 雜湊聚簇透過雜湊碼查詢的時候需要的IO很少。幾乎一個IO就可以提取到所需的資料，除非發生了行溢位。而傳統索
引至少需要2個IO才能得到資料。
2、 雜湊聚簇查詢CPU開銷大。雜湊聚簇是CPU密集型的，而索引是IO密集型的。
3、 對錶中資料量比較有把握，如行數，每行佔用空間，有合理的上限，正確設定好HASHKYES和SIZE引數，那麼雜湊聚簇將比較適用。
4、 雜湊聚簇降低DML效能。
5、 總是經常透過HASHKEY等值訪問資料。

3、rowid訪問
rowid訪問是獲得單行資料最快的方法
索引中儲存了rowid，我們先從索引中收集rowid，然後使用rowid進行記錄的讀取。


------------------------------索引訪問方式----------------------
索引範圍掃描
單個索引掃描
降序索引反問掃描
and-euqal過濾器

1、索引範圍掃描
索引範圍掃描將從索引中讀取rowid列表，如果索引的叢集因子很高，那麼每個rowid就會指向不同的資料塊，訪問不同的資料塊將導致磁碟的輸入輸出，如果是磁碟的話就會產生磁頭新的尋道。我們需要將表中的記錄按照主索引的物理順序進行重新排序，這樣可以將索引範圍掃描過程中的磁碟輸入輸出的數量降低很多。
dba_index 檢視中的clustering-factor欄位提供了叢集引資的數量
--當叢集因子數與資料塊的數量非常接近時，表中的記錄就會與索引同步
--當叢集因字數與記錄數量接近的時候是最差的情況

2、快速全索引掃描
有些sql查詢可以只讀取索引而不用去讀取表中的資料，因為索引本身就可以滿足這個查詢。
db-file-multiblock-read-count也可以作用於索引全掃描的情況，同時系統也允許對索引全掃描進行並行查詢，進一步加快了反應速度。
要求使用索引快速全掃描的情況：
--所有要求查詢的欄位必須都在索引中
--查詢返回大於索引中所有記錄的10%。百分比試由多塊讀取的程度和並行度兩個引數決定。
--要計算表中符合特定條件的記錄的數目。比如使用count（*）的操作。
一般使用並行的快速完全索引掃描作為查詢最快的方法，但是這個過程會有很多因素介入，需要對滿足快速全索引掃描的任何一個查詢進行並行時間的測試，並檢視反應速度是否真的有所提高。


--------------------sql  連結-------------------------------------
常見連結方法：
巢狀迴圈連結
雜湊連結
排序合併連結
start with 查詢連結
connect by 查詢連結

1、巢狀迴圈連結
內部表 驅動表 小表
外部表 大表
oracle比較內部資料集的每一條記錄和外部資料集的每一條記錄，並返回滿足條件的記錄。
當中間結果資料集比較小的情況下，巢狀迴圈連結有最快的反應速度。
而在中間結果資料集非常大的情況下，雜湊連結會為我們提供最好的總體吞吐量和更快的執行速度。

2、雜湊連結
雜湊連結是在驅動表中執行全表掃描，然後在儲存上建立一個雜湊表，雜湊表可以從最大的那個表中讀取資料。
在雜湊連結中 兩個表都透過全表掃描進行讀取（通常是多塊讀+並行查詢）
雜湊反連結  anti-jion not in 子句，使用雜湊反連結
雜湊半連結  half-join

hash-multiblock-io-count的初始化引數決定由雜湊連結執行的多塊讀取的數量。

3、排序合併連結 sort merge join
排序合併連結是指從目標表中讀取兩個記錄資料集，並使用連結欄位將兩個記錄集分別排序。合併過程將來自一個資料集的每一條記錄同來自另一個資料集與之匹配的記錄相連線，並返回記錄資料集的交集。

三種最受歡迎的連結排序：
巢狀迴圈連結 &排序合併連結：更大的吞吐量 排序 大記錄子集快 不需要索引
雜湊連結 更短的反應時間 不排序 對於大記錄子集慢 需要索引

-----------------------對sql結果集進行排序----------------------
order by
join
group by
aggregate 獲取一個單獨的記錄 是對一組選定記錄應用分組函式所得的結果
select unique
select distinct
create index
在記憶體中將根據sort-area-size init。ora 引數劃分一個全域性程式區——排序的空間
對於多執行緒伺服器的連結，那麼排序空間將位於large-pool中
dba必須在為較大的排序任務分配足夠的排序空間 以避免在磁碟桑排序
也不能為並不需要執行很多排序擦做的任務分配太多空間  要在二者間保持一個平衡
不適合sort-area-size的排序任務將分頁到temp表空間進行磁碟排序 磁碟排序執行速度比記憶體排序的執行速度慢一萬多倍
一個專門用來排序的空間的大小決定於 sort-area-size
為每一個單獨的排序分配空間是由 sort-area-retained-size 決定
排序無法在指定空間完成，那麼將呼叫臨時表空間的磁碟排序。

磁碟排序缺點：
降低單個任務的速度
影響oracle例項其它正在執行的任務
空閒緩衝區等待 分頁的代價
磁碟排序很慢