oracle 通過statspace 進行效能調優

一、說明在這貼子,我希望和大家一起討論YAPP,並提出自己的一點經驗及見解.

引用請註明出處　

　現在，大部分DBA都不會只利用資料緩衝區命中率，latch free wait time等指標來做資料庫效能調整。wait events是時下很流行效能指標,但oracle推薦的respone time.STATSPACK（或以前版本的BSTAT/ESTAT）中包含了DBA所需要的大部分指標。但如何有效地利用這些資料調整效能？從那裡開始調整？在本文我試用YAPP（respone time）結合STATSPACK提供的資料，並結合一個實際例子，做了個分析。歡迎大家指正.

第一部分:
YAPP效能優化思想
1．衡量資料庫的效能指標Response time ,YAPP是oracle早在1999就提供了另一種資料庫效能調整方法，資料庫的效能通過響應時間來衡量資料庫的效能：　　Response time = service time + wait time即使用者面對的響應時間由服務時間和等待時間組成。服務時間是處理你的請求實際使用的CPU時間，等待時間即等待資源可用所花費的時間。例如如果執行一個需要查詢索引的SQL語句，CPU時間可能包括buffer cache中的索引資料塊的處理時間，掃描該資料塊找到所需行的時間等，此過程中ORACLE可能需要從盤上讀資料，此時可能出現磁碟等待。

2.結合Oracle文件中的闡述，YAPP利用兩個重要的規則。80/20規則，和量化原則。（我覺得不僅適用是oracle中，其實做大多數事很好的規則）。
80/20規則:指的是很少的問題，影響大部分的效能。也就是***所說的主要矛盾。量化原則：不用解釋從字面就知道卻非常重要。一定要可度量的，這裡當然主要指的respone time.　　YAPP方法的主要思路是找出service time，wait time的主要組成部分(量化)，然後進行排序並調整主要部分(80/20規則)。另外用YAPP做優化時，你可以通過減少整個時間（如用更快的磁碟）或單位時間（如減少訪問磁碟次數）。基本步驟如下：　　（1）、得到服務時間和等待時間及其組成部分　　（2）、將所有組成部分排序　　（3）、依次優化每個部分　　（4）、對錶中的每一項,減少每次執行的代價或執行次數

三、ORACLE中的response time的量化　使用者感知的響應時間由一系列時間成分組成，所謂效能優化就是優化最耗時的成分，依次類推。從ORACLE instance的角度，請求一般含三個部分：client（如SQLPLUS，TUXEDO），前臺程式（如LOCAL = NO的服務程式），後臺程式（如DBWR）。所有的ORACLE程式（前臺和後臺）都會將所使用的CPU時間（service time）和各種等待事件所費時間記錄下來，這些資訊記錄在SGA，使用者可通過V$▁檢視訪問這些資料。這種資料分為session級和system級，使用者可訪問V$SYSTEM▁EVENT、V$SESSION_EVENT和V$SYSSTAT、V$SESSTAT等檢視來獲取這些資訊。ORACLE的STATSPACK工具（老版本中的BSTAT/ESTAT）就是查詢這些檢視來收集，計算和生成效能資料。要在ORACLE中產生時間記錄，DBA必須在INIT.ORA中將TIMED▁STATISTICS設定為TRUE或通過ALTERSYSTEM將其定義為TRUE(8.15有bug)。8i以前的版本中時間單位為1/100秒。9i以後時間單位為1/1,000,000秒(微秒)。　　在基於時間的優化方法中，最重要的檢視是V$SYSTEM▁EVENT、V$SESSION_EVENT，V$SYSSTAT、V$SESSTAT，V$LATCH，V$SQLAREA。V$SYSSTAT記錄使用的CPU時間，V$SYSTEM▁EVENT記錄程式等待時間花費的間，V$SQLAREA能用於找出最耗資源的SQL語句，而V$LATCH則可用於各種LATCH的等待資訊。這些檢視的詳細結構和含義見ORACLE文件。
Response time = service time + wait time
Service time = parse time cpu + Recursive cpu usage +other CPU TIME
Service time就是v$sysstat中的CPU used by this session（字面意義容易誤導人）。　Recursive cpu usage和parse time cpu是CPU used by this session的組成部分（對應v$sysstat Recursive cpu usage和parse time cpu，除此之外的CPU時間我們一律定義為other CPU.
Other CPU time:大部分的是SQL化費的。9i之後,我們可以從v$sql,v$sqlarea的cpu_time可以看出。。一般而言，SQL語句花費的CPU時間與訪問的緩衝區個數成比例，我們也可以從buffer
Gets做一個估計。
Wait time:主要從wait event的wait time來衡量。

