ORACLE將執行過的SQL語句存放在記憶體的共用池

標籤：oracle   管理員   shared   資料庫   使用者   

Oracle SQL效能最佳化深入淺出
ORACLE將執行過的SQL語句存放在記憶體的共用池(shared buffer pool)中，可以被所有的資料庫使用者共用。當你執行一個SQL語句(有時被稱為一個遊標)時,如果它和之前的執行過的語句完全相同,
ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑. 這個功能大大地提高了SQL的執行效能並節省了記憶體的使用。

為了不重複解析相同的SQL語句，在第一次解析之後，Oracle將SQL語句存放在記憶體中。這塊位於系統全域地區SGA(systemglobal area)的共用池(shared buffer poo1)中的記憶體可以被所有的資料庫使用者共用。因此，當你執行一個SQL語句(有時被稱為一個遊標)時，如果它和之前執行過的語句完全相同，Oracle就能很快獲得已經被解析的語句以及最好的執行方案。Oracle的這個功能大大地提高了SQL的執行效能並節省了記憶體的使用。
可惜的是，Oracle只對簡單的表提供高速緩衝(cache bufferiIlg)，這個功能並不適用於多表串連查詢。資料庫管理員必須在啟動參數檔案中為這個地區設定合適的參數，當這個記憶體地區越大，就可以保留更多的語句，當然被共用的可能性也就越大了。當向Oracle提交一個SQL語句時，Oracle會首先在這塊記憶體中尋找相同的語句。

SQL共用的三個條件：
1，當前被執行的語句和共用池中的語句必須完全相同 （包括大小寫、空格、換行等）
2，兩個語句所指的對象必須完全相同 （同義字與表是不同的對象）
3，兩個SQL語句中必須使用相同的名字的綁定變數(bind variables)

Oracle對兩者採取的是一種嚴格匹配策略，要達成共用。SQL語句必須完全相同(包括空格、換行等)。能夠使用共用的語句必須滿足三個條件：

① 字元級的比較。當前被執行的語句和共用池中的語句必須完全相同。
例如： SELECT * FROM ATABLE；和下面每一個SQL語句都不同：
SELECT *from ATABLE
Select * From Atable；
② 語句所指對象必須完全相同。即兩條SQL語句操作的資料庫物件必須同一。
③語句中必須使用相同命名的綁定變數。如：第一組的兩個SQL語句是相同的，可以共用；而第二組中兩個語句不同，即使在運行時賦予不同的綁定變數以相同的值：
● 第一組 select pin，name from people where pin = ：blk1.pin；
select pin，name from people where pin =：blk1.pin；
●第二組 select pin，name from people where pin =：blk1.ot_jnd；
select pin，name from people where pin = ：blk1.ov_jnd；

650) this.width=650;" title="點擊查看大圖" alt="659x414" src="http://img.educity.cn/img_10/263/2014010505/21786054626.png" width="659" height="414" />


650) this.width=650;" title="點擊查看大圖" alt="832x520" src="http://img.educity.cn/img_10/263/2014010505/21789054626.png" width="700" height="437" />

SQL PARSE與共用SQL語句：

當一個Oracle執行個體接收一條sql後
1、Create a Cursor 建立遊標
2、Parse the Statement 分析語句
3、Describe Results of a Query 描述查詢的結果集
4、Define Output of a Query 定義查詢的輸出資料
5、Bind Any Variables 綁定變數
6、Parallelize the Statement 並存執行語句
7、Run the Statement 運行語句
8、Fetch Rows of a Query 取查詢出來的行
9、Close the Cursor 關閉遊標


下面這個語句每執行一次就需要在SHARE POOL 硬解析一次，一百萬使用者就是一百萬次，消耗CPU和記憶體，如果業務量大，很可能導致宕庫……

如果綁定變數，則只需要硬解析一次，重複調用即可
select * from dConMsg
where contract_no = 32013484095139

ORACLE 最佳化器模式：
Oracle的最佳化器共有3種模式：RULE (基於規則)、COST(基於成本)、CHOOSE(基於選擇)。
設定預設的最佳化器的方法，是在啟動參數檔案中針對OPTIMIZER_ MODE參數的各種聲明進行選擇，如RULE、COST、CHOOSE、ALL_ ROWS、FIRST_ ROWS。當然也可以在SQL語句層級或是會話層級對其進行覆蓋。
為了使用基於成本的最佳化器(CBO，Cost—Based Optimizer)，必須經常運行analyze命令，以增加資料庫中的對象統計資訊(object statistics)的準確性。如果資料庫的最佳化器模式設定為基於選擇，那麼實際的最佳化器模式將和是否運行過analyze命令有關。如果資料表已經被analyze過，最佳化器模式將自動切換成CBO，反之，資料庫將採用RULE形式的最佳化器。在預設情況下，Oracle採用CHOOSE最佳化器。為避免那些不必要的全表掃描，必須盡量避免使用CHOOSE最佳化器，而直接採用基於規則或者基於成本的最佳化器。

影響資料庫系統效能的要素：
1，主機CPU,RAM,儲存系統;
2，OS參數配置,ORACLE參數配置;
3，應用方面：資料庫設計及SQL編程的品質
一個效能優秀的應用系統需要：
1，良好的硬體設定；
2，正確合理的資料庫及中介軟體參數配置；
3，合理的資料庫設計；
4，良好的sql編程；
5，運行期的效能最佳化

SQL Tunning 的重點：
SQL: insert, update, delete, select（主要關注的是select）
關注的是：如何用最小的硬體資源消耗、最少的回應時間定位元據位置

SQL最佳化的一般性原則：
1，目標：
減少伺服器資源消耗（主要是磁碟IO）；
2，設計方面：
盡量依賴oracle的最佳化器，並為其提供條件；
合適的索引，索引的雙重效應，列的選擇性；
3，編碼方面：
利用索引，避免大表FULL TABLE SCAN；
合理使用暫存資料表；
避免寫過於複雜的sql，不一定非要一個sql解決問題；
在不影響業務的前提下減小事務的粒度；

最佳化概括：
● 建立表的時候。應盡量建立主鍵，盡量根據實際需要調整資料表的PCTFREE和PCTUSED參數；大資料表刪除，用truncate table代替delete。

● 合理使用索引，在OLTP應用中一張表的索引不要太多。資料重複量大的列不要建立二叉樹索引，可以採用位元影像索引；複合式索引的列順序盡量與查詢條件列順序保持一致；對於資料操作頻繁的表，索引需要定期重建，以減少失效的索引和片段。


● 查詢盡量用確定的列名，少用*號。select count(key)from tab where key> 0效能優於select count(*)from tab；

當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 ‘*’ 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將’*’ 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間；

盡量少嵌套子查詢，這種查詢會消耗大量的CPU資源；對於有比較多or運算的查詢，建議分成多個查詢，用union all連接起來；多表查詢的查詢語句中，選擇最有效率的表名順序。Oracle解析器對錶解析從右至左，所以記錄少的表放在右邊。

● 盡量多用commit語句提交事務，可以及時釋放資源、解鎖、釋放日誌空間、減少管理花費；在頻繁的、效能要求比較高的資料操作中，盡量避免遠端存取，如資料庫鏈等，訪問頻繁的表可以常駐記憶體：alter table．．．cache；

● 在Oracle中動態執行SQL，盡量用execute方式，不用dbms_sql包。

ORACLE將執行過的SQL語句存放在記憶體的共用池