oracle sql tuning 1--總體原則方法

資料庫調優的一個重要方面就是SQL調優.SQ調優包含3個基本的步驟

1.定位系統中那些耗資源的SQL

2.找出這些不好的語句後檢視他們的執行計劃

3.採取積極措施讓這些語句的執行計劃變得良好或者說是符合要求

以上3個步驟需要重複直到系統達到一定滿意程度或者沒有其它語句可以調整

調優的目的是什麼呢？

目的就是為了使響應時間縮短，處理同樣的工作系統暫用的資源更少,以下的一些方法能夠達到這個目的

1.readuce the  workload      縮小工作量

sql調節通常需要找更有效的方法去完成同樣的工作,對於需要調整的語句，在不改變原有語句功能的情況下，調整執行計劃，以縮小資源消耗

兩個例子說明資源消耗能被減少：

(1).如果經常執行的查詢訪問表的資料的比率小，那麼可以通過使用索引使查詢更有效率。這裡通過建立索引，減少的資源的使用.

(2).如果一個使用者在檢視一個有10000條記錄的表的前20條，返回的資料是按一定條件排序的，又假如這個查詢和索引都滿足索引，那麼這個使用者並不需要去訪問和排序這10000條記錄就可以看到滿足條件的前20條記錄.

2.balance the workload      平衡工作量

系統在執行的過程中，白天通常表現出峰值，系統資源佔用比較高。晚上系統的使用比較低。如果不是很關鍵的報表，批處理JOB,排程到晚上時間執行，這樣在白天系統的併發性縮小，白天就會有更多的資源來滿足那些關鍵的應用程式.

3.parallelize  the workload  並行執行工作

查詢訪問大量資料(典型的資料倉儲查詢),通常需要並行執行。在低併發的資料倉儲系統中這種並行執行對於縮小響應時間很有用。但是在OLTP環境下，它表現為高併發，通常會增加了系統總的資源消耗，影響到其他使用者!

說了這些那麼如何找到那些惡習的不良SQL呢?

有以下幾個方法:

• Automatic Database Diagnostic Monitor (ADDM)

• Automatic Workload Repository (AWR)

• V$SQL view

• Custom Workload

• SQL Trace

識別那些資源敏感的SQL

首先你要確定問題是出在一個程式上，還是一些程式都有問題，還是大部分程式都有類似的效能問題

（1）對於單獨程式來說

如果是單個的程式有問題就沒有什麼必要去檢查SQL程式碼了，OEM提供的工具能夠找到所謂差的SQL，產生SQL執行計劃，評估SQL效能

如果識別差的SQL不太緊急的話（例如SQL是動態產生的），那麼使用SQL_TRACE生成trace檔案，檔案中包含執行的SQL，然後使用TKPROF產生輸出檔案.TKPROF產生的輸出檔案中的SQ會按照不同的引數排序展現出來，例如執行消耗的時間（exeela），這就可以幫助識別那些差的SQL，按消耗時間排序就可以看出來，一般最差的都在檔案的前面

（2）如果整個應用需要適當調整，或者打算減少整個系統的CPU，IO消耗，那麼找出這些敏感的SQL一般來說有以下步驟

#1.確定那個在白天的那個時間跨度裡你想執行檢查，通常白天都是資料庫處理應用的高峰時間

#2.收集作業系統和ORACLE的統計資訊在你執行檢查的時間跨度期間，ORACLE的統計資訊至少可以從以下效能相關檢視中看出些眉目 I/O (V$FILESTAT), system statistics (V$SYSSTAT), and SQL statistics (V$SQLAREA, V$SQL or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS).

#3.在第2步收集的資料中找那些最佔資源的SQL。比較好的做法是查詢v$sqlstats，這個檢視包含共享池中所有SQL的資源使用情況。這個檢視中最常用的一些資源情況有：

 

Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)

Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)

Sorts (V$SQLSTATS.SORTS, for many sorts)

 

 

 

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