四、在STATSPACK結合YAPP優化效能
STATSPACK的基本思想　　V$▁檢視中記錄的資料大都是系統啟動後的累加值，從某一個時間點看這些累加值沒有太大的實際意義。只有每隔一段時間對這些累加值取樣，計算出抽樣之間的差別對優化才有價值。ORACLE的STATSPACK就是完成定期取樣的工作。資料收集完成後，DBA可以執行STATSPACK帶的SPREPORT生成某兩個取樣點之間的差別。STATSPACK生成的報告中含有大多數DBA所需要的資料，包括上述檢視中的資料.關鍵是如何分析這些資料。
STATSPACK的時間概念，是statspack非常重要參照引數，時間只有對比，才有意義。如：
30分鐘wait time在幾十小時快照間隔不是問題，如果在45分鐘間隔，就可能是問題。為什麼說可能呢？其實，還有一個重要參考:有多少個session在連線。如有2000 session，那麼這也不會是問題。下面用YAPP的方法來分析statspack　　1、找出晌應時間組成部分，並量化.　　基於時間的優化方法YAPP就是要找出最值得優化（最耗時）的成分。我們需找出前臺程式使用的sevice time及等待事件花費的時間，service time資訊可以從V$SYSSTAT中得到而事件等待花費的時間可從V$SYSTEM▁EVENT中得到。在STATSPACK報告中它們分別在Instance Activity Stats for DB和Wait Events for DB章節中找到。wait time，parse time cpu，Recursive cpu usage。　在9i中最簡單的方法就是找出Top5 Wait Events.有意思的是9i中CPU time也會在Top5 Events中出現。如
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 3,722 79.45
db file sequential read 811,828 815 17.40
db file scattered read 202,998 114 2.44
log file sync 20,560 7 .14
log file sequential read 189 6 .12
-------------------------------------------------------------值得請注意的是這裡的CPU time實際就是service time，而不是wait events，很容易誤解。下面是根據STATSPACK報告的資料，用基於YAPP方法的優化步驟：（1）、在top timed events,比較service time, wait time優化最大比例。如上例就應當先優化service time (79.45%)（2）、找出CPU used by this session的值，parse time cpu, Recursive cpu usage,
Other CPU time找出最耗時優化。　　（3）wait time也是如此

四、例項說明這是我在實際中調整生產資料庫的一個例子。

四、例項說明這是我在實際中調整生產資料庫的一個例子。第一：檢視wait time, service time
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 3,722 79.45
db file sequential read 811,828 815 17.40
db file scattered read 202,998 114 2.44
log file sync 20,560 7 .14
log file sequential read 189 6 .12
-------------------------------------------------------------很顯然，需要的調整的service time, CPU time佔了79.45%,雖然其它也表現不好。下一步是找出CPU used by this session的值，parse time cpu, Recursive cpu usage.
Instance Activity Stats for DB: ORC6 Instance: orc6 Snaps: 21 -22

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 372,215 103.5 11.2
.
.
parse time cpu 56,253 15.6 1.7
.
. recursive cpu usage 299,266 83.2 9.0由此：parse time CPU 56253/372215=15%
recursive cpu usage 299266/372215=80%
other cpu為5%.考慮到應用中有許多pl/sql，其部分的sql會在report表現。因此檢視：
SQL ordered by Gets for DB: ORC6 Instance: orc6 Snaps: 21 -22
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100

CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
104,852,823 33,259 3,152.6 91.5 2977.30 3160.98 269479692
Module: autoscanservices.exe
begin CELL_NAMES_CELL_EXIST(CELLNAME=>:CELLNAME, TESTTYPE=>:TEST
TYPE, RESULT=>:RESULT); end;

104,476,590 33,265 3,140.7 91.2 2963.67 3144.84 4132718792
Module: autoscanservices.exe
SELECT count(*) from cell_names where CELL_NAME=:b1

130,701 105 1,244.8 0.1 1.22 1.18 1578495722
Module: autoscanservices.exe
UPDATE cell_names set cell_name_state='D',do_user=:b2,do_time=sy
sdate where machine_no=:b1在這裡我們找到了問題所在。CELL_NAMES_CELL_EXIST(CELLNAME=>:CELLNAME, TESTTYPE=>:TEST
TYPE, RESULT=>:RESULT);它裡面包含一個SELECT count(*) from cell_names where CELL_NAME=:b1語句。從上可知SELECT count(*) from cell_names where CELL_NAME=:b1產生大量的buffer gests有時竟達到總數的80-90%時。追蹤該SQL得到其執行計劃如下*****************************************************************************

SELECT count(*)
from
cell_names where CELL_NAME=:b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.07 0 3337 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.07 0 3337 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 65

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3337 r=0 w=0 time=77558 us)
1 TABLE ACCESS FULL CELL_NAMES (cr=3337 r=0 w=0 time=77546 us)

******************************************************************************從而得到原因，全表掃描引起的，大量的buffer get消耗大量的資源。這個表有三十多萬多記錄，在cell_name沒有索引.原因，全表掃描引起的.解決方法：在cell_names的cell_name建立索引。隨後的做report Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 536 51.76
db file sequential read 67,100 427 41.27
library cache load lock 28 23 2.19
library cache pin 20 15 1.49
db file scattered read 6,236 10 .97
-------------------------------------------------------------同樣在一個小時的間隔中cpu time ,wait time大量減少，達到提高response time的目的

